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

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

  • Статья

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

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

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

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

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

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

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

Колонковое хранилище данных

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

Rowstore

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

Примечание.

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

Группа строк

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

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

Группа строк, из которой удалены все данные, переходит из состояния COMPRESSED в состояние TOMBSTONE, а затем удаляется фоновым процессом с названием tuple-mover. См. сведения о состояниях групп строк в статье 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 Database, Azure SQL Managed Instance и выделенных пулов SQL в системе Azure Synapse Analytics, перемещению кортежей способствует фоновая задача слияния, которая автоматически сжимает небольшие дельта-группы строк, которые существовали в течение некоторого времени, определенного внутренним порогом, или объединяет сжатые группы строк, из которых было удалено большое количество строк. Со временем это улучшает качество columnstore-индекса.

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

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

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

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

Кластеризованный индекс колонночного хранилища

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

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

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

Примечание.

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

Группа строк Дельта

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

Когда количество строк в «дельта-группе строк» достигает максимального значения, состояние группы меняется с «OPEN» на «CLOSED». Фоновый процесс под названием tuple-mover проверяет закрытые группы строк. При обнаружении завершенной группы строк, группа строк дельта сжимается и сохраняется в колоночном хранилище в сжатом состоянии.

Когда разностная группа строк сжимается, существующая разностная группа строк становится в состояние 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 функционируют по одному принципу. Разница в том, что некластеризованный индекс вторичен и создается на основе таблицы индексов rowstore, а кластеризованный индекс columnstore является первичным хранилищем для всей таблицы.

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

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

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

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

Почему следует использовать индекс columnstore?

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

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

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

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

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

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

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

Рекомендуемые варианты использования

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

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

Индексы columnstore обеспечивают значительное повышение производительности при выполнении аналитических запросов, которые сканируют большие объемы данных (в частности, большие таблицы). Используйте индексы 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 может занять больше времени, чем в неупорядоченный индекс из-за операции сортировки данных, однако с упорядоченными индексами columnstore запросы могут выполняться быстрее после этого.

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

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

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

AUTD применяется к Управляемому экземпляру SQL Azure, настроенной с помощью политики обновленияAlways-up-to-date.
2022 Применяется к Управляемому экземпляру Azure SQL, настроенному с политикой обновления SQL Server 2022.
1в базе данных SQL Fabric таблицы с кластеризованными индексами columnstore не зеркально отображены в Fabric OneLake.

Метаданные

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

Перестроить индекс columnstore

Методы обслуживания индекса: реорганизация и перестроение		 В большинстве случаев ALTER INDEX ... REORGANIZE предоставляет результаты, аналогичные ALTER INDEX ... REBUILD, но с более низким потреблением ресурсов. ALTER INDEX ... REORGANIZE всегда работает онлайн. Оба варианта дефрагментируют индекс columnstore и принудительно перемещают строки из deltastore в columnstore.

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

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