Руководство по проектированию индексов columnstoreColumnstore indexes - Design guidance

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Общие рекомендации по проектированию индексов columnstore.High-level recommendations for designing columnstore indexes. Немногие принципы проектирования позволяют добиться такого же высокого уровня сжатия данных и производительности запросов, как индексы columnstore.A small number of good design decisions helps you achieve the high data compression and query performance that columnstore indexes are designed to provide.

предварительные требованияPrerequisites

В этой статье предполагается, что вы уже знакомы с архитектурой и терминологией columnstore.This article assumes you are familiar with columnstore architecture and terminology. Дополнительные сведения см. в статьях, посвященных обзору индексов columnstore и их архитектуре.For more information, see Columnstore indexes - Overview and Columnstore Index Architecture.

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

Перед проектированием индекса columnstore постарайтесь как можно лучше разобраться в требованиях к данным.Before designing a columnstore index, understand as much as possible about your data requirements. Например, продумайте ответы на следующие вопросы:For example, think through the answers to these questions:

  • Каков размер таблицы?How large is my table?
  • Выполняют ли мои запросы в большинстве случаев анализ со сканированием больших диапазонов значений?Do my queries mostly perform analytics that scan large ranges of values? Индексы columnstore позволяют обрабатывать большие диапазоны значений, а не выполнять поиск определенных значений.Columnstore indexes are designed to work well for large range scans rather than looking up specific values.
  • Выполняется ли в моей рабочей нагрузке множество операций обновления и удаления?Does my workload perform lots of updates and deletes? Индексы columnstore эффективно работают со стабильными данными.Columnstore indexes work well when the data is stable. Запросы должны обновлять и удалять менее 10 % строк.Queries should be updating and deleting less than 10% of the rows.
  • Имеются ли таблицы фактов и измерений для хранилища данных?Do I have fact and dimension tables for a data warehouse?
  • Требуется ли анализ транзакционной рабочей нагрузки?Do I need to perform analytics on a transactional workload? Если такой анализ требуется, см. руководство по проектированию columnstore для операционной аналитики в режиме реального времени.If this is the case, see the columnstore design guidance for real-time operational analytics.

Индекс columnstore может не пригодиться.You might not need a columnstore index. Таблицы rowstore с кучами или кластеризованными индексами лучше всего работают с запросами на поиск данных или определенного значения, а также с запросами небольшого диапазона данных.Rowstore tables with heaps or clustered indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Используйте индексы rowstore с рабочими нагрузками по транзакциям, так как последние чаще требуют поиска по таблицам, а не сканирования таблиц большого диапазона.Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of large range table scans.

Выбор подходящего индекса columnstoreChoose the best columnstore index for your needs

Индекс columnstore бывает кластеризованным или некластеризованным.A columnstore index is either clustered or nonclustered. Кластеризованный индекс columnstore может включать один или несколько некластеризованных индексов сбалансированного дерева.A clustered columnstore index can have one or more nonclustered B-tree indexes. Индексы columnstore просты в использовании.Columnstore indexes are easy to try. При создании таблицы в качестве индекса columnstore ее можно легко преобразовать обратно в таблицу rowstore, удалив индекс columnstore.If you create a table as a columnstore index, you can easily convert the table back to a rowstore table by dropping the columnstore index.

Ниже приведена сводка сценариев использования и рекомендаций.Here is a summary of the options and recommendations.

Сценарий использования columnstoreColumnstore option Рекомендации по использованиюRecommendations for when to use СжатиеCompression
Кластеризованный индекс columnstoreClustered columnstore index Используется для:Use for:
1) традиционной рабочей нагрузки хранилища данных со схемой типа "звезда" или "снежинка";1) Traditional data warehouse workload with a star or snowflake schema
2) рабочих нагрузок Интернета вещей, вставляющих большие объемы данных с минимальным числом операций обновления и удаления.2) Internet of Things (IOT) workloads that insert large volumes of data with minimal updates and deletes.
Среднее значение: 10-кратное сжатиеAverage of 10x
Некластеризованный индекс сбалансированного дерева в кластеризованном индексе columnstoreNonclustered B-tree indexes on a clustered columnstore index Используется для:Use to:
1. применения ограничений первичного и внешнего ключа в кластеризованном индексе columnstore;1. Enforce primary key and foreign key constraints on a clustered columnstore index.
2. ускорения запросов на поиск конкретных значений или небольших диапазонов значений;2. Speed up queries that search for specific values or small ranges of values.
3. ускорения обновления и удаления определенных строк.3. Speed up updates and deletes of specific rows.
Среднее значение: 10-кратное сжатие. Кроме того, требуется дополнительное место хранения для NCI.10x on average plus some additional storage for the NCIs.
Некластеризованный индекс columnstore в куче на основе диска или в индексе сбалансированного дереваNonclustered columnstore index on a disk-based heap or B-tree index Используется для:Use for:
1. Рабочей нагрузки OLTP с запросами аналитики.1) An OLTP workload that has some analytics queries. Вы можете удалить индексы сбалансированного дерева, созданные для анализа, и заменить их одним некластеризованным индексом columnstore.You can drop B-tree indexes created for analytics and replace them with one nonclustered columnstore index.
2. Множества традиционных рабочих нагрузок OLTP, выполняющих операции извлечения, преобразования и загрузки (ETL) для перемещения данных в отдельное хранилище данных.2) Many traditional OLTP workloads that perform Extract Transform and Load (ETL) operations to move data to a separate data warehouse. Чтобы не выполнять операции извлечения, преобразования и загрузки, а также не использовать отдельное хранилище данных, создайте индекс columnstore в некоторых таблицах OLTP.You can eliminate ETL and a separate data warehouse by creating a nonclustered columnstore index on some of the OLTP tables.
NCCI — это дополнительный индекс, требующий в среднем на 10 % больше памяти.NCCI is an additional index that requires 10% more storage on average.
Индекс columnstore в таблице в памятиColumnstore index on an in-memory table Те же рекомендации, что и для некластеризованного индекса columnstore в таблице на диске, за исключением того, что базовая таблица представляет собой таблицу в памяти.Same recommendations as nonclustered columnstore index on a disk-based table, except the base table is an in-memory table. Индекс columnstore является дополнительным индексом.Columnstore index is an additional index.

Использование кластеризованного индекса columnstore для больших таблиц хранилища данныхUse a clustered columnstore index for large data warehouse tables

Кластеризованный индекс — это не просто индекс, это главное хранилище таблиц.The clustered columnstore index is more than an index, it is the primary table storage. Он позволяет достичь высокого уровня сжатия данных и значительно повысить производительность выполнения запросов в больших хранилищах данных и таблицах измерений.It achieves high data compression and a significant improvement in query performance on large data warehousing fact and dimension tables. Кластеризованные индексы columnstore лучше всего подходят для запросов аналитики, а не транзакционных запросов, так как запросы аналитики, как правило, выполняют операции с большим диапазоном значений, а не поиск определенных значений.Clustered columnstore indexes are best suited for analytics queries rather than transactional queries, since analytics queries tend to perform operations on large ranges of values rather than looking up specific values.

Используйте кластеризованный индекс columnstore в таких ситуациях:Consider using a clustered columnstore index when:

  • В каждой секции есть по крайней мере миллион строк.Each partition has at least a million rows. В индексах columnstore группы строк есть в каждой секции.Columnstore indexes have rowgroups within each partition. Если таблица слишком мала для заполнения группы строк в каждой секции, вы не сможете воспользоваться такими преимуществами индекса columnstore, как сжатие и повышение производительности выполнения запросов.If the table is too small to fill a rowgroup within each partition, you won't get the benefits of columnstore compression and query performance.
  • Как правило, запросы анализируют диапазоны значений.Queries primarily perform analytics on ranges of values. Например, чтобы найти среднее значение столбца, запросу нужно просканировать все значения этого столбца.For example, to find the average value of a column, the query needs to scan all the column values. Затем значения вычисляются путем суммирования, чтобы получить среднее значение.It then aggregates the values by summing them to determine the average.
  • Большинство операций вставки выполняется в больших объемах данных с минимальным количеством операций обновления и удаления.Most of the inserts are on large volumes of data with minimal updates and deletes. Множество рабочих нагрузок, таких как рабочие нагрузки Интернета вещей, вставляют большие объемы данных с минимальным числом операций обновления и удаления.Many workloads such as Internet of Things (IOT) insert large volumes of data with minimal updates and deletes. Для этих рабочих нагрузок пригодятся сжатие и повышение производительности выполнения запросов, что обеспечивается при использовании кластеризованного индекса columnstore.These workloads can benefit from the compression and query performance gains that comes from using a clustered columnstore index.

Не используйте кластеризованный индекс columnstore в следующих ситуациях:Don't use a clustered columnstore index when:

  • Столбцу требуется тип данных varchar(max), nvarchar(max) или varbinary(max).The table requires varchar(max), nvarchar(max), or varbinary(max) data types. Спроектируйте индекс columnstore таким образом, чтобы он не включал эти столбцы.Or, design the columnstore index so that it doesn't include these columns.
  • Данные таблицы не постоянные.The table data is not permanent. Используйте кучу или временную таблицу для быстрого сохранения и удаления данных.Consider using a heap or temporary table when you need to store and delete the data quickly.
  • В таблице содержится меньше миллиона строк на секцию.The table has less than one million rows per partition.
  • Обновления и удаления составляют более 10 % операций в таблице.More than 10% of the operations on the table are updates and deletes. Большое количество операций обновления и удаления вызывает фрагментацию.Large numbers of updates and deletes cause fragmentation. Фрагментация влияет на степень сжатия и производительность запроса. Операция реорганизации позволяет передать все данные в индекс columnstore и устраняет фрагментацию.The fragmentation affects compression rates and query performance until you run an operation called reorganize that forces all data into the columnstore and removes fragmentation. Дополнительные сведения см. в статье Minimizing index fragmentation in columnstore index (Минимизация фрагментации индекса в индексах columnstore).For more information, see Minimizing index fragmentation in columnstore index.

Дополнительные сведения см. в статье Columnstore indexes — data warehousing (Хранилище данных для индексов columnstore).For more information, see Columnstore indexes - data warehousing.

Добавление некластеризованных индексов сбалансированного дерева для эффективного поиска в таблицеAdd B-tree nonclustered indexes for efficient table seeks

Начиная с SQL Server 2016 (13.x);SQL Server 2016 (13.x), вы можете создавать некластеризованные индексы сбалансированного дерева как вторичные индексы кластеризованного индекса columnstore.Beginning with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can create nonclustered B-tree indexes as secondary indexes on a clustered columnstore index. Некластеризованный индекс сбалансированного дерева обновляется при изменении индекса columnstore.The nonclustered B-tree index is updated as changes occur to the columnstore index. Это мощная функция, которую можно использовать в своих интересах.This is a powerful feature that you can use to your advantage.

Вторичный индекс сбалансированного дерева позволяет выполнять более эффективный поиск определенных строк без сканирования всех строк.By using the secondary B-tree index, you can efficiently search for specific rows without scanning through all the rows. Кроме того, появляется доступ к другим возможностям.Other options become available too. Например, можно принудительно задать ограничение первичного или внешнего ключа, применив уникальное ограничение к индексу сбалансированного дерева.For example, you can enforce a primary or foreign key constraint by using a UNIQUE constraint on the B-tree index. Так как неуникальное значение в индекс сбалансированного дерева не вставляется, SQL ServerSQL Server не может вставить значение в columnstore.Since a non-unique value will fail to insert into the B-tree index, SQL ServerSQL Server cannot insert the value into the columnstore.

Используйте индекс сбалансированного дерева в индексе columnstore в следующих случаях:Consider using a B-tree index on a columnstore index to:

  • Выполнение запросов на поиск конкретных значений или небольших диапазонов значений.Run queries that search for particular values or small ranges of values.
  • Применение ограничений, таких как ограничения первичного или внешнего ключа.Enforce a constraint such as a primary key or foreign key constraint.
  • Эффективное выполнение операций обновления и удаления.Efficiently perform update and delete operations. Индекс сбалансированного дерева позволяет быстро находить определенные строки, которые следует обновить или удалить, без сканирования всей таблицы или ее секции.The B-tree index is able to quickly locate the specific rows for updates and deletes without scanning the full table or partition of a table.
  • У вас есть дополнительное место для хранения индекса сбалансированного дерева.You have additional storage available to store the B-tree index.

Использование некластеризованного индекса columnstore для операционной аналитики в реальном времениUse a nonclustered columnstore index for real-time analytics

Начиная с выпуска SQL Server 2016 (13.x);SQL Server 2016 (13.x), вам предоставляется некластеризованный индекс columnstore в таблице rowstore на базе диска или в таблице выполняющейся в памяти OLTP.Beginning with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can have a nonclustered columnstore index on a rowstore disk-based table or an in-memory OLTP table. Таким образом, вы можете выполнять анализ в таблице транзакций в режиме реального времени.This makes it possible to run the analytics in real-time on a transactional table. Хотя транзакции выполняются в базовой таблице, вы можете выполнять анализ в индексе columnstore.While transactions are occurring on the underlying table, you can run analytics on the columnstore index. Изменения доступны для индекса rowstore и columnstore в режиме реального времени, так как обоими индексами управляет одна таблица.Since one table manages both indexes, changes are available in real-time to both the rowstore and the columnstore indexes.

Так как индекс columnstore обеспечивает уровень сжатия в 10 раз выше, чем индекс rowstore, ему необходимо небольшое дополнительное пространство.Since a columnstore index achieves 10x better data compression than a rowstore index, it only needs a small amount of extra storage. Например, если в сжатую таблицу rowstore передается 20 ГБ данных, индексу columnstore может потребоваться 2 ГБ места дополнительно.For example, if the compressed rowstore table takes 20 GB, the columnstore index might require an additional 2 GB. Объем дополнительного пространства также зависит от числа столбцов в некластеризованном индексе columnstore.The additional space required also depends on the number of columns in the nonclustered columnstore index.

Используйте некластеризованный индекс columnstore в таких ситуациях:Consider using a nonclustered columnstore index to:

  • Выполнения анализа в транзакционной таблице rowstore в режиме реального времени.Run analytics in real-time on a transactional rowstore table. Вы можете заменить имеющиеся индексы сбалансированного дерева, предназначенные для аналитики, на некластеризованный индекс columnstore.You can replace existing B-tree indexes that are designed for analytics with a nonclustered columnstore index.

  • Устранение необходимости использования отдельного хранилища данных.Eliminate the need for a separate data warehouse. В большинстве случаев в компаниях транзакции выполняются в таблице rowstore и данные загружаются в отдельное хранилище для выполнения анализа.Traditionally, companies run transactions on a rowstore table and then load the data into a separate data warehouse to run analytics. Для многих рабочих нагрузок можно исключить процесс загрузки и устранить необходимость использования отдельного хранилища данных, создав некластеризованный индекс columnstore в транзакционных таблицах.For many workloads, you can eliminate the loading process and the separate data warehouse by creating a nonclustered columnstore index on transactional tables.

В SQL Server 2016 (13.x);SQL Server 2016 (13.x) предусмотрено несколько стратегий выполнения этого сценария.SQL Server 2016 (13.x);SQL Server 2016 (13.x) offers several strategies to make this scenario performant. Они очень просты, так как вы можете включить некластеризованный индекс columnstore, не изменяя приложение OLTP.It's very easy to try it since you can enable a nonclustered columnstore index with no changes to your OLTP application.

Чтобы добавить дополнительные вычислительные ресурсы, необходимо выполнить анализ на вторичной реплике для чтения,To add additional processing resources, you can run the analytics on a readable secondary. позволяющей отделить обработку транзакционной и аналитической рабочей нагрузки.Using a readable secondary separates the processing of the transactional workload and the analytics workload.

Дополнительные сведения см. в статье Get started with columnstore indexes for real-time operational analytics (Начало работы с индексами columnstore для получения операционной аналитики в реальном времени).For more information, see Get started with columnstore indexes for real-time operational analytics

Дополнительные сведения о выборе подходящего индекса columnstore см. в записи блога Сунила Агарвала (Sunil Agarwal) Which columnstore index is right for my workload? (Какие индексы columnstore подходят для моей рабочей нагрузки?).For more information on choosing the best columnstore index, see Sunil Agarwal's blog Which columnstore index is right for my workload?.

Использование секционированных таблиц для управления данными и повышения производительности запросовUse table partitions for data management and query performance

Индексы columnstore поддерживают секционирование, подходящее для управления данными и их архивирования.Columnstore indexes support partitioning which is a good way to manage and archive data. Секционирование также повышает производительность выполнения запросов путем ограничения операций одной или несколькими секциями.Partitioning also improves query performance by limiting operations to one or more partitions.

Упрощение управления данными с помощью секцийUse partitions to make the data easier to manage

В больших таблицах практичнее всего управлять диапазонами данных с помощью секций.For large tables, the only practical way to manage ranges of data is by using partitions. Преимущества использования секций для таблиц rowstore также относятся к индексам columnstore.The advantages of partitions for rowstore tables also apply to columnstore indexes.

Например, при использовании секций в таблицах rowstore и columnstore можно получить следующие преимущества:For example, both rowstore and columnstore tables use partitions to:

  • Управление размером добавочных резервных копий.Control the size of incremental backups. Вы можете создавать резервные копии секций в разных файловых группах и помечать их как доступные только для чтения.You can back up partitions to separate filegroups and then mark them as read-only. При этом во время архивации файловые группы только для чтения будут пропускаться.By doing this, future backups will skip the read-only filegroups.
  • Снижение затрат на хранение за счет перемещения старых секций в экономичное хранилище.Save storage costs by moving an older partition to less expensive storage. Например, можно выполнить переключение секций, чтобы переместить секцию в экономичное хранилище.For example, you could use partition switching to move a partition to a less expensive storage location.
  • Эффективное выполнение операций путем их ограничения одной секцией.Perform operations efficiently by limiting the operations to a partition. Например, для обслуживания индекса вы можете назначить только фрагментированные секции.For example, you can target only the fragmented partitions for index maintenance.

Кроме того, при использовании секционирования индекса columnstore можно добиться следующего:Additionally, with a columnstore index, you use partitioning to:

  • Экономия 30 % затрат на хранение.Save an additional 30% in storage costs. Вы можете выполнить сжатие старых секций с помощью параметров сжатия COLUMNSTORE_ARCHIVE.You can compress older partitions with the COLUMNSTORE_ARCHIVE compression options. Производительность выполнения запроса к данным снизится, что допустимо, если секция редко запрашивается.The data will be slower for query performance, which is acceptable if the partition is queries infrequently.

Повышение производительности запросов с помощью секцийUse partitions to improve query performance

Используя секции, можно ограничить запросы, чтобы они сканировали только конкретные секции. При этом число сканируемых строк ограничивается.By using partitions, you can limit your queries to scan only specific partitions which limits the number of rows to scan. Например, если индекс секционируется по годам, а запрос выполняет анализ прошлогодних данных, запросу нужно просканировать данные всего в одной секции.For example, if the index is partitioned by year and the query is analyzing data from last year, it only needs to scan the data in one partition.

Использование нескольких секций для индекса columnstoreUse fewer partitions for a columnstore index

Когда объем данных не слишком большой, индекс columnstore позволяет эффективно работать с меньшим количеством секций, чем обычно используется для индекса rowstore.Unless you have a large enough data size, a columnstore index performs best with fewer partitions than what you might use for a rowstore index. Если у вас нет хотя бы миллиона строк на секцию, большинство строк могут отправляться в дельта-хранилище, где они не получают преимуществ высокой производительности и сжатия, обеспечиваемых индексом columnstore.If you don't have at least one million rows per partition, most of your rows might go to the deltastore where they don't receive the performance benefit of columnstore compression. Например, если загрузить миллион строк в таблицу с 10 секциями по 100 000 строк на каждую, все строки перейдут в разностные группы строк.For example, if you load one million rows into a table with 10 partitions and each partition receives 100,000 rows, all of the rows will go to delta rowgroups.

ПримерExample:

  • Загрузите 1 000 000 строк в одну секцию или в несекционированную таблицу.Load 1,000,000 rows into one partition or a non-partitioned table. Вы получите одну сжатую группу строк с 1 000 000 строк.You get one compressed rowgroup with 1,000,000 rows. Это удобно для высокого уровня сжатия данных и высокой производительности запросов.This is great for high data compression and fast query performance.
  • Загрузите 1 000 000 строк с равномерным распределением по 10 секциям.Load 1,000,000 rows evenly into 10 partitions. Каждая секция получает по 100 000 строк. Это значение ниже минимального порога для сжатия columnstore.Each partition gets 100,000 rows, which is less than the minimum threshold for columnstore compression. В результате индекс columnstore может иметь 10 разностных групп строк с 100 000 строк в каждой.As a result the columnstore index could have 10 delta rowgroups with 100,000 rows in each. Вы можете принудительно передать разностные группы строк в индекс columnstore.There are ways to force the delta rowgroups into the columnstore. Тем не менее, если это единственные строки в индексе columnstore, сжатые группы строк будут слишком малы для максимального сжатия и максимальной производительности запросов.However, if these are the only rows in the columnstore index, the compressed rowgroups will be too small for best compression and query performance.

Дополнительные сведения о секционировании см. в записи блога Сунила Агарвала (Sunil Agarwal) Should I partition my columnstore index? (Следует ли секционировать индекс columnstore?).For more information about partitioning, see Sunil Agarwal's blog post, Should I partition my columnstore index?.

Выбор подходящего метода сжатияChoose the appropriate data compression method

Индекс columnstore предлагает два варианта сжатия данных: сжатие columnstore и сжатие архива.The columnstore index offers two choices for data compression: columnstore compression and archive compression. Способ сжатия можно выбрать при создании индекса или изменить его позже с помощью инструкции ALTER INDEX... REBUILD.You can choose the compression option when you create the index, or change it later with ALTER INDEX ... REBUILD.

Повышение производительности запросов при использовании сжатия columnstoreUse columnstore compression for best query performance

Уровень сжатия индекса columnstore обычно в 10 раз выше, чем уровень сжатия индекса rowstore.Columnstore compression typically achieves 10x better compression rates over rowstore indexes. Это стандартный способ сжатия для индексов columnstore, повышающий производительность запросов.It is the standard compression method for columnstore indexes and enables fast query performance.

Высокий уровень сжатия данных при сжатии архиваUse archive compression for best data compression

Сжатие архива позволяет достичь максимального уровня сжатия, если производительность запросов не так важна.Archive compression is designed for maximum compression when query performance is not as important. По сравнению со сжатием columnstore здесь обеспечивается более высокая степень сжатия данных. Однако этот способ имеет свои недостатки.It achieves higher data compression rates than columnstore compression, but it comes with a price. Сжатие и распаковка данных занимает больше времени, поэтому вы не сможете увеличить производительность запросов.It takes longer to compress and decompress the data, so it is not well-suited for fast query performance.

Оптимизация при преобразовании таблицы rowstore в индекс columnstoreUse optimizations when you convert a rowstore table to a columnstore index

Если данные уже находятся в таблице rowstore, можно использовать инструкцию CREATE COLUMNSTORE INDEX для преобразования таблицы в кластеризованный индекс columnstore.If your data is already in a rowstore table, you can use CREATE COLUMNSTORE INDEX to convert the table to a clustered columnstore index. Вы можете оптимизировать производительность запросов после преобразования таблицы, как описано далее.There are a couple optimizations that will improve query performance after the table is converted, described next.

Повышение качества группы строк с помощью MAXDOPUse MAXDOP to improve rowgroup quality

Вы можете настроить максимальное число процессоров для преобразования кучи или кластеризованного индекса сбалансированного дерева в индекс columnstore.You can configure the maximum number of processors for converting a heap or clustered B-tree index to a columnstore index. Чтобы настроить процессоры, используйте параметр максимальной степени параллелизма (MAXDOP).To configure the processors, use the maximum degree of parallelism option (MAXDOP).

При наличии больших объемов данных не назначайте инструкции MAXDOP значение 1, так как быстродействие существенно не увеличится.If you have large amounts of data, MAXDOP 1 will likely be too slow. Лучше всего увеличить значение MAXDOP до 4.Increasing MAXDOP to 4 works fine. Если при этом число строк в нескольких группах строк станет неоптимальным, вы можете выполнить инструкцию ALTER INDEX REORGANIZE, чтобы объединить их в фоновом режиме.If this results in a few rowgroups that do not have the optimal number of rows you can run ALTER INDEX REORGANIZE to merge them together in the background.

Сохранение порядка сортировки индекса сбалансированного дереваKeep the sorted order of a B-tree index

Так как строки в индексе сбалансированного дерева уже хранятся в порядке сортировки, сохранение этого порядка при сжатии строк в индекс columnstore может повысить производительность запросов.Since the B-tree index already stores rows in a sorted order, preserving that order when the rows get compressed into the columnstore index can improve query performance.

Индекс columnstore не сортирует данные, но он использует метаданные для отслеживания минимального и максимального значения каждого сегмента столбца в каждой группе строк.The columnstore index does not sort the data, but it does use metadata to track the minimum and maximum values of each column segment in each rowgroup. При сканировании диапазона значений индекс быстро определяет группу строк, которую можно пропустить.When scanning for a range of values, it can quickly compute when to skip the rowgroup. Если данные упорядочены, он может пропустить несколько групп строк.When the data is ordered, more rowgroups can be skipped.

Чтобы сохранить порядок сортировки во время преобразования, сделайте следующее:To preserve the sorted order during conversion:

  • Используйте инструкцию CREATE COLUMNSTORE INDEX с предложением DROP_EXISTING.Use CREATE COLUMNSTORE INDEX with the DROP_EXISTING clause. При этом также сохраняется имя индекса.This also preserves the name of the index. Если у вас есть скрипты, использующие имя индекса rowstore, их не потребуется обновлять.If you have scripts that already use the name of the rowstore index you won't need to update them.

    В этом примере кластеризованный индекс rowstore в таблице MyFactTable преобразуется в кластеризованный индекс columnstore.This example converts a clustered rowstore index on a table named MyFactTable to a clustered columnstore index. Имя индекса, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, не изменяется.The index name, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, stays the same.

    CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
    ON MyFactTable  
    WITH (DROP_EXISTING = ON);  
    

Ниже перечислены задачи для создания и обслуживания индексов columnstore.These are tasks for creating and maintaining columnstore indexes.

ЗадачаTask Справочные разделыReference Topics ПримечанияNotes
Создание таблицы как кластеризованного индекса columnstoreCreate a table as a columnstore. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), таблицы можно создавать как кластеризованный индекс columnstore.Beginning with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can create the table as a clustered columnstore index. Для этого не нужно создавать таблицу rowstore, а затем конвертировать ее в columnstore.You do not have to first create a rowstore table and then convert it to columnstore.
Создание таблицы в памяти с индексом columnstore.Create a memory table with a columnstore index. CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL) Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), таблицы, оптимизированные для памяти, можно создавать с индексом columnstore.Beginning with SQL Server 2016 (13.x);SQL Server 2016 (13.x), you can create a memory-optimized table with a columnstore index. Индекс columnstore можно добавить и после создания таблицы, используя синтаксис ALTER TABLE ADD INDEX.The columnstore index can also be added after the table is created, using the ALTER TABLE ADD INDEX syntax.
Преобразование таблицы rowstore в таблицу columnstoreConvert a rowstore table to a columnstore. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) Преобразуйте существующую кучу или сбалансированное дерево в columnstore.Convert an existing heap or binary tree to a columnstore. В примерах показано, как обрабатывать существующие индексы, а также имя индекса, которое нужно использовать в процессе преобразования.Examples show how to handle existing indexes and also the name of the index when performing this conversion.
Преобразование таблицы columnstore в rowstoreConvert a columnstore table to a rowstore. CREATE CLUSTERED INDEX (Transact-SQL) или Преобразование таблицы columnstore обратно в кучу rowstoreCREATE CLUSTERED INDEX (Transact-SQL) or Convert a columnstore table back to a rowstore heap Обычно это преобразование не требуется, но бывают ситуации, когда оно необходимо.Usually this conversion isn't necessary, but there can be times when you need to convert. В примерах показано, как преобразовать columnstore в кучу или кластеризованный индекс.Examples show how to convert a columnstore to a heap or clustered index.
Создание индекса columnstore в таблице rowstoreCreate a columnstore index on a rowstore table. CREATE COLUMNSTORE INDEX (Transact-SQL)CREATE COLUMNSTORE INDEX (Transact-SQL) Таблица rowstore может включать один индекс columnstore.A rowstore table can have one columnstore index. Начиная с версии SQL Server 2016 (13.x);SQL Server 2016 (13.x), индекс columnstore может иметь отфильтрованное условие.Beginning with SQL Server 2016 (13.x);SQL Server 2016 (13.x), the columnstore index can have a filtered condition. В примерах показан основной синтаксис.Examples show the basic syntax.
Создание высокопроизводительных индексов для оперативной аналитикиCreate performant indexes for operational analytics. Начало работы с Columnstore для получения операционной аналитики в реальном времениGet started with Columnstore for real time operational analytics Описывает процесс создания дополнительных индексов columnstore и сбалансированного дерева, которые позволят использовать индексы сбалансированного дерева в запросах OLTP и индексы columnstore в запросах аналитики.Describes how to create complementary columnstore and B-tree indexes so that OLTP queries use B-tree indexes and analytics queries use columnstore indexes.
Создание высокопроизводительных индексов сolumnstore для хранилищ данныхCreate performant columnstore indexes for data warehousing. Columnstore indexes — data Warehousing (Хранилище данных для индексов columnstore)Columnstore indexes - data Warehousing Описывает использование индексов сбалансированного дерева в таблицах columnstore для создания высокопроизводительных запросов к хранилищу данных.Describes how to use B-tree indexes on columnstore tables to create performant data warehousing queries.
Использование индекса сбалансированного дерева для принудительного применения ограничения первичного ключа к индексу columnstore.Use a B-tree index to enforce a primary key constraint on a columnstore index. Columnstore indexes — data Warehousing (Хранилище данных для индексов columnstore)Columnstore indexes - data warehousing Показано, как объединить индексы сбалансированного дерева и columnstore для принудительного применения ограничений первичного ключа к индексу columnstore.Shows how to combine B-tree and columnstore indexes to enforce primary key constraints on the columnstore index.
Удаление индекса columnstore.Drop a columnstore index DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL) Для удаления индекса columnstore используется стандартный синтаксис DROP INDEX, применяемый индексами сбалансированного дерева.Dropping a columnstore index uses the standard DROP INDEX syntax that B-tree indexes use. При удалении кластеризованного индекса columnstore таблица columnstore преобразуется в кучу.Dropping a clustered columnstore index will convert the columnstore table to a heap.
Удаление строки из индекса columnstoreDelete a row from a columnstore index DELETE (Transact-SQL)DELETE (Transact-SQL) Используйте синтаксис DELETE (Transact-SQL) для удаления строки.Use DELETE (Transact-SQL) to delete a row.

Строкаcolumnstore : SQL ServerSQL Server помечает строку как логически удаленную, но не возвращает физическое хранилище для строки до тех пор, пока индекс не будет перестроен.columnstore row: SQL ServerSQL Server marks the row as logically deleted but does not reclaim the physical storage for the row until the index is rebuilt.

Строкаdeltastore : SQL ServerSQL Server логически и физически удаляет строку.deltastore row: SQL ServerSQL Server logically and physically deletes the row.
Обновление строки в индексе columnstoreUpdate a row in the columnstore index UPDATE (Transact-SQL)UPDATE (Transact-SQL) Используйте синтаксис UPDATE (Transact-SQL) для обновления строки.Use UPDATE (Transact-SQL) to update a row.

Строкаcolumnstore : SQL ServerSQL Server помечает строку как логически удаленную, а затем вставляет обновленную строку в deltastore.columnstore row: SQL ServerSQL Server marks the row as logically deleted, and then inserts the updated row into the deltastore.

Строкаdeltastore : SQL ServerSQL Server обновляет строку в deltastore.deltastore row: SQL ServerSQL Server updates the row in the deltastore.
Принудительное перемещение всех строк из deltastore в columnstoreForce all rows in the deltastore to go into the columnstore. ALTER INDEX (Transact-SQL) ... REBUILDALTER INDEX (Transact-SQL) ... REBUILD

Реорганизация и перестроение индексовReorganize and Rebuild Indexes
Инструкция ALTER INDEX с параметром REBUILD перемещает все строки в columnstore.ALTER INDEX with the REBUILD option forces all rows to go into the columnstore.
Дефрагментация индекса columnstoreDefragment a columnstore index ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL) ALTER INDEX ... REORGANIZE дефрагментирует индексы columnstore в оперативном режиме.ALTER INDEX ... REORGANIZE defragments columnstore indexes online.
Слияние таблиц с индексами columnstore.Merge tables with columnstore indexes. MERGE (Transact-SQL)MERGE (Transact-SQL)

Следующие шагиNext steps

Чтобы создать пустой индекс columnstore для:To create an empty columnstore index for:

Дополнительные сведения о преобразовании имеющейся кучи rowstore или индекса сбалансированного дерева в кластеризованный индекс columnstore или о создании некластеризованного индекса columnstore см. в статье CREATE COLUMNSTORE INDEX (Transact-SQL).For more information on how convert an existing rowstore heap or B-tree index into a clustered columnstore index, or to create a nonclustered columnstore index, refer to CREATE COLUMNSTORE INDEX (Transact-SQL).