Руководство по проектированию индексов columnstore

  • Статья

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

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

Предварительные условия

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

Определение требований к данным

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

  • Каков размер таблицы?
  • Выполняют ли мои запросы в большинстве случаев анализ со сканированием больших диапазонов значений? Индексы columnstore позволяют обрабатывать большие диапазоны значений, а не выполнять поиск определенных значений.
  • Выполняется ли в моей рабочей нагрузке множество операций обновления и удаления? Индексы columnstore эффективно работают со стабильными данными. Запросы должны обновлять и удалять менее 10 % строк.
  • Имеются ли таблицы фактов и измерений для хранилища данных?
  • Требуется ли анализ транзакционной рабочей нагрузки? Если да, ознакомьтесь с руководством по проектированию columnstore для оперативной аналитики в режиме реального времени.

Индекс columnstore может не пригодиться. Таблицы Rowstore (или B-tree) с кучами или кластеризованными индексами лучше всего выполняют запросы, которые ищут данные, поиск определенного значения или запросы на небольшой диапазон значений. Используйте индексы rowstore с рабочими нагрузками по транзакциям, так как последние чаще требуют поиска по таблицам, а не сканирования таблиц большого диапазона.

Выбор подходящего индекса columnstore

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

Ниже приведена сводка сценариев использования и рекомендаций.

Сценарий использования columnstore Рекомендации по использованию Сжатие
Кластеризованный индекс columnstore Используйте для:

1) традиционной рабочей нагрузки хранилища данных со схемой типа "звезда" или "снежинка";

2) рабочих нагрузок Интернета вещей, вставляющих большие объемы данных с минимальным числом операций обновления и удаления.		 Среднее значение: 10-кратное сжатие
Упорядоченный кластеризованный индекс columnstore Применяется к Azure Synapse Analytics и SQL Server 2022 (16.x) и выше
Используется, когда кластеризованный индекс columnstore запрашивается через один упорядоченный столбец предиката или набор столбцов. Это руководство аналогично выбору ключевых столбцов для кластеризованного индекса rowstore, хотя сжатые базовые группы строк работают по-разному. Дополнительные сведения см. в статье CREATE COLUMNSTORE INDEX and Performance tuning with упорядоченный кластеризованный индекс columnstore.		 Среднее значение: 10-кратное сжатие
Некластеризованные индексы B-дерева в кластеризованном индексе columnstore Используется для:

1. Применение ограничений первичного ключа и внешнего ключа для кластеризованного индекса columnstore.

2. Ускорение запросов, которые ищут определенные значения или небольшие диапазоны значений.

3. Ускорение обновлений и удаление определенных строк.		 Среднее значение: 10-кратное сжатие. Кроме того, требуется дополнительное место хранения для NCI.
Некластеризованный индекс columnstore в куче на основе диска или в дереве B-дерева Используйте для:

1. Рабочей нагрузки OLTP с запросами аналитики. Вы можете удалить индексы сбалансированного дерева, созданные для анализа, и заменить их одним некластеризованным индексом columnstore.

2. Множества традиционных рабочих нагрузок OLTP, выполняющих операции извлечения, преобразования и загрузки (ETL) для перемещения данных в отдельное хранилище данных. Чтобы не выполнять операции извлечения, преобразования и загрузки, а также не использовать отдельное хранилище данных, создайте индекс columnstore в некоторых таблицах OLTP.		 NCCI — это дополнительный индекс, требующий в среднем на 10 % больше памяти.
Индекс Columnstore в таблице в памяти Те же рекомендации, что и для некластеризованного индекса columnstore в таблице на диске, за исключением того, что базовая таблица представляет собой таблицу в памяти. Индекс columnstore является дополнительным индексом.

Использование кластеризованного индекса columnstore для больших таблиц хранилища данных

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

Используйте кластеризованный индекс columnstore в таких ситуациях:

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

Не используйте кластеризованный индекс columnstore в следующих ситуациях:

  • Для таблицы требуются типы данных varchar(max), nvarchar(max)или varbinary(max ). Или создайте индекс columnstore таким образом, чтобы он не включал эти столбцы (применяется к SQL Server 2016 (13.x) и предыдущим версиям.

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

  • В таблице содержится меньше миллиона строк на секцию.

  • Обновления и удаления составляют более 10 % операций в таблице. Большое количество операций обновления и удаления вызывает фрагментацию. Фрагментация влияет на степень сжатия и производительность запроса. Операция реорганизации позволяет передать все данные в индекс columnstore и устраняет фрагментацию. Дополнительные сведения см. в статье Minimizing index fragmentation in columnstore index (Минимизация фрагментации индекса в индексах columnstore).

Дополнительные сведения см. в статье Columnstore indexes — data warehousing (Хранилище данных для индексов columnstore).

Использование упорядоченного кластеризованного индекса columnstore для больших таблиц хранилища данных

Область применения: Azure Synapse Analytics и начиная с SQL Server 2022 (16.x)

Рекомендуется использовать упорядоченный кластеризованный индекс columnstore в следующих сценариях:

  • Если данные относительно статически (без частой записи и удаления) и упорядоченный кластеризованный ключ индекса columnstore статический, упорядоченные кластеризованные индексы columnstore могут обеспечить значительные преимущества производительности по сравнению с неупорядоченными кластеризованными индексами columnstore или кластеризованными индексами rowstore для аналитических рабочих нагрузок.
  • Чем больше уникальных значений в первом столбце упорядоченного ключа кластеризованного индекса columnstore, тем лучше повышение производительности может быть для упорядоченных кластеризованных индексов columnstore. Это связано с улучшенной ликвидацией сегмента для строковых данных. Дополнительные сведения см. в разделе об устранении сегментов.
  • Выберите упорядоченный кластеризованный ключ индекса columnstore, который часто запрашивается и может воспользоваться устранением сегментов, особенно первым столбцом ключа. Повышение производительности из-за исключения сегментов в других столбцах таблицы будет менее предсказуемым.
  • Варианты использования, когда запрашиваются только последние аналитические данные, например последние 15 секунд, упорядоченные кластеризованные индексы columnstore могут обеспечить ликвидацию сегментов для старых данных. Первый столбец в ключе упорядоченных кластеризованных данных columnstore должен быть данными даты и времени, например вставленной или созданной даты и времени. Устранение сегмента будет более эффективным в упорядоченном кластеризованном индексе columnstore, чем в неупорядоченном кластеризованном индексе columnstore.
  • Рассмотрим упорядоченные кластеризованные индексы columnstore в таблицах, содержащих ключи с данными GUID, где теперь тип данных uniqueidentifier можно использовать для устранения сегментов.

Упорядоченный кластеризованный индекс columnstore может быть не так эффективным в следующих сценариях:

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

Добавление некластеризованных индексов сбалансированного дерева для эффективного поиска в таблице

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

Вторичный индекс сбалансированного дерева позволяет выполнять более эффективный поиск определенных строк без сканирования всех строк. Кроме того, появляется доступ к другим возможностям. Например, можно принудительно задать ограничение первичного или внешнего ключа, применив уникальное ограничение к индексу сбалансированного дерева. Так как не уникальное значение не вставляется в индекс дерева B, SQL Server не может вставить значение в columnstore.

Используйте индекс сбалансированного дерева в индексе columnstore в следующих случаях:

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

Использование некластеризованного индекса columnstore для операционной аналитики в реальном времени

Начиная с SQL Server 2016 (13.x), можно использовать некластеризованный индекс columnstore в таблице на основе дисков rowstore или в памяти таблице OLTP. Таким образом, вы можете выполнять анализ в таблице транзакций в режиме реального времени. Хотя транзакции выполняются в базовой таблице, вы можете выполнять анализ в индексе columnstore. Изменения доступны для индекса rowstore и columnstore в режиме реального времени, так как обоими индексами управляет одна таблица.

Так как индекс columnstore обеспечивает уровень сжатия в 10 раз выше, чем индекс rowstore, ему необходимо небольшое дополнительное пространство. Например, если в сжатую таблицу rowstore передается 20 ГБ данных, индексу columnstore может потребоваться 2 ГБ места дополнительно. Объем дополнительного пространства также зависит от числа столбцов в некластеризованном индексе columnstore.

Используйте некластеризованный индекс columnstore в таких ситуациях:

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

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

SQL Server 2016 (13.x) предлагает несколько стратегий для выполнения этого сценария. Они очень просты, так как вы можете включить некластеризованный индекс columnstore, не изменяя приложение OLTP.

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

Дополнительные сведения см. в статье Get started with columnstore indexes for real-time operational analytics (Начало работы с индексами columnstore для получения операционной аналитики в реальном времени).

Дополнительные сведения о выборе подходящего индекса columnstore см. в записи блога Сунила Агарвала (Sunil Agarwal) Which columnstore index is right for my workload? (Какие индексы columnstore подходят для моей рабочей нагрузки?).

Использование секционированных таблиц для управления данными и повышения производительности запросов

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

Упрощение управления данными с помощью секций

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

Например, при использовании секций в таблицах rowstore и columnstore можно получить следующие преимущества:

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

Кроме того, при использовании секционирования индекса columnstore можно добиться следующего:

  • Экономия 30 % затрат на хранение. Вы можете выполнить сжатие старых секций с помощью параметров сжатия COLUMNSTORE_ARCHIVE. Данные будут медленнее для производительности запросов, что приемлемо, если секция запрашивается редко.

Повышение производительности запросов с помощью секций

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

Использование нескольких секций для индекса columnstore

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

Пример:

  • Загрузите 1 000 000 строк в одну секцию или в несекционированную таблицу. Вы получите одну сжатую группу строк с 1 000 000 строк. Это удобно для высокого уровня сжатия данных и высокой производительности запросов.
  • Загрузите 1 000 000 строк с равномерным распределением по 10 секциям. Каждая секция получает по 100 000 строк. Это значение ниже минимального порога для сжатия columnstore. В результате индекс columnstore может иметь 10 разностных групп строк с 100 000 строк в каждой. Вы можете принудительно передать разностные группы строк в индекс columnstore. Тем не менее, если это единственные строки в индексе columnstore, сжатые группы строк будут слишком малы для максимального сжатия и максимальной производительности запросов.

Дополнительные сведения о секционировании см. в записи блога Сунила Агарвала (Sunil Agarwal) Should I partition my columnstore index? (Следует ли секционировать индекс columnstore?).

Выбор подходящего метода сжатия

Индекс columnstore предлагает два варианта сжатия данных: сжатие columnstore и сжатие архива. При создании индекса можно выбрать параметр сжатия или изменить его позже с помощью ALTER INDEX ... ПЕРЕСТРОЙТЕ.

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

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

Высокий уровень сжатия данных при сжатии архива

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

Оптимизация при преобразовании таблицы rowstore в индекс columnstore

Если данные уже находятся в таблице rowstore, можно использовать инструкцию CREATE COLUMNSTORE INDEX для преобразования таблицы в кластеризованный индекс columnstore. Вы можете оптимизировать производительность запросов после преобразования таблицы, как описано далее.

Повышение качества группы строк с помощью MAXDOP

Вы можете настроить максимальное число процессоров для преобразования кучи или кластеризованного индекса сбалансированного дерева в индекс columnstore. Чтобы настроить процессоры, используйте параметр максимальной степени параллелизма (MAXDOP).

Если у вас есть большие объемы данных, MAXDOP 1 , скорее всего, будет слишком медленным. Увеличение MAXDOP для 4 нормальной работы. Если это приведет к нескольким группам строк, которые не имеют оптимального количества строк, можно запустить ALTER INDEX REORGANIZE , чтобы объединить их вместе в фоновом режиме.

Сохранение порядка сортировки индекса сбалансированного дерева

Так как строки в индексе сбалансированного дерева уже хранятся в порядке сортировки, сохранение этого порядка при сжатии строк в индекс columnstore может повысить производительность запросов.

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

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

  • Используйте инструкцию CREATE COLUMNSTORE INDEX с предложением DROP_EXISTING. При этом также сохраняется имя индекса. Если у вас есть скрипты, использующие имя индекса rowstore, их не потребуется обновлять.

    В этом примере кластеризованный индекс rowstore в таблице MyFactTable преобразуется в кластеризованный индекс columnstore. Имя индекса, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, не изменяется.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
ON MyFactTable
WITH (DROP_EXISTING = ON);

Общие сведения об устранении сегментов

Каждая rowgroup содержит один сегмент столбца для каждого столбца в таблице. Каждый сегмент столбца сжимается одновременно и сохраняется на физическом носителе.

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

Ниже перечислены задачи для создания и обслуживания индексов columnstore.

Задача Справочные статьи Примечания
Создание таблицы как кластеризованного индекса columnstore CREATE TABLE (Transact-SQL) Начиная с SQL Server 2016 (13.x), можно создать таблицу в виде кластеризованного индекса columnstore. Для этого не нужно создавать таблицу rowstore, а затем конвертировать ее в columnstore.
Создание таблицы в памяти с индексом columnstore. CREATE TABLE (Transact-SQL) Начиная с SQL Server 2016 (13.x), можно создать оптимизированную для памяти таблицу с индексом columnstore. Индекс columnstore можно добавить и после создания таблицы, используя синтаксис ALTER TABLE ADD INDEX.
Преобразование таблицы rowstore в таблицу columnstore CREATE COLUMNSTORE INDEX (Transact-SQL) Преобразуйте существующую кучу или дерево B в columnstore. В примерах показано, как обрабатывать существующие индексы, а также имя индекса, которое нужно использовать в процессе преобразования.
Преобразование таблицы columnstore в rowstore CREATE CLUSTERED INDEX (Transact-SQL) или Преобразовать таблицу columnstore обратно в кучу rowstore Обычно это преобразование не требуется, но бывают ситуации, когда оно необходимо. В примерах показано, как преобразовать columnstore в кучу или кластеризованный индекс.
Создание индекса columnstore в таблице rowstore CREATE COLUMNSTORE INDEX (Transact-SQL) Таблица rowstore может включать один индекс columnstore. Начиная с SQL Server 2016 (13.x), индекс columnstore может иметь отфильтрованное условие. В примерах показан основной синтаксис.
Создание высокопроизводительных индексов для оперативной аналитики Начало работы с Columnstore для получения операционной аналитики в реальном времени Описывает процесс создания дополнительных индексов columnstore и сбалансированного дерева, которые позволят использовать индексы сбалансированного дерева в запросах OLTP и индексы columnstore в запросах аналитики.
Создание высокопроизводительных индексов сolumnstore для хранилищ данных Columnstore indexes — data Warehousing (Хранилище данных для индексов columnstore) Описывает использование индексов сбалансированного дерева в таблицах columnstore для создания высокопроизводительных запросов к хранилищу данных.
Использование индекса сбалансированного дерева для принудительного применения ограничения первичного ключа к индексу columnstore. Columnstore indexes — data Warehousing (Хранилище данных для индексов columnstore) Показано, как объединить индексы сбалансированного дерева и columnstore для принудительного применения ограничений первичного ключа к индексу columnstore.
Удаление индекса columnstore. DROP INDEX (Transact-SQL) Для удаления индекса columnstore используется стандартный синтаксис DROP INDEX, применяемый индексами сбалансированного дерева. При удалении кластеризованного индекса columnstore таблица columnstore преобразуется в кучу.
Удаление строки из индекса columnstore DELETE (Transact-SQL) Удалите строку с помощью DELETE (Transact-SQL ).

Строка columnstore : SQL Server помечает строку как логическую удаленную, но не освобождает физическое хранилище для строки, пока индекс не будет перестроен.

строка deltastore : SQL Server логически и физически удаляет строку.
Обновление строки в индексе columnstore UPDATE (Transact-SQL) Чтобы обновить строку, используйте UPDATE (Transact-SQL).

Строка columnstore : SQL Server помечает строку как логическую удаленную, а затем вставляет обновленную строку в deltastore.

строка deltastore : SQL Server обновляет строку в deltastore.
Принудительное перемещение всех строк из deltastore в columnstore ALTER INDEX (Transact-SQL) ... ПЕРЕСТРОИТЬ

Реорганизация и перестроение индексов		 Инструкция ALTER INDEX с параметром REBUILD перемещает все строки в columnstore.
Дефрагментация индекса columnstore ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE дефрагментирует индексы columnstore online.
Слияние таблиц с индексами columnstore. MERGE (Transact-SQL)

Далее

Чтобы создать пустой индекс columnstore для:

Дополнительные сведения о том, как преобразовать существующую кучу rowstore или индекс B-дерева в кластеризованный индекс columnstore или создать некластеризованный индекс columnstore, см. в статье CREATE COLUMNSTORE INDEX (Transact-SQL).