Início / dba / Perguntas / 12922
Accepted
Rocky Singh
Asked: 2011-06-01 04:57:19 +0800 CST

Regra rígida e rápida para incluir colunas no índice

  • 772

Existe alguma regra rígida e rápida para decidir quais colunas e em que ordem devem ser colocadas em Incluído no índice não clusterizado. Eu estava lendo este post https://stackoverflow.com/questions/1307990/why-use-the-include-clause-when-creating-an-index e descobri isso para a seguinte consulta:

SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5

O pôster sugeriu fazer um índice assim:

CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(EmployeeID, DepartmentID)
  INCLUDE (Lastname)

aqui vem minha pergunta por que não podemos fazer um índice assim

CREATE NONCLUSTERED INDEX NC_EmpDep 
      ON Employee( EmployeeID, DepartmentID, LastName)

ou

    CREATE NONCLUSTERED INDEX NC_EmpDep 
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)

e o que leva o pôster a decidir manter a coluna LastName incluída. Por que não outras colunas? e como decidir em que ordem devemos manter as colunas lá?

sql-server sql-server-2005

4 respostas

  1. Best Answer

    Essa sugestão de índice por marc_s está errada. Eu adicionei um comentário. (E foi minha resposta aceita também!)

    O índice para esta consulta seria

    CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (Lastname, EmployeeID)

    Um índice é normalmente

    CREATE INDEX <name> ON <table> (KeyColList) INCLUDE (NonKeyColList)

    Onde:

    • KeyColList = Colunas de chave = usado para restrição de linha e processamento
      WHERE, JOIN, ORDER BY, GROUP BY etc
    • NonKeyColList = Colunas sem chave = usadas em SELECT e agregação (por exemplo, SUM(col)) após seleção/restrição
    • 49

  2. JNK e gbn deram ótimas respostas, mas também vale a pena considerar o quadro geral - não apenas focar em uma única consulta. Embora essa consulta específica possa se beneficiar de um índice (#1):

    Employee(DepartmentID) INCLUDE (Lastname, EmployeeID)

    Esse índice não ajuda em nada se a consulta mudar um pouco, como:

    SELECT EmployeeID, DepartmentID, LastName
FROM Employee
WHERE DepartmentID = 5 AND LastName = 'Smith'

    Isso precisaria do índice (# 2):

    Employee(DepartmentID, LastName) INCLUDE (EmployeeID)

    Imagine que você tivesse 1.000 funcionários no Departamento 5. Usando o índice #1, para encontrar todos os Smiths, você precisaria procurar em todas as 1.000 linhas do Departamento 5, pois as colunas incluídas não fazem parte da chave. Usando o índice nº 2, você pode procurar diretamente no Departamento 5, LastName Smith.

    O índice nº 2 é, portanto, mais útil para atender a uma variedade maior de consultas - mas o custo é uma chave de índice mais inchada, o que tornará as páginas não-folha do índice maiores. Cada sistema será diferente, então não há regra geral aqui.

    Como uma observação lateral, vale ressaltar que se EmployeeID fosse a chave de clustering para esta tabela - assumindo um índice clusterizado - então você não precisa incluir EmployeeID - ele está presente em todos os índices não clusterizados, o que significa que o índice #2 poderia apenas ser

    Employee(DepartmentID, LastName)
    • 19

  3. Não tenho certeza de como você conseguiu aquele primeiro. Para mim, para essa consulta, eu usaria:

    CREATE NONCLUSTERED INDEX NC_EmpDep 
  ON Employee(DepartmentID)
  INCLUDE (EmployeeID, Lastname)

    Não existe uma "regra rígida e rápida" para praticamente qualquer coisa no SQL.

    Mas, para o seu exemplo, o único campo que o índice usará é DepartmentIDporque está na WHEREcláusula.

    Os outros campos só precisam ser facilmente acessíveis a partir daí. Você seleciona com base nos DepartmentIDcampos INCLUDEno nó folha do índice.

    Você não quer usar seus outros exemplos porque eles não funcionariam para este índice.

    Pense em um índice como uma lista telefônica. A maioria das listas telefônicas são ordenadas por Sobrenome, Nome, Inicial do meio. Se você sabe o nome de alguém, mas não o sobrenome, a lista telefônica não serve para nada, pois você não pode pesquisar o primeiro nome com base na ordem do índice da lista telefônica.

    Os INCLUDEcampos são como o número de telefone, endereço, etc. outras informações para cada entrada no livro.

    EDITAR:

    Para esclarecer melhor por que não usar:

    CREATE NONCLUSTERED INDEX NC_EmpDep 
          ON Employee( EmployeeID, LastName)
INCLUDE (DepartmentID)

    Este índice só é útil se você tiver um EmployeeIDou AMBOS EmployeeID e LastNameem sua WHEREcláusula. Isso é praticamente o OPOSTO do que você precisa para esta consulta.

    • 7

  4. Acho que você ainda pode usar o índice (employee_id, department_id), mas teria que incluir uma linha 'dummy' na frase where, como: "employee_id = employee_id)

    • ter um índice em (employee_id, departemnent_id),
    • ter que pesquisar / restringir apenas em um department_id
    • sabendo que não usará o índice desde a ordem errada (ou as coisas mudaram até agora, e o seguinte "truque" não é mais necessário. Sou um "velho"?) .

    • Usar o truque "antigo"?

      selecione * em Employee emp
      onde emp.employee_id = emp.employee_id
      e emp.department_id = 5

    (Então, não estou focando na parte de inclusão aqui do Sobrenome, mas no sim/ou não uso da chave.)

    Atenciosamente,

    Miguel

    • 0

relate perguntas