Двадцять п’ять заповідей SQL, Інші СУБД, Бази даних, статті

Не можна не враховувати важливість ефективних SQL-операторів в заснованих на використанні СУБД Oracle додатках. Погано написаний оператор може привести до хаосу в базі даних. Оскільки в багатьох організаціях користувачі роблять доступ до баз даних з використанням засобів генерації звітів і прямих запитів, ефективно написаний запит на мові SQL дозволяє не тільки поліпшити продуктивність програми, але і зменшити мережевий трафік. Тому як користувачі, так і розробники повинні добре розуміти роботу оптимізатора запитів і можливості настройки, яка може зробити оператори більш ефективними і менш ризикованими.

У статті коротко обговорюються 25 прийомів, що дозволяють домогтися більш швидкого виконання операторів SQL. Деякі з цих прийомів раніше описувалися в інструкціях компанії Oracle і журналах, а багато інших раніше не публікувалися.

1. Добре знайте свої дані та бізнес-додаток

Ідентична інформація часто може бути отримана з різних джерел. Зустрітися з цими джерелами, і ви повинні бути в курсі обсягу даних та їх розподілу в своїй базі даних. Ви також повинні мати повне розуміння своєї моделі даних (так само як і зв’язків між різними бізнес-об’єктами) до написання необхідних операторів SQL. Це розуміння допоможе набагато краще складати запити для вилучення інформації з декількох таблиць. CASE-засоби, подібні Designer/2000, дуже допомагають документувати зв’язку між різними об’єктами.

2. Тестуйте свої запити на реалістичних даних

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

3. Пишіть у своїх додатках ідентичні оператори SQL

Застосовуйте всі можливості зв’язування змінних, процедур і пакетів. Переваги ідентичних операторів SQL включають зменшення витрат пам’яті на сервері баз даних і більш швидке виконання, оскільки зникає потреба в новому граматичному розборі. Наприклад, такі оператори не є ідентичними:

select * from employee where empid = 10;
SELECT * FROM EMPLOYEE WHERE EMPID = 10;
select * from employee where empid = 20;

але при використанні пов’язують змінної з ім’ям i_empid оператор

select * from employee where empid = :i_empid;

буде ідентичним.

4. Уважно ставтеся до використання індексів на таблицях

Постарайтеся створити всі необхідні індекси. Однак занадто велике число індексів може привести до зниження ефективності. Як же вирішити, які стовпці слід індексувати?


5. Робіть доступними до використання індексні шляху доступу до даних

Для отримання переваг від наявності індексів пишіть SQL-оператори таким чином, щоб для їх виконання були доступні індексні шляху доступу. Оптимізатор не може використовувати індексний шлях доступу, грунтуючись тільки на існування індексу; шлях доступу повинен бути зроблений доступним в SQL. Механізм “вказівок” (hints) – це один із способів гарантувати використання індексу.

6. При можливості використовуйте Explain Plan і TKPROF

Якщо ваші SQL-оператори недостатньо добре налаштовані, вони можуть бути неефективні, навіть якщо сама база даних Oracle “добре змазана”. Зустрітися з Explain Plan і засоби TKPROF, щоб уміти з користю їх застосовувати. Explain Plan допомагає дізнатися шлях доступу, що використовується для виконання оператора SQL; TKPROF показує реальні показники ефективності. Ці кошти прив’язані до програмного забезпечення сервера баз даних Oracle і можуть допомогти поліпшити ефективність виконання операторів SQL.

7. Розберіться в тому, як працює оптимізатор

Оператори SQL можуть бути виконані з використанням оптимізатора, керованого правилами, або оптимізатора, заснованого на оцінках. У старих додатках було більш поширене використання оптимізатора, керованого правилами. Багато користувачів Oracle застосовували цей підхід протягом ряду років і були цілком задоволені ним. Але при розробці нових програм розгляньте можливість застосування оптимізатора, заснованого на оцінках. Компанія Oracle удосконалює цей оптимізатор в кожному випуску, роблячи його більш стабільним і надійним. Якщо ви вирішите його використовувати, будьте готові регулярно виконувати аналіз схеми. Відповідне засіб зберігає статистичні показники бази даних в таблицях-каталогах, а потім ці показники використовуються оптимізатором. Налаштування операторів SQL можлива тільки при використанні оптимізатора, заснованого на правилах. Якщо ви плануєте перейти на використання нового оптимізатора, вам слід оцінити ефективність кожного оператора SQL у всіх додатках, що використовують базу даних.

8. Глобально думайте при виконанні локальних дій

Пам’ятайте завжди: будь-які зміни бази даних з метою налаштування одного оператора SQL можуть вплинути на інші оператори, використовувані додатками і користувачами.

9. Розділ WHERE є критичним

Для наступних прикладів розділу WHERE індексний шлях доступу не буде використовуватися, навіть якщо індекс існує (COL1 і COL2 – стовпці однієї таблиці, і створений індекс на COL1):

SELECT DEPT_NAME
FROM DEPARTMENT
WHERE UPPER(DEPT_NAME) like “SALES%”);

10. Для фільтрації записів використовуйте WHERE, а не HAVING

Уникайте використання розділу HAVING разом з GROUP BY на індексованих стовпцях. У цьому випадку індекс не використовується. Фільтр рядка за допомогою розділу WHERE, а не розділу HAVING. Якщо для таблиці EMP існує індекс на стовпці DEPTID, в при виконанні наступного запиту цей індекс використовуватися не буде:

SELECT DEPTID, SUM(SALARY)
FROM EMP
GROUP BY DEPTID
HAVING DEPTID = 100;

Однак цей запит можна переписати так, щоб індекс застосовувався:

SELECT DEPTID, SUM(SALARY)
FROM EMP
WHERE DEPTID = 100
GROUP BY DEPTID;

11. Вказуйте в розділі WHERE початкові стовпці ключа індексу

При наявності індексу з складовим ключем цей індекс може бути використаний, якщо в розділі WHERE фігурують умови на початкові стовпці його ключа. Для наступного запиту може бути застосований складовою індекс на шпальтах PART_NUM і PRODUCT_ID, утворений в зв’язку з обмеженням первинного ключа:

SELECT *
FROM PARTS
WHERE PART_NUM = 100;

в той час як в наводиться нижче запиті складовою індекс використовуватися не може:

SELECT *
FROM PARTS
WHERE PRODUCT_ID = 5555;

Останній запит можна переписати так, щоб індекс можна було застосувати. У цьому запиті передбачається, що стовпець PART_NUM буде завжди містити позитивні значення:

SELECT *
FROM PARTS
WHERE PART_NUM > 0
AND PRODUCT_ID = 5555;

12. Порівняйте сканування через індекс з повним переглядом таблиці

При виборі з таблиці більше 15 відсотків рядків повний перегляд таблиці зазвичай виконується швидше, ніж сканування через індекс. У таких випадках пишіть свої запити так, щоб при їх виконанні використовувався повний перегляд таблиці. При виконанні таких запитів індексне сканування не буде застосовуватися, навіть якщо існує індекс на стовпці SALARY. У першому запиті вказівка ​​FULL змусить Oracle-сервер повністю переглядати таблицю. Коли використання індексу приносить більше шкоди, ніж користі, можна застосовувати ці методи, щоб перешкодити використанню індексу.

SELECT * –+FULL
FROM EMP
WHERE SALARY = 50000;
SELECT *
FROM EMP
WHERE SALARY+0 = 50000;

Для виконання наступного запиту також не буде застосовуватися індексне сканування, навіть якщо існує індекс на стовпці SS #:

SELECT *
FROM EMP
WHERE SS# // “” = “111-22-333”;

Індекс не використовується і в тому випадку, коли Oracle-сервер повинен виконувати неявне перетворення даних. У наступному прикладі SALARY є числовим стовпчиком таблиці EMP, і символьне значення перетвориться в числове:

SELECT *
FROM EMP
WHERE SALARY = “50000”;

Якщо відсоток обираних рядків менше або дорівнює 15, індексне сканування буде працювати краще, оскільки в цьому випадку для доступу в рядку потрібно декілька логічних читань блоків з диска, а при повному перегляді за одне логічне читання забезпечується доступ до всіх рядках, які знаходяться в одному блоці. Щоб проілюструвати цю думку, припустимо, що команда ANALYZE застосовується до таблиці EMP і всім її індексами. Oracle генерує наступну статистичну інформацію в таблицях-каталогах USER_TABLES і USER_INDEXES:

Table Statistics:
NUM_ROWS = 1000
BLOCKS = 100
Index Statistics:
BLEVEL = 2
AVG_LEAF_BLOCKS_PER_KEY = 1
AVG_DATA_BLOCKS_PER_KEY = 1

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

При використанні індексу для вибору одного рядка – 3: (BLEVEL + (AVG_LEAF_BLOCKS_PER_KEY – 1) + AVG_DATA_PER_KEY).

При повному перегляді таблиці без індексу – 100.

При використанні індексу для вибору всіх рядків – 3000: (NUM_ROWS * число блоків, читання яких потрібно для вибору одного рядка).

13. Використовуйте ORDER BE для індексного сканування

Оптимізатор Oracle буде використовувати індексне сканування, якщо запит містить розділ ORDER BY із зазначенням індексованого стовпця. Для виконання наступного запиту буде використаний індекс на стовпці EMPID, навіть якщо цей стовпець не використовується в умовах розділу WHERE. Для кожного рядка з індексу буде вилучатись ROWID, а потім з використанням ROWID проводитиметься звернення до рядка.

SELECT SALARY
FROM EMP
ORDER BY EMPID;

Якщо запит буде погано виконуватися, можна спробувати переписати його з використанням вказівки FULL (див. дванадцятий заповідь).


14. Знайте свої дані

Як зазначалося вище, ви повинні бути близько знайомі зі своїми даними. Наприклад, нехай є таблиця з ім’ям BOXER і двома стовпцями BOXER_NAME і SEX. Для стовпця SEX існує неунікальний індекс. Якщо є рівне число боксерів чоловічої і жіночої статі, то наступний запит буде швидше виконаний шляхом повного перегляду таблиці:

SELECT BOXER_NAME
FROM BOXER
WHERE SEX = “F”;

Можна гарантувати такий спосіб виконання, включивши в запит вказівку FULL.

Якщо ж таблиця містить 98% рядків, що відносяться до боксерам-чоловікам, то наступний запит буде виконаний швидше через використання індексного сканування:

SELECT BOXER_NAME –+ INDEX (BOXER SEX)
FROM BOXER
WHERE SEX = “F”;

Цей приклад ілюструє, наскільки важливо знати розподіл даних. Ефективність виконання SQL-запитів буде сильно мінятися при зростанні розмірів бази даних і зміну розподілу даних. В Oracle 7.3 була включена функція HISTOGRAMS, що дозволяє оптимізатору бути в курсі розподілу даних в таблиці і вибирати відповідний план виконання запиту.

15. Знайте, коли використовувати перегляд великих таблиць

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

16. Мінімізуйте число переглядів таблиць

Зазвичай зменшення числа перегляду таблиць в SQL-запитах призводить до підвищення ефективності. Запити з меншим числом переглядів таблиць – більш швидкі запити. Ось приклад. Таблиця STUDENT містить чотири стовпця з іменами NAME, STATUS, PARENT_INCOME і SELF_INCOME. Ім’я є первинним ключем. Значення статус дорівнює 0 для незалежних студентів та 1 – для залежних студентів. Наступний запит повертає імена і величину доходів незалежних і залежних студентів. Форма запиту передбачає два перегляду таблиці STUDENT, створення тимчасової таблиці для подальшої обробки і сортування для усунення дублікатів:

SELECT NAME, PARENT_INCOME
FROM STUDENT
WHERE STATUS = 1
UNION
SELECT NAME, SELF_INCOME
FROM STUDENT
WHERE STATUS = 0;

Той же самий результат буде отриманий при виконанні запиту з одним переглядом таблиці:

SELECT NAME, PARENT_INCOME * STATUS + SELF_INCOME * (1 – STATUS)
FROM STUDENT;

17. З’єднуйте таблиці в правильному порядку

Порядок з’єднання таблиць в запитах із з’єднаннями декількох таблиць має критичне значення. Якщо таблиці з’єднуються в правильному порядку, то загальне число оброблюваних рядків буде менше. Завжди слід виконувати спочатку максимально обмежує пошук, щоб відфільтрувати якомога більше число рядків на ранніх фазах виконання запиту з сполуками. Тоді на наступних фазах з’єднання оптимізатору доведеться мати справу з меншим числом рядків, що підвищить ефективність. Слід переконатися, що головна таблиця (переглядається у зовнішньому циклі з’єднання на основі вкладених циклів) містить найменшу число рядків. При з’єднанні основної та уточнюючої таблиць (наприклад, таблиць ORDERS і ORDER_LINE_ITEMS) переконайтеся, що першою буде основна таблиця; при обробці в зовнішньому циклі уточнюючої таблиці зазвичай буде порушено набагато більше число рядків.

При використанні оптимізатора, заснованого на правилах, головна таблиця повинна вказуватися останньою в списку розділу FROM. Якщо застосовується метод вкладених циклів, слід обміркувати доцільність створення індексу для прискорення пошуку у внутрішньому циклі. Засоби Explain Plan і TKPROF дозволяють отримати інформацію про застосовуваний метод виконання з’єднання, порядок з’єднання таблиць і числі рядків, що обробляються на кожній фазі з’єднання.

У разі застосування оптимізатора на основі оцінок порядок, в якому таблиці вказані в розділі FROM, не є суттєвим для оптимізатора, який завжди намагається знайти найкращий план виконання запиту. Для управління порядком з’єднання таблиць можна використовувати вказівку ORDERED. При виконанні наступного запиту таблиці були б з’єднані в тому порядку, в якому вони вказані в розділі WHERE:

SELECT ORDERS.CUSTID, ORDERS.ORDERNO,
ORDERS_LINE_ITEMS.PRODUCTNO –+ORDERED
FROM ORDERS, ORDERS_LINE_ITEMS
WHERE ORDERS.ORDERNO = ORDER_LINE_ITEMS.ORDERNO;

18. При можливості використовуйте тільки пошук через індекси

Тоді для виконання запитів оптимізатор потребуватиме тільки в пошуку в індексі, а не в таблиці, і ефективність буде краще. Оптимізатор буде використовувати тільки пошук в індексі, якщо вся інформація, необхідна для виконання запиту, міститься в самому індексі. Якщо для таблиці EMP існує складовою індекс на шпальтах LNAME і FNAME, то при виконанні наступного запиту буде використано лише пошук в індексі:

SELECT FNAME
FROM EMP
WHERE LNAME = “SMITH”;

У той же час при виконанні запиту

SELECT FNAME, SALARY
FROM EMP
WHERE LNAME = “SMITH”;

буде проводитися індексне сканування таблиці з доступом до її рядках по ROWID. Так що звертайте увагу на список стовпців в розділі SELECT і включайте в нього тільки ті стовпці, вміст яких дійсно потрібно.

19. Надмірність корисна

Поміщайте в розділ WHERE якомога більше інформації. Наприклад, якщо вказаний розділ WHERE COL1 = COL2 AND COL1 = 10, оптимізатор зможе вивести, що COL2 = 10. Але при завданні розділу у формі WHERE COL1 = COL2 AND COL2 = COL3, оптимізатор не буде вважати, що COL1 = COL3.

20. Намагайтеся писати якомога простіші і тупі оператори SQL

Оптимізатор може не впоратися із занадто складними операторами SQL, іноді написання кількох більш простих операторів дозволяє добитися кращої ефективності, ніж завдання одного складного оператора. Заснований на оцінках оптимізатор СУБД Oracle не є абсолютно стійким. Він знаходиться на стадії розробки і поліпшується в кожному новому випуску системи. Тому слід весь час дивитися на оцінки вартості, що видаються засобом Explain Plan. “Вартість” – це поняття відносне; ніхто не знає, що значить числова оцінка вартості, але чим менше це значення, тим краще ефективність виконання оператора SQL. При налаштуванні оператора потрібно прагнути до зниження цієї оцінки.

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

21. Одного і того ж можна добитися різними способами

У багатьох випадках одні й ті ж результати можуть бути отримані з використанням різних операторів SQL. Для виконання таких операторів можуть застосовуватися різні шляхи доступу. Наприклад, оператор MINUS може виконуватися набагато швидше, ніж запити з WHERE NOT IN (SELECT) або WHERE NOT EXISTS. Припустимо, що є індекси на стовпці STATE і стовпці AREA_CODE. Незважаючи на наявність цих індексів для виконання наступного запиту потрібно повний перегляд таблиці (через використання предиката NOT IN):

SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (“VA”, “DC”, “MD”)
AND AREA_CODE NOT IN (804, 410);

Однак цей запит може бути переписаний з використанням оператора MINUS, що дозволить використовувати індексне сканування:

SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE STATE IN (“VA”, “DC”, “MD”)
MINUS
SELECT CUSTOMER_ID
FROM CUSTOMERS
WHERE AREA_CODE NOT IN (804, 410);

Якщо в розділі WHERE запиту міститься OR, такий запит може бути переписаний із заміною OR на UNION. Перш, ніж зважитися використовувати варіант SQL-запиту, ретельно порівняйте плани виконання всіх можливих варіантів.

22. Використовуйте спеціальні стовпці

Не забувайте про наявність спеціальних стовпців ROWID і ROWNUM. Пам’ятайте, що доступ до рядка по ROWID є найшвидшим. Ось приклад оператора UPDATE, в якому використовується сканування по ROWID:

SELECT ROWID, SALARY
INTO TEMP_ROWID, TEMP_SALARY
FROM EMPLOYEE;
UPDATE EMPLOYEE
SET SALARY = TEMP_SALARY * 1.5
WHERE ROWID = TEMP_ROWID;

Значення ROWID в базі даних не є константою, тому не задавайте явних значень ROWID в операторах SQL і додатках.

Використовуйте ROWNUM для обмеження числа рядків, що повертаються запитом. Наступний оператор не поверне більше 100 рядків:

SELECT EMPLOYEE.SS#, DEPARTMENT.DEPT_NAME
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
AND ROWNUM < 100;

23. Явні курсори переважніше неявних

При використанні неявних курсорів потрібно зайве читання. Для роботи з явними курсорами використовуються оператори SQL DECLARE, OPEN, FETCH і CLOSE. Неявні курсори в СУБД Oracle відкриваються для операторів DELETE, UPDATE, INSERT та SELECT.

24. Дослідіть можливості опції паралельного виконання запитів і використовуйте її переваги

Ця опція дає можливість паралельного виконання операторів SQL з метою прискорення. В Oracle7 паралельно могли виконуватися тільки запити з повним переглядом таблиці. В Oracle8 можуть бути распараллелен і запити з індексним скануванням в заданому діапазоні значень ключа, якщо індекс є розділеним. Опція можна використовувати тільки в системах SMP і MPP з кількома дисковими пристроями. У сервері Oracle є багато можливостей, але наявність цих можливостей само по собі не гарантує підвищену ефективність. Необхідно відповідним чином конфігурувати базу даних і спеціально оформляти оператори SQL. Наприклад, наступний оператор SQL міг би бути виконаний паралельно:

SELECT *  –+PARALLEL(ORDERS,6)
FROM ORDERS;

25. Скорочуйте мережевий трафік і збільшуйте пропускну здатність мережі

Використання обробки масивів і блоків PL / SQL може підвищити ефективність і знизити мережевий трафік. Обробка масивів дозволяє за допомогою одного оператора SQL обробити кілька рядків. Наприклад, використання масивів в операторі INSERT дозволяє за одне звернення до сервера занести в таблицю 1000 рядків. Використання великого числа операторів SQL перевантажує мережевий трафік. Однак, якщо оператори SQL містяться в одному блоці PL / SQL, то можна послати весь блок на Oracle-сервер, обробити їх і отримати результати на стороні клієнта.


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


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

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

Ваш отзыв

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

*

*