Faço parte da equipe de banco de dados da minha empresa e atualmente estou enfrentando um dilema em relação à otimização e desempenho de consultas. Sempre que alguns de meus colegas se deparam com uma consulta lenta, a solução padrão é criar um índice para acelerá-la. Isso resultou em algumas tabelas com mais de 70 índices!
Pessoalmente, tendo a abordar esta questão de forma diferente. Em vez de criar novos índices a cada vez, muitas vezes me pego modificando os índices existentes, como adicionar uma coluna incluída adicional, etc., para acelerar uma consulta. Contudo, nem todos os membros da equipe adotam essa abordagem, levando a um número crescente de índices em nosso banco de dados.
Apesar desses esforços, notei uma desaceleração em outras operações DML à medida que o número de índices aumenta. Parece um beco sem saída: sem o índice, a consulta fica lenta, mas com o índice, todo o resto fica um pouco mais lento.
Estou procurando conselhos sobre como navegar nessa situação. Quantos índices são geralmente considerados “demais”? Existem práticas recomendadas ou estratégias para equilibrar a necessidade de consultas rápidas com o desempenho geral do banco de dados?
Quaisquer insights ou recursos seriam muito apreciados.
Obrigado!
Normalmente gosto de seguir uma diretriz 5x5. Cinco índices por tabela, com no máximo cinco campos por índice. Esta é uma diretriz, não um limite rígido. Às vezes encontro um caso de uso que garante sete campos em meu índice ou, às vezes, uma tabela que precisa de dez índices, etc. Independentemente disso, o ponto principal é que qualquer coisa vaga em torno dessa diretriz provavelmente será razoável, geralmente.
Mais de 70 índices em uma única tabela não parecem razoáveis, provavelmente para os padrões da maioria das pessoas. E se você descobrir que isso está afetando a velocidade de gravação nessa tabela, provavelmente é um sinal direto do seu sistema de que você tem muitos. Considere o seguinte: existem pelo menos 70 colunas na tabela?... se não, então há muito mais índices do que seria necessário para cobrir parcialmente todos os predicados possíveis que você possa imaginar.
Para mim, parece que provavelmente há muita sobreposição dos índices existentes e eles deveriam ser reavaliados. Eu não duvidaria que alguns possam ser consolidados em um único índice, enquanto outros talvez nem sejam mais usados. Uma boa ferramenta para detectar quais índices têm baixo uso é o
sp_BlitzIndex.Seria útil estar mais ciente do design do índice para não criar índices redundantes ou não utilizados.
O ajuste de consulta em vez do ajuste de índice como primeira opção também ajudaria às vezes. Nem todo problema de desempenho pode ser resolvido apenas por um índice. Às vezes, há uma maneira mais eficiente de reescrever a consulta.
Existem também outras ferramentas para materializar dados, como colunas computadas persistentes e visualizações indexadas .
Faça com que o aplicativo grave os dados preparados na forma necessária para minimizar a complexidade das consultas.
Reprojete a estrutura da tabela para ser mais eficiente. Por exemplo, talvez uma tabela ampla possa ser dividida em várias tabelas menores, especialmente se os dados entre essas colunas não precisarem ser transacionalmente consistentes entre si. Então o número de índices em uma única tabela pode ser reduzido.
Se você precisar adicionar outro índice a uma tabela, considere se é possível torná-lo um Filtered Index , de forma que ele se aplique apenas a um subconjunto de dados, agilizando as gravações na tabela.
As visualizações materializadas são uma boa solução para todos os fins se houver consultas lentas específicas que as pessoas desejam mais rapidamente. Eles apenas calculam a resposta quando as tabelas dependentes são atualizadas para que quando você quiser os resultados, eles já estejam prontos.
Outra mudança específica na arquitetura seriam as tabelas particionadas. Um exemplo de cenário em que eles são úteis é digamos que todos os dias você obtém dados sobre 1.000 coisas. Você tem uma mesa com, digamos,
date, thing_id, attr1, attr2, attr3. Como novos dados chegam diariamente, quando você os insere na tabela, eles são agrupados fisicamente por dia. Agora, digamos que quando você consulta os dados, geralmente deseja consultar apenas uma das coisas. Quando você faz essa consulta, mesmo que esteja bem indexada, mesmo que o armazenamento esteja em um SSD*, ela terá que obter resultados fisicamente da unidade que então descarta, o que significa que terá que ler mais. As tabelas particionadas fazem com que existam (provavelmente a nomenclatura errada) tabelas filhas para cada uma das chaves de partição. Dessa forma, quando você faz uma inserção, ele coloca cada novo dado em sua partição apropriada para que dados semelhantes estejam fisicamente próximos de outros dados semelhantes. Quando você faz uma consulta filtrando por essa partição, ela pode obter esses dados muito mais rapidamente do que se não fosse particionada.*Mesmo que os SSDs não tenham um cabeçote físico que se mova para ler os dados, eles ainda têm um tamanho mínimo de bloco para leitura.