Análise inicial de performance de queries no MySql Laboratório prático.

Poryanspinelli56

Performance de banco de dados nem sempre está ligada apenas a hardware ou configuração do servidor.
Muitas vezes o problema está em queries mal otimizadas ou em consultas que, mesmo rápidas, são executadas milhares de vezes por minuto.

Neste pequeno laboratório vamos simular um cenário simples:

  • Criar uma base de testes
  • Inserir muitos registros
  • Executar uma query problemática
  • Analisar o plano de execução com EXPLAIN
  • Melhorar a consulta com índices
  • Entender quando o MySQL usa temporary tables e filesort

Esse tipo de análise inicial costuma ser um dos primeiros passos quando um DBA precisa investigar lentidão em um banco de dados.

1 – CRIANDO O AMBIENTE DE TESTE

CREATE DATABASE lab_performance;

USE lab_performance;

CREATE TABLE pedidos (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
cliente VARCHAR(100),
produto VARCHAR(100),
valor DECIMAL(10,2),
data_pedido DATETIME
);

2 – INSERINDO UM GRANDE VOLUME DE REGISTROS

Para tornar o cenário mais próximo da realidade, vamos inserir muitos registros na tabela.

Uma forma simples é usar uma procedure com loop.

DELIMITER $$

CREATE PROCEDURE carga_pedidos()
BEGIN
DECLARE i INT DEFAULT 1;

WHILE i <= 500000 DO

    INSERT INTO pedidos (cliente, produto, valor, data_pedido)
    VALUES (
        CONCAT('CLIENTE_', FLOOR(RAND()*1000)),
        CONCAT('PRODUTO_', FLOOR(RAND()*100)),
        RAND()*1000,
        NOW()
    );

    SET i = i + 1;

END WHILE;
END$$

DELIMITER ;

Para executar:

CALL carga_pedidos();

3 – VALIDANDO OS REGISTROS INSERIDOS

SELECT COUNT(*) FROM pedidos;
Resultado esperado aproximado:

COUNT(*)
500006

Agora temos meio milhão de registros para trabalhar.

4 – QUERY PROBLEMÁTICA

Agora vamos executar uma consulta simples:

SELECT *
FROM pedidos
WHERE cliente = 'CLIENTE1';

Mesmo sendo uma consulta simples, sem índice o MySQL precisa percorrer praticamente toda a tabela.

5 – ANALISANDO COM EXPLAIN

EXPLAIN SELECT *
FROM pedidos
WHERE cliente = 'CLIENTE1';

Resultado típico:

type: ALL
rows: ~498000
Extra: Using where

O campo mais importante aqui é:

type: ALL

Isso significa que o MySQL está fazendo um FULL TABLE SCAN.

Ou seja:

  • Ele precisa ler praticamente toda a tabela
  • Quanto maior a tabela, maior o custo

Exemplo diagrama.

6 – CRIANDO UM ÍNDICE

Agora vamos criar um índice na coluna usada no filtro.

CREATE INDEX idx_cliente
ON pedidos(cliente);

7 – ANALISANDO NOVAMENTE

EXPLAIN SELECT *
FROM pedidos
WHERE cliente = 'CLIENTE1';

Agora o plano muda para algo parecido com:

type: ref
rows: 2

Ou seja:

  • O MySQL consegue localizar diretamente os registros
  • Muito menos linhas precisam ser analisadas

Isso reduz drasticamente o custo da consulta.

8 – OUTRO CENÁRIO COMUM: GROUP BY E ORDER BY

Agora vamos executar uma query um pouco mais complexa:

SELECT
cliente,
COUNT(*) AS total_pedidos,
SUM(valor) AS total_valor
FROM pedidos
GROUP BY cliente
ORDER BY total_valor DESC;

Essa consulta faz:

  • agrupamento por cliente
  • soma de valores
  • ordenação pelo total

9 – ANALISANDO COM EXPLAIN

EXPLAIN
SELECT
cliente,
COUNT(*) AS total_pedidos,
SUM(valor) AS total_valor
FROM pedidos
GROUP BY cliente
ORDER BY total_valor DESC;

Resultado típico:

Extra: Using temporary; Using filesort

Isso significa que o MySQL precisou:

  • criar uma tabela temporária
  • ordenar os resultados manualmente

10 – CRIANDO UM ÍNDICE COMPOSTO

CREATE INDEX idx_cliente_valor
ON pedidos(cliente, valor);

Rodando novamente o EXPLAIN:

Agora pode aparecer algo como:

Using index; Using temporary; Using filesort

Ou seja:

  • o índice ajuda a leitura dos dados
  • mas ainda é necessário usar temporary e filesort

POR QUE ISSO ACONTECE?

A ordenação está sendo feita sobre um valor agregado:

SUM(valor)

Esse valor só é conhecido depois que o MySQL termina o GROUP BY.

Por isso o banco precisa:

1) agrupar os registros
2) calcular o SUM
3) ordenar o resultado final

Mesmo com índice, esse custo continua existindo.

11 – ABORDAGENS MAIS PERFORMÁTICAS

Quando esse tipo de consulta aparece com frequência em produção, algumas abordagens ajudam bastante:

1) Reduzir o volume de dados consultado

Exemplo:

WHERE data_pedido >= NOW() – INTERVAL 30 DAY

2) Trabalhar com tabelas de agregação

Criar tabelas de resumo com totais já calculados.

3) Limitar resultados

Usar LIMIT quando a aplicação só precisa dos primeiros resultados.

12 – O PROBLEMA QUE POUCOS DBAs LEMBRAM

Em muitos cenários de produção, o problema não está necessariamente em uma query extremamente lenta, mas sim na frequência com que ela é executada.

Uma aplicação pode disparar a mesma consulta milhares de vezes por minuto, criando pressão constante no banco de dados, no buffer pool e na CPU do servidor.

O diagrama abaixo ilustra esse fluxo típico de execução repetitiva de queries.

Ou seja, uma query aparentemente rápida pode causar impacto enorme quando executada em grande volume, gerando os topicos abaixos.

  • alta utilização de CPU
  • pressão no Buffer Pool
  • aumento de locks
  • degradação de performance no banco inteiro

Para entender melhor esse impacto acumulado, veja o gráfico abaixo que demonstra como consultas aparentemente rápidas podem consumir muito tempo total quando executadas em grande volume.

Abaixo mais um grafico para exemplificar a evolução de um query frequency

Observarmos como o impacto de uma consulta cresce conforme aumenta sua frequência de execução.

Mesmo quando uma query é relativamente rápida, o custo total pode se tornar significativo quando ela é executada milhares ou até centenas de milhares de vezes pela aplicação.

O gráfico abaixo ilustra exatamente esse efeito: quanto maior a frequência de execução, maior o tempo total consumido pelo banco de dados.

CONCLUSÃO

É exatamente por isso que a análise de queries não deve considerar apenas o tempo individual de execução, mas também o volume e a frequência com que essas consultas são disparadas pela aplicação, outro ponto é estar ciente que nem sempre se resolvera com a criação de um indice.

O que aprendemos com este lab.

  • uso de EXPLAIN
  • entender full table scan
  • identificar temporary / filesort
  • avaliar frequência de execução

Deixe uma resposta