Índices de almacenamiento de columnas: rendimiento de consultas

Aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Analítica (PDW)Base de datos SQL en Microsoft Fabric

En este artículo se incluyen recomendaciones para lograr el rendimiento rápido de las consultas con índices de almacén de columnas.

Los índices de almacén de columnas pueden lograr hasta 100 veces mejor rendimiento en las cargas de trabajo de análisis y almacenamiento de datos, y hasta 10 veces mejor compresión de datos que los índices de almacén de filas tradicionales. Estas recomendaciones ayudan a que las consultas alcancen el rendimiento de consulta rápido que los índices de almacén de columnas están diseñados para ofrecer.

Recomendaciones para mejorar el rendimiento de las consultas

Aquí se proporcionan algunas recomendaciones para lograr el rendimiento alto que se espera que proporcionen los índices de almacén de columnas.

1. Organice los datos para eliminar más grupos de filas de un examen completo de la tabla

• Elija cuidadosamente el orden de inserción. Normalmente, en el almacén de datos tradicional, los datos se insertan realmente en orden cronológico y el análisis se realiza en la dimensión de tiempo. Por ejemplo, en los análisis de ventas por trimestre. Para este tipo de carga de trabajo, la eliminación del grupo de filas se realiza automáticamente. En SQL Server 2016 (13.x), puede averiguar el número de grupos de filas omitidos como parte del procesamiento de consultas.

• Use un índice clúster de almacenamiento de filas. Si el predicado de consulta común está en una columna (por ejemplo, C1) no relacionada con el orden de inserción, cree un índice agrupado de almacén de filas en la columna C1. A continuación, quite el índice agrupado de almacenamiento por filas y cree un índice de almacén de columnas agrupado. Si crea explícitamente el índice de almacén de columnas agrupado mediante MAXDOP = 1, el índice de almacén de columnas agrupado resultante se ordena perfectamente en la columna C1. Si especifica MAXDOP = 8, verá la superposición de valores entre ocho grupos de filas. Para un índice de almacén de columnas no agrupado (NCCI), si la tabla tiene un índice agrupado de almacén de filas, las filas ya están ordenadas por la clave de índice agrupada. En este caso, el índice de almacén de columnas no agrupado también se ordena automáticamente. Un índice columnstore no mantiene inherentemente el orden de las filas. A medida que se insertan nuevas filas o se actualizan las filas anteriores, es posible que tenga que repetir el proceso a medida que el rendimiento de las consultas de análisis podría deteriorarse.

• Implementar la partición de tablas. Puede particionar el índice de almacén de columnas y, a continuación, usar la eliminación de particiones para reducir el número de grupos de filas que se van a examinar. Por ejemplo, una tabla de hechos almacena las compras realizadas por los clientes. Un patrón de consulta común es buscar compras trimestrales por customer. En este caso, combine la columna de orden de inserción con la partición de la columna customer. Cada partición contiene filas para cada customer, ordenadas tras la inserción. Además, considere la posibilidad de usar la partición de tablas si necesita eliminar datos antiguos del almacén de columnas. La conmutación y truncamiento de particiones que no son necesarias es una estrategia eficaz para eliminar datos sin generar fragmentación.

• Evite la eliminación de grandes cantidades de datos. Quitar filas comprimidas de un grupo de filas no es una operación sincrónica. Resultaría caro descomprimir un grupo de filas, eliminar la fila y, a continuación, volver a comprimirla. Por lo tanto, cuando se eliminan datos de grupos de filas comprimidos, estos grupos de filas se siguen examinando, aunque devuelvan menos filas. Si el número de filas eliminadas de varios grupos de filas es lo suficientemente grande como para combinarse en menos grupos de filas, la reorganización del almacén de columnas aumenta la calidad del índice y el rendimiento de las consultas mejora. Si el proceso de eliminación de datos suele vaciar grupos de filas completos, considere la posibilidad de usar la creación de particiones de tablas. Cambie las particiones que ya no son necesarias y trunquelas, en lugar de eliminar filas.

  Note

  A partir de SQL Server 2019 (15.x), el tuple-mover recibe ayuda de una tarea de combinación en segundo plano. Esta tarea comprime automáticamente los grupos de filas delta abiertos más pequeños que han existido durante cierto tiempo, según un umbral interno, o combina grupos de filas comprimidos de los que se ha eliminado un gran número de filas. Esto mejora la calidad del índice columnstore con el tiempo. Si es necesario eliminar grandes cantidades de datos del índice de almacén de columnas, considere la posibilidad de dividir esa operación en lotes de eliminación más pequeños a lo largo del tiempo. El procesamiento por lotes permite que la tarea de combinación en segundo plano controle la tarea de combinar grupos de filas más pequeños y mejore la calidad del índice. A continuación, no es necesario programar ventanas de mantenimiento de reorganización de índices después de la eliminación de datos. Para obtener más información sobre los términos y conceptos de los índices de columnstore, consulte Índices de columnstore: descripción general.

2. Planear para que haya suficiente memoria para crear índices de almacén de columnas en paralelo

La creación un índice de almacén de columnas es de forma predeterminada una operación paralela a menos que se restrinja la memoria. Crear el índice en paralelo requiere más memoria que crear el índice en serie. Cuando hay suficiente memoria, la creación de un índice de almacén de columnas tarda aproximadamente 1,5 veces más que la creación de un árbol B en las mismas columnas.

La memoria necesaria para crear un índice de almacén de columnas depende del número de columnas, el número de columnas de cadena, el grado de paralelismo (DOP) y las características de los datos. Por ejemplo, si la tabla tiene menos de un millón de filas, el motor de base de datos usa solo un subproceso para crear el índice de almacén de columnas.

Si la tabla tiene más de un millón de filas, pero el motor de base de datos no puede obtener una concesión de memoria suficientemente grande para crear el índice mediante MAXDOP, el motor de base de datos disminuye MAXDOP automáticamente según sea necesario. En algunos casos, el DOP debe reducirse a uno para compilar el índice con memoria limitada dentro de la asignación de memoria disponible.

Desde SQL Server 2016 (13.x), la consulta siempre funciona en modo por lotes. En versiones anteriores, la ejecución por lotes solo se utiliza cuando DOP es mayor que uno.

Explicación del rendimiento del almacén de columnas

Los índices de almacén de columnas logran un rendimiento de consulta elevado mediante la combinación del procesamiento en modo por lotes en memoria de alta velocidad con técnicas que reducen en gran medida los requisitos de E/S. Dado que las consultas analíticas exploran un gran número de filas, normalmente están limitadas por las operaciones de E/S y, por lo tanto, reducir las operaciones de E/S durante la ejecución de las consultas es fundamental para el diseño de los índices de almacén de columnas. Una vez que los datos se leen en la memoria, es fundamental reducir el número de operaciones en memoria.

Los índices de almacén de columnas reducen la E/S y optimizan las operaciones en memoria a través de una alta compresión de datos, la eliminación del almacén de columnas, la eliminación del grupo de filas y el procesamiento por lotes.

Compresión de datos

Los índices de almacén de columnas logran hasta 10 veces mayor compresión de datos que los índices de almacén de filas. Esto reduce en gran medida la E/S necesaria para ejecutar las consultas de análisis y, por tanto, mejora el rendimiento de las consultas.

• Los índices de almacén de columnas leen los datos comprimidos del disco, lo que significa que deben leerse menos bytes de datos en la memoria.

• Los índices de almacén por columnas almacenan los datos en formato comprimido en memoria, lo que reduce la E/S al evitar volver a leer los mismos datos en memoria. Por ejemplo, con una compresión de 10 veces, los índices de almacén de columnas pueden mantener 10 veces más datos en memoria, en comparación con el almacenamiento de los datos en formato sin comprimir. Con más datos en memoria, es más probable que el índice de almacén de columnas encuentre los datos que necesita en la memoria sin incurrir en lecturas innecesarias del disco.

• Los índices de almacén de columnas comprimen los datos por columnas en lugar de por filas, lo que genera altas tasas de compresión y reduce el tamaño de los datos almacenados en disco. Se comprime y almacena cada columna de forma independiente. Los datos de una columna siempre tienen el mismo tipo de datos y tiende a tener valores similares. Las técnicas de compresión de datos por columnas son excelentes para lograr mayores índices de compresión cuando los valores son similares.

Por ejemplo, una tabla de hechos almacena direcciones de cliente y tiene una columna para country-region. El número total de valores posibles es inferior a 200. Algunos de esos valores se repiten muchas veces. Si la tabla de hechos tiene 100 millones de filas, la country-region columna se comprime fácilmente y requiere poco almacenamiento. La compresión de fila por fila no puede aprovechar la similitud entre los valores de las columnas de esta manera y debe usar más bytes para comprimir los valores de la columna country-region.

Eliminación de columnas

Los índices columnstore omiten leer columnas no referenciadas por la consulta. La eliminación de columnas reduce aún más la E/S para la ejecución de consultas y, por tanto, mejora el rendimiento de las consultas.

• La eliminación de la columna es posible porque los datos se organizan y comprimen columna por columna. En cambio, cuando los datos se almacenan fila por fila, los valores de columna de cada fila se almacenan físicamente juntos y no se pueden separar fácilmente. El procesador de consultas debe leer en una fila completa para recuperar valores de columna específicos, lo que aumenta la E/S porque los datos adicionales se leen innecesariamente en la memoria.

Por ejemplo, si una tabla tiene 50 columnas y la consulta solo usa cinco de esas columnas, el índice de almacén de columnas solo captura las cinco columnas del disco. Omite la lectura en las otras 45 columnas, lo que reduce la E/S por otro 90 %, suponiendo que todas las columnas tienen un tamaño similar. Si los mismos datos se almacenan en un almacén de filas, el procesador de consultas debe leer las 45 columnas restantes.

Eliminación de grupos de filas

Para escaneos de tabla completa, un gran porcentaje de los datos normalmente no coincide con los criterios de predicado de la consulta. Mediante el uso de metadatos, el índice de almacén de columnas puede omitir la lectura en los grupos de filas que no contienen datos necesarios para el resultado de la consulta, todo sin E/S real. Esta capacidad, denominada eliminación de grupos de filas, reduce la E/S en las exploraciones completas de tabla y, por tanto, mejora el rendimiento de las consultas.

¿Cuándo un índice de almacén de columnas tiene que realizar un análisis de tabla completa?

A partir de SQL Server 2016 (13.x), puede crear uno o varios índices de almacén de filas no agrupados normales o de árbol B en un índice de almacén de columnas agrupado. Los índices no agrupados de árbol B pueden acelerar una consulta que tenga un predicado de igualdad o un predicado con un pequeño intervalo de valores. Para predicados más complicados, el optimizador de consultas puede elegir un análisis de tabla completa. Sin la capacidad de omitir grupos de filas, un examen de tabla completa puede llevar mucho tiempo, especialmente para tablas grandes.

¿Cuándo se beneficia una consulta de análisis de la eliminación del grupo de filas para un análisis de tabla completa?

Por ejemplo, un negocio minorista modela sus datos de ventas mediante una tabla de hechos con el índice de almacén de columnas agrupado. Cada nueva venta almacena varios atributos de la transacción, incluida la fecha en que se vende un producto. Interesantemente, aunque los índices de almacén de columnas no garantizan un orden ordenado, las filas de esta tabla se cargan en un orden de ordenación de fecha. Con el tiempo esta tabla crece. Aunque la empresa minorista puede conservar los datos de ventas de los últimos 10 años, una consulta de análisis solo necesita calcular un agregado para el último trimestre. Los índices de almacenamiento por columnas pueden evitar acceder a los datos de los 39 trimestres anteriores con solo consultar los metadatos de la columna de fecha. Se trata de una reducción del 97 % en la cantidad de datos que se leen en la memoria y se procesan.

¿Qué grupos de filas se omiten en un análisis de tabla completa?

Para determinar qué grupos de filas eliminar, el índice de almacén de columnas usa metadatos para almacenar los valores mínimos y máximos de cada segmento de columna para cada grupo de filas. Cuando ninguno de los rangos de los segmentos de columna cumple los criterios del predicado de la consulta, se omite el grupo de filas completo sin efectuar ninguna operación real de E/S. Esto funciona porque los datos normalmente se cargan en un orden ordenado. Aunque no se garantiza la ordenación de filas, los valores de datos similares a menudo se encuentran en el mismo grupo de filas o en un grupo de filas vecino.

Para obtener más información sobre los grupos de filas, consulte Directrices de diseño de índices de almacén de columnas.

Ejecución del modo por lotes

Ejecución en modo por lotes procesa filas en grupos, normalmente hasta 900 a la vez, para mejorar la eficiencia. Por ejemplo, la consulta SELECT SUM(Sales) FROM SalesData calcula el total de ventas de la SalesData tabla. En modo por lotes, el motor de consultas procesa los datos en grupos de 900 filas. Este enfoque reduce el costo de acceso a los metadatos y otros tipos de sobrecarga al distribuirlos entre todas las filas de un lote, en lugar de incurrir en la sobrecarga de cada fila. Además, el modo por lotes funciona con datos comprimidos siempre que sea posible y quita algunos de los operadores de intercambio usados en modo de fila, lo que acelera significativamente las consultas analíticas.

No todos los operadores de ejecución de consultas se pueden ejecutar en el modo por lotes. Por ejemplo, las operaciones del lenguaje de manipulación de datos (DML), como insertar, eliminar o actualizar, se ejecutan una fila a la vez. El operador de modo por lotes, como Scan, Join, Aggregate, Sort y otros, puede mejorar el rendimiento de las consultas. Desde que se introdujo el índice de almacén de columnas en SQL Server 2012 (11.x), se mantiene un esfuerzo constante por ampliar los operadores que pueden ejecutarse en modo por lotes. En la tabla siguiente se muestran los operadores que se ejecutan en modo por lotes según la versión del producto.

¹ Se aplica a SQL Server 2016 (13.x), los niveles Premium de SQL Database, los niveles Estándar: S3 y versiones posteriores, y todos los niveles de núcleo virtual y el sistema de plataforma de análisis (PDW)

Para más información, vea la Guía de arquitectura de procesamiento de consulta.

Empuje de agregación

Ruta de ejecución normal para el cálculo agregado a fin de obtener las filas aptas del nodo SCAN y realizar la agregación de los valores en modo por lotes. Aunque esto ofrece un buen rendimiento, a partir de SQL Server 2016 (13.x), la operación de agregado se puede insertar en el nodo SCAN. La delegación de agregaciones mejora el rendimiento de los cálculos de agregación en varios órdenes de magnitud cuando se usa junto con la ejecución en modo por lotes, siempre que se cumplan las siguientes condiciones:

• Los agregados son MIN, MAX, SUM, COUNT y COUNT(*).
• El operador de agregación debe estar encima del nodo SCAN o del nodo SCAN con GROUP BY.
• Este agregado no es un agregado distinto.
• La columna de agregado no es una columna de cadena.
• La columna de agregado no es una columna virtual.
• El tipo de datos de entrada y salida debe ser uno de los siguientes y debe ajustarse a 64 bits:
  • tinyint, int, bigint, smallint, bit
  • smallmoney, money, decimal y numeric con precisión <= 18
  • smalldate, date, datetime, datetime2, time

Por ejemplo, la inserción de agregados se realiza en las dos consultas siguientes:

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Descenso de predicados de cadena

Cuando se diseña un esquema de almacenamiento de datos, el modelado del esquema recomendado es usar un esquema de estrella o copo de nieve que conste de una o varias tablas de hechos y muchas tablas de dimensión.

Por ejemplo, un hecho puede ser un registro que representa una venta de un producto determinado en una región específica, mientras que la dimensión representa un conjunto de regiones, productos y así sucesivamente. Las tablas de hechos y de dimensión están conectadas mediante una relación de clave primaria/clave foránea. Las consultas de análisis de uso más frecuente unen una o varias tablas de dimensiones con la tabla de hechos.

Consideremos una tabla de dimensiones Products. Una clave primaria típica es ProductCode, normalmente representada como una cadena. Para optimizar el rendimiento de las consultas, es una práctica recomendada crear una clave sustituta, normalmente una columna de entero, para hacer referencia desde la tabla de hechos a la fila de la tabla de dimensiones.

El índice de almacén de columnas ejecuta consultas de análisis con combinaciones y predicados que implican claves numéricas o basadas en enteros de forma eficaz. SQL Server 2016 (13.x) mejoró el rendimiento de las consultas de análisis con columnas basadas en cadenas significativamente, al insertar los predicados con columnas de cadena en el nodo SCAN.

La aplicación de predicados de cadena aprovecha el diccionario primario/secundario creado para las columnas para mejorar el rendimiento de las consultas. Por ejemplo, considere un segmento de columna de cadena dentro de un grupo de filas que consta de 100 valores de cadena distintos. Cada valor de cadena distinto se hace referencia a 10 000 veces en promedio, suponiendo un millón de filas. Con la aplicación anticipada de predicados de cadena, la ejecución de la consulta evalúa el predicado sobre los valores del diccionario. Si el predicado cumple la condición, todas las filas referidas al valor del diccionario también la cumplen automáticamente. Esto mejora el rendimiento de dos maneras:

• Solo se devuelve la fila calificada, lo que reduce el número de filas que necesitan fluir fuera del nodo de examen.
• Se reduce el número de comparaciones de cadenas. En este ejemplo, solo se requieren 100 comparaciones de cadenas, frente a 1 millón de comparaciones. Existen algunas limitaciones:
  • No hay propagación de predicados de cadena en los grupos de filas delta. No hay ningún diccionario para las columnas en grupos de filas delta.
  • No se aplica la inserción de predicados de cadenas si el diccionario supera las entradas de 64 KB.
  • No se admite la evaluación de expresiones que contienen valores NULL.

Eliminación de segmentos

La elección de los tipos de datos podría tener un impacto significativo en el rendimiento de las consultas, en función de los predicados de filtro habituales de las consultas sobre el índice de almacén de columnas.

En los datos de almacenamiento por columnas, los grupos de filas se componen de segmentos de columna. Hay metadatos con cada segmento para permitir la eliminación rápida de segmentos sin leerlos. Esta eliminación de segmentos se aplica a los tipos de datos numéricos, de fecha y hora, y al tipo de datos datetimeoffset con escala menor o igual que dos. A partir de SQL Server 2022 (16.x), las funcionalidades de eliminación de segmentos se extienden a los tipos de datos string, binary, guid y el tipo de datos datetimeoffset para la escala superior a dos.

Después de actualizar a una versión de SQL Server que admita la eliminación de segmentos de cadena mínimos/máximos (SQL Server 2022 (16.x) y versiones posteriores), el índice de almacén de columnas no se beneficia de esta característica hasta que se reconstruya utilizando un ALTER INDEX REBUILD o un CREATE INDEX WITH (DROP_EXISTING = ON).

La eliminación de segmentos no se aplica a los tipos de datos LOB, como las longitudes de tipo de datos (max).

Actualmente, solo SQL Server 2022 (16.x) y las versiones posteriores admiten la eliminación de grupos de filas en índices de almacén de columnas agrupados para predicados de prefijo LIKE, por ejemplo, column LIKE 'string%'. No se admite la eliminación de segmentos para el uso sin prefijo de LIKE, como column LIKE '%string'.

Los índices de almacén de columnas ordenados también se benefician de la eliminación de segmentos, especialmente para las columnas de cadena. En los índices de almacén de columnas ordenados, la eliminación de segmentos en la primera columna de la clave del índice es más eficaz, porque esta está ordenada. Las mejoras de rendimiento debido a la eliminación de segmentos en otras columnas de la tabla son menos predecibles. Para más información sobre los índices de almacén de columnas ordenados, consulte Uso de un índice de almacén de columnas ordenado para tablas de almacenamiento de datos grandes. Para consultar la disponibilidad de los índices de almacén de columnas ordenados, vea Disponibilidad de los índices de columna ordenados.

Con la opción SET STATISTICS IOde conexión de consulta , puede ver la eliminación de segmentos en acción. Busque un resultado como el siguiente que indique que se ha producido la eliminación de segmentos. Los grupos de filas se componen de segmentos de columna, por lo que esto podría indicar la eliminación de segmentos. En el ejemplo de salida siguiente SET STATISTICS IO de una consulta, la consulta omitió aproximadamente el 83 % de los datos:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Contenido relacionado

• Directrices de diseño para índices de almacén de columnas
• Índices de almacén de columnas - Guía para la carga de datos
• Introducción a Columnstore para análisis operativos en tiempo real
• Índices de almacén de columnas en el almacenamiento de datos
• Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos
• Arquitectura del índice de almacén de columnas
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)