Поделиться через

Индексы Columnstore в хранилище данных

  • Статья

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр Azure SQLАналитическая платформенная система (PDW)SQL база данных в Microsoft Fabric

Индексы Columnstore в сочетании с секционированием необходимы для создания хранилища данных SQL Server. В этой статье рассматриваются ключевые варианты использования и примеры проектов хранения данных с помощью модуля База данных SQL.

Ключевые функции для хранения данных

SQL Server 2016 (13.x) представил эти функции для улучшения производительности columnstore:

  • AlwaysOn поддерживает запросы к индексу columnstore в доступной для чтения вторичной реплике.
  • MARS поддерживает колоночные индексы.
  • Новое динамическое представление управления sys.dm_db_column_store_row_group_physical_stats (Transact-SQL) предоставляет сведения об устранении неполадок производительности на уровне группы строк.
  • Однопотоковые запросы на колонковые индексы могут выполняться в пакетном режиме. Ранее в пакетном режиме могли выполняться только многопотоковые запросы.
  • Оператор SORT выполняется в пакетном режиме.
  • Несколько DISTINCT операций выполняются в пакетном режиме.
  • Статистические операции с окнами теперь выполняются в пакетном режиме для уровня совместимости базы данных 130 или более высокого.
  • Оптимизация агрегаций для эффективной обработки агрегатов. Работает с любым уровнем совместимости базы данных.
  • Выдавливание строкового предиката для эффективной обработки строковых предикатов. Работает с любым уровнем совместимости базы данных.
  • Изоляция моментального снимка для уровня совместимости базы данных 130 или более высокого.
  • В SQL Server 2022 (16.x) появились упорядоченные кластеризованные индексы columnstore. Дополнительные сведения см. в разделе CREATE COLUMNSTORE INDEX и Настройка производительности с упорядоченными индексами columnstore. Сведения о доступности упорядоченного columnstore индекса см. в разделе "Доступность упорядоченного columnstore индекса".

Дополнительные сведения о новых функциях в версиях и платформах SQL Server и Azure SQL см. в разделе Что нового в колонковых индексах.

Повышение производительности за счёт сочетания некластеризованных индексов и индексов столбцовых хранилищ данных.

Начиная с SQL Server 2016 (13.x), можно определить некластеризованный индекс rowstore поверх кластеризованного индекса columnstore.

Пример. Повышение эффективности операций поиска в таблицах с помощью некластеризованного индекса

Для повышения эффективности операций поиска в таблицах хранилища данных можно создать некластеризованный индекс, предназначенный для запуска запросов, которые показывают максимальную производительность с операциями поиска в таблицах. Например, запросы, которые ищут соответствующие значения или возвращают небольшой диапазон значений, лучше работают с индексом дерева B, а не с индексом columnstore. Они не требуют полного сканирования таблицы через индекс columnstore и возвращают правильный результат быстрее, выполняя двоичный поиск по индексу дерева 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);

Пример. Использование некластеризованного индекса для принудительного применения ограничения первичного ключа в таблице columnstore

По своей конструкции таблица columnstore не поддерживает ограничение кластеризованного первичного ключа. Теперь с помощью некластеризованного индекса для таблицы columnstore можно принудительно применить ограничение первичного ключа. Первичный ключ равнозначен ограничению UNIQUE в столбце, отличном от NULL, а SQL Server реализует ограничение UNIQUE как некластеризованный индекс. Объединяя эти факты, следующий пример определяет ограничение UNIQUE для столбца accountkey, не равного NULL. Результат представляет собой некластеризованный индекс, принудительно применяющий ограничение первичного ключа в виде ограничения UNIQUE для столбца, отличного от NULL.

Далее таблица преобразуется в кластеризованный индекс columnstore. Во время преобразования некластеризованный индекс сохраняется. Результат представляет собой кластеризованный индекс columnstore с некластеризованным индексом, принудительно применяющим ограничение первичного ключа. Поскольку любое обновление или вставка в таблицу columnstore также влияет на некластеризованный индекс, все операции, которые нарушают уникальное ограничение и не допускают значения NULL, приводят к сбою всей операции.

Результат представляет собой индекс columnstore с некластеризованным индексом, принудительно применяющим ограничение первичного ключа для обоих индексов.

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

Повышение производительности за счет включения блокировки на уровне строки и на уровне группы строк

Чтобы дополнить некластеризованный индекс в функции индекса columnstore, SQL Server 2016 (13.x) предлагает детализированную блокировку для операций выбора, обновления и удаления. Запросы могут выполняться с блокировкой на уровне строки для индексных операций поиска по некластеризованному индексу и блокировкой на уровне группы строк для полного сканирования таблиц по индексу columnstore. Это позволяет повысить параллелизм чтения и записи при надлежащем использовании блокировки на уровне строки и на уровне группы строк.

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

Изоляция моментальных снимков и изоляция моментальных снимков, зафиксированные для чтения

Используйте изоляцию моментальных снимков (SI), чтобы гарантировать согласованность транзакций и изоляцию моментальных снимков с фиксацией для чтения (RCSI), чтобы гарантировать согласованность уровней инструкций для запросов к индексам columnstore. Это позволяет запросам выполняться без блокировки модулей записи данных. Такое неблокирующее поведение также значительно снижает вероятность возникновения взаимоблокировок при сложных транзакциях. Дополнительные сведения см. в разделе "Изоляция моментальных снимков" в SQL Server.

Связанный контент