Como otimizar SELECTs e outras consultas do MySQL - XOOPS Brasil
Primeiramente, uma coisa que afeta todas as consultas: Quanto mais complexo seu sistema de permissões, maior a sobrecarga.
Se você não tiver nenhuma instrução GRANT realizada, MySQL otmizará a verificação de permissões de alguma forma. Dessa forma, se você possui um volume muito alto, o tempo pode piorar tentando permitir o acesso. Por outro lado, maior verificação de permissões resulta em uma sobrecarga maior.
Se o seu problema é com alguma função explícita do MySQL, você pode sempre consultar o tempo da mesma com o cliente MySQL:
O exemplo acima demonstra que o MySQL pode excutar 1.000.000 expressões + em 0.32 segundos em um PentiumII 400MHz.
Todas funções MySQL devem ser bem otimizadas, mas existem algumas excessões e o benchmark(loop_count,expression) é uma ótima ferramenta para saber se existe um problema com sua query.
EXPLAIN nome_tabela
ou EXPLAIN SELECT opções_select
EXPLAIN nome_tabela é um sinônimo para DESCRIBE nome_tabela ou SHOW COLUMNS FROM nome_tabela.
Quando uma instrução SELECT for precedida da palavra chave EXPLAIN, o MySQL explicará como ele deve processar a SELECT, fornecendo informação sobre como as tabelas estão sendo unidas e em qual ordem.
Com a ajuda de EXPLAIN, você pode ver quando devem ser adicionados índices à tabelas para obter uma SELECT mais rápida que utiliza índices para encontrar os registros.
Voce deve executar frequentemente ANALYZE TABLE para atualizar estatísticas de tabela tais como a cardinalidade das chaves que podem afetar a escolha que o otimizador faz. Veja mais informações sobre isto na Seção 4.6.2, "Sintaxe de ANALYZE TABLE".
Você também pode ver se o otimizador une as tabelas em uma melhor ordem. Para forçar o otimizador a utilizar uma ordem específica de join para uma instrução SELECT, adicione uma cláusula STRAIGHT_JOIN.
Para ligações mais complexas, EXPLAIN retorna uma linha de informação para cada tabela utilizada na instrução SELECT. As tabelas são listadas na ordem que seriam lidas. O MySQL soluciona todas as joins utilizando um método multi-join de varedura simples. Isto significa que o MySQL lê uma linha da primeira tabela, depois encontra uma linha que combina na segunda tabela, depois na terceira tabela e continua. Quando todas tabelas são processadas, ele mostra as colunas selecionadas e recua através da lista de tabelas até uma tabela na qual existem registros coincidentes for encontrada. O próximo registro é lido desta tabela e o processo continua com a próxima tabela.
No MySQL versão 4.1 a saída do EXPLAIN foi alterada para funcionar melhor com construções como UNIONs, subqueries e tabelas derivadas. A mais notável é a adição de duas novas colunas: id e select_type.
A saída de EXPLAIN inclui as seguintes colunas:
Identificador SELECT, o número sequêncial desta SELECT dentro da consulta.
Tipo de cláusula SELECT, que pode ser uma das seguintes:
SELECT simples (sem UNIONs ou subqueries).
SELECT mais externa.
Segunda SELECT e as SELECTs posteriores do UNION
Seunda SELECT e SELECTs posteriores do UNION, dependente da subquery exterior.
Primeiro SELECT na subquery.
Primeiro SELECT, dependente da subquery exterior.
SELECT de tabela derivada (subquery na cláusula FROM).
A tabela para a qual a linha de saída se refere.
O tipo de join. Os diferentes tipos de joins são listados aqui, ordenados do melhor para o pior tipo:
A tabela só tem uma linha (= tabela de sistema). Este é um caso especial do tipo de join const.
A tabela têm no máximo um registro coincidente, o qual será lido na inicialização da consulta. Como só há um registro, os valores da coluna neste registro podem ser considerados constantes pelo resto do otimizador. Tabelas const são muito rápidas e são lidas apenas uma vez!
const é usado quando você compara todas as partes de uma chave PRIMARY/UNIQUE com restrições:
Uma linha será lida desta tabela para cada combinação de linhas da tabela anterior. Este é o melhor tipo de join depois dos tipos const. É usado quando todas as partes do índice são usados pela join e o índice é é único (UNIQUE) ou uma chave primária (PRIMARY KEY).
eq_ref pode ser usado para coluna indexadas que é comparada com o\ operador =. O item comparado pode ser uma constante ou uma expressão que usa colunas de tabelas que são lidas antes desta tabela.
Nos seguintes examplos, ref_table poderá usar eq_ref
Todas as colunas com valores de índices correspondentes serão lidos desta tabela para cada combinação de registros da tabela anterior. ref é usado se o join usa apenas o prefixo mais a esquerda da chave, ou se a chave não é única (UNIQUE) ou uma chave primária (PRIMARY KEY) (em outras palavras, se a join não puder selecionar um único registro baseado no valor da chave). Se a chave que é usada coincide apenas em alguns registros, este tipo de join é bom.
ref pode ser usado para colunas indexadas que são comparadas com o operador =.
Nos seguintes exemplos, ref_table poderá usar ref
Como ref, mas com o adicional que faremos uma busca extra para linhas com NULL. Veja mais informações sobre isto na Seção 5.2.5, "Como o MySQL Otimiza IS NULL".
Esta otimização do tipo join é nova para o MySQL 4.1.1 e é mais usada na resolução de sub queries.
Apenas registros que estão numa dada faixa serão retornados, usando um índice para selecionar os registros. A coluna key indica qual índice é usado. key_len contém a maior parte da chave que foi usada. A coluna ref será NULL para este tipo.
range pode ser usado para quando uma coluna de chave é comparada a uma constante com =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN e IN.
Isto é o mesmo que ALL, exceto que apenas a árvore de índice é varrida. Isto é normalmente mais rápido que ALL, já que o arquivo de índice normalmente é menor que o arquivo de dados.
Ele pode ser usado quando a consulta só usa colunas que são parte de um índice.
Será feita uma varredura completa da tabela para cada combinação de registros da tabela anterior. Isto normalmente não é bom se a tabela é a primeiro tabela não marcada como const, e normalmente muito ruim em todos os casos ordenados. Você normalmente pode ebitar ALL adicionando mais índices, assim o registro pode ser retornado baseado em valores constantes ou valores de colunas de tabelas anteriores.
A coluna possible_keys indica quais índices o MySQL pode utilizar para encontrar os registros nesta tabela. Note que esta coluna é totalmente independente da ordem das tabelas. Isto significa que algumas das chaves em possible_keys podem não ser usadas na prática com a ordem de tabela gerada.
Se esta coluna for NULL, não existem índices relevantes. Neste caso, você poderá melhora a performance de sua query examinando a cláusula WHERE para ver se ela refere a alguma coluna ou colunas que podem ser indexadas. Se for verdade, crie um índice apropriado e confira a consulta com EXPLAIN novamente. Veja mais informações sobre isto na Seção 6.5.4, "Sintaxe ALTER TABLE".
Para ver os índices existentes em uma tabela, utilize SHOW INDEX FROM nome_tabela.
A coluna key indica a chave (índice) que o MySQL decidiu usar. A chave será NULL se nenhum índice for escolhido. Para forçar o MySQL a usar um índice listado na coluna possible_keys, use USE INDEX/IGNORE INDEX em sua consulta. Veja mais informações sobre isto na Seção 6.4.1, "Sintaxe SELECT".
Executando myisamchk --analyze (see Seção 4.5.6.1, "Sintaxe do myisamchk") ou ANALYSE TABLE (see Seção 4.6.2, "Sintaxe de ANALYZE TABLE") na tabela também ajudará o otimizador a escolher índices melhores.
A coluna key_len indica o tamanho da chave que o MySQL decidiu utilizar. O tamanho será NULL se key for NULL. Note que isto nos diz quantas partes de uma chave multi-partes o MySQL realmente está utilizando.
A coluna ref mostra quais colunas ou contantes são usadas com a key para selecionar registros da tabela.
A coluna rows informa o número de linhas que o MySQL deve examinar para executar a consulta.
Esta coluna contem informações adicionais de como o MySQL irá resolver a consulta. A seguir uma explicação das diferentes strings de texto que podem ser encontradas nesta coluna:
O MySQL não continuará a procurar por mais registros para a combinação de registro atual depois de ter encontrado o primeiro registro coincidente.
O MySQL estava apto a fazer uma otimização LEFT JOIN na consulta e não examinará mais registros nesta tabela para a combinação do registro anterior depois que encontrar um registro que satisfaça o critério do LEFT JOIN.
Assume que t2.id é definido com NOT NULL. Neste caso o MySQL irá percorrer t1 e procurar pelos registros em t2 através de t1.id. Se o MySQL encontrar um registro combinando em t2, ele sabe que t2.id nunca poderá ser NULL e não ir percorrer até o resto dos registros em t2 que possuirem o mesmo id. Em outras palavras, para cada registro em t1 o MySQL só precisa fazer uma única pesquisa em t2, independente de quantos registros coincidentes existirem em t2.
O MySQL não encontrou um bom índice para usar. No lugar, ele irá fazer uma verificação sobre qual índice usar (se existir) para cada combinação das tabelas precedentes, e usará este índice para recuperar os registros da tabela. Isto não é muito rápido mas é mais rápido que fazer um join sem um índice.
O MySQL precisará fazer uma passada extra para descobrir como recuperar os registros na ordem de classificação. A classificação é feita indo através de todos os registros de acordo com join type e armazenar a chave de ordenação mais o ponteiro para o registro para todos os registros que combinarem com o WHERE. Então as chaves são classificadas. Finalmente os registros são recuperados na ordem de classificação.
A informação da coluna é recuperada da tabela utilizando somente informações na árvore de índices sem ter que fazer uma pesquisa adicional para ler o registro atual. Isto pode ser feito quando todas as colunas usadas para a tabela fizerem parte do mesmo índice.
Para resolver a consulta, o MySQL precisará criar uma tabela temporária para armazenar o resultado. Isto acontece normalmente se você fizer um ORDER BY em um conjunto de colunas diferentes das quais você fez um GROUP BY.
Uma cláusula WHERE será utilizada para restringir quais registros serão combinados com a próxima tabela ou enviar para o cliente. se você não possui esta informação e a tabela é do tipo ALL ou index, pode existir alguma coisa errada na sua query (Se você não pretender examinar todos os registros da tabela).
Se você desejar deixar suas consultas o mais rápido possível, você deve dar uma olhada em Using filesort e Using temporary.
Você pode ter uma boa indicação de quão boa é sua join multiplicando todos os valores na coluna rows na saída de EXPLAIN. Isto deve dizer a grosso modo quantos registros o MySQL deve examinar para executar a consulta. Este número é também usado quando você restringe consultas com a variável max_join_size. Veja mais informações sobre isto na Seção 5.5.2, "Parâmetros de Sintonia do Servidor".
O exemplo a seguir mostra como um JOIN pode ser otimizado progressivamente utilizando a informação fornecida por EXPLAIN.
Suponha que você tem a instrução SELECT exibida abaixo, que você está examinando utilizando EXPLAIN:
Para este exemplo, assuma que:
As colunas comparadas foram declaradas como a seguir:
Tabela Coluna Tipo da coluna
As tabelas possuem os índices mostrados abaixo:
Tabela Índice
et EMPLOYID (chave primária)
do CUSTNMBR (chave primária)
Como o tipo é ALL em todas tabelas, esta saída indica que o MySQL está gerando um produto Cartesiano de todas as tabelas! Isto levará muito tempo para ser executado, pois o produto do número de registros em cada tabela deve ser examinado ! Neste caso, existem 74 * 2135 * 74 * 3872 registros. Se as tabelas forem maiores, imagine quanto tempo este tipo de consulta pode demorar.
Um dos problemas aqui é que o MySQL não pode (ainda) utilizar índices em colunas de maneira eficiente se elas foram declaras ide forma diferente. Neste contexto, VARCHAR e CHAR são o mesmo a menos que tenham sido declarados com tamanhos diferentes. Como tt.ActualPC é declarado como CHAR(10) e et.EMPLOYID é declarado como CHAR(15), existe aqui uma diferença de tamanho.
Para corrigir esta diferença entre tamanhos de registros, utilize ALTER TABLE para alterar o tamanho de ActualPC de 10 para 15 caracteres:
Agora ambos campos tt.ActualPC e et.EMPLOYID são VARCHAR(15). Executando a instrução EXPLAIN novamente produzirá este resultado:
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 Using where
Isto não está perfeito, mas está bem melhor ( o produto dos valores de rows agora menor por um fator de 74 ). Esta versão é executada em vários segundos.
Uma segunda alteração pode ser feita para eliminar as diferenças de tamanho das colunas para as comparações tt.AssignedPC = et_1.EMPLOYID e tt.ClientID = do.CUSTNMBR :
Agora EXPLAIN produz a saída mostrada abaixo:
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using where
Este resultado é quase o melhor que se pode obter.
O problema restante é que, por padrão, o MySQL assume que valores na coluna tt.ActualPC estão distribuídos igualmente, e este não é o caso para a tabela tt. Felizmente, é fácil informar ao MySQL sobre isto:
Agora a join está perfeita, e EXPLAIN produz esta saída:
tt ALL AssignedPC NULL NULL NULL 3872 Using where
Perceba que a coluna rows na saída de EXPLAIN é uma boa ajuda para otimizador de joins do MySQL. Para otimizar uma consulta, você deve conferir se os números estão perto da realidade. Se não, você pode obter melhor desempenho utilizando STRAIGHT_JOIN em sua instrução SELECT e tentar listar as tabelas em uma ordem diferente na cláusula FROM.
Na maioria dos casos você pode estimar a performance contando buscas em disco. Para tabelas pequenas, normalmente você pode encontrar o registro com 1 pesquisa em disco (uma vez que o índice provavelmente está no cache). Par tabelas maiores, você pode estimar (usando indíces de arvores B++) que você precisará de: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 buscas em disco para encontrar um registro.
No MySQL um bloco de índice tem geralmente 1024 bytes e o ponteiro de dados 4 bytes. Uma tabela de 500.000 registros com um índice com tamanho de 3 (inteiro médio) lhe dá: log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 pesquisas.
Como o índice acima necessita cerca de 500,000 * 7 * 3/2 = 5.2M, (assumindo que os buffers de índices são carregados até 2/3, que é o normal) você provavelmente terá grande parte dos índices em memória e provavelmente precisará somente de 1 ou 2 chamadas para ler dados do SO para encontrar o registro.
Entretanto, para escritas, você precisará utilizar 4 requisições para encontrar onde posicionar o novo índice e normalmente 2 buscas para atualizar o índice e escrever o registro.
Perceba que o que foi dito acima não significa que sua aplicação perderá performance por N log N! Como tudo é armazenado no cache de seu SO ou do servidor SQL as coisas começarão a ficar um pouco mais lentas quando as tabelas começarem a crescer. Quando os dados se tornam muito grandes para o cache, as coisas começarão a ficar bem mais lentas até que suas aplicações estejam limitadas a buscas em disco (o que aumenta em N log N). Para evitar isto, aumente o cache de índice quando os dados crescerem. Veja mais informações sobre isto na Seção 5.5.2, "Parâmetros de Sintonia do Servidor".
Em geral, quando você desejar tornar uma consulta lenta SELECT ... WHERE mais rápida, a primeira coisa que deve ser conferida é se você pode ou não adicionar um índice. Veja mais informações sobre isto na Seção 5.4.3, "Como o MySQL Utiliza Índices". Todas as referências entre diferentes tabelas devem ser feitas normalmente com índices. Você pode utilizar o comando EXPLAIN para determinas quais índices são usados para uma SELECT. Veja mais informações sobre isto na Seção 5.2.1, "Sintaxe de EXPLAIN (Obter informações sobre uma SELECT)".
Para ajudar o MySQL a otimizar melhor as consultas, execute myisamchk --analyze em uma tabela depois dela ter sido carregada com dados relevantes. Isto atualiza um valor para cada parte do índice que indica o número médio de registros que tem o mesmo valor. (Para índices únicos, isto é sempre 1, é claro). O MySQL usará isto para decidir qual índice escolher quando você conectar duas tabelas utilizando uma 'expressão não constante'. Os resultados de analyze podem ser conferidos utilizando SHOW INDEX FROM nome_tabela e examindo a coluna Cardinality.
Para ordenar um índice e dados de acordo com um índice, utilize myisamchk --sort-index --sort-records=1 (se você deseja ordenar pelo índice 1). Se você possui um índice unico no qual deseja ler todos registros na ordem do índice, esta é uma boa forma para torná-lo mais rápido. Perceba entretanto, que esta ordenação não foi escrita de maneira otimizada e levará muito tempo em tabelas grandes!
As otimizações WHERE são colocadas aqui na parte da SELECT porque normalmente elas são usadas com SELECT, mas as mesmas otimizações aplicam-se para WHERE em instruções DELETE e UPDATE.
Note também que esta seção está incompleta. O MySQL faz várias otimizações e ainda não tivemos tempo para documentarmos todas elas.
Algumas das otimizações feitas pelo MySQL são são listadas abaixo:
Remoção de parênteses desnecessários:
Enlaços de constantes:
Remoção de condições contantes (necessário por causa dos enlaços de contantes):
Expressões constantes utilizadas por índices são avaliadas somente uma vez.
COUNT(*) em uma única tabela sem um WHERE é recuperado diretamente da informação da tabela dos tipos MyISAM e HEAP. Isto também é feito para qualquer expressão NOT NULL quando usada somente com uma tabela.
Pré detecção de expressões contantes inválidas. O MySQL detecta rapidamente que algumas instruções SELECT são impossíveis e não retornará registros.
HAVING é fundido com WHERE se não for utilizado GROUP BY ou funções de agrupamento (COUNT(), MIN()...).
Para cada sub-join, um WHERE mais simples é construído para obter uma avaliação mais rápida de WHERE para cada sub-join e também para saltar registros da maneira mais rápida possível.
Todas tabelas constantes são lidas primeiro, antes de qualquer tabelas na consulta. Uma tabela constante é:
Uma tabela vazia ou uma tabela com 1 registro.
Uma tabela que é usada com uma cláusula WHERE em um índice UNIQUE, ou uma PRIMARY KEY, onde todas as partes do índice são usadas com expressões constantes e as partes do índice são definidas como NOT NULL.
Todas as tabelas seguintes são usadas como tabelas constantes:
-> WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
A melhor combinação de join para unir as tabelas é encontrada tentando todas as possibilidades. Se todas colunas em ORDER BY e em GROUP BY vierem da mesma tabela, então esta tabela será preferencialmente a primeira na união.
Se existerem uma cláusula ORDER BY e uma GROUP BY diferente, ou se a ORDER BY ou GROUP BY conterem colunas de tabelas diferentes da primeira tabela na fila de join, uma tabela temporária será criada.
Se você utilizar SQL_SMALL_RESULT, o MySQL usará a tabela temporária em memória.
Cada índice de tabela é consultado e o melhor índice que cobrir menos de 30% dos registros é usado. Se nenhum índice for encontrado, uma varredura rápida é feita pela tabela.
Em alguns casos, o MySQL pode ler registros do índice mesmo sem consultar o arquivo de dados. Se todas colunas usadas do índice são numéricas, então somente a árvore de índice é usada para resolver a consulta.
Antes de dar saída em cada registro, aqueles que não combinam com a cláusula HAVING são ignorados.
-> WHERE key_part_1=constant;
-> ORDER BY key_part1,key_part2,... LIMIT 10;
-> ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;
As seguintes consultas são resolvidas utilizando somente a árvore de índices (assumindo que as colunas indexadas são numéricas):
-> WHERE key_part1=val1 AND key_part2=val2;
As consultas a seguir utilizam indexação para recuperar os registros na ordem de classificação sem um passo de ordenação separado:
-> ORDER BY key_part1,key_part2,... ;
-> ORDER BY key_part1 DESC,key_part2 DESC,... ;
O MySQL pode fazer a mesma otimização em column IS NULL que ele pode com column = constant_value. Por exemplos, o MySQL pode usar índices e faixas para buscar por NULL com IS NULL.
Se você usa column_name IS NULL em um NOT NULL em uma cláusula WHERE na tabela que não é usada no OUTER JOIN, esta espressão será otimizada de qualquer forma.
O MySQL 4.1. pode adicionalmente otimizar a combinação column = expr AND column IS NULL, uma forma que é comum em sub queries resolvidas. EXPLAIN mostrará ref_or_null quando esta otimização é usada.
Esta otimização pode tratar um IS NULL para qualquer parte da chave.
Alguns exemplos de consultas que são otimizadas (assumindo chave em t2 (a,b)):
ref_or_null funciona fazendo primeiro uma leitura na chave indicada e depois disto uma busca separada por linhas com chave NULL.
Note que a otimização só pode tratar um nível IS NULL.
No caso acima o MySQL só usará busca de chave na parte (t1.a=t2.a AND t2.a IS NULL) e não poderá usar a parte da chave em b.
DISTINCT combinado com ORDER BY também irá em vários casos criar uma tabela temporária.
Note que como DISTINCT pode usar GROUP BY, você deve estar ciente de como o MySQL funciona com campos na parte ORDER BY ou HAVING que não são parte dos campos selecionados. Veja mais informações sobre isto na Seção 6.3.7.3, "GROUP BY com Campos Escondidos".
Quando combinando LIMIT row_count com DISTINCT, o MySQL irá parar logo que encontrar row_count registros únicos.
Se você não utiliza colunas de todas tabelas usadas, o MySQL irá parar a varredura das tabelas não usadas logo que encontrar a primeira coincidência.
Neste caso, assumindo que t1 é usando antes de t2 (confira com EXPLAIN), MySQL irá parar de ler de t2 (para aquele registro particular em t1) quandoo primeiro registro em t2 for encontrado.
A LEFT JOIN B join_condition no MySQL está implementada como a seguir:
A tabela B é configurada para ser dependente da tabela A e de todas as tabelas das quais A depende.
A tabela A é configurada para ser dependente de todas as tabelas (exceto B) que são usadas na condição LEFT JOIN.
A condição LEFT JOIN é usada para decidir como devemos recuperar registros a partir da tabela B. (Em outras palavras, qualquer condição na claúsula WHERE não é usada).
Todas as otimizações padrões de join são feitas, com a excessão que uma tabela é sempre lida depois de todas as tabelas das quais é dependente. Se existir uma dependência circular o MySQL irá emitir um erro.
Todas as otimizações padrões de WHERE são realizadas.
Se existir um registro em A que coincida com a cláusula WHERE, mas não existir nenhum registro em B que coincida com a condição ON então um registro extra em B é gerado com todas as colunas com valor NULL.
Se você utiliza LEFT JOIN para encontrar registros que não existem em alguma tabela e está usando o seguinte teste: nome_coluna IS NULL na parte WHERE, onde nome_colun é um campo que é declarado como NOT NULL, então o MySQL para de pesquisar por mais registros (para uma combinação particular de chaves) depois de ter encontrado um registro que combinar com a condição LEFT JOIN.
RIGHT JOIN é implementado de forma análoga à LEFT JOIN.
A ordem de leitura das tabelas forçada por LEFT JOIN e STRAIGHT JOIN irá ajudar o otimizador de joins (que calcula em qual ordem as tabelas devem ser unidas) a fazer seu trabalho mais rapidamente, já que haverão poucas permutações de tabelas a serem conferidas.
Perceba que o texto acima significa que se você fizer uma consulta do tipo:
A partir do MySQL 4.0.14, o MySQL faz a seguinte otimização LEFT JOIN:
Se a condição WHERE é sempre falsa para a linha NULL gerada, o LEFT JOIN é alterado para um join normal.
Por exemplo, na seguinte consulta a cláusula WHERE seria falso se t2.coluna fosse NULL, asssim é seguro converter para uma join normal.
Isto pode ser feito mais rápido já que o MySQL pode agora usar a tabela t2 antes da tabela t1 se resultasse consulta melhor. Para forçar uma ordem de tabela específica, use STRAIGHT JOIN.
O MySQL irá fazer uma pesquisa completa em b já que o LEFT JOIN irá força-lo a ser lido antes de d.
A correção neste caso é alterar a consulta para:
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 WHERE key_part1=constante ORDER BY key_part2
SELECT * FROM t1 WHERE key_part1=constante GROUP BY key_part2
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:
Você está fazendo um ORDER BY usando partes de chaves não consecutivas.
Você está misturando ASC e DESC.
As chaves usadas para buscar os registros são as mesmas usadas para fazer o ORDER BY:
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. Veja mais informações sobre isto na Seçã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:
Em alguns casos o MySQL irá tratar a consulta de maneira diferente quando você estiver utilizando LIMIT row_count e não estiver utilizando HAVING:
Se você estiver selecionando apenas alguns registros com LIMIT, o MySQL usará índices em alguns casos quando ele normalmente preferiria fazer uma varredura completa na tabela.
Se você utilizar LIMIT row_count com ORDER BY, O MySQL irá terminar a ordenação logo que ele encontrar os primeiros row_count registros em vez de ordenar a tabela inteira.
Ao combinar LIMIT row_count com DISTINCT, o MySQL irá parar logo que ele encontrar row_count registros únicos.
Em alguns casos um GROUP BY pode ser resolvido lendo a chave em ordem (ou fazer uma classificação na chave) e então calcular resumos até o valor da chave alterar. Neste caso, LIMIT row_count não irá calcular nenhum GROUP BY desnecessário.
Logo que o MySQL enviar os primeiros # registros para o cliente, ele irá abortar a consulta.
LIMIT 0 irá sempre retornar rapidamente um conjunto vazio. Isto é util para conferir a consulta e obter os tipos de campos do resultado.
Quando o servidor utiliza tabelas temporárias para resolver a consulta, o LIMIT row_count é usado para calcular a quantidade de espaço necessário.
O tempo para inserir um registro consiste aproximadamente de:
Conexão: (3)
Enviar a consulta para o servidor: (2)
Analisar a consulta (2)
Inserir o registro: (1 x tamanho do registro)
Inserir os índices: (1 x número de índices)
Fechar: (1)
onde os números são de certa forma proporcionais ao tempo total. Isto não leva em consideracão o sobrecarga inicial para abrir tabelas (que é feita uma vez para cada consulta concorrente em execução).
O tamanho da tabela diminuem a velocidade da inserção de índices em N log N (Arvores B).
Algumas maneiras de acelerar as inserções:
Se você estiver inserindo vários registros do mesmo cliente ao mesmo tempo, utilize instruções INSERT com listas de múltiplos valores. Isto é muito mais rápido (muitas vezes em alguns casos) do que utilizar instruções INSERT separadas. Se você esta adicionando dados a uma tabela que não está vazia, você pode ajustar a variável bulk_insert_buffer_size para tornár isto mais rápido. Veja mais informações sobre isto na Seção 4.6.8.4, "SHOW VARIABLES".
Se você inserir vários registros de diferentes clientes, você pode obter velocidades mais altas utilizando a instrução INSERT DELAYED. Veja mais informações sobre isto na Seção 6.4.3, "Sintaxe INSERT".
Perceba que com MyISAM você pode inserir registros ao mesmo tempo que SELECTs estejam executando se não existirem registros apagados nas tabelas.
Ao carregar uma tabela de um arquivo texto, utilize LOAD DATA INFILE. Isto é normalmente 20 vezes mais rápido do que utilizar várias instruções INSERT Veja mais informações sobre isto na Seção 6.4.8, "Sintaxe LOAD DATA INFILE".
É possível com algum trabalho extra fazer o LOAD DATA INFILE executar ainda mais rápido quando a tabela tiver vários índices. Utilize o seguinte procedimento:
Opcionalmente crie a tabela com CREATE TABLE. Por exemplo, utilizando mysql ou Perl-DBI.
Execute a instrução FLUSH TABLES ou o comando shell mysqladmin flush-tables.
Utilize myisamchk --keys-used=0 -rq /path/to/db/nome_tabela. Isto removerá o uso de todos os índices da tabela.
Insira dados na tabela com LOAD DATA INFILE. Isto não atualizará índices e será muito mais rápido.
Se no futuro você precisar da tabela somente para leitura, execute myisampack na mesma para torná-la menor. Veja mais informações sobre isto na Seção 7.1.2.3, "Características de Tabelas Compactadas".
Recrie os índices com myisamchk -r -q /caminho/para/bd/nome_tabela. Isto criará a árvore de índices em memória antes de escrevê-la para o disco, que é muito mais rápido porque evita que seja feita muita busca disco. A árvore de índices resultante é também balanceada perfeitamente.
Execute uma instrução FLUSH TABLES ou o comando shell mysqladmin flush-tables.
Note que LOAD DATA INFILE també faz a otimização acima se você a inserção for em uma tabela vazia; a principal diferença com o procedimento acima é qeu você pode deixar o myisamchk alocar muita mais memória temporária para a criação do índice que você deseje que o MySQL alocasse para todas as recriações de índice.
Desde o MySQL 4.0 você também pode usar ALTER TABLE nome_tbl DISABLE KEYS em vez de myisamchk --keys-used=0 -rq /caminho/para/bd/nome_tbl e ALTER TABLE nome_tbl ENABLE KEYS em vez de myisamchk -r -q /caminho/para/bd/nome_tbl. Deste modo você também pode saltar os passos FLUSH TABLES.
Você pode acelerar inserções feitas usando várias instruções bloqueando suas tabelas:
A principal diferença na velocidade é que o buffer de índices é descarregado no disco somente uma vez, depois de todas instruções INSERT term sido completadas. Normalmente existiria tantas descargas do buffer de índices quanto instruções INSERT diferentes. O bloqueio não é necessário se você pode inserir todos registros com uma simples instrução.
Para tabelas transacionais, você deve usar BEGIN/COMMIT em vez de LOCK TABLES para conseguir um aumento na velocidade.
O bloqueio irá também diminuir o tempo total de testes de multi-conexões, mas o tempo máximo de espera para algumas threads irá aumentar (porque eles esperam pelos bloqueios). Por exemplo:
thread 1 faz 1000 inserções
thread 2, 3 e 4 faz 1 inserção
thread 5 faz 1000 inserções
Se você não estiver usando travas, 2, 3 e 4 irão terminar antes de 1 e 5, Se estiver utilizando travas, 2, 3 e 4 provavelmente não irão terminar antes de 1 ou 5, mas o tempo total deve ser cerca de 40% mais rápido.
Como as operações INSERT, UPDATE e DELETE são muito rápidas no MySQL, você obterá melhor perfomance geral adicionando travas em tudo que fizer mais que cerca de 5 inserções ou atualizações em um registro. Se você fizer várias inserções em um registro, você pode utilizar LOCK TABLES seguido de um UNLOCK TABLES de vez em quando (em torno de 1000 registro) para permitr que outras threads acessem a tabela. Isto também continua mostrando um bom ganho de performance.
Com certeza, LOAD DATA INFILE é muito mais rápido para carregar dados.
Para obter mais velocidade para LOAD DATA INFILE e INSERT, aumente o tamanho do buffer de chaves. Veja mais informações sobre isto na Seção 5.5.2, "Parâmetros de Sintonia do Servidor".
Consultas de atualização são otimizadas como uma consulta que usa SELECT com a sobrecarga adicional de escrita. A velocida da escrita depende do tamanho dos dados e do número de índices que serão atualizados. Índices que não forem alterados não serão atualizados.
Outra forma para obter atualizações rápidas é atrasar as atualizações e então fazer várias atualizações em um registro posteriormente. Fazer várias atualizações em um registro é muito mais rápido do que fazer uma por vez se você travar a tabela.
Perceba que, com formato de registros dinâmicos, atualizar um registro para um valor maior que o tamanho total pode dividir o registro. Portanto, se você faz isso frequentemente, é muito importante usar OPTIMZE TABLE de vez em quando. Veja mais informações sobre isto na Seção 4.6.1, "Sintaxe de OPTIMIZE TABLE".
Se você deseja apagar todos os registros em uma tabela, deve usar TRUNCATE TABLE nome_tabela. Veja mais informações sobre isto na Seção 6.4.6, "Sintaxe TRUNCATE".
O tempo para apagar um registro é exatamente proporcional ao número de índices. Para apagar registros mais rapidamente, você pode aumentar o tamanho do cache de índices. Veja mais informações sobre isto na Seção 5.5.2, "Parâmetros de Sintonia do Servidor".
Dicas não ordenadas para sistemas rápidos:
Utilize conexões persistentes aos banco de dados para evitar a sobrecarga da conexão. Se você não poder utilizar conexões persistentes e for fazer várias novas conexões para o banco de dados, você pode desejar alterar o valor da variável thread_cache_size. Veja mais informações sobre isto na Seção 5.5.2, "Parâmetros de Sintonia do Servidor".
Sempre verifique se todas as suas consultas realmente utilizam os índices que foram criados nas tabelas. No MySQL você pode fazer isto com o comando EXPLAIN. See Explain: (manual) Explain.
Tente evitar consultas SELECT complexas em tabelas que são muito atualizadas. Isto evita problemas com travamento de tabelas.
Com tabelas MyISAM que não tenham linhas deletadas, você pode inserir registros ao mesmo tempo que outra tabela a estiver lendo. Se este recurso é importante para você, deve considerar métodos onde você não tem que apagar registrou ou executar OPTIMIZE TABLE depois de ter apagado vários registros.
Utilize ALTER TABLE ... ORDER BY expr1,expr2... se você na maioria das vezes recupera registros na ordem expr1,expr2... Utilizando esta opção depois de grandes alterações para a tabela, pode lhe dar um ganho de performance.
Em alguns casos pode fazer sentido introduzir uma coluna 'hash' baseada nas informações das outras colunas. Se esta coluna for curta e razoavelmente única pode ser muito mais rápido do que ter um grande índice em várias colunas. No MySQL é muito fácil usar esta coluna extra: SELECT * FROM nome_tabela WHERE hash=MD5(concat(col1,col2)) AND col_1='constante' AND col_2='constante'
Para tabelas que alteram muito você deve tentar evitar todas colunas VARCHAR ou BLOB. Você terá tamanho de registro dinâmico assim que usar um simples campo VARCHAR ou BLOB. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL.
Normalmente não é muito útil cortar uma tabela em diferentes tabelas apenas porque os registros estão 'grandes'. Para acessar um registro, o maior problema para a performance é a busca em disco para encontra o primeiro byte do registro. Depois de encontrar os dados a maioria dos novos discos podem ler o registro inteiro rápido o bastante para a maioria das aplicações. Os únicos caos onde realmente faz sentido dividir uma tabela é se ela é uma tabela de registros com tamanho dinâmico (veja acima) que você pode alterar para um tamanho fixo, ou se você frequentemente precisa examinar a tabela e não precisa da maioria das colunas. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL.
Se frequentemente você precisar calcular alguma coisa baseada em informação de vários registros (ex: contagem de registros), provavlmente é melhor introduzir uma nova tabela e atualizar o contador em tempo real. Uma atualização do tipo UPDATE table set count=count+1 where index_column=constante é muito rapida!
Isto é realmente importante quando você usa bancos de dados como o MySQL que só tem travamento de tabelas (multiplos leituras/escrita única). Isto também dará melhor performance com a maioria dos banco de dados, já que o gerenciador de bloqueio de registro terá menos a fazer neste caso.
Se você precisar colerar estatisicas de tabelas maiores, utilize tabelas resumo em vez de buscar em toda a tabela. Manter os resumos deve ser mais rápido que tentar criar estatitíscas instantaneamente. É muito mais rápido criar novas tabelas através dos logs quando as coisas mudam (dependendo das descisões de negócio) que ter que alterar a aplicação em execução.
Se possível, deve-se classificar relatórios como 'instantâneo' ou 'estatísticos' onde os dados necessários para relatórios estaiísticos são gerados apenas com base nas tabelas resumo que são geradas a partir dos dados atuais.
Tire vantagem do fato de que a coluna tem valores padrões. Insira valores explicitamente apenas quando os valores a serem inseridos diferem do padrão. Isto reduz a analise que o MySQL precisa fazer e aumenta a velocidade de inserção.
Em alguns casos é conveniente empacotar e armazenar os dados em um campo blob. Neste caso você deve adicionar algum código em sua aplicação para empacotar/desempacotar as coisas no campo blob, mas isto pode poupar vários acessos a algum estágio. Isto é prático quando você possui dados que não conformam com uma estrutura estática de tabela.
Normalmente, você deve tentar manter todos dados não-redundantes (o que é chamado de 3a forma normal na teoria de bancos de dados), mas você não deve ter medo de duplicar alguns itens ou criar tabelas de resumo se você precisar delas para ganhar mais velocidade.
Stored Procedures ou UDF (funções definidas pelo utilizadores) pode ser uma boa forma para obter mais performance. Neste caso você deve, entretanto, sempre ter uma maneira de fazer isso de outra maneira (mais lenta) se você utilizar algum banco de dados que não suporta isto.
Você sempr pode ganhar velocidade fazendo cache de perguntas/respostas na sua aplicação e tentando fazer várias inserções/atualizações ao mesmo tempo. Se seu banco de dados suporta travamento de tabelas (como o MySQL e Oracle), isto deve ajudar a garantir que o cache de índices é descarregado somente uma vez depois de todas atualizações.
Use INSERT /*! DELAYED */ quando não precisar saber quando os dados são gravados. Isto melhora a velocidade porque vários registros podem ser gravados com uma simples escrita em disco.
Use INSERT /*! LOW_PRIORITY */ quando você desejar que suas consultas sejam mais importantes.
Use SELECT /*! HIGH_PRIORITY */ para obter consultas que ignoram a fila. Isto é, a consulta é feita mesmo se alguem estiver esperando para fazer uma escrita.
Use a instrução INSERT multi-linhas para armazenar vários registros com um comando SQL (vários servidores SQL suportam isto).
Use LOAD DATA INFILE para carregar volumes maiores de dados. Isto é mais rápido que as inserções normais e mais rápido até quando o myisamchk for integrado no mysqld.
Use colunas AUTO_INCREMENT para garantir valores únicos.
Use OPTIMIZE TABLE de vez em quando para evitar fragmentação quando estiver usando formatos de tabela dinâmica. Veja mais informações sobre isto na Seção 4.6.1, "Sintaxe de OPTIMIZE TABLE".
Use tabelas HEAP para obter mais velocidade sempre que possível. Veja mais informações sobre isto na Capítulo 7, Tipos de Tabela do MySQL.
Quando estiver usando uma configuração de servidor Web normal, imagens devem ser armazenadas como arquivos. Isto é, armazene apenas uma referência para o arquivo no banco de dados. A principal razão para isto é que um servidor Web normal é muito melhor trabalhando com cache de arquivos do que com conteúdo de banco de dados. Portanto será muito mais fácil obter um sistema rápido se você utilizar arquivos.
Use tabelas em memória para dados não-críticos que são acessados frequentemente (como informações sobre o último banner visto para utilizadores que não possuem cookies).
Colunas com informações identicas em diferentes tabelas devem ser declaradas idênticas e ter nomes idênticos. No entanto, antes da versão 3.23, você pode obter ligações mais lentas.
Tente manter os nomes mais simples (use nome em vez de nome_cliente na tabela cliente). Para deixar seus nomes portáveis para outros servidores SQL você deve mantê-los menores que 18 caracteres.
Se você realmente precisa de alta velocidade, você deve verificar as interfaces de baixo nível para armazenagem de dados que os diferentes servidores SQL suportam! Por exemplo, para acessar tabelas MySQL MyISAM diretamente, você pode obter um aumento de velocidade de 2-5 vezes comparado ao uso da interface SQL. Para conseguir essa façanha, os dados devem estar no mesmo servidor que sua aplicação, e normalmente devem ser acessados por apenas um processo (porque travamento de arquivos externo são muito lentos). Os problemas acima podem ser eliminados introduzindo comandos MyISAM de baixo nível no servidor MySQL (isto pode ser a maneira mais fácil para aumentar a performance). Tenha cuidado em projetar a interface com o banco de dados, ela deve ser bem facil para suportar estes tipos de otimizações.
Em vários casos é mais rápido acessar dados de um banco de dados (utilizando uma conexão ativa) do que acessar um arquivo texto, apenas pelo fato do banco de dados ser mais compacto do que o arquivo texto (se você estiver utilizando dados numéricos), e isto irá envolver menos acessos à disco. Você também irá poupar código porque não será necessário analisar seus arquivos texto para encontrar limites de registros e campos.
Você pode também usar replicação para conseguir ainda mais performance nas suas aplicações. Veja mais informações sobre isto na Seção 4.11, "Replicação no MySQL".
Declarando uma tabela com DELAY_KEY_WRITE=1 irá tornar a atualização de índices mais rápida, pois as mesmas não serão escritas em disco até o arquivo ser fechado. O lado ruim é que você deve executar myisamchk nestas tabelas antes de iniciar o mysqld para garantir que os dados estão corretos se o mysqld for finalizado no meio da execução. Como a informação de chave pode sempre ser gerada a partir dos dados, você não deve perder nada usando DELAY_KEY_WRITE.