De certa forma, já sei a resposta para essa pergunta, mas sempre sinto que preciso aprender mais sobre o assunto.
Meu entendimento básico é que, de um modo geral, um único índice que inclui apenas todos os campos que você pode consultar/classificar em um determinado momento provavelmente não será útil, mas já vi esse tipo de coisa. Alguém pensou: "Bem, se apenas colocarmos tudo isso em um índice, o banco de dados pode usá-lo para encontrar o que precisa", sem nunca ter visto um plano de execução para algumas das consultas reais sendo executadas.
Imagine uma tabela assim:
id int pk/uid
name varchar(50)
customerId int (foreign key)
dateCreated datetime
Posso ver um único índice incluindo os campos name, customerIde .dateCreated
Mas meu entendimento é que tal índice não seria utilizado em uma consulta como, por exemplo:
SELECT [id], [name], [customerId], [dateCreated]
FROM Representatives WHERE customerId=1
ORDER BY dateCreated
Para tal consulta, parece-me que uma ideia melhor seria um índice incluindo os campos customerIde , com o campo sendo 'primeiro'. Isso criaria um índice que teria os dados organizados de forma que essa consulta pudesse encontrar rapidamente o que precisa - na ordem em que precisa.dateCreatedcustomerId
Outra coisa que vejo, talvez com tanta frequência quanto a primeira, são os índices individuais em cada campo; então, um em cada namee customerIdcampos dateCreated.
Ao contrário do primeiro exemplo, esse tipo de arranjo me parece às vezes pelo menos parcialmente útil; o plano de execução da consulta pode mostrar que pelo menos está usando o índice no customerIdpara selecionar os registros, mas não está usando o índice com o dateCreatedcampo para ordená-los.
Eu sei que esta é uma pergunta ampla, porque a resposta específica para qualquer consulta específica em qualquer conjunto específico de tabelas geralmente é ver o que o plano de execução diz que fará e, caso contrário, levar em consideração as especificidades da(s) tabela(s) e consultas conta. Além disso, sei que depende da frequência com que uma consulta pode ser executada, em oposição à sobrecarga de manter um índice específico para ela.
Mas suponho que o que estou perguntando é como um 'ponto de partida' geral para índices, a ideia de ter índices específicos para consultas específicas e frequentes e os campos nas cláusulas WHERE ou ORDER BY faz sentido?
Você está certo em que sua consulta de exemplo não usaria esse índice.
O planejador de consulta considerará o uso de um índice se:
Não poderá fazer uso de índices que comecem com um campo não utilizado pela consulta.
Então, para o seu exemplo:
consideraria índices como:
mas não:
Se encontrasse ambos
[customerId]e[customerId], [dateCreated], [name]sua decisão de preferir um sobre o outro dependeria das estatísticas do índice, que dependem de estimativas do saldo de dados nos campos. Se[customerId], [dateCreated]fosse definido, ele deveria preferir isso aos outros dois, a menos que você dê uma dica de índice específica em contrário.Também não é incomum ver um índice definido para cada campo em minha experiência, embora isso raramente seja ideal, pois o gerenciamento extra necessário para atualizar os índices na inserção/atualização e o espaço extra necessário para armazená-los são desperdiçados quando metade do eles podem nunca ser usados - mas, a menos que seu banco de dados veja cargas pesadas de gravação, o desempenho não será ruim, mesmo com os índices em excesso.
Índices específicos para consultas frequentes que, de outra forma, seriam lentos devido à verificação de tabela ou índice geralmente são uma boa ideia, embora não exagere, pois você pode trocar um problema de desempenho por outro. Se você definir
[customerId], [dateCreated]como um índice, por exemplo, lembre-se de que o planejador de consulta poderá usá-lo para consultas que usariam um índice apenas[customerId]se presente. Embora usar apenas[customerId]seja um pouco mais eficiente do que usar o índice composto, isso pode ser mitigado ao acabar tendo dois índices competindo por espaço na RAM em vez de um (embora se todo o seu conjunto de trabalho normal se encaixar facilmente na RAM, essa competição de memória extra pode não ser um problema).Para responder à sua pergunta original, sim, os índices devem ser projetados em torno das consultas , não apenas da tabela . A ordem dos campos no índice é de vital importância. Projetar um único índice para ser ideal para várias consultas é mais difícil e você terá que fazer compensações.
Em relação ao seu segundo ponto, sim, um monte de índices em campos individuais é irritantemente comum. Eu vejo isso o tempo todo em meu ambiente e geralmente é um sinal de alerta para mim que a equipe de desenvolvimento não trabalhou com um DBA para projetar índices adequados.
Minha estratégia para criar índices é indexar:
Então, para o seu exemplo:
Eu provavelmente criaria um índice em (CustomerID, dateCreated) INCLUDE (id, name). Esse índice de cobertura significa que a consulta nunca precisa atingir a tabela original, melhorando muito o desempenho.
Este exemplo é quase simples demais , no entanto. Um índice ingênuo em apenas (CustomerID) funcionaria quase tão bem (supondo que cada cliente tenha apenas um único representante, portanto, apenas uma única pesquisa de marcador na tabela será necessária). Também pode ser benéfico fazer um índice clusterizado em (CustomerID, ID), dependendo de quais outras consultas são executadas na tabela.