Индексы колоночной структуры в хранилищах данных

Статья
2025-04-10

Применимо к: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) предоставляет сведения об устранении неполадок производительности на уровне группы строк.
Все запросы к индексам columnstore могут выполняться в пакетном режиме. Ранее только параллельные запросы могли выполняться в пакетном режиме.
Операторы сортировки, уникальной сортировки и уникальные выполняются в пакетном режиме.
Агрегаты окон теперь выполняются в пакетном режиме для уровня совместимости базы данных 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
);

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

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

Так как таблица может иметь не более одного кластеризованного индекса, таблица с кластеризованным индексом columnstore не может иметь ограничения кластеризованного первичного ключа. Чтобы создать ограничение первичного ключа для таблицы columnstore, необходимо объявить его как некластеризованный.

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

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

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

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

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

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

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

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