Главная » Microsoft SQL Server, Базы данных » Разделение таблиц и индексов

0

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

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

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

?               Крупная финансовая компания имеет несколько терабайтов исторических данных; при этом следует обеспечить максимальную эффективность запросов как к текущим, так и к историческим данным. Сегментирование данных по предыдущим и текущему периодам позволит наиболее часто выполняемым запросам к текущим данным обращаться к меньшей по размеру таблице.

СУБД SQL Server 2000 имела разделенные представления разделов и федера- Новинка ‘ тивные базы данных, однако их было довольно тяжело конфигурировать и ис- 2005                           пользовать. Несмотря на то что саму теорию нельзя назвать новой, ее практи

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

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

?               Операции вставки и обновления должны также вставлять и обновлять страницы индексов. Когда таблица разделена, обновляются только страницы индексов тех разделов, которые были обновлены.

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

?               Резервирование части таблицы с использованием файловых групп облегчает архивирование.

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

?               Возможно, наиболее существенным преимуществом масштабирования разделенных таблиц является то, что SQL Server может использовать параллельные процессорные операции при работе с разделенными таблицами.

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

Создание разделения таблицы в SQL Server 2005 является простейшим четырехшаговьш процессом.

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

2.              Создайте схему разделения, назначающую разделы файловым группам.

3.              Создайте таблицу с некластеризованным первичным ключом.

4.              Создайте кластеризованный индекс для таблицы, используя схему и функцию разделения. Функции и схемы разделения работают совместно для сегментирования данных (рис. 53.1).

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

Рис. 53.1. Функция разделения используется схемой разделения для помещения данных в отдельные файловые группы

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

Под функцией разделения понимается механизм определения границ разделов.

При разделении таблиц данные могут сегментироваться на основании одного столбца. Даже если таблица еще не определена, функция должна знать тип данных столбца сегментирования. Таким образом, параметром функции разделения является тип данных, который использовался для сегментирования данных. В следующем примере функция fnYears принимает тип данных datatime.

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

Границы, или диапазоны, определяются как левый или правый. Левая граница подразумевает, что данные, равные ей, включаются в раздел, находящийся слева от нее. Например, граница ’31/12/2004′ создает два раздела. Нижний раздел будет включать данные, относящиеся к данным, меньшим или равным ’31/12/2004′, а верхний раздел — данные, относящиеся к более поздним датам.

Под правой границей подразумевается то, что граница относится к верхнему разделу. Например, для отделения нового, 2005 года, правый диапазон должен содержать границу ‘ 1/1/2005′. Все значения, меньшие этой даты, переходят в левый, или нижний, раздел. Все данные с датами, равными или большими заданной, переходят в следующий раздел. Следующие две функции используют левую и правую границу, формируя один и тот же результат:

CREATE PARTITION FUNCTION fnyears(DateTime)

AS RANGE LEFT FOR VALUES

( ’31/12/2001′ , ’31/12/2 002 ‘ , ’31/12/2 003 1 , ’31/12/2 004 ‘) ;

И

CREATE PARTITION FUNCTION fnYearsRT(DateTime)

AS RANGE RIGHT FOR VALUES

(‘1/1/20021, ‘1/1/2003′, ‘1/1/2004′, ‘1/1/2005′);

Обе эти функции создают четыре определенные границы и пятую, неопределенную.

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

Существуют три представления каталогов, которые отображают информацию о функции разделения: syspartition_functions, syspartition_function_ range_values и syspartition_parameters.

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

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

CREATE PARTITION SCHEME psYearsAll AS PARTITION pfYearsRT ALL TO ([Primary]);

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

CREATE PARTITION SCHEME psYearsFiles AS PARTITION pfYearsRT

TO (PartOl, Part02, Part03, Part04, Part05);

Функции и схемы разделения должны создаваться с помощью программного кода T-SQL, однако после создания их можно увидеть в окне Object Explorer утилиты Management Studio под узлом базы данных Storage (рис. 53.2).

| Для просмотра информации о схеме разделения программным путем выполни- сте запрос к базе syspartition_schemes.

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

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

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

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

CREATE TABLE dboWorkOrder (

WorkOrderlD INT NOT NULL PRIMARY KEY NONCLUSTERED,

ProductID INT NOT NULL,

OrderQty INT NOT NULL,

StockerQty INT NOT NULL,

ScappedQty INT NOT NULL,

Рис. 53.2. Конфигурацию разделов можно увидеть в Object Explorer

Puc. 53.3. Отчет Disk Usage на странице Summary утилиты Management Studio для базы данных Part it ionDemo отображает информацию о каждом из разделов

StartDate DATETIME NOT NULL,

EndDate DATETIME NOT NULL,

DueDate DATETIME NOT NULL,

ScapReason INT NULL,

ModifiedDate DATETIME NOT NULL ) ;

CREATE CLUSTERED INDEX ix_WorkORder_DueDate ON dbo.WorkOrder (DueDate)

ON psYearsAll(DueDate);

Следующий сценарий вставляет 7259100 строк в таблицу WorkOrder за 2 минуты 42 секунды, что подтверждается на странице Summary базы данных:

DECLARE @Counter INT;

SET @Counter = 0;

WHILE @Counter < 100 BEGIN

SET @Counter = @Counter + 1;

INSERT dbo.WorkOrder (ProductID, OrderQty, StockedQty,

ScrappedQty, StartDate, EndDate, DueDate, ScrapReasonID, ModifiedDate)

SELECT ProductID, OrderQty, StockedQty, ScrappedQty,

StartDate, EndDate, DueDate, ScrapReasonID,

ModifiedDate FROM AdventureWorks.Production.WorkOrder;

END;

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

Рис. 53.4. В окне Object Dependencies видно, что таблица WorkOrder использует схему разделения psYearAll, которая в свою очередь использует функцию pfYerarsRT

Для просмотра информации об использовании разделов взгляните на представления syspartitions и syspartition_counts.

Выполнение запросов к разделенным таблицам

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

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

SELECT $PARTITION.pfYearsRT(DueDate) AS Partition,

COUNT(*) AS Count FROM WorkOrder

GROUP BY $PARTITION.pfYearsRT(DueDate)

ORDER BY Partition;

Будет получен следующий результат:

Partition Count

1               703900

2               1821200

3               2697100

4               2036900

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

SELECT WorkOrderlD,ProductID, OrderQty, StockedQty, ScrappedQty FROM dbo.WorkOrder WHERE year(DueDate) = ‘2002’;

Изменение разделенных таблиц

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

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

Слияние разделов

Модификаторы MERGE и SPLIT могут изменить структуру разделов таблицы. Команда ALTER PARTITION. . . MERGE RANGE позволяет эффективно удалить одну из границ из функции разделения и объединить два раздела. Например, удаление границы между 2003 и

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

Puc. 53.5. Логическая операция сканирования кластеризованного индекса содержит новый параметр Partition ID

ALTER PARTITION FUNCTION pfYearsRT()

MERGE RANGE (‘1/1/2004′);

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

;      Если несколько таблиц совместно используют одну и ту же модифицируемую

На заметку схему и функцию разделения, эти изменения повлияют на все таблицы.

Разбиение разделов

Для разбиения существующего раздела вначале нужно назначить файловую группу новому разделу в схеме, используя команду ALTER PARTITION. . .NEXT USED. После этого может быть модифицирована функция разделения. Команда ALTER PARTITION. . .SPLIT RANGE вставляет новую границу в функцию разделения. Именно команда изменения функции разделения фактически выполняет всю работу.

В следующем примере сегмент данных о рабочих заказах за 2003-2004 годы разбивается на два раздела. Новый раздел будет содержать данные только за июль 2004 года — последний месяц с данными в учебной базе данных AdventureWorks:

ALTER PARTITION SCHEME psYearsFiles NEXT USED [Primary];

ALTER PARTITION FUNCTION pfYearsRT()

SPLIT RANGE ( ‘ 1/7/2004′) ;

Переключение таблиц

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

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

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

?               Исходная разделенная таблица не может быть назначением внешнего ключа.

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

?               Новая таблица должна иметь ограничение проверки, ограничивающее диапазон данных нового раздела, таким образом, СУБД SQL Server не должна перепроверять диапазон данных.

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

?               Раздел назначения должен быть пустым.

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

Выделение новой таблицы

Таблица WorkOrderNEW удовлетворяет вышеописанным критериям и будет хранить данные за август 2004 года из базы данных Adventureworks:

CREATE TABLE dbo.WorkOrderNEW (

WorkOrderID INT IDENTITY NOT NULL,

ProductID INT NOT NULL,

OrderQty INT NOT NULL,

StockedQty INT NOT NULL,

ScrappedQty INT NOT NULL,

StartDate DATETIME NOT NULL,

EndDate DATETIME NOT NULL,

DueDate DATETIME NOT NULL,

ScrapReasonID INT NULL,

ModifiedDate DATETIME NOT NULL )

ON Part05;

Рис. 53.6. Переключение таблицы фактически является изменением внутренних метаданных базы данных, преобразующим обособленную таблицу в один из разделов

Индексы, идентичные предыдущей таблице, будут созданы в разделенной таблице: ALTER TABLE dbo.WorkOrderNEW ADD CONSTRAINT WorkOrderNEWPK

PRIMARY KEY NONCLUSTERED (WorkOrderlD, DueDate)

go

CREATE CLUSTERED INDEX ix_WorkOrderNEW_DueDate ON dbo.WorkOrderNEW (DueDate)

Добавляем обязательное ограничение:

ALTER TABLE dbo.WorkOrderNEW ADD CONSTRAINT WONewPT

CHECK (DueDate BETWEEN ‘1/8/2004′ AND ’31/8/2004′);

Теперь импоршруем новые данные из Ad venture works, используя данные за январь 2004 года: INSERT dbo.WorkOrderNEW (ProductID, OrderQty, StockedQty,

ScrappedQty, StartDate, EndDate, DueDate, ScrapReasonID,

ModifiedDate)

SELECT

ProductID, OrderQty, StockedQty, ScrappedQty,

DATEADD(mm,7,StartDate), DATEADD(mm,7,EndDate),

DATEADD(mm,7,DueDate), ScrapReasonID,

DATEADD(mm,7,ModifiedDate)

FROM AdventureWorks.Production.WorkOrder

WHERE DueDate BETWEEN ‘1/1/2004′ and ’31/1/2004′;

Новая таблица теперь имеет 3158 строк.

Переключение в разделенную таблицу

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

ALTER TABLE dbo.WorkOrder

DROP CONSTRAINT WorkOrderPK ALTER TABLE dbo.WorkOrder ADD CONSTRAINT WorkOrderPK

PRIMARY KEY NONCLUSTERED (WorkORderlD,DueDate)

ON psYearsAll(DueDate);

Далее разделенной таблице нужен свободный раздел.

ALTER PARTITION SCHEME psYearsFiles NEXT USED [Primary]

ALTER PARTITION FUNCTION pfYearsRT()

SPLIT RANGE (’1/8/2004′)

Осуществление переключения

Инструкция ALTER TABLE . . . SWITCH переместит новую таблицу в заданный раздел. Чтобы определить пустой раздел назначения, выберите на странице Summary базы данных отчет Disk Usage.

ALTER TABLE WorkOrderNEW

SWITCH TO WorkOrder PARTITION 5

Обратное переключение

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

ALTER TABLE

SWITCH PARITION 1 to WorkOrderArchive

Подвижные разделы

Если проявить немного воображения, то технологию создания и слияния существующих разделов можно применить для создания подвижных разделов. Подвижные разделы полезны для функций разделения, основанных на времени, например для создания разделов для каждого месяца. Каждый месяц подвижный раздел расширяется на новый месяц. Для создания 13-месячного подвижного раздела выполняйте каждый месяц следующие действия.

1.              Добавьте новую границу.

2.              Укажите границе на следующую используемую файловую группу.

3.              Объедините два последних раздела, чтобы объединить все данные.

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

Индексация разделенных таблиц

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

Создание разделенных индексов

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

CREATE INDEX WorkOrder_ProductID ON WorkOrder (ProductID, DueDate)

ON psYearsFiles(DueDate);

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

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

ALTER INDEX WorkOrder_ProductID ON dbo.WorkOrder REBUILD PARTITION = 5

Так как разделение доступно только в редакции Enterprise Edition, в этой же редакции доступна и интерактивная перестройка индексов.

Удаление разделения

Для удаления разделения любой таблицы нужно удалить кластеризованный индекс и создать новый, но уже без использования предложения ON. При удалении кластеризованного индекса следует добавить параметр MOVE ТО, чтобы консолидировать данные в заданную файловую группу, ликвидируя таким образом разделение таблицы:

DROP INDEX ix_WorkOrder_DueDate ON dbo.Workorder WITH (MOVE TO [Primary]);

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии