Ramos da Informática

SQL Server – Como utilizar indexes e melhorar performance

SQL Server - Como utilizar indexes e melhorar performance

Vamos entender da teoria à prática sobre como utilizar indexes para melhorar performance de suas Queries com SQL Server. Melhorando a performance, deixando mais rápido.

Vamos entender da teoria à prática sobre como utilizar indexes para melhorar performance de suas Queries com SQL Server. Melhorando a performance, deixando mais rápido.

Primeiro vamos a alguns conceitos:

Índice clusterizado

Este é um termo usado para indicar que o índice é a própria tabela de dados. Ou seja os dados da tabela são gravados na ordem deste índice, então a própria tabela é usada como índice. Isto obviamente tem vantagens e desvantagens mas em geral as vantagens superam as desvantagens. Obviamente os índices não clusterizados são aqueles que precisam de uma estrutura auxiliar para armazenar na ordem desejada. Usando a lógica fica claro que uma tabela só pode ter um índice clusterizado.

Para saber mais pode ler o artigo da Wikipedia ou a documentação do SQL Server que implementa esta técnica de forma mais explícita.

Então não há nenhum exagero nisto, pelo contrário, está prática é a mais comum quando o banco de dados a oferece.





Queremos criar melhores conteúdos:

Criamos uma pesquisa sobre o seu perfil profissional:
  1. Principais objetivos;
  2. Principais Problemas no seu dia a dia;
  3. Principais Alegrias.
Participe: Pesquisa Ramos da Informática – Queremos te conhecer melhor.

Índice multi-colunar

É extremamente comum ter várias colunas em um índice. Sempre que exista algum motivo para acessar os dados na ordem composta de mais de uma coluna, isto deve ser feito.

Leia também:

Lembrando que você não precisa usar todas as colunas de cada índice nas suas buscas. Claro que o índice só poderá ser utilizado com eficácia se na busca constar por inteiro as primeiras colunas. Então você buscar só pela primeira coluna utiliza o índice. Buscar só pela segunda coluna não tem como utilizá-lo. Aí a solução é ter um outro índice com a segunda coluna deste como a primeira de outro índice.

Índices para usar com LIKE podem funcionar bem quando você tem um texto seguido de %, mas se tiver um texto depois do símbolo %, o índice não poderá ajudar. É o mesmo princípio explicado acima. É uma quebra da sequência de busca.

Pense num dicionário como Aurélio. Você acha as palavras facilmente certo? Porque as palavras estão em um índice clusterizado. Você sabe a ordem e sabe que pode fazer uma busca semelhante à busca binária. Mas se você souber apenas o fim da palavra, você consegue fazer esta busca eficientemente?

Claro que colocar colunas adicionais só por colocar não vai ajudar nada. Tem que ter critério. Tem que entender esse processo de que no fundo quando você cria um índice com várias colunas está criando uma palavra enorme que faz sentido se estiver naquela ordem. E se você procurar binariamente ela possa ser encontrada facilmente. A busca funciona bem enquanto você tiver uma sequência de dados organizados logicamente.

Colocar colunas que não podem ser aproveitadas na ordem descrita no índice em nenhum tipo de query expressiva que será usada só fará consumir espaço no índice, é um contra-senso.

Não é fácil determinar isto sem ver o caso específico. Mesmo que possa ser útil para uma query, pode ser desnecessário. Em alguns casos o ganho pode ser muito pequeno para compensar. Ter experiência ajuda determinar isto. Mas pode atrapalhar. O jeito certo é testar. Ver se todas situações funciona bem com ou sem índice. Então a melhor resposta que posso te dar para o principal da sua pergunta é teste. Veja se as queries se beneficiam do índice.

Claro que no começo você vai perder tempo com índices que não ajudam em nada ou mesmo atrapalham. Se você for profissional na melhor acepção da palavra o tempo fará você selecionar melhor e evitar os testes desnecessários sem deixar de lado os que podem trazer resultados importantes. Mas sem testar nem mesmo os gênios da indexação de banco de dados podem garantir o que funciona bem em situações específicas.

E tenha em mente que o q é bom em um momento pode não ser mais no futuro. As queries podem mudar, a carga de utilização de cada uma muda, o padrão de dados pode fazer um índice bom ser ruim e e vice-versa. Não tem regra mágica.

Quando você tem um índice com UNIQUE dificilmente terá utilidade ter outras colunas depois da coluna com restrição de unicidade.

Note que algumas pessoas acham que colocar todas colunas em um índice ou criar um índice para cada coluna resolve todas situações. Além disto ser um desperdício óbvio não resolve todas situações (e nem deveria, só deve resolver o que será necessário e vai produzir resultados eficientes de verdade). É virtualmente impossível tratar todas as combinações já que as queries podem estar procurando por dados de forma parcial ou manipulada de alguma forma. Mesmo que restrinja as soluções possíveis só à colunas completas não é fácil combinar todas colunas a não ser que exitas pouco mais de 2 ou 3 colunas.

Vantagens e desvantagens do uso de índices em base de dados

Vantagens

Dá para notar que a grande vantagem mesmo é o primeiro item, os demais são colaterais, embora de grande importância também.

DOUTORES DO EXCEL, VOCÊ APRENDE EXCEL DO BÁSICO AO AVANÇADO E POWER BI

DOUTORES DO EXCEL, VOCÊ APRENDE EXCEL DO BÁSICO AO AVANÇADO E POWER BI

Desvantagens

É virtualmente impossível criar índices para qualquer chave a não ser em tabelas extremamente simples. Então é ilusão achar que índices resolverão todos os problemas. E mesmo que fosse possível, eles causariam mais mal do que bem.

Leia:

Você só deve criar índices quando eles realmente são necessários e fique provado que eles estão ajudando.

Dicas para escolher o que indexar seu SQL e deixar mais rápida suas consultas ao banco de dados SQL Server

É bom ressaltar que existem diversos tipos de índices. O mais comum é a árvore binária nas suas diversas variações, cada uma com uma situação mais adequada. Mas outros índices podem ser bem úteis também, como o hash onde a chave é determinada por uma fórmula e o acesso é feito direto por um índice posicional (mais comum em memória) ou índices invertidos muito utilizados para indexar textos onde as palavras contidas no texto são usadas como chaves e não dados inteiros como é comum em outros índices.

Recomendamos:

Indices desnecessários podem ser um problema

Basicamente é porque cada novo índice exige mais tempo para atualizar o banco em qualquer alteração que afete estes índices. Já vi gente sugerir fazer índices para tudo. Com apenas 6 ou 7 campos, para todas as combinações são necessários dezenas para centenas de índices e todos precisariam ser atualizados mesmo que altere apenas um campo. O tempo gasto pode afetar a escalabilidade do banco.

Em alguns casos a consulta pode ser prejudicada também. O espaço ocupado prejudicará o cachedos dados/índices que realmente são importantes. Além disto acessar primeiro o índice para depois acessar o dado tem um custo e ele pode ser maior que acessar os dados diretamente em certos padrões. E nem sempre o otimizador do sistema detecta corretamente se ele deve fazer o acesso direto.

Da mesma forma as recomendações automáticas dos sistemas SGDBs para criar índices nem sempre são corretas.

Mais tutoriais:

Além disto cada novo índice é um recurso a mais para dar manutenção. Viola o YAGNI. Inclusive dificulta upgrades no modelo de uma base de dados no sistema em produção.

Já vi “especialistas conceituados” dizerem que o ideal é criar índice para tudo e ir tirando os que não precisam. Não consigo imaginar como isto possa fazer sentido. É o pior caso de otimização prematura que já vi. Toda otimização deve ser criada quando você mediu e ficou claro que ela é necessária. Índice existe fundamentalmente para otimizar acesso aos dados.

Os sistemas de banco de dados mais completos possuem muitos recursos para obter as informações necessárias para a decisão correta. Estas informações podem ajudar a identificar índices desnecessários também. E como é um recurso para otimizar é preciso revisar com frequência, o que pode ser bom em um momento pode não ser mais no futuro.

Um exemplo típico de erro em criação de índice é quando ele é usado exclusivamente para geração de relatórios esporádicos. A perda para gerar um relatório esporádico sem a otimização do índice costuma causar menos prejuízo que o índice causará no sistema como um todo.

Mas um dos piores exageros que já vi, e acontece muito, é criar um índice para colA+colB e outro para colA. O primeiro pode ser usado para obter a mesma otimização que o segundo provê, tornando-o desnecessário.

Mas algo que preciso ressaltar sempre: medir é o mais importante. Eu e mesmo profissionais bem mais experientes que eu no assunto vivem tendo surpresas. O que parece ajudar muitas vezes prejudica mais.

Boa referência.

Para evitar entendimento errado da outra resposta (que foi removida mas acho que a informação ainda é relevante) pelas pessoas que estão aprendendo sobre o assunto vou colocar alguns pontos:

Está cheio de mitos em desenvolvimento de software e como consequência temos muitos softwares com problemas porque há a leitura de informações ilusórias sobre os assuntos. Deixar margem para entendimento errado causa mais mal do que bem.

E pra terminar:

 

VOCÊ ESTÁ NAS SEÇÕES:  Banco de Dados » SQL Server

Sair da versão mobile