No SO, alguém perguntou recentemente Por que o ORDER BY não está usando o índice?
A situação envolvia uma tabela InnoDB simples no MySQL com três colunas e 10 mil linhas. Uma das colunas, um número inteiro, foi indexada - e o OP procurou recuperar toda a tabela classificada nessa coluna:
SELECT * FROM person ORDER BY age
Ele anexou EXPLAINa saída mostrando que essa consulta foi resolvida com um filesort(em vez do índice) e perguntou por que isso aconteceria.
Apesar da dica que faz FORCE INDEX FOR ORDER BY (age) com que o índice seja usado , alguém respondeu (com comentários de apoio/votos positivos de outros) que um índice só é usado para classificação quando todas as colunas selecionadas são lidas do índice (ou seja, como normalmente seria indicado por Using indexna Extracoluna de EXPLAINsaída). Posteriormente, foi dada uma explicação de que percorrer o índice e, em seguida, buscar colunas da tabela resulta em E/S aleatória, que o MySQL considera mais caro do que um arquivo filesort.
Isso parece ir contra o capítulo do manual sobre ORDER BYOtimização , que não apenas transmite a forte impressão de que a satisfação ORDER BYde um índice é preferível a realizar uma classificação adicional (na verdade, filesorté uma combinação de quicksort e mergesort e , portanto, deve ter um limite inferior de ; enquanto percorrer o índice em ordem e procurar na tabela deveria ser - então isso faz todo o sentido), mas também deixa de mencionar essa suposta "otimização" ao mesmo tempo em que afirma:Ω(nlog n)O(n)
As consultas a seguir usam o índice para resolver a
ORDER BYpeça:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
Na minha leitura, esse é precisamente o caso nesta situação (ainda assim o índice não estava sendo usado sem uma dica explícita).
Minhas perguntas são:
É realmente necessário que todas as colunas selecionadas sejam indexadas para que o MySQL opte por usar o índice?
Em caso afirmativo, onde isso está documentado (se houver)?
Se não, o que estava acontecendo aqui?
Essa é uma pergunta complicada porque existem fatores que determinam se vale a pena usar um índice.
FATOR Nº 1
Para qualquer índice, qual é a população-chave? Em outras palavras, qual é a cardinalidade (contagem distinta) de todas as tuplas registradas no índice?
FATOR Nº 2
Qual mecanismo de armazenamento você está usando? Todas as colunas necessárias são acessíveis a partir de um índice?
QUAL É O PRÓXIMO ???
Vamos dar um exemplo simples: uma tabela que contém dois valores (Masculino e Feminino)
Vamos criar uma tabela com um teste para uso de índice
TESTE InnoDB
TESTE MyISAM
Análise para InnoDB
Quando os dados foram carregados como InnoDB, observe que todos os quatro
EXPLAINplanos usaram ogenderíndice. O terceiro e o quartoEXPLAINplanos usaram ogenderíndice, embora os dados solicitados fossemid. Por quê? Porqueidestá noPRIMARY KEYe todos os índices secundários têm ponteiros de referência de volta para oPRIMARY KEY(através do gen_clust_index ).Análise para MyISAM
Quando os dados foram carregados como MyISAM, observe que os três primeiros
EXPLAINplanos usaram ogenderíndice. No quartoEXPLAINplano, o Query Optimizer decidiu não usar nenhum índice. Ele optou por uma varredura completa da tabela. Por quê?Independentemente do DBMS, os Otimizadores de Consulta operam com uma regra prática muito simples: se um índice estiver sendo rastreado como um candidato a ser usado para realizar a pesquisa e o Otimizador de Consulta calcular que deve pesquisar mais de 5% do número total de linhas da tabela:
CONCLUSÃO
Se você não tiver índices de cobertura adequados ou se a população-chave para qualquer tupla for superior a 5% da tabela, seis coisas devem acontecer:
WHERE,GROUP BYe ORDER BY` dessas consultasWHEREcolunas de cláusula com valores estáticosGROUP BYcolunasORDER BYcolunasWHEREcláusula sensata)Eu escrevi sobre esta regra de ouro de 5% no passado:
May 07, 2012: MySQL EXPLAIN não mostra 'use index' para FULLTEXTMar 22, 2012: Por que o MySQL escolheu este plano de execução?Mar 09, 2012: índice não sendo usadoJan 18, 2012: A variável de status do MySQL Handler_read_rnd_next está crescendo muitoDec 27, 2011: MySQL - maneira mais rápida de ALTER TABLE para InnoDBJul 29, 2011: MySQL Query Optimization : Indexação e PaginaçãoJul 12, 2011: MySQL consulta muito lenta ao alterar um campo WHERE, apesar de nenhum índice/chaveATUALIZAÇÃO 14/11/2012 13:05 EDT
Eu dei uma olhada na sua pergunta e no post original do SO . Então, pensei sobre o
Analysis for InnoDBque mencionei antes. Coincide com apersonmesa. Por quê?Tanto para tabelas
mfcomo parapersonidEXPLAINplano completamente diferenteAgora, observe a consulta da pergunta SO:
select * from person order by age\G. Como não háWHEREcláusula, você exigiu explicitamente uma verificação completa da tabela . A ordem de classificação padrão da tabela seria porid(PRIMARY KEY) por causa de seu auto_increment e o gen_clust_index (também conhecido como Clustered Index) é ordenado por rowid interno . Ao ordenar pelo índice, lembre-se de que os índices secundários do InnoDB têm o rowid anexado a cada entrada de índice. Isso produz a necessidade interna de acesso total à linha a cada vez.A configuração
ORDER BYem uma tabela InnoDB pode ser uma tarefa assustadora se você ignorar esses fatos sobre como os índices InnoDB são organizados.Voltando àquela consulta SO, já que você exigiu explicitamente uma verificação completa da tabela , IMHO, o MySQL Query Optimizer fez a coisa certa (ou pelo menos escolheu o caminho de menor resistência). Quando se trata de InnoDB e da consulta SO, é muito mais fácil executar uma varredura completa da tabela e mais algumas,
filesortem vez de fazer uma varredura completa do índice e uma pesquisa de linha por meio do gen_clust_index para cada entrada de índice secundário.Não sou um defensor do uso de Index Hints porque ignora o plano EXPLAIN. Porém, se você realmente conhece seus dados melhor que o InnoDB, terá que recorrer a Index Hints, principalmente em consultas que não possuem
WHEREcláusula.ATUALIZAÇÃO 14/11/2012 14:21 EDT
De acordo com o livro Understanding MySQL Internals
O parágrafo 7º diz o seguinte:
É por isso que afirmei anteriormente: é muito mais fácil executar uma verificação completa da tabela e, em seguida, algum tipo de arquivo, em vez de fazer uma verificação completa do índice e uma pesquisa de linha por meio do gen_clust_index para cada entrada de índice secundário . O InnoDB fará uma pesquisa de índice duplo toda vez . Isso soa meio brutal, mas são apenas os fatos. Novamente, leve em consideração a falta de
WHEREcláusula. Isso, por si só, é a dica para o MySQL Query Optimizer fazer uma varredura completa da tabela.Adaptado (com permissão) da resposta de Denis para outra pergunta no SO:
Como todos os registros (ou quase todos) serão buscados pela consulta, geralmente é melhor você não ter nenhum índice. A razão para isso é que realmente custa algo para ler um índice.
As you're going for the entire table, sequentially reading the table and sorting its rows in memory may be your cheapest plan. If you only need a few rows and most will match the where clause, going for the smallest index will do the trick.
To understand why, picture the disk I/O involved.
Suppose you want the whole table without an index. To do this, you read data_page1, data_page2, data_page3, etc., visiting the various disk pages involved in order, until you reach the end of the table. You then then sort and return.
If you want the top 5 rows without an index, you'd sequentially read the entire table as before, while heap-sorting the top 5 rows. Admittedly, that's a lot of reading and sorting for a handful of rows.
Suppose, now, that you want the whole table with an index. To do this, you read index_page1, index_page2, etc., sequentially. This then leads you to visit, say, data_page3, then data_page1, then data_page3 again, then data_page2, etc., in a completely random order (that by which the sorted rows appear in the data). The IO involved makes it cheaper to just read the whole mess sequentially and sort the grab bag in memory.
If you merely want the top 5 rows of an indexed table, in contrast, using the index becomes the correct strategy. In the worst case scenario you load 5 data pages in memory and move on.
Um bom planejador de consultas SQL, aliás, tomará sua decisão sobre usar ou não um índice com base na fragmentação de seus dados. Se buscar linhas em ordem significar zoom para frente e para trás na tabela, um bom planejador pode decidir que não vale a pena usar o índice. Por outro lado, se a tabela for agrupada usando o mesmo índice, é garantido que as linhas estarão em ordem, aumentando a probabilidade de serem usadas.
Mas então, se você juntar a mesma consulta com outra tabela e essa outra tabela tiver uma cláusula where extremamente seletiva que pode usar um pequeno índice, o planejador pode decidir que é realmente melhor, por exemplo, buscar todos os IDs de linhas marcadas como
foo, hash juntar as tabelas e classificá-las na memória.