Identificando e Analisando Consultas Lentas no MySQL

Quando uma consulta SQL apresanta degradação de performance em um sistema MySQL, é fundamental compreender a causa raiz da lentidão. As abordagens mais comuns para resolver esse problema incluem:

1. Reescrita da consulta com base na experiência prévia em otimização.
2. Adequação da consulta aos padrões e convenções de desenvolvimento da equipe.
3. Inspeção do plano de execução para verificar o uso de índices, a ordem de junção das tabelas e os algoritmos de acesso aplicados pelo otimizador.

Embora as duas primeiras estratégias sejam úteis, a terceira é indipsensável. Interagir diretamente com o mecanismo do banco de dados para visualizar o plano de execução oferece uma visão objetiva de como a consulta será processada. No MySQL, a instrução EXPLAIN (e seus sinônimos DESC e DESCRIBE) é a ferramenta padrão para essa finalidade. Ela revela se a consulta está utiilzando índices adequados, se há criação de tabelas temporárias ou se operações de ordenação extras estão sendo acionadas.

Analisando um Plano de Execução Básico

Considere a seguinte consulta executada em uma tabela chamada sales_records:

mysql> EXPLAIN SELECT * FROM sales_records WHERE region_id = 4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales_records
   partitions: NULL
         type: ref
possible_keys: idx_region, idx_composite
          key: idx_composite
      key_len: 5
          ref: const
         rows: 28450
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

Os campos mais relevantes neste resultado são:

• possible_keys: Lista todos os índices que o otimizador considerou viáveis para a consulta.
• key: Indica o índice efetivamente escolhido. Neste caso, idx_composite foi selecionado.
• rows: Estimativa do número de linhas que o MySQL precisará examinar. Aqui, o valor é de aproximadamente 28 mil linhas.
• Extra: Informações adicionais sobre a execução. O valor NULL indica que não há operações extras complexas (como ordenação em memória ou disco).

O Paradoxo do Índice: Por que a Consulta Continua Lenta?

Mesmo utilizando um índice, a consulta pode não atingir a performance desejada. O uso de um índice não garante, por si só, alta performance. Para entender o motivo, podemos consultar a cardinalidade dos índices na tabela sales_records:

mysql> SELECT table_name, index_name, column_name, cardinality 
    -> FROM information_schema.statistics 
    -> WHERE table_schema='company_db' AND table_name='sales_records';
+---------------+--------------+-------------+-------------+
| TABLE_NAME    | INDEX_NAME   | COLUMN_NAME | CARDINALITY |
+---------------+--------------+-------------+-------------+
| sales_records | idx_date     | sale_date   |         412 |
| sales_records | idx_region   | region_id   |           6 |
| sales_records | idx_product  | product_id  |         150 |
| sales_records | idx_composite| region_id   |           6 |
| sales_records | idx_composite| product_id  |         840 |
| sales_records | idx_composite| amount      |       62000 |
| sales_records | PRIMARY      | id          |      115000 |
+---------------+--------------+-------------+-------------+

O índice idx_composite é um índice composto. A cardinalidade da coluna region_id dentro deste índice é extremamente baixa (apenas 6 valores distintos). Isso significa que a seletividade do índice é pobre. Para uma busca de igualdade (region_id = 4), o banco de dados é forçado a percorrer dezenas de milhares de registros, tornando a operação custosa. Nesses cenários, a otimização puramente em nível de SQL atinge seu limite, sendo necessária uma revisão da modelagem de dados ou da lógica de negócio.

Lidando com Ordenações e "Using filesort"

Vamos modificar a consulta adicionando uma cláusula de ordenação:

mysql> EXPLAIN SELECT * FROM sales_records WHERE region_id = 4 ORDER BY id DESC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales_records
         type: ref
possible_keys: idx_region, idx_composite
          key: idx_composite
      key_len: 5
          ref: const
         rows: 28450
     filtered: 100.00
        Extra: Using filesort

O campo Extra agora exibe Using filesort, o que indica que o MySQL não pôde usar o índice para resolver a ordenação, exigindo uma operação de classificação adicional. Uma tentativa comum de otimização seria criar um índice que cubra tanto o filtro quanto a ordenação:

mysql> ALTER TABLE sales_records ADD INDEX idx_region_id_desc (region_id, id DESC);
Query OK, 0 rows affected (1.12 sec)

No entanto, ao executar o EXPLAIN novamente, o otimizador pode continuar escolhendo o índice antigo e mantendo o Using filesort. Isso ocorre porque o otimizador do MySQL avalia o custo total da operação. Para forçar o uso do novo índice e comparar os cenários, utilizamos a dica FORCE INDEX:

mysql> EXPLAIN SELECT * FROM sales_records FORCE INDEX(idx_region_id_desc) 
    -> WHERE region_id = 4 ORDER BY id DESC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales_records
         type: ref
possible_keys: idx_region_id_desc
          key: idx_region_id_desc
      key_len: 5
          ref: const
         rows: 31200
     filtered: 100.00
        Extra: NULL

Validando Custos com EXPLAIN ANALYZE

Embora o FORCE INDEX tenha eliminado o Using filesort, o número estimado de linhas lidas aumentou. Para tomar uma decisão baseada em dados, o MySQL 8.0 introduziu o EXPLAIN ANALYZE, que executa a consulta e exibe os custos reais e estimados:

mysql> EXPLAIN ANALYZE SELECT * FROM sales_records FORCE INDEX(idx_region_id_desc) 
    -> WHERE region_id = 4 ORDER BY id DESC\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on sales_records using idx_region_id_desc (region_id=4)  
           (cost=3650.40 rows=31200) (actual time=0.250..35.410 rows=18500 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM sales_records 
    -> WHERE region_id = 4 ORDER BY id DESC\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: sales_records.id DESC  (cost=3210.50 rows=28450) (actual time=65.120..68.300 rows=18500 loops=1)
    -> Index lookup on sales_records using idx_composite (region_id=4)  
       (actual time=0.055..45.200 rows=18500 loops=1)

A análise de custo revela que a estratégia de ordenação em memória (custo 3210.50) é considerada mais barata pelo otimizador do que a leitura de um número maior de linhas através do novo índice (custo 3650.40). O MySQL pondera o consumo de CPU para ordenar os dados versus o consumo de I/O para ler mais páginas de disco. Conclui-se, portanto, que a criação do índice idx_region_id_desc não trouxe benefícios reais para esta consulta específica e pode ser descartada para evitar sobrecarga em operações de escrita.