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

Индексы Columnstore — руководство по загрузке данных

  • Статья

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

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

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

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

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

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

Снимок экрана, показывающий загрузку в кластеризованный индекс columnstore.

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

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

Примечание.

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

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

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

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

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

  • Оптимизация блокировки: Блокировка X группы строк автоматически приобретается при загрузке данных в сжатую группу строк. Однако при массовой загрузке в дельта группу строк получается блокировка для дельта группы строк, X но тем не менее ядро СУБД по-прежнему получает блокировки страниц и экстентов, так как X блокировка группы строк не является частью иерархии блокировок.

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

Планирование объемов массовой загрузки для уменьшения количества дельта-групп строк

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

Эти сценарии описывают, когда загруженные строки попадают непосредственно в columnstore и когда они попадают в deltastore. В примере каждая группа строк может содержать от 102 400 до 1 048 576 строк на группу. На практике максимальный размер группы строк может быть меньше 1048 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 строк в таблицу. Результаты показывают наличие одной СЖАТОЙ группы строк в хранилище столбцов (в виде сжатых сегментов столбцов) и одной строки в 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;

Снимок экрана: группа строк и deltastore для пакетной загрузки.

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

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

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

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

Эта команда загружает данные в индекс columnstore аналогично bcp или массовой вставке, но в одном пакете. Если число строк в промежуточной таблице < 102400, строки загружаются в delta групу строк, в противном случае строки загружаются непосредственно в сжатую групу строк. Одно из ключевых ограничений заключалось в том, что эта операция 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 но ядро СУБД по-прежнему получает блокировки страниц и экстентов, так как X блокировка группы строк не является частью иерархии блокировки.

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

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

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

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

Примечание.

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

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

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

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

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

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

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

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