Общие сведения об индексах columnstore

  • Статья

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

Индексы columnstore — это стандарт хранения и запрашивания больших объемов данных в таблицах фактов. При этом используется формат хранения данных в столбцах и выполняется соответствующая обработка запросов, что позволяет практически в 10 раз повысить производительность запросов к хранилищу данных по сравнению с традиционным хранилищем, в котором данные хранятся в строках. Также, можно добиться 10-кратного сжатия данных относительно несжатых данных. Начиная с версии SQL Server 2016 (13.x) с пакетом обновления 1 (SP1), индексы columnstore позволяют выполнять операционную аналитику — производительный анализ транзакционной рабочей нагрузки в реальном времени.

Дополнительные сведения о связанных сценариях:

Что такое индекс columnstore?

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

Основные термины и понятия

Следующие основные концепции и понятия связаны с индексами columnstore.

Columnstore

Columnstore — это данные, логически упорядоченные в виде таблицы, состоящей из строк и столбцов, и физически хранящиеся в формате столбцов.

в хранилище строк

Rowstore — это данные, логически упорядоченные в виде таблицы, состоящей из строк и столбцов, и физически хранящиеся в формате строк. Это стандартный способ хранения реляционных данных таблиц. В SQL Server rowstore ссылается на таблицу, в которой базовый формат хранилища данных — куча, кластеризованный индекс или оптимизированная для памяти таблица.

Примечание.

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

Rowgroup

Rowgroup — это группа строк, сжимаемых в формате columnstore одновременно. Rowgroup обычно содержит максимальное возможное число строк — 1 048 576 строк.

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

Группа строк, из которой были удалены все данные, переходит из состояния COMPRESSED в состояние TOMBSTONE, после чего она удаляется фоновым процессом, который называется задачей переноса кортежей. См. сведения о состояниях групп строк в статье sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Совет

Слишком большое количество небольших групп строк ухудшает качество индекса columnstore. До SQL Server 2017 (14.x) требуется реорганизовать операцию для объединения небольших сжатых групп строк, следуя внутренней политике порогового значения, которая определяет, как удалить удаленные строки и объединить сжатые группы строк.
Начиная с SQL Server 2019 (15.x), фоновая задача слияния также работает для слияния групп строк COMPRESSED, из которых было удалено большое количество строк.
После объединения небольших групп строк качество индекса улучшается.

Примечание.

Начиная с SQL Server 2019 (15.x), База данных SQL Azure, Управляемый экземпляр SQL Azure и выделенных пулов SQL в Azure Synapse Analytics, перемещение кортежей помогает фоновой задачей слияния, которая автоматически сжимает небольшие разностные группы строк OPEN, которые существуют в течение некоторого времени, как определено внутренним пороговым значением, или объединяет сжатые группы строк из где было удалено большое количество строк. Это со временем повышает качество индекса columnstore.

Сегмент столбца

Сегмент столбца — это столбец данных из rowgroup.

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

Логическая схема сегмента столбца. Каждый столбец имеет один сегмент столбца для каждой группы строк.

Кластеризованный индекс columnstore

Кластеризованный индекс columnstore — это физическое хранилище для всей таблицы.

Логическая схема кластеризованного индекса columnstore. Включает сжатые сегменты столбцов и строки в индексе, но не в columnstore.

Чтобы снизить фрагментацию сегментов столбцов и повысить производительность, индекс columnstore может временно сохранять некоторые данные в кластеризованный индекс, который называется deltastore, и использовать для удаленных строк сбалансированное дерево идентификаторов. Операции deltastore обрабатываются в фоновом режиме. Для получения правильных результатов запросов кластеризованные индексы columnstore объединяют результаты запроса от columnstore и deltastore.

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

Разностная группа строк

Разностная группа строк — это кластеризованный индекс сбалансированного дерева, который используется только с индексами columnstore. Она оптимизирует сжатие и повышает эффективность хранения строк, пока их количество не достигнет порогового значения (1 048 576 строк), а затем переносит строки в индекс columnstore.

Когда количество строк в разностной группе строк достигает максимального значения, состояние группы меняется с OPEN на CLOSED. Фоновый процесс, который называется задачей переноса кортежей, проверяет наличие закрытых групп строк. При обнаружении закрытой группы строк разностная группа строк сжимается и сохраняется в columnstore в виде группы строк с состоянием COMPRESSED.

Когда разностная группа строк была сжата, существующая разностная группа строк переходит в состояние TOMBSTONE, которое будет удалено позже перемещением кортежей, когда нет ссылки на него.

См. сведения о состояниях групп строк в статье sys.dm_db_column_store_row_group_physical_stats (Transact-SQL).

Примечание.

Начиная с SQL Server 2019 (15.x), перемещение кортежей помогает задачей фонового слияния, которая автоматически сжимает небольшие разностные группы строк OPEN, которые существовали в течение некоторого времени, как определено внутренним пороговым значением, или объединяет сжатые группы строк, из которых было удалено большое количество строк. Это со временем повышает качество индекса columnstore.

Deltastore

Индекс columnstore может содержать более одной разностной группы строк. Все разностные группы строк совокупно называются deltastore.

При крупной массовой загрузке большинство строк переходят непосредственно в columnstore без промежуточного помещения в deltastore. Некоторых строк в конце массовой загрузки может оказаться слишком мало для соответствия минимальному размеру rowgroup, составляющему 102 400 строк. В результате этого последние строки переходят в deltastore вместо columnstore. Для небольших массовых загрузок с менее 102 400 строк, все строки перемещаются напрямую в deltastore.

некластеризованный индекс columnstore

Некластеризованный индекс columnstore и кластеризованный индекс columnstore функционируют по одному принципу. Разница в том, что некластеризованный индекс вторичен и создается на основе таблицы индексов rowstore, а кластеризованный индекс columnstore является первичным хранилищем для всей таблицы.

Некластеризованный индекс содержит копию всех или части строк и столбцов в базовой таблице. Индекс определяется как один или несколько столбцов таблицы и включает дополнительное условие для фильтрации строк.

Некластеризованный индекс columnstore позволяет осуществлять операционную аналитику в реальном времени, когда рабочая нагрузка OLTP выполняется с использованием базового кластеризованного индекса, а аналитика при этом проводится параллельно на основе индекса columnstore. Дополнительные сведения см. в статье Начало работы с Columnstore для получения операционной аналитики в реальном времени.

выполнение в пакетном режиме.

Пакетный режим выполнения — это метод обработки запросов, при котором обрабатываются сразу несколько строк. Выполнение в пакетном режиме тесно интегрировано и оптимизировано для взаимодействия с форматом хранения columnstore. Пакетный режим выполнения иногда называется выполнением на основе векторов или векторизированным выполнением. В запросах к индексам columnstore используется режим пакетного выполнения, что обычно повышает производительность запросов в 2–4 раза. Дополнительные сведения см. в статье Руководство по архитектуре обработки запросов.

Для чего нужен индекс columnstore?

Индекс columnstore обеспечивает высокую (обычно десятикратную) степень сжатия данных, что позволяет существенно снизить затраты на хранение данных. Кроме того, он на порядок повышает эффективность аналитики по сравнению с индексом сбалансированного дерева. Индексы columnstore — это предпочтительный формат для хранения данных и выполнения аналитики. Начиная с SQL Server 2016 (13.x), можно использовать индексы columnstore для аналитики в режиме реального времени в рабочей нагрузке.

Почему индексы columnstore такие быстрые.

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

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

  • Пакетное выполнение повышает эффективность запросов (обычно в 2–4 раза) благодаря обработке сразу нескольких строк.

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

Когда следует использовать индекс columnstore?

Рекомендации по использованию

Как сделать выбор между индексами rowstore и columnstore?

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

Индексы columnstore обеспечивают значительное повышение производительности при выполнении аналитических запросов, которые сканируют большие объемы данных (в частности, большие таблицы). Используйте индексы columnstore с рабочими нагрузками по хранению и аналитике данных (в частности, с таблицами фактов), так как для них чаще требуется полное сканирование таблиц, а не поиск по таблицам.

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

Можно ли использовать индексы rowstore и columnstore в одной и той же таблице?

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

Начиная с SQL Server 2016 (13.x), вы можете иметь один или несколько некластеризованных индексов rowstore в индексе columnstore и эффективно выполнять поиск в базовом хранилище столбцов. Кроме того, появляется доступ к другим возможностям. Например, можно принудительно задать ограничение PRIMARY KEY, применив к таблице rowstore ограничение UNIQUE. Так как неуниковое значение не вставляется в таблицу rowstore, SQL Server не может вставить значение в columnstore.

Упорядоченные индексы columnstore

Благодаря включению эффективной ликвидации сегментов упорядоченные кластеризованные индексы columnstore (CCI) обеспечивают гораздо более быструю производительность, пропуская большие объемы упорядоченных данных, которые не соответствуют предикату запроса. Загрузка данных в упорядоченную таблицу CCI может занять больше времени, чем в неупорядоченную таблицу CCI, из-за операции сортировки данных, однако запросы могут выполняться быстрее с упорядоченным CCI.

Доступность упорядоченного индекса columnstore

Впервые появившиеся в SQL Server 2022 (16.x), упорядоченные индексы columnstore доступны на следующих платформах.

Платформа Упорядоченные кластеризованные индексы columnstore Упорядоченные некластеризованные индексы columnstore
База данных SQL Azure Да Да
SQL Server 2022 (16.x) Да Нет
Управляемый экземпляр SQL Azure Да Да
Выделенный пул SQL в Azure Synapse Analytics Да Нет

Метаданные

Все столбцы в индексе columnstore хранятся в метаданных как включенные столбцы. Индекс columnstore не имеет ключевых столбцов.

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.partitions (Transact-SQL)

sys.internal_partitions (Transact-SQL)

sys.column_store_segments (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

sys.column_store_row_groups (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

sys.dm_column_store_object_pool (Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

sys.dm_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

Для всех реляционных таблиц, не заданных как кластеризованный индекс columnstore, в качестве базового формата данных используется индекс rowstore. CREATE TABLE создает таблицу rowstore, если не указан параметр WITH CLUSTERED COLUMNSTORE INDEX.

С помощью инструкции CREATE TABLE можно создать таблицу с индексом columnstore, указав параметр WITH CLUSTERED COLUMNSTORE INDEX. Чтобы конвертировать таблицу rowstore в columnstore, используйте инструкцию CREATE COLUMNSTORE INDEX.

Задача Справочные статьи Примечания.
Создание таблицы как кластеризованного индекса 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 для хранилищ данных Индексы сolumnstore для хранилищ данных Описывает использование индексов сбалансированного дерева в таблицах columnstore для создания высокопроизводительных запросов к хранилищу данных.
Использование индекса сбалансированного дерева для принудительного применения ограничения первичного ключа к индексу columnstore. Индексы сolumnstore для хранилищ данных Показано, как объединить индексы сбалансированного дерева и 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.
Загрузка данных в индекс columnstore. Индексы columnstore. Руководство по загрузке данных
Принудительное перемещение всех строк из deltastore в columnstore ALTER INDEX (Transact-SQL) ... REBUILD

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

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