Otimização de Queries

EXPLAIN no MySQL

EXPLAIN SELECT c.nome, COUNT(p.id) AS pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE c.ativo = 1
GROUP BY c.id\G

Colunas do EXPLAIN

Coluna	Significado
id	Ordem de execução
select_type	SIMPLE, PRIMARY, SUBQUERY, DERIVED
table	Tabela
type	const > eq_ref > ref > range > index > ALL
possible_keys	Índices candidatos
key	Índice usado
rows	Estimativa de linhas escaneadas
Extra	Using index, Using where, Using filesort

Tipos de acesso (do melhor ao pior):
const: Primary Key lookup (1 linha)
eq_ref: JOIN por PK
ref: Índice não único (várias linhas)
range: Range scan (> , <, BETWEEN)
index: Full index scan
ALL: Full table scan (EVITAR!)

Otimizando Queries

-- ❌ Ruim: full table scan
EXPLAIN SELECT * FROM pedidos WHERE YEAR(data) = 2024;
-- type: ALL, rows: 1M

-- ✅ Bom: range scan (usa índice)
EXPLAIN SELECT * FROM pedidos 
WHERE data >= '2024-01-01' AND data < '2025-01-01';
-- type: range, key: idx_data, rows: 50K

-- ❌ Ruim: OR quebra índice
EXPLAIN SELECT * FROM clientes 
WHERE nome = 'João' OR email = '[email protected]';

-- ✅ Bom: UNION (cada SELECT usa índice)
EXPLAIN SELECT * FROM clientes WHERE nome = 'João'
UNION
SELECT * FROM clientes WHERE email = '[email protected]';

-- ❌ Ruim: SELECT * (traz colunas desnecessárias)
SELECT * FROM pedidos WHERE cliente_id = 1;

-- ✅ Bom: apenas colunas necessárias
SELECT id, total, data FROM pedidos WHERE cliente_id = 1;

Index Hints

-- Forçar uso de índice específico
SELECT * FROM pedidos FORCE INDEX (idx_cliente_data)
WHERE cliente_id = 1 AND data > '2024-01-01';

-- Ignorar índice
SELECT * FROM pedidos IGNORE INDEX (idx_data)
WHERE YEAR(data) = 2024;

-- Usar índice para ORDER BY
SELECT * FROM pedidos USE INDEX (idx_cliente_data)
WHERE cliente_id = 1
ORDER BY data DESC;

Using Filesort — Evitando

-- ❌ Ruim: filesort (ordenação sem índice)
EXPLAIN SELECT * FROM pedidos 
WHERE cliente_id = 1
ORDER BY data DESC;
-- Extra: Using where; Using filesort

-- ✅ Bom: índice atende ORDER BY
CREATE INDEX idx_cliente_data ON pedidos (cliente_id, data DESC);

EXPLAIN SELECT * FROM pedidos 
WHERE cliente_id = 1
ORDER BY data DESC;
-- Extra: Using where (sem filesort!)

Paginação Eficiente

-- ❌ Ruim: OFFSET grande (escaneia linhas descartadas)
SELECT * FROM pedidos ORDER BY id LIMIT 20 OFFSET 100000;

-- ✅ Bom: Keyset Pagination (cursor-based)
SELECT * FROM pedidos 
WHERE id > 100000     -- último ID da página anterior
ORDER BY id 
LIMIT 20;

-- ✅ Bom: Seek Method
SELECT * FROM pedidos 
WHERE (data, id) < ('2024-06-01', 500)
ORDER BY data DESC, id DESC
LIMIT 20;

Query Rewriting

-- 1. Subquery vs JOIN
-- ❌ Subquery (pode ser mais lenta)
SELECT nome FROM clientes 
WHERE id IN (SELECT cliente_id FROM pedidos WHERE total > 1000);

-- ✅ JOIN (geralmente mais rápido)
SELECT DISTINCT c.nome
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE p.total > 1000;

-- 2. COUNT optimizations
-- ❌ Lento em MyISAM
-- ✅ COUNT(*) é rápido em MyISAM mas lento em InnoDB (precisa scan)
SELECT COUNT(*) FROM pedidos WHERE status = 'pendente';

-- ✅ Alternativa: manter contador separado (tabela de resumo)

-- 3. EXISTS vs COUNT
-- ❌ COUNT desnecessário
IF (SELECT COUNT(*) FROM pedidos WHERE cliente_id = 1) > 0

-- ✅ EXISTS (para quando encontra primeiro)
IF EXISTS (SELECT 1 FROM pedidos WHERE cliente_id = 1)

type=ALL = full table scan = problema. type=range/ref = bom, usa índice. Filesort = sem índice para ORDER BY. Keyset pagination escala melhor que OFFSET. Prefira JOIN a subqueries.