Por que os bancos de dados não criam seus próprios índices automaticamente?

Atualizar

Isso agora está implementado no SQL Server Azure. Gera recomendações

e o gerenciamento de índice pode ser configurado para ser automático .

Ativar gerenciamento de índice automático

Você pode definir o SQL Database Advisor para implementar recomendações automaticamente. À medida que as recomendações forem disponibilizadas, elas serão aplicadas automaticamente. Como em todas as operações de índice gerenciadas pelo serviço, se o impacto no desempenho for negativo, a recomendação será revertida.

Resposta Original

Alguns bancos de dados já (mais ou menos) criam índices automaticamente.

No SQL Server, o plano de execução às vezes pode incluir um operador Index Spool onde o RDBMS cria dinamicamente uma cópia indexada dos dados. No entanto, este spool não é uma parte persistente do banco de dados mantido em sincronia com os dados de origem e não pode ser compartilhado entre as execuções de consultas, ou seja, a execução de tais planos pode acabar criando e descartando repetidamente índices temporários nos mesmos dados.

Talvez no futuro os RDBMSs tenham a capacidade de descartar dinamicamente e criar índices persistentes de acordo com a carga de trabalho.

O processo de otimização do índice é, no final das contas, apenas uma análise de custo-benefício. Embora seja verdade que os humanos possam ter mais informações sobre a importância relativa das consultas em uma carga de trabalho, não há motivo para que essas informações não possam ser disponibilizadas ao otimizador. O SQL Server já possui um administrador de recursos que permite que as sessões sejam classificadas em diferentes grupos de carga de trabalho com diferentes alocações de recursos de acordo com a prioridade.

Os DMVs de índice ausente mencionados por Kenneth não devem ser implementados cegamente, pois consideram apenas os benefícios de uma consulta específica e não fazem nenhuma tentativa de levar em consideração o custo do índice potencial para outras consultas. Também não consolida índices ausentes semelhantes. por exemplo, a saída deste DMV pode relatar índices ausentes A,B,CeA,B INCLUDE(C)

Algumas questões atuais com a ideia são

• A qualidade de qualquer análise automatizada que não crie realmente o índice dependerá muito da precisão do modelo de custeio.
• Mesmo no campo da análise automatizada, uma solução off-line poderá ser mais completa do que uma solução on-line, pois é imperativo que uma solução on-line não adicione uma grande sobrecarga de contabilidade ao servidor ativo e interfira em seu objetivo principal de executar consultas.
• Os índices criados automaticamente em resposta à carga de trabalho serão necessariamente criados em resposta a consultas que os considerariam úteis, portanto, ficarão para trás das soluções que criam os índices com antecedência.

Provavelmente, é razoável esperar que a precisão dos modelos de custeio melhore com o tempo, mas o ponto 2 parece mais complicado de resolver e o ponto 3 é inerentemente insolúvel.

No entanto, provavelmente a grande maioria das instalações não está nessa situação idealizada com uma equipe qualificada que monitora, diagnostica e antecipa (ou pelo menos reage a) mudanças nas cargas de trabalho continuamente.

O projeto AutoAdmin na Microsoft Research está em execução desde 1996

O objetivo deste projeto é tornar os bancos de dados autoajustáveis ​​e autoadministrados, explorando o conhecimento da carga de trabalho

A página inicial do projeto lista vários projetos intrigantes. Um é particularmente relevante para a questão aqui

Outro problema interessante surge quando não há DBA disponível (por exemplo, um banco de dados embutido ou uma pequena empresa). Em tais cenários, uma abordagem de ajuste de índice contínuo de baixo toque pode se tornar importante. Nós exploramos soluções ...[em] “ An Online Approach to Physical Design Tuning ” no ICDE 2007.

Os autores afirmam

Com recursos de DBMS cada vez mais comuns, como índices on-line, é atraente explorar soluções mais automáticas para o problema de design físico que avançam no estado da arte.

O artigo apresenta um algoritmo

Suas principais características são:

• À medida que as consultas são otimizadas, identificamos um conjunto relevante de índices candidatos que melhorariam o desempenho. Esse recurso permite que o processamento de consultas continue em paralelo com os índices criados em segundo plano.
• No tempo de execução, rastreamos os benefícios potenciais que perdemos por não ter esses índices candidatos e também a utilidade dos índices existentes na presença de consultas, atualizações e restrições de espaço.
• Depois de reunir “evidências” suficientes de que uma alteração no design físico é benéfica, acionamos automaticamente as criações ou exclusões de índices.
• A natureza online do nosso problema implica que geralmente ficaremos atrás de soluções ótimas que conhecem o futuro. No entanto, medindo cuidadosamente as evidências, garantimos que não sofreremos decisões “atrasadas” significativamente, limitando assim o valor da perda incorrida

A implementação do algoritmo permite a limitação em resposta a alterações na carga do servidor e também pode interromper a criação do índice se, durante a criação, a carga de trabalho mudar e o benefício esperado cair abaixo do ponto considerado válido.

A conclusão dos autores sobre o tema On- line versus ajuste físico tradicional.

Os algoritmos online deste trabalho são úteis quando os DBAs não têm certeza sobre o comportamento futuro da carga de trabalho ou não têm possibilidade de fazer uma análise ou modelagem abrangente. Se um DBA tiver informações completas sobre as características da carga de trabalho, uma análise estática e implantação por ferramentas existentes (por exemplo, [2, 3]) seria uma alternativa melhor.

As conclusões aqui são semelhantes às de outro artigo Autonomous Query-driven Index Tuning

Nossa abordagem não pode vencer o index advisor se toda a carga de trabalho for conhecida antecipadamente. No entanto, em ambientes dinâmicos com cargas de trabalho em constante evolução e alteração, a abordagem orientada a consultas produz melhores resultados.

O design do índice que você implementa é mais uma arte do que uma ciência. O RDBMS não é inteligente o suficiente para receber cargas de trabalho comuns e projetar uma estratégia de indexação inteligente. Cabe à intervenção humana (leia-se: DBA) analisar a carga de trabalho e determinar qual é a melhor abordagem.

Se não houvesse penalidade de ter índices, seria uma abordagem de espingarda apenas adicionar um número infinito de índices. Mas como a modificação de dados (INSERTS, UPDATES e DELETES) tem impacto nos índices habilitados em uma tabela, haverá essa sobrecarga variável desses índices.

É preciso design e estratégia humanos para criar índices de forma inteligente que maximizarão o desempenho de leitura, tendo a menor quantidade de sobrecarga de modificação de dados.

Na verdade, existem alguns bancos de dados que fazem isso. Por exemplo, o BigTable do Google e o SimpleDB da Amazon criam índices automaticamente (embora nenhum dos dois seja RDBMS) . Há também pelo menos um mecanismo MySQL RDBMS que faz isso. O SQL Server também rastreia os índices que acha que você deve criar , embora não chegue a criá-los de fato.

O problema é surpreendentemente difícil de corrigir, então não é de admirar que a maioria dos bancos de dados não os crie automaticamente (BigTable/SimpleDB se safa porque eles não permitem junções arbitrárias, o que torna as coisas significativamente mais fáceis) . Além disso, criar índices em tempo real é um processo demorado que requer acesso exclusivo a toda a tabela - definitivamente não é algo que você deseja que aconteça enquanto a tabela estiver online.

No entanto, dado o número de aplicativos da web LAMP que foram escritos por amadores que nem sabem o que é um índice , ainda acho que esse recurso seria benéfico para algumas pessoas.

Embora já existam algumas respostas extensas, elas parecem contornar a resposta real: os índices nem sempre são desejáveis.

Com a analogia do carro mencionada nos comentários, seria melhor dizer por que nem todos os carros são equipados com pacotes de esportes radicais? Em parte é uma despesa, mas também se deve ao fato de que muitas pessoas não precisam ou não querem pneus de perfil baixo e suspensão dura como pedra; é desnecessariamente desconfortável.

Então, talvez você tenha 1.000 leituras para cada inserção, por que não ter um índice criado automaticamente? Se a tabela é ampla e as consultas variadas, por que não ter várias? Talvez a confirmação seja crítica no tempo e as leituras não; nessas circunstâncias, pode ser inaceitável desacelerar sua inserção. Talvez você esteja trabalhando com espaço em disco limitado e não pode se dar ao luxo de ter índices adicionais consumindo o espaço que possui.

A questão é que os índices não são criados automaticamente porque não são a resposta para tudo. Projetar índices não é simplesmente dizer "ei, isso vai acelerar minhas leituras", há outros fatores a serem considerados.

Eles podem analisar consultas anteriores e sugerir/criar índices, mas isso não funciona de maneira ideal porque os índices atingem um equilíbrio para acelerar o que você deseja otimizar a um custo e o servidor não pode saber suas intenções.

Eles não são inteligentes, são um pedaço de código. Toda vez que você insere novos dados em um banco de dados, ele precisa encontrar um novo local para ele e um mapa para localizá-lo quando solicitado. A indexação parece mais fácil do que é, você acabou de dar um novo número a um novo bloco de dados? Bem, e se a próxima consulta não for sobre o último bloco de dados, mas sobre 36271 blocos anteriores? Você pode encontrá-lo facilmente com seu índice, certo? Mas e se a consulta incluir uma palavra como "pesca" a ser encontrada no antigo bloco 36271 feito em 1997? Ho? Nem uma palavra sobre a pesca no antigo artigo.

Se os dados chegassem ao banco de dados um por um, eles poderiam ser indexados dessa forma. Mas a indexação simples terá resultados errados e/ou desempenho lento mais cedo ou mais tarde...