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

  • Статья

Применимо к:SQL ServerAzure SQL DatabaseAzure Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

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

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

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

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

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

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

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

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

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

    Заметка

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

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

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

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

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

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

Пояснения о производительности 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), можно создать один или несколько обычных некластеризованных индексов B-дерева в кластеризованном индексе columnstore так же, как и в куче rowstore. Некластеризованные индексы в виде сбалансированного дерева могут ускорить выполнение запроса, содержащего предикат равенства или предикат с небольшим диапазоном значений. Для более сложных предикатов оптимизатор запросов может выбрать полное сканирование таблицы. Без возможности пропуска строк групп полное сканирование таблицы может занимать очень много времени, особенно это касается больших таблиц.

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

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

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

  1. Возвращается только соответствующая строка, что приводит к сокращению числа строк, которые должны передаваться из узла SCAN.

  2. Значительно уменьшается число сравнений строк. В этом примере вместо 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 не будет использовать эту функцию, пока она не будет перестроена с помощью ПЕРЕСТРОЕНИЯ или DROP/CREATE.

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

В настоящее время только SQL Server 2022 (16.x) и более поздних версий поддерживает исключение кластеризованной LIKE группы строк columnstore для префикса предикатов, например 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.
...

Далее