Compartir vía

Optimización del rendimiento con índices de almacén de columnas ordenados

  • Artículo

Se aplica a: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceBase de datos de Azure SQL de Microsoft Fabric

Al habilitar la eliminación eficaz de segmentos, los índices de almacén de columnas ordenados proporcionan un rendimiento más rápido omitiendo grandes cantidades de datos ordenados que no coinciden con el predicado de consulta. Cargar datos en un índice de almacén de columnas ordenado y mantenerlo ordenado a través de recompilaciones de índices puede tardar más tiempo que en un índice no ordenado debido a la operación de ordenación de datos, pero las consultas ordenadas de índices de almacén de columnas se pueden ejecutar más rápido después.

Cuando los usuarios consultan una tabla de almacén de columnas, el optimizador comprueba los valores mínimos y máximos almacenados en cada segmento. Los segmentos que están fuera de los límites del predicado de la consulta no se leen del disco a la memoria. Una consulta puede finalizar más rápido si el número de segmentos que se van a leer y su tamaño total es menor.

Para obtener disponibilidad de índices de almacén de columnas ordenada, consulte Disponibilidad de índice de almacén de columnas ordenada.

Para obtener más información sobre las nuevas características agregadas a los índices columnstore, consulte Novedades en los índices columnstore.

Índice de almacén de columnas ordenado frente a no ordenado

En un índice de almacén de columnas, los datos de cada columna de cada grupo de filas se comprimen en un segmento independiente. Cada segmento contiene metadatos que describen sus valores mínimos y máximos, por lo que los segmentos que están fuera de los límites del predicado de consulta no se leen del disco durante la ejecución de la consulta.

Cuando no se ordena un índice de almacén de columnas, el generador de índices no ordena los datos antes de comprimirlos en segmentos. Esto significa que pueden producirse segmentos con intervalos de valores superpuestos, lo que hace que las consultas lean más segmentos del disco y tarden más tiempo en finalizar.

Al crear un índice de almacén de columnas ordenado, el motor de base de datos ordena los datos existentes por las claves de pedido que especifique antes de que el generador de índices los comprima en segmentos. Con los datos ordenados, la superposición de segmentos se reduce o elimina, lo que permite que las consultas tengan una eliminación de segmentos más eficaz y, por tanto, un rendimiento más rápido porque hay menos segmentos que leer del disco.

Dependiendo de la memoria disponible, el tamaño de los datos, el grado de paralelismo, el tipo de índice (agrupado frente a no agrupado) y el tipo de compilación de índice (sin conexión frente a en línea), la ordenación de los índices de almacén de columnas ordenados podría ser completa (sin superposición de segmentos) o parcial (alguna superposición de segmento). Por ejemplo, la ordenación parcial se produce cuando la memoria disponible no es suficiente para una ordenación completa. Las consultas que usan un índice de almacén de columnas ordenado a menudo se ejecutan más rápido que con un índice no ordenado incluso si el índice ordenado se creó mediante una ordenación parcial.

Se proporciona una ordenación completa para los índices de almacén de columnas agrupados ordenados creados o recompilados con las opciones de ONLINE = ON y MAXDOP = 1. En este caso, la ordenación no está limitada por la memoria disponible porque usa la base de datos de tempdb para desbordar los datos que no caben en la memoria. El proceso de compilación del índice puede ser más lento debido a operaciones adicionales de E/S de tempdb. Sin embargo, con una recompilación de índices en línea, las consultas pueden seguir usando el índice existente mientras se vuelve a generar el nuevo índice ordenado.

También se puede proporcionar una ordenación completa para los índices de almacén de columnas agrupados y no agrupados ordenados creados o recompilados con las opciones de ONLINE = OFF y MAXDOP = 1 si la cantidad de datos que se van a ordenar es lo suficientemente pequeña como para ajustarse completamente a la memoria disponible.

En todos los demás casos, la ordenación en los índices de almacén de columnas ordenados es parcial.

Nota

Actualmente, los índices de almacén de columnas ordenados solo se pueden crear o volver a generar en línea en Azure SQL Database y en Azure SQL Managed Instance con la Directiva de actualización siempre actualizada.

Para comprobar los intervalos de segmento de una columna y determinar si hay alguna superposición de segmentos, use la consulta siguiente, sustituyendo los marcadores de posición por los nombres de esquema, tabla y columna:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Por ejemplo, la salida de esta consulta para un índice de almacén de columnas totalmente ordenado podría ser el siguiente. Tenga en cuenta que no hay superposición en las columnas min_data_id y max_data_id para distintos segmentos.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Nota

En un índice de almacén de columnas ordenado, los nuevos datos resultantes del mismo lote de operaciones de carga de datos o DML se ordenan solo dentro de ese lote. No hay ninguna ordenación global que incluya los datos existentes en la tabla.

Para ordenar los datos en el índice después de insertar nuevos datos o actualizar los datos existentes, vuelva a generar el índice.

Para una recompilación sin conexión de un índice de almacén de columnas con particiones, la recompilación se realiza una partición cada vez. Los datos de la partición que se vuelven a compilar no están disponibles hasta que la recompilación se complete para esa partición.

Los datos permanecen disponibles durante una recompilación en línea. Para obtener más información, consulte Realizar operaciones de índice en línea.

Rendimiento de las consultas

La ganancia de rendimiento de un índice de almacén de columnas ordenado depende de los patrones de consulta, del tamaño de los datos, de la ordenación de los datos, de la estructura física de los segmentos y de los recursos de proceso disponibles para la ejecución de consultas.

Las consultas con los patrones siguientes normalmente se ejecutan más rápido con índices de almacén de columnas ordenados.

  • Consultas que tienen predicados de igualdad, desigualdad o rango.
  • Consultas en las que las columnas de predicado y las columnas CCI ordenadas son las mismas.

En este ejemplo, la tabla T1 tiene un índice de almacén de columnas agrupado ordenado en la secuencia de Col_C, Col_By Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

El rendimiento de las consultas 1 y 2 puede beneficiarse más del índice columnstore ordenado que las consultas 3 y 4, ya que hacen referencia a todas las columnas ordenadas.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Rendimiento de la carga de datos

El rendimiento de la carga de datos en una tabla con un índice de almacén de columnas ordenado es similar a una tabla con particiones. La carga de datos puede tardar más tiempo que con un índice de almacén de columnas no ordenado debido a la operación de ordenación de datos, pero las consultas se pueden ejecutar más rápido después.

Reducción de la superposición de segmentos

El número de segmentos superpuestos depende del tamaño de los datos que se van a ordenar, la memoria disponible y el valor máximo de grado de paralelismo (MAXDOP) durante la compilación del índice de almacén de columnas ordenado. Las estrategias siguientes reducen la superposición de segmentos, pero pueden hacer que el proceso de compilación del índice tarde más tiempo.

  • Si la compilación de índices en línea está disponible, use las opciones ONLINE = ON y MAXDOP = 1 al crear un índice de almacén de columnas agrupado ordenado. Esto crea un índice totalmente ordenado.
  • Si la compilación de índices en línea no está disponible, use la opción MAXDOP = 1.
  • Ordene previamente los datos por las claves de ordenación antes de la carga.

Cuando MAXDOP es mayor que 1, cada subproceso utilizado para la creación del índice de almacén de columnas ordenado trabaja en un subconjunto de datos y lo ordena localmente. No hay ninguna ordenación global entre los datos ordenados por subprocesos diferentes. El uso de subprocesos paralelos puede reducir el tiempo de creación del índice, pero genera más segmentos superpuestos que cuando se usa un único subproceso. El uso de una operación de un solo hilo ofrece la máxima calidad de compresión. Puede especificar MAXDOP con el comando CREATE INDEX.

Ejemplos

Buscar columnas ordenadas y ordinal

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Creación de un índice de almacén de columnas ordenado

Índice de almacén de columnas ordenado agrupado:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Índice de almacén de columnas ordenado no agrupado:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Agregar o quitar columnas de pedido y recompilar un índice de almacén de columnas ordenado existente

Índice de almacén de columnas ordenado agrupado:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Índice de almacén de columnas ordenado no agrupado:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Creación de un índice de almacén de columnas agrupado ordenado en línea con ordenación completa en una tabla de montón

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Recompilación de un índice de almacén de columnas agrupado ordenado en línea con una ordenación completa

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Contenido relacionado