Индексы типа Columnstore — руководство по проектированию

Статья
2025-04-09

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

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

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

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

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

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

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

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

Выберите наилучший колонночный индекс, соответствующий вашим потребностям

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

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

Опция хранения в столбцах	Рекомендации о том, когда использовать	Сжатие
Кластеризованный столбцовый индекс	Используйте для: 1) традиционной рабочей нагрузки хранилища данных со схемой типа "звезда" или "снежинка"; 2) рабочих нагрузок Интернета вещей, вставляющих большие объемы данных с минимальным числом операций обновления и удаления.	Среднее 10-кратное значение
Упорядоченный индекс столбчатого хранилища	Используется, когда кластеризованный индекс columnstore запрашивается через один упорядоченный столбец предиката или набор столбцов. Это руководство аналогично выбору ключевых столбцов кластеризованного индекса rowstore, хотя сжатые базовые группы строк ведут себя по-разному. Дополнительные сведения см. в разделе CREATE COLUMNSTORE INDEX и Настройка производительности с использованием упорядоченных индексов Columnstore.	Среднее 10-кратное значение
Некластеризованные индексы B-дерева на кластеризованном колоночном индексе	Используется для: 1. Применение ограничений первичного ключа и внешнего ключа для кластеризованного индекса columnstore. 2. Ускорение запросов, которые ищут определенные значения или небольшие диапазоны значений. 3. Ускорение обновлений и удаление определенных строк.	Среднее увеличение в 10 раз, плюс некоторое дополнительное место для хранения NCI.
Некластеризованный индекс колоннохранилища на дисковой куче или индексе B-дерева	Используйте для: 1. Рабочая нагрузка OLTP с некоторыми аналитическими запросами. Вы можете удалить B-tree индексы, созданные для аналитики, и заменить их одним некластеризованным индексом Columnstore. 2. Множества традиционных рабочих нагрузок OLTP, выполняющих операции извлечения, преобразования и загрузки (ETL) для перемещения данных в отдельное хранилище данных. Чтобы не выполнять ETL-процессы и не использовать отдельное хранилище данных, создайте некластеризованный columnstore-индекс в некоторых таблицах OLTP.	NCCI — это дополнительный индекс, требующий в среднем на 10 % больше памяти.
Индекс Columnstore в таблице в оперативной памяти	Те же рекомендации, что и для некластеризованного индекса columnstore в таблице на диске, за исключением того, что базовая таблица представляет собой таблицу в памяти.	Индекс columnstore является дополнительным индексом.

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

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

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

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

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

Для таблицы требуются типы данных varchar(max), nvarchar(max)или varbinary(max ). Или создайте индекс columnstore таким образом, чтобы он не включал эти столбцы (применяется к SQL Server 2016 (13.x) и предыдущим версиям.
Данные таблицы не постоянные. Используйте кучу или временную таблицу для быстрого сохранения и удаления данных.
В таблице содержится меньше миллиона строк на раздел.
Обновления и удаления составляют более 10 % операций в таблице. Большое количество операций обновления и удаления вызывает фрагментацию. Фрагментация влияет на скорость сжатия и производительность запросов, пока не будет выполнена операция под названием реорганизация, которая помещает все данные в колоночное хранилище и устраняет фрагментацию. Дополнительные сведения см. в статье Minimizing index fragmentation in columnstore index (Минимизация фрагментации индекса в индексах columnstore).

Дополнительные подробности см. в разделе колоночные индексы в хранилище данных.

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

Сведения о доступности упорядоченного columnstore-индекса см. в Индексах Columnstore: Обзор.

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

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

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

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

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

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

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

Рассмотрите возможность использования индекса B-дерева для индекса колоночного хранилища в следующих случаях:

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

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

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

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

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

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

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

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

Дополнительные сведения см. в статье "Начало работы с 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?).

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

Индекс колонночного хранилища предлагает два варианта сжатия данных: сжатие колонночного хранилища и архивное сжатие. При создании индекса можно выбрать параметр сжатия или изменить его позже с помощью команды ALTER INDEX ... REBUILD.

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

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

Используйте архивацию для наилучшего сжатия данных

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

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

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

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

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

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

Сохранение порядка сортировки индекса B-дерева

Поскольку строки в индексе B-дерева уже хранятся в отсортированном порядке, сохранение этого порядка при сжатии строк в 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 см. в разделе "Связанные задачи".