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

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

  • Статья

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Например, розничный бизнес моделирует свои данные о продажах с использованием таблицы фактов с кластеризованным 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 (вставка, удаление, обновление, объединение) нет нет нет DML не является операцией в пакетном режиме, так как она не параллельна. Даже включение последовательного режима пакетной обработки для DML не даст никаких значительных преимуществ.
сканирование колонного индекса SCAN Недоступно да да Для индексов columnstore можно передать предикат на узел SCAN.
Проверка индекса columnstore (некластеризованная) СКАНИРОВАНИЕ да да да да
поиск по индексу Недоступно Недоступно нет Мы выполняем операцию поиска с помощью некластеризованного индекса B-дерева в режиме строки.
вычислить скаляр Выражение, результатом вычисления которого является скалярное значение. да да да Как и все операторы пакетного режима, существуют некоторые ограничения на тип данных.
объединение UNION и UNION ALL нет да да
Фильтр Применение предикатов да да да
совпадение хэшей Агрегатные функции на основе хэша, внешнее хэш-объединение, правое хэш-объединение, левое хэш-объединение, правое внутреннее объединение, левое внутреннее объединение да да да Ограничения для статистической обработки: отсутствуют функции min и max для строк. Доступны следующие агрегатные функции: sum, count, avg, min, max.
Ограничения для соединения: отсутствуют соединения несоответствующих типов в нецелочисленных типах.
соединение слиянием нет нет нет
многопоточные запросы да да да
вложенные циклы нет нет нет
однопоточные запросы, выполняемые с MAXDOP 1 нет нет да
однопоточные запросы с последовательным планом запроса нет нет да
сортировка Упорядочение по предложению в SCAN с индексом columnstore. нет нет да
топ сорт нет нет да
оконные агрегаты Недоступно Недоступно да Новый оператор в SQL Server 2016 (13.x).

1 Относится к SQL Server 2016 (13.x), премиум-уровням и стандартным уровням S3 и выше базы данных SQL, ко всем уровням vCore и системе платформы аналитики (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 и numeric с точностью <= 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обычно представленный в виде строки. Для повышения производительности запросов рекомендуется создать суррогатный ключ, обычно целый столбец integer, чтобы обращаться к строке в таблице измерений из таблицы фактов.

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

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

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

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

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

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

После обновления до версии SQL Server, который поддерживает устранение сегмента строки min/max (SQL Server 2022 (16.x) и более поздних версий), индекс columnstore не выигрывает от этой функции, пока он не будет перестроен с использованием ALTER INDEX REBUILD или CREATE INDEX WITH (DROP_EXISTING = ON).

Устранение сегментов не применяется к типам данных LOB, таким как типы данных с длиной (макс.).

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

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

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

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

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