Индексы columnstore. Руководство по загрузке данных

Область применения:yesSQL Server (все поддерживаемые версии) YesБаза данных SQL Azure YesУправляемый экземпляр SQL Azure yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

Рекомендации и параметры для загрузки данных в индексы columnstore с помощью стандартных методов массовой загрузки и тонкой вставки SQL. Загрузка данных в индекс columnstore является неотъемлемой частью любого процесса хранения данных, так как данные перемещаются в индекс при подготовке к анализу.

Незнакомы с индексами columnstore? См. статьи, посвященные обзору индексов columnstore и их архитектуре.

Что такое массовая загрузка?

Массовая загрузка — это тип добавления большого числа строк в хранилище данных. Это самый эффективный подход к перемещению данных в индекс columnstore, так как перемещаются пакеты строк. При массовой загрузке группы строк максимально заполняются, а затем сжимаются непосредственно в columnstore. В deltastore отправляются только те строки, которые по завершении загрузки не собирают даже минимальное число строк (102 400) на группу строк.

Чтобы выполнить массовую загрузку, вы можете использовать программу bcp, службы Integration Services или выбрать строки из промежуточной таблицы.

Loading into a clustered columnstore index

Как видно на диаграмме, массовая загрузка:

  • Не выполняет предварительную сортировку данных. Данные вставляются в группы строк в порядке их получения.
  • Если размер >пакета равен 102400, строки находятся непосредственно в сжатых группах строк. Рекомендуется выбрать размер >пакета =102400 для эффективного массового импорта, так как можно избежать перемещения строк данных в разностные группы строк, прежде чем строки в конечном итоге будут перемещены в сжатые группы строк фоновым потоком, перемещение кортежей (ТМ).
  • Если размер < пакета 102 400 или остальные строки равны < 102 400, строки загружаются в разностные группы строк.

Примечание

В таблице rowstore с данными некластеризованного индекса columnstore SQL Server всегда вставляет данные в базовую таблицу. Данные никогда не вставляются непосредственно в индекс columnstore.

Ниже приведены встроенные механизмы оптимизации производительности в операции массовой загрузки.

  • Параллельные загрузки. У вас может быть несколько параллельных массовых загрузок (с командой bcp или инструкцией BULK INSERT). Каждая их них загружает отдельный файл данных. В отличие от массовых загрузок rowstore в SQL Server, TABLOCK задавать не нужно, так как каждый поток массового импорта будет загружать данные только в отдельные группы строк (сжатые или разностные) с монопольной блокировкой.

  • Ограниченное протоколирование. Прямая загрузка данных в сжатые группы строк приводит к существенному уменьшению размера журнала. Например, если данные были сжаты в 10 раз, соответствующий журнал транзакций будет приблизительно в 10 раз меньше без применения TABLOCK, простой модели восстановления или модели восстановления с неполным протоколированием. Все данные, которые попадают в разностную группу строк, подвергаются полному протоколированию. Сюда входят все пакеты с размером менее 102 400 строк. Рекомендуется использовать batchsize >= 102400. Так как TABLOCK не требуется, данные можно загружать параллельно.

  • Минимальное протоколирование. Сократить протоколирование можно еще больше, если выполнить требования для минимального протоколирования. Однако, в отличие от загрузки данных в rowstore, параметр TABLOCK приводит к применению к таблице блокировки X, а не BU (массовое обновление). Поэтому параллельная загрузка данных невозможна. Дополнительные сведения о блокировке см. в разделе Блокировки и управление версиями строк.

  • Оптимизация блокировки. Блокировка X автоматически применяется к группе строк при загрузке данных в сжатую группу строк. Однако при массовой загрузке в разностную группу строк устанавливается блокировка X для группы строк, но SQL Server по-прежнему блокирует СТРАНИЦУ или ЭКСТЕНТ, так как блокировка X группы строк не является частью иерархии блокировки.

Если имеется некластеризованный индекс сбалансированного дерева для индекса columnstore, оптимизация блокировки или ведения журнала для самого индекса невозможна, но оптимизация кластеризованного индекса columnstore, как описано выше, возможна.

Обратите внимание, что операции DML (вставка, удаление, обновление) не являются операциями пакетного режима, так как они не выполняются параллельно.

Планирование размеров массовых загрузок для уменьшения числа разностных групп строк

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

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

Строки для массовой загрузки Строки, добавленные в сжатую группу строк Строки, добавленные в разностную группу строк
102 000 0 102 000
145,000 145,000

Размер группы строк: 145,000
0
1,048,577 1 048 576

Размер группы строк: 1 048 576.
1
2,252,152 2,252,152

Размеры групп строк: 1 048 576, 1 048 576, 155 000.
0
     

В следующем примере показаны результаты загрузки 1 048 577 строк в таблицу. Результаты показывают наличие одной СЖАТОЙ rowgroup в columnstore (в виде сжатых сегментов столбцов) и 1 строки в deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id, 
  state, state_desc, total_rows, deleted_rows, size_in_bytes   
FROM sys.dm_db_column_store_row_group_physical_stats  

Rowgroup and deltastore for a batch load

Использование промежуточной таблицы для улучшения производительности

Если вы загружаете данные только для промежуточного хранения перед их последующими преобразованиями, загрузка таблицы в таблицу кучи будет происходить значительно быстрее по сравнению с загрузкой данных в кластеризованную таблицу columnstore. Кроме того, загрузка данных во [временную таблицу] также будет происходить значительно быстрее, чем загрузка таблицы в постоянное хранилище.

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

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Эта команда загружает данные в индекс columnstore примерно так же, как и с помощью инструкций BCP или Bulk Insert, но в виде одного пакета. Если количество строк в промежуточной таблице < 102400, строки загружаются в разностную группу строк, в противном случае строки загружаются непосредственно в сжатую группу строк. Одно из основных ограничений заключалось в том, что эта операция INSERT была однопотоковой. Чтобы загрузить данные в параллельном режиме, можно создать несколько промежуточных таблиц или выполнить инструкцию INSERT/SELECT с неперекрывающимися диапазонами строк из промежуточной таблицы. В SQL Server 2016 (13.x); это ограничение отсутствует. Команда, приведенная ниже, загружает данные из промежуточной таблицы в параллельном режиме, но необходимо задать TABLOCK. Вам может показаться, что это противоречит тому, что говорилось ранее о массовой загрузке. Однако ключевое различие заключается в том, что параллельная загрузка данных из промежуточной таблицы осуществляется в рамках одной транзакции.

INSERT INTO [<columnstore index>] WITH (TABLOCK) 
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

При загрузке в кластеризованный индекс columnstore из промежуточной таблицы доступны следующие оптимизации:

  • Оптимизация журнала: Уменьшение ведения журнала при загрузке данных в сжатую группу строк.
  • Оптимизация блокировки: При загрузке в сжатую группу строк получается блокировка X в группе строк. Однако для разностной группы строк требуется блокировка X для группы строк, но SQL Server по-прежнему блокирует СТРАНИЦУ или ЭКСТЕНТ, так как блокировка X группы строк не является частью иерархии блокировки.

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

Что такое тонкая вставка?

Тонкая вставка — это способ перемещения отдельных строк в индекс columnstore. Для тонких вставок используется инструкция INSERT INTO. При их использовании все строки попадают в deltastore. Это эффективно для небольшого числа строк и совсем непрактично для больших загрузок.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)  

Примечание

Следует отметить, что параллельные потоки, выполняемые с помощью инструкции INSERT INTO для вставки значений в кластеризованный индекс columnstore, могут вставлять строки в одну и ту же группу строк deltastore.

Как только в группе окажется 1 048 576 строк, разностная группа строк будет отмечена как закрытая, но она по-прежнему будет доступна для запросов и операций обновления или удаления, хотя новые вставленные строки будут добавляться в существующую или новую группу строк deltastore. Существует фоновый поток, называемый задачей переноса кортежей (TM) , который периодически сжимает закрытые разностные группы строк примерно через каждые 5 минут. Для сжатия закрытой разностной группы строк можно явным образом вызвать следующую команду.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE  

Если вы хотите принудительно закрыть и сжать разностную группу строк, можно выполнить следующую команду. Выполнение этой команды может потребоваться, если вы загрузили строки и никакие новые строки не нужны. Благодаря явному закрытию и сжатию разностной группы строк можно экономить место в хранилище и повышать производительность аналитических запросов. Рекомендуется вызвать эту команду, если вы не ожидаете вставки новых строк.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)  

Как работает загрузка в секционированную таблицу

Для секционированных данных SQL Server сначала назначает каждую строку секции, а затем выполняет операции columnstore для данных в секции. Каждая секция содержит собственные группы строк, а также как минимум одну разностную группу строк.

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

Теперь запись блога размещена на сайте techcommunity. Запись датируется 11.03.2015: Data Loading performance considerations with Clustered Columnstore indexes (Вопросы производительности загрузки данных с использованием кластеризованных индексов columnstore).