В прошлом администраторам баз
данных, управляющим таблицами с миллионами строк данных, приходилось
создавать множественные таблицы. Произведя секционирование таблиц,
администратору приходилось привязывать
эти таблицы друг к другу во время выполнения
многих запросов. Привязывание секций друг к другу требовало создания
секционированного представления или хранимой процедуры-оболочки,
определявшей местонахождение данных и выполнявшей другую хранимую
процедуру, которая работала только с секциями, нужными для
возвращения набора данных.
Хотя эти методы позволяли достичь требуемого
результата, они были весьма обременительны. Операции
администрирования нескольких таблиц и их индексов, а также методы,
используемые для обратного привязывания таблиц, зачастую становились
источником затруднений при администрировании и обслуживании. Кроме
того, создание нескольких таблиц для секционирования данных
приводило к отсутствию гибкости, так как в хранимых процедурах,
заданиях обслуживания, заданиях служб преобразования данных (DTS),
коде приложения и других процессах приходилось учитывать характер
секционирования. Поэтому для обеспечения возможности добавления или
сброса этих квазисекций без изменения кода такие элементы обычно
создавались нединамическими способами и, следовательно, имели низкую
эффективность.
Выпуски SQL Server™ 2005 Enterprise Edition и
Developer Edition позволяют секционировать большое количество
данных, хранимых в единой таблице, на несколько секций меньшего
размера, поддающихся более эффективному управлению и обслуживанию.
Возможность создания сегментов данных, доступ к которым
осуществляется через единую точку входа, устраняет многие проблемы
администрирования, которые возникали при прежнем способе работы.
Благодаря использованию единой точки входа (имени таблицы или имена
индекса) множественность сегментов данных становится скрытой от кода
приложения, и администратор или разработчик может выполнять нужные
изменения секций без необходимости изменения кодовой базы.
Таким образом, можно создавать множественные
секции, перемещать их, сбрасывать старые секции и даже изменять
способ секционирования данных, не внося каких-либо изменений в код
приложения. Код приложения просто продолжает обращаться к той же
самой базовой таблице или имени индекса. Это позволяет также
уменьшить количество данных, хранимых в отдельных индексах, что, в
свою очередь, снижает время обслуживания этих индексов. Кроме того,
можно загружать данные в пустые секции, что позволяет увеличить
скорость загрузки данных.
Технически каждая таблица в сервере SQL Server
2005 секционирована, т. е. имеет по крайней мере одну секцию. Кроме
того, сервер SQL Server 2005 позволяет администраторам баз данных
создавать дополнительные секции в каждой таблице. Секционирование
таблиц и индексов задается жестко на уровне строк (секционирование
по столбцам не допускается) и позволяет осуществлять доступ через
единую точку входа (имя таблицы или имя индекса) таким образом, что
в коде приложения не требуется знать число секций, находящихся за
точкой входа. Секционирование может осуществляться на базовой
таблице, а также на связанных с ней индексах.
Создание секционированных
таблиц
Для создания таблицы, которая способна вырастать
в размерах и задействовать дополнительные секции помимо исходной,
применяются функции секционирования и схемы секционирования. Именно
эти объекты позволяют разделять данные на конкретные сегменты и
управлять их местоположением в рамках конструкции хранилища.
Например, можно распределить данные по нескольким дисковым массивам
в зависимости от возраста данных или других отличительных признаков.
Следует отметить, что таблицу можно секционировать по одному из ее
столбцов, и каждая секция должна содержать данные, которые не могут
быть помещены в другие секции.
Функции секционирования. При секционировании
таблицы в первую очередь нужно определить принцип, по которому
данные будут разделяться на сегменты. Для сопоставления отдельных
строк данных с разными сегментами служит функция секционирования.
Строки данных могут сопоставляться по столбцу любого типа, кроме
следующих: text, ntext, image, xml, timestamp, varchar(max),
nvarchar(max), varbinary(max), псевдонимы типов данных и
пользовательские типы данных среды CLR. Однако функция
секционирования должна помещать строку данных только в одну секцию
таблицы; иными словами, в результате применения функции одна и та же
строка не может принадлежать нескольким секциям одновременно.
Чтобы секционировать таблицу, в ней необходимо
создать столбец секционирования. Этот столбец секционирования можно
создать в схеме таблицы в момент создания таблицы, либо добавить
позднее путем модификации таблицы. Столбец может принимать значения
NULL, но все строки, содержащие значения NULL, будут по умолчанию
помещаться в самую левую секцию таблицы. Этого можно избежать,
указав при создании функции секционирования, что значения NULL
должны помещаться в самую правую секцию таблицы. Выбор левой или
правой секций – важное решение проектирования, проявляющееся при
изменении схемы секционирования, добавлении дополнительных секций
или удалении существующих.
При создании функции секционирования можно
выбрать функции LEFT или RIGHT. Разница между секциями LEFT и RIGHT
состоит в том, где в схеме секционирования будут размещаться
граничные значения. При использовании секций LEFT (этот вариант
выбирается по умолчанию) граничное значение включается в секцию,
тогда как при использовании секций RIGHT граничное значение
размещается в следующей секции.
Чтобы лучше понять этот принцип, рассмотрим
следующие примеры секций LEFT и RIGHT:
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
В первой функции (Left_Partition) значения 1, 10
и 100 размещаются соответственно в первой, второй и третьей секциях.
Во второй функции (Right_Partition) эти значения размещаются во
второй, третьей и четвертой секциях.
При создании секционированной таблицы важно,
чтобы секции получились как можно более равными. Это поможет понять,
сколько места потребуется для каждой секции. Использование
параметров LEFT и RIGHT определяет, куда будут размещаться данные,
что, в свою очередь, задает размер секции и размеры индексов,
созданных на ней.
Определить номер секции, в которую попадет то или
иное значение, можно с помощью функции $PARTITION, как показано
ниже:
SELECT .Left_Partition (10)
SELECT .Right_Partition (10)
Первая инструкция SELECT возвращает значение 2,
вторая – значение 3.
Схемы секционирования. После создания функции и
выбора способа разделения данных между секциями следует решить, где
будут создаваться отдельные секции в дисковой подсистеме. Для
создания этой дисковой структуры используются схемы секционирования.
Схемы секционирования управляют тем, как отдельные секции хранятся
на диске, путем использования файловых групп для размещения каждой
секции на дисковой подсистеме. Схемы секционирования можно настроить
таким образом, чтобы все секции располагались в единой файловой
группе, чтобы каждая секция располагалась в своей файловой группе
или чтобы несколько секций использовали общие файловые группы.
Последний способ дает администратору базы данных широкие возможности
рассредоточения операций ввода-вывода на диске.
На рис. 1 показаны некоторые из способов, позволяющих
присвоить схеме секционирования одну или несколько файловых групп.
Следует помнить, что файловые группы, используемые схемой
секционирования, должны существовать в базе данных перед созданием
схемы.
Если создать шаблоны функций секционирования,
показанные на рис. 1, и использовать эту схему секционирования
для создания таблицы, то впоследствии можно будет определить, где
будут размещаться отдельные строки данных в секционированных
таблицах. Это позволит впоследствии просматривать распределение
строк данных, вставленных в секционированную таблицу. Обеспечивающий
это программный код будет выглядеть, как показано на рис. 2.
Изменение секционированных таблиц
Несмотря на тщательное планирование, иногда
приходится вносить изменения в секционированные таблицы после того,
как они созданы и наполнены данными. Схема секционирования может
работать, как задумано, но администратору базы данных может,
например, понадобиться добавить новые секции при накоплении новых
данных, или же может потребоваться за один раз сбросить большое
количество секционированных данных. К счастью, секционированные
таблицы и базовые структуры секционирования позволяют вносить
изменения после того, как таблица введена в эксплуатацию и наполнена
данными.
Добавление секций. Многие планы секционирования
включают возможность добавления новых секций в будущем. Этот момент
времени может задаваться определенной датой или зависеть от
значения, находящегося в столбце инкрементальных идентификаторов.
Однако если добавление новых секций в секционированную таблицу не
было запланировано заранее, это можно сделать в любой момент.
Рассмотрим таблицу, создание которой показано на рис. 2. Добавить в нее новую секцию, которая будет
содержать значения больше 500, можно следующим образом:
--Determine where values live before new partition
SELECT .Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT .Left_Partition (501) --should return a value of 5
Возможность добавления новых секций обеспечивает
высокую гибкость. На рис. 3 показано, как добавить секцию слева от
функции. В данном случае необходимо определить в схеме
секционирования, где будет размещаться новая секция, так как уже
исчерпаны все файловые группы, созданные при первом построении схемы
секционирования. Хотя для всех секций используется файловая группа
PRIMARY, в схеме секционирования необходимо явно определить, что эта
файловая группа должна использоваться и для новой секции.
Слияние двух секций. Сервер SQL Server позволяет
сбрасывать отдельные секции из таблицы, при этом сохраняя данные.
Таким способом можно выполнить слияние старых активных данных с
архивированными данными или уменьшить количество имеющихся секций
для упрощения администрирования секционированной таблицы. С помощью
слияния секций можно также перемещать данные из одной файловой
группы в другую для освобождения дискового пространства на
определенных дисковых массивах. В программном коде на рис. 4 показано, как можно переместить данные из
одной секции в другую на одной и той же файловой группе.
Перемещение таблицы с одной секцией в
секционированную таблицу. Во время выполнения процедур загрузки
часто требуется загружать большие партии данных в базу данных, а
затем изменять или агрегировать эти данные, прежде чем переместить
их в результирующую таблицу. Механизмы секционирования в сервере SQL
Server 2005 позволяют перемещать таблицу с одной секцией в таблицу с
несколькими секциями. Это означает, что можно загрузить данные в
одну загрузочную таблицу, внести необходимые изменения в эти данные,
а затем переместить таблицу целиком в другую существующую таблицу,
избегая при этом необходимости перемещать строки данных по одной. На
этом уровне секционирования не происходит изменения базовых структур
секционирования. В данном случае происходит изменение
секционированной таблицы. На рис. 5 показан пример программного кода,
выполняющего описанные действия.
Перемещение секции из одной таблицы в другую.
Одной из типичных задач администрирования является перемещение
старых данных в отдельные архивные таблицы. Процесс архивации обычно
требует серии инструкций, которые приводят к дополнительному
потреблению ресурсов в журналах транзакций. Чтобы легко архивировать
большое количество данных без лишних издержек, связанных с журналом
транзакций, можно воспользоваться переводом принадлежности секции с
одной таблицы на другую. Эта возможность позволяет администратору
базы данных перемещать сегменты старых данных из активных таблиц в
архивные. Поскольку сами данные при этом в действительности не
перемещаются, этот способ требует значительно меньше времени, чем
при перемещении строк данных по одной. На рис. 6 показано, как можно это сделать.
Использование единичной секции для создания новой
таблицы. Единичную секцию можно переместить из существующей
секционированной таблицы в пустую несекционированную таблицу. В
результате этого администратор базы данных получает возможность
выполнить обслуживание индексов на этой единичной секции или легко
сбросить большое количество данных без необходимости регистрировать
процесс удаления в журнале. На рис. 7 показано, как переместить секцию в пустую
таблицу и затем сбросить данные с помощью этой новой
таблицы.
Секционированные индексы
Вместе с возможностью секционирования данных
таблицы появляется и возможность создавать секционированные индексы.
Это позволяет администратору базы данных проектировать структуру
индекса на основе разделенных данных, а не на основе всех данных
таблицы. Создание секционированных индексов влечет за собой создание
отдельных сбалансированных деревьев на секционированных индексах. В
результате разделения индексов создаются индексы меньшего размера, и
обработчику хранилища становится проще их обслуживать во время
изменения, добавления и удаления данных. Администратор базы данных
также может обслуживать эти уменьшенные индексы по отдельности, что
дает возможность более эффективно обслуживать индексы на больших
наборах данных.
Создание секционированных индексов. При создании
секционированных индексов можно создавать выровненные или
невыровненные индексы. Выровненные индексы подразумевают прямую
связь с секционированными данными. (В случае с невыровненными
индексами выбирается другая схема секционирования.)
Из этих двух методов предпочтителен выровненный
индекс, который выбирается автоматически, если после создания
секционированной таблицы индексы создаются без указания другой схемы
секционирования. Использование выровненных индексов предоставляет
необходимую гибкость для создания дополнительных секций в таблице, а
также позволяет переводить принадлежность той или иной секции на
другую таблицу. Наличие этих возможностей – основная причина, по
которой администраторы баз данных сразу создают секционированные
таблицы. Для решения большинства задач, связанных с
секционированием, достаточно использовать для индексов схему
секционирования таблицы.
Индексы на таблицах можно создавать таким
образом, чтобы данные в индексе не были выровнены с данными в
таблице. Если данные хранятся в секционированной таблице, это
позволяет соединять данные различными способами (секционированные
данные могут эффективно соединяться с другими секционированными
данными при помощи оптимизатора запросов). Как вариант, можно
сделать это с несекционированной таблицей, что позволит создать
секционированный индекс (на таблице с единичной секцией) для
упрощения обслуживания индекса.
Программный код на рис. 8 создает секционированный некластеризованный
индекс на секционированной таблице. Некластеризованный индекс
выравнивается с таблицей; в качестве некластеризованного ключа
индекса используется столбец секционирования таблицы.
Программный код на рис. 9 создает невыровненный некластеризованный
индекс на секционированной таблице. В этом некластеризованном
индексе в качестве ключа индекса используются разные столбцы; этот
ключ может использоваться в упорядоченных объединениях с другими
секционированными таблицами.
Обслуживание секционированных индексов. В прошлом
обслуживание индексов для больших таблиц, содержавших миллионы или
даже миллиарды строк данных, часто занимало у администраторов баз
данных непозволительно много времени. Обслуживание зачастую
оставалось невыполненным вследствие того, что во время перестроения
индекса данные блокировались. Используя сервер SQL Server 2005,
администратор базы данных может выполнить обслуживание индекса в
оперативном режиме, не блокируя базовую таблицу на длительный период
времени. Но даже этот подход, позволяющий пользователям работать с
данными во время обслуживания индекса, все равно может замедлить
работу системы, поскольку подразумевает использование определенного
количества ресурсов. Более эффективный подход – секционировать
индексы на меньшие сегменты и выполнять обслуживание индексов на
этих уменьшенных секциях. Например, чтобы выполнить обслуживание
индекса для секции с одним индексом, можно просто добавить
нижеприведенный отрывок кода к концу кода, приведенного на рис. 8.
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
Следует учесть, что обслуживание индекса для
секций с одним индексом должно выполняться в автономном режиме,
поэтому оно может послужить причиной блокировки таблицы во время
обслуживания индекса. Во избежание этого следует переместить
единичную секцию в отдельную секцию, выполнить обслуживание индекса,
а затем переместить секцию обратно в главную таблицу. Этот процесс
вызовет некоторое снижение производительности во время обратного
перемещения секции в таблицу и обновления кластеризованного индекса,
но это приводит к меньшим затруднениям, чем блокировка всей таблицы,
и требует меньше системных ресурсов.
Заключение
Итак, секционирование таблиц в сервере SQL Server
2005 обеспечивает значительно более высокую гибкость хранения и
обслуживания данных в больших таблицах, не требуя доработки кода
приложения или процессов сервера SQL Server. Благодаря этим
возможностям сервер SQL Server зарекомендовал себя как платформа,
отвечающая задачам критически важных баз данных на уровне
предприятий.
