Leia o artigo anterior:

Otimização de Consultas MySQL – Parte 01

*

Fala, rapaziada!!! Conforme prometido, falaremos desta vez do Otimizador de Consultas do MySQL. Para quem ainda não leu a primeira parte do artigo, clique aqui.

O Otimizador de Consultas MySQL

Quando executamos uma consulta que seleciona registros, o MySQL a
analisa a fim de verificar se uma otimização pode ser feita para
processar a consulta mais rapidamente. O Otimizador tira proveito de índices, obviamente, mas outras
informações também são usadas. Por exemplo, se executamos a consulta
seguinte, o MySQL a executará muito rápido, não importando o quão
grande a tabela seja:

SELECT * FROM tabela WHERE 0;

Neste caso, o MySQL verifica a cláusula WHERE, conclui que não há
possibilidade de um registro combinar e não se preocupa em procurar na
tabela. Podemos verificar isso pelo uso do comando EXPLAIN, que mostra informações sobre como a consulta será executada.

mysql> explain select * from animal where 0;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                   |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE     | NULL  | NULL | NULL           | NULL |      NULL | NULL | NULL | Impossible WHERE|
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.11 sec)

Normalmente, o EXPLAIN retornará mais informações do que isto,
incluindo informação de índices que estão sendo usados para pesquisar a
tabela, tipos de joins que serão usados e estimativas de número de
registros necessitarão ser examinados em cada tabela.

Como o Otimizador funciona

O Otimizador tem muitos objetivos, mas seus alvos preliminares são os usos de índices possíveis e o uso do índice mais restritivo
para eliminar o máximo número de registros na pesquisa. Depois disso, o
objetivo na execução do comando SELECT é encontrar registros, não
rejeitá-los. O exemplo mostra uma consulta que testa duas colunas, cada
uma com um índice:

SELECT col3 FROM tabela
WHERE col1 = 'algum valor' AND col2 = 'algum outro valor';

Suponhamos também que o teste na coluna col1 combine 900 registros, o
teste da col2 combine 300 registros e, juntas, combinem 30 registros. Se
pesquisarmos pela col1, 870 registros serão rejeitados. Se pesquisarmos
pela col2, 270 registros serão rejeitados e menos processamento de
disco será necessário. Como resultado, o otimizador testará a col2
primeiro.

Podemos auxiliar o otimizador a tirar proveito dos índices usando as seguintes diretrizes:

• Comparar colunas que tenham o mesmo tipo de
  dado. Quando usamos colunas indexadas em comparações, devemos usar
  colunas de mesmo tipo. Tipos de dados idênticos trazem melhor ganho de
  performance do que tipos similares. Por exemplo, INT é diferente de
  BIGINT. CHAR(10) é considerado o mesmo que CHAR(10) ou VARCHAR(10), mas
  diferente de CHAR(12) ou VARCHAR(12). Se as colunas que estamos
  comparando tiverem tipos diferentes, podemos usar ALTER TABLE para
  modificar um deles para que os tipos combinem.
• Mantenha colunas separadas em comparações. Se usamos uma coluna numa
  chamada de função ou como parte de um termo mais complexo na expressão
  aritmética, o MySQL não pode usar índices porque ele deve calcular o
  valor da expressão para cada registro. Algumas vezes, isto é
  inevitável, mas muitas vezes podemos reescrever a consulta para tornar
  a coluna separada. A cláusula WHERE seguinte ilustra como isso
  funciona. Elas são equivalentes aritmeticamente, mas muito diferentes
  em termos de otimização:

WHERE col < 4 / 2; (recomendado)
WHERE col * 2 < 4;

• Não use o caracter de porcentagem (%) no início de um padrão LIKE.
• Auxilie o otimizador a fazer melhores estimativas de efetividade dos
  índices. Por padrão, quando comparamos valores em colunas indexadas com
  uma constante, o otimizador supõe que os valores chaves estão
  distribuídos uniformemente dentro do índice. O otimizador também fará uma
  rápida checagem do índice para estimar quantas entradas serão usadas
  para determinar se o índice será usado para comparações constantes.
  Para tipos de tabelas MyISAM, InnoDB e BDB, podemos fazer o servidor
  executar uma análise dos valores chaves usando ANALYZE TABLE. Isto traz
  ao otimizador melhores informações.
• Use EXPLAIN para verificar a operação do otimizador. O comando
  EXPLAIN nos mostra se os índices estão sendo usados. Esta informação é
  muito útil quando tentamos diferentes maneiras de escrever um comando
  ou checamos se a adição de índices fará a diferença na eficiência da
  execução da consulta.
• Dê dicas ao otimizador. Normalmente, o otimizador se considera livre
  para determinar a ordem na qual as tabelas serão percorridas para
  retornar os dados mais rapidamente. Na ocasião, o otimizador nem sempre
  fará a melhor escolha. Se observarmos que isto está acontecendo,
  podemos forçar a escolha do otimizador usando a palavra chave
  STRAIGHT_JOIN. Isto força que as tabelas serão ligadas na ordem chamada
  na cláusula FROM. Também podemos usar FORCE INDEX, USE NDEX ou IGNORE
  INDEX para mostrar quais índices preferir.
• Tire proveito de áreas que o otimizador é mais completo. O MySQL pode
  realizar joins e subconsultas, mas o suporte a subconsultas é mais
  recente. Conseqüentemente, o otimizador está mais ajustado para joins
  do que subconsultas, em alguns casos.
• Teste formas alternativas de consultas, mas as teste mais de uma vez.
  Quando testamos formas alternativas de consultas, devemos testar
  diversas vezes cada forma. Se testarmos duas alternativas somente uma
  vez cada, freqüentemente a segunda forma será mais eficiente pois as
  informações da primeira consulta ainda estão no cache e não é
  necessário uma nova leitura em disco. Também há influência do uso do
  servidor naquele determinado momento.
• Evite a sobrecarga do uso de auto-conversão de tipos. O MySQL executa
  automaticamente a conversão de tipos (casting), mas se podemos evitar
  conversões, melhoramos a performance. Por exemplo, se a coluna col_num
  é tipo INT, cada uma das consultas abaixo retornará o mesmo resultado,
  mas a segunda consulta envolve conversão automática. Assim, a primeira
  consulta é mais eficiente.

SELECT * FROM tabela WHERE col_num = 4;
SELECT * FROM tabela WHERE col_num = '4';

Usando EXPLAIN para checar a operação do Otimizador

O comando EXPLAIN para observar os planos de execução gerados pelo
otimizador para processar blocos de comandos. Mostraremos dois usos
para o EXPLAIN:

• Verificar se a escrita da consulta de diferentes formas afeta se um índice está sendo usado ou não.
• Verificar os efeitos que a adição de índices numa tabela tem na
  habilidade do otimizador para gerar eficientes planos de execução.

Usaremos como exemplo três consultas que retornam o mesmo resultado para checar a operação do otimizador.

WHERE TO_DAYS(coluna_data) - TO_DAYS(CURDATE()) < cutoff;
WHERE TO_DAYS(coluna_data) < cutoff - TO_DAYS(CURDATE());
WHERE coluna_data < DATE_ADD(CURDATE(), INTERVAL cutoff DAY);

EXPLAIN permite checar se uma forma de escrita numa expressão é melhor
do que outra. Para ver isso, usaremos EXPLAIN em cada cláusula WHERE.
Consideraremos que a coluna_data tem um índice.

mysql> EXPLAIN SELECT * FROM tabela
        -> WHERE TO_DAYS(coluna_data) - TO_DAYS(CURDATE()) < 30;
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: MEMBER
                 type: ALL
possible_keys: NULL
                  key: NULL
          key_len: NULL
                  ref: NULL
                 rows: 102
                Extra: Using where

mysql> EXPLAIN SELECT * FROM tabela
        -> WHERE TO_DAYS(coluna_data) < 30 + TO_DAYS(CURDATE())
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: MEMBER
                 type: ALL
possible_keys: NULL
                  key: NULL
          key_len: NULL
                  ref: NULL
                 rows: 102
                Extra: Using where

mysql> EXPLAIN SELECT * FROM tabela
        -> WHERE coluna_data < DATE_ADD(CURDATE(), INTERVAL 30 DAY)
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: MEMBER
                 type: range
possible_keys: expiration
                  key: expiration
          key_len: 4
                  ref: NULL
                 rows: 6
                Extra: Using where

Os resultados dos primeiros dois blocos mostram que o índice não foi
utilizado. O valor de type indica como os valores são lidos da tabela.
O valor ALL significa que todos os registros serão examinados. O valor
NULL em cada uma das colunas relacionadas a key também indicam que o
índice não está sendo utilizado.

Já o resultado do terceiro bloco mostra que a cláusula WHERE foi
escrita de modo que o otimizador pode usar o índice na coluna_data:

• O valor de type que o índice está sendo usado para uma gama de valores.
• Os valores de possible_keys e key mostram que o índice na coluna_data
  é considerado um índice candidato e também é o índice usado atualmente.
• O valor de rows mostra que o otimizador estima que será necessário
  examinar 6 registros para processar a consulta. O que é melhor do que o
  valor de 102 registros dos dois primeiros planos de execução.

O segundo uso para EXPLAIN é para encontrar se a adição de índices
ajudará o otimizador a executar um bloco de comandos mais
eficientemente. Neste exemplo, usaremos duas tabelas que inicialmente
não têm índices. Isto servirá para mostrar o efeito da criação de
índices. Embora o exemplo seja simples, este princípio é aplicado em
consultas complexas com joins.

Suponhamos que temos duas tabelas t1 e t2, cada uma com 1.000 registros
contendo valores de 1 a 1.000. A consulta que nós examinaremos busca
registros onde os valores das duas tabelas são iguais.

mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
+------+------+
| i1   | i2   |
+------+------+
|       1 |     1 |
|       2 |     2 |
|       3 |     3 |
|       4 |     4 |
|       5 |     5 |

Sem índices, o EXPLAIN produz o seguinte resultado:

mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t1
                 type: ALL
possible_keys: NULL
                  key: NULL
          key_len: NULL
                  ref: NULL
                 rows: 1000
                Extra:
*************************** 2. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t2
                 type: ALL
possible_keys: NULL
                  key: NULL
          key_len: NULL
                  ref: NULL
                 rows: 1000
                Extra: Using where

O valor ALL no type indica que uma varredura completa será feita pelos
registros das tabelas. NULL no valor de possible_keys que não foram
encontrados índices candidatos.

Essas informações nos mostram que o otimizador não encontra informações úteis para a execução mais eficiente de consultas.

• Será feita uma varredura completa em t1.
• Para cada registro de t1 será feita uma varredura completa em t2.

O valor de rows mostra que o otimizador estima quantos registros serão
necessários para examinar cada fase da consulta. A estimativa para t1 é
de 1.000 registros porque será feita uma varredura em todos os
registros. Semelhantemente a estimativa para t2 também é de 1.000, mas
para cada registro de t1. Isso significa que o processamento da
consulta examinará 1.000 x 1.000, ou um milhão de registros.

Agora adicionamos um índice à coluna i2 da tabela t2:

mysql> ALTER TABLE t2 ADD INDEX (i2);
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t1
                 type: ALL
possible_keys: NULL
                  key: NULL
          key_len: NULL
                  ref: NULL
                 rows: 1000
                Extra:
*************************** 2. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t2
                 type: ref
possible_keys: i2
                  key: i2
          key_len: 5
                  ref: sampdb.t1.i1
                 rows: 10
                Extra: Using where; Using index

A saída para t1 ficou inalterada, indicando que uma varredura completa
ainda está sendo feita, mas o otimizador processou t2 de forma
diferente:

• O valor de type mudou de ALL para ref, mostrando que um valor de referência de t1 será usado para localizar registros em t2.
• O valor de referência é dado no campo ref: sampdb.t1.i1.
• O valor de rows é reduzido de 1.000 para 10, o que mostra que o
  otimizador estima que necessitará examinar somente 10 registros em t2
  para cada registro de t1. Isto é uma estimativa pessimista, pois
  somente 1 registro de em t2 combinará. Você verá mais à frente como
  auxiliar o otimizador a melhorar esta estimativa. A estimativa total de
  combinações de registros é 1.000 x 10 = 10.000. Este valor é muito
  melhor do que a estimativa anterior, que era de 1.000.000 de registros.

Agora, você pode ver o efeito com a criação de índice em t1:

mysql> ALTER TABLE t1 ADD INDEX (i1);
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t1
                 type: index
possible_keys: i1
                  key: i1
          key_len: 5
                  ref: NULL
                 rows: 1000
                Extra: Using index
*************************** 2. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t2
                 type: ref
possible_keys: i2
                  key: i2
          key_len: 5
                  ref: sampdb.t1.i1
                 rows: 10
                Extra: Using where; Using index

Esta saída é similar ao EXPLAIN anterior, mas a adição do índice fez
uma diferença na saída de t1. O valor de type foi alterado de NULL para
index e o valor de Extra foi mudado de vazio para Using index. Essas
mudanças indicam que, embora uma varredura completa dos valores dos
índices ainda esteja sendo feita, agora o otimizador pode ler
diretamente o índice sem usar o arquivo de dados.

Mais à frente você verá como ajudar o otimizador a fazer melhores
estimativas de custo executando o comando ANALYZE TABLE. Analisando as
tabelas e executando o EXPLAIN novamente, os campos terão melhores
estimativas.

mysql> ANALYZE TABLE t1, t2;
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
*************************** 1. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t1
                 type: index
possible_keys: i1
                  key: i1
          key_len: 5
                  ref: NULL
                 rows: 1000
                Extra: Using index
*************************** 2. row ***************************
                   id: 1
  select_type: SIMPLE
                table: t2
                 type: ref
possible_keys: i2
                  key: i2
          key_len: 5
                  ref: sampdb.t1.i1
                 rows: 1
                Extra: Using where; Using index

Neste caso, o otimizador estima que cada registro de t1 combinará com somente um registro de t2.

Priorizando o Otimizador

Isto soa estranho, mas há ocasiões em que você precisa alterar o comportamento do otimizador MySQL:

• Priorizar a ordem dos joins na otimização de
  tabelas. Use STRAIGHT_JOIN para forçar o otimizador a usar as tabelas
  numa ordem determinada. Se é isto que você deseja, você deve ordenar as
  tabelas de forma que a primeira tabela seja a que combinará o menor
  numero de registros. Se você não está certo de qual tabela é esta,
  utilize a tabela que tem o maior número de registros primeiro. Em
  outras palavras, tente usar primeiro a tabela que tenha a seleção mais
  restrita.

Outra possibilidade é o uso dos modificadores FORCE_INDEX, USER_INDEX e
IGNORE_INDEX depois do nome de uma tabela na lista de joins. Isto pode
ser útil nos casos onde o otimizador não faz a escolha correta.

• Esvaziar uma tabela com mínimos efeitos colaterais. Quando você
  necessita esvaziar uma tabela MyISAM completamente, é mais rápido fazer
  o servidor apagar e recriar a tabela baseado na descrição do arquivo
  .frm. Para fazer isso, usamos o comando TRUNCATE TABLE.

A otimização de tabelas MyISAM esvaziadas pela recriação torna a
operação extremamente rápida, porque não é necessário excluir cada
registro individualmente.

Contudo, há alguns efeitos colaterais do TRUNCATE_TABLE que podem ser indesejados sob certas circunstâncias:

• TRUNCATE TABLE não necessariamente produz uma contagem exata de número de registros excluídos.
• Para tabelas do tipo MyISAM, valores AUTO_INCREMENT normalmente não
  são reusados quando registros são excluídos. Contudo, utilizando
  TRUNCATE TABLE a seqüência de numeração é reiniciada.

Na próxima semana, falaremos sobre a escolha de tipos de dados e eficiência de consultas. Será o terceiro e último artigo da série.

Abraços!

*

Adaptação de: http://www.informit….a…377652&rl=1