Недавно столкнулся с одним приложением, которое генерировало запросы к БД. Я понимаю, что этим никого не удивишь, но когда приложение стало тормозить и мне пришло задание разобраться в чём причина, я был сильно удивлён, обнаружив эти запросы. Вот с чем иногда приходится иметь дело SQL Server:
SELECT COUNT(DISTINCT "pr"."id") FROM ((((((((((((((((("SomeTable" "pr"
LEFT OUTER JOIN "SomeTable1698" "uf_pr_id_698" ON "uf_pr_id_698"."request" = "pr"."id")
LEFT OUTER JOIN "SomeTable1700" "ufref3737_i2" ON "ufref3737_i2"."request" = "pr"."id")
LEFT OUTER JOIN "SomeTable1666" "x0" ON "x0"."request" = "ufref3737_i2"."f6_callerper")
LEFT OUTER JOIN "SomeTable1666" "uf_ufref4646_i3_f58__666" ON "uf_ufref4646_i3_f58__666"."request" = "ufref3737_i2"."f58_")
LEFT OUTER JOIN "SomeTable1694" "x1" ON "x1"."request" = "ufref3737_i2"."f38_servicep")
LEFT OUTER JOIN "SomeTable3754" "ufref3754_i12" ON "pr"."id" = "ufref3754_i12"."request")
LEFT OUTER JOIN "SomeTable1698" "uf_ufref3754_i12_reference_698" ON "uf_ufref3754_i12_reference_698"."request" = "ufref3754_i12"."reference")
LEFT OUTER JOIN "SomeTable1698" "x2" ON "x2"."request" = "ufref3737_i2"."f34_parentse")
LEFT OUTER JOIN "SomeTable4128" "ufref3779_4128_i14" ON "ufref3737_i2"."f34_parentse" = "ufref3779_4128_i14"."request")
LEFT OUTER JOIN "SomeTable1859" "x3" ON "x3"."request" = "ufref3779_4128_i14"."reference")
LEFT OUTER JOIN "SomeTable3758" "ufref3758_i15" ON "pr"."id" = "ufref3758_i15"."request")
LEFT OUTER JOIN "SomeTable1698" "uf_ufref3758_i15_reference_698" ON "uf_ufref3758_i15_reference_698"."request" = "ufref3758_i15"."reference")
LEFT OUTER JOIN "SomeTable3758" "ufref3758_i16" ON "pr"."id" = "ufref3758_i16"."request")
LEFT OUTER JOIN "SomeTable4128" "ufref3758_4128_i16" ON "ufref3758_i16"."reference" = "ufref3758_4128_i16"."request")
LEFT OUTER JOIN "SomeTable1859" "x4" ON "x4"."request" = "ufref3758_4128_i16"."reference")
LEFT OUTER JOIN "SomeTable4128" "ufref4128_i17" ON "pr"."id" = "ufref4128_i17"."request")
LEFT OUTER JOIN "SomeTable1859" "uf_ufref4128_i17_reference_859" ON "uf_ufref4128_i17_reference_859"."request" = "ufref4128_i17"."reference")
LEFT OUTER JOIN "SomeTable1666" "uf_ufref4667_i25_f69__666" ON "uf_ufref4667_i25_f69__666"."request" = "uf_pr_id_698"."f69_"
WHERE ("uf_pr_id_698"."f1_applicant" IN (248,169,180,201,203,205,209,215,223,357,371,379,3502,3503,3506,3514,3517,3531,3740,3741)
OR "x0"."f24_useracco" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "uf_ufref4646_i3_f58__666"."f24_useracco" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "uf_ufref4667_i25_f69__666"."f24_useracco" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR ("uf_pr_id_698"."f10_status" Is Null OR "uf_pr_id_698"."f10_status" <> 111) AND "ufref3737_i2"."f96_" = 0 AND (("ufref3737_i2"."f17_source" Is Null OR "ufref3737_i2"."f17_source" <> 566425)
AND ("ufref3737_i2"."f17_source" Is Null OR "ufref3737_i2"."f17_source" <> 566424) OR ("uf_pr_id_698"."f10_status" Is Null OR "uf_pr_id_698"."f10_status" <> 56) )
AND ("uf_pr_id_698"."f12_responsi" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "x1"."f19_restrict" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "uf_ufref3754_i12_reference_698"."f12_responsi" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "x2"."f12_responsi" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "x3"."f5_responsib" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "uf_ufref3758_i15_reference_698"."f12_responsi" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "x4"."f5_responsib" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136)
OR "uf_ufref4128_i17_reference_859"."f5_responsib" IN (578872,564618,565084,566420,566422,566936,567032,567260,567689,579571,580813,594452,611522,611523,615836,621430,628371,633044,634132,634136))
AND ("uf_pr_id_698"."f12_responsi" Is Null OR "uf_pr_id_698"."f12_responsi" <> 579420) ) AND "pr"."area" IN (700) AND "pr"."area" IN (700) AND "pr"."deleted_by_user"=0 AND "pr"."temporary" = 0Название объектов было изменено.
Больше всего бросается в глаза то, что одна и та же таблица используется множество раз, а количество скобок сводит с ума. Но не только мне не понравился такой код, SQL Server то же не в восторге и тратит много ресурсов на создание плана для него. Запрос может выполняться от 50 до 150 мс, а построение плана может занимать до 2,5 секунд. Сегодня я не буду рассматривать варианты исправления ситуации, скажу только что в моём случае исправить генерацию запроса в приложении было невозможно.
Я бы хотел рассмотреть почему же SQL Server так долго строит план запроса. В любой СУБД, SQL Server не исключение, главным вопросом оптимизации является способ соединения таблиц друг с другом. Кроме непосредственно самого способа соединения, очень важен порядок соединения таблиц.
Давайте поговорим о порядке соединения таблиц подробнее. В данном вопросе очень важно понять — возможное количество соединений таблиц растёт по экспоненте, а не линейно. Например, для 2-х таблиц число возможных вариантов соединения всего 2, для 3-х это число может доходить до 12-и. Разный порядок соединения, может иметь разную стоимость запроса и оптимизатор SQL Server должен выбрать «оптимальный» способ, но при большем количестве таблиц, это становится ресурсоёмкой задачей. В случае если SQL Server начнёт перебирать все возможные варианты, то такой запрос может никогда не выполнится, по этой причине SQL Server этого никогда не делает и всегда ищет «достаточно хороший план», а не «наилучший». SQL Server всегда пытается найти компромисс между временем выполнения и качеством плана.
Вот наглядный пример роста количества вариантов соединения по экспоненте. SQL Server может выбирать разные способы соединения (left-deep, right-deep, bushy trees).
Визуально это выглядит следующим образом:
Таблица показывает возможное количество вариантов соединения при увеличении количество таблиц:
Вы можете самостоятельно получить эти значения:
Для left-deep: 5! = 5 x 4 x 3 x 2 x 1 = 120
Для bushy tree: (2n–2)!/(n–1)!
Вывод: Относитесь более аккуратно к количеству JOIN в запрос и не мешайте оптимизатору. Если у вас не получается добиться нужного результата в запросе, где много JOIN, разбейте его на несколько, более мелких запросов и вы удивитесь на сколько лучше может получиться результат.
P.S. Конечно, мы должны понимать, что кроме определения порядка соединения таблиц, оптимизатор запросов должен так же выбрать какой тип соединения использовать, выбрать способ доступа к данным (Scan, Seek) и тд.
Комментарии (36)
darthunix
05.04.2017 16:17+1Я так и не понял из статьи, как именно MS SQL находит баланс между стоимостью плана и лимитом по времени перебора. N! вариантов — это хорошо для объяснения на пальцах, но вряд ли именно так все работает. У того же PostgreSQL помимо стандартного планировщика запросов есть ещё модуль генетической оптимизации. На последнем pgconf показывали модуль адаптивной оптимизации, который учитывал предыдущий опыт построения планов по схожим запросам. Наверняка в MS SQL все не менее наворечено и можно тоже что-то понастраивать с вычислением стоимости планов на большом количестве соединений. А просто сказать, что когда много таблиц, то планировщик начинает работать не очень… не информативно.
Dm1tr1ch
05.04.2017 16:30-1Как именно работает оптимизатор — это тема отдельной статьи, возможно я продолжу эту тему в новой статье.
darthunix
05.04.2017 17:52+2Понимаете, просто само название статьи и начала абзацев вроде
Я бы хотел рассмотреть почему же SQL Server так долго строит план запроса.
Давайте поговорим о порядке соединения таблиц подробнее
настраивают на то, что на примере запроса из начала статьи будет разобран алгоритм построения плана и дана общая теория по специфике работы планировщика MS SQL. А закончится статья тем, что автор победил проблему с учётом вышеописанных знаний. По итогу же имеем «Вот запрос, нагенерированный 1С на 20 таблиц с повторами. Он строится иногда долго. Почему? Много таблиц в соединении плохо, так как эн-факториал. Вывод — не делайте много таблиц и уважайте планировщик». Ну такому уровню статей не место на Хабре, простите.Dm1tr1ch
05.04.2017 18:06-2А так же я написал
Сегодня я не буду рассматривать варианты исправления ситуации, скажу только что в моём случае исправить генерацию запроса в приложении было невозможно.
darthunix
05.04.2017 18:18+2Хорошо, спрошу максимально прямо — о чем эта статья и что из нееследует вынести после прочтения?
Dm1tr1ch
05.04.2017 22:23-1В первую очередь то, что выделил в статье курсивом
В данном вопросе очень важно понять — возможное количество соединений таблиц растёт по экспоненте, а не линейно
И вынес в раздел «вывод»
Относитесь более аккуратно к количеству JOIN в запрос и не мешайте оптимизатору. Если у вас не получается добиться нужного результата в запросе, где много JOIN, разбейте его на несколько, более мелких запросов и вы удивитесь на сколько лучше может получиться результат.
Для других — что оптимизатор это более сложная часть SQL Server и что о нём надо как минимум задумываться
Возможно кто-то даже решит заняться изучением оптимизатора основательно. Если хоть 1 человек примет такое решение — статья написана не зря.
alexhott
05.04.2017 18:52да уж такой запрос на базе больше 10 строк это смерть.
если не секрет что за приложение такое нарисовало?
конструкция с in() обычно отрабатывается как loop что не способствует производительности
ну и план запроса неплохо бы в студию
а вообще любой планировщик бессилен против кривых рук
mnv
06.04.2017 00:38+3"Запросы с JOIN выполняются долго" — это наверное такая мантра, я часто это слышал. Но это далеко не всегда так. Даже если джойнятся большие таблицы. Даже если в запросе используется много джойнов.
Проблемы как правило либо в отсутствии нужных индексов, либо в наличии таких препятствий как OR и IN в условиях запроса, либо попытка обработки слишком большого объема данных в запросе. Или если понятно, что запрос будет отдавать одинаковые данные как с LEFT JOIN так и с INNER JOIN, то последний вариант может оказаться радикально эффективнее. Да что тут говорить, у каждого запроса в каждой СУБД могут быть свои особенности и этих особенностей не мало.
В приведенном запросе оптимизатор может нормально зацепиться разве что за условия
AND "pr"."area" IN (700) AND "pr"."deleted_by_user"=0 AND "pr"."temporary" = 0
Но скорее всего эти условия сильно не ограничивают выборку, поэтому индексы не работают и приходится делать полный перебор. Подробности можно увидеть в EXPLAIN.
musicriffstudio
06.04.2017 08:42+3Вывод: Относитесь более аккуратно к количеству JOIN в запрос и не мешайте оптимизатору. Если у вас не получается добиться нужного результата в запросе, где много JOIN, разбейте его на несколько, более мелких запросов и вы удивитесь на сколько лучше может получиться результат.
неправильный вывод.
В запросе много IN и ORб данные многократно повторяются, а потом обрезаются DISTINCT.
Судя по названиям полей это какой-то анализатор активности юзеров в веб-приложении. Т.е. просто взяли полотенце логов и вывалили их в таблицы, т.е. база данных не нормализована.
Попросите специалиста создать нормальную (BCNF) структуру таблиц в новой базе и сливайте туда данные для анализа.
Количество JOIN в запросах, вероятно, увеличится в разы, а скорость выполнения (что естественно) уменьшится в сотни раз.
Это стандартный подход.
Да, в очередной раз порекомендую https://habrahabr.ru/company/mailru/blog/266811/
Dm1tr1ch
06.04.2017 09:43-2Сам запрос, приведён как яркий пример из моей жизни, чтобы можно было на основе реальных данных показать что бывает. На самом деле проблемы у этого запроса не в количестве JOIN, но он был показателен, в каких ситуациях возможны проблемы. В любой СУБД бывают моменты, когда разбиение запроса на части даёт существенный прирост в производительности. Так вот, суть вывода в том, что следует взять на вооружение, что если не помогает обычная оптимизация, то, возможно, поможет разбиение запроса. Я не предлагаю думать о проблемах количества JOIN ранее, чем с этим есть проблемы.
musicriffstudio
06.04.2017 09:46+2в примере явно нет никакой оптимизации и данные не нормализованы. Вывод неверный.
Dm1tr1ch
06.04.2017 09:47-1Ок, у меня нет цели вас переубеждать.
Скажу лишь то, что не хотел погружаться в оптимизатор в первой статье, но проблемы оценок в самом первом соединении, приведут к большим проблемам к концу запроса. Следовательно, чем больше цепочка, тем больше будет проблем на её конце.
Vlad_fox
06.04.2017 12:18При анализе хорошо бы указывать версию сервера БД, в разных версиях оптимизатор может вести себя достаточно по разному.
Запрос ужасен, надо что-то делать с тем сильным интеллектом, который его сгенерил, иначе если еще будут добавляться таблици, данных в таблицах станет существенно больше — будетполная жопаеще большая проблема.
OlegZH
06.04.2017 14:27Да, было бы неплохо посидеть на досуге и поразбирать этот заковыристый запрос. Неужели, в реальных приложениях «всё так плохо» (с запросами), или, всё-таки, структуры таблиц стараются такими, чтобы максимально упростить запросы?
И… что это за точки в тексте запроса:
Я немного отстал от жизни и мне требуется пояснить синтаксис."pr"."id" = "ufref3758_i16"."request"
AlexBin
07.04.2017 17:50+3Позволите мне рассказать анекдот про вашу статью?
Чапаев: А чего вся квартира в тараканах?
Петька: А это я диссертацию пишу. О тараканах.
Чапаев: Ну, расскажи.
Петька: Берем таракана, ставим его на стол и кричим: Беги! И он бежит! Отрываем ему ноги и кричим: Беги! Но он не бежит. Делаем вывод: таракан без ног не слышит!
ggrnd0
Oracle очень легко пережёвывает запрос с 20 join и 5 подзапросами с группировкой как в подзапросах так и на верхнем уровне на бизнес данных за 15лет (в некоторых таблицах до 30+ млн срок)...
Если БД захлебывается, то либо ей не хватает оперативки, либо индексов.
А по приведенному запросу, если он и проседает, то из-за огромного количества OR в блоке where…
Так например, MySQL не будет использовать индексы при обработке фильтра where вообще.
Dm1tr1ch
Я лично не сравнивал оба оптимизатора Oracle и SQL Server, но не раз слышал от коллег по Oracle негативные отзывы о их оптимизаторе. Опыт работы таких специалистов — интегратор и более 10 лет с Oracle, что не должно вызывать вопросы в их компетенции.
Конкретно по вашему предположению, возможно Oracle просто не использует все возможные комбинации и выбирает из скудного списка, который, по их мнению, покрывает большинство ситуаций.
P.S. SQL Server то же работает и с 20 и более JOIN, вопрос как. Не всегда это получается быстро. К тому же я написал в разделе P.S., что выбор способа соединения таблиц это всего лишь одна из множество задач оптимизатора запроса.
bormotov
Про оракловый оптимизатор даже книга отдельная есть, где не просто рассказывают «как оно работает», а на каждый аспект набор тестов, которые можно взять и по-запускать на своей базе.
После чтения такой книги, очень странно слышать слова «негативные отзывы от специалиста с 10 лет работы».
Dm1tr1ch
По SQL Server то есть есть книги на тему оптимизатора, с примерами и рекомендациями, но это же не значит что оптимизатор будет всегда прав, это просто невозможно (можно просто банально плохо написать запрос и никакой оптимизатор не поможет).
bormotov
Планировщик запроса — это просто алгоритм. Он может быть плохим, конечно. Книжка которую я читал про оракловый, охватывала версии оракла 8, 9, 10, и автор прям наглядно показывал, как планировщику от версии к версии добавляют мозгов, и ситуации, когда эти «более умные» мозги принимают решения хуже, чем было.
Меня удивляет отношение человека, с опытом 10 лет.
В моём понимании, «планировщик запроса» — это такая данность, как восход Солнца. И довольно глупо ругать Солнце, что оно утром светит в окно и мешает спать. Самое простое — добавить к «окно на восток», хорошие светонепроницаемые шторы.
И кажется, профессионал должен не ругать Солнце, а сразу высказать, какие шторы, для каких случаев, и почему.
Кстати, отдельный вопрос — когда вообще нужно собирать результаты из 20-ти таблиц? Можно хотя0бы намекнуть на прикладную область, и что за данные такого рода запросом вытаскивали?
Dm1tr1ch
Согласен по поводу данности оптимизатора.
Что касается прикладной области, то в моём случае приложением организовано как 1С, из тучи «динамических» таблиц, с похожим названием, но проблема была даже не в этом. Проблема была в том, что генератор запросов на каждую галочку не добавлял параметров к уже существующим соединениям, а писал ещё 1 JOIN. То есть я мог наставить галочек и получить 10 JOIN с одной и той же таблицей и в каждом JOIN я делал ограничение по своему столбцу.
knagaev
«Динамические» таблицы — это боль для RDBMS.
Как говорил мой знакомый, «по такой структуре БД хорошо диссертацию писать, но работать оно не будет».
varagian
Сколько интриги и метафор о книге про оптимизатор, а где же название-то? Общественность интересуется.
Dm1tr1ch
Одну из них, бесплатную, можно скачать вот тут — http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook
У bormotov, то же можете поинтересоваться про книгу по Oracle, если необходимо.
knagaev
Ещё про оптимизатор Оракла полезно почитать книги мегагуру Тома Кайта — в разделе Библиография.
Он, кстати, очень не любит выкручивать руки оптимизатору хинтами и убеждён, что во многом проблемы производительности решаются правильной архитектурой БД.
И документацию Oracle Database 12c Release 2 Performance однозначно стоит почитать.
P.S. для меня тоже было странно услышать о таком мнении специалиста 10+ об оптимизаторе Oracle…
bormotov
про оракловый оптимизатор вообще никаких интриг:
Дж. Льюис, ORACLE: Основы стоимостной оптимизации.
На русском издавали, например «Питер», аккурат десять лет назад.
knagaev
Немножко промахнулся с ответом — хотел сюда ответить, пожалуйста, читайте мой комментарий
Mistrall
Я могу не намекнуть, я могу пальцем показать: оборотно-сальдовая ведомость в 1С Бухгалтерия.
При «правильных» настройках планировщик Oracle разворачивает запрос её формирования более, чем в 1000 join из 50+ таблиц.
VolCh
Возможно, группировки сильно ограничивают количество вариантов.