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

Область применения:yesSQL Server (все поддерживаемые версии) YesБаза данных SQL Azure YesУправляемый экземпляр SQL Azure yesAzure Synapse Analytics yesAnalytics Platform 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, которые существовали некоторое время в соответствии с внутренним пороговым значением, или объединяет группы строк с состоянием COMPRESSED, из которых было удалено большое количество строк. Это со временем повышает качество индекса 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 хранят данные в памяти в сжатом виде. Это позволяет сократить количество операций ввода-вывода за счет того, что одни и те же данные считываются в память меньшее количество раз. Например, за счет десятикратного сжатия индексы 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); и База данных SQL1 Комментарии
Операции DML (вставка, удаление, обновление, объединение) нет нет нет Операции DML не являются операциями пакетного режима, так как они не выполняются параллельно. Даже включение последовательного режима пакетной обработки для DML не даст никаких значительных преимуществ.
columnstore index scan SCAN Н/Д да да Для индексов columnstore можно передать предикат на узел SCAN.
columnstore Index Scan (nonclustered) SCAN да Да Да да
index seek Н/Д Н/Д нет Операции поиска выполняются с помощью некластеризованного индекса в виде сбалансированного дерева в режиме строк.
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);.

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, moneydecimal и numeric с точностью <= 18
    • smalldate, date, datetime, datetime2, time

Дополнительная поддержка включения статистических вычислений осуществляется за счет эффективных статистических вычислений сжатых или закодированных данных в среде с поддержкой кэша и использования SIMD.

aggregate pushdown

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

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 КБ.
    • Выражения, значением вычисления которых является NULL, не поддерживаются.

См. также:

Руководство по загрузке данных индексов columnstore в руководстве по проектированию индексов columnstore
Начало работы с Columnstore для получения операционной аналитики в реальном времени
Индексы сolumnstore для хранилищ данных
Реорганизация и перестроение индексов
Архитектура индексов columnstore
Инструкция CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)