sql-server - tabla - indices en sql server 2014

¿Por qué usar la cláusula INCLUDE al crear un índice? (7)

En esta discusión se está perdiendo el punto importante: la pregunta no es si las "columnas sin clave" son mejores para incluir como columnas de índice o como columnas incluidas .

La pregunta es qué tan costoso es usar el mecanismo de inclusión para incluir columnas que no son realmente necesarias en el índice . (Por lo general no forma parte de las cláusulas where, pero a menudo se incluye en las selecciones) Entonces tu dilema es siempre:

Usar índice en id1, id2 ... idN solo o
Use index en id1, id2 ... idN plus include col1, col2 ... colN

Donde: id1, id2 ... idN son columnas que se usan a menudo en las restricciones y col1, col2 ... colN son columnas que se seleccionan a menudo, pero generalmente no se usan en las restricciones

(La opción de incluir todas estas columnas como parte de la clave de índice siempre es tonta (a menos que también se usen en restricciones), ya que siempre sería más caro mantenerlas, ya que el índice debe actualizarse y ordenarse incluso cuando "claves" no han cambiado).

¿Así que usa la opción 1 o 2?

Respuesta: Si su tabla rara vez se actualiza, en su mayoría se inserta o se elimina de, entonces es relativamente económico utilizar el mecanismo de inclusión para incluir algunas "columnas activas" (que se usan a menudo en las selecciones, pero que no se usan con frecuencia en las restricciones), ya que las inserciones / eliminaciones requieren que el índice se actualice / ordene de todos modos y, por lo tanto, poca sobrecarga adicional se asocia con el almacenamiento de unas cuantas columnas adicionales al actualizar el índice. La sobrecarga es la memoria adicional y la CPU utilizada para almacenar información redundante en el índice.

Si las columnas que considera agregar como columnas incluidas se actualizan a menudo (sin que se actualicen las columnas de clave de índice), o si son tantas que el índice se acerca a una copia de su tabla, use la opción 1 Yo sugeriría! Además, si agregar ciertas columnas de inclusión no hace ninguna diferencia de rendimiento, es posible que desee omitir la idea de agregarlas :) ¡Verifique que sean útiles!

El número promedio de filas por los mismos valores en las claves (id1, id2 ... idN) también puede ser de cierta importancia.

Observe que si una columna, que se agrega como una columna incluida del índice, se usa en la restricción : Mientras se pueda usar el índice como tal (basado en la restricción contra las columnas de clave de índice), entonces el servidor SQL Server está coincidiendo la restricción de columna contra el índice (valores de nodo de hoja) en lugar de recorrer la tabla de forma costosa.

Mientras estudiaba para el examen 70-433, noté que puedes crear un índice de cobertura de una de las siguientes dos formas.

CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)

- O -

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)

La cláusula INCLUDE es nueva para mí. ¿Por qué lo usaría y qué pautas sugeriría para determinar si crear un índice de cobertura con o sin la cláusula INCLUDE?

Hay un límite para el tamaño total de todas las columnas en línea en la definición del índice. Dicho esto, sin embargo, nunca he tenido que crear un índice tan amplio. Para mí, la mayor ventaja es el hecho de que puede cubrir más consultas con un índice que ha incluido columnas, ya que no tienen que definirse en ningún orden en particular. Pensar es como un índice dentro del índice. Un ejemplo sería el StoreID (donde StoreID es de baja selectividad, lo que significa que cada tienda está asociada con una gran cantidad de clientes) y luego los datos demográficos del cliente (Apellido, Nombre, Fecha de nacimiento): Si solo alinea esas columnas en este orden (StoreID, Apellido) , Nombre, DOB), solo puede buscar de manera eficiente clientes para los que sepa StoreID y Apellido.

Por otro lado, definir el índice en StoreID e incluir las columnas DName, FirstName, DOB le permitirá, en esencia, hacer dos predicados de indexación de búsquedas en StoreID y luego buscar el predicado en cualquiera de las columnas incluidas. Esto le permitiría cubrir todas las posibles permutaciones de búsqueda siempre que empiece con StoreID.

Las columnas de índice básico están ordenadas, pero las columnas incluidas no están ordenadas. Esto ahorra recursos al mantener el índice, al mismo tiempo que permite proporcionar los datos en las columnas incluidas para cubrir una consulta. Por lo tanto, si desea cubrir consultas, puede colocar los criterios de búsqueda para ubicar filas en las columnas ordenadas del índice, pero luego "incluir" columnas adicionales, sin clasificar, con datos que no sean de búsqueda. Definitivamente, ayuda a reducir la cantidad de clasificación y fragmentación en el mantenimiento del índice.

Las razones por las que (incluidos los datos en el nivel de hoja del índice) se explicaron bien. La razón por la que da dos sacudidas sobre esto, es que cuando ejecuta su consulta, si no tiene las columnas adicionales incluidas (nueva característica en SQL 2005), SQL Server tiene que ir al índice agrupado para obtener las columnas adicionales. lo que lleva más tiempo y agrega más carga al servicio de SQL Server, los discos y la memoria (la memoria caché del búfer para ser específico) a medida que se cargan en la memoria nuevas páginas de datos, lo que posiblemente elimina otros datos que se necesitan con más frecuencia fuera de la memoria caché del búfer.

Si la columna no está en WHERE/JOIN/GROUP BY/ORDER BY , pero solo en la lista de columnas en la cláusula SELECT .

La cláusula INCLUDE agrega los datos en el nivel más bajo / hoja, en lugar de en el árbol de índice. Esto hace que el índice sea más pequeño porque no es parte del árbol.

INCLUDE columns no son INCLUDE columns clave en el índice, por lo que no están ordenadas. Esto significa que no es realmente útil para predicados, clasificación, etc. como mencioné anteriormente. Sin embargo, puede ser útil si tiene una búsqueda residual en unas pocas filas de la (s) columna (s) clave (s)

Otro artículo de MSDN con un ejemplo trabajado.

Una consideración adicional que no he visto en las respuestas ya dadas, es que las columnas incluidas pueden ser de tipos de datos que no están permitidos como columnas de clave de índice, como varchar (max).

Esto le permite incluir tales columnas en un índice de cobertura. Hace poco tuve que hacer esto para proporcionar una consulta generada por nHibernate, que tenía muchas columnas en SELECT, con un índice útil.

Usaría INCLUDE para agregar una o más columnas al nivel de hoja de un índice no agrupado, si al hacerlo puede "cubrir" sus consultas.

Imagine que necesita consultar la ID de un empleado, la ID de departamento y el apellido.

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

Si tiene un índice no agrupado en (EmployeeID, DepartmentID), una vez que encuentre los empleados para un departamento determinado, ahora tiene que hacer una "búsqueda de marcadores" para obtener el registro completo de empleados, solo para obtener la columna del último nombre . Eso puede ser bastante costoso en términos de rendimiento, si encuentra muchos empleados.

Si hubieras incluido ese apellido en tu índice:

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

entonces toda la información que necesita está disponible en el nivel de hoja del índice no agrupado. Solo al buscar en el índice no agrupado y encontrar a sus empleados para un departamento determinado, tiene toda la información necesaria y la búsqueda de marcadores para cada empleado que se encuentra en el índice ya no es necesaria -> ahorra mucho tiempo.

Obviamente, no puede incluir todas las columnas en todos los índices no agrupados, pero si tiene consultas a las que les falta una o dos columnas para "cubrirlas" (y que se usan mucho), puede ser muy útil incluirlas. en un índice no agrupado adecuado.