Хранение данных: горизонтальное разделение таблицы фактов

Мишель Пуле

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

Горизонтально разделенная на секции таблица - это таблица, в которой наборы строк разделены на непересекающиеся секции. Каждая секция в горизонтально разделенной таблице определена по диапазону значений, таких как время, географическая область или фамилии клиента. Можно предусмотреть хранение каждой секции на различных жестких дисках для увеличения производительности запроса. Хотя горизонтально разделить таблицу на секции нетрудно, потребуется некоторое техническое проектирование, потому что в данном случае администратор имеет дело с большим количеством данных. Давайте исследуем, когда и почему нужно горизонтально делить таблицу фактов на секции, и как создать функцию разделения таблицы, используя встроенные функциональные возможности SQL Server 2005.

Почему таблицу фактов разделяют на секции?

Большими таблицами (таблицами с сотнями миллионов строк) может быть трудно управлять. В транзакционной базе данных ассоциативные таблицы (то есть те таблицы, которые используют отношение "многие-ко-многим") часто имеют наибольшее количество строк. В моделировании размерностей таблица фактов - эквивалент ассоциативной таблицы. Подобно ассоциативной таблице в транзакционной базе данных, в таблице фактов чаще бывает намного больше строк, чем связанных размерностей, возможно, по количеству столько же, сколько строк получается в результате произведения (числа строк в размерности 1) x (число строк в размерности 2) x (# число строк в размерности n).

Разделим эти громадные таблицы на управляемые части. Если временные окна для обслуживания базы данных сжимаются, или количество обрабатываемых данных растет, можно разделить таблицу на секции и выполнять задачи, такие как резервное копирование и восстановление, по секциям или разделам. Или при контроле непротиворечивости базы данных (DBCC) для того чтобы обновить статистику таблицы по секциям вместо статистики по полной таблице. SQL Server 2005 интерпретирует многие секции разделенной таблицы как единый логический объект, и многочисленные секции для пользователей выглядят как единая таблица.

Для горизонтального разделения таблиц существует несколько причин:

управлять проще, когда каждая секция размещена в отдельном хранилище и увеличение числа операций чтения/ записи способствует быстрому выполнению запроса;
удобнее резервировать и восстанавливать секционные данные, индексы могут быть перестроены и реорганизованы по секциям. А сами индексы могут быть разделены по секциям;
можно использовать прямые запросы, которые включают условие WHERE, содержащее поле разбиения или индексное поле в соответствующем разделе;
можно уменьшить диапазон блокировок и непроизводственные издержки менеджера блокировок, потому что блокировка ограничена разделом;
можно без труда объединять или разбивать на разделы, если разделение проходит по одной и той же группе файлов.

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

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

Создаем функцию разделения на секции

Чтобы разбить таблицу на разделы, нужно использовать функцию, состоящую из столбца разделения и ограничений. Чтобы создать функцию разбиения на разделы таблицы фактов SALES, которая показана на рисунке 1, выполните команду

CREATE PARTITION FUNCTION
MyPartitionFunctionLeft
(datetime)
AS RANGE LEFT
FOR VALUES ("1/01/2003',
"1/01/2005', "1/01/2007)

MyPartitionFunctionLeft - это название функции разделения, datetime - тип данных столбца разделения, а RANGE LEFT указывает, как делить значения данных, которые подвязаны к датам FOR VALUES.

Для задания ограничений есть два пути: RANGE LEFT или RANGE. RIGHT.Условие RANGE LEFT делит данные от самого низкого значения до самой высокой величины (то есть по возрастанию). Условие RANGE RIGHT делит данные от самого высокого значения до самого низкого (то есть по убыванию).

Столбец разделения часто имеет тип данных datetime вроде столбца Date_of_Event (показанного на рисунке 1). Разделить записи типа datetime на непересекающиеся группы просто. Например, если правила бизнеса и известные рабочие запросы указывают, что разделение таблицы на секции по датам продаж разумно, тогда можно делить данные на двухлетние группы, как это сделано в предыдущей команде с функцией разделения. Разделение на секции RANGE LEFT делит данные в диапазонах значений, показанных на Рисунке 2.

Если дата продажи была 23 июня 2004, запись найдется в разделе 2 (P2). Если захотите создать функцию разделения на секции с условием RANGE RIGHT, выполните команду

CREATE PARTITION FUNCTION
MyPartitionFunction datetime
AS RANGE RIGHT
FOR VALUES ("1/01/2003',
"1/01/2005', "1/01/2007)

Секционирование RANGE RIGHT разбивает данные на диапазоны значений, показанные на Рисунке 3. Я рекомендую ради соответствия и простоты запросов выбирать одно описание диапазона (то есть RANGE LEFT или RANGE RIGHT) и следовать ему во всех секционированных таблицах.

Каждая область значений в секции имеет границы, которые определены в операторе FOR VALUES. Обратите внимание, что если для граничных величин используется тип данных datetime и если компания имеет офис в Европе, то нужно остановиться на международном стандарте для datetime, так, чтобы это было унифицировано по всей компании. SQL Server предполагает, что язык для сеанса us_english задается по умолчанию. Если это не тот случай, можно создать определяемую пользователем функцию UDF, которая преобразует различные форматы даты в us_english, и ссылку на UDF в условии FOR VALUES. Нельзя использовать константы в UDF в условии FOR VALUES; можно ссылаться на переменные, функции и UDF.

Создаем схему разделения

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

CREATE PARTITION SCHEME
MyPartitionScheme
AS MyPartitionFunction
TO (MyFilegroup1, MyFilegroup2,
MyFilegroup3, MyFilegroup4,
MyFilegroup5)

MyPartitionScheme - название схемы секционирования, а имя MyPartitionFunction обозначает функцию разделения на секции. Эта команда отображает граничные величины в секции, которые связаны с одной или несколькими группами файлов. Строки с данными со значениями Date_of_Event datetime до 1/01/03 связаны с MyFilegroup1. Строки со значениями datetime больше или равны 1/01/03 и до 1/01/05 назначены MyFilegroup2. Строки со значениями datetime более или равны 1/01/05 и до момента 1/01/07 связаны с MyFilegroup3. Все остальные строки со значениями datetime более или равны 1/01/07 связаны с MyFilegroup4.

Для каждого набора граничных значений (помните, граничные значения находятся в условии FOR VALUES функции разделения) будет (количество граничных значений + 1) секций. Предыдущее предложение CREATE PARTITION SCHEME включает три ограничения и четыре секции. Независимо от того, созданы ли секции с RANGE RIGHT или RANGE LEFT, здесь будет всегда (число ограничений + 1) секций, вплоть до 1000 секций на таблицу. Так почему в нашем примере пять групп файлов вместо четырех? Ведь оператор функции разделения имеет только три ограничения. Пятая группа файлов - это дополнительная, "для последующего использования" группа файлов. Давайте рассмотрим, как эта группа используется и насколько важно ее присутствие в схеме секционирования.

В примере с CREATE PARTITION FUNCTION последнее граничное значение - это 1/01/2007, строки со значением Date_of_Event больше или равные 1/01/2007 будут сохранены в секции 4 (P4). Когда пройдет дата 1/01/2009, потребуется создать новую секцию, чтобы поддержать схему хранения, которую вы установили. Если ваш оригинальный оператор CREATE PARTITION SCHEME не включает следующую используемую группу файлов, вы не сможете разбить P4 на секцию P4, которая будет содержать данные с 2007 по конец 2008 года, и секцию 5 (P5), которая будет содержать данные с 1/01/2009 и далее. Если ваш план включает периодическое создание новых разделов, для того чтобы содержать новые данные, как в этом примере, убедитесь, что вы включили следующую группу файлов "для последующего использования" в предложение CREATE PARTITION SCHEME.

Необязательно связывать одну секцию с одной группой файлов, как показано на Рисунке 4.

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

Создание схемы секционирования может быть наиболее важным шагов в процессе разделения на секции. В будущем может появиться потребность объединить данные из двух смежных секций в одну, добавить ограничение к существующей секции или переместить данные из заполненной секции в пустую. Для того чтобы выполнить эти операции, администратор должен осуществить некоторое планирование и создать схему секционирования так, чтобы эта секция обеспечила такие действия. SQL Server 2005 Books Online (BOL) предусматривает несколько хороших ресурсов для планирования, включая "Planning Guidelines for Partitioned Tables and Indexes" (msdn2.microsoft.com/en-us/library/ms180767.aspx))

Создание секционированной таблицы

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

CREATE TABLE SALESFact
(SalesFact_KEY bigint identity
(1, 1) primary not clustered NOT
NULL,
... all the other columns in the
table, ending up with the
partitioning column... ,
Date_of_Event datetime
NOT NULL)
ON MyPartitionScheme (Date_of_Event)

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

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

ALTER PARTITION FUNCTION
MyPartitionFunction()
MERGE RANGE ("1/01/2003')

Эта команда будет объединять секции следующим образом: секция 1 (P1) вольется в P2. Это означает, что P2 будет содержать все строки со значением Date_of_Event до 1/01/05. Внутри (то есть в системной таблице sys.partitions) секции будут перенумерованы, начиная с единицы (не с нуля). Секции P1 и P2 станут P1, секция P3 станет P2 и P4 станет P3. Я рекомендую ознакомиться с DDL, прежде чем объединять секции, потому что потенциально можно остановить все операции на время выполнения задания, если не быть осторожным при выполнении объединений. Эрик Хансон, руководитель проекта по обработке запросов в SQL Server Relational Engine Team в Microsoft, рекомендует выполнять разбиение и объединение только на пустых секциях, даже если для этого придется временно освободить секции.

Как просто управлять громадными таблицами

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