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

Настройка производительности с упорядоченными индексами columnstore

Применимо к: SQL Server 2022 (16.x) и более поздних версий базы данных SQLAzure Для Управляемого экземпляраSQL Azure в Microsoft Fabric

Благодаря включению функции эффективной ликвидации сегментов, индексы columnstore с упорядочиванием обеспечивают более высокую производительность, пропуская большие объемы упорядоченных данных, не соответствующих условиям запроса. Загрузка данных в упорядоченный индекс columnstore и поддержание его упорядоченности через перестройки индекса может занять больше времени, чем в неупорядоченном индексе, из-за операции сортировки данных. Однако запросы к упорядоченным индексам columnstore могут выполняться быстрее.

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

Сведения о доступности упорядоченного индекса columnstore см. в разделе " Упорядоченная доступность индекса columnstore".

Дополнительные сведения о недавно добавленных функциях для индексов columnstore см. в статье "Новые возможности индексов columnstore".

Упорядоченный и неупорядоченный индекс columnstore

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

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

При создании упорядоченного индекса columnstore ядро СУБД сортирует существующие данные по ключам порядка, заданным до того, как построитель индексов сжимает их на сегменты. При отсортированных данных перекрытие сегментов уменьшается или устраняется, что позволяет запросам более эффективно устранять сегменты и, таким образом, обеспечивать более быструю работу, так как нужно читать меньше сегментов с диска.

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

Полная сортировка предоставляется для упорядоченных кластеризованных индексов columnstore, созданных или перестроенных с помощью обоих ONLINE = ON и MAXDOP = 1 параметров. В этом случае сортировка не ограничивается доступной памятью, так как она использует tempdb базу данных для разлива данных, которые не соответствуют памяти. Это может замедлить процесс сборки индекса из-за дополнительных tempdb операций ввода-вывода. Однако при перестроении индекса в сети запросы могут продолжать использовать существующий индекс, пока новый упорядоченный индекс перестраивается.

Полная сортировка также может быть предоставлена для упорядоченных кластеризованных и некластеризованных columnstore индексов, созданных или перестроенных с использованием обоих параметров ONLINE = OFF и MAXDOP = 1, если объем данных для сортировки достаточно мал, чтобы полностью разместиться в доступной памяти.

Во всех остальных случаях сортировка упорядоченных индексов columnstore является частичной.

Примечание.

В настоящее время упорядоченные индексы columnstore можно создавать или перестроить в сети только в базе данных SQL Azure, в Управляемом экземпляре SQL AzureAUTD и в предварительной версии SQL Server 2025 (17.x).

Чтобы проверить диапазоны сегментов для столбца и определить, есть ли перекрывающиеся сегменты, используйте следующий запрос, заменив заполнители схемой, таблицей и именами столбцов:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Например, выходные данные этого запроса для полностью отсортированного индекса columnstore могут выглядеть следующим образом. Обратите внимание, что в столбцах min_data_id и max_data_id для разных сегментов нет перекрытия.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Примечание.

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

Чтобы сортировать данные в индексе после вставки новых данных или обновления существующих данных, перестройте индекс.

Для автономного перестроения секционированного колонночного индекса перестроение выполняется по одному разделу одновременно. Данные в разделе, который находится в процессе восстановления, недоступны до завершения восстановления этого раздела.

Данные остаются доступными во время перестроения в режиме онлайн. Дополнительные сведения см. в разделе "Выполнение операций индекса в сети".

Производительность запросов

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

Запросы с указанными шаблонами обычно выполняются быстрее с упорядоченными columnstore-индексами.

  • Запросы с предикатами равенства, неравенства или диапазона.
  • Запросы, в которых столбцы предиката и упорядоченные столбцы CCI совпадают.

В этом примере таблица T1 содержит кластеризованный индекс columnstore, упорядоченный в последовательности Col_C, Col_Bи Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

Эффективность запросов 1 и 2 больше выигрывает от упорядоченного индекса columnstore, чем запросы 3 и 4, потому что они ссылаются на все упорядоченные столбцы.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Производительность загрузки данных

Производительность загрузки данных в таблицу с упорядоченным индексом columnstore похожа на секционированную таблицу. Загрузка данных может занять больше времени, чем с неупорядоченным индексом columnstore из-за операции сортировки данных, однако запросы могут выполняться быстрее после этого.

Уменьшение перекрытия сегментов

Количество перекрывающихся сегментов зависит от размера данных для сортировки, доступной памяти и максимальной степени параллелизма (MAXDOP) во время сборки упорядоченного индекса columnstore. Следующие стратегии сокращают перекрытие сегментов, однако они могут сделать так, что процесс сборки индекса может занять больше времени.

  • Если доступно онлайновое создание индекса, используйте обе опции ONLINE = ON и MAXDOP = 1 при создании упорядоченного кластеризованного индекса колонкового хранилища. При этом создается полностью отсортированный индекс.
  • Если сборка индекса в Сети недоступна, используйте MAXDOP = 1.
  • Перед загрузкой предварительно отсортируйте данные по ключам сортировки.

Если MAXDOP больше 1, каждый поток, используемый для упорядоченного индекса columnstore, работает с подмножеством данных и сортирует его локально. Глобальная сортировка данных, отсортированных различными потоками, отсутствует. Использование параллельных потоков может сократить время создания индекса, но создает более перекрывающиеся сегменты, чем при использовании одного потока. Использование одной потоковой операции обеспечивает наивысшее качество сжатия. Можно указать MAXDOP с помощью CREATE INDEX команды.

Примеры

Проверка упорядоченных столбцов и порядковый номер.

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Создание упорядоченного индекса колоночного хранилища

Кластеризованный упорядоченный индекс columnstore:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Некластеризованный упорядоченный индекс columnstore:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Добавление или удаление столбцов заказа и перестроение существующего упорядоченного индекса columnstore

Кластеризованный упорядоченный индекс columnstore:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Некластеризованный упорядоченный индекс columnstore:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Создать упорядоченный кластеризованный колонночный индекс онлайн с полной сортировкой в кучной таблице

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Перестроение упорядоченного кластеризованного индекса columnstore в Сети с полной сортировкой

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

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