Sintaxe de EXPLAIN (Obter informações sobre uma 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 MariaDB 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. Leia "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 MariaDB soluciona todas as joins utilizando um método multi-join de varedura simples. Isto significa que o MariaDB 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 exibe 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 MariaDB 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:

• id

  Identificador SELECT, o número sequêncial desta SELECT dentro da consulta.

• select_type

  Tipo de cláusula SELECT, que pode ser uma das seguintes:

  • SIMPLE

    SELECT simples (sem UNIONs ou subqueries).

  • PRIMARY

    SELECT mais externa.

  • UNION

    Segunda SELECT e as SELECTs posteriores do UNION

  • DEPENDENT UNION

    Seunda SELECT e SELECTs posteriores do UNION, dependente da subquery exterior.

  • SUBQUERY

    Primeiro SELECT na subquery.

  • DEPENDENT SUBQUERY

    Primeiro SELECT, dependente da subquery exterior.

  • DERIVED

    SELECT de tabela derivada (subquery na cláusula FROM).

• table

  A tabela para a qual a linha de saída se refere.

• type

  O tipo de join. Os diferentes tipos de joins são listados aqui, ordenados do melhor para o pior tipo:

  • system

    A tabela só tem uma linha (= tabela de sistema). Este é um caso especial do tipo de join const.

  • 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:

    SELECT * FROM const_table WHERE primary_key=1;
    SELECT * FROM const_table WHERE primary_key_part1=1 AND primary_key_part2=2;
    

  • eq_ref

    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

    SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
    SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
    

  • 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

    SELECT * FROM ref_table WHERE key_column=expr;
    SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
    SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;
    

  • ref_or_null

    Como ref, mas com o adicional que faremos uma busca extra para linhas com NULL. Leia "Como o MariaDB Otimiza IS NULL".

    SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
    

    Esta otimização do tipo join é nova para o MariaDB e é mais usada na resolução de sub queries.

  • range

    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.

    SELECT * FROM range_table WHERE key_column = 10;
    SELECT * FROM range_table WHERE key_column BETWEEN 10 and 20;
    SELECT * FROM range_table WHERE key_column IN (10,20,30);
    SELECT * FROM range_table WHERE key_part1= 10 and key_part2 IN (10,20,30);
    

  • index

    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.

  • ALL

    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.

• possible_keys

  A coluna possible_keys indica quais índices o MariaDB 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. Leia "Sintaxe ALTER TABLE".

  Para ver os índices existentes em uma tabela, utilize SHOW INDEX FROM nome_tabela.

• key

  A coluna key indica a chave (índice) que o MariaDB decidiu usar. A chave será NULL se nenhum índice for escolhido. Para forçar o MariaDB a usar um índice listado na coluna possible_keys, use USE INDEX/IGNORE INDEX em sua consulta. Leia "Sintaxe SELECT".

  Executando myisamchk --analyze (see "Sintaxe do myisamchk") ou ANALYSE TABLE (see "Sintaxe de ANALYZE TABLE") na tabela também ajudará o otimizador a escolher índices melhores.

• key_len

  A coluna key_len indica o tamanho da chave que o MariaDB decidiu utilizar. O tamanho será NULL se key for NULL. Note que isto nos diz quantas partes de uma chave multi-partes o MariaDB realmente está utilizando.

• ref

  A coluna ref exibe quais colunas ou contantes são usadas com a key para selecionar registros da tabela.

• rows

  A coluna rows informa o número de linhas que o MariaDB deve examinar para executar a consulta.

• Extra

  Esta coluna contem informações adicionais de como o MariaDB irá resolver a consulta. A seguir uma explicação das diferentes strings de texto que podem ser encontradas nesta coluna:

  • Distinct

    O MariaDB não continuará a procurar por mais registros para a combinação de registro atual depois de ter encontrado o primeiro registro coincidente.

  • Not exists

    O MariaDB 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.

    Exemplo:

    SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
    

    Assume que t2.id é definido com NOT NULL. Neste caso o MariaDB irá percorrer t1 e procurar pelos registros em t2 através de t1.id. Se o MariaDB 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 MariaDB só precisa fazer uma única pesquisa em t2, independente de quantos registros coincidentes existirem em t2.

  • range checked for each record (index map: #)

    O MariaDB 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.

  • Using filesort

    O MariaDB 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.

  • Using index

    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.

  • Using temporary

    Para resolver a consulta, o MariaDB 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.

  • Using where

    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 MariaDB deve examinar para executar a consulta. Este número é também usado quando você restringe consultas com a variável max_join_size. Leia "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:

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
 tt.ProjectReference, tt.EstimatedShipDate,
 tt.ActualShipDate, tt.ClientID,
 tt.ServiceCodes, tt.RepetitiveID,
 tt.CurrentProcess, tt.CurrentDPPerson,
 tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
 et_1.COUNTRY, do.CUSTNAME
 FROM tt, et, et AS et_1, do
 WHERE tt.SubmitTime IS NULL
 AND tt.ActualPC = et.EMPLOYID
 AND tt.AssignedPC = et_1.EMPLOYID
 AND tt.ClientID = do.CUSTNMBR;

Para este exemplo, assuma que:

• As colunas comparadas foram declaradas como a seguir:

  Tabela	Coluna	Tipo da coluna
  tt	ActualPC	CHAR(10)
  tt	AssignedPC	CHAR(10)
  tt	ClientID	CHAR(10)
  et	EMPLOYID	CHAR(15)
  do	CUSTNMBR	CHAR(15)

• As tabelas possuem os índices mostrados abaixo:

  Tabela	Índice
  tt	ActualPC
  tt	AssignedPC
  tt	ClientID
  et	EMPLOYID (chave primária)
  do	CUSTNMBR (chave primária)

• The tt.ActualPC values aren't evenly distributed.

Initially, before any optimizations have been performed, the EXPLAIN statement produces the following information:

table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872
 range checked for each record (key map: 35)

Como o tipo é ALL em todas tabelas, esta saída indica que o MariaDB 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 * 2135 * 74 * 3872 registros. Se as tabelas forem maiores, imagine quanto tempo este tipo de consulta pode demorar.

Um dos problemas aqui é que o MariaDB 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:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Agora ambos campos tt.ActualPC e et.EMPLOYID são VARCHAR(15). Executando a instrução EXPLAIN novamente produzirá este resultado:

table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 Using where do ALL PRIMARY NULL NULL NULL 2135
 range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
 range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

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 :

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
 -> MODIFY ClientID VARCHAR(15);

Agora EXPLAIN produz a saída mostrada abaixo:

table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using where
 ClientID,
 ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

Este resultado é quase o melhor que se pode obter.

O problema restante é que, por padrão, o MariaDB 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 MariaDB sobre isto:

shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh

Agora a join está perfeita, e EXPLAIN produz esta saída:

table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 Using where
 ClientID,
 ActualPC et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

Perceba que a coluna rows na saída de EXPLAIN é uma boa ajuda para otimizador de joins do MariaDB. 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.

Retornar