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

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

  • Статья

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

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

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

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

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

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

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

  • Используйте кластеризованный индекс rowstore. Если общий предикат запроса находится в столбце (например, C1) не связан с порядком вставки, создайте кластеризованный индекс rowstore в столбце C1. Затем удалите кластеризованный индекс rowstore и создайте кластеризованный индекс columnstore. Если вы создаете кластеризованный индекс columnstore явным образом, MAXDOP = 1результирующий кластеризованный индекс columnstore идеально упорядочен по столбцу C1. При указании MAXDOP = 8вы увидите перекрытие значений в восьми группах строк. Для некластеризованного индекса columnstore (NCCI), если в таблице есть кластеризованный индекс rowstore, строки уже упорядочены ключом кластеризованного индекса. В этом случае некластеризованный индекс columnstore также автоматически упорядочен. Индекс columnstore по сути не поддерживает порядок строк. Так как новые строки вставляются или обновляются старые строки, может потребоваться повторить процесс, так как производительность запросов аналитики может ухудшаться.

  • Реализуйте секционирование таблиц. Можно секционировать индекс columnstore, а затем использовать исключение секций для уменьшения количества групп строк для сканирования. Например, таблица фактов хранит покупки, сделанные клиентами. Распространенный шаблон запроса — поиск квартальных покупок по customer. В этом случае объедините столбец порядка вставки с секционированием по customer столбцу. Каждая секция содержит строки для каждой customer, упорядоченной при вставке. Кроме того, рекомендуется использовать секционирование таблиц, если необходимо удалить старые данные из columnstore. Выключение и усечение секций, которые не нужны, является эффективной стратегией удаления данных без создания фрагментации.

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

    Примечание.

    Начиная с SQL Server 2019 (15.x), кортеж-перемещение помогает фоновой задачей слияния. Эта задача автоматически сжимает небольшие разностные группы строк OPEN, которые существовали в течение некоторого времени, как определено внутренним пороговым значением, или объединяет сжатые группы строк, из которых было удалено большое количество строк. Это со временем повышает качество индекса columnstore. При удалении больших объемов данных из индекса columnstore рекомендуется разделить операцию на меньшие пакеты удаления с течением времени. Пакетная обработка позволяет фоновой задаче слияния обрабатывать задачу объединения небольших групп строк и повысить качество индекса. После удаления данных не нужно планировать периоды обслуживания реорганизации индекса. Дополнительные сведения о терминах и понятиях columnstore см. в статье "Общие сведения об индексах Columnstore".

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

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

Объем памяти, необходимый для создания индекса columnstore, зависит от количества столбцов, числа столбцов строкового типа, степени параллелизма (DOP) и характеристик данных. Например, если в таблице меньше одного миллиона строк, SQL Server использует только один поток для создания индекса columnstore.

Если в таблице более одного миллиона строк, но SQL Server не может получить достаточно большого объема памяти, чтобы создать индекс с помощью MAXDOP, SQL Server автоматически уменьшается MAXDOP по мере необходимости. В некоторых случаях DOP необходимо уменьшить до одного, чтобы создать индекс под ограниченной памятью в доступном предоставлении памяти.

Так как SQL Server 2016 (13.x) запрос всегда работает в пакетном режиме. В предыдущих выпусках пакетное выполнение используется, только если значение DOP больше единицы.

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

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

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

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

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

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

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

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

Например, таблица фактов хранит адреса клиентов и имеет столбец для country-region. Общее количество возможных значений меньше 200. Некоторые из этих значений повторяются много раз. Если таблица фактов имеет 100 миллионов строк, country-region столбец сжимается легко и требует небольшого хранилища. Сжатие строк по строкам не может прописывать сходство значений столбцов таким образом и должно использовать больше байтов для сжатия значений в столбце country-region .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1 Относится к 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, decimal and numeric with precision <= 18
    • smalldate, date, datetime, datetime2, time

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

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

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

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

Совет

В таблице фактов хранятся бизнес-измерения или транзакции, а в таблице измерений — измерения, относительно которых требуется анализировать факты. Дополнительные сведения о моделировании измерений см. в разделе "Моделирование измерений" в Microsoft Fabric.

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

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

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

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

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

Устранение сегментов

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

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

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

Устранение сегментов не применяется к типам бизнес-данных, таким как длина типа данных (max).

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

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

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

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

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