Секционирование таблиц MS SQL
При организации ETL-процессов, может возникнуть необходимость загрузки большого объема данных в таблицы, активно используемые пользователями. При этом, зачастую возникают проблемы с производительностью, связанные с блокировками обновляемых таблиц.Эти проблемы можно решить воспользовавшись возможностями, которые предоставляет механизм секционирования таблиц MS SQL.
Механизм секционирования таблиц MS SQL позволяет сначала загрузить данные в дополнительную таблицу, провести в ней все необходимые манипуляции с данными и подключить ее к основной таблице, как очередную секцию.
Например, у меня была большая таблица с операциями, секционированная по дате операции. Нужно было организовать процесс ежедневной загрузки данных в эту таблицу.
При вставке большого объема записей сразу в основную таблицу, появлялись проблемы с блокировками, в связи с одновременными запросами пользователей на выборку данных из этой же таблицы. Для решения этих проблем решено было воспользоваться переключением секций.
Суть его в следующем. Данные грузятся не сразу в основную таблицу, а в аналогичную по структуре служебную таблицу. На этой же таблице строятся необходимые индексы, и выполняется, в случае необходимости, предварительная обработка данных. После чего, уже подготовленные данные можно подключить к основной таблице, что происходит практически мгновенно.
Рассмотрим пример.
Допустим, у нас есть основная таблица MAIN_TABLE, секционированная по столбцу «Дата операции» (DATE_operation) и имеющая индексы. Для возможности переключения секций все индексы должны быть «выровненными», то есть включать в себя столбец секционирования.
Функция секционирования по дате:
Схема секционирования:
Основная таблица, секционированная по полю [DATE_operation]:
Кластеризованный индекс:
Некластеризованный индекс:
Для загрузки данных нам потребуется дополнительная таблица со структурой полей и индексами, аналогичными основной. Также, эта таблица (или секция, если она секционирована) должна находиться в той же файловой группе, что и секция основной таблицы, на место которой мы будем ее подключать.
Создадим дополнительную таблицу в той же файловой группе, что и основная таблица:
Также создадим индексы. В реальной задаче имеет смысл создавать некластеризованные индексы уже после загрузки данных, что может дать выигрыш в производительности.
Наложим на таблицу ограничение по полю секционирования, соответственно секции, в которую мы будем ее переключать, так как данные должны явно соответствовать только одной, конкретной секции (переменная @Import_Date – дата, за которую грузим данные):
Загружаем данные за очередной день в дополнительную таблицу (для больших объемов предпочтительнее использование BULK insert).
Так как вставка идет в дополнительную таблицу, запросы пользователей к основной таблице и загрузка данных идут параллельно и не мешают друг другу.
После окончания загрузки, при желании, можно провести необходимые действия с данными и переключить уже готовые данные в основную таблицу. Так как эта операция проводится только над метаданными, переключение происходит моментально.
Для переключения требуется знать, в какую секцию следует переключить нашу таблицу. Получим номер секции в основной таблице, соответствующий дате операции загруженных данных:
И само переключение (целевая секция для переключения должна быть пустой):
Нужно учитывать, что непосредственно для операции переключения требуется монопольный доступ к обеим таблицам. Поэтому имеет смысл выполнять эту операцию во время определенного заранее времени обслуживания БД (например, в ночное время). Так как эта операция выполняется практически мгновенно, время недоступности таблицы будет минимальным.
После переключения, новые данные сразу будут доступны для запросов пользователей основной таблицы. Причем индексы перестраивать уже не нужно, так как они тоже подключились к основной таблице.
В результате применения данного метода решилась проблема с блокировками запросов пользователей во время добавления в таблицу новых данных, а время недоступности таблицы сведено к минимуму.