Поділ таблиць та індексів

Розділити таблицю – це означає розбити її на два або більше невеликих сегмента, грунтуючись на діапазонах даних Поділ найбільш ефективно, коли його ключем є стовпець, який найчастіше використовують для відбору діапазону даних З цієї причини велика ймовірність того, що запити будуть звертатися тільки до одного з сегментів даних Наведемо приклади

■ Організація має представників у пяти різних регіонах поділ таблиці по регіонах дозволяє запитам кожного з представників звертатися тільки до власного розділу

■ Велика виробнича компанія має велику таблицю реєстрації дій Її краще розбити на кілька розділів, по одному для кожного з підрозділів При цьому кожне спеціалізований додаток буде мати тенденцію звертатися тільки до власних даних, які знаходяться в одному розділі

■ Велика фінансова компанія має кілька терабайтов історичних даних при цьому слід забезпечити максимальну ефективність запитів як до поточних, так і до історичних даних Сегментування даних по попереднім і поточним періодам дозволить найбільш часто виконуваних запитам до поточних даними звертатися до меншої за розміром таблиці

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

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

Поділ таблиць зменшує повний розмір кластеризованих і некластерізованних індексів збалансованого дерева, що надає ряд переваг в області масштабованості

■ Операції вставки і оновлення повинні також вставляти та оновлювати сторінки індексів Коли таблиця розділена, оновлюються лише сторінки індексів тих розділів, які були оновлені

■ Обслуговування індексів може бути досить витратною операцією Індекси розділу можуть бути значно менше, що істотно скоротить вплив реіндексацію і дефрагментації індексу на продуктивність

■ Резервування частини таблиці з використанням файлових груп полегшує архівування

■ Малий розмір збалансованих дерев індексів прискорює пошук індексів

■ Можливо, найбільш істотною перевагою масштабування розділених таблиць є те, що SQL Server може використовувати паралельні процесорні операції при роботі з розділеними таблицями

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

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

1 Створіть функцію поділу, визначальну характер сегментування даних

2 Створіть схему поділу, призначає розділи файловим групам

3 Створіть таблицю з некластерізованний первинним ключем

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

У наведеному прикладі використана база даних PartitionDemo, яка має три файлові групи: Primary, Parti і Part2 Відповідний сценарій ви можете завантажити з сайту даної книги

Рис 531 Функція поділу використовується схемою поділу для приміщення даних в окремі файлові групи

Створення функції поділу

Під функцією поділу розуміється механізм визначення меж розділів

При поділі таблиць дані можуть сегментуватися на підставі одного стовпця Навіть якщо таблиця ще не визначена, функція повинна знати тип даних стовпця сегментування Таким чином, параметром функції поділу є тип даних, який використовувався для сегментування даних У наступному прикладі функція 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 (рис 532)

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

Створення розділеної таблиці

Після того як створені функція і схема поділу, створення таблиці не складає труднощів Розділена таблиця повинна створюватися з некластерізованний первинним ключем Після цього додавання в таблицю кластеризованого індексу фактично сегментирует таблицю на підставі схеми поділу (рис 533) Зверніть увагу на те, що на панелі властивостей таблиці також відображається схема поділу, використовувана таблицею

Функції та схеми поділу не мають власників, тому при зверненні до них не потрібно використовувати чотирьохкомпонентну імя або згадувати в імені власника схеми

Наступна таблиця аналогічна таблиці робочих замовлень бази даних 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,

Рис 532 Конфігурацію розділів можна побачити в Object Explorer

Puc 533 Звіт 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 dboWorkOrder (DueDate)

ON psYearsAll(DueDate)

Наступний сценарій вставляє 7259100 рядків у таблицю WorkOrder за 2 хвилини 42 секунди, що підтверджується на сторінці Summary бази даних:

DECLARE @Counter INT

SET @Counter = 0

WHILE @Counter &lt 100 BEGIN

SET @Counter = @Counter + 1

INSERT dboWorkOrder (ProductID, OrderQty, StockedQty,

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

SELECT ProductID, OrderQty, StockedQty, ScrappedQty,

StartDate, EndDate, DueDate, ScrapReasonID,

ModifiedDate FROM AdventureWorksProductionWorkOrder

END

Кілька схем поділу можуть використовувати одну і ту ж функцію поділу З точки зору архітектури це може мати сенс, якщо кілька таблиць повинні бути розділені з використанням одних і тих же кордонів, що підвищує однаковість розділів Для перевірки того, які таблиці які схеми поділу використовують, грунтуючись на функціях поділу, використовуйте діалогове вікно Object Dependencies (рис 534) для функції або схеми розділу Ви можете відкрити його, використовуючи контекстне меню функції поділу

Рис 534 У вікні Object Dependencies видно, що таблиця WorkOrder використовує схему поділу psYearAll, яка в свою чергу використовує функцію pfYerarsRT

Для перегляду інформації про використання розділів погляньте на подання syspartitions і syspartition_counts

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

У розділених таблицях слід зазначити одну прекрасну особливість: для виконання до них запитів не вимагається написання будь-якого особливого програмного коду Оптимізатор запитів для отримання даних автоматично вибирає правильні таблиці

Оператор $ partition може повернути цілочисельний ідентифікатор розділеної таблиці при використанні з функцією поділу У наступному фрагменті коду підраховується кількість рядків у кожному з розділів:

SELECT $PARTITIONpfYearsRT(DueDate) AS Partition,

COUNT(*) AS Count FROM WorkOrder

GROUP BY $PARTITIONpfYearsRT(DueDate)

ORDER BY Partition

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

Partition Count

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 703900

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1821200

3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2697100

4&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2036900

Наступний запит відбирає дані за один рік, таким чином, всі ці дані знаходяться в одному розділі Аналізуючи план виконання запиту, показаний на рис 535, ми бачимо, що оптимізатор запитів використовує високошвидкісне сканування кластеризованого індексу в розділі з ідентифікатором Ptnldsl005:

SELECT WorkOrderlD,ProductID, OrderQty, StockedQty, ScrappedQty FROM dboWorkOrder WHERE year(DueDate) = 2002;

Зміна розділених таблиць

Щоб оновити розділену таблицю відповідно до зміненими даними і виконати тестування різних схем поділу, її можна без праці модифікувати Незважаючи на видиму простоту команд, зміна структури розділених таблиць, як ви розумієте, не може відбутися швидко

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

Злиття розділів

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

2004&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp рр. у функції поділу pfYearsRT і обєднання даних з відповідних розділів можна виконати за допомогою наступної інструкції:

Puc 535 Логічна операція сканування кластеризованого індексу містить новий параметр 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 не повинна перевіряти ще діапазон даних

■ Як відособлена таблиця, так і розділ, який її отримує, повинні знаходитися в одній файлової групі

■ Розділ призначення повинен бути порожнім

По суті, перетворення таблиці в розділ є реорганізацією метаданих з метою перепризначення існуючої таблиці в якості розділу (рис 536) Ніякі дані фактично не переміщаються, так що перемикання таблиці відбувається практично миттєво, незалежно від розмірів таблиці

Виділення нової таблиці

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

CREATE TABLE dboWorkOrderNEW (

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

Рис 536 Перемикання таблиці фактично є зміною внутрішніх метаданих бази даних, що перетворює відокремлену таблицю в один з розділів

Індекси, ідентичні попередній таблиці, будуть створені в розділеній таблиці: ALTER TABLE dboWorkOrderNEW ADD CONSTRAINT WorkOrderNEWPK

PRIMARY KEY NONCLUSTERED (WorkOrderlD, DueDate)

go

CREATE CLUSTERED INDEX ix_WorkOrderNEW_DueDate ON dboWorkOrderNEW (DueDate)

Додаємо обовязкове обмеження:

ALTER TABLE dboWorkOrderNEW ADD CONSTRAINT WONewPT

CHECK (DueDate BETWEEN 1/8/2004 AND 31/8/2004)

Тепер імпоршруем нові дані з Ad venture works, використовуючи дані за січень 2004 року: INSERT dboWorkOrderNEW (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 AdventureWorksProductionWorkOrder

WHERE DueDate BETWEEN 1/1/2004 and 31/1/2004;

Нова таблиця тепер має 3158 рядків

Перемикання в розділену таблицю

Вихідна розділена таблиця, створена раніше в цьому розділі, мала неподілений некластерізованний первинний ключ Так як одне з правил перемикання в розділену таблицю свідчить, що всі індекси повинні бути розділеними, першим завданням в наступному прикладі є видалення і перебудова первинного ключа таблиці WorkOrder, щоб він став розділеним:

ALTER TABLE dboWorkOrder

DROP CONSTRAINT WorkOrderPK ALTER TABLE dboWorkOrder 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 dboWorkOrder REBUILD PARTITION = 5

Так як поділ доступно тільки в редакції Enterprise Edition, в цій же редакції доступна і інтерактивна перебудова індексів

Видалення поділу

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

DROP INDEX ix_WorkOrder_DueDate ON dboWorkorder WITH (MOVE TO [Primary])

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*