Як писати криві запити з неоптимальним планом і змусити задуматися СУБД


Все просто. Тут можна знайти «Основи розбору запитів для чайників» у разі PostgreSQL та чудові невигадані приклади з продакшену про те, як не треба писати запити на PostgreSQL і MySQL і що буває, якщо їх так все-таки писати.



Введення
Розповім про кілька очевидних речей, які досить добре описані у манах та документації, яку читають, зазвичай, після вступу на десяток грабель, або після відстрілу певного числа кінцівок або після іншого членоушкодження.

Частин буде кілька, логічно вони слабо пов'язані один з одним, але всі вони зустрілися в ході вирішення різних потреб бізнесу і так чи інакше задовольняли його потреби.

Нюанси роботи WITH
Та сама штука WITH, яка дуже схожа на синтаксичний цукор без великого смислового навантаження і асоціюється (у непосвячених) з розбиттям великий онучі на окремі методи в дусі Мартіна і Фаулера. Тут головна особливість полягає в тому, що це ніфіга не аналог методу/функції, особливо коли справа доходить до оптимізації запиту.

Відразу прошу вибачення у читача, але далі в тексті будуть тільки шматки запитів, які мають принципове значення, запити цілком публікуватися не будуть. По-перше, щоб не втомлювати особливостями структури даних і по-друге, щоб я випадково не запостив що-небудь приватно-корпоративне. Якщо шматки будуть абсолютно не читані, будь ласка, не бийте сильно, а запропонуйте їх облагородити. Спасибі.

Як робити не треба.
вихідний шматок sql з основного тіла запиту
LEFT JOIN specifications_history AS specification_history
ON specification_history.id = specification_detail.entity_history_id
AND specification_history.specification_id = ANY(specification_parts.ids)
LEFT JOIN specification_revision_details AS specification_section_detail
ON specification_section_detail.specification_revision_id = specification_revision.id
AND specification_section_detail.entity_type = 1002
LEFT JOIN specification_sections_history AS specification_section_history
ON specification_section_history.id = specification_section_detail.entity_history_id
LEFT JOIN specification_revision_details AS section_item_detail
ON section_item_detail.specification_revision_id = specification_revision.id
AND section_item_detail.entity_type = 1003
LEFT JOIN section_items_history AS section_item_history
ON section_item_history.id = section_item_detail.entity_history_id


'облагороджений' шматок запиту
WITH revision_products AS (
SELECT DISTINCT specification_revision.id AS revision_id,
specification_history.specification_id AS specification_id,
section_item_history.product_id AS product_id
FROM specification_revisions AS specification_revision
INNER JOIN specification_revision_details AS specification_detail
ON specification_detail.specification_revision_id = specification_revision.id
AND specification_detail.entity_type = 1001
INNER JOIN specifications_history AS specification_histor
ON specification_history.id = specification_detail.entity_history_id
INNER JOIN specification_revision_details AS specification_section_detail
ON specification_section_detail.specification_revision_id = specification_revision.id
AND specification_section_detail.entity_type = 1002
INNER JOIN specification_sections_history AS specification_section_history
ON specification_section_history.id = specification_section_detail.entity_history_id
INNER JOIN specification_revision_details AS section_item_detail
ON section_item_detail.specification_revision_id = specification_revision.id
AND section_item_detail.entity_type = 1003
INNER JOIN section_items_history AS section_item_history
ON section_item_history.id = section_item_detail.entity_history_id
WHERE section_item_history.product_id IS NOT NULL
)


Тут сталося таке: з основного тіла запиту, в якому було чимало LEFT JOIN їх забрали у WITH і перетворили в INNER JOIN. Шматку видали милозвучна назва, щоб таким чином підвищити читаність основного тіла, а всі деталі реалізації потягли подалі. Практики чистого коду в кращому вигляді. З читаністю, дійсно, стало краще. В основному тілі запиту залишилося 5 джоинов замість 10. Ось тільки швидкість виконання запиту тут же впала з 75мс до 95сек. У Explain з'явилися цікаві речі виду:

-> Unique (cost=796821.66..848031.33 rows=5120967 width=12) (actual time=80769.666..94946.622 rows=315260 loops=1)
-> Sort (cost=796821.66..809624.07 rows=5120967 width=12) (actual time=80769.663..90662.993 rows=37659600 loops=1)
Sort Key: specification_revision_1.id specification_history.specification_id, section_item_history.product_id
Sort Method: external merge Disk: 809888kB

Тобто хтось взяв 37 лямів рядків і почав їх бадьоро сортувати 1 гіге пам'яті. Відразу виникли питання:
  • «а звідки у нас 37кк рядків, коли найбільшою з таблиць їх 1.5 кк?»
  • «ми не змінювали алгоритм, ми тільки код читабельним зробили, чому все повисло?»
  • «він декларативний, ми сказали, що хочемо, а як не казали, чому все зламалося?»
Відповідь: перенесення джоинов з основного тіла у WITH зробив рівно те, що описано в документації:
WITH Queries (Common Table Expressions)A useful of property WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another application is possible to prevent unwanted multiple evaluations of functions with side-effects. However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary subquery. The WITH query will generally be evaluated written as, without suppression of rows that the parent query might discard afterwards. (But, as mentioned above, evaluation might stop early if the reference(s) to the query demand only a limited number of rows.)

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

Тобто ми левелапнули шматок запиту до рівня самостійної частини, забувши додати до нього важливі умов з WHERE, які урізають вибірку по самі помідори. В результаті переджойнили всю базу, а потім віддали цього монстрика в основне тіло, де взяли з нього десяток рядків.

У конкретному вищеописаному випадку WHERE була умова виду «product_id = 1234», яке і задавало основне обмеження за даними. Якщо б це умова потягли у WITH, то все продовжило б працювати приблизно з тією ж швидкістю. Однак, так можна зробити тільки у випадку статичного значення для правої частини умови. Якщо айдишних виходить, наприклад, в ході рекурсивного запиту, то WITH така умова не потягнеш і ідея з поділом запиту на шматки буде безбожно гальмувати.

Висновок:
  • потрібно читати документацію;
  • не всі практики розробки однаково корисні в різних областях цієї самої розробки.


Візуалізація explain
Думаю, всі в курсі про explain.depesz.com. Там красиво показують що не так із запитом.



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

Тут хочеться сказати пару слів по кожній з колонок і пояснити як вони впливають на результат виконання. Таки так, це написано там же хелпе, але рідко хто читає хелп, поки не припре.

  • # — просто порядковий номер операції в ході виконання запиту
  • Exclusive — час на виконання конкретної операції (у мілісекундах)
  • Inclusive — час на виконання усього конвеєра команд (наприклад, на картинці вище, для виконання uniq потрібно зробити, як мінімум, сортування)
  • Rows X — на скільки промахнувся Акелла планувальник, коли ванговал кількість рядків, які повинна повернути операція (таки так, це важливо для подальшого прийняття рішень про те, як же далі виконувати запит)


Поради початківцям оптимізаторам
Якщо все гальмує і не знаєте з чого почати, то ось декілька порад. Потрібно взяти розфарбований explain (бажано разом з analyze) з попереднього пункту і подивитися на нього. Найчастіше, проблема (читай, 80%+), зосереджена в одній з операцій, описаних в плані виконання. Тобто за Exclusive/Inclusive знайти саме темне і непевне місце. Знову ж таки, в наведеному вище прикладі видно, що операція uniq триває 94 секунди з сумарних 95 секунд, за які виконується запит. Там же бачимо, що в uniq майже весь час займає sort, який йде 90 сек. Тут же видно проблему у вигляді кількості рядків, алгоритму сортування та використаної пам'яті. Залишилося всього нічого: зрозуміти «хто винен і що робити». Тут допоможе тільки знання структури даних цільової БД і вимоги до результатів запиту. Може буде достатньо переставити пару рядків або додати додаткова умова, а може знадобиться повністю переписати запит, так як у вихідному вигляді єдине, що у нього може добре виходити — це гальмувати.

Так само варто звернути увагу на великий «Rows X». Це говорить про промаху прогнозованого і фактичного результату вибірки і, найчастіше, обумовлено недостатнім обсягом статистики про таблицях. Це може призводити до неоптимальному плану виконання запиту. Наприклад: хочемо вибрати один рядок таблиці з 1 мільйоном елементів; якщо планувальник вирішить, що результат вибірки буде не 1 рядок, а ~200 000, то він не буде шукати по індексу, а піде фулл сканом, так як це оптимальна стратегія для такого співвідношення підсумкових рядків і розміру таблиці. Висновки про швидкість робіть самі.

Стандартні граблі
Ось, що найчастіше зустрічалося на практиці і що було причиною непотрібного поведінки запитів:
  • нерозуміння структури даних і джоин даних через невідомі обхідні і манівці або, ще краще, джоин зайвих даних; крайній такий випадок був в MySQL; ось трохи полегшений приклад, який передає суть проблеми:
    SELECT ordered_products.*
    FROM products,
    products AS ordered_products
    GROUP BY ordered_products.id

    З одного боку, просто вказали зайву таблицю всередині FROM і ніяк їй не скористалися. З іншого боку, ми отримали неявне з'єднання двох таблиць через CROSS JOIN і $N^2$ результуючих рядків (актуально, як мінімум, для версії MySQL 5.5). У моєму випадку в таблиці products було 40к рядків, але кінця виконання запиту я так і не дочекався. На скільки я знаю, Oracle вміє робити join elimination, але, в будь-якому випадку, краще не сподіватися на фічі СУБД, а думати головою.
    Бонус: як це зробити в ActiveRecord і все повісити
    Product.joins(", (#{Product.table_name}) AS ordered_products").
    select('ordered_products.*').
    group("ordered_products.#{Product.primary_key}")
    

  • Любов до OUTER JOIN. Вони породжують, як мінімум, геометричний ріст рядків у проміжних результатах і може легко виявитися, що на деяких вхідних даних запит буде гальмувати, а СУБД захлинатися від обсягу даних. Крайній приклад був на запиті, згаданому вище по тесту (який з WITH). Він відмінно працював при строгому обмеженні за product_id. Цей запит добре працював з масивом з 5-15 айдишников і час виконання запиту лінійно зростала, але далі, кожен наступний айдишник в масиві збільшував час виконання запиту в 2-3 рази. Проблема якраз була в безлічі OUTER JOIN, які мультиплікативно збільшували число оброблюваних рядків і з деякого моменту їх кількість ставало надто великим, а план виконання не можна було показувати неповнолітнім розробникам.
  • В продовження попереднього пункту: деякі люблять ставити FULL OUTER JOIN замість LEFT/RIGHT, яких вистачає в переважній більшості випадків (перевірено на мешканцях хабра, з якими обговорювали запит з попередньої статті про співбесідах). Проблема все та ж: генерація зайвих даних і збільшення споживання ресурсів. З особистого: FULL OUTER JOIN нещодавно реально знадобився в продакшені вперше за 2 роки… щасливий був, як дитина.
  • Дивна магія з функціями, наприклад, з PostgreSQL, коли замість пари джоинов в декларативному стилі намагаються зробити те ж саме, але в імперативному стилі через масиви та інші структури даних укупі з функціями, їх перетворюючими. Приклад, на жаль, не знайду, тому доведеться повірити на слово. Пам'ятаю лише, що такі речі періодично миготять на stackoverflow. Радує тільки, що вони майже ніколи не вибиваються в лідери по лайкам.


Кінець
Всім дякую. Якщо у вас є свої чудові приклади того, як робити не треба, то, будь ласка, не мовчіть, «шкідливих порад» і «живих прикладів» багато не буває. Та і стаття — це, як і квиток на іспиті, лише привід поговорити.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.