Otimização com EXPLAIN no MySQL

Introdução

O MySQL disponibiliza o comando EXPLAIN, uma ferramenta poderosa para analisar instruções SELECT e fornecer detalhes sobre sua execução. Essa análise permite que desenvolvedores identifiquem gargalos e otimizem suas consultas de forma mais eficaz.

A utilização do comando é bastante direta, bastando prefixar a consulta SELECT com a palavra-chave EXPLAIN:

EXPLAIN SELECT * from dados_usuario WHERE id < 300;

Preparação do Ambiente

Para demonstrar o uso prático do EXPLAIN, criaremos duas tabelas de teste com estruturas ligeiramente diferentes das originais:

CREATE TABLE `dados_usuario` (
  `codigo`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `nome`     VARCHAR(50) NOT NULL DEFAULT '',
  `idade`    INT(11)              DEFAULT NULL,
  PRIMARY KEY (`codigo`),
  KEY `idx_nome` (`nome`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO dados_usuario (nome, idade) VALUES ('carlos', 28);
INSERT INTO dados_usuario (nome, idade) VALUES ('ana', 32);
INSERT INTO dados_usuario (nome, idade) VALUES ('bruno', 45);
INSERT INTO dados_usuario (nome, idade) VALUES ('diana', 19);
INSERT INTO dados_usuario (nome, idade) VALUES ('eduardo', 28);
INSERT INTO dados_usuario (nome, idade) VALUES ('fernanda', 32);
INSERT INTO dados_usuario (nome, idade) VALUES ('gustavo', 45);
INSERT INTO dados_usuario (nome, idade) VALUES ('helena', 19);
INSERT INTO dados_usuario (nome, idade) VALUES ('igor', 28);
INSERT INTO dados_usuario (nome, idade) VALUES ('julia', 32);

CREATE TABLE `registros_pedido` (
  `codigo`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `cod_usuario`      BIGINT(20)           DEFAULT NULL,
  `item_produto`     VARCHAR(50) NOT NULL DEFAULT '',
  `fabricante`       VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`codigo`),
  KEY `idx_usuario_item_fabricante` (`cod_usuario`, `item_produto`, `fabricante`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8

INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (1, 'produto1', 'WHH');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (1, 'produto2', 'WL');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (1, 'produto1', 'DX');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (2, 'produto1', 'WHH');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (2, 'produto5', 'WL');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (3, 'produto3', 'MA');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (4, 'produto1', 'WHH');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (6, 'produto1', 'WHH');
INSERT INTO registros_pedido (cod_usuario, item_produto, fabricante) VALUES (9, 'produto8', 'TE');

Formato de Saída do EXPLAIN

A saída do comando EXPLAIN apresenta informações detalhadas sobre como a consulta será executada:

mysql> explain select * from dados_usuario where codigo = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Vamos analisar os significados das colunas principais:

id: Identificador único para cada consulta SELECT na execução.
select_type: Tipo de consulta SELECT.
table: Tabela envolvida na consulta.
partitions: Partição correspondente, se aplicável.
type: Tipo de join.
possible_keys: Índices que poderiam ser utilizados na consulta.
key: Índice efetivamente utilizado.
ref: Campo ou constante utilizado junto com o índice.
rows: Número estimado de linhas a serem examinadas.
filtered: Porcentagem de linhas filtradas pelas condições.
extra: Informações adicionais sobre a execução.

Vamos nos concentrar nos campos mais importantes:

select_type

O campo select_type indica o tipo de consulta, com valores comuns como:

SIMPLE: Consulta sem UNION ou subconsultas.
PRIMARY: Consulta mais externa em consultas aninhadas.
UNION: Segunda ou subsequente consulta em uma operação UNION.
DEPENDENT UNION: Consulta UNION que depende de resultados externos.
UNION RESULT: Resultado de uma operação UNION.
SUBQUERY: Primeira SELECT em uma subconsulta.
DEPENDENT SUBQUERY: Subconsulta que depende de resultados externos.

O tipo mais comum é SIMPLE, utilizado em consultas diretas sem subconsultas ou UNIONs:

mysql> explain select * from dados_usuario where codigo = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Para consultas UNION, a saída é diferente:

mysql> EXPLAIN (SELECT * FROM dados_usuario  WHERE codigo IN (1, 2, 3))
    -> UNION
    -> (SELECT * FROM dados_usuario WHERE codigo IN (3, 4, 5));
+----+--------------+--------------+------------+-------+---------------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table        | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+--------------+------------+-------+---------------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | dados_usuario | NULL       | range | PRIMARY             | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
|  2 | UNION        | dados_usuario | NULL       | range | PRIMARY             | PRIMARY | 8       | NULL |    3 |   100.00 | Using where     |
| NULL | UNION RESULT | <union1,2>   | NULL       | ALL   | NULL                | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+--------------+------------+-------+---------------------+---------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

type

O campo type é crucial para avaliar a eficiência da consulta. Ele indica se a consulta realiza varredura completa da tabela ou utiliza índices.

Principais tipos de acesso

Os valores mais comuns para type são:

system: Tabela contém apenas uma linha. Tipo especial de const.
const: Consulta em índice primário ou único com igualdade, retorna no máximo uma linha. Muito eficiente.

mysql> explain select * from dados_usuario where codigo = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

eq_ref: Comum em joins multi-tabela, para cada linha da tabela anterior corresponde exatamente uma linha na tabela atual. Operações geralmente com =.

mysql> EXPLAIN SELECT * FROM dados_usuario, registros_pedido WHERE dados_usuario.codigo = registros_pedido.cod_usuario\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: registros_pedido
   partitions: NULL
         type: index
possible_keys: idx_usuario_item_fabricante
          key: idx_usuario_item_fabricante
      key_len: 314
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.registros_pedido.cod_usuario
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

ref: Utilizado em joins com índices não únicos ou quando aplicada a regra do prefixo esquerdo do índice.

mysql> EXPLAIN SELECT * FROM dados_usuario, registros_pedido WHERE dados_usuario.codigo = registros_pedido.cod_usuario AND registros_pedido.cod_usuario = 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: registros_pedido
   partitions: NULL
         type: ref
possible_keys: idx_usuario_item_fabricante
          key: idx_usuario_item_fabricante
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.01 sec)

range: Utiliza índice para busca por intervalo. Comum em operações como =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN().

mysql> EXPLAIN SELECT *
    ->         FROM dados_usuario
    ->         WHERE codigo BETWEEN 2 AND 8 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 7
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

index: Varredura completa do índice, sem acesso aos dados. Quando os dados necessários estão contidos no próprio índice.

mysql> EXPLAIN SELECT nome FROM  dados_usuario \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_nome
      key_len: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ALL: Varredura completa da tabela. Tipo de acesso menos eficiente, deve ser evitado sempre que possível.

mysql> EXPLAIN SELECT idade FROM  dados_usuario WHERE idade = 28 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dados_usuario
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
     filtered: 30.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Comparação de performance

A relação de performence entre os tipos de acesso é:

ALL < index < range ~ index_merge < ref < eq_ref < const < system

possible_keys

Indica os índices que o MySQL poderia utilizar para executar a consulta. Não garante que serão usados de fato.

key

Mostra o índice efetivamente utilizado na consulta.

key_len

Comprimento em byttes do índice utilizado. Útil para verificar se índices compostos estão sendo totalmente aproveitados.

Regras de cálculo:

Strings:
char(n): n bytes
varchar(n): utf8 = 3n + 2 bytes; utf8mb4 = 4n + 2 bytes
Numéricos:
TINYINT: 1 byte
SMALLINT: 2 bytes
MEDIUMINT: 3 bytes
INT: 4 bytes
BIGINT: 8 bytes
Data:
DATE: 3 bytes
TIMESTAMP: 4 bytes
DATETIME: 8 bytes
Atributos: NULL = 1 byte; NOT NULL = 0 bytes

Exemplos:

mysql> EXPLAIN SELECT * FROM registros_pedido WHERE cod_usuario < 3 AND item_produto = 'produto1' AND fabricante = 'WHH' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: registros_pedido
   partitions: NULL
         type: range
possible_keys: idx_usuario_item_fabricante
          key: idx_usuario_item_fabricante
      key_len: 9
          ref: NULL
         rows: 5
     filtered: 11.11
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Neste caso, apenas o campo cod_usuario do índice composto é utilizado devido à busca por intervalo, resultando em key_len = 9 (8 para BIGINT + 1 para NULL).

mysql> EXPLAIN SELECT * FROM registros_pedido WHERE cod_usuario = 1 AND item_produto = 'produto1' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: registros_pedido
   partitions: NULL
         type: ref
possible_keys: idx_usuario_item_fabricante
          key: idx_usuario_item_fabricante
      key_len: 161
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Aqui, key_len = 161 (8 para cod_usuario + 150 para item_produto + 3 para NULL).

rows

Número estimado de linhas que o otimizador precisa examinar para executar a cosnulta. Valores menores indicam consultas mais eficientes.

Extra

Contém informações adicionais importantes sobre a execução:

Using filesort: Indica que uma ordenação extra é necessária, sem uso de índice. Deve ser evitada por consumir muitos recursos.

mysql> EXPLAIN SELECT * FROM registros_pedido ORDER BY item_produto \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: registros_pedido
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_usuario_item_fabricante
      key_len: 253
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)

Using index: "Varredura de índice de cobertura", os dados necessários estão no próprio índice, sem acesso à tabela.
Using temporary: Indica uso de tabela temporária, comum em ordenações, agrupamentos e joins. Deve ser otimizado quando possível.

Tags: MySQL EXPLAIN otimização SQL índices análise de consultas

Publicado em 6-5 17:38 por Thomas

Doido Dev