Nuestros investigadores estrellas agotaron sus reservas de café, por su búsqueda a tiempo completo por la respuesta, hasta que Adán halló la respuesta en GitHub así que ahora la compartimos aquí.
Solución:
El almacenamiento interno de índices utiliza una estructura de árbol B y consta de “páginas de índice” (la raíz y todas las páginas intermedias) y “páginas de datos de índice” (sólo las páginas hoja).
Tenga en cuenta que no confunda las “páginas de datos de índice” con las “páginas de datos” (páginas hoja de índices agrupados) que almacenan la mayoría de las columnas de datos reales.
- Solo las columnas de índice se almacenan en las páginas de índice.
- Colocando algunas columnas en el
INCLUDEsección, menos datos por índice key se almacena en cada página. - Lo que significa que se necesitan menos páginas para contener el índice keys. (Facilitando el almacenamiento en caché de estas páginas de uso frecuente en la memoria durante más tiempo.)
- Y posiblemente menos niveles en el árbol. (En tal caso, los beneficios de rendimiento pueden ser mucho mayores porque cada recorrido de nivel de árbol es otro acceso al disco.)
Cuando se utiliza un índice, el índice key se utiliza para navegar a través de las páginas de índice hasta la página de datos de índice correcta.
- Si el índice tiene
INCLUDEcolumnas, esos datos están disponibles de inmediato si la consulta los necesita. - Si la consulta requiere columnas que no están disponibles en el índice keys o la
INCLUDEcolumnas, entonces se requiere una “búsqueda de marcadores” adicional a la fila correcta en el índice agrupado (o montón si no hay un índice agrupado definido).
Algunas cosas a tener en cuenta que, con suerte, resuelven parte de su confusión:
- Si el keys de su índice y filtros en su consulta no son selectivo suficiente, entonces el índice será ignorado (independientemente de lo que haya en su
INCLUDEcolumnas). - Cada índice que crea tiene una sobrecarga para las declaraciones INSERT y UPDATE; más aún para índices “más grandes”. (Más grande se aplica a
INCLUDEcolumnas también.) - Entonces, si bien en teoría podría crear una multitud de grandes índices con columnas de inclusión para que coincidan con todas las permutaciones de las rutas de acceso, sería muy contraproducente.
Vale la pena señalar que antes INCLUDE se agregaron columnas como una característica:
- Era un ‘truco’ común de ajuste de índice para expandir el keys de un índice para incluir columnas que no eran necesarias en el índice / filtro. (Conocido como índice de cobertura).
- Estas columnas se requerían comúnmente en columnas de salida o como columnas de referencia para uniones a otras tablas.
- Esto evitaría las infames “búsquedas de marcadores”, pero tenía la desventaja de hacer que el índice fuera más “más ancho” de lo estrictamente necesario.
- De hecho, muy a menudo, las columnas anteriores del índice ya identificarían un fila única lo que significa que las columnas adicionales incluidas serían completamente redundantes si no fuera por el beneficio de “evitar búsquedas de marcadores”.
INCLUDEBásicamente, las columnas permiten el mismo beneficio de manera más eficiente.
nótese bien Algo muy importante a destacar. Por lo general, no obtiene ningún beneficio de
INCLUDEcolumnas en sus índices si tiene el hábito perezoso de escribir siempre sus consultas comoSELECT * .... Volviendo todas las columnas Básicamente, se está asegurando de que se requiera una búsqueda de marcadores en cualquier caso.
En el primer índice, en Index page solo PostalCode es el key columna y AddressLine1, AddressLine2, City, StateProvinceID son parte del nodo hoja para evitar key/RID buscar
Preferiré el primer índice cuando mi tabla se filtrará siempre PostalCode y cualquiera de estas columnas AddressLine1, AddressLine2, City, StateProvinceID será parte de select y no filtracion
select AddressLine1, AddressLine2, City, StateProvinceID
from Person.Address
Where PostalCode=
En segundo índice, en Index page habrá cinco key columnas PostalCode, AddressLine1, AddressLine2, City, StateProvinceID
Preferiré el segundo índice cuando tenga la posibilidad de filtrar datos como
Where PostalCode = And AddressLine1 =
o
Where PostalCode = And AddressLine2 =
o
Where PostalCode = And AddressLine1 = and AddressLine2 =
y así..
En cualquier caso, la primera columna del índice debe ser parte de la filtración para utilizar el índice.
En el primer ejemplo, solo la columna de índice: PostalCode se almacena en el árbol de índice con todas las demás columnas almacenadas en el nivel de hoja del índice. Esto hace que el índice sea más pequeño y es útil si no estaría usando un where, Join, group by contra las otras columnas, sino solo contra el PostalCode.
En el segundo índice, todos los datos de todas las columnas se almacenan en el árbol de índice, esto hace que el índice sea mucho más grande, pero es útil si estaría usando cualquiera de las columnas en una instrucción WHERE / JOIN / GROUP BY / ORDER By.
Incluir columnas agiliza la recuperación de los datos cuando se especifican en la lista de selección.
Por ejemplo, si está ejecutando:
SELECT PostalCode, AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address
Where PostalCode= 'A1234'
Esto se beneficiará al crear un índice en PostalCode e incluir todas las demás columnas.
Por otro lado, si está ejecutando:
SELECT PostalCode, AddressLine1, AddressLine2, City, StateProvinceID
FROM Person.Address
Where PostalCode= 'A1234' or City = 'London' or StateProvinceID = 1 or AddressLine1 = 'street A' or AddressLine2 = 'StreetB'
Esto se beneficiaría más de tener todas las columnas en el índice.
Eche un vistazo a los enlaces a continuación, estos pueden ayudar más con su consulta.
Índice con columna incluida: https://msdn.microsoft.com/en-us/library/ms190806(v=sql.105).aspx
Organización de tablas e índices: https://msdn.microsoft.com/en-us/library/ms189051(v=sql.105).aspx
Sección de Reseñas y Valoraciones
Eres capaz de añadir valor a nuestro contenido participando con tu veteranía en las notas.