Compartir a través de

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

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLAnalytics Platform System (PDW)Base de datos SQL en La versión preliminar de Microsoft Fabric

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 almacenes de datos con el Motor de Base de Datos SQL.

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:

  • Los Grupos de disponibilidad AlwaysOn admiten la consulta de 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.
  • Todas las consultas de los índices de almacén de columnas se pueden ejecutar en modo por lotes. Anteriormente, solo las consultas paralelas se podían ejecutar en modo por lotes.
  • Los operadores Sort, Distinct Sort y Distinct se ejecutan en modo por lotes.
  • Ahora, los agregados de ventana se ejecutan en modo por lotes para el nivel de compatibilidad de la base de datos 130 y versiones posteriores.
  • Delegación de agregado para un procesamiento eficaz de agregados. Se admite en todos los niveles de compatibilidad con bases de datos.
  • Delegació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 para el modo de compatibilidad de base de datos nivel 130 y superiores.
  • Los índices de almacén de columnas agrupados ordenados se introdujeron con SQL Server 2022 (16.x). Para obtener más información, consulte CREATE COLUMNSTORE INDEX y Ajuste del rendimiento con índices de almacén de columnas ordenados. Para obtener la disponibilidad de los índices de almacén de columnas ordenados, vea Disponibilidad del índice de almacén de columnas ordenado.

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.

Mejorar el rendimiento al combinar índices no agrupados y de almacén de columnas

A partir de SQL Server 2016 (13.x), puede crear í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 eficiencia de las búsquedas de tabla en un almacén de datos, puede crear un índice no agrupado diseñado para ejecutar consultas que se ejecuten óptimamente mediante 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 examen completo del índice de almacén de columnas y devuelven el resultado correcto más rápido mediante 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
);

--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;

--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

Dado que una tabla puede tener como máximo un índice agrupado, una tabla con un índice de almacén de columnas agrupado no puede tener una restricción de clave principal agrupada. Para crear una restricción de clave principal en una tabla de almacén de columnas, debe declararla como no agrupada.

En el ejemplo siguiente se crea una tabla con una restricción de clave principal no agrupada y, a continuación, se crea un índice de almacén de columnas agrupado en la tabla. Dado que cualquier inserción o actualización en la tabla de almacén de columnas también modifica el índice no clúster, todas las operaciones que infringen la restricción de clave principal hacen que se produzca un error en toda la operación.

--Create a primary key constraint on a columnstore table.

--Create a rowstore table with a nonclustered primary key constraint.
CREATE TABLE t_account (
    AccountKey int NOT NULL,
    AccountDescription nvarchar (50),
    AccountType nvarchar(50),
    UnitSold int,
    CONSTRAINT pk_account PRIMARY KEY NONCLUSTERED (AccountKey)
);

--Convert the table to columnstore.
--The primary key constraint is preserved as a nonclustered index on the columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account;

Mejora del rendimiento habilitando el bloqueo de nivel de fila y de grupo de filas

Para complementar el índice no clúster en una característica de índice de almacén de columnas, SQL Server 2016 (13.x) ofrece una funcionalidad de bloqueo pormenorizada para las operaciones SELECT, UPDATEy DELETE. Se pueden ejecutar consultas con un bloqueo de nivel de fila en las búsquedas de índice contra un índice no agrupado y con un bloqueo de nivel de grupo de filas en los escaneos de tabla completa contra el índice de almacén de columnas. Usa esto para lograr una mayor simultaneidad de lectura/escritura mediante el uso adecuado de bloqueos a nivel de fila y grupo de filas.

--Granular locking example
--Store table t_account as a columnstore table.
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account

--Add a nonclustered index for use with this example
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

--Look at locking with access through the nonclustered index
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

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 aislamiento de instantánea de lectura confirmada

Use el aislamiento de instantáneas (SI) para garantizar la coherencia transaccional y el aislamiento de instantáneas de lectura confirmada (RCSI) para garantizar la coherencia de nivel de instrucción para las consultas en los índices de almacén de columnas. Esto permite que las consultas se ejecuten sin provocar el bloqueo de los procesos de escritura de datos. Este comportamiento de no bloqueo también reduce notablemente la probabilidad de que se produzcan interbloqueos en las transacciones complejas. Para obtener más información, consulte Niveles de aislamiento basados en versiones de fila en el motor de base de datos.

Related content

  • Last updated on