Индексы Columnstore. Обзор

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

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

• Индексы сolumnstore для хранилищ данных
• Начало работы с Columnstore для получения операционной аналитики в реальном времени

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

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

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

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

columnstore

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

Rowstore

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), базы данных Azure SQL, Управляемый экземпляр SQL Azure и выделенных пулов SQL в Azure Synapse Analytics, кортежу перемещения помогает фоновая задача слияния, которая автоматически сжимает небольшие группы строк open delta, которые существовали в течение некоторого времени. определяется внутренним пороговым значением или объединяет сжатые группы строк, из которых удалено большое количество строк. Это со временем повышает качество индекса columnstore.

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

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

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

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

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

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

Примечание

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

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

Разностная группа строк — это кластеризованный индекс сбалансированного дерева, который используется только с индексами 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, которые существовали некоторое время в соответствии с внутренним пороговым значением, или объединяет группы строк с состоянием COMPRESSED, из которых было удалено большое количество строк. Это со временем повышает качество индекса 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?

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

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

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

• Дополнительные сценарии использования индексов columnstore см. в статье Выбор оптимального индекса columnstore в соответствии с вашими потребностями.

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

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

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

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

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

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

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

Метаданные

Все столбцы в индексе 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)

Дальнейшие действия

Новые возможности призагрузке данных индексов columnstore
Сводка функций индексов columnstore по версиям
Производительность запросов индексов columnstore
Начало работы с Columnstore для получения операционной аналитики в реальном времени
Индексы сolumnstore для хранилищ данных
Дефрагментация индексов columnstore
Руководство по архитектуре и разработке индексов SQL Server
Рекомендации по проектированию индексов columnstore
CREATE COLUMNSTORE INDEX (Transact-SQL)