Оптимізатор запитів
Delphi site: daily Delphi-news, documentation, articles, review, interview, computer humor.
Тепер, коли дерево розбору ретельно перевірено, настає черга оптимізатора, який перетворює його на план виконання запиту. Часто існує безліч способів виконати запит, і всі вони дають той самий результат. Завдання оптимізатора - вибрати найкращий із них.
У MySQL використовується вартісний оптимізатор, який намагається передбачити вартість різних планів виконання та вибрати з них найдешевший. Як одиниця вартості приймаються витрати на зчитування випадкової сторінки даних розміром 4 Кбайт. Щоб дізнатися, як оптимізатор оцінив запит, виконайте запит, а потім подивіться на сеансову змінну Last_query_cost:
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
mysql> SHOW STATUS LIKE 'last_query_cost';
Цей результат означає, що згідно з оцінкою оптимізатора для виконання запиту потрібно буде виконати приблизно 1040 випадкових читань сторінок даних. Оцінка обчислюється з урахуванням різної статистичної інформації: кількість сторінок у таблиці чи індексі, кардинальність (кількість різних значень) індексу, довжина рядків і ключів, розподіл ключів. Оптимізатор не зважає на вплив кешування - передбачається, що будь-яке читання зводиться до операції дискового вводу/виводу.
Не завжди оптимізатор обирає найкращий план, тому є багато причин.
• Некоректна статистика. Сервер отримує статистичну інформацію від підсистеми зберігання, і тут є безліч варіантів: від абсолютно вірних до тих, хто не має нічого спільного з дійсністю. Наприклад, підсистема зберігання InnoDB не веде точну статистику кількості рядків у таблиці, так вже влаштована архітектура багатоверсійногоуправління конкурентним доступом (MVCC).
• Прийнята метрика вартості не завжди еквівалентна істинній вартості виконання запиту, тому навіть коли статистика точна, запит може виявитися дорожчим або дешевшим за оцінку MySQL. У деяких випадках план, який передбачає читання більшої кількості сторінок, буде дешевшим, тому, наприклад, що читання з диска виконується послідовно або сторінки вже знаходяться в пам'яті.
• Подання MySQL про те, що таке «оптимально», може розходитися з вашим поданням. Ви, ймовірно, хочете отримати результат якнайшвидше, але для MySQL поняття «швидко» не існує, він оперує лише «вартістю», а обчислення вартості, як ми щойно бачили, - неточна наука.
• MySQL не бере до уваги інші запити, що одночасно виконуються, а вони можуть вплинути на час обробки оптимізованого.
• MySQL не завжди виконує оптимізацію вартості. Іноді він просто слідує правилам, наприклад: «якщо запит містить фразу MATCH(), то використовується повнотекстовий індекс, якщо такий існує». Подібне рішення буде прийнято, навіть якщо швидше було б скористатися іншим індексом та неповнотекстовим запитом із фразою WHERE.
• Оптимізатор не враховує вартість операцій, які йому непідконтрольні, наприклад виконання функцій, що зберігаються або визначені користувачем.
• Пізніше ми побачимо, що не завжди оптимізатор може розглянути всі можливі плани виконання, тому оптимальний план він може просто не побачити.
Оптимізатор запитів MySQL - це дуже складний код, в якому для перетворення запиту в план виконання застосовується багато різних операцій. Але існує лише два основні види оптимізації: статична та динамічна. Для виконання статичної оптимізаціїдосить лише дослідження дерева розбору. Наприклад, оптимізатор може перетворити фразу WHERE на еквівалентну форму, застосовуючи алгебраїчні правила. Статична оптимізація не залежить від конкретних значень, таких як константи за умови WHERE. Будучи один раз проведена, статична оптимізація завжди залишається чинною, навіть якщо запит буде повторно виконаний з іншими значеннями. Можна вважати, що це оптимізація на етапі компіляції.
З іншого боку, динамічні оптимізації залежать від контексту і можуть визначатися багатьма факторами, скажімо, конкретним значенням WHERE або кількістю рядків в індексі. Їх доводиться знову обчислювати при кожному виконанні запиту. Можна вважати, що це "оптимізація на етапі виконання".
Ця відмінність важлива при виконанні підготовлених (prepared) команд і процедур, що зберігаються. MySQL може зробити статичну оптимізацію одноразово, але динамічні оптимізації повинні заново обчислюватися при кожному виконанні запиту. Іноді MySQL навіть повторно здійснює оптимізацію під час виконання запиту 1 .
Нижче наведено кілька типів оптимізації, що підтримуються в MySQL.
Зміна порядку з'єднання Таблиці не обов'язково з'єднувати саме в порядку, зазначеному в запиті. Визначення найкращого порядку з'єднання - важлива оптимізація; Докладніше ми розглянемо нижче в розділі «Оптимізатор з'єднань» на стор. 226.
Перетворення OUTER JOIN на INNER JOIN
Оператор OUTER JOIN необов'язково виконувати як зовнішнє з'єднання. За певних умов, залежать, наприклад, від фрази WHERE і схеми таблиці, запит із OUTER JOIN еквівалентний запиту з INNER JOIN. MySQL вміє розпізнавати та переписувати такі запити, після чого вони можуть бути піддані оптимізації типу"Зміна порядку з'єднання".
Застосування алгебраїчних правил еквівалентності
MySQL застосовує перетворення алгебри для спрощення виразів і приведення їх до канонічного вигляду. Вона вміє також обчислювати константні вирази, виключаючи свідомо невиконні і завжди виконувані умови. Наприклад, терм (5=5 AND a>5) приводиться до більш простого: a>5. Аналогічно умова (a 5 AND b = c AND a = 5. Ці правила дуже корисні при написанні умовних запитів, про що йтиметься нижче в цьому розділі.
Оптимізації COUNT(), MIN() та MAX()
Наявність індексів та відомостей про можливість зберігання NULL-значень у стовпцях часто дозволяє взагалі не обчислювати ці вирази. Наприклад, щоб знайти мінімальне значення в стовпці, який є найлівішою частиною ключа індексу типу B-Tree, MySQL може запросити перший рядок з цього індексу. Це можна навіть на стадії оптимізації і далі розглядати отримане значення як константу. Аналогічно для пошуку максимального значення в індексі типу B-Tree сервер зчитує останній рядок. Якщо застосовано таку оптимізацію, то в плані, виведеному командою EXPLAIN, буде присутня фраза «Select tables optimized away» (деякі таблиці виключені при оптимізації). Це означає, що оптимізатор повністю виключив таблицю із плану виконання, підставивши замість неї константу.
Подібним чином деякі підсистеми зберігання можуть оптимізувати запити, що містять COUNT(*) без фрази WHERE (наприклад, MyISAM, де кількість рядків у таблиці завжди відома точно). Для отримання додаткових відомостей див. «Оптимізація запитів з COUNT()» цього розділу на стор. 242.
Обчислення та згортка константних виразів Якщо MySQL виявляє, що вираз можна згорнути в константу, то робитьце стадії оптимізації. Наприклад, певну змінну можна перетворити на константу, якщо вона не змінюється в запиті. Іншим прикладом можуть бути арифметичні висловлювання.
Як не дивно, навіть такі речі, які ви, швидше за все, назвали б запитом, можна звернути до константи під час оптимізації. Наприклад, обчислення функції MIN() за індексом. Цей приклад можна навіть узагальнити на пошук констант за первинним ключем або за унікальним індексом. Якщо у фразі WHERE зустрічається константна умова такого індексу, то оптимізатор знає, що MySQL міг би знайти значення на початку виконання запиту. Згодом знайдене значення можна трактувати як константу. Наведемо приклад:
mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id -> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
MySQL виконує цей запит у два етапи, про що свідчать два рядки у виведеній таблиці. На першому етапі знаходиться потрібний рядок у таблиці film. Оптимізатор MySQL знає, що такий рядок єдиний, оскільки стовпець film_id – це первинний ключ. Так як оптимізатор відома точна величина (значення у фразі WHERE), яка буде повернена в результаті пошуку, то в стовпці ref для цієї таблиці варто const.
На другому кроці MySQL вважає стовпець film_id з рядка, знайденого на першому кроці, відомою величиною. Він може так вчинити, тому що в момент переходу до другого кроку оптимізатор вже знає усі значення, визначені раніше. Зазначимо, що тип ref для таблиці film_actor дорівнює const так само, як і для таблиці film. Константні умови можуть застосовуватися також внаслідок поширення "константності" значення з одного місця в інше за наявності фрази WHERE, USING або ON зобмеженням типу "рівно". У наведеному вище прикладі фраза USING гарантує, що значення film_id буде однаково протягом усього запиту - воно має бути рівним константі, заданій у фразі WHERE.
Якщо індекс містить всі необхідні запиту стовпці, то MySQL може скористатися ним, взагалі не читаючи дані таблиці. Ми докладно розглядали індекси, що покривають, у розділі 3.
MySQL вміє перетворювати деякі види підзапитів на більш ефективні еквівалентні форми, зводячи їх до пошуку за індексом.
MySQL може припинити обробку запиту (або якийсь крок обробки), як зрозуміє, що цей запит або крок повністю виконано. Очевидний приклад - фраза LIMIT, але є ще кілька випадків раннього завершення. Наприклад, зустрівши свідомо нездійсненну умову, MySQL може припинити обробку всього запиту. Погляньте на наступний приклад:
mysql> EXPLAIN SELECT film.film_ >
Цей запит зупинено на кроці оптимізації, але іноді MySQL перериває запит невдовзі після його виконання. Сервер може застосувати таку оптимізацію, коли підсистема виконання приходить до висновку, що потрібно витягувати тільки значення, що різняться, або зупинитися, якщо значення не існує. Наприклад, наступний запит знаходить усі фільми, в яких немає жодного актора: 1
mysql> SELECT film.film_id -> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL;
Запит виключає всі фільми, в яких є хоч один актор. У фільмі може бути задіяно багато акторів, але, виявивши першого, сервер припиняє обробку поточного фільму і переходить до наступного, оскільки знає, що умові WHERE такий фільм не задовольняє.Подібну оптимізацію «розрізняються/не існує» можна застосувати до деяких запитів, які включають оператори DISTINCT, NOT EXISTS( ) та LEFT JOIN.
MySQL розпізнає ситуації, коли у певному запиті два стовпці мають бути рівними, - наприклад, в умови JOIN, і поширює умову WHERE на еквівалентні стовпці. Зокрема, наступний запит
mysql> SELECT film.film_id -> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id > 500;
демонструє MySQL, що умова WHERE застосовується не тільки до таблиці film, але і до таблиці film_actor, оскільки в силу фрази USING обидва стовпці повинні збігатися.
Якщо ви звикли до іншої СУБД, в якій така оптимізація не реалізована, то вам, ймовірно, рекомендували «допомогти оптимізатору», самостійно задавши у фразі WHERE умови для обох таблиць, наприклад:
. WHERE film.film_id > 500 AND film_actor.film_id > 500
MySQL це необов'язково і лише ускладнює супровід запитів.
Порівняння за списком IN( )
Багато СУБД оператор IN() - лише синонім кількох умов OR, оскільки логічно вони еквівалентні. Але не MySQL, тут перелічені в списку Щ)значення сортуються, і для роботи з ним застосовується швидкий двійковий пошук. Обчислювальна складність при цьому становить O(log n), де n - розмір списку, тоді як складність еквівалентної послідовності умов OR дорівнює O(n) (тобто набагато повільніше для великих списків).
Наведений вище перелік, звичайно, неповний, тому що MySQL вміє застосовувати набагато більше оптимізацій, ніж вмістилося б у всьому цьому розділі, але уявлення про складність та витонченість оптимізатора ви все ж таки отримали. Головна думка, яку слід винести зцього обговорення, - не намагайтеся перехитрити оптимізатор. Зрештою, ви просто зазнаєте невдачі або зробите свої запити надмірно заплутаними і важкими для супроводу, не отримавши жодної вигоди. Дозвольте оптимізатору займатися своєю справою.
Зрозуміло, хоч би яким «розумним» був оптимізатор, іноді він не знаходить найкращий результат. Буває так, що ви знаєте про свої дані щось таке, про що оптимізатору невідомо, наприклад, якась умова, яка гарантовано істинна внаслідок логіки програми. Крім того, оптимізатор не наділений деякою функціональністю, наприклад хеш-індексами, а часом алгоритм оцінки вартості вибирає план, який виявляється дорожчим, ніж можлива альтернатива.
Якщо ви впевнені, що оптимізатор дає поганий результат і знаєте чому, то можете йому допомогти. Варіантів тут кілька: включити у запит підказку (hint), переписати запит, перепроектувати схему чи додати індекси.