У минулому адміністраторам баз даних, керуючим таблицями з мільйонами рядків даних, доводилося створювати множинні таблиці. Провівши секціонування таблиць, адміністратору доводилося прив'язувати
ці таблиці один до одного під час виконання багатьох запитів. Прив'язка секцій один до одного вимагало створення Секціонірованние уявлення або збереженої процедури-оболонки, що визначала місцезнаходження даних і виконувала іншу процедуру, що зберігається, яка працювала тільки з секціями, потрібними для повернення набору даних.
Хоча ці методи дозволяли досягти необхідного результату, вони були вельми обтяжливі. Операції адміністрування декількох таблиць і їх індексів, а також методи, використовувані для зворотного прив'язування таблиць, часто ставали джерелом труднощів при адмініструванні та обслуговуванні. Крім того, створення кількох таблиць для секціонування даних призводило до відсутності гнучкості, так як в збережених процедурах, завданнях обслуговування, завданнях служб перетворення даних (DTS), коді програми і інших процесах доводилося враховувати характер секціонування. Тому для забезпечення можливості додавання або скидання цих квазісекцій без зміни коду такі елементи зазвичай створювалися недінаміческімі способами і, отже, мали низьку ефективність.
Таким чином, можна створювати множинні секції, переміщати їх, скидати старі секції і навіть змінювати спосіб секціонування даних, не вносячи жодних змін в код програми. Код додатка просто продовжує звертатися до тієї ж самої базової таблиці або імені індексу. Це дозволяє також зменшити кількість даних, що зберігаються в окремих індексах, що, в свою чергу, знижує час обслуговування цих індексів. Крім того, можна завантажувати дані в порожні секції, що дозволяє збільшити швидкість завантаження даних.
Створення секціонованих таблиць
Для створення таблиці, яка здатна виростати в розмірах і задіяти додаткові секції крім вихідної, застосовуються функції секціонування і схеми секціонування. Саме ці об'єкти дозволяють розділяти дані на конкретні сегменти і управляти їх розташування в рамках конструкції сховища. Наприклад, можна розподілити дані по декільком дисковим масивам в залежності від віку даних або інших характерних ознак. Слід зазначити, що таблицю можна секціонувати по одному з її стовпців, і кожна секція повинна містити дані, які не можуть бути поміщені в інші секції.
Функції секціонування. При секціонуванні таблиці в першу чергу потрібно визначити принцип, за яким дані будуть розділятися на сегменти. Для зіставлення окремих рядків даних з різними сегментами служить функція секціонування. Рядки даних можуть зіставлятися за стовпцем будь-якого типу, крім наступних: text, ntext, image, xml, timestamp, varchar (max), nvarchar (max), varbinary (max), псевдоніми типів даних і призначені для користувача типи даних середовища CLR. Однак функція секціонування повинна вміщувати рядок даних тільки в одну секцію таблиці; іншими словами, в результаті застосування функції одна і та ж рядок не може належати кільком секціях одночасно.
Щоб секціонувати таблицю, в ній необхідно створити стовпець секціонування. Цей стовпець секціонування можна створити в схемі таблиці в момент створення таблиці, або додати пізніше шляхом модифікації таблиці. Стовпець може приймати значення NULL, але все рядки, що містять значення NULL, будуть за замовчуванням поміщатися в найлівішу секцію таблиці. Цього можна уникнути, вказавши при створенні функції секціонування, що значення NULL повинні поміщатися в найправішу секцію таблиці. Вибір лівої чи правої секцій - важливе рішення проектування, що виявляється при зміні схеми секціонування, додаванні додаткових секцій або видаленні існуючих.
При створенні функції секціонування можна вибрати функції LEFT або RIGHT. Різниця між секціями LEFT і RIGHT полягає в тому, де в схемі секціонування розміщуватимуться граничні значення. При використанні секцій LEFT (цей варіант вибирається за замовчуванням) граничне значення включається в секцію, тоді як при використанні секцій RIGHT граничне значення розміщується в наступній секції.
Щоб краще зрозуміти цей принцип, розглянемо такі приклади секцій LEFT і RIGHT:
У першій функції (Left_Partition) значення 1, 10 і 100 розміщуються відповідно в першій, другій і третій секціях. У другій функції (Right_Partition) ці значення розміщуються в другій, третій і четвертій секціях.
При створенні секціонірованной таблиці важливо, щоб секції вийшли якомога більше рівними. Це допоможе зрозуміти, скільки місця буде потрібно для кожної секції. Використання параметрів LEFT і RIGHT визначає, куди будуть розміщуватися дані, що, в свою чергу, ставить розмір секції і розміри індексів, створених на ній.
Визначити номер секції, в яку потрапить те чи інше значення, можна за допомогою функції $ PARTITION, як показано нижче:
Перша інструкція SELECT повертає значення 2, друга - значення 3.
Схеми секціонування. Після створення функції і вибору способу розділення даних між секціями слід вирішити, де будуть створюватися окремі секції в дискової підсистеми. Для створення цієї дискової структури використовуються схеми секціонування. Схеми секціонування керують тим, як окремі секції зберігаються на диску, шляхом використання файлових груп для розміщення кожної секції на дискової підсистеми. Схеми секціонування можна налаштувати таким чином, щоб всі секції розташовувалися в єдиній файлової групі, щоб кожна секція розташовувалася в своїй файлової групі або щоб кілька секцій використовували загальні файлові групи. Останній спосіб дає адміністратору бази даних широкі можливості розосередження операцій введення-виведення на диску.
На рис. 1 показані деякі із способів, що дозволяють присвоїти схемою секціонування одну або кілька файлових груп. Слід пам'ятати, що файлові групи, які використовуються схемою секціонування, повинні існувати в базі даних перед створенням схеми.
Якщо створити шаблони функцій секціонування, показані на рис. 1. і використовувати цю схему секціонування для створення таблиці, то згодом можна буде визначити, де будуть розміщуватися окремі рядки даних в секціонованих таблицях. Це дозволить згодом переглядати розподіл рядків даних, вставлених в Секціонірованние таблицю. Забезпечує це програмний код буде виглядати, як показано на рис. 2.
Зміна секціонованих таблиць
Незважаючи на ретельне планування, іноді доводиться вносити зміни в секціоновані таблиці після того, як вони створені і наповнені даними. Схема секціонування може працювати, як задумано, але адміністратору бази даних може, наприклад, знадобитися додати нові секції при накопиченні нових даних, або ж може знадобитися за один раз скинути велику кількість секціонованих даних. На щастя, секціоновані таблиці і базові структури секціонування дозволяють вносити зміни після того, як таблиця введена в експлуатацію і наповнена даними.
Додавання секцій. Багато планів секціонування включають можливість додавання нових секцій у майбутньому. Цей момент часу може здаватися певною датою або залежати від значення, що знаходиться в стовпці інкрементальних ідентифікаторів. Однак якщо додавання нових секцій у Секціонірованние таблицю не було заплановано заздалегідь, це можна зробити в будь-який момент. Розглянемо таблицю, створення якої показано на рис. 2. Додати в неї нову секцію, яка буде містити значення більше 500, можна наступним чином:
Можливість додавання нових секцій забезпечує високу гнучкість. На рис. 3 показано, як додати секцію зліва від функції. В даному випадку необхідно визначити в схемі секціонування, де буде розміщуватися нова секція, так як вже вичерпані всі файлові групи, створені при першому побудові схеми секціонування. Хоча для всіх секцій використовується файлова група PRIMARY, в схемі секціонування необхідно явно визначити, що ця файлова група повинна використовуватися і для нової секції.
Злиття двох секцій. Сервер SQL Server дозволяє скидати окремі секції з таблиці, при цьому зберігаючи дані. Таким способом можна виконати злиття старих активних даних з архівувати даними або зменшити кількість наявних секцій для спрощення адміністрування секціонірованной таблиці. За допомогою злиття секцій можна також переміщати дані з однієї файлової групи в іншу для звільнення дискового простору на певних дискових масивах. У програмному коді на рис. 4 показано, як можна перемістити дані з однієї секції в іншу на одній і тій же файлової групі.
Переміщення секції з однієї таблиці в іншу. Однією з типових задач адміністрування є переміщення старих даних в окремі архівні таблиці. Процес архівації зазвичай вимагає серії інструкцій, які призводять до додаткового споживання ресурсів в журналах транзакцій. Щоб легко архівувати велику кількість даних без зайвих витрат, пов'язаних з журналом транзакцій, можна скористатися перекладом приналежності секції з однієї таблиці на іншу. Ця можливість дозволяє адміністратору бази даних переміщати сегменти старих даних з активних таблиць в архівні. Оскільки самі дані при цьому в дійсності не переміщаються, цей спосіб вимагає значно менше часу, ніж при переміщенні рядків даних по одній. На рис. 6 показано, як можна це зробити.
Використання одиничної секції для створення нової таблиці. Одиничну секцію можна перемістити з існуючої секціонірованной таблиці в порожню несекціонірованную таблицю. В результаті цього адміністратор бази даних отримує можливість виконати обслуговування індексів на цій одиничної секції або легко скинути велику кількість даних без необхідності реєструвати процес видалення в журналі. На рис. 7 показано, як перемістити секцію в порожню таблицю і потім скинути дані за допомогою цієї нової таблиці.
Разом з можливістю секціонування даних таблиці з'являється і можливість створювати секціоновані індекси. Це дозволяє адміністратору бази даних проектувати структуру індексу на основі розділених даних, а не на основі всіх даних таблиці. Створення секціонованих індексів тягне за собою створення окремих збалансованих дерев на секціонованих індексах. В результаті поділу індексів створюються індекси меншого розміру, і оброблювачу сховища стає простіше їх обслуговувати під час зміни, додавання і видалення даних. Адміністратор бази даних також може обслуговувати ці зменшені індекси окремо, що дає можливість більш ефективно обслуговувати індекси на великих наборах даних.
Створення секціонованих індексів. При створенні секціонованих індексів можна створювати вирівняні або невирівняні індекси. Вирівняні індекси мають на увазі прямий зв'язок з секціонованими даними. (У випадку з невирівняні індексами вибирається інша схема секціонування.)
З цих двох методів кращий вирівняний індекс, який вибирається автоматично, якщо після створення секціонірованной таблиці індекси створюються без вказівки іншої схеми секціонування. Використання вирівняних індексів надає необхідну гнучкість для створення додаткових секцій в таблиці, а також дозволяє переводити приналежність тієї чи іншої секції на іншу таблицю. Наявність цих можливостей - основна причина, по якій адміністратори баз даних відразу створюють секціоновані таблиці. Для вирішення більшості завдань, пов'язаних з секціонуванням, досить використовувати для індексів схему секціонування таблиці.
Індекси на таблицях можна створювати таким чином, щоб дані в індексі були вирівняні з даними в таблиці. Якщо дані зберігаються в секціонірованной таблиці, це дозволяє з'єднувати дані різними способами (секціоновані дані можуть ефективно з'єднуватися з іншими секціонованими даними за допомогою оптимізатора запитів). Як варіант, можна зробити це з несекціонірованной таблицею, що дозволить створити Секціонірованние індекс (на таблиці з одиничною секцією) для спрощення обслуговування індексу.
Програмний код на рис. 8 створює Секціонірованние некластерізованний індекс на секціонірованной таблиці. Некластерізованний індекс вирівнюється з таблицею; як некластерізованний ключа індексу використовується стовпець секціонування таблиці.
Програмний код на рис. 9 створює невирівняні некластерізованний індекс на секціонірованной таблиці. У цьому некластерізованний індексі як ключ індексу використовуються різні стовпці; цей ключ може використовуватися в упорядкованих об'єднаннях з іншими секціонованими таблицями.
Слід врахувати, що обслуговування індексу для секцій з одним індексом повинно виконуватися в автономному режимі, тому воно може послужити причиною блокування таблиці під час обслуговування індексу. Щоб уникнути цього слід перемістити одиничну секцію в окрему секцію, виконати обслуговування індексу, а потім перемістити секцію назад в головну таблицю. Цей процес викличе деяке зниження продуктивності під час зворотного переміщення секції в таблицю і поновлення кластерізованного індексу, але це призводить до менших ускладнень, ніж блокування всієї таблиці, і вимагає менше системних ресурсів.