Optimización con índices

Completado

Los índices son estructuras de datos que aceleran la recuperación de datos mediante la creación de rutas de búsqueda optimizadas para filas de tabla. Sin índices, el motor de base de datos debe examinar cada fila de una tabla para buscar registros coincidentes, un examen de tabla completo que se ralentiza prohibitivamente a medida que crecen las tablas.

Un índice funciona como el índice de un libro: en lugar de leer cada página para encontrar un artículo, consulte el índice para saltar directamente a las páginas pertinentes. La base de datos usa índices de forma similar, convirtiendo potencialmente millones de comparaciones de filas en una serie de búsquedas eficaces.

Sin embargo, los índices consumen espacio de almacenamiento y ralentizan INSERTlas operaciones , UPDATEy DELETE porque la base de datos debe mantener la estructura del índice junto con los datos. Esta compensación hace que la selección de índices se convierta en una decisión de diseño clave que afecta directamente al rendimiento de las consultas y de las operaciones de escritura.

Los distintos tipos de índice sirven para diferentes propósitos.

Uso de índices de almacenamiento en filas

Diseñar índices eficaces es clave para lograr un buen rendimiento de la base de datos y la aplicación. Una falta de índices, sobreindización o índices mal diseñados son fuentes principales de problemas de rendimiento de la base de datos.

Los índices de almacén de filas organizan los datos en formato de fila, almacenando todas las columnas de una fila juntas en la misma página, lo que hace que sean óptimos para las cargas de trabajo transaccionales que recuperan registros completos o realizan actualizaciones frecuentes.

Un índice agrupado ordena y almacena las filas de datos de la tabla en función de sus valores de clave. Estos valores clave son las columnas incluidas en la definición del índice. Solo puede haber un índice clúster por tabla, porque las filas de datos solo pueden almacenarse en un solo orden.

Un índice no clúster tiene una estructura independiente de las filas de datos. Un índice no clúster contiene los valores de clave de índice no clúster y cada entrada de valor de clave tiene un puntero a la fila de datos que contiene el valor clave. Se pueden crear varios índices no clúster en una tabla o una vista indizada.

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

Los índices agrupados son los mejores cuando se necesitan consultas de rango eficientes, claves estables y estrechas, o un criterio de ordenación natural, como columnas de identidad o campos de fecha, ya que definen el orden de fila físico y optimizan los exámenes sobre los datos ordenados.

Los índices no clúster son ideales cuando se necesitan búsquedas rápidas para predicados específicos, combinaciones o patrones de ordenación que no se alinean con la clave agrupada, o cuando se desea cubrir una consulta mediante la inclusión de columnas adicionales para evitar búsquedas de claves.

Elegir entre ellos depende de cómo acceda a los datos: use un índice agrupado para la ruta de acceso principal y los índices no agrupados para admitir patrones alternativos, altamente selectivos o con frecuencia consultados mientras equilibra el costo que introducen en las operaciones de escritura.

Entender los índices de almacenamiento de columnas

Los índices tradicionales de almacén de filas almacenan los datos fila por fila, lo cual es ideal para sistemas transaccionales que recuperan registros individuales. Sin embargo, las consultas analíticas que examinan millones de filas para calcular agregados (SUM, AVG, COUNT) pierden tiempo leyendo columnas que no necesitan. Los índices de almacén de columnas tienen como objetivo resolverlo mediante el almacenamiento de columnas de datos por columna, leyendo solo las columnas necesarias para la consulta.

Comprender la arquitectura de Columnstore

Un índice de almacén de columnas organiza los datos en grupos de filas, cada uno que contiene hasta 1048 576 filas. Dentro de cada grupo de filas, el motor almacena cada columna por separado como un segmento de columna y lo comprime de forma independiente. Esta arquitectura permite al optimizador de consultas leer solo las columnas necesarias para una consulta, omitiendo completamente los datos irrelevantes.

Al insertar datos, los lotes pequeños van primero a un almacén delta, una estructura temporal de almacén de filas con un índice de árbol B+. Una vez que un grupo de filas delta acumula suficientes filas (al menos 102 400), un proceso en segundo plano denominado tuple-mover (motor de tuplas) lo comprime en el almacén de columnas. Las filas que llegan a través de operaciones de carga masivas de 102 400 o más filas ignoran el almacén delta y se comprimen directamente en el almacén de columnas.

En la tabla siguiente se describe la recomendación para los índices columnstore.

Escenario	Recomendación	Motivo
Tablas de hechos del almacenamiento de datos	Usar almacén de columnas	Las tablas con millones de filas usadas para el análisis se benefician del almacenamiento en columnas y la compresión
Bases de datos de informes	Usar almacén de columnas	Las cargas de trabajo intensivas de lectura con consultas agregadas funcionan más rápido con acceso orientado a columnas
Datos históricos	Utilice columnstore	Los datos archivados que se actualizan rara vez, pero que se analizan con frecuencia logran relaciones de compresión elevadas
Tablas pequeñas (<1 millón de filas)	Evitar almacenamiento en columnas	La sobrecarga supera las ventajas; los grupos de filas necesitan filas suficientes para una compresión eficaz
Actualizaciones y eliminaciones de alta frecuencia	Evitar el almacenamiento en columnas	Las modificaciones marcan las filas como eliminadas en lugar de actualizarse en su lugar, lo que provoca fragmentación.
Búsquedas de una sola fila	Evitar almacén de columnas	Los índices Rowstore son más rápidos para recuperar registros individuales

Utilice el índice de columnas almacenadas agrupado (CCI)

Un índice de almacén de columnas agrupado (CCI) es un tipo de índice de almacén de columnas que se convierte en la estructura de almacenamiento principal de toda la tabla, reemplazando cualquier índice de almacén de filas agrupado existente. A diferencia de un índice de almacén de columnas no agrupado (NCCI), que crea una copia de columnas secundaria junto con la tabla de almacén de filas, un CCI almacena todos los datos de tabla exclusivamente en formato de columna.

Esto significa que la tabla no tiene almacenamiento tradicional basado en filas: el motor comprime y almacena cada columna por separado. Tanto CCI como NCCI usan las mismas optimizaciones de procesamiento por lotes y compresión de columnas, pero usan un CCI cuando el análisis es la carga de trabajo principal y no necesita patrones de acceso transaccional de nivel de fila. Por el contrario, un NCCI permite mantener índices de almacenamiento en filas para consultas transaccionales, mientras proporciona una estructura columnar para las consultas analíticas en la misma tabla.

Puede crear un índice de almacén de columnas agrupado mediante la CREATE CLUSTERED COLUMNSTORE INDEX instrucción . Este es un ejemplo:

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

Usar índice de almacén de columnas no agrupado (NCCI)

Un índice de almacén de columnas no agrupado (NCCI) crea una copia de columnas independiente de las columnas seleccionadas junto con la tabla de almacén de filas existente, lo que permite que la misma tabla sirva de forma eficaz las cargas de trabajo transaccionales y analíticas. La tabla conserva su índice de almacén de filas agrupado original para búsquedas y actualizaciones rápidas de una sola fila, mientras que el NCCI permite un acceso optimizado basado en columnas para consultas analíticas. El optimizador de consultas elige automáticamente entre las estructuras de almacén de filas y almacén de columnas en función del patrón de consulta.

Puede crear un índice de almacén de columnas no agrupado mediante la CREATE NONCLUSTERED COLUMNSTORE INDEX instrucción . Este es un ejemplo:

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

Supervisión de índices de columnstore

Puede supervisar el estado y el rendimiento de los índices de almacén de columnas consultando la sys.dm_db_column_store_row_group_physical_stats vista de administración dinámica.

En la consulta siguiente se muestran las estadísticas de grupo de filas, incluidos el estado, los recuentos de filas, las filas eliminadas y el tamaño de almacenamiento. Los grupos de filas abiertos siguen aceptando inserciones en el almacén delta, los grupos de filas cerrados esperan a que el motor de tupla los comprima y los grupos de filas comprimidos almacenan los datos en formato de columna. Altos recuentos de filas eliminadas o muchos grupos de filas pequeños indican una fragmentación que se puede resolver con ALTER INDEX REORGANIZE.

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

La selección de índices afecta directamente al rendimiento de las consultas y al rendimiento de escrituras. Diseñe los índices cuidadosamente durante el desarrollo inicial para evitar costosas recompilaciones y problemas de rendimiento en producción.