¿Qué importancia tiene el orden de las columnas en los índices?

El orden de las columnas es crítico. Ahora qué orden es correcto depende de cómo se va a consultar. Un índice se puede usar para hacer una búsqueda exacta o un escaneo de rango. Una búsqueda exacta es cuando se especifican valores para todas las columnas en el índice y la consulta aterriza exactamente en la fila que le interesa. Para busca el orden de las columnas es irrelevante. Un análisis de rango es cuando solo se especifican algunas columnas, y en este caso cuando el orden se vuelve importante. SQL Server puede usar un índice para un análisis de rango solo si se especifica la columna más a la izquierda, y luego solo si se especifica la siguiente columna más a la izquierda, y así sucesivamente. Si tiene un índice en (A, B, C) se puede usar para escanear el rango para A=@a, para A=@a AND B=@b pero no para B=@b, para C=@c niB=@b AND C=@c. El caso A=@a AND C=@c es mixto, ya que en la porción A=@a usará el índice, pero el C=@c no (la consulta escaneará todos los valores B para A=@a, no 'saltará' a C=@c). Otros sistemas de bases de datos tienen el llamado operador 'skip scan' que puede tomar alguna ventaja de columnas internas en un índice cuando las columnas externas no están especificadas.

Con ese conocimiento en la mano, puede mirar las definiciones del índice de nuevo. Un índice en (MostSelective, SecondMost, Least) será efectivo solo cuando se especifique la columna MostSelective. Pero siendo eso lo más selectivo, la relevancia de las columnas internas se degradará rápidamente. Muy a menudo encontrará que un mejor índice está en (MostSelective) include (SecondMost, Least) o en (MostSelective, SecondMost) include (Least). Debido a que las columnas internas son menos relevantes, colocando columnas de baja selectividad en tales posiciones correctas en el índice no hace más que ruido para una búsqueda, por lo que tiene sentido moverlos fuera de las páginas intermedias y mantenerlos solo en las páginas de hoja, para fines de cobertura de consultas. En otras palabras, moverlos a INCLUIR. Esto se vuelve más importante a medida que aumenta el tamaño de la columna Least. La idea es que este índice solo puede beneficiar a las consultas que especifican MostSelective ya sea como un valor exacto o un rango, y que la columna sea la más selectiva ya restringe las filas candidatas a grandes grado.

Por otro lado, un índice en (Least, SecondMost, MostSelective) puede parecer un error, pero en realidad es un índice bastante poderoso. Debido a que tiene la columna Least como su consulta más externa, se puede usar para consultas que tienen que agregar resultados en columnas de baja selectividad. Tales consultas son frecuentes en OLAP y almacenes de datos de análisis, y aquí es exactamente donde dichos índices tienen un muy buen caso a su favor. Tales índices en realidad son excelentes índices agrupados , exactamente porque organice el diseño físico en grandes trozos de filas relacionadas (mismo valor Least, que generalmente indican algún tipo de categoría o tipo) y facilitan las consultas de análisis.

Así que, desafortunadamente, no hay un orden 'correcto'. No debe seguir ninguna receta de cortador de galletas, sino analizar el patrón de consulta que va a usar contra esas tablas y decidir qué orden de columna de índice es el correcto.

Debe poner las columnas que serán las más selectivas al principio de la declaración de índice.

Correcto. Los índices pueden ser compuestos-compuestos de múltiples columnas-y el orden es importante debido al principio más a la izquierda. La razón es que la base de datos comprueba la lista de izquierda a derecha, y tiene que encontrar una referencia de columna correspondiente que coincida con el orden definido. Por ejemplo, tener un índice en una tabla de direcciones con columnas:

• Dirección
• Ciudad
• Estado

Cualquier consulta que utilice la columna address puede utilizar el índice, pero si la consulta solo tiene city y/o state referencias - el índice no se puede utilizar. Esto se debe a que la columna más a la izquierda no está referenciada. El rendimiento de la consulta debe indicarle cuál es el óptimo: índices individuales o compuestos múltiples con diferentes órdenes. Buena lectura: El punto de inflexión, por Kimberley Tripp

Como Remus dice, depende de tu carga de trabajo.

Sin embargo, quiero abordar un aspecto engañoso de la respuesta aceptada.

Para las consultas que están realizando una búsqueda de igualdad en todas las columnas del índice no hay diferencia significativa.

Lo siguiente crea dos tablas y las rellena con datos idénticos. La única diferencia es que una tiene las claves ordenadas de más a menos selectivas y la otra al revés.

CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);
CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);

CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least);
CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective);

INSERT INTO Table1 (MostSelective, SecondMost, Least)
output inserted.* into Table2
SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, '~'
FROM master..spt_values
WHERE type = 'P' AND number >= 0
ORDER BY number;

Ahora haciendo una consulta contra las dos mesas...

SELECT *
FROM   Table1
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~';

SELECT *
FROM   Table2
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~'; 

... Ambos usan una multa de índice y a ambos se les da exactamente el mismo costo.

El arte ASCII en la respuesta aceptada no es de hecho cómo se estructuran los índices. Las páginas de índice de la Tabla1 están representadas a continuación (haga clic en la imagen para abrirla a tamaño completo).

Las páginas de índice contienen filas que contienen la clave completa (en este caso, en realidad hay una columna de clave adicional anexada para el identificador de fila, ya que el índice no se declaró único, pero puede descartarse puede encontrar más información al respecto aquí).

Para la consulta anterior, SQL Server no se preocupa por la selectividad de las columnas. Hace una búsqueda binaria de la página raíz y descubre que la Clave (PPP...,3,~ ) es >=(JJJ...,1,~ ) y < (SSS...,3,~ ) por lo que debe leer página 1:118. Luego hace una búsqueda binaria de las entradas clave en esa página y localiza la página de hoja para viajar hacia abajo a.

Alterar el índice en orden de selectividad no afecta ni el número esperado de comparaciones de claves de la búsqueda binaria ni el número de páginas que necesitan ser navegadas para hacer una búsqueda de índice. En el mejor de los casos, podría acelerar ligeramente la comparación clave en sí.

A veces ordenar primero el índice más selectivo tendrá sentido para otras consultas en su carga de trabajo.

Por ejemplo, si la carga de trabajo contiene consultas de los siguientes forma.

SELECT * ... WHERE  MostSelective = 'P'

SELECT * ...WHERE Least = '~'

Los índices anteriores no cubren ninguno de ellos. MostSelective es lo suficientemente selectivo como para hacer un plan con una búsqueda y búsquedas que valga la pena, pero la consulta contra Least no lo es.

Sin embargo, este escenario (búsqueda de índice no cubriente en subconjunto de la(s) columna (s) inicial (es) de un índice compuesto) es solo una posible clase de consulta que puede ser ayudada por un índice. Si en realidad nunca busca por MostSelective por sí solo o una combinación de MostSelective, SecondMost y siempre busca por una combinación de los tres columnas entonces esta ventaja teórica es inútil para usted.

Inversamente consultas como

SELECT MostSelective,
       SecondMost,
       Least
FROM   Table2
WHERE  Least = '~'
ORDER  BY SecondMost,
          MostSelective 

Sería ayudado por tener el orden inverso de la comúnmente prescrita - ya que cubre la consulta, puede soportar una búsqueda y devuelve filas en el orden deseado para arrancar.

Así que este es un consejo que se repite a menudo, pero a lo sumo es una heurística sobre el beneficio potencial de otras consultas - y no es un sustituto para realmente mirar su carga de trabajo.