Мета розділу – визначення критеріїв та розгляд основних напрямів оптимізації роботи БД, які відповідають вимогам користувачів та забезпечують комфортність їхньої роботи.
Під оптимальною роботою БД зазвичай розуміють створення таких умов до доступу й маніпулювання даними, що забезпечують максимальну швидкодію при мінімальних витратах ресурсів. На такий важливий ресурс, як продуктивність сервера розробники БД можуть впливати не часто. Тому, декларувавши відомий факт, що чим потужніше апаратна частина, тим краще, розглянемо такі напрями, які підлягають впливу з боку розробників та адміністраторів БД:
1) оптимізація структури БД;
2) оптимізація запитів;
3) оптимізація прикладної програми клієнта.
У свою чергу оптимізацію структури БД виконують за такими напрямами:
1) нормалізація відношень;
2) методи доступу до даних;
3) налагодження внутрішніх механізмів СУБД.
Оптимізація запитів складається з:
1) побудови структури БД, адекватної запитам;
2) оптимізації структури індексів таблиць БД;
3) оптимізації текстів запитів.
Оптимізацію прикладних програм клієнтів будемо розглядати лише з боку організації ефективного доступу до БД.
Нормалізація відношень впливає на швидкодію операцій вибірки даних [4 - 7, 10, 12]. Часто нормалізація погіршує швидкодію. З погляду теорії, вона приводить до найбільш ясного відображення сутностей предметної області. Усуваючи надмірність даних, вона тим самим суттєво заощаджує дисковий простір. З погляду практики, і саме на великих обсягах даних, оптимізація може суттєво зменшити швидкодію. При звертанні до однієї, нехай великої, таблиці БД, витрачається менше часу, чим при звертанні до декількох, більш дрібних таблиць із застосуванням операцій з'єднання.
Крім того, високий ступінь нормалізації призводить до збільшення кількості відношень у БД. Унаслідок цього структура БД не сприймається розробниками та користувачами цілісно. Спрацьовує так званий «людський фактор», тобто уже на стадії логічного проектування виникають серйозні помилки в структурі БД, що надалі може спричинити негативні наслідки.
Тому при проектуванні структури БД слід враховувати як негативні, так і позитивні сторони нормалізації відношень. Звичайно жертвують додатковою витратою дискової пам'яті задля зберігання не повністю нормалізованих таблиць, прагнучи забезпечити максимальну швидкодію, що актуально при зростанні числа одночасно працюючих із системою користувачів.
Хоча з теорії відомо, що структура даних має бути незалежною від способів доступу до них, на практиці це зовсім не так. Наприклад, виникають ситуації, коли необхідно забезпечити доступ та корегування даних декількома користувачами, які беруть участь у досить складних обчисленнях тих або інших величин. У таких ситуаціях іноді неможливо обійтися без визначення поточного стану цієї сукупності даних у додатковому полі. Його значення дозволяє читати, корегувати або переглядати дані, задіяні в розрахунках при звертанні до них групи користувачів з різних терміналів. Таким чином, проектуючи логічну структуру БД, неможливо абстрагуватися від того, яким чином дані будуть оброблятися на сервері та у прикладній програмі клієнта.
Відмітимо, що залежність між текстами запитів до БД, структурою й складом індексів таблиць також свідчить про зв'язок між структурою даних і методами доступу до них.
Для швидкого доступу до таблиці БД необхідно, щоб вона фізично займала безперервний блок сторінок. Відомо, що при виділенні нових сторінок у СКБД InterВase не робить ніяких спроб виділити суміжні сторінки для зберігання однієї й тієї таблиці [14, 15]. Тому дані, що відносяться до однієї сторінки, можуть бути інформацією з декількох таблиць, тобто фрагментовані. Зберігання множинних поколінь записів також призводить до сильного «забруднення» БД і сповільнює роботу з нею. При зміні запису фактично створюється нова її версія, яка змінена або додана трансакціями, що згодом відмінялися та не вилучалися з БД. Крім того, при вилученні записів не відбувається переміщення тих їхніх версій, які залишилися для того, щоб вилучити «дірки», які утворювалися на сторінках БД.
Вирішенням зазначених проблем є періодичне створення резервної копії й оновлення за допомогою неї БД. У цьому випадку збирається «сміття», тобто версії записів, які далі не будуть використовуватись, та усуваються «дірки» на сторінках БД, що утворилися після вилучення записів. Кожна таблиця розміщується в безперервному блоці сторінок.
Розмір самої сторінки БД має велике значення, оскільки читання-запис у СКБД InterВase здійснюються сторінками. Тому запис таблиці по можливості повинен розміщуватися в межах однієї сторінки. Якщо розмір сторінки малий для зберігання одного запису і для нього потрібно декілька сторінок, то для читання такого запису потрібно виконати кілька фізичних операцій. З іншого боку, розмір сторінки не повинен бути занадто великим, оскільки в цьому випадку будуть зчитуватися непотрібні записи.
Розмір буфера вводу-виводу також здатний впливати на швидкодію. Для БД, до яких найчастіше застосовуються операції читання, рекомендується збільшити розмір буфера вводу-виводу. Для БД, у яких частіше виконуються операції запису, розмір буфера рекомендується зменшити.
Наявність індексу може суттєво підвищити швидкість виконання деяких запитів. Однак, оскільки індекси мають оновлюватися системою при кожному внесенні змін у базову таблицю, то вони створюють додаткове навантаження на систему. Індекси зазвичай створюються з метою задовільнення певних критеріїв пошуку, після того як таблиця вже перебувала якийсь час у роботі й збільшилася в розмірах. Вони можуть створюватися тільки для таблиць БД, але не для представлень.
Індекс – це структура даних, що дозволяє зменшити час доступу до окремих рядків таблиць, незалежно від їхнього фізичного розміщення.
Він подібний предметному покажчику, що наведений наприкінці книжки. Це структура, яка пов'язана з файлом і призначена для пошуку інформації за тим принципом, що й предметний покажчик у книзі. Індекс дозволяє уникнути послідовного або покрокового сканування файлу в пошуках потрібних даних. При його використанні об'єктом пошуку може бути один або кілька записів файлу. Як і предметний покажчик книжки, індекс упорядкований і кожний його елемент містить назву об'єкта пошуку, а також один або кілька покажчиків (ідентифікаторів записів), що мають посилання на місце його розташування.
В InterВase поняття ключів та індексів поділені за логічним рівнем [14, 15]. Первинний (PRIMARY KEY) і зовнішній (FOREIGN KEY) ключі будуються для забезпечення посилальної цілісності реляційно-пов'язаних таблиць. Первинний ключ, крім цього, виконує функції підтримки унікальності своїх значень, що обумовлене його основним призначенням – однозначно характеризувати запис у таблиці БД. Для таких самих цілей може використовуватися й унікальний ключ (UNIQUE). Однак він використовується оператором SELECT для прискорення доступу до даних. «Звичайні» індекси на відміну від ключів, служать лише для оптимізації доступу до даних. З фізичної точки зору, те, що логічно при створенні таблиць поділялося на ключі та індекси, на фізичному рівні перетвориться на індекси. Однак, якщо «звичайним» індексам можна привласнити ім'я, то фізичні індекси, реалізовані на основі визначень ключів, будуються й іменуються системою автоматично.
Більшість діалектів, у тому числі InterВase, підтримують наступний оператор, що дозволяє створювати індекси для таблиць БД:
CREATE [UNIQUE] [ASС[ENDING] | DESC[ENDING]] INDEX Ім'я
ON Ім'я таблиці (атрибут[, атрибут ...]).
Тут обов'язково необхідно привласнити ім'я індексу, ім'я таблиці й перелічити імена атрибутів, за якими необхідно побудувати індекс. Нагадаємо, що формат оператора CREATE INDEX дозволяє виключити дублювання значень у ключових атрибутах, вказавши пропозицію UNIQUE. У цьому випадку унікальність значень потенційного ключа буде автоматично підтримуватися системою. За замовчуванням сортування значень індексних полів здійснюється за зростанням. Це можна вказати явно, використавши пропозицію ASС[ENDING]. Якщо необхідно забезпечити зворотну послідовність сортування – за зменшенням, то використовують пропозицію DESC[ENDING].
Створити індекси можливо в будь-який момент, навіть для вже заповненої даними таблиці. Однак можуть виникнути проблеми, що пов'язані з дублюванням значень у різних рядках, якщо в операторі використовується пропозиція UNIQUE. Тому має сенс визначати унікальні індекси безпосередньо при створенні таблиці, перед тим як до неї почали заносити інформацію. У результаті система відразу ж візьме на себе контроль над унікальністю значень відповідних атрибутів.
Для вилучення індексу використовується оператор
DROP INDEX Ім'я;
Однак цим оператором не можна вилучити індекс, створений за допомогою CREATE TABLE (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для цього слід застосовувати оператор ALTER TABLE. Не можна також вилучити індекс, що на цей час використовується іншими користувачами БД. Крім цього, для вилучення індексу потрібно мати відповідні привілеї доступу до БД.
Від структури індексів у значній мірі залежить ефективність виконання запитів [4, 6]. При цьому СКБД спочатку переглядає список індексів, які визначені для таблиць. Потім вибирається одна із двох схем виконання запиту – використовувати наявні індекси або послідовно переглянути таблиці. Оптимізатор СКБД прагне виконати запит з максимальною швидкодією та мінімальним використанням обчислювальних ресурсів. Перед першим викликом СКБД завжди оптимізує виконання запиту, ґрунтуючись на поточному стані БД. Удруге запити, у яких змінюються тільки значення параметрів, не оптимізуються: відбувається лише попереднє з’єднання формальних та фактичних параметрів, після чого запит виконується.
Повністю передбачити за якою схемою оптимізатор СКБД буде виконувати запит неможливо, оскільки стан БД може змінюватися. Але існують загальні положення, які слід враховувати при проектуванні запитів і структури індексів. Індекси необхідно створювати тоді, коли по стовпцю або групі стовпців часто:
1) проводиться пошук у БД: атрибут або група атрибутів часто перелічуються в пропозиції WHERE оператора SELECT;
2) виконуються з'єднання таблиць;
3) здійснюється сортування результатів у наборах даних, що повертаються запитами: атрибут або група атрибутів часто використовуються в пропозиції ORDER BY оператора SELECT.
Не рекомендується створювати індекси по стовпцях або групах стовпців, які:
1) мало використовуються для пошуку, об'єднання й сортування результатів запитів;
2) нерідко міняють значення, що призводить до необхідності частого оновлення індексів і відповідно до суттєвого сповільнення швидкості роботи з БД;
3) містять невелике число варіантів значень.
У разі, коли при виконанні запитів використовується сортування по тих самих стовпцях, то необхідно пам'ятати, що створення єдиного індексу здатне прискорити виконання запитів. Якщо в умові пошуку або в пропозиції ORDER BY використовуються не всі стовпці з цього індексу, то для оптимізації запиту застосовують тільки безперервну послідовність стовпців.
Часткове використання складеного індексу дозволяє суттєво зменшити витрати ресурсів СКБД [2, 4, 12]. Для цього складений індекс слід будувати так, щоб стовпці, за якими найчастіше ведеться пошук, перебували на початку списку. Тоді для пошуку може бути використана частина індексу. Наприклад, нехай часто виконуються запити
SELECT *
FROM SOMETABLE
WHERE A = :ParamA AND В = :ParamВ;
SELECT *
FROM SOMETABLE
WHERE A = :ParamA AND В = :ParamВ AND С = :ParamC;
SELECT *
FROM SOMETABLE
WHERE A = :ParamA AND В = :ParamВ AND С = :ParamC AND D = :ParamD;
Тоді, побудувавши складений індекс за стовпцями А, В, C, D, ми можемо стверджувати, що даний індекс буде використаний при оптимізації всіх трьох запитів. У першому випадку буде використана підмножина індексу, тобто значення А, В, у другому – значення А, В, C, у третьому – А, В, C, D ( тобто всі значення індексу).
Якщо умови відбору даних об'єднані за допомогою AND, то послідовність використання стовпців не має великого значення. Наприклад, для виконання наступних запитів може застосовуватися індекс з попереднього випадку:
SELECT *
FROM SOMETABLE
WHERE A = 100 AND В = 200;
SELECT *
FROM SOMETABLE
WHERE B = 200 AND A = 100;
Для пропозиції ORDER BY послідовність розташування стовпців у складеному індексі відіграє не малу роль. Наприклад, для оптимізації виконання наступних запитів необхідно використовувати різні індекси:
SELECT *
FROM SOMETABLE
ORDER BY А, В, C;
SELECT *
FROM SOMETABLE
ORDER BY А, C, В.
Слід пам'ятати, що в пропозиції ORDER BY можна використовувати тільки безперервну послідовність значень. Наприклад, попередній індекс не може використовуватися для виконання запитів:
SELECT *
FROM SOMETABLE
ORDER BY А, C;
SELECT *
FROM SOMETABLE
ORDER BY B, D.
Але він буде корисним для наступного запиту:
SELECT *
FROM SOMETABLE
ORDER BY А, B;
При частому використанні в умовах пошуку пропозиції WHERE декількох стовпців, які зв'язані між собою операцією «або» (OR) замість індексу за стовпцями А, В, C створюють декілька індексі, побудованих за кожним стовпцем окремо, інакше буде здійснений послідовний перегляд усієї таблиці. Це пов'язане з тим, що індексно-послідовний доступ за атрибутами А, В, C може бути здійснений тільки для стовпця А; значення стовпців В і С у цьому випадку розкидані по індексу. Важливо пам'ятати, що при використанні оператора OR кожна частина умов пошуку викликає окреме сканування таблиць, що беруть участь у запиті. Так, наприклад, при виконанні оператора SELECT
SELECT *
FROM SOMETABLE
WHERE A = 100 OR В = 200 OR C = 300
буде виконано три окремих сканування таблиці для пошуку значень, що задовольняють умовам А = 100; В = 200; C = 300.
Окремий потік пошуку породжує й кожний елемент у списку IN. Наприклад, пропозиція WHERE A IN (100, 200, 300) інтерпретується як WHERE A = 100 OR A = 200 OR A = 300. Однак при вказівці діапазону BETWEEN WHERE A BETWEEN 100 AND 300 цього не відбувається. Тому там, де можливо, слід заміняти IN на BETWEEN.
Зменшення загальної кількості індексів здійснюється за рахунок раціонального застосування складених індексів, а також забезпечення посилальної цілісності за допомогою тригерів. При великій кількості індексів знижується швидкість додавання, зміни й вилучення записів із таблиць БД. Нам відомо, що в БД використовується два види індексів: одні для прискорення доступу до даних, інші – для забезпечення посилальної цілісності. Якщо останні не беруть участь в оптимізації доступу до даних, тоді їх доцільно вилучати, а посилальну цілісність забезпечити за допомогою тригерів.
Розбалансування індексу призводить до того, що його глибина (depth) стає більше за критичне значення (2). Глибина індексу – параметр, що показує максимальне число операцій, необхідних для знаходження потрібного значення в таблиці БД за допомогою даного індексу. Індекси таблиці можуть бути розбалансовані після багаторазового внесення змін. У разі розбалансування цінність індексу при виконанні запитів знижується через збільшення часу, витрачуваного на вибірку даних. Тому час від часу необхідно:
1) перебудувати індекс;
2) перерахувати показник «вибираємості» індексу;
3) знищити індекс і знову його створити.
Перебудова індексу полягає в його перестворенні та збалансуванні після деактивізації оператором
ALTER INDEX Ім'я INACTIVATE
і наступній активізації оператором
ALTER INDEX Ім'я ACTIVATE.
Деактивізація індексу корисна також і у тому випадку, коли потрібно вставити в таблицю БД велику кількість записів. При додаванні записів у таблицю у звичайному режимі до її активного індексу синхронно додається відповідна інформація, що може його розбалансувати. Слід пам'ятати, що:
- не можна перебудовувати індекс, який у цей момент використовується іншими користувачами БД;
- не можна перебудовувати індекс, створений у операторі CREATE TABLE (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для цього слід застосовувати оператор ALTER TABLE;
- для виконання оператора ALTER INDEX потрібно мати відповідні привілеї доступу до БД.
Показник «корисності» індексу оснований на відомостях про число повторюваних його рядків. Він використовується СКБД під час доступу до таблиці для створення оптимального плану виконання запиту. Ефективність використання індексу при пошуку інформації в таблиці БД залежить від того, чи побудований він за унікальними значеннями і, якщо ні, наскільки відрізняються дані, за якими він побудований.
Показник «корисності» індексу розраховується під час його створення як число унікальних значень усередині індексу, що віднесене до середньої кількості записів. Після внесення змін у таблицю змінюється ступінь відмінності значень стовпців, за якими побудований індекс. Тому розрахований показник «корисності» може не відображувати реальний стан індексу й значення показника рекомендується примусово перераховувати: час від часу – при внесенні невеликих змін і завжди – при внесенні істотних змін. Перерахування здійснюється оператором
SET STATISTICS INDEX Ім'я.
Для участі у виконанні запиту вибираються індекси з максимальним показником «корисності». Такі індекси забезпечують більш швидкий пошук. Максимальний показник «корисності» мають унікальні індекси.
Середня кількість записів – показник, який розраховується щораз при оптимізації запиту як кількість сторінок БД, зайнятих цією таблицею, поділена на максимальне число записів на сторінці. Скорочення числа сторінок, зайнятих БД, і видалення на них «дірок» призводить до зменшення показника середнього числа записів і, як наслідок, до підвищення показника «корисності» індексів. Це ще один аргумент на користь періодичної оптимізації БД шляхом створення резервної копії та її оновлення.
Утиліта WISQL СКБД InterBase надає можливість показати план виконання запиту. Для цього виберіть пункт меню Session | Basic Settings, а потім встановить режим Display Query Plan. Тоді разом з результатом роботи запита буде виводитись план його виконання. Під планом виконання запиту розуміється перелік індексів, які використовуються СКБД для вибору даних з таблиць.
Для примусового виконання запиту за тим або іншим планом необхідно в операторі SELECT указати таку пропозицію:
PLAN <план виконання запиту>, де <план виконання запиту> =
[JOIN | [SORT] MERGE] ({елемент плану | план виконання запиту}[, {елемент плану | план виконання запиту} ...]),
где <елемент плану> = {таблиця | псевдонім}
NATURAL | INDEX (<індекс>[, <індекс> ...]) | ORDER <індекс>.
Синтаксис пропозиції <план виконання запиту> дозволяє з'єднати між собою кілька таблиць (пропозиція JOIN). У разі відсутності індексів, за яких записи таблиці можуть бути впорядковані, застосовується примусове сортування (пропозиція [SORT] MERGE). У пропозиції <елемент плану> – вказують таблицю, у якій проводиться пошук даних. Якщо таблиця декілька разів задіяна у запиті, то для скорочення тексту плану корисно застосовувати її псевдонім. Пропозиція NATURAL вказує, що для пошуку записів застосовується послідовний доступ. Це єдиний спосіб пошуку в тому випадку, коли немає відповідних індексів. Можна вказати один або кілька індексів, які мають використовуватися для пошуку записів, що задовольняють умові запиту (пропозиція INDEX). А пропозиція ORDER дозволяє визначити індекс, який необхідний для сортування таблиці. Приклади використання пропозиції PLAN розглянуті у відповідній документації на СКБД.
Мінімізація кількості з'єднань із БД дозволяє зберігати ресурси системи [3, 5, 13, 18]. Витрата системних ресурсів може позначитися на ефективності доступу до даних. Рекомендується зводити кількість з'єднань до мінімуму, а в ідеалі використовувати тільки одне з'єднання для кожної прикладної програми клієнта.
Методи мінімізації з'єднань із БД залежать від того середовища розробки, у якому написана прикладна програма-клієнт. Тому Ви маєте ознайомитися з відповідною технічною документацією. Наприклад, для з'єднання з віддаленою БД у прикладних програмах – клієнтах, написаних в середовище Delphi або C-Bilder, використовується компонент TDatabase. Він служить для:
- створення постійного з'єднання з БД;
- створення локального псевдоніма БД;
- зміни параметрів з'єднання, встановлених для псевдоніма БД;
- керування трансакціями.
Якщо не використовувати компонент TDatabase, то з'єднання з БД буде встановлювати кожний компонент типу «набір даних» – Ttable (таблиця), TQuery (SQL–запит), TStoredProc (збережувана процедура). Крім того, при з'єднанні з віддаленою БД безпосередньо, представленими компонентами типа «набір даних», змінювати попередньо встановлені параметри з'єднання неможливо.
Зниження мережного трафіка досягається за рахунок надходження в прикладні програми клієнтів мінімально можливого обсягу інформації з БД, якої достатньо для проведення маніпуляцій з даними. Наприклад, із двох наборів даних, що реалізовані компонентами TTable і TQuery, для маніпулювання даними в прикладній програмі–клієнті рекомендується використовувати останній компонент. По-перше, при доступі до табличних даних компонент TTable зчитує всі записи таблиці, у той час як компонент TQuery – рівно стільки, скільки необхідно для поточних цілей візуалізації, наприклад, для заповнення сітки TDВGrid. По-друге, при доступі до таблиць великого обсягу використання компонента TTable може призвести до істотних тимчасових затримок.
Компоненти TTable і TQuery мають різну природу: TTable орієнтований на навігаційний метод доступу до даних, що більш характерно для роботи з локальними СКБД, TQuery – на роботу з безліччю записів, що характерно при доступі до віддалених БД архітектури «клієнт-сервер». Компонент TTable дозволяє звернутися до однієї таблиці БД, TQuery може повертати дані одночасно з декількох таблиць БД. Відповідне підтвердження змін даних у TTable робиться для кожного запису, що суттєво збільшує мережний трафік. Зміна даних при використанні компонента TQuery може проводитися відразу над множиною записів операторами INSERT, UPDATE, DELETE.
Компонент TStoredProc використовується тільки для роботи з процедурами, що викликаються, й не застосовується для роботи з процедурами вибору, які також можуть повертати набори даних. Для роботи з процедурами вибору використовується компонент TQuery.
Перенос ваги обчислювальної роботи на сервер, по-перше, прискорює роботу прикладної програми–клієнта, а по-друге, мінімізує можливість виникнення помилок. Ось рекомендації, що дозволяють це здійснювати.
Оптимальність роботи СКБД завжди при доступі та маніпулюванні даними оцінюється кінцевим користувачем за її швидкодією. Оптимізація починається на етапі логічного моделювання предметної області (нормалізація відношень), продовжується на етапі організації доступу до даних (оптимізація запитів) і завершується організацією взаємодії клієнтської частини із СКБД.
© Куваєв Я.Г., 2005—2023.
Всі права захищені.
Вся інформація, яка розміщена на цьому веб-сайті, призначена тільки для персонального використання і не підлягає подальшому відтворенню і/або поширенню в будь-якій формі, інакше як за письмовим дозволом Автора.