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

  • Статья

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

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

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

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

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

По умолчанию для каждой таблицы, созданной без параметра индекса, внутренний компонент (построитель индексов) создает на нем неупорядоченный кластеризованный индекс columnstore (CCI). Данные в каждом столбце сжимаются в отдельный сегмент CCI rowgroup. Существуют метаданные для диапазона значений каждого сегмента, поэтому сегменты, находящиеся вне границ предиката запроса, не считываются с диска во время выполнения запроса. CCI обеспечивает наивысший уровень сжатия данных и уменьшает размер сегментов для чтения, чтобы запросы могли выполняться быстрее. Однако поскольку построитель индексов не сортирует данные перед их сжатием в сегменты, могут возникать сегменты с перекрывающимися диапазонами значений. Это приводит к тому, что запросы считывают больше сегментов с диска и на завершение процесса уходит больше времени.

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

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

SELECT
o.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,
cls.max_data_id-cls.min_data_id as difference 

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 o.name = '<Table Name>' and cols.name = '<Column Name>'  
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Примечание.

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

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

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

Запросы со всеми этими шаблонами обычно выполняются быстрее с упорядоченным CCI.

  • Запросы имеют предикаты равенства, неравенства или диапазона
  • Столбцы предиката и упорядоченные столбцы 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 может воспользоваться более упорядоченной CCI, чем другие запросы, так как они ссылаются на все упорядоченные столбцы CCI.

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

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

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

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

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

  • Создайте упорядоченный CCI с OPTION (MAXDOP = 1). Каждый поток, используемый для создания упорядоченного CCI, работает с подмножеством данных и сортирует их локально. Глобальная сортировка данных, отсортированных различными потоками, отсутствует. Использование параллельных потоков позволяет сократить время создания упорядоченного CCI, но при этом будет создано больше пересекающихся сегментов, чем при использовании одного потока. Использование одной потоковой операции обеспечивает наивысшее качество сжатия. Можно указать MAXDOP с помощью CREATE INDEX команд или CREATE TABLE команд. Например:
CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
  • Предварительная сортировка данных по ключам сортировки перед их загрузкой в таблицы.

Ниже приведен пример упорядоченного распределения таблиц CCI с нулевым сегментом, перекрывающимся приведенными выше рекомендациями. Упорядоченная CCI упорядочена по столбцу bigint без дубликатов.

Снимок экрана: текстовые данные, показывающие отсутствие перекрывающихся сегментов.

Создание упорядоченного CCI в больших таблицах

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

  1. Создайте секции в целевой большой таблице (с именем Table_A).
  2. Создайте пустую упорядоченную таблицу CCI (с именем Table_B) с той же таблицей и схемой секционирования, что и в Table_A.
  3. Переместите одну секцию из Table_A в Table_B.
  4. Выполните ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID>, чтобы перестроить подключенную секцию в Table_B.
  5. Повторите шаги 3 и 4 для каждой секции в Table_A.
  6. После переключения всех секций с Table_A на Table_B и их перестроения удалите Table_A и переименуйте Table_B в Table_A.

Возможности SQL Server 2022

В выпуске SQL Server 2022 (16.x) появились упорядоченные кластеризованные индексы columnstore, аналогичные соответствующей возможности выделенных пулов SQL Azure Synapse.

  • SQL Server 2022 (16.x) и более поздних версий и других платформ SQL поддерживают кластеризованные возможности исключения сегментов columnstore для строковых, двоичных и guid типов данных и типа данных datetimeoffset для масштабирования больше двух. Ранее это исключение сегмента применяется к числовым, датовым и временным типам данных, а также к типу данных datetimeoffset с масштабированием меньше или равно двум.
  • В настоящее время только SQL Server 2022 (16.x) и более поздних версий и других платформ SQL поддерживают исключение кластеризованной LIKE группы строк columnstore для префикса предикатов, например column LIKE 'string%'. Исключение сегментов не поддерживается для использования не префикса, например column LIKE '%string'LIKE.

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

Дополнительные сведения см. в разделе Новые возможности индексов Columnstore.

Сведения о упорядоченных индексах columnstore в выделенных пулах SQL в Azure Synapse Analytics см. в разделе "Настройка производительности с упорядоченными кластеризованными индексами columnstore".

Примеры

А. Чтобы проверить упорядоченные столбцы и порядковый номер упорядочивания:

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

B. Чтобы изменить порядковый номер столбца, добавить или удалить столбцы из списка упорядочивания либо изменить CCI на упорядоченный CCI:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

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