Видавництво - Відкриті Системи, Журнал - SQL Magazine OnLine - #02
Дюзан ПетковикДекілька порад, заснованих на глибокому аналізі механізму роботи SQL Server, які дозволяють підвищити продуктивність баз даних.
Не так просто визначити, що зможе забезпечити кращу продуктивність бази даних: сканування таблиці або доступ із використанням індексів. Частково відповідь залежить від того, який відсоток від загальної кількості записів у таблиці становить кількість рядків, що повертаються. Але є ще один важливий фактор, тип індексу, який ви збираєтеся використовувати: кластеризований або некластеризований. Оптимізатор запитів у SQL Server 7.0 не завжди вибирає найшвидший метод. Автор статті провів кілька експериментів із метою зрозуміти логіку роботи оптимізатора запитів. Своїми міркуваннями він ділиться із читачем у цій статті.
Сканування таблиць та індексний доступ
Робота оптимізатора запитів у SQL Server полягає у виборі найкращого способу виконання запиту. Для прискорення обробки запитів оптимізатор використовує індекси. У тих випадках, коли таблиця не має індексів, або коли оптимізатор вирішує не використовувати існуючі один або кілька індексів, система проводить сканування таблиці.
Індексний доступ є методом доступу, при якому SQL Server застосовує наявний індекс для читання сторінок таблиці та запису в них. Оскільки індексний доступ суттєво скорочує кількість операцій введення/виводу, часто при його використанні продуктивність бази даних виявляється набагато вищою, ніж при скануванні таблиці.
Кластеризовані та некластеризовані індекси
При використанні в операторі CREATE INDEX параметр NONCLUSTERED для таблиці визначаєтьсянекластеризований індекс (саме такий індекс створюється за умовчанням). Некластеризований індекс має таку ж структуру, як кластеризований, але з двома важливими відмінностями. Перше – некластеризовані індекси не змінюють фізичний порядок розташування рядків у таблиці. Друге - листя некластеризованих індексів містить ключ індексу та закладку.
Закладка завжди показує, де шукати рядок, який відповідає ключу некластеризованого індексу. Закладка некластеризованого індексу може бути представлена у двох формах залежно від того, чи є у таблиці кластеризований індекс. Якщо для іншого стовпця таблиці, що розглядається, побудований кластеризований індекс, то закладка некластеризованого індексу вказує на структуру В-дерева кластеризованого індексу таблиці. Якщо ж таблиця не має кластеризованого індексу, то закладка ідентична ідентифікатору рядка RID (Relative Identifier).
SQL Server застосовує некластеризовані індекси для пошуку даних одним із двох можливих способів. Якщо таблиця не впорядкована (тобто, у неї немає кластеризованого індексу), то SQL Server спочатку проходить по всьому некластеризованому індексу, а потім використовує ідентифікатор рядка для знаходження самого рядка. Якщо ж таблиця має кластеризований індекс, то SQL Server спочатку простежує некластеризований індекс, а потім здійснює подорож структурою кластеризованого індексу таблиці. Коли SQL Server застосовує кластеризований індекс для пошуку даних, він стартує з кореневої вершини відповідного дерева. Після трьох чи чотирьох операцій читання він доходить до листової вершини, де і зберігаються самі дані. Внаслідок цього пошук з використанням кластеризованого індексу відбувається набагато швидше, ніж пошук за індексною структурою відповідногонекластеризованого індексу.
Виходячи з наведеної вище інформації про сканування таблиць та індексний доступ із застосуванням кластеризованих або некластеризованих індексів, можна дійти наступного висновку. Не можна прямолінійно зробити однозначний висновок щодо того, який метод швидший. При виборі найкращого з погляду продуктивності способу пошуку, коли вибирається або сканування таблиці, або індексний доступ, одним із найважливіших факторів є тип індексу (кластеризований або некластеризований).
Для проведення експериментів слід спочатку створити некластеризований індекс за допомогою наступного оператора Transact-SQL (T-SQL):
CREATE INDEX i_orders_orderid ON orders(orderid)
Для другого тесту спочатку слід ліквідувати існуючий некластеризований індекс, а потім створити кластеризований індекс, використовуючи наступний оператор T-SQL:
DROP INDEX orders.i_orderds_orderid CREATE CLUSTERED INDEX c_orderid ON orders(orderid)
Порівняння сканування таблиці та доступу із застосуванням некластеризованого індексу
Результат порівняння продуктивності при скануванні таблиці та у разі використання доступу через некластеризований індекс залежить від ряду факторів: розміру таблиці, схеми зберігання рядків та від кількості рядків, що повертаються у відповідь на запит (у відсотках від загальної кількості рядків у таблиці). По-перше, тому що для великої таблиці при скануванні виграш у часі, зумовлений завчасним читанням інформації, може бути значно більшим, ніж для середніх та невеликих таблиць. Частка повернення вимірюється відношенням рядків, що повертаються до загальної кількості рядків у таблиці. Для великої таблиці сканування стає ефективним при менших значеннях такої частки, ніж длясередньої чи малої таблиці. По-друге, логічна обробка операцій введення/виводу (тобто з використанням некластеризованого індексу) залежить від фізичного розташування рядків на диску. Якщо рядки розкидані за великим обсягом, то кількість операцій введення/виводу буде значною, а відсоток рядків, що повертаються, буде нижче, ніж при компактному розміщенні рядків на диску. Нагадаємо, що високий відсоток рядків, що повертаються, обумовлює більш високу ефективність сканування в порівнянні з використанням некластеризованого індексу. По-третє, довжина рядка впливає те, скільки рядків поміститься однією сторінці даних. У тих випадках, коли на одній сторінці міститься велика кількість рядків, операції введення/виводу при скануванні таблиці виконуються значно продуктивніше, ніж при малій кількості сторінок.
Для покращення продуктивності можна використовувати у запиті підказку INDEX (SQL Server пропонує кілька підказок, які можна застосовувати у запитах для вказівки оптимізатору, що слід використовувати певний індекс). Оператор T-SQL з підказкою INDEX(i_orders_orderid) змусить SQL Server використовувати існуючий некластеризований індекс i_orders_orderid, як показано на екрані 1.
Екран 1 демонструє типовий план виконання запиту із застосуванням некластеризованого індексу, в якому передбачено крок використання Bookmark Lookup. (Нагадаємо, що рівень листя некластеризованого індексу містить ключ індексу та закладку, яка показує, де SQL Server повинен шукати рядок, що відповідає зазначеному ключу.) На екрані 2 показаний план виконання запиту із застосуванням сканування таблиці. Оптимізатор спочатку вважав за краще проводити сканування таблиці. Але після того, як у запит було введено підказку INDEX,оптимізатор почав використовувати індексний доступ. Виборчість в обох випадках становила приблизно 10%.
Час обробки запиту з використанням індексного доступу виявився дещо кращим, ніж при використанні іншого виду пошуку. Як видно на екрані 2, оптимізатор вирішив виконати сканування таблиці, хоча був некластеризований індекс.
Порівняння сканування таблиці та доступу через кластеризований індекс
SELECT * FROM orders (index (0)) WHERE orderid BETWEEN 11000000 і 88000000
У загальному випадку оптимізатор запитів вибирає план виконання, який забезпечує найбільш швидку обробку запиту. Але він не завжди бездоганний. Для часто використовуваних та складних запитів необхідно провести тестування у певному діапазоні вибірковості, метою якого буде перевірка правильності вибору оптимізатора. Для кластеризованих індексів при вибірковості запиту вище 75 відсотків слід порівняти час виконання запиту при індексному доступі та при скануванні таблиці. Тест дасть відповідь на питання, що працює швидше. Для некластеризованих індексів з'ясовувати, який метод доступу (сканування таблиці або доступ із застосуванням індексу) забезпечує більш високу продуктивність, слід за вибірковістю запиту в межах від 5 до 10 відсотків. Автор статті сподівається, що наведені в цій статті поради та підказки допоможуть читачам досягти найкращих показників продуктивності баз даних.