Теоретический материал
Секционирование таблиц (горизонтальное секционирование таблиц) и индексов применяется с целью повышения производительности, масштабируемости и управляемости в больших базах данных (VLDB – very large databases). VLDB принято называть базы данных, совокупный размер которых измеряется сотнями гигабайт, а возможно терабайтами и больше. Большая таблица – это такая таблица, показатели производительности или временные затраты на обслуживание которой выходят за допустимые рамки. Кроме того, таблицу можно считать большой, если действия, производимые одним пользователем, оказывают значительное влияние на другого пользователя, или если операции обслуживания базы данных влияют на возможности остальных пользователей.
В многопроцессорных системах разделение больших таблиц приводит к улучшению производительности за счет параллелизма. Крупномасштабные операции над чрезвычайно большими наборами данных (в миллионы строк) могут извлечь выгоду из параллельной обработки независимых подмножеств данных. В качестве простого примера улучшения производительности при использовании секционирования может выступать агрегирование (группировка). Вместо того чтобы группировать данные в одной большой таблице, SQL Server может группировать их в нескольких секциях независимо друг от друга, и затем объединить агрегаты.
В SQL Server связанные таблицы (к примеру, Bill и BillItem), которые разделены по одному и тому же ключу секционирования (в нашем случае BillId) и одной и той же функции секционирования, называются выровненными. Если оптимизатор SQL Server обнаруживает, что соединяются (join) две секционированные и выровненные таблицы, он может предпочесть соединить сначала данные, располагающиеся в одних и тех же секциях, а затем объединить результаты. Это позволяет SQL Server более эффективно использовать многопроцессорные системы.
Секционирование делает большие таблицы и индексы более управляемыми, так как позволяет быстро и эффективно получать доступ к поднаборам данных и управлять ими, при этом сохраняя целостность всей коллекции. При использовании секционирования загрузка данных из системы OLTP в систему OLAP занимают значительно меньше времени по сравнению с использованием несекционированных таблиц и индексов.
Данные секционированных таблиц и индексов подразделяются на блоки (секциям), которые могут быть распределены по нескольким файловым группам в базе данных. Данные секционируются горизонтально, поэтому группы строк сопоставляются с отдельными секциями. Таблица или индекс рассматриваются как единая логическая сущность при выполнении над данными запросов или обновлений. Все секции одного индекса или таблицы должны находиться в одной и той же базе данных.
Секционированные таблицы и индексы поддерживают все свойства и возможности, связываемые с разработкой и опрашиванием стандартных таблиц и индексов, включая ограничения, значения по умолчанию, значения идентификации и отметок времени, а также триггеры.
Перед секционированием таблицы или индекса необходимо запланировать создание следующих объектов базы данных:
· функции секционирования;
· схемы секционирования.
Функция секционирования на основе значений некоторых столбцов, называемых столбцами секционирования, определяет, каким образом строки в таблице или индексе распределяются по секциям.
Схема секционирования сопоставляет каждую из секций, определенных функцией секционирования, с файловой группой.
При планировании функции секционирования следует исходить из двух условий: столбцов, значения которых определяют то, каким образом секционируется таблица, то есть так называемых столбцов секционирования, и диапазона значений столбца секционирования для каждой из секций. Этот диапазон определяет число секций, на которые разбивается таблица. В MS SQL Server у таблиц может быть не более 1000 секций. Например, в качестве функции секционирования таблицы Bill может быть выбрано разбиение по месяцам продаж по столбцу Bill.Date.
При частом выполнении запросов на эквивалентное соединение двух и более секционированных таблиц, их секционированные столбцы должны совпадать со столбцами, по которым производится соединение. Дополнительно: таблицы или их индексы должны быть упорядочены. Это означает, что в них используется либо общая именованная функция секционирования, либо разные, но дающие одинаковый результат. То есть:
1. Указанные таблицы секционированы по одинаковому количеству параметров, имеющих одинаковый тип данных.
2. В указанных таблицах имеется одинаковое количество секций.
3. В указанных таблицах секции имеют одинаковые граничные значения.
При выполнении указанных условий оптимизатор запросов SQL Server проводит операцию соединения гораздо быстрее, так как в этом случае могут быть соединены сами секции. Если запрос проводит соединение двух неупорядоченных таблиц, либо таблиц, не секционированных для соединения, наличие секционирования в таблице может замедлить выполнение запроса вместо его ускорения.
Для повышения производительности операций ввода-вывода может понадобиться объединить секции в файловые группы, размещенные на разных физических дисках. В процессе сортировки данных для операций ввода-вывода в SQL Server сначала проводится сортировка данных по секциям. При такой схеме работы SQL Server может осуществлять одновременный доступ только к одному диску, что может привести к снижению производительности. Для повышения производительности рекомендуется распределить файлы данных разных секций по нескольким жестким дискам, установив при этом RAID. Таким образом, несмотря на сортировку данных по секциям, SQL Server сможет одновременно осуществлять доступ ко всем жестким дискам каждой секции. Данная конфигурация может быть применена как к секциям внутри одной файловой группы, так и к секциям, размещенным в нескольких файловых группах.
Алгоритм секционирования
Чтобы создать секционированную таблицу или индекс, необходимо выполнить следующие шаги:
1. Создайте функцию секционирования, чтобы указать, каким образом таблица или индекс, где используется эта функция, могут быть секционированы.
2. Создайте схему секционирования, чтобы указать размещение секций функцией секционирования для файловых групп.
3. Создайте таблицу или индекс с использованием схемы секционирования.
Функция секционирования указывает, как секционируется таблица или индекс. Посредством этой функции домен преобразуется в набор секций. Чтобы создать функцию секционирования, необходимо указать число секций, столбец секционирования и диапазон значений столбца секционирования для каждой секции. Обратите внимание на то, что можно указать только один столбец секционирования. Все типы данных, допустимые для использования в качестве индексных столбцов, могут использоваться как столбцы секционирования, за исключением timestamp. Не могут быть указаны типы данных ntext, text, image, xml, varchar(max), nvarchar(max) и varbinary(max). Кроме того, нельзя указывать столбцы с данными пользовательского типа и типа alias среды CLR Microsoft.NET Framework.
Функция секционирования имеет следующий синтаксис:
CREATE PARTITION FUNCTION partition_function_name (input_parameter_type)
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ([ boundary_value [,...n ] ])
[; ]
где,
input_parameter_type – тип данных столбца, используемого для секционирования;
boundary_value – граничные значения для каждой секции;
LEFT | RIGHT – указывает, к какой области интервала значений принадлежит аргумент boundary_value [,...n ] (к левой или правой).
Схема секционирования сопоставляет секции, полученные в результате использования функции секционирования, с определенным набором файловых групп.
При создании схемы секционирования пользователь определяет файловые группы, с которыми сопоставляются табличные секции, в соответствии с аргументами функции секционирования. Необходимо указать достаточно файловых групп для размещения соответствующего количества секций. Можно указать сопоставление всех секций с различными файловыми группами, сопоставление некоторых секций с одной группой файлов или сопоставление всех секций с одной файловой группой. Можно также указать дополнительные, «неназначенные» файловые группы в случае, если в дальнейшем предполагается добавить еще секции. В этом случае SQL Server помечает одну из файловых групп свойством NEXT USED. Это означает, что файловая группа будет содержать следующую добавленную секцию.
Для схемы секционирования может быть использована только одна функция секционирования. Однако функция секционирования может участвовать в двух и более схемах секционирования.
Функция создания схемы секционирования имеет синтаксис:
CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ({ file_group_name | [ PRIMARY ] } [,...n ])
[; ]
где
partition_function_name – имя функции секционирования, использующей схему секционирования;
ALL – указывает, что все секции сопоставляются с файловой группой, определяемой аргументом file_group_name, или с первичной файловой группой, если указывается [PRIMARY];
file_group_name – указывает имена файловых групп, содержащих секции, указываемые аргументом partition_function_name. Если указывается [ PRIMARY ], секция сохраняется в первичной файловой группе.