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

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)

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