Новые возможности индексов columnstore

  • Статья

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

Узнайте, какие функции columnstore доступны для каждой версии SQL Server, а также последние выпуски База данных SQL, Azure Synapse Analytics и системы платформы аналитики (PDW).

Сводка функций по выпускам продукта

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

Функция индекса columnstore SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x)1 SQL Server 2017 (14.x) SQL Server 2019 (15.x) SQL Server 2022 (16.x) База данных SQL 1 Выделенный пул SQL для Azure Synapse Analytics
Выполнение пакетного режима для многопоточных запросов2 yes yes yes yes yes yes yes yes
Пакетный режим выполнения для однопоточных запросов yes yes yes yes yes yes
Параметр сжатия архивации yes yes yes yes yes yes yes
Изоляция моментальных снимков и изоляция моментальных снимков read committed. yes yes yes yes yes yes
Указание индекса columnstore при создании таблицы yes yes yes yes yes yes
AlwaysOn поддерживает индексы columnstore yes yes yes yes yes yes yes yes
Вторичная реплика для чтения AlwaysOn поддерживает некластеризованные индексы columnstore только для чтения yes yes yes yes yes yes yes yes
Вторичная реплика для чтения AlwaysOn поддерживает обновляемые индексы columnstore yes yes yes yes
Некластеризованный индекс columnstore только для чтения в куче или сбалансированном дереве yes yes Да 3 Да 3 Да 3 Да 3 Да 3 Да 3
Обновляемый некластеризованный индекс columnstore в куче или сбалансированном дереве yes yes yes yes yes yes
Разрешены дополнительные индексы сбалансированного дерева в куче или сбалансированном дереве с некластеризованным индексом columnstore yes yes yes yes yes yes yes yes
Обновляемый кластеризованный индекс columnstore yes yes yes yes yes yes yes
Индекс сбалансированного дерева в кластеризованном индексе columnstore yes yes yes yes yes yes
Индекс columnstore в таблице, оптимизированной для памяти yes yes yes yes yes yes
Определение некластеризованного индекса columnstore поддерживает использование отфильтрованных условий yes yes yes yes yes yes
Параметр задержки сжатия для индексов columnstore в инструкциях CREATE TABLE и ALTER TABLE yes yes yes yes yes yes
Поддержка типа nvarchar(max) yes yes yes yes no 4
Индекс columnstore может содержать нематериализованный вычисляемый столбец yes yes yes
Поддержка фонового слияния для переноса кортежей yes yes yes yes
Упорядоченные кластеризованные индексы columnstore yes yes yes
Упорядоченные некластикционные индексы columnstore yes

1 Для База данных SQL индексы columnstore доступны в База данных SQL Azure уровнях DTU Premium, уровнях DTU Standard — S3 и более поздних, а также во всех уровнях виртуальных ядер. Для SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) и более поздних версий индексы columnstore доступны во всех выпусках. Для SQL Server 2016 (13.x) (до sp1) и более ранних версий индексы columnstore доступны только в выпуск Enterprise.

2 Степень параллелизма (DOP) для операций в пакетном режиме ограничена 2 для SQL Server выпуск Standard и 1 для веб-выпусков SQL Server и Express Edition. Это ограничение относится к индексам columnstore, созданным на основе дисковых таблиц и оптимизированных для памяти таблиц.

3 . Чтобы создать некластеризованный индекс columnstore только для чтения, сохраните индекс в файловой группе только для чтения.

4 Не поддерживается в выделенных пулах SQL, но поддерживается в бессерверном пуле SQL.

SQL Server 2022 (16.x)

В SQL Server 2022 (16.x) добавлены эти функции.

  • Упорядоченные кластеризованные индексы columnstore повышают производительность запросов на основе упорядоченных предикатов столбцов. Упорядоченные индексы columnstore могут повысить производительность, пропуская сегменты данных в целом. Это может значительно сократить объем операций ввода-вывода, необходимых для выполнения запросов к данным columnstore. Дополнительные сведения см. в разделе об устранении сегментов. Упорядоченные индексы columnstore кластера доступны в SQL Server 2022 (16.x). Дополнительные сведения см. в статье CREATE COLUMNSTORE INDEX and Performance tuning with упорядоченные кластеризованные индексы columnstore.

  • Предикат pushdown с кластеризованной группой строк columnstore для устранения строк использует значения границ для оптимизации поиска строк. Все индексы columnstore получают преимущество от расширенного исключения сегментов по типу данных. Начиная с SQL Server 2022 (16.x), эти возможности устранения сегментов расширяются на типы данных string, binary и guid, а также тип данных datetimeoffset для масштабирования больше двух. Ранее исключение сегмента columnstore применялось только к числовым, датовым и временным типам данных, а также типу данных datetimeoffset с масштабированием меньше или равно двум. После обновления до версии SQL Server, поддерживающей устранение сегмента строки min/max (SQL Server 2022 (16.x) и более поздних версий, индекс columnstore не будет использовать эту функцию, пока она не будет перестроена с помощью ПЕРЕСТРОЕНИЯ или DROP/CREATE.

  • Дополнительные сведения о добавленных функциях см. в статье "Новые возможности SQL Server 2022".

SQL Server 2019 (15.x)

SQL Server 2019 (15.x) добавляет следующие новые возможности:

Функциональный

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

SQL Server 2017 (14.x)

SQL Server 2017 (14.x) добавляет эти новые функции.

Функциональный

  • SQL Server 2017 (14.x) поддерживает неперсистированные вычисляемые столбцы в кластеризованных индексах columnstore. Не поддерживает материализованные вычисляемые столбцы в кластеризованных индексах columnstore. Невозможно создать некластеризованный индекс columnstore в вычисляемом столбце.

SQL Server 2016 (13.x)

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

Функциональный

  • Таблица rowstore может включать один обновляемый некластеризованный индекс columnstore. Ранее некластеризованный индекс columnstore был доступен только для чтения.

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

  • Таблица в памяти может включать один индекс columnstore. Ее можно создать при создании или добавлении таблицы позже с помощью ALTER TABLE (Transact-SQL). Ранее создание индекса columnstore допускалось только в таблицах на дисках.

  • Кластеризованный индекс columnstore может включать один или несколько некластеризованных индексов rowstore. Ранее индекс columnstore не поддерживал некластеризованные индексы. SQL Server автоматически обслуживает некластеризованные индексы для операций DML.

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

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

Производительность для уровня совместимости базы данных 120 или 130

  • Индексы columnstore поддерживают уровень изоляции моментальных снимков read committed (RCSI) и изоляцию моментальных снимков (SI). Это позволяет выполнять запросы аналитики, согласованные с транзакциями, без блокировки.

  • Индекс columnstore поддерживает дефрагментацию индексов путем удаления удаленных строк без необходимости явного перестроения индекса. Инструкция ALTER INDEX ... REORGANIZE удаляет удаленные строки (на основе внутренней политики) из индекса columnstore в оперативном режиме.

  • Вторичная реплика для чтения AlwaysOn может получать доступ к индексам columnstore. За счет переноса запросов аналитики во вторичную реплику AlwaysOn можно повысить производительность оперативной аналитики.

  • Передача агрегата (Aggregate Pushdown) вычисляет агрегатные функции MIN, MAX, SUM, COUNT и AVG во время сканирования таблицы, если тип данных использует не более 8 байт и не относится к типу string. Передача агрегата поддерживается с предложением GROUP BY и без него для кластеризованных и некластеризованных индексов columnstore. В SQL Server это улучшение зарезервировано для выпуска Enterprise.

  • Передача предиката для строк ускоряет выполнение запросов, которые сравнивают строки типа VARCHAR/CHAR или NVARCHAR/NCHAR. Это относится к общим операторам сравнения, включая такие операторы, как LIKE, которые используют фильтры битовой карты. Это работает со всеми поддерживаемыми параметрами сортировки. В SQL Server это улучшение зарезервировано для выпуска Enterprise.

  • Улучшения для операций пакетного режима за счет использования аппаратных возможностей на основе векторов. Ядро СУБД обнаруживает уровень поддержки ЦП для расширений AVX 2 (расширенные векторные расширения) и SSE 4 (расширения ПОТОКОВой передачи SIMD 4) и использует их при поддержке. В SQL Server это улучшение зарезервировано для выпуска Enterprise.

Производительность для уровня совместимости базы данных 130

  • Поддержка нового пакетного режима для запросов с помощью любой из следующих операций:

    • SORT
    • агрегаты с несколькими четко различимыми функциями некоторые примеры: COUNT/COUNT, AVG/SUM, CHECKSUM_AGG, STDEV/STDEVP;
    • агрегатные оконные функции: COUNT, COUNT_BIG, SUM, AVG, MIN, MAX и CLR;
    • определяемые пользователем оконные агрегаты: CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP и GROUPING;
    • аналитические агрегатные оконные функции: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST и PERCENT_RANK.

  • Однопоточные запросы, выполняемые с MAXDOP 1 или последовательным планом запроса, выполняются в пакетном режиме. Ранее только многопоточных запросов выполнялись с пакетным выполнением.

  • Запросы к таблицам, оптимизированным для памяти, могут иметь параллельные планы в режиме взаимодействия SQL при доступе к данным как в индексе rowstore, так и в индексе columnstore.

Возможность поддержки

Следующие системные представления являются новыми для columnstore:

sys.column_store_row_groups (Transact-SQL)

sys.dm_column_store_object_pool (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_db_index_operational_stats (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

sys.internal_partitions (Transact-SQL)

Следующие представления DMV на основе OLTP в памяти содержат обновления для columnstore:

sys.dm_db_xtp_hash_index_stats (Transact-SQL)

sys.dm_db_xtp_index_stats (Transact-SQL)

sys.dm_db_xtp_memory_consumers (Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

sys.dm_db_xtp_object_stats (Transact-SQL)

sys.dm_db_xtp_table_memory_stats (Transact-SQL)

Ограничения

  • Для таблиц в памяти индекс columnstore должен включать все столбцы; индекс columnstore не может включать отфильтрованное условие.
  • Для таблиц в памяти запросы к индексу columnstore выполняются только в режиме взаимодействия, а не в собственном режиме в памяти. Поддерживается параллельное выполнение.

Известные проблемы

Область применения: SQL Server, База данных SQL Azure, Управляемый экземпляр SQL Azure, выделенный пул SQL Azure Synapse Analytics

  • В настоящее время бизнес-столбцы (varbinary(max), varchar(max) и nvarchar(max)) в сжатых сегментах columnstore не влияют на DBCC SHRINKDATABASE и DBCC SHRINKFILE.

SQL Server 2014 (12.x)

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

  • Таблица может использовать кластеризованный индекс columnstore в качестве основного хранилища таблиц. В таблице не разрешены другие индексы, но кластеризованный индекс columnstore можно обновить, чтобы вы могли выполнять регулярные нагрузки и вносить изменения в отдельные строки.
  • Некластеризованный индекс columnstore продолжает иметь те же функции, что и в SQL Server 2012 (11.x), за исключением дополнительных операторов, которые теперь могут выполняться в пакетном режиме. Он по-прежнему не поддерживает обновление, помимо перестроения и переключения секций. Некластеризованный индекс columnstore поддерживается только в таблицах на диске, но не в таблицах в памяти.
  • Кластеризованный и некластеризованный индекс columnstore имеет параметр сжатия архивных данных, который дополнительно сжимает данные. Параметр архивации удобен для сокращения объема данных в памяти и на диске, однако он снижает производительность запросов. Его можно рекомендовать для редко используемых данных.
  • Кластеризованный и некластеризованный индексы columnstore функционируют весьма сходным образом; они используют одинаковый формат хранения по столбцам, подсистему обработки запросов и набор динамических административных представлений. Различие заключается в типе индекса (основной и дополнительный), кроме того, некластеризованный индекс columnstore доступен только для чтения.
  • Следующие операторы выполняются в пакетном режиме для многопоточных запросов: SCAN, FILTER, PROJECT, JOIN, GROUP BY и UNION ALL.

SQL Server 2012 (11.x)

SQL Server 2012 (11.x) представил некластеризованный индекс columnstore в качестве другого типа индекса для таблиц rowstore и пакетной обработки запросов к данным columnstore.

  • Таблица rowstore может включать один некластеризованный индекс columnstore.
  • Индекс columnstore доступен только для чтения. После создания индекса columnstore нельзя обновлять таблицу с помощью операций INSERT, DELETE и UPDATE. Для выполнения этих операций необходимо удалить индекс, обновить таблицу и перестроить индекс columnstore. Дополнительные данные в таблицу можно загрузить с помощью переключения секций. Преимущество переключения секций заключается в том, что можно загрузить данные без удаления и перестроения индекса columnstore.
  • Индекс columnstore всегда требует дополнительного хранилища, как правило, дополнительного 10 % по хранилищу строк, так как он хранит копию данных.
  • Пакетная обработка обеспечивает двукратное (и более) повышение производительности, но она доступна только для параллельного выполнения запросов.

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