Início / dba / Perguntas / 153117
Accepted
Nuno
Asked: 2016-10-24 10:18:31 +0800 CST

Usar "OR" em duas colunas indexadas não usa índices

  • 772

Na consulta abaixo, frome tidsão índices da repliestabela.

SELECT * FROM `replies`
WHERE `from`="<userId>"
OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="<userId>")

Ao usar "OR", parece que ele faz uma varredura completa da tabela (~ 3 milhões de linhas). O EXPLAINdiz que uma possível chave seria from, mas não usa nenhuma.

No entanto, na consulta abaixo, frid_lte frid_gtsão indexados. As duas colunas estão em um índice complexo (frid_lt, frid_gt), mas frid_gttambém possui seu próprio índice.

SELECT `mid` FROM `messages`
WHERE `frid_lt`="<userId>" OR `frid_gt`="<userId>"

E esta consulta usa dois índices. O EXPLAINdiz "index_merge" e "Usando sort_union(frid_lt,frid_gt); Usando where".

Por que a primeira consulta não usa uma mesclagem de índice?
Existe alguma melhoria que eu possa fazer para que o mecanismo também use uma mesclagem de índice?

mysql index

1 respostas

  1. Best Answer

    ORnão otimiza bem. Uma solução comum é usar UNION:

    ( SELECT * FROM replies WHERE `from` = "..." )
UNION ALL   -- or UNION DISTINCT if you know there are no dups
( SELECT r.* FROM replies AS r
    JOIN posts AS p  ON p.tid = r.tid
    WHERE p.from = "..." )

    Observe que também evitei o geralmente ineficienteIN ( SELECT ... )

    Para maior desempenho, tenha estes índices:

    replies:  INDEX(`from`)
posts:    INDEX(`from`, tid)  -- in this order
replies:  INDEX(tid)

    (E observe que PRIMARY KEY é um índice, então não adicione um índice redundante.)

    Em seu segundo exemplo, a "mesclagem de índice" que você experimentou pode ou não ser mais rápida que um arquivo UNION.

    Ah, é uma ATUALIZAÇÃO

    Para otimizar UPDATE, faça dois separados UPDATEs(no UNION, no OR). Um verifica diretamente from. O outro é um "UPDATE multi-table" (veja o manual) similar ao segundo select acima.

    • 1

relate perguntas