Секционирование в SQL Server

блог о bi, №1 в рунете

Секционирование («партицирование») данных — это процесс разделения больших объемов данных на меньшие, более управляемые части, известные как секции.

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

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

Функция секционирования

Функция секционирования — это объект базы данных, определяющий, как строки таблицы или индекса сопоставляются с набором секций на основе значений определенного столбца, называемого столбцом секционирования. Каждое значение в столбце секционирования является входными данными для функции секционирования, которая возвращает значение секции.

Функция секционирования создаётся с помощью инструкции CREATE PARTITION FUNCTION. Она определяет правила, согласно которым строки таблицы или индекса будут распределяться по секциям на основе значений элементов заданного столбца. Например, можно создать функцию секционирования, которая будет распределять строки по месяцам года:

CREATE PARTITION FUNCTION SalesDatePF (DATE)
AS RANGE RIGHT FOR VALUES ('20200101', '20200201', ..., '20291231');

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

CREATE PARTITION SCHEME SalesDatePS
AS PARTITION SalesDatePF TO ([PRIMARY], [PRIMARY], ..., [PRIMARY]);

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

Обслуживание секционированных таблиц

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

Мониторинг производительности: регулярный мониторинг показателей производительности, таких как время выполнения запросов, использование CPU и памяти, помогает выявлять узкие места и оптимизировать работу системы.
Анализ запросов: использование инструментов, таких как EXPLAIN и EXECUTE PLAN, позволяет анализировать планы выполнения запросов и оптимизировать их для улучшения производительности.
Контроль за размещением данных: в некоторых случаях (например, при увеличении данных внутри партиции) может потребоваться перенос данных между партициями или перенос самих партиций для балансировки нагрузки или оптимизации хранения.
Резервное копирование: для обеспечения отказоустойчивости и возможности восстановления после сбоев необходимо регулярно создавать резервные копии данных, включая системные таблицы и данные пользователей.

Автоматическое обновление статистики: для поддержания точности планов выполнения запросов важно регулярно обновлять статистику для партиций. В MS SQL Server можно настроить автоматическое обновление статистики для улучшения производительности запросов.
Подробная информация по обслуживанию секционированных страниц содержится в статье «Обслуживание БД»

Инкрементная статистика

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

Зачем нужна инкрементная статистика?

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

Настройка инкрементной статистики

В MS SQL Server инкрементная статистика доступна через опцию CREATE STATISTICS с параметром WITH INCREMENTAL = TRUE.

CREATE PARTITION SCHEME SalesDatePS
CREATE STATISTICS my_stats ON my_table (my_column)
WITH INCREMENTAL = TRUE;

Оптимизация удаления данных через TRUNCATE PARTITION

Операция TRUNCATE PARTITION используется для удаления всех строк из определенной партиции без записи индивидуальных удалений.

TRUNCATE PARTITION OF table_name PARTITION (column_name = value);

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

Вывод

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

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

ВЕРНУТЬСЯ К БЛОГУ