Índices de almacén de columnas en el almacenamiento de datos

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

Los índices de almacén de columnas, junto con las particiones, son esenciales para crear un almacenamiento de datos de SQL Server. Este artículo se centra en los casos de uso clave y ejemplos de diseños de almacenamiento de datos con sql Motor de base de datos.

Características clave para el almacenamiento de datos

SQL Server 2016 (13.x) introdujo estas características para las mejoras de rendimiento del almacén de columnas:

  • AlwaysOn permite consultar un índice de almacén de columnas en una réplica secundaria legible.
  • Multiple Active Result Sets (MARS) admite índices de almacén de columnas.
  • Una nueva vista de administración dinámica sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) proporciona información de solución de problemas de rendimiento en el nivel de grupo de filas.
  • Las consultas uniproceso de los índices de almacén de columnas se pueden ejecutar en el modo por lotes. Antes, solo las consultas multiproceso se ejecutaban por lotes.
  • El operador SORT se ejecuta en el modo por lotes.
  • Las operaciones DISTINCT múltiples se ejecutan en el modo por lotes.
  • Los agregados de ventana ahora se ejecutan en modo por lotes para el nivel de compatibilidad de la base de datos 130 y niveles superiores.
  • Aplicación de agregado para un procesamiento eficaz de los agregados. Se admite en todos los niveles de compatibilidad con bases de datos.
  • Aplicación de predicado de cadena para el procesamiento eficaz de los predicados de cadena. Se admite en todos los niveles de compatibilidad con bases de datos.
  • Aislamiento de instantánea en el nivel de compatibilidad de base de datos 130 y niveles superiores.
  • Los índices de almacén de columnas de clúster ordenados se introdujeron con SQL Server 2022 (16.x). Para obtener más información, consulte CREATE COLUMNSTORE INDEX and Performance tuning with ordered clustered columnstore indexes (Optimización de rendimiento y ÍNDICE DE ALMACÉN DE COLUMNAs con índices de almacén de columnas agrupados ordenados). Para obtener disponibilidad ordenada del índice de almacén de columnas, consulte Disponibilidad de índices de columna ordenada.

Para más información sobre las nuevas características en versiones y plataformas de SQL Server y Azure SQL, consulte Novedades de los índices de almacén de columnas.

Mejora del rendimiento al combinar índices de almacén de columnas y no agrupados

A partir de SQL Server 2016 (13.x), puede definir índices no agrupados de almacén de filas en un índice de almacén de columnas agrupado.

Ejemplo: mejorar la eficacia de las búsquedas de tabla con un índice no agrupado

Para mejorar la eficacia de las búsquedas de tabla en un almacenamiento de datos, puede crear un índice no agrupado diseñado para ejecutar consultas que realicen las mejores búsquedas de tabla. Por ejemplo, las consultas que buscan valores coincidentes o que devuelven un pequeño intervalo de valores tienen un rendimiento mejor en un índice de árbol B que en un índice de almacén de columnas. No requieren un recorrido de tabla completo a través del índice de almacén de columnas y devuelven el resultado correcto más rápidamente si se realiza una búsqueda binaria a través de un índice de árbol B.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.  
  
--Create the table  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--Add a nonclustered index.  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

Ejemplo: usar un índice no agrupado para aplicar una restricción de clave principal en una tabla de almacén de columnas

Por diseño, una tabla de almacén de columnas no permite una restricción de clave principal agrupada. Ahora puede usar un índice no agrupado en una tabla de almacén de columnas para aplicar restricciones de clave principal. Una clave principal equivale a una restricción UNIQUE en una columna no NULL y SQL Server implementa una restricción UNIQUE como un índice no agrupado. Combinando ambos factores, en el siguiente ejemplo se define una restricción UNIQUE en la accountkey de la columna no NULL. El resultado es un índice no agrupado que aplica una restricción de clave principal como una restricción UNIQUE en una columna no NULL.

Luego, la tabla se convierte en un índice de almacén de columnas agrupado. Durante la conversión, se conserva el índice no agrupado. El resultado es un índice de almacén de columnas agrupado con un índice no agrupado que aplica una restricción de clave principal. Dado que cualquier actualización o inserción en la tabla de almacén de columnas también afecta al índice no agrupado, todas las operaciones que infrinjan la restricción UNIQUE y el valor no NULL hace que se produzca un error en toda la operación.

El resultado es un índice de almacén de columnas con un índice no agrupado que aplica una restricción de clave principal en ambos índices.

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

Mejorar el rendimiento al habilitar el bloqueo de nivel de fila y de grupo de filas

Para complementar al índice no agrupado en una característica de índice de almacén de columnas SQL Server 2016 (13.x) ofrece una función de bloqueo pormenorizado para seleccionar, actualizar y eliminar operaciones. Se pueden ejecutar consultas con un bloqueo de nivel de fila en las búsquedas de índice en un índice no agrupado y con un bloqueo de nivel de grupo de filas en los recorridos de tabla completa en el índice de almacén de columnas. Use el bloqueo de nivel de fila y el bloqueo de nivel de grupo de filas adecuadamente para conseguir una mayor simultaneidad de lectura y escritura.

--Granular locking example  
--Store table t_account as a columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
BEGIN TRAN  
    -- The query plan chooses a seek operation on the nonclustered index  
    -- and takes the row lock  
    SELECT * FROM t_account WHERE AccountKey = 100;  
COMMIT TRAN

Aislamiento de instantánea y aislamientos de instantánea de lectura confirmada

Use el aislamiento de instantánea (SI) para garantizar la coherencia transaccional y los aislamientos de instantánea de lectura confirmada (RCSI) para garantizar la coherencia de nivel de instrucción de las consultas en índices de almacén de columnas. Esto permite que las consultas se ejecuten sin bloquear escrituras de datos. Este comportamiento de no bloqueo también reduce notablemente la probabilidad de que se produzcan interbloqueos en las transacciones complejas. Para más información, consulte Aislamiento de instantáneas en SQL Server.

Contenido relacionado