Skip navigation links
5.2.9 Como o MySQL Otimiza Cláusulas LIMIT »
Section Navigation      [Toggle]

5.2.8. Como o MySQL Otimiza Cláusulas ORDER BY

Em alguns casos o MySQL pode utilizar índices para satisfazer uma requisição de ORDER BY ou GROUP BY sem fazer uma ordenação extra.

O índice também pode ser usado mesmo se o ORDER BY não coincidir exatamente com o índice, uma vez que todas as partes de índices não usadas e todos os extras na coluna ORDER BY são constantes na cláusula WHERE. A seguinte consulta usará o índice para resolver a parte ORDER BY / GROUP BY: 

SELECT * FROM t1 ORDER BY key_part1,key_part2,...
SELECT * FROM t1 WHERE key_part1=constante ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constante GROUP BY key_part2
SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 DESC
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC

Alguns casos onde o MySQL não pode usar índices para resolver o ORDER BY: (Note que o MySQL ainda usará índices para encontrar o registro que coincide com a cláusula WHERE):

  • Você está fazendo um ORDER BY em diferentes chaves:

    SELECT * FROM t1 ORDER BY key1,key2

  • Você está fazendo um ORDER BY usando partes de chaves não consecutivas.

    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2

  • Você está misturando ASC e DESC.

    SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC

  • As chaves usadas para buscar os registros são as mesmas usadas para fazer o ORDER BY:

    SELECT * FROM t1 WHERE key2=constant ORDER BY key1

  • Você está unindo muitas tabelas e as colunas nas quais você está fazendo um ORDER BY não são todas da primeira tabela que não é const e que é usada para retornar registros. (Esta é a primeira tabela na saída do EXPLAIN que não usa um método de busca de registro const).

  • Você tem diferentes expressões ORDER BY e GROUP BY.

  • O índice da tabela usada é um tipo de índice que não armazena registros em ordem. (Como o índice HASH em tabelsn HEAP).

Nestes casos onde o MySQL tem que ordenar o resultado, ele usa o seguinte algoritmo:

  • Lê todos os registros de acordo com a chave ou por uma varredura da tabela. Registros que não coincidem com a cláusula WHERE são saltados.

  • Armazena a chave ordenada em um buffer (de tamanho sort_buffer).

  • Quando o buffer ficar cheio, execute ordeno-o e armazene o resultado em um arquivo temposrário. Salve um ponteiro para o bloco ordenado. (No caso de todos os regitros caberem no buffer ordenado, nenhum arquivo temporário é criado).

  • Repete o armazenamento acima até todas as linhas tenham sido lidos.

  • Faz um multi-merge até MERGEBUFF (7) regiões para um bloco em outro arquivo temporário. Repete até que todos os blocos do primeiro arquivo estejam no segundo arquivo.

  • Repete o seguinte até que restem menos que MERGEBUFF2 (15) blocos.

  • No último multi-merge, só o ponteiro para o registro (última parte de chave ordenada) é escrito em um arquivo de resultado.

  • Agora o código em sql/records.cc será usado para ler através deles ordenadamente usando os ponteiros de registro no arquivo resultante. Para otimização , lemos em um grande bloco de ponteiros de registros, ordena-os então lemos o registros ordenadamente de de um buffer de registro. (read_rnd_buffer_size) .

Você pode verificar com EXPLAIN SELECT ... ORDER BY se o MySQL pode usar índices para resolver a consulta. Se você obtiver Using filesort na coluna extra, então o MySQL não pode usar índices para resolver o ORDER BY. See Secção 5.2.1, “Sintaxe de EXPLAIN (Obter informações sobre uma SELECT)”.

Se você quiser ter uma velocidade ORDER BY maior, primeiro você deve ver se você pode fazer que o MySQL use índices em vez de fazer um fase de ordenação extra. Se não for possível, então você pode fazer:

  • Aumente o tamanho da variável sort_buffer_size.

  • Aumente o temenho da variável read_rnd_buffer_size.

  • Altere tmpdir para apontar para um disco dedicado com muito espaço vazio. Se você usa o MySQL 4.1 ou posterior você pode distribuir a carga entre diversos discos físicos definindo tmpdir com uma lista de caminhos separados por dois pontos : (ponto e vírgula ; no Windows). Eles serão usados de acordo com o método round-robin. Nota: Estes caminho devem estar em diferentes discos físicos, e não em diferentes partições do mesmo disco.

Por padrão, o MySQL ordena todas as consultas GROUP BY x,y[,...] como se você tivesse especificado ORDER BY x,y[,...]. Se você incluir a cláusula ORDER BY explicitamente, o MySQL a otimizará sem qualquer penalidade na velocidade, embora a ordenacao ainda ocorra. Se a consulta inclui um GROUP BY mas você deseja evitar a sobrecarga da ordenar o resultado, você pode suprimir a ordenacao especificando ORDER BY NULL: 

INSERT INTO foo SELECT a,COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.