Головна Оптимізація продуктивності моделі даних: налаштування бази даних
Кращий спосіб вирішення проблеми з ресурсами - просто не допустити їх ...
Оптимізація продуктивності бази даних SQL Server починається з вибору коректної конфігурації бази даних і моделі даних. Можна підвищити швидкодію, доповнивши базу даних індексами різних типів і більш потужними апаратними засобами, але повністю ліквідувати недоліки моделі даних все одно не вдасться. Наслідком невдалої конфігурації бази даних або моделі даних може стати занадто великий час відгуку системи, блоковані або завислі транзакції, неправильні або неточні результати при підготовці бізнес-звітів, розсинхронізація даних, неузгодженість даних і неможливість скласти запит для отримання потрібних даних. Але невдала модель даних - не єдина причина таких проблем. Наприклад, повільний відгук системи може бути результатом перевантаженні серверу. Невдале поєднання оновлень транзакції від конфліктуючих програм може призвести до зависання чи блокування. Слід завжди ретельно дослідити причини неполадок. Якщо не вдається виявити перевантажений процесор або конфлікт між двома транзакціями, які намагаються монопольно заволодіти одним інформаційним ресурсом, необхідно уважно розглянути конфігурацію бази даних і моделі даних; саме вони можуть бути причиною неприємностей.
Для побудови бази даних, поведінка якої буде повністю відповідати очікуванням, потрібно в першу чергу розглянути її фундамент. Починати слід з оптимізації операційної системи. Потім потрібно налаштувати конфігурацію бази даних для підтримки коректної моделі даних та збереження їх цілісності. Заздалегідь продумуючи конфігурацію базових елементів, можна запобігти багатьом проблеми продуктивності і створити умови для підвищення загального швидкодії бази даних.
Підготовка середовища
Оптимізація продуктивності SQL Server починається з операційної системи Windows. SQL Server може працювати тільки в середовищі Microsoft, тому виключно важлива умова успіху - взаєморозуміння з системним адміністратором Windows. Два найважливіших параметра сервера бази даних - файлова система і файл підкачки. Для SQL Server слід використовувати файлову систему NTFS - вона більш стабільна і краще захищена, ніж FAT, хоча вважається, що операції запису трохи швидше виконуються в FAT. Під час налаштування файлу підкачки практичне правило для віртуальної пам'яті - встановити статичний розмір в 1,5 рази більше розміру фізичної пам'яті. Крім того, якщо який-небудь компонент сервера, такий як мережева плата або жорсткий диск, переходить в режим очікування після періоду, якщо нею, то слід заборонити перехід в режим очікування (або доручити зробити це системного адміністратора). Краще не ризикувати необхідністю «холодної» завантаження для активізації елемента комп'ютера. У мультипротокольний середовищі слід переконатися, що TCP / IP - Перша в наборі протоколів. Якщо мережеві з'єднання мають малу пропускну здатність, то потрібно, щоб стандартна величина тайм-ауту при реєстрації перевищувала час реєстрації додатків, що використовують базу даних.
На додаток до оптимізації операційної системи для роботи з SQL Server слід підвищити відмовостійкість середовища. Для надійності та швидкодії я рекомендую використовувати для SQL Server масив RAID. Рішення RAID може бути дорогим, але кошти, відпущені на його придбання, будуть витрачені не даремно, якщо продумано вибрати оптимальний тип RAID для даного середовища, визначивши частини бази даних, які необхідно захистити в першу чергу. Пояснення різних типів систем RAID і їх переваг приведено у врізки «SQL Server на масиві RAID».
Оптимізація продуктивності SQL Server неможлива без коректної конфігурації бази даних. Для підвищення швидкодії бази даних слід віднести основні типи даних, які потрібно зберігати в базі, до окремих груп файлів. Потрібно відділити системні таблиці від призначених для користувача таблиць, дані від індексів, табличні дані від зображень, тексту і n-тексту (ntext використовується для зберігання рядків символів Unicode). Застосовуючи цю схему для розділення даних на кілька груп файлів, можна побудувати надзвичайно масштабовану базу даних. Нагадаю, що масштабованість - це можливість збільшити кількість оброблюваних транзакцій без зниження продуктивності. У малих системах можна зібрати всі файлові групи на одному диску (за винятком журналу транзакцій, який повинні завжди зберігати на іншому диску, окремо від інших даних). У міру розширення системи, збільшення числа користувачів і обсягу даних, можна переміщати різні групи файлів на окремі диски, тим самим розподіляючи робоче навантаження між кількома дисками. Завдяки розділенню бази даних на кілька груп файлів управління резервним копіюванням спрощується. Можна використовувати групи файлів для резервного копіювання дуже великих баз даних (very large database, VLDB) у тимчасовому вікні, відведеному для копіювання бази даних. Більш докладна інформація про використання груп файлів для резервного копіювання наведена у статті Кімберлі Тріпп «Поки не вдарив грім» на сайті www.windowsitpro.ru за адресою http://www.osp.ru/win2000/sql/200309sq477.htm. Групи файлів можна використовувати і для горизонтального поділу, яке описано в статті «Повернення до життя» за адресою http://www.osp.ru/win2000/sql/admsecrets/401_1.htm (www.windowsitpro.ru). При проектуванні високопродуктивної бази даних групи файлів - корисний інструмент, який допоможе уникнути виникнення проблем ще до початку роботи.
Оригінальний текст в лістингу 1 ілюструє мій звичайний спосіб побудови бази даних. Кожна група файлів має три імені: ім'я групи файлів, логічне назва файлу і фізичне ім'я файлу. Ці імена можна побачити, відкривши будь-яке вікно властивостей бази даних, а потім вибравши вкладку Data Files. На цій вкладці елементи стовпця Filegroup відповідають PRIMARY і імен груп файлів, які наведені в першій частині лістингу 1. Елементи в стовпці Location - це імена фізичних файлів, в які входить повний шлях до місця зберігання фізичного файлу на свій диск. Системні таблиці SQL Server слід помістити в групу PRIMARY, а призначені для користувача таблиці та індекси - у відповідні групи файлів, окремо від системних таблиць SQL Server. Зображення й текстові дані розміщуються у власній групі файлів, як показано у фрагменті вихідного тексту з міткою A (лістинг 2).
Параметри конфігурації, що слідують за командою CREATE DATABASE, встановлюються відповідно до стандарту ANSI SQL-92. Можливо, один або кілька параметрів доведеться змінити відповідно до вимог конкретного підприємства. Слід переконатися, що задані параметри конфігурування сумісні з конкретною середовищем.
Цілісність - обов'язкова умова
Наступний крок в оптимізації продуктивності бази даних - налаштувати SQL Server на примусову цілісність посилань. Наприклад, між таблицями Store і Sale в лістингу 2, адаптованому з бази даних pubs, існує відношення залежності. Продаж (Sale) не може здійснюватися без зв'язку зі складом (Store). Цілісність посилань означає, що ця бізнес-зв'язок реалізується одним з двох способів. Можна призначити це завдання з додатком поза SQL Server або надати SQL Server можливість встановити це правило. На мій погляд, ведення статичних правил, таких як цілісність посилань між Store і Sale, краще надати базі даних. Оригінальний текст для правила складається один раз (фрагмент з міткою B в лістингу 2). Потім SQL Server застосовує правило для всіх користувачів бази даних. Якщо ввести правило за допомогою програми, то воно може зникнути з майбутніх версій цієї програми або буде не розпізнаний іншими додатками, які звертаються до тих же даних. Це може призвести до порушень цілісності посилань і, можливо, перекручення даних.
На додаток до забезпечення цілісності посилань усередині SQL Server я рекомендую використовувати DRI (declarative referential integrity - декларативна цілісність посилань) замість тригерів або збережених процедур. DRI - обмеження, а обмеження виконуються більш ефективно, ніж тригери або збережені процедури, особливо якщо для передачі даних у базу використовуються масивні операції завантаження інформації.
У лістингу 2 показано, як організувати DRI між таблицями Store і Sale. Як зазначалося вище, ці дві таблиці адаптовані з бази даних pubs. Я змінила стовпці і вставила параметри, необхідні в добре організованою виробничому середовищі, а також додала кілька стовпців, щоб проілюструвати вплив моделі даних на продуктивність. Оператор CREATE TABLE для таблиці dbo.Store додає новий стовпець, StorePhoto, в якому містяться зображення. Слід зазначити, що в останньому рядку оператора CREATE TABLE дані направляються на зберігання до групи файлів MyDatabase_data, але пов'язані з ними зображення будуть зберігатися в групі файлів MyDatabase_image.
Зовнішні ключі. Я завжди ставлю обмеження для первинних і зовнішніх ключів окремо від оператора CREATE TABLE, щоб мати можливість управляти іменами обмежень. Можна призначити стовпець як первинний ключ або зовнішнього ключа при створенні таблиці, але я не роблю цього. Я віддаю перевагу окремі оператори ALTER TABLE для обмежень первинних і зовнішніх ключів. При проектуванні моделі даних доводиться постійно вносити зміни. Тому необхідно мати можливість швидко ідентифікувати різні обмеження.
Очевидно, що стандартні імена обмежень, що призначаються SQL Server, є значущими. Наприклад, FK__sales__stor_id0AD2A005, ім'я оригінальному обмеження зовнішнього ключа між Stores і Sales в моєму екземплярі бази даних pubs, явно відноситься до зовнішнього ключа в таблиці Sales, який використовує стовпець stor_id. Однак з імені обмеження не можна зрозуміти, на яку таблицю проводиться посилання. Моє угоду про іменуванні, FK_Sale2Store, більш короткий, і, хоча в моєму імені не міститься інформація про стовпці, воно говорить про те, що Sale залежить від Store. Крім того, мені не потрібно виконувати запит, щоб отримати повне ім'я обмеження. Досить дотримуватися стандарту, встановленому для імен обмежень зовнішнього ключа. Назва обмеження будь-якого зовнішнього ключа буде починатися з FK_, за яким слідує ім'я залежною таблиці, номер 2 і, нарешті, ім'я незалежної таблиці.
Індексація. SQL Server не будує індекс автоматично для стовпців зовнішнього ключа (на відміну первинного ключа в таблиці). У виробничому середовищі можна використовувати стовпці зовнішнього і первинного ключів для операцій злиття. Тому при створенні нової бази даних слід побудувати індекс для кожного стовпця зовнішнього ключа в кожній залежною таблиці. Як правило, ці індекси не кластерізовани. В одній з наступних статей я детальніше розповім про кластеризації, а поки достатньо згадати, що кластеризація проводиться за колонку або стовпців, які належить найбільш інтенсивно використовувати для отримання даних. Можна навіть почекати, поки база даних наблизиться або досягне виробничого етапу, перш ніж приймати рішення про кластеризації.
Ілюстрація даного підходу до кластеризації - кластерізованний індекс у фрагменті C лістингу 2, який був побудований для таблиці Sale з використанням StoreCode і SaleID. Звіти про продажі будуть випускатися щодня або щогодини і сортуватися спочатку по магазинах (представленим StoreCode), потім по SaleID. Значення SaleID зростають протягом дня (SaleID - ідентифікатор), тому вони фактично являють собою послідовні номери записів; як вони, так і значення SaleID збільшуються з кожною новою продажем. Більшість звітів означають підсумкові звіти з підсумковими даними за окремими магазинах. StoreCode разом з SaleID - відмінний кандидат на кластеризації. Завдяки кластерного індексу прискорюється вилучення даних, тому що дані вже впорядковані - в даному випадку спочатку по Store Code, а потім, усередині кожного значення Store Code, за SaleID. Власне, StoreCode пов'язує таблицю Sale з таблицею Store. Наявність двох індексів - одного кластерного, одного некластерного, кожен з яких починається з StoreCode, можливо, надміру, але в ході експлуатації бази даних можна буде побачити, як SQL Server використовує (або не використовує) ці індекси. Якщо SQL Server не задіє некластерізованний індекс, то від нього можна сміливо відмовитися. Проте на стадії проектування рекомендується побудувати окремий індекс для кожного стовпця зовнішнього ключа, навіть якщо спочатку це призведе до надмірності індексів.
Якщо можливо, слід визначити стандартні значення та контрольні обмеження (check constraint задає діапазон значень елементів стовпців) в базі даних, а не на прикладному рівні з тієї ж причини, з якої переважно покласти на базу даних управління цілісністю посилань. Обмеження -- об'єкти бази даних, тому вони виконуються швидше й ефективніше, ніж програмний код в зовнішньому додатку. Якщо правила, які можуть бути виражені за умовчанням, і контрольні обмеження статичні - не міняються кожного тижня або кожного місяця, то ці правила можна визначити як обмеження стовпців або таблиць. У операторі CREATE TABLE dbo.Sale лістингу 2 стовпець SaleDate має стандартне значення CURRENT_TIMESTAMP. Це правило навряд чи зміниться за весь час експлуатації бази даних, тому його зручно визначити як обмеження стовпця. Наступне правило, SalePayTerms, висловлено як контрольне обмеження. SalePayTerms не може мати значення NULL, і спочатку для SalePayTerms визначено значення Net 30, Net 60 і On Invoice. Програма не може змінити Цей набір значень. Якщо потрібно додати значення в набір, то слід змінити дане обмеження стовпця. Якщо передбачається, що набір значень буде часто змінюватися, то більш зручний спосіб ввести обмеження - скласти переглядові таблицю умов оплати покупок, присвоїти кожному рядку таблиці унікальне значення в якості ідентифікатора і зіставити переглядові таблицю з dbo.Sale у співвідношенні "один до багатьох» (1: M). Ставлення між переглядовій таблицею і dbo.Sale буде залежним ставленням, які здійснюються в такий же спосіб, як відносини між dbo.Store і dbo.Sale.
Оптимізація продуктивності бази даних - величезна і важлива тема, яка потребує розуміння середовища бази даних і великого практичного досвіду. Починати оптимізацію зручно з вивчення моделі даних і вимог бізнесу. Тільки після цього можна налаштувати модель даних на високу продуктивність.