Banco de Dados – Índices desnecessários no banco são um problema?

mm

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

Basicamente 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 cache dos 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.

Compartilhe.

PinIt
Top