Підказки оптимізатору запитів
Delphi site: daily Delphi-news, documentation, articles, review, interview, computer humor.
У СУБД MySQL є ряд підказок оптимізатору запитів, якими можна скористатися, щоб вплинути на вибір плану виконання, якщо той, що запропонований оптимізатором, вас не задовольняє. Нижче наведено їх перелік з рекомендаціями, коли є сенс застосовувати цю підказку. Підказка включається до запиту, чий план виконання ви хочете змінити, та діє лише для цього запиту. Точний синтаксис підказок можна знайти в документації MySQL. Деякі їх залежать від номера версії СУБД.
HIGH_PRIORITY та LOW_PRIORITY
Кажуть, який пріоритет призначити цій команді щодо інших команд, які намагаються звернутися до тих же таблиць. Підказка HIGH_PRIORITY означає, що MySQL повинен помістити команду SELECT у чергу раніше за всіх інших, які очікують отримання блокувань для модифікації даних. Іншими словами, команда SELECT має бути виконана якнайшвидше, а не чекати своєї черги. Ця підказка може бути застосована і до команди INSERT; у разі вона просто скасовує дію глобального параметра LOW_ PRIORITY, встановленого лише на рівні сервера.
Підказка LOW_PRIORITY надає зворотне дію: у разі команда чекатиме завершення решти команд, бажаючих звернутися до тим самим таблицям, навіть якщо їх було відправлено пізніше. Можна провести аналогію із зайво ввічливою людиною, яка притримує двері до ресторану, поки є охочі увійти; так він заморить собі голодом! Ця підказка застосовується до команд SELECT, INSERT, UPDATE, REPLACE.
Обидві підказки працюють із підсистемами зберігання, в яких реалізовано блокування на рівні таблиць, але в InnoDB та інших підсистемах з більш детальним контролем блокування таКонкурентного доступу необхідності в них виникати не повинно. Будьте обережні, застосовуючи їх до таблиць типу MyISAM, оскільки таким чином можна заборонити одночасні вставки та суттєво знизити продуктивність.
Підказки HIGH_PRIORITY та LOW_PRIORITY часто розуміють неправильно. Їх сенс не в тому, щоб виділити запиту більше ресурсів, щоб «сервер приділив йому більше уваги», або поменше, щоб «сервер не перетруджувався». Вони просто впливають на дисципліну обслуговування черги команд, які чекають на доступ до таблиці.
Ця підказка застосовується до команд INSERT та REPLACE. Команда з цією підказкою повертає управління негайно, а рядки, що підлягають вставці, поміщаються в буфер і будуть реально вставлені всі відразу, коли таблиця звільниться. Найчастіше це буває корисно для протоколювання та аналогічних додатків, у яких потрібно записувати багато рядків, не змушуючи клієнта чекати і не виконуючи операцію вводу/виводу кожної команди окремо. Однак цей режим має багато обмежень; так, відкладена вставка реалізована в усіх підсистемах зберігання, а функція LAST_INSERT_ID( ) у разі неприменима.
Ця підказка може зустрічатися відразу після ключового слова SELECT у команді SELECT або в будь-якій іншій команді між двома таблицями, що з'єднуються. У першому випадку вона каже серверу, що зазначені у запиті таблиці потрібно з'єднувати як перерахування. У другому випадку вона визначає порядок з'єднання таблиць, між якими знаходиться.
Підказка STRAIGHT_JOIN корисна, якщо вибраний MySQL порядок з'єднання не є оптимальним або оптимізатор витрачає надто багато часу на вибір порядку. В останньому випадку потік занадто багато часу проводить у стані «Statistics», а, увімкнувши цю підказку, можна буде скоротитипростір пошуку оптимізатора.
За допомогою команди EXPLAIN ви можете подивитися, який порядок вибрав оптимізатор, а потім переписати запит, розташувавши таблиці саме в цьому порядку та додавши підказку STRAIGHT_JOIN. Вказана ідея є непоганою, якщо ви впевнені, що фіксований порядок не призведе до зниження продуктивності для деяких умов WHERE. Однак, не забувайте переглядати такі запити після переходу на нову версію MySQL, оскільки можуть з'явитися інші оптимізації, що пригнічуються наявністю STRAIGHT_JOIN.
SQL_SMALL_RESULT та SQL_BIG_RESULT
Ці підказки можна застосовувати лише до команди SELECT. Вони говорять оптимізатору, коли і як використовувати тимчасові таблиці або сортування при виконанні запитів з GROUP BY або DISTINCT. SQL_SMALL_ RESULT означає, що результуючий набір буде невеликий, тому його можна помістити в індексовану тимчасову таблицю, щоб не сортувати для групування. Навпаки, SQL_BIG_RESULT означає, що результат великий, і краще використовувати часові таблиці на диску з наступним сортуванням.
Ця підказка каже оптимізатору, що результати потрібно помістити в тимчасову таблицю і якнайшвидше звільнити табличні блокування. Це зовсім не те, що буферизація на стороні клієнта, описана раніше в розділі «Клієнт-серверний протокол MySQL» на стор. і водночас швидко звільнити блокування. Компроміс у тому, що тепер замість пам'яті клієнта споживається пам'ять сервера.
SQL_CACHE та SQL_NO_CACHE
Ці підказки говорять серверу про те, що цей запит є чи не є кандидатом на помешкання в кеш.запитів. Про те, як ними користуватися, розказано у наступному розділі.
Ця підказка змушує MySQL обчислити весь результуючий набір, навіть якщо є фраза LIMIT, що обмежує кількість рядків, що повертаються. Отримати загальну кількість рядків дозволяє функція FOUND_ROWS( ) (див. однак розділ «Оптимізація SQL_CALC_ FOUND_ROWS» на стор. 249, де йдеться про те, чому не слід скористатися цією підказкою).
FOR UPDATE та LOCK IN SHARE MODE
Ці підказки керують блокуваннями для команд SELECT, але тільки в тих підсистемах зберігання, де блокування реалізовані на рівні рядків. Вони дозволяють поставити блокування на знайдені рядки, що буває корисним, коли заздалегідь відомо, що ці рядки потрібно буде оновити, або щоб уникнути ескалації і відразу отримати монопольні блокування.
Коли писалася ця книга, лише підсистема InnoDB дозволяла використовувати дані підказки, і поки що занадто рано говорити, чи підтримають їх у майбутньому інші підсистеми зберігання з блокуванням.
ми рядків. Застосовуючи ці підказки в InnoDB, майте на увазі, що вони пригнічують деякі оптимізації, наприклад індекси, що покривають. InnoDB не може монопольно заблокувати рядки, не звертаючись до індексу первинного ключа, в якому зберігається інформація про версії рядків.
USE INDEX, IGNORE INDEX та FORCE INDEX
Ці підказки говорять оптимізатору про те, які індекси використовувати або ігнорувати при пошуку рядків у таблиці (наприклад, для розробки рішення про порядок з'єднання). У версії MySQL 5.0 і раніше вони не впливають на вибір сервером індексів для сортування і групування. У MySQL 5.1 можна доповнити підказку ключовими словами FOR ORDER BY або FOR GROUP BY.
FORCE INDEX - те саме, що USE INDEX, але ця підказка повідомляє оптимізатору про те,що сканування таблиці обійдеться набагато дорожче пошуку за індексом, навіть якщо індекс не дуже корисний. Ви можете включити дані підказки, якщо вважаєте, що оптимізатор вибрав невідповідний індекс, або якщо хочете з якоїсь причини скористатися конкретним індексом, наприклад, для неявного впорядкування без використання ORDER BY. У розділі «Оптимізація LIMIT^ зміщенням» (стор. 248) був наведений приклад, який показує, як можна ефективно отримати мінімальне значення за допомогою фрази LIMIT.
У версії MySQL 5.0 і пізніших існують кілька системних змінних, які впливають поведінка оптимізатора.
Ця змінна каже оптимізатору, наскільки вичерпно досліджувати часткові плани. Якщо запит надто довго перебуває у стані «Statistics», спробуйте зменшити це значення.
Ця змінна, яка за замовчуванням увімкнена, дозволяє оптимізатору пропускати деякі плани в залежності від кількості досліджених рядків.
Обидві змінні контролюють режим скороченого перебору планів виконання. Таке «зрізання кутів» буває корисним для підвищення продуктивності при обробці складних запитів, але загрожує тим, що задля досягнення ефективності сервер може пропустити оптимальний план. Тому іноді має сенс міняти їх значення.