- PVSM.RU - https://www.pvsm.ru -
Всё просто. Тут можно найти «Основы разбора запросов для чайников» в случае PostgreSQL и замечательные невыдуманные примеры из продакшена о том, как не надо писать запросы на PostgreSQL и MySQL и что бывает, если их так всё-таки писать.
Расскажу про несколько очевидных вещей, которые довольно хорошо описаны в манах и документации, которую читают, обычно, после вступления на десяток грабель, либо после отстрела определённого числа конечностей или после иного членовредительства.
Частей будет несколько, логически они слабо связаны друг с другом, но все они встретились в ходе решения различных потребностей бизнеса и так или иначе удовлетворяли его потребности.
Та самая штука WITH, которая очень похожа на синтаксический сахар без большой смысловой нагрузки и ассоциируется (у непосвящённых) с разбиением большой портянки на отдельные методы в духе Мартина и Фаулера. Здесь главная особенность заключается в том, что это нифига не аналог метода/функции, особенно когда дело доходит до оптимизации запроса.
Сразу прошу прощения у читателя, но далее в тексте будут только куски запросов, имеющих принципиальное значение, запросы целиком публиковаться не будут. Во-первых, чтобы не утомлять особенностями структуры данных и во-вторых, чтобы я нечаянно не запостил что-нибудь приватно-корпоративное. Если куски будут абсолютно не читаемы, пожалуйста, не бейте сильно, а предложите как их облагородить. Спасибо.
Как делать не надо.
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 гиге памяти. Тут же возникли вопросы:
Ответ: перенос джоинов из основного тела в WITH сделал ровно то, что описано в документации [1]:
Если коротко и грубо: запросы из WITH выполняются один раз и, чаще всего, не оптимизируются, то есть место их использования не влияет на план выполнения.
То есть мы левелапнули кусок запроса до уровня самостоятельной части, забыв добавить к нему важные условий из WHERE, которые урезают выборку по самые помидоры. В результате переджойнили всю базу, а затем отдали этого монстрика в основное тело, где взяли из него десяток строк.
В конкретном вышеописанном случае в WHERE было условие вида «product_id = 1234», которое и задавало основное ограничение по данным. Если бы это условие утащили в WITH, то всё продолжило бы работать примерно с той же скоростью. Однако, так можно сделать только в случае статического значения для правой части условия. Если айдишних получается, например, в ходе рекурсивного запроса, то в WITH такое условие не утащишь и идея с разделением запроса на куски будет безбожно тормозить.
Вывод:
Думаю, все в курсе про explain.depesz.com [2]. Там красиво показывают что не так с запросом.
По сути, это просто раскраска для дефолтного вывода команды explain, зато это очень наглядно и особенно помогает в начале, пока не знаешь на что смотреть… хотя чего врать, и не в начале помогает, просто красиво и удобно.
Здесь хочется сказать пару слов по каждой из колонок и пояснить как они влияют на результат выполнения. Таки да, это написано там же в хелпе [3], но редко кто читает хелп, пока не припрёт.
Если всё тормозит и не знаете с чего начать, то вот пара советов. Нужно взять раскрашенный explain (желательно вместе с analyze) из предыдущего пункта и посмотреть на него. Чаще всего, проблема (читай, 80%+ времени выполнения), сосредоточена в одной из операций, описанных в плане выполнения. То есть по Exclusive/Inclusive найти самое тёмное и стрёмное место. Опять же, в примере выше видно, что операция uniq длится 94 секунды из суммарных 95 секунд, за которые выполняется запрос. Там же видим, что в uniq почти всё время занимает sort, который идёт 90 сек. Здесь же видно проблему в виде количества строк, алгоритма сортировки и используемой памяти. Осталось всего ничего: понять «кто виноват и что делать». Здесь уже поможет только знание структуры данных целевой БД и требования к результатам запроса. Может будет достаточно переставить пару строк или добавить доп.условие, а может понадобится полностью переписать запрос, так как в исходном виде единственное что у него может хорошо получаться — это тормозить.
Так же стоит обратить внимание на большой «Rows X». Это говорит о промахе предсказанного и фактического результата выборки и, чаще всего, обусловлено недостаточным объёмом статистики о таблицах. Сие может приводить к неоптимальному плану выполнения запроса. Например: хотим выбрать одну строку из таблицы с 1 миллионом элементов; если планировщик решит, что результат выборки будет не 1 строка, а ~200 000, то он не будет искать по индексу, а пойдёт фулл сканом, так как это оптимальная стратегия для такого соотношения результирующих строк и размера таблицы. Выводы о скорости делайте сами.
Вот, что чаще всего встречалось на практике и что было причиной непотребного поведения запросов:
SELECT ordered_products.*
FROM products,
products AS ordered_products
GROUP BY ordered_products.id
С одной стороны, просто указали лишнюю таблицу внутри FROM и никак ей не воспользовались. С другой стороны, мы получили неявное соединение двух таблиц через CROSS JOIN и результирующих строк (актуально, как минимум, для версии MySQL 5.5). В моём случае в таблице products было 40к строк, но конца выполнения запроса я так и не дождался. На сколько я знаю, Oracle умеет делать join elimination [4], но, в любом случае, лучше не надеяться на фичи СУБД, а думать головой.
Product.joins(", (#{Product.table_name}) AS ordered_products").
select('ordered_products.*').
group("ordered_products.#{Product.primary_key}")
Всем спасибо. Если у вас есть свои замечательные примеры того, как делать не надо, то, пожалуйста, не молчите, «вредных советов» и «живых примеров» много не бывает. Да и статья — это, как и билет на экзамене, лишь повод поговорить.
Автор: Loriowar
Источник [7]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/mysql/239131
Ссылки в тексте:
[1] документации: https://www.postgresql.org/docs/9.5/static/queries-with.html
[2] explain.depesz.com: https://explain.depesz.com
[3] хелпе: https://explain.depesz.com/help
[4] join elimination: https://habrahabr.ru/post/248817/
[5] статьи о собеседованиях: https://habrahabr.ru/post/314654/
[6] stackoverflow: http://stackoverflow.com/
[7] Источник: https://habrahabr.ru/post/320916/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best
Нажмите здесь для печати.