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

  • Статья

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

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

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

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

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

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

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

Начиная с 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.

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