5.8 Otimização e ajuste de desempenho
Ciclo de otimização, leitura de planos com EXPLAIN, índices e seu custo, particionamento, memória do servidor, pool de conexões e escala vertical × horizontal.
Onde se ganha desempenho
⚠ A página original chama de "escalonamento" apenas o acréscimo de hardware. O termo é escalar (escalabilidade), e somar CPU e memória é só a escala vertical; existe também a horizontal, com mais servidores.
⚠ A página original lista "melhoria da segurança" como vantagem: consultas mais rápidas protegeriam contra ataques e violações de dados. Desempenho não é controle de segurança; proteção vem de acesso, criptografia e auditoria (aulas 5.3 a 5.5).
Lendo o plano com EXPLAIN
PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, data, total
FROM pedido
WHERE cliente_id = 12345;MySQL 8
EXPLAIN -- colunas type, key, rows
SELECT id, data, total
FROM pedido
WHERE cliente_id = 12345;
-- em árvore / com tempos reais
EXPLAIN FORMAT=TREE SELECT …;
EXPLAIN ANALYZE SELECT …;O exemplo da aula, com antes e depois
-- vale para PostgreSQL e MySQL
CREATE INDEX idx_pedido_cliente_data
ON pedido (cliente_id, data);
SELECT id, data, total
FROM pedido
WHERE cliente_id = 12345
ORDER BY data DESC
LIMIT 20;No PostgreSQL, em tabela de produção use CREATE INDEX CONCURRENTLY para não bloquear as gravações durante a criação. No MySQL 8, o InnoDB já cria índices sem bloquear gravações na maioria dos casos (DDL online).
O índice só em cliente_id, do original, já resolve o filtro; o composto também atende o ORDER BY.
Configuração do servidor e manutenção
PostgreSQL
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();
-- shared_buffers exige reiniciar
SHOW shared_buffers;MySQL 8
-- dinâmico e gravado em mysqld-auto.cnf
SET PERSIST innodb_buffer_pool_size
= 8589934592; -- 8 GB
SHOW VARIABLES
LIKE 'innodb_buffer_pool_size';Percentuais são pontos de partida, não receitas: o valor certo depende da carga e se mede, como tudo nesta aula.
Revisão rápida
Por que não criar índice em todas as colunas?
Cada índice deixa as gravações mais lentas e ocupa espaço. Só compensa onde há consulta que o use.
Qual o cuidado ao rodar EXPLAIN ANALYZE num DELETE?
Ele executa de verdade. Rode dentro de BEGIN … ROLLBACK.
No MySQL, o que indica type = ALL no EXPLAIN?
Leitura da tabela inteira: nenhum índice foi usado para aquele acesso.
Réplica de leitura é escala vertical ou horizontal?
Horizontal: acrescenta servidores em vez de aumentar um só.