Explicando o MySQL Explain

Como usar o EXPLAIN?

A syntaxe para o explain é simples, apenas adicione EXPLAIN antes da query que você quer estudar. Ex: EXPLAIN SELECT coluna1, coluna2 FROM tabela WHERE coluna3 = valor;. Pode ser qualquer tipo de query: SELECT, UPDATE, DELETE, INSERT.

Mas, temos também outras formas de usar como: EXPLAIN FORMAT=json QUERY;  ou  EXPLAIN FORMAT=json QUERY\G; para trazer o resultado formatado.

Campos do explain

Ao executar o EXPLAIN no MySQL, você verá um reusltado com os seguintes campos: id, select_type, table, possible_keys, key, key_len, ref, rows, filtered e extra. No formato json os campos mudam um pouco, mas trazem as mesmas informações, veja na tabela abaixo.

Como analisar uma query com o MySQL Explain?

Primeiro, execute a query com o EXPLAIN.

        
          explain
          select
            a.first_name,
            a.last_name,
            f.title,
            f.description
          from
            actor a
          join film_actor fa ON
            fa.actor_id = a.actor_id
          join film f ON
            f.film_id = fa.film_id
          where
            a.first_name like 'A%';
        
        

Com o resultado em mãos é hora de analisar os dados:

A coluna select_type indica qual o tipo de select foi feito. Ela é importante para saber se foi um select simpes, um union, uma subquery. Veja a lista completa aqui.

A coluna table indica a tabela examinada. Nessa coluna traz o alias utilizado para atabela ou nome da tabela caso nenhum alias seja utilizado.

A coluna type descreve como as tabelas são unidas. Essa coluna te ajuda a saber se na consulta está ocorrendo algum full table scan (escaneamento completo em alguma tabela) ou se os dados estão sendo selecionados por valor constante, index, por um range de valores. Entre os melhores valores para essa coluna temos system, const e eq_ref e entre os piores temos ALL e index, sendo ALL o pior de todos. Veja a lista completa aqui.

Outro campo que deve ser observado é o possible_keys e o key, eles indicam respectivamente quais os índices que o mysql pode usar para achar os registros e qual o índice o mysql usou para achar os registros.

Depois, olhe o campo ref, ela mostra quais colunas ou constantes são comparadas ao índice que aparece na coluna key que foram utilizados para selecionar as linhas da tabela.

Além desses, é importante olhar os campos rows e filtered. A coluna rows indica quantas linhas o MySQL acredita que precisa examinar para executar a query, enquanto que a coluna filtered indica uma porcentagem estimada de linhas da tabela que são filtradas pela condição da tabela.

Algumas dicas

1. A coluna rows x filtered indica a quantidade de linhas examinadas. Ex: no caso acima a primeira linha traz que 200 na coluna rows e 11.11 (11.11%) na coluna filtered, então, a quantidade de linhas examinadas foi 200 x 11.11% = 22,22.
2. O número total de linhas examinadas é o produto da coluna rows de cada linha. No caso acima a conta seria 200 x 27 x 1 = 5400.
3. Fique atento a coluna key_len, ela indica o tamnho do índice. O MySQL pode usar índices em colunas com mais eficiência se forem declarados como do mesmo tipo e tamanho.
   1. VARCHAR e CHAR são considerados os mesmos se declarados com o mesmo tamanho. ex: VARCHAR(10) e CHAR(10)
4. Verifique se o índice usado na consulta é o ideal. O MySQL trabalha para usar sempre o melhor índice, mas é sempre bom verificar.
5. Nem sempre adicionar um novo íncide vai ajudar, então faça sempre testes.
6. Nos testes de query use SHOW STATUS LIKE 'Last_Query_Cost'; (dica do meu brother Felipe Borges). Execute esse comando após ter executado alguma query, o query cost não tem uma unidade de medida, o que você precisa saber é que quanto maior pior, a partir do MySQL 5.7, esse valor é exibido na coluna cost ao executar o EXPLAIN
7. Verifique se a consulta pode ser feita usado algum outro campo que possúi índice. No caso acima se mudarmos a pesquisa para usar o campo last_name o número de linhas examinadas deiminui drasticamente 7×27×1 = 189.

             
               explain
               select
                 a.first_name,
                 a.last_name,
                 f.title,
                 f.description
               from
                 actor a
               join film_actor fa ON
                 fa.actor_id = a.actor_id
               join film f ON
                 f.film_id = fa.film_id
               where
                 a.last_name like 'A%';
             
             

   id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
   1	SIMPLE	a		range	PRIMARY,idx_actor_last_name	idx_actor_last_name	137		7	100	Using index condition
   1	SIMPLE	fa		ref	PRIMARY,idx_fk_film_id	PRIMARY	2	sakila.a.actor_id	27	100	Using index
   1	SIMPLE	f		eq_ref	PRIMARY	PRIMARY	2	sakila.fa.film_id	1	100