Proposta de Estratégia de Tuning para Consultas

Prévia do material em texto

Para otimizar o desempenho das suas consultas e garantir que elas utilizem os índices de 
forma eficaz, as seguintes estratégias de tuning são recomendadas: 
1. CRIAÇÃO E A JUSTE DE ÍNDICES 
A base de qualquer estratégia de tuning é a criação de índices apropriados. Com base 
nos seus experimentos com e sem índices e na análise do EXPLAIN, você já deve ter uma 
boa ideia de quais colunas são frequentemente usadas em cláusulas WHERE, JOIN e 
ORDER BY. 
 Índices Compostos: Se suas consultas frequentemente filtram ou ordenam por múltiplas 
colunas, considere criar índices compostos. A ordem das colunas em um índice composto 
é crucial. Coloque a coluna mais seletiva (aquela que reduz mais o número de linhas) 
primeiro. Por exemplo, se você consulta por nome e sobrenome, e nome é mais seletivo, 
o índice deveria ser (nome, sobrenome). 
 Índices de Cobertura (Covering Indexes): Para consultas que selecionam apenas um 
subconjunto de colunas, um índice de cobertura pode ser extremamente benéfico. Este 
tipo de índice contém todas as colunas necessárias para a consulta, permitindo que o 
banco de dados obtenha todos os dados diretamente do índice, sem precisar acessar a 
tabela principal. Isso evita leituras de disco adicionais e pode acelerar significativamente as 
consultas. 
o Exemplo: Se uma consulta é SELECT nome, email FROM usuarios 
WHERE cidade = 'São Paulo', um índice em (cidade, nome, email) 
poderia cobrir a consulta. 
 Índices para Cláusulas ORDER BY e GROUP BY: Se suas consultas frequentemente usam 
ORDER BY ou GROUP BY, um índice nas colunas relevantes pode eliminar a necessidade 
de operações de classificação caras (Using filesort), que são realizadas em memória 
ou em disco. 
 Índices para Chaves Estrangeiras: Embora não diretamente relacionados a LIKE, indexar 
colunas que servem como chaves estrangeiras é crucial para o desempenho de JOINs. 
 Evitar Índices Excessivos: Embora índices melhorem o desempenho de leitura, eles 
adicionam sobrecarga para operações de escrita (INSERT, UPDATE, DELETE), pois o banco 
de dados precisa manter os índices atualizados. Crie índices apenas onde eles são 
realmente necessários e justificados pela frequência e importância das consultas. 
2. OTIMIZAÇ ÃO DAS CONSULTAS SQL 
Além de criar índices, a maneira como você escreve suas consultas pode ter um impacto 
significativo no desempenho. 
 Evitar Funções em Cláusulas WHERE: Aplicar funções a colunas em uma cláusula WHERE 
geralmente impede que o otimizador de consultas use índices. Por exemplo, WHERE 
FUNCAO(coluna) = 'valor' fará com que o banco de dados ignore o índice em 
coluna, resultando em uma varredura completa. Tente reescrever a consulta para que a 
 
função seja aplicada ao valor que está sendo comparado, e não à coluna indexada (e.g., 
WHERE coluna = FUNCAO_INVERSA('valor') se possível). 
 Cuidado com LIKE no Início: O uso de LIKE '%texto' geralmente resulta em 
varredura sequencial, pois o índice (tipicamente uma B-tree) não pode ser utilizado para 
encontrar o início da string. Se possível, tente usar LIKE 'texto%', que pode aproveitar 
os índices. Para casos onde o % no início é inevitável e a busca textual é frequente, 
considere soluções mais avançadas como índices de texto completo (FULLTEXT em 
MySQL/MariaDB, GIN ou GIST em PostgreSQL) ou ferramentas de busca externa. 
 Cláusulas JOIN Otimizadas: Certifique-se de que as colunas usadas nas cláusulas JOIN 
entre tabelas estejam indexadas. Isso é fundamental para um desempenho eficiente em 
consultas que envolvem múltiplas tabelas, pois permite que o banco de dados localize 
rapidamente as linhas correspondentes. 
 EXISTS vs. IN: Em algumas situações, EXISTS pode ser mais eficiente que IN, 
especialmente quando a subconsulta retorna um grande número de linhas, pois EXISTS 
para de procurar assim que encontra a primeira correspondência. Teste ambas as 
abordagens para ver qual se adapta melhor ao seu cenário. 
 Limitar Resultados (LIMIT): Sempre que possível, use LIMIT para restringir o número de 
linhas retornadas, especialmente em tabelas grandes. Isso reduz a quantidade de dados 
que o banco de dados precisa processar e enviar pela rede. 
 Minimizar SELECT *: Selecionar apenas as colunas necessárias (SELECT coluna1, 
coluna2) em vez de SELECT * reduz a quantidade de dados lidos do disco e 
transferidos, o que é especialmente importante para tabelas com muitas colunas ou 
colunas com dados grandes (e.g., BLOBs, TEXTs). 
3. ANÁLISE CONTÍNUA COM EXPLAIN 
O comando EXPLAIN (ou EXPLAIN ANALYZE, dependendo do seu sistema de banco de 
dados) é sua ferramenta mais valiosa para o tuning. 
 Análise Pós-Tuning: Após implementar as mudanças de índice ou na consulta, sempre 
execute o EXPLAIN novamente para confirmar que as otimizações estão funcionando 
como esperado. Verifique se o EXPLAIN mostra que os índices estão sendo utilizados 
(Using index ou index scan) e se não há operações de varredura de tabela completa 
(Full Table Scan) onde um índice deveria ser usado. 
 Identificação de Gargalos: Use o EXPLAIN para identificar gargalos de desempenho, 
como operações de classificação de disco (Using filesort), criação de tabelas 
temporárias (Using temporary) ou varreduras completas de tabelas grandes. O 
EXPLAIN ANALYZE (PostgreSQL) ou EXPLAIN EXTENDED (MySQL) fornece detalhes 
adicionais sobre o tempo de execução real e o número de linhas processadas. 
4. COLETA E MANUTENÇÃO DE ESTATÍST ICAS DO BANCO DE DADOS 
Os otimizadores de consulta dependem de estatísticas atualizadas sobre os dados nas 
suas tabelas e índices para tomar decisões inteligentes sobre como executar as consultas. 
 Manutenção Regular: Certifique-se de que o sistema de banco de dados esteja coletando 
e atualizando suas estatísticas regularmente. Muitos bancos de dados fazem isso 
automaticamente, mas em ambientes de alto volume ou após grandes cargas de dados, 
pode ser necessário forçar a atualização manual (e.g., ANALYZE TABLE em MySQL, 
 
VACUUM ANALYZE em PostgreSQL). Estatísticas desatualizadas podem levar o otimizador 
a fazer escolhas subótimas (e.g., optar por uma varredura de tabela em vez de um índice 
porque as estatísticas sugerem que o índice não é seletivo). 
5. PARTICIONAMENTO DE TABELAS (CONSIDERAÇÃO AVANÇADA) 
Para tabelas extremamente grandes (milhões ou bilhões de linhas), o particionamento 
pode ser uma estratégia de tuning avançada. 
 Gerenciamento de Dados: O particionamento divide uma tabela grande em partes 
menores e mais gerenciáveis, com base em um critério específico (e.g., por data, por faixa 
de ID, por região). Isso pode melhorar o desempenho em consultas que visam apenas um 
subconjunto dos dados, pois o banco de dados só precisa escanear as partições relevantes, 
reduzindo o volume de dados a serem processados. 
 Manutenção: Facilita a manutenção, como backup e restauração, de partes específicas da 
tabela, e pode melhorar o desempenho de operações como TRUNCATE em partições 
antigas.