Como o MariaDB Otimiza Cláusulas ORDER BY

Em alguns casos o MariaDB 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 MariaDB não pode usar índices para resolver o ORDER BY: (Note que o MariaDB 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 MariaDB 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 MariaDB pode usar índices para resolver a consulta. Se você obtiver Using filesort na coluna extra, então o MariaDB não pode usar índices para resolver o ORDER BY. Leia "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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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:

Retornar