Секционирование и работа с секционированными таблицами

Back

В ходе работ над большими таблицами мы постоянно сталкиваемся с проблемами с производительностью их обслуживания и обновления данных. Одним из наиболее продуктивных и удобных решений возникающих проблем является секционирование.
Если в общих словах, то секционирование – это разбиение таблицы или индекса на блоки. В зависимости от настройки секционирования блоки могут быть различных размеров, могут храниться в разных файловых группах и файлах.
У секционирования есть как преимущества, так и недостатки.
Преимущества хорошо расписаны на сайте компании Microsoft, приведу выдержку:


«Секционирование больших таблиц или индексов может дать следующие преимущества в управляемости и производительности.

  • Это позволяет быстро и эффективно переносить подмножества данных и обращаться к ним, сохраняя при этом целостность набора данных. Например, такая операция, как загрузка данных из OLTP в систему OLAP, выполняется за секунды, а не за минуты и часы, как в случае несекционированных данных.
  • Операции обслуживания можно выполнять быстрее с одной или несколькими секциями. Операции более эффективны, так как выполняются только с поднаборами данных, а не со всей таблицей. Например, можно сжать данные в одну или несколько секций или перестроить одну или несколько секций индекса.
  • Можно повысить скорость выполнения запросов в зависимости от запросов, которые часто выполняются в вашей конфигурации оборудования. Например, оптимизатор запросов может быстрее выполнять запросы на эквисоединение двух и более секционированных таблиц, если в этих таблицах одни и те же столбцы секционирования, потому что можно соединить сами секции.

В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. SQL Server может одновременно обращаться только к одному диску, что может снизить производительность. Для ускорения сортировки данных рекомендуется распределить файлы данных в секциях по нескольким жестким дискам, создав RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции.
Кроме того, можно повысить производительность, применяя блокировки на уровне секций, а не всей таблицы. Это может уменьшить количество конфликтов блокировок для таблицы
».

К недостаткам же можно отнести сложность в администрировании и поддержке работы секционированных таблиц.

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

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

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

1. Найти нужные строки в большой таблице;
2. Удалить найденные строки из таблицы и индекса;
3. Вставить новые строки в таблицу, обновить индекс.

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

Перейдем от слов к делу и покажем, как же это реализовать.

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

Для обновления данных нам потребуется мини-копия целевой таблицы. Мини-копией она является потому, что в ней будут храниться данные, которые должны добавиться в целевую таблицу, т.е. данные всего за 1 месяц. Так же потребуется третья пустая таблица для реализации обмена данных. Зачем она нужна – объясню позже.

К мини-копии и таблице для обмена ставятся жесткие условия:

  • До использования оператора SWITCH должны существовать обе таблицы. Перед выполнением операции переключения в базе данных должны существовать и таблица, откуда перемещается секция (исходная таблица), и таблица, получающая секцию (целевая таблица).
  • Секция-получатель должна существовать и должна быть пустой. Если таблица добавляется как секция в уже существующую секционированную таблицу или секция перемещается из одной секционированной таблицы в другую, то секция-получатель должна существовать и быть пустой.
  • Несекционированная таблица-получатель должна существовать и должна быть пустой. Если секция предназначена для формирования единой несекционированной таблицы, то необходимо, чтобы таблица, получающая новую секцию, существовала и являлась пустой несекционированной таблицей.
  • Секции должны быть из одного и того же столбца. Если секция переключена из одной секционированной таблицы в другую, то обе таблицы должны быть секционированы по одному и тому же столбцу.
  • Исходная и целевая таблицы должны находиться в одной и той же файловой группе. Исходная и целевая таблицы в инструкции ALTER TABLE...SWITCH должны храниться в одной и той же файловой группе, так же как и их столбцы с большими значениями. Любые соответствующие индексы, секции индексов или индексированные представления секций также должны храниться в той же файловой группе. Однако она может отличаться от файловой группы для соответствующих таблиц или других соответствующих индексов.

Объясню ограничения на нашем примере:

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

2. Таблица для обмена должна быть пустой и так же должна быть секционированна по тому же столбцу или же должна храниться в той же файловой группе.

3. Реализуем обмен.

   Сейчас мы имеем следующее:
      • Таблица с данными за все времена (далее Table_A)
      • Таблица с данными за 1 месяц (далее Table_B)
      • Пустая таблица (далее Table_C)

Первым делом нам нужно узнать в какой секции у нас хранятся данные.
Узнать это можно запросом:

SELECT 
  count(*) as [count_rows]
  , $PARTITION.[PartitionFunction1](dt) as [section]
  , rank() over (order by  $PARTITION.[PartitionFunction1](dt))
FROM dbo.[Table_B] (nolock)
group by  $PARTITION.[PartitionFunction1](dt)

В этом запросе мы получаем секции, в которых есть строки с информацией. Количество можно не подсчитывать – мы это делали для проверки необходимости обмена данных. Rank же используем, чтобы можно было идти в цикле и обновлять несколько секций в одной процедуре.

Как только узнали в каких секциях у нас хранятся данные – их можно менять местами. Допустим, что данные хранятся в секции 1.

Тогда нужно выполнить следующие операции:
• Поменять секции из целевой таблицы с таблицей для обмена.
ALTER TABLE [dbo].[Table_A] SWITCH PARTITION 1 TO [dbo].[Table_C] PARTITION 1
Теперь мы имеем следующее:
В целевой таблице не осталось данных в нужной нам секции, т.е. секция пуста
• Поменять местами секции из целевой таблицы и мини-копии
ALTER TABLE [dbo].[Table_B] SWITCH PARTITION 1 TO [dbo].[Table_A] PARTITION 1
Теперь мы имеем следующее:
В целевой таблице появились данные за месяц, а в мини-копии теперь пустота
• Очистить или удалить таблицу для обмена.

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

Спасибо за проявленный интерес! Интересующие материалы придут вам на указанную почту в течение одного рабочего дня.

Поздравляем, ваша регистрация успешно пройдена!