Время прочтения: 4 мин.

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

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

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

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

Рассмотрим пример.

Допустим, у нас есть основная таблица MAIN_TABLE, секционированная по столбцу «Дата операции» (DATE_OPERATION) и имеющая индексы. Для возможности переключения секций все индексы должны быть «выровненными», то есть включать в себя столбец секционирования.

Функция секционирования по дате:

CREATE PARTITION FUNCTION [PF_DATE_OPERATION] (date)  
AS RANGE LEFT FOR VALUES (…, ‘2020-01-01’, ‘2020-01-02’, ‘2020-01-03’, …)

Схема секционирования:

CREATE PARTITION SCHEME [PS_DATE_OPERATION]  
AS PARTITION [PF_DATE_OPERATION] ALL TO ([FILE_GROUP_OPERATION])

Основная таблица, секционированная по полю [DATE_OPERATION]:

CREATE TABLE [MAIN_TABLE] ([ID_OPERATION] int NOT NULL, [DATE_OPERATION] date NOT NULL, NAME_OPERATION NVARCHAR(255) NULL, …) ON [PS_DATE_OPERATION]([DATE_OPERATION])

Кластеризованный индекс:

CREATE CLUSTERED INDEX [IDX_ID_OPERATION] ON [MAIN_TABLE] ([ID_OPERATION], [DATE_OPERATION]) ON [PS_DATE_OPERATION]([DATE_OPERATION])

Некластеризованный индекс:

CREATE NONCLUSTERED INDEX [IDX_NAME_OPERATION] ON [MAIN_TABLE] ([NAME_OPERATION]) INCLUDE ([DATE_OPERATION])) ON [PS_DATE_OPERATION]([DATE_OPERATION])

Для загрузки данных нам потребуется дополнительная таблица со структурой полей и индексами, аналогичными основной. Также, эта таблица (или секция, если она секционирована) должна находиться в той же файловой группе, что и секция основной таблицы, на место которой мы будем ее подключать.

Создадим дополнительную таблицу в той же файловой группе, что и основная таблица:

CREATE TABLE [ADDITIONAL_TABLE] ([ID_OPERATION] int NOT NULL, [DATE_OPERATION] datetime NOT NULL, NAME_OPERATION NVARCHAR(255) NULL, …) ON [FILE_GROUP_OPERATION]

Также создадим индексы. В реальной задаче имеет смысл создавать некластеризованные индексы уже после загрузки данных, что может дать выигрыш в производительности.

CREATE CLUSTERED INDEX [IDX_ID_OPERATION_ADD] ON [ADDITIONAL_TABLE] ([ID_OPERATION], [DATE_OPERATION]) ON [FILE_GROUP_OPERATION]

CREATE NONCLUSTERED INDEX [IDX_NAME_OPERATION_ADD] ON [ADDITIONAL_TABLE] ([NAME_OPERATION]) INCLUDE ([DATE_OPERATION])) ON [FILE_GROUP_OPERATION]

Наложим на таблицу ограничение по полю секционирования, соответственно секции, в которую мы будем ее переключать, так как данные должны явно соответствовать только одной, конкретной секции (переменная @Import_Date – дата, за которую грузим данные):

ALTER TABLE  [ADDITIONAL_TABLE] ADD CONSTRAINT CHECK_DATE_OPERATION CHECK ([DATE_OPERATION] >= @Import_Date and ([DATE_OPERATION] < DATEADD(DAY,1,@Import_Date) )

Загружаем данные за очередной день в дополнительную таблицу (для больших объемов предпочтительнее использование BULK INSERT).

INSERT INTO [ADDITIONAL_TABLE] (select * from Data_Source where Date_Operation >= @Import_Date and @Import_Date < DateAdd(day,1,@Import_Date))

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

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

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

Declare @Num_Partition int;
SELECT @Num_Partition = $PARTITION. [PF_DATE_OPERATION] (@Import_Date);

И само переключение (целевая секция для переключения должна быть пустой):

ALTER TABLE [ADDITIONAL_TABLE]  SWITCH TO [MAIN_TABLE] PARTITION (@Num_Partition)

Нужно учитывать, что непосредственно для операции переключения требуется монопольный доступ к обеим таблицам. Поэтому имеет смысл выполнять эту операцию во время определенного заранее времени обслуживания БД (например, в ночное время). Так как эта операция выполняется практически мгновенно, время недоступности таблицы будет минимальным.

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

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