01.02.2017 07:00
Loriowar 24 10900 Источник
Всё просто. Тут можно найти «Основы разбора запросов для чайников» в случае 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 property of 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 possible application is 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 as written, 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. Радует только, что они почти никогда не выбиваются в лидеры по лайкам.

Конец


Всем спасибо. Если у вас есть свои замечательные примеры того, как делать не надо, то, пожалуйста, не молчите, «вредных советов» и «живых примеров» много не бывает. Да и статья — это, как и билет на экзамене, лишь повод поговорить.
Поделиться с друзьями
-->

Комментарии (24)


  1. kir_vesp
    01.02.2017 10:17
    #10046992

    По первому же примеру: ман не главное, ведь даже при чтении функции очевидно, что она проводит тупо выборку нужного формата и возвращает её, это логически приводит к тому, что все остальные условия будут работать уже с полученной выборкой.


    Взяли результат из сложной функции -> фильтранули его -> "ай-яй-яй, всё сломалось, но мы же ничего не меняли в логике!". Бинго! Вы вынесли одно из условий в следующий этап обработки.


    ИМХО, тут скорее на лицо проблема построения логических цепочек. Т.к. даже для меня, человека весьма далёкого от подобных сложных запросов и не знающего о функциях и их работе в SQL, очевидно, что тут получается сначала огромный результат по всем продуктам(!) и только потом он изменяется.


    1. Loriowar
      01.02.2017 11:04
      #10047100
      +1

      Об этом и хотелось сказать. Что в случае 2 табличек и 3 условий, всё довольно очевидно. А когда объектов десяток или больше и если не знать кто с кем как связан, то легко можно вынести такой кусок куда подальше и быть уверенным, что всё будет хорошо. По личному ощущению, после 100 строк sql-запроса становится довольно сложно в нём ориентироваться. Плюс, рискну огрести, но всё-таки: по рассказу постфактум всё может быть довольно понятно, но когда в ходе разработки на очередном этапе всё поломалось, то найти причину не так просто. Конечно потом ударишь себя ладонью в лоб, и скажешь как же мы могли этого не заметить, но это будет позже.


      1. kir_vesp
        01.02.2017 11:25
        #10047148

        Согласен, но всё-таки, если параметр WHERE стоит за пределами выражения, это должно наводить на мысль..)


        1. Loriowar
          01.02.2017 11:28
          #10047154
          +1

          Таки да. Но если в WHERE ещё десятка полтора условий и значимых для конкретной части только 1-2шт, то они там и теряются. Всё познаётся в сравнении. В sql нужно быть безумно внимательным, иначе неизменно получается хрень.


          1. kir_vesp
            01.02.2017 11:38
            #10047178

            Хм, а дробить это сразу а функции можно или это сильно будет сказываться на производительности? Просто мне всё-таки хочется понять, почему Вы сказали, что практики "чистого кода" не сильно применимы к SQL(ну, да, это вообще не ЯП, но всё-таки).


            1. Loriowar
              01.02.2017 11:48
              #10047204

              Скажем так, имею в виду, что к чистому sql-запросу практики для императивного программирования очень редко применимы (крайний пример: огромный метод в классе на том же Ruby — это великое зло и все будут жаждать разбить его, а огромный запрос, который выбирает за милисекунды нужные данные — это необходимое зло, с которым мало кто что-то делает, максимум подтащат красивое соглашение об оформлении кода). Если бы это был PL/SQL, то там другой коленкор, но всё равно скорость превалирует над красотой. В вышеописанном случае: есть небольшая портянка на 150 строк. Любые попытки разбить запрос на отдельные части ведут к проседанию производительности. Может быть можно его иначе написать и будет более читаемо, но вряд ли трудозатраты окупятся. Как раз об этом и хочется поговорить ещё с кем-либо.


    1. mayorovp
      01.02.2017 16:59
      #10047854
      +2

      Вам, может быть, и очевидно что запрос сначала делает полную выборку, а уже потом ее фильтрует… а вот хорошие оптимизаторы запросов так не считают.


      Специально проверил в MS SQL:


      select t1.id as x, t2.id as y, t3.id as z 
from c as t1, c as t2, c as t3
where t1.id < 10 and t2.id < 10 and t3.id < 10;

with t as (
    select t1.id as x, t2.id as y, t3.id as z 
    from c as t1, c as t2, c as t3)
select * from t
where x < 10 and y < 10 and z < 10;

      Оба запроса имеют одинаковые планы.


      1. kir_vesp
        01.02.2017 17:30
        #10047912

        да, но в Вашем примере не хватает одной детали. В "облагороженном" куске кода было такое условие: WHERE section_item_history.product_id IS NOT NULL, — а потом за пределами WITH должно было стоять ещё одно, но уже с конкретным параметром product_id. Так что, для корректности пример должен быть в духе:


        select t1.id as x, t2.id as y, t3.id as z 
from c as t1, c as t2, c as t3
where t1.id < 10 and t2.id < 10 and t3.id < 10;

with t as (
    select t1.id as x, t2.id as y, t3.id as z 
    from c as t1, c as t2, c as t3
    where x is not null and y is not null and z is not null)
select * from t
where x < 10 and y < 10 and z < 10;


        1. kir_vesp
          01.02.2017 17:34
          #10047922

          Как я уже писал выше, с SQL знаком весьма поверхностно, но два разных условия выборки на лицо.


        1. mayorovp
          01.02.2017 18:14
          #10048024

          Дополнительное простое условие WHERE ну никак не может замедлить выборку.


          В любом случае, добавление нового условия и оборачивание в WITH — это независимые изменения.


          1. Loriowar
            01.02.2017 18:59
            #10048122

            Вот рискну не согласиться с вами по поводу того, что WHERE никак не может замедлить выборку. Банальный пример: таблица с полями id и text. В text кладём что-нибудь поувесистей на пару метров или пару гигов. Сначала ищем по id, он же pk и на нём же, по умолчанию, висит индекс. Получаем данные моментально. Потом берём LIKE по текстовой колонке без индексов и ждём второго пришествия. Можно хитрее придумать. Например, у нас партиционирование и очередной WHERE заставит лезть в старые партиции, хранящиеся чуть ли не на магнитных лентах глубоко в архивах (или просто на медленном хранилище, так как потребности в них особо нет). Конечно нужно сильно подумать о том, как бы такую архитектуру с партициями сделать, но всё же. Посему считаю высказывание "WHERE не может замедлить запрос" — ложным.


            1. mayorovp
              02.02.2017 08:50
              #10048872

              Переформулирую так. Дополнительное условие в WHERE, не содержащее вложенных запросов, не способно увеличить размер выборки.


              1. Loriowar
                02.02.2017 09:19
                #10048904

                Согласен. Увеличить размер выборки — не может. А замедлить её выполнение — может. Прошу не путать размер и скорость и не пытаться их связать какой-либо зависимостью.


                1. Ivan22
                  02.02.2017 13:53
                  #10049522
                  +1

                  Очень просто — postgresSQL не может пропушить условия внутрь WITH блока. А MSSQL может. Вот и вся разница. Плюс ньюанс о том что постгрес всегда материализует WITH блок, а другие субд необязательно.


    1. Barafu
      02.02.2017 00:58
      #10048668
      +2

      При обучении это сильно зависит от предыдущего опыта. Грубо говоря, если человек много пишет на С, он по-умолчанию подумает, что здесь избыточные операции. Если же человек пишет на Хаскеле, где вся ненужная работа выкидывается и чтобы посчитать 2*2 легко можно создать бесконечный возрастающий массив, умножить его на себя же и взять второй элемент; то человек легко может подумать, что и здесь всё оптимизируется, и не будет же база данных заниматься такой фигнё, как строить в памяти квадрат со сторонами в 4к элементов!


      1. Loriowar
        02.02.2017 09:27
        #10048910

        Немного уйду от темы, но всё же. Такое случается в начале, когда 1-2 языка программирования в ассортименте и опыта не шибко много. Потом уже начинаешь думать а какой из известных подходов более всего актуален для поставленной задачи и для заданных условий. Там же учишься не смешивать контексты разных языков, чтобы, например, не писать в Си-шном стиле на Ruby. После этого оказывается, что есть сразу несколько вариантов решения, среди которых все имеют плюсы и минус. Вот тут-то и начинается самое интересное. Кстати, это интереснейшая тема для обсуждения!


  1. potapuff
    01.02.2017 11:07
    #10047106

    Поправьте меня с:

    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)


    но, ANY(specification_parts.ids) — это ересь. При соединениях сравнения выполняются построчно, потому внутри ANY(specification_parts.ids) все-равно всегда будет ровно одна строка. А значит, условие полностью равносильно с… AND specification_history.specification_id = specification_parts.ids


    1. Loriowar
      01.02.2017 11:30
      #10047162

      В specification_parts.ids лежит Array из PG. В этом вся засада. И именно поэтому он ids, а не id.


  1. masai
    01.02.2017 12:02
    #10047236
    +1

    Да и статья — это, как и билет на экзамене

    Выучишь — а попадётся другое.


    1. Loriowar
      01.02.2017 12:09
      #10047254

      Жизнь вообще очень "внезапная" штука. А если честно, то это фраза замечательного преподавателя различных частей мат.анализа и просто здоровского математика, зовут которого Алфимов Георгий Леонидович.


  1. darthunix
    01.02.2017 15:37
    #10047688

    По факту вся статья сводится к простому правилу — минимизируйте количество строк и столбцов на каждом этапе запроса для увеличения производительности.

    По поводу первой части с with… Проблема была не в cte, а в некорректно переписанном запросе, который не использовал условие where product_id = 1234. Ещё бы он не работал медленнее. Зачем вы смущаете юных падованов, связывая замедление с конструкцией with?)
    Опять же, аргумент вида

    Если айдишних получается, например, в ходе рекурсивного запроса, то в WITH такое условие не утащишь и идея с разделением запроса на куски будет безбожно тормозить

    далёк от истины. Ничто не мешает положить результат рекурсивного запроса в виде единственного айдишника в cte, а потом написать 
    where product_id = (select id from recursive_with)


    Касаемо красивой визуализации explain analyze — лично я предпочитаю построение в pgAdmin: толщина связей в отображении плана запроса очень чётко указывают на проблему производительности.


    1. Loriowar
      01.02.2017 16:15
      #10047774

      Таки да, вы правы абсолютно, вся статья о минимизации. И о том, что нужно понимать структуру данных, чтобы эффективно минимизировать.


      Касаемо WITH вместе с рекурсией. Либо я неправильно выразился, либо вы меня плохо поняли. Имелось в виду, когда во второй части WITH RECURSIVE (которая после UNION) используется блок из отдельного WITH. То есть он нужен для вычисления следующего шага рекурсии на основании результатов текущего. Здесь мы не можем (либо я просто не знаю как) прокинуть свежевычисленный id внутрь отдельного WITH, чтобы урезать выборку в нём. И, кажись, в этом месте я накосячил в статье, так как плохо описал эту ситуацию.


      1. darthunix
        01.02.2017 17:14
        #10047876
        +1

        А вот теперь суть истории стала понятна и статья обрела смысл) Да, безусловно, использовать внутри рекурсивного запроса конструкцию with для вычисления следующего шага нельзя, это обычно фатально для производительности. Я тоже так стрелял себе в ногу…
        Вообще вы подняли хорошую тему — надо бы собрать набор паттернов и антипаттернов в sql и написать большую статью, которая дополнялась бы по мере обсуждения в комментариях. Не хотите попробовать?;)


        1. Loriowar
          01.02.2017 17:29
          #10047908

          Вот, пытаюсь по мере сил. Проблема лишь в том, что SQL в работе не так часто случается. А без конкретных примеров из практики получается уныло и не интересно. Подборка по прострелу конечности в Ruby/Rails у меня гораздо больше, но руки не дойдут опубликовать. А в целом только за. Если как-нибудь организовать наброс материала теми, у кого он есть, то готов заняться оформлением, проверкой и рассказом в доступной форме.