SQL – Como utilizar indexes para melhorar performance das Queries

SQL – Como utilizar indexes para melhorar performance das Queries

6 de junho de 2019 0 Por Ramos de Souza Janones
Powered by Rock Convert

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

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.

Í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.

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.

Curso completo de Games, inclusive Realidade Aumentada.Powered by Rock Convert

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

Vantagens

  • Melhora a performance de consulta em muitos casosOs acessos aos dados são enormemente reduzidos. A forma como o índice é montado permite busca em uma parte dos dados. O mais comum é a utilização de árvore binária mantendo os dados ordenados (mas existem outros tipos), assim uma busca binária pode ocorrer com complexidade O(logN), ou seja, você consegue achar uma informação disponível em índice em escala maior com o aumento de dados. Exemplo: Se tiver 50 linhas você consegue chegar onde quer em até 7 passos, sem o índice demoraria até 50. Com mais de um bilhão de linhas, você alcança o que deseja usando índice em pouco mais de 30 passos. A diferença é brutal.
  • Pode trazer dados específicos de forma mais rápidaSe o que você está pedindo para trazer (fetch) está todo em índice é possível que só uma consulta ao índice seja suficiente para obter os dados, sem precisar consultar a tabela de dados. E como os índices são menores e utilizados com mais frequência tendem a ficar em memória. Isto pode ser uma enorme vantagem.
  • Permite o acesso de dados ordenados sem o custo de realizar a ordenação quando é necessárioAté para obter a performance indicada no primeiro item os dados são naturalmente ordenados e isto é muitas vezes o que se deseja. Quando se sabe que vai utilizar muitas vezes o acesso sequencial de dados em determinada ordem, o índice vai ajudar muito. Caso contrário, uma tabela temporária (tem formas de otimizar isto, mas só para ajudar um pouco) deverá ser criada e todo o processo de ordenação deverá ser feito na hora. E ele é bem custoso.
  • É fácil garantir que determinadas informações chaves não estejam duplicadasComo a busca é rápida e simples, descobrir se uma chave já existe é uma operação muito barata, pelo menos em comparação a descobrir o mesmo sem índice.

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

  • Piora a performance em escritas de dados no banco de dadosToda vez que uma informação chave for modificada (inserida, alterada, deletada) obrigará a escrita no índice. E o índice pode ser interpretado como uma tabela adicional escondida no banco de dados. E se a informação modificada está presente em várias chaves (vários índices), todos eles deverão ser alterados (em inclusão e remoção, todos sempre são afetados, ainda que seja possível otimizar para a remoção, sob pena de encarecer o custo de leitura). A alteração do índice implica em acesso de leitura e escrita nele, apesar de ser uma operação eficiente se comparada com o acesso direto à tabela, não deixa de ter um custo adicional.
  • Aumenta o consumo de espaço para armazenamento do banco de dados (memória e disco)É claro que esta tabela adicional de chaves de índice vai ocupar um espaço extra também. Costuma ser um espaço menor que a tabela de dados original mas existe um custo extra. Se houver muitos índices é possível que o espaço seja até maior que a tabela original. Com muito índice fica difícil colocar tudo na memória.
  • Aumenta a necessidade de manutenção interna no banco de dadosIsto é um pouco dependente da implementação mas é comum que páginas de chaves sejam abandonadas conforme elas vão sendo alteradas. Além disto o DBA pode ter mais elementos com que se preocupar.
  • Pode diminuir a performance de consultasNão há garantias que todas as consultas serão mais rápidas com o uso de índices. Como há uma operação adicional para acesso ao índice antes do acesso ao dados principal é possível que a soma do tempo gasto nas operações seja maior que o acesso só ao dado principal mesmo que o acesso no principal sem índice seja teoricamente menos eficiente. Isto é mais comum quando o volume de dados é pequeno mas isto também é verdade em casos de consultas complexas ou onde uma grande porção dos dados da tabela serão retornados em qualquer ordem.

É 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. 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

  • Evite criar muitos índices se há muita escrita nas tabelas envolvidas, índice ajuda muito a dar performance para leitura, mas prejudica a escrita.
  • Sempre análise o padrão de uso para escolher o melhor tipo de índice. SGDBs modernos permitem índices que ajudam certos tipos de acesso OLTP ou OLAP.
  • Chaves menores sem repetições e não nulas costumam ser melhores escolhas. Para os casos onde isto não é possível veja se o DBMS permite filtrar ser chaves.
  • Quando usar mais de uma coluna escolher a ordem pode ajudar muito nos resultados de mais de uma consulta.
  • Meça antes de criar um índice e evite-os em tabelas pequenas demais.
  • O índice primário deve ser preferencialmente sequencial e imutável, além da obviedade de ser chave única.

É 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.

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.

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:

  • A perda de performance causada pela criação de índices desnecessários se dá em qualqueroperação que altere um dado contido nas chaves do índice. Não importa se é INSERTUPDATE e até mesmo DELETE dependendo da implementação. Alterações que não afetam a chave do índice não causam problemas de performance. Em sistemas de banco de dados que usam alguma forma de MVCC qualquer alteração acaba afetando pelo menos o índice da chave primária (que talvez seja clusterizado) já que o dado é copiado para outro local.
  • A performance do SELECT também pode ser afetada negativamente conforme citado acima na minha resposta. Não ocorre com frequência, principalmente porque o planejador costuma evitar o uso do índice nestes casos, mas pode.
  • Já a criação do índice beneficia qualquer operação de acesso aos dados que o planejador do banco de dados identifique que seja útil. Normalmente isto ocorre em comparações do WHEREmas não em qualquer comparação (isto é importante). Há casos que o índice não ajuda. Índice não é mágico, ele tem uma organização que otimizam algumas relações, não todas. Ocorre também quando se usa ORDER BYJOIN e em quando há uso de algumas funções agregadoras. Mas é bom ficar claro que só uma medição pode garantir que haverá ganho. É claro que a experiência pode fazer o desenvolvedor do banco de dados identificar casos simples, mas é preciso ter cuidado. O bom nadador costuma se arriscar e morrer afogado mais que o nadador que tem medo de água desconhecida. Regras gerais, como sempre, podem causar mais mal do que bem. Sempre tem que analisar o caso específico com informações específicas para o caso.Exemplos onde o índice provavelmente não vai ajudar: WHERE colA = colB / 2 ou WHERE colA LIKE '%Silva%'.
  • De qualquer forma o ganho ocorre em qualquer das operações de acesso aos dados que tenha algum uso das cláusulas acima. Claro que em UPDATE e DELETE apenas o WHERE e o ORDER BYé relevante.

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.

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

Siga os bons!

Ramos de Souza Janones

Janones, é um empreendedor brasileiro apaixonado por empreendedorismo e tecnologia. Ao longo dos anos trabalhando com o desenvolvimento de softwares desktop desde a linguagem Clipper, passando pelo Delphi e atualmente com Java.

Optou pela formação de Publicidade e Marketing por sua segunda empresa de tecnologia ter participado do "boom" da internet nos anos 90 e na procura de melhorar seus conhecimentos em negócios.

Em razão da principal formação e profundos conhecimentos em programação e banco de dados, é capaz de realizar o desenvolvimento de aplicativos web, desktop e mobile com maior criatividade e inovação que profissionais de desenvolvimento com uma formação única e mais especifica, dedicada somente ao desenvolvimento de softwares.

Com toda sua experiência com empresas de software, sua formação e paixão por negócios escreveu o livro "Marketing para Empresas e Profissionais de Software", publicado pela editora carioca Ciência Moderna em 2012. Além de outros livros sobre programação.

Últimos posts por Ramos de Souza Janones (exibir todos)

Sumário
SQL - Como utilizar indexes para melhorar performance das Queries
Nome do artigo
SQL - Como utilizar indexes para melhorar performance das Queries
Descrição
Vamos entender da teoria à prática sobre como utilizar indexes para melhorar performance de suas Queries com SQL Server. Melhorando a performance.
Autor
Nome
Ramos da Informática
Logo



Frontend Do Zero Ao Profissional