By: Ben Snaidero
Descripción general
En esta sección cubriremos lo que necesita saber sobre los índices no agrupados.
Qué es un índice no agrupado
Un índice no agrupado (o índice de árbol b normal) es un índice en el que el orden de las filas no coincide con el orden físico de los datos reales. Está ordenado por las columnas que componen el índice. En un índice no agrupado, las páginas de hoja del índice no contienen datos reales, sino que contienen punteros a los datos reales. Estos punteros apuntarían a la página de datos de índice agrupado donde existen los datos reales (o a la página de montón si no existe un índice agrupado en la tabla).
Por qué crear índices no agrupados
El principal beneficio de tener un índice no agrupado en una tabla es que proporciona acceso rápido a los datos. El índice permite al motor de la base de datos localizar datos rápidamente sin tener que escanear toda la tabla. A medida que una tabla se hace más grande, es muy importante que se agreguen los índices correctos a la tabla, ya que sin ningún índice, el rendimiento de la consulta disminuirá drásticamente.
Cuándo se deben crear índices no agrupados
Hay dos casos en los que tener un índice no agrupado en una tabla es beneficioso. Primero, cuando hay más de un conjunto de columnas que se utilizan en la cláusula WHERE de consultas que acceden a la tabla. Un segundo índice (suponiendo que ya haya un índice agrupado en la columna de clave primaria) acelerará los tiempos de ejecución y reducirá el número de consultas. En segundo lugar, si sus consultas requieren con frecuencia que los datos se devuelvan en un orden determinado, tener un índice en estas columnas puede reducir la cantidad de CPU y memoria requerida, ya que no será necesario realizar una clasificación adicional, ya que los datos en el índice ya están ordenados.
El siguiente ejemplo showshow no se requiere un análisis de tabla para obtener los datos, solo una búsqueda de índice del índice no agrupado y una búsqueda del índice agrupado para obtener los datos. Además, tenga en cuenta que no se requiere ningún tipo de ordenación, ya que los datos ya están en el orden correcto.
SELECT * FROM Sales.SalesOrderDetail WHERE ProductID = 750ORDER BY ProductID;
Cómo crear un índice no agrupado
Crear un índice no agrupado es básicamente lo mismo que crear un índice agrupado,pero en lugar de especificar la cláusula de agrupamiento especificamos la cláusula de agrupamiento. También podemos omitir esta cláusula por completo, ya que un no agrupado es el valor predeterminado al crear un índice.
El siguiente TSQL muestra un ejemplo de cada instrucción.
-- Adding non-clustered index CREATE NONCLUSTERED INDEX IX_Person_LastNameFirstName ON Person.Person(LastName ASC,FirstName ASC);CREATE INDEX IX_Person_FirstName ON Person.Person (FirstName ASC);
Qué es un índice de cobertura
Un índice de cobertura es un índice que se compone de todas (o más) las columnas requeridas para satisfacer una consulta como columnas clave del índice. Cuando se puede usar un índice de cobertura para ejecutar una consulta, se requieren menos operaciones de E / s ya que optimizerno longer tiene que realizar búsquedas adicionales para recuperar los datos reales de la tabla.
A continuación se muestra un ejemplo del TSQL que puede usar para crear un índice de cobertura en la tabla de productos.
CREATE NONCLUSTERED INDEX IX_Production_ProductNumber_Name ON Production.Product (Name ASC,ProductNumber ASC);
La siguiente consulta TSQL ahora se puede ejecutar solo accediendo al nuevo índice que acabamos de crear, ya que todas las columnas de la consulta son parte del índice.
SELECT ProductNumber, Name FROM Production.Product WHERE Name = 'Cable Lock';
El siguiente plan Explain confirma que no se requiere una búsqueda adicional para esta consulta.
Qué es un índice con columnas incluidas
Un índice creado con columnas incluidas es un índice no agrupado que también incluye columnas no clave en los nodos hoja del índice, similar a un índice agrupado. Hay un par de beneficios al usar las columnas incluidas. En primer lugar, le da la capacidad de incluir tipos de columnas que no están permitidos como claves de índice en su índice. Además, cuando todas las columnas de su consulta son una clave de índice o una columna incluida, la consulta ya no tiene que hacer una búsqueda adicional para obtener todos los datos necesarios para satisfacer la consulta, lo que resulta en menos operaciones de disco. Esto es similar al índice de cobertura mencionado anteriormente.
Usando el mismo ejemplo de arriba, el siguiente TSQL creará el mismo índice excepto con la columna ProductNumber referenciada como columna incluida y no como columna de clave de índice.
CREATE NONCLUSTERED INDEX IX_Production_ProductNumber_Name ON Production.Product (Name ASC) INCLUDE (ProductNumber);
Usando la misma consulta que la anterior, esto también debería poder ejecutarse sin requerir búsquedas adicionales.
SELECT ProductNumber, Name FROM Production.Product WHERE Name = 'Cable Lock';
El siguiente plan Explain confirma que no se requiere una búsqueda adicional para esta consulta también.
Índices no agrupadosrelación a índice agrupado
Como se describió anteriormente, el índice agrupado almacena los datos reales de los números de código no agrupados en los nodos hoja del índice. Los nodos hoja de cada índice no agrupado no contienen datos y en su lugar tienen punteros a la página de datos real(o nodo hoja) del índice agrupado. El diagrama siguiente ilustra este punto.
Índices filtrados
¿Qué es?
Un índice filtrado es un tipo de índice especial en el que solo se indexa una cierta porción de las filas de la tabla. Según los criterios de filtro que se aplican cuando se crea el índice, solo se indexan las filas restantes, lo que puede ahorrar espacio,mejorar el rendimiento de las consultas y reducir la sobrecarga de mantenimiento, ya que el índice es mucho más pequeño.
¿Por qué usarlo?
Los índices filtrados son útiles cuando está creando índices en tablas donde hay muchos valores nulos en ciertas columnas o ciertas columnas tienen una cardinalidad muy baja y con frecuencia está consultando un valor de baja frecuencia.
¿Cómo crearlo?
El índice filtrado se crea simplemente agregando una cláusula WHERE a cualquier instrucción de creación de index sin clústeres. El siguiente TSQL es un ejemplo de sintaxis para crear un índice filtrado.
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OrderDate_INC_ShipDate ON Sales.SalesOrderHeader(OrderDate ASC) WHERE ShipDate IS NULL;
Confirmar uso de índice
La siguiente consulta debe usar nuestro índice recién creado, ya que hay muy pocos registros en la tabla con ShipDate NULL. Aquí está el TSQL.
SELECT OrderDate FROM Sales.SalesOrderHeader WHERE ShipDate IS NULLORDER BY OrderDate ASC;