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

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

  • Статья

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

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

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

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

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

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

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

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

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

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

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

Опция хранения в столбцах Рекомендации о том, когда использовать Сжатие
Кластеризованный столбцовый индекс Используйте для:

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

2) рабочих нагрузок Интернета вещей, вставляющих большие объемы данных с минимальным числом операций обновления и удаления.		 Среднее 10-кратное значение
Упорядоченный индекс столбчатого хранилища Используется, когда кластеризованный индекс columnstore запрашивается через один упорядоченный столбец предиката или набор столбцов. Это руководство аналогично выбору ключевых столбцов кластеризованного индекса rowstore, хотя сжатые базовые группы строк ведут себя по-разному. Дополнительные сведения см. в разделе CREATE COLUMNSTORE INDEX и Настройка производительности с использованием упорядоченных индексов Columnstore. Среднее значение: 10-кратное увеличение
Некластеризованные индексы B-дерева в кластеризованном индексе columnstore Используется для:

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, чем в неупорядоченном индексе 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.

Задача Справочные статьи Примечания.
Создайте таблицу как columnstore. CREATE TABLE (Transact-SQL) Начиная с SQL Server 2016 (13.x), можно создать таблицу в виде кластеризованного индекса columnstore. Для этого не нужно создавать таблицу rowstore, а затем конвертировать ее в 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 в 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 и B-дерева, которые позволят использовать индексы B-дерева в запросах OLTP, а индексы columnstore — в аналитических запросах.
Создавайте эффективные колонночные индексы для хранилищ данных. Columnstore-индексы в системах хранения данных Описывает использование индексов B-дерева в колоночных таблицах для создания высокоэффективных запросов к хранилищу данных.
Использование индекса сбалансированного дерева для принудительного применения ограничения первичного ключа к индексу columnstore. Индексы столбчатого типа в хранилище данных Показано, как объединить индексы B-дерева и индексы колоночного хранилища для обеспечения ограничений первичного ключа на индекс колоночного хранилища.
Удалите индекс columnstore. DROP INDEX (Transact-SQL) Для удаления колоночного индекса (columnstore) используется стандартный синтаксис DROP INDEX, применяемый для индексов B-дерева. Удаление кластеризованного индекса 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 ALTER INDEX (Transact-SQL) Инструкция ALTER INDEX ... REORGANIZE дефрагментирует индексы columnstore в оперативном режиме.
Объединение таблиц с колонковыми индексами. MERGE (Transact-SQL)

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

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

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