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

Статья
04/04/2023

Применимо к: SQL Server Azure SQL Database Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

Индексы columnstore позволяют повысить производительность обработки аналитических рабочих нагрузок и запросов хранилища данных максимум в 100 раз и увеличить эффективность сжатия до 10 раз по сравнению с традиционными индексами rowstore. Эти рекомендации помогут добиться высокой производительности запросов, для обеспечения которой предназначены индексы columnstore. Дальнейшие пояснения о производительности индексов columnstore см. в конце раздела.

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

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

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

Сжатие данных

Индексы columnstore позволяют повысить эффективность сжатия данных в 10 раз по сравнению с индексами rowstore. Это значительно уменьшает число операций ввода-вывода, необходимых для выполнения запросов аналитики, и, следовательно, повышает производительность запросов.

Индексы columnstore считывают сжатые данные с диска. Таким образом, уменьшается число байтов данных для считывания в память.
Индексы columnstore хранят данные в памяти в сжатом виде. Это позволяет сократить количество операций ввода-вывода за счет того, что одни и те же данные считываются в память меньшее количество раз. Например, за счет десятикратного сжатия индексы columnstore могут хранить в десять раз больше данных в памяти по сравнению с хранением данных в несжатом виде. Поскольку в памяти находится больше данных, индекс columnstore с большей вероятностью сможет находить нужные данные без дополнительных операций чтения с диска.
Индексы columnstore сжимают данные по столбцам, а не по строкам, обеспечивая высокую степень сжатия и сокращая объем данных, хранимых на диске. Каждый столбец сжимается и сохраняется по отдельности. Данные в столбце всегда имеют один тип данных и, как правило, схожие значения. Методы сжатия данных целесообразно использовать для достижения более высоких коэффициентов сжатия схожих значений.
Например, если в таблице фактов хранятся адреса клиентов и есть столбец для страны или региона, общее количество возможных значений меньше 200. Некоторые из этих значений будут повторяться много раз. Если таблица фактов содержит 100 миллионов строк, столбец страны или региона будет легко сжиматься и требует очень мало места в хранилище. Построчное сжатие не может таким образом использовать подобие значений столбцов с прописной буквой и будет использовать больше байтов для сжатия значений в столбце страны или региона.

Исключение столбцов

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

Возможность исключения столбцов обусловлена тем, что данные упорядочиваются и сжимаются по столбцам. Однако если данные хранятся построчно, значения столбца в каждой строке физически сохраняются вместе и их сложно разделить. Чтобы извлечь определенные значения столбцов, обработчику запросов нужно считать всю строку. Это увеличивает количество операций ввода-вывода из-за ненужного считывания в память дополнительных данных.
Например, если таблица содержит 50 столбцов, а запрос использует только 5 столбцов, индекс columnstore извлечет с диска только 5 столбцов. Он пропустит чтение 45 других столбцов. Это сокращает число операций ввода-вывода дополнительно на 90 %, так как предполагается, что все столбцы имеют одинаковый размер. Если же данные хранятся в rowstore, обработчику запросов потребуется считать 45 дополнительных столбцов.

Исключение групп строк

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

Когда индекс columnstore должен выполнять полное сканирование таблицы?

Начиная с SQL Server 2016 (13.x);, в кластеризованном индексе columnstore можно создавать один или несколько обычных некластеризованных индексов в виде сбалансированного дерева так же, как в куче rowstore. Некластеризованные индексы в виде сбалансированного дерева могут ускорить выполнение запроса, содержащего предикат равенства или предикат с небольшим диапазоном значений. Для более сложных предикатов оптимизатор запросов может выбрать полное сканирование таблицы. Без возможности пропуска строк групп полное сканирование таблицы может занимать очень много времени, особенно это касается больших таблиц.

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

Например, предприятие розничной торговли смоделировало свои данные по продажам с помощью таблицы фактов с кластеризованным индексом columnstore. При каждой новой операции продажи происходит сохранение различных атрибутов транзакции, включая дату продажи. Что интересно, несмотря на то, что индексы columnstore не гарантируют поддержку сортировки, строки в эту таблицу будут загружаться с сортировкой по дате. Со временем размер этой таблицы будет увеличиваться. Хотя предприятие розничной торговли может хранить данные о продажах за последние 10 лет, может потребоваться выполнить аналитический запрос только для вычисления совокупных данных по последнему кварталу. Индексы columnstore могут исключить доступ к данным по предыдущим 39 кварталам за счет простого просмотра метаданных для столбца даты. Это позволит получить дополнительное 97-процентное сокращение объема данных, считываемых и обрабатываемых в памяти.

Какие группы строк пропускаются при полном сканировании таблицы?

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

Дополнительные сведения о группах строк см. в статье Руководство по проектированию индекса columnstore.

Выполнение в пакетном режиме

Выполнение в пакетном режиме — это обработка набора строк (как правило, не более 900) в целях повышения эффективности выполнения запросов. Например, запрос SELECT SUM (Sales) FROM SalesData вычисляет общий объем продаж из таблицы SalesData. В пакетном режиме подсистема выполнения запросов обрабатывает данные в группе из 900 значений. Это распределяет затраты на доступ к метаданным и другие типы накладных расходов на все строки в пакете, а не оплачивает затраты за каждую строку, тем самым значительно снижая путь к коду. Обработка в пакетном режиме применяется к сжатым данным, когда это возможно, и исключает необходимость применения некоторых операторов обмена, используемых в режиме обработки строк. При этом скорость выполнения аналитических запросов увеличивается на несколько порядков.

Однако работу в пакетном режиме поддерживает лишь часть операторов выполнения запросов. Например, операции DML (вставка, удаление или обновление) выполняются одновременно для одной строки. Операторы пакетного режима предназначены для ускорения производительности запросов, таких как сканирование, соединение, статистическая обработка, сортировка и т. д. Так как индекс columnstore впервые появился в SQL Server 2012 (11.x), набор операторов, которые могут выполняться в пакетном режиме, постоянно расширяется. В таблице ниже приведены операторы, которые выполняются в пакетном режиме в соответствии с версией продукта.

Операторы пакетного режима	Использование	SQL Server 2012 (11.x)	SQL Server 2014 (12.x)	SQL Server 2016 (13.x); и База данных SQL¹	Комментарии
Операции DML (вставка, удаление, обновление, объединение)		нет	нет	нет	Операции DML не являются операциями пакетного режима, так как они не выполняются параллельно. Даже включение последовательного режима пакетной обработки для DML не даст никаких значительных преимуществ.
columnstore index scan	SCAN	Недоступно	да	да	Для индексов columnstore можно передать предикат на узел SCAN.
Columnstore index Scan (некластеризованный)	SCAN	да	да	да	да
index seek		Недоступно	Недоступно	нет	Мы выполняем операцию поиска с помощью некластеризованного индекса B-дерева в режиме строки.
compute scalar	Выражение, результатом вычисления которого является скалярное значение.	да	да	да	Существует несколько ограничений по типу данных. Это относится ко всем операторам пакетного режима.
объединение	UNION и UNION ALL	нет	да	да
фильтр	Применение предикатов	да	да	да
hash match	Статистические функции на основе хэша, внешнее хэш-соединение, правое хэш-соединение, левое хэш-соединение, правое внутреннее соединение, левое внутреннее соединение	да	да	да	Ограничения для статистической обработки: отсутствуют функции min и max для строк. Доступны следующие статистические функции: sum, count, avg, min, max. Ограничения для соединения: отсутствуют соединения несоответствующих типов в нецелочисленных типах.
merge join		нет	нет	нет
многопоточные запросы		да	да	да
вложенные циклы		нет	нет	нет
однопоточные запросы, выполняемые с MAXDOP 1		нет	нет	да
однопоточные запросы с планом последовательных запросов		нет	нет	да
sort	Упорядочение по предложению в SCAN с индексом columnstore.	нет	нет	да
top sort		нет	нет	да
window aggregates		Недоступно	Недоступно	да	Новый оператор в SQL Server 2016 (13.x);.

¹Применяется к SQL Server 2016 (13.x);, уровням База данных SQL "Премиум", "Стандартный" — S3 и выше, всем уровням виртуальных ядер, а также Система платформы аналитики (PDW).

Дополнительные сведения см. в статье Руководство по архитектуре обработки запросов.

Агрегированная отправка

Обычно для выполнения статистических вычислений требуется извлечь соответствующие строки с узла SCAN и вычислить значения в пакетном режиме. Несмотря на то, что в этом случае обеспечивается высокая производительность, в SQL Server 2016 (13.x); операцию статистического вычисления можно включить на узле SCAN, чтобы значительно повысить производительность статистических вычислений на основе пакетного режима. При этом должны соблюдаться указанные далее условия.

Статистические выражения — MIN, MAX, SUM, COUNT и COUNT(*).
Статистический оператор должен находиться на узле SCAN или узле SCAN с предложением GROUP BY.
Статистическое выражение не является уникальным.
Столбец статистической обработки не является строковым.
Столбец статистической обработки не является виртуальным.
Входной и выходной тип данных должен иметь один из следующих значений и соответствовать 64 битам:
- tinyint, int, bigint, smallint, bit
- smallmoney, moneyи numeric с точностью <= 18 decimal
- smalldate, date, datetime, datetime2, time

Например, статистическое вычисление включено в обоих приведенных ниже запросах.

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Включение предиката строки

При разработке схемы хранилища данных рекомендуется использовать схему типа "звезда" или "снежинка", состоящую из одной или нескольких таблиц фактов и нескольких таблиц измерений. В таблице фактов хранятся бизнес-измерения или транзакции, а в таблице измерений — измерения, относительно которых требуется анализировать факты.

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

Рассмотрим таблицу измерения Products. Стандартным первичным ключом будет ProductCode, который обычно представлен как тип данных string. Для повышения производительности запросов рекомендуется создать суррогатный ключ (обычно это целочисленный столбец) для ссылки на строки в таблице измерения из таблицы фактов.

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

Для улучшения производительности запросов при включении предиката строки используется первичный или вторичный словарь, созданный для столбцов. Например, рассмотрим сегмент столбца строки в группе строк, состоящей из 100 разных строковых значений. Это означает, что при условии наличия 1 млн строк на каждое различающееся строковое значение существует в среднем 10 000 ссылок.

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

Возвращается только соответствующая строка, что приводит к сокращению числа строк, которые должны передаваться из узла SCAN.
Значительно уменьшается число сравнений строк. В этом примере вместо 1 миллиона сравнений требуется только 100 сравнений строк. Существует ряд ограничений, указанных ниже.
- Отсутствует включение строки предиката для разностных групп строк. Отсутствует словарь для столбцов в разностных группах строк.
- Если размер словаря превышает 64 КБ записей, то строковый предикат отсутствует.
- Выражения, значением вычисления которых является NULL, не поддерживаются.

Исключение сегментов

Выбор типа данных может существенно повлиять на производительность запросов на основе предикатов общих фильтров для запросов к индексу columnstore.

В данных columnstore группы строк состоят из сегментов столбцов. С каждым сегментом имеются метаданные, позволяющие быстро устранять сегменты, не считывая их. Исключение сегмента применяется к числовым типам данных, типам данных даты и времени, а также к типу данных datetimeoffset с масштабом меньше или равным двум. Начиная с SQL Server 2022 (16.x) возможности исключения сегментов распространяются на строковые, двоичные типы данных, типы данных GUID и тип данных datetimeoffset для масштаба больше двух.

После обновления до версии SQL Server, поддерживающей исключение сегмента min/max строки (SQL Server 2022 (16.x) и более поздних версий, индекс columnstore не будет использовать эту функцию, пока он не будет перестроен с помощью REBUILD или DROP/CREATE.

Исключение сегмента не применяется к типам данных LOB, таким как (максимальная) длина типа данных.

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

В Azure Synapse Analytics и начиная с SQL Server 2022 (16.x) можно создавать упорядоченные кластеризованные индексы columnstore, которые позволяют упорядочивать по столбцам, чтобы упростить исключение сегментов, особенно для строковых столбцов. В упорядоченных кластеризованных индексах columnstore исключение сегментов в первом столбце в ключе индекса является наиболее эффективным, так как оно отсортировано. Повышение производительности из-за исключения сегментов в других столбцах таблицы будет менее предсказуемым. Дополнительные сведения об упорядоченных кластеризованных индексах columnstore см. в статье Использование упорядоченного кластеризованного индекса columnstore для больших таблиц хранилища данных.

С помощью параметра подключения к запросу SET STATISTICS IO можно просмотреть исключение сегментов в действии. Найдите такие выходные данные, как показано ниже, чтобы указать, что произошло исключение сегмента. Группы строк состоят из сегментов столбцов, поэтому это может указывать на исключение сегмента. Приведенный ниже пример выходных данных ввода-вывода SET STATISTICS для запроса, примерно 83 % данных был пропущен запросом:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

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

Рекомендации по улучшению производительности запросов

1. Упорядочение данных для исключения дополнительных групп строк из полного сканирования таблицы

2. Планирование достаточного объема памяти для параллельного создания индексов columnstore