Оптимизация SQL-запросов является одной из ключевых задач при работе с реляционными базами данных. Эффективные SQL-запросы позволяют значительно улучшить производительность приложений и обеспечить более быстрый доступ к данным. В данной статье мы рассмотрим как переписать запрос, чтобы выполнялся быстрее. В статье пойдет речь о PostgreSQL, хотя применять данные советы к любой базе данных SQL Ниже будут представлены термины и операторы, о которых пойдет в данной статье.
cost — стоимость выполнения этого узла и всех его дочерних узлов. Первое число показывает стоимость до получения первой строки результата, а второе — всех строк полностью. Стоимость выполнения измеряется в неких условных единицах. Они нужны в основном для сравнения планов между собой — это может пригодиться, когда есть несколько вариантов написания одного и того же запроса, и из них нужно выбрать самый производительный. EXPLAIN - Ожидаемый план запроса, без выполнения
Оптимизация SQL — это процесс улучшения производительности запросов к базе данных с целью уменьшения времени выполнения и использования ресурсов (таких как CPU и память).
Оптимизация сравнения IN
В SQL оператор IN используется для проверки того, содержится ли значение в списке значений. Это более удобный способ записи, чем использование нескольких условий OR. Обнаружилось, что при большом количестве значений скорость запроса сильно падает, если используется IN. Это происходит потому, что значение колонки каждой строки поочерёдно сравнивается с каждым из возможных вариантов, тем самым нагружая процессор.
Оптимизация через виртуальные таблицы
Чтобы избежать полного сканирования таблицы, можно воспользоваться JOIN с виртуальной таблицей. Команда VALUES представляет список значений в виде таблицы.
В SQL оператор VALUES используется для создания виртуальной таблицы, которая может быть использована в запросах. Это может быть полезно, когда вы хотите выполнить выборку, вставку или другие операции с набором данных, который не хранится в физической таблице.
Код
/*до рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder
WHERE shipcity IN ('Madrid', 'Lyon')
AND custid = '69'
AND shipperid = 3
/*после рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder AS t1
JOIN(VALUES ('Madrid'),('Lyon')) AS t2
ON t1.shipcity = t2.column1
WHERE custid = '69'
AND shipperid = 3
Оптимизация через оператор ANY(ARRAY[])
Этот оператор проверяет, есть ли хотя бы один элемент в итерируемом объекте, который является истинным. Он завершает выполнение, как только находит первый истинный элемент, что может быть быстрее, чем проверка всего объекта с помощью IN. Данный пункт только для PostgreSQL.
Код
/*до рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder
WHERE shipcity IN ('Madrid', 'Lyon', 'Bern', 'Leipzig')
AND custid = '69'
AND shipperid = 3
/*после рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder
WHERE shipcity = ANY(ARRAY['Madrid', 'Lyon', 'Bern', 'Leipzig'])
AND custid = '69'
AND shipperid = 3Коррелирующий подзапрос
Коррелирующий подзапрос — это подзапрос, который ссылается на столбцы внешнего запроса. В отличие от некоррелирующих подзапросов, которые могут быть выполнены независимо от внешнего запроса, коррелирующие подзапросы требуют контекста из внешнего запроса для выполнения.
Основная проблема запроса — в многократном считывании данных. Является антипаттерном.
Код
/*до рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder AS t1
WHERE shipcity IN (
SELECT shipcity
FROM (
SELECT shipcity,
COUNT(orderid) AS cnt
FROM public.salesorder AS t2
WHERE t1.shipcity = t2.shipcity
GROUP BY shipcity) AS foo
WHERE cnt > 15)
/*после рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder AS t1
JOIN (SELECT shipcity
FROM (
SELECT shipcity,
COUNT(orderid) AS cnt
FROM public.salesorder
GROUP BY shipcity) AS foo
WHERE cnt > 15) AS t2
ON t1.shipcity = t2.shipcityОптимизация выборки диапазона с помощью BETWEEN
Оператор BETWEEN выполняет сравнение значений и, как правило, выполняется быстрее, чем функции, поскольку он может использовать индексы, что позволяет оптимизировать выполнение запроса. В то время как EXTRACT и DATE_PART требуют обработки данных, чтобы извлечь нужную информацию перед сравнением, что может быть менее эффективно.
Оператор BETWEEN в SQL используется для выбора значений в указанном диапазоне. Он позволяет фильтровать результаты выборки, определяя интервал между двумя значениями. Оператор может использоваться с числовыми, строковыми и временными данными.
Код
/*до рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder AS t1
WHERE EXTRACT(YEAR FROM orderdate) = 2006
AND EXTRACT(MONTH FROM orderdate) = 7
/*после рефакторинга*/
EXPLAIN
SELECT *
FROM public.salesorder AS t1
WHERE orderdate BETWEEN '2006-07-01 00:00:00'::TIMESTAMP
AND '2006-07-31 00:00:00'::TIMESTAMPОптимизация с помощью оператора EXISTS
Оператор EXISTS в SQL используется для проверки существования записей в подзапросе. Если запрос возвращает хотя бы одну строку, то EXISTS возвращает TRUE, в противном случае — FALSE
Оператор EXISTS будет эффективнее, чем JOIN, потому что сервер не считывает лишние строки из таблицы, если необходимо убедиться, что запись существует, в какой-либо таблице.
Код
/*до рефакторинга*/
EXPLAIN
SELECT COUNT(DISTINCT t1.orderid)
FROM public.salesorder AS t1
JOIN public.orderdetail AS t2
ON t1.orderid = t2.orderid
/*после рефакторинга*/
EXPLAIN
SELECT COUNT(DISTINCT t1.orderid)
FROM public.salesorder AS t1
WHERE EXISTS(
SELECT NULL
FROM public.orderdetail AS t2
WHERE t1.orderid = t2.orderid)Пред оптимизационные шаги
Извлечение только необходимых столбцов улучшает производительность
Ограничение количества строк может ускорить вывод данных.
Не используй функцию SUBSTRING в условиях. Использование LIKE позволяет использовать индексы
Создание промежуточных результатов при агрегации. Использование CTE может помочь оптимизировать вычисления
Более читабельные выражения
Фильтрация агрегатных функций
Для подсчета кол-ва строк, которые подходят под определенные условия без добавления оператора WHERE можно использовать агрегатную функцию SUM вместе с CASE. Это будет более оптимизировано, чем использовать оператор множеств UNION ALL, но менее читаемо. Для более читабельного запроса - используй оператор FILTER.
В SQL оператор FILTER используется в сочетании с агрегирующими функциями для ограничения набора данных, к которому применяются эти функции. Это позволяет вам выполнять агрегацию лишь по определённым строкам, удовлетворяющим заданным условиям
Код
/*до рефакторинга*/
EXPLAIN
SELECT SUM(CASE WHEN num % 3 = 0 THEN 1 END) AS foo,
SUM(CASE WHEN num % 5 = 0 THEN 1 END) AS bar,
SUM(CASE WHEN num % 3 = 0 AND num % 5 = 0 THEN 1 END) AS foobar
FROM table1
/*после рефакторинга*/
EXPLAIN
SELECT COUNT(*) FILTER(WHERE num % 3 = 0) AS foo,
COUNT(*) FILTER(WHERE num % 5 = 0) AS bar,
COUNT(*) FILTER(WHERE num % 3 = 0 AND num % 5 = 0) AS foobar
FROM table1 Использование ранжирования вместо DISTINCT
Если вам нужно получить уникальные значения и важно оптимальное время выполнения, рассмотрите возможность использования ROW_NUMB ER() с группировкой, особенно если вы можете воспользоваться индексами.
DISTINCT - Эта операция используется для выбора уникальных значений из столбца или набора столбцов. При больших объемах данных операция может быть медленной, поскольку ей необходимо просмотреть все строки, чтобы идентифицировать уникальные значения.
ROW_NUMBER() - Эта функция присваивает уникальный номер каждой строке в результирующем наборе, основываясь на заданном порядке сортировки. Как правило, она быстрее, чем DISTINCT, если вы просто хотите получить уникальные строки без необходимости проверять каждый элемент на уникальность, особенно если у вас уже есть индексированный столбец.
Код
/*до рефакторинга*/
EXPLAIN
SELECT COUNT(DISTINCT productid)
FROM public.orderdetail AS t1
JOIN public.salesorder AS t2
ON t1.orderid = t2.orderid
WHERE orderdate BETWEEN '2000-01-01'::DATE
AND '2003-01-01'::DATE
/*после рефакторинга*/
EXPLAIN
SELECT COUNT(productid)
FROM(
SELECT productid,
ROW_NUMBER() OVER(PARTITION BY productid) AS rn
FROM public.orderdetail AS t1
JOIN public.salesorder AS t2
ON t1.orderid = t2.orderid
WHERE orderdate BETWEEN '2000-01-01'::DATE
AND '2003-01-01'::DATE) AS t
WHERE t.rn = 1Избегание CASE при проверке булевых полей
Конструкция CASE более сложна и громоздка. Для простых логических условий предпочтительнее использовать OR или другие логические операции, так как это улучшает как читаемость, так и потенциальную производительность запросов.
Код
/*до рефакторинга*/
EXPLAIN
SELECT CASE
WHEN t1.is_deleted_row IS TRUE
OR t2.is_deleted_row IS TRUE THEN TRUE
ELSE FALSE
END AS is_deleted_row
FROM table_1 AS t1
JOIN table_2 AS t2
ON t1.num = t2.num
/*после рефакторинга*/
EXPLAIN
SELECT t1.is_deleted_row
OR t2.is_deleted_row AS is_deleted_row
FROM table_1 AS t1
JOIN table_2 AS t2
ON t1.num = t2.numВывод
В заключение, оптимизация SQL-запросов в PostgreSQL является важным процессом, который помогает улучшить производительность баз данных и снизить время выполнения запросов. Применяя описанные в статье о рефакторинге запроса, можно добиться значительного улучшения отклика приложений и уверенности в масштабируемости системы в условиях растущих нагрузок.
Комментарии (27)
ialexander
03.01.2025 08:18В ваших примерах не всегда очевидна польза той или иной оптимизации.
К примеру, вы пишите, что
Оператор EXISTS будет эффективнее, чем JOIN, потому что сервер не считывает лишние строки из таблицы, если необходимо убедиться, что запись существует, в какой-либо таблице.
Если вы взгляните на планы для обоих запросов с JOIN и EXISTS в статье, то увидите, что в обоих случаях делается полный скан таблиц, и считывается 830 записей из salesorder и 2155 из orderdetail. Разница в стоимости запросов в данном случае из-за ошибки оптимизатора PostgreSQL, который решил, что в первом случае после join останется 2155 записей, а во втором 830, что явная ошибка так как их должно остаться одинаково. В реальности я бы сказал,что второй запрос будет дороже из-за дополнительного HashAggregate stage.
Вообще, если взглянуть на проблему JOIN и EXISTS шире, то хороший оптимизатор старается переписать запрос, который использует подзапросы, как в случае с JOIN, в запрос с JOIN, что, кстати, и произошло в данном случае, если вы взляните на планы.
В друих случаях, я бы доработал примеры, например, в случае с BETWEEN не продемонстрировано использовование индекса в одном случае и полного скана в другом. И я бы переименовал этот совет - речь ведь больше не про BETWEEN, а про то, что в ряде случаев из-за использование функций, индексы становится невозможно использовать и по\тому лучше избегать использования функций на проиндексированных колонках.Ни в одном из примеров не продемонстрировано, что перезаписывание действительно делает запрос быстрее, только показана оценка стоимости запроса. Но доверять ей не стоит так как оптимизаторы баз данных частенько ошибаются в них. Хорошо было бы увидеть выигрыш во времени исполнения на реалистичных данных (а не на таблицах с парой тысяч записей) и с использованием индексов.
sshikov
03.01.2025 08:18Примерах? Да тут примеры вообще ничего не показывают...
Обнаружилось, что при большом количестве значений скорость запроса сильно падает, если используется IN.
Во-первых, вот это утверждение само следовало бы подтвердить измерениями. Потому что строго говоря, на число значений в IN есть практический лимит (пусть даже не в СУБД, так в JDBC драйвере, например), т.е. померять следовало бы хотя бы 1, 10, 100, 1000 и 100000 значений в IN. Кроме того, "обнаружилось" что c IN (1, 2, 3, ...) всегда работает быстро, если в колонке c всегда 1 (и я тоже не буду это доказывать - если это не так, это баг). Т.е. вот это вот "скорость сильно падает" зависит еще и от распределения значений в колонке слева от IN.
А дальше следует пример с двумя значениями в IN, и двумя значениями в VALUES, и при этом разницы в быстродействии мы практически не видим, т.е. она на грани погрешности измерения (и как вы уже отметили, кост в плане - не показатель вообще).
Satyricon
03.01.2025 08:18в ряде случаев из-за использование функций, индексы становится невозможно использовать и по\тому лучше избегать использования функций на проиндексированных колонках
В постгрес есть функциональные индексы. Очень выручали при рефакторинге текущих решений, когда, например, столбец с номерами автомобилей превращался в мешанину русских и английских букв в различных регистрах (не продумали заранее). Индекс по функции fn_normalize(gosnumber) сильно исправляет ситуацию.
taskevich
03.01.2025 08:18Можно еще использовать CTE(Common Table Expressions) для оптимизации запросов. Жаль, что в статье ничего не сказано
PB_Academy Автор
03.01.2025 08:18Добрый день, в блоке пред оптимизационные шаги описана CTE
taskevich
03.01.2025 08:18Увидел. Тогда жаль, что для CTE один пункт выделен, хотя можно было и подробно расписать о его работе.
Satyricon
03.01.2025 08:18CTE далеко не всегда помогает в оптимизации. У меня есть кейс, когда CTE, переписанный на временные таблицы, ускорялся с 10 минут до 7 секунд.
ptr128
03.01.2025 08:18Иногда помогает materialized.
К сожалению, то, как временные таблицы реализованы в PostgreSQL, приводит к необходимости их избегать, когда это возможно. Или средствами CTE, или, если результат временной выборки используется неоднократно и требует индексации, то средствами нежурналируемых таблиц.
В рамках одного оптимизируемого запроса, использование временных таблиц может дать явный прирост производительности. Но в рамках конкурентной среды с относительно длительными транзакциями, общая производительность может ухудшиться.
miksoft
03.01.2025 08:18Стоило бы убрать Big Data из тэгов, к бигдате статья не имеет отношения. И добавить PostgreSQL в заголовок и в тэги. Как раньше пользователи MS SQL считали, что он и есть SQL, так теперь пользователи PostgreSQL считают, что он и есть SQL. Хотя, разумеется, это не так в обоих случаях.
Статья банальная, такие регулярно выходят на Хабре. Ничего нового не увидел.
В ряде мест подача какая-то вывернутая наизнанку. Например, не BETWEEN выполняется быстрее, а использование конструкции функция(поле) часто делает невозможным использование индекса по этому полю. Но это не значит, что BETWEEN обладает каким-то особенным волшебством.
Почему-то под картинками подпись "Скорость ...", хотя на них никакой скорости не отображено.
Возможно проглядел, но во всей статье не увидел ничего про скорость.Некоторые советы сомнительные и нуждаются в тщательной аргументации, например, "Использование ранжирования вместо DISTINCT".
"Использование CTE может помочь оптимизировать вычисления".
А может и не помочь. В других SQL-движках встречал вред от CTE, когда CTE выполняется несколько раз (по числу ссылок на него в запросе) и, как следствие, несколько раз читает огромную таблицу."Обнаружилось, что при большом количестве значений скорость запроса сильно падает, если используется IN. Это происходит потому, что значение колонки каждой строки поочерёдно сравнивается с каждым из возможных вариантов, тем самым нагружая процессор."
Даже MySQL древних версий умеет из значений в IN строить бинарное дерево и находить соответствие за O(log(n))-время. Неужели PostgreSQL не умеет?И странное дело - вроде бы картинки в статье в png-формате, однако шрифты дико замылены, как будто их сохраняли в jpg формат с хорошим сжатием.
Ivan22
03.01.2025 08:18"низкий технический уровень материала"
PB_Academy Автор
03.01.2025 08:18Добрый вечер, не согласен с вами. С критикой, которая написана соглашусь, есть погрешности. Статья про как переписать запросы с ускорением. На Greenplum данные аспекты хорошо показываются.
Akina
03.01.2025 08:18Оптимизация сравнения IN
Считаю, что эта часть статьи абсолютно бездарна и просто провалена.
Во-первых, нужно было рассмотреть все три альтернативы - WHERE IN, WHERE EXISTS и INNER JOIN, и соответственно три анти-альтернативы WHERE NOT IN, WHERE NOT EXISTS и LEFT JOIN WHERE IS NULL. Уже на этой стадии возникли бы ба-а-альшие проблемы, потому как в зависимости от структуры хранения и статистики данных оптимальна то она, то другая альтернатива... более того, иногда одна из альтернатив (это прекрасно видно при анализе плана) сервером приводится к другой, иногда нет... На самом деле очень объёмная и интересная тема.
Во-вторых, а где собственно исходные данные для оптимизации? Где структуры, индексы, статистика данных? На непонятно каком исходном материале взята совершенно с потолка пара запросов...
В третьих, где адекватные результаты сделанного (вот никак не могу применить к этому термина "оптимизация")? почему сравнение сделано не для самих запросов, а для предсказаний к этим запросам? Что, большая проблема написать не EXPLAIN, а EXPLAIN ANALYZE? Запрос не изменяет данных, выполняется на крошечном объёме данных, потому никаких осложнений от реального выполнения запроса не ожидается. А ведь порой результат предсказания и результат реального выполнения - это две ну очень большие разницы.
------------------
Дальше - ничуть не лучше. Особенно убивает спрятанное под спойлер с текстом "Код". Это ж насколько надо не уважать читателя, чтобы кинуть ему какой-то непонятный запрос, потом его же, но абы оптимизированный, и полагать, что читателю этого достаточно. А читатель хочет повторить! Нет, запросы из-под спойлера он скопирует, а дальше что?
-------------------
Избегание CASE при проверке булевых полей
А тут просто-таки ерунда написана. Выражения - неэквивалентны.
Просто посмотрите, что будет, если
t1.is_deleted_row = NULL, а t2.is_deleted_row = FALSE.PB_Academy Автор
03.01.2025 08:18Добрый вечер.
По поводу скрытого кода. Для удобства чтения сделано. Если читателю неинтересен код, то он дополнительно не скролит вниз.
По поводу case. Проверка на null и bool значения принято через оператор is. К тому же в PostgreSQL подефолту проверка на true, если указаны операторы and/or.
По поводу не представленных тестовых данных. Если прикреплю ссылку - найдутся пользователи, которые задизлайкают и напишут не для забора. Уже проходили.
По поводу индексов. Индексы не всегда ускоряют. Сводить в статье, что используйте правильно индексы - везде написано. Смысл статьи был как рефакторинг может ускорить запрос. В начале описано, о чем будет статья. Про индексы не было сказано.
sergio_deschino
03.01.2025 08:18Практически со всеми критикующими я соглашусь, но почему когда люди рассказывают об оптимизации запросов мимоходом упоминают индексы и практически ни разу в статьях такого уровня я не видел разъяснений по этому поводу, не говоря уже о том, что при необходимости, возможности и целесообразности индексы можно …. создать самому! Или для уровня тех, на кого рассчитан материал, это совсем магия по мнению авторов?
Akina
03.01.2025 08:18Да потому что подавляющее большинство этих статей пишут не для людей, а "для галочки". И в значительном проценте случаев такие статьи пишут люди, которые сами "плавают" в теме.
PB_Academy Автор
03.01.2025 08:18Добрый вечер. Прошу не что стоит принижать статью до "галочки", а также, что автор "плавает" в теме. Вам не понравилось, что было не рассмотрено exists/in/join на достойном уровне. Я сделаю выводы и в дальнейшем будут тут более качественные статьи.
То, что вы ниже стали писать. Это необъективщина. Вам не понравился блок скрытый, кому-то нравится. Насчёт case, вы также не правы. Прошу использовать при проверке оператор is, а не равенство. Если мы за достойные примеры, то прошу писать принятые практики.
Akina
03.01.2025 08:18Насчёт case, вы также не правы. Прошу использовать при проверке оператор is, а не равенство.
Да вы что, не отличаете оператор сравнения от оператора присвоения, что ли?
Кстати, вы проверили то, что я написал? убедились, что выражения неэквивалентны? Если всё ещё нет - то вот вам ссылочка: https://dbfiddle.uk/nezOP17J
Вам не понравился блок скрытый, кому-то нравится.
То есть, вы решили, что претензии - к форме, а не к содержанию? иными словами, вы вообще не поняли, что я пишу и чем именно остался не удовлетворён? Или это был единственный способ и вроде бы ответить, и в то же время не признать?
Впрочем, вы абсолютно так же "не так поняли" и соответственно непонятно о чём ответили на мой отзыв чуть выше - причём аж четыре раза подряд.
java_man
03.01.2025 08:18@Akina вам не понравилась статья, так зачем вы, вместо предложений делаете хейт спич? Вы могли предложить переписать и примеры статей, чтобы читающие комментарии пользователи посмотрели более подробно. А вместо этого демотивируете потенциальных писателей хабра на интересные статьи.
Считаю, что эта часть статьи абсолютно бездарна и просто провалена.
Специально зашел к вам, могу также сказать, что ваши посты можно считать бездарными, но зачем.
Зачем это может понадобиться? не знаю...
У каждого есть своя аудитория. Если вам не нравится, ну пройдите, а не называйте статьями бездарными и тд. Статья хорошая, не идеальная.
PB_Academy Автор
03.01.2025 08:18Добрый вечер. В статье сказано про рефакторинг запроса. Про индексы не сказано. Сейчас вы предъявляете, что можно создать индексы для ускорения. По мнению автора - создание индекса описано в других статьях по оптимизации запроса. В этой про рефакторинг. Например антипаттерн кореллирующий подзапрос и обычный join.
Если мы возьмём Greenplum, то индексация там лучше не делать.
Q3_Results
03.01.2025 08:18В очередной раз убедился, что у нас очень токсичная культура критики. Если вы, критики, знаете и можете дать советы, как улучшить - научитесь это делать в форме, которая не отбивает желания у авторов продолжать публикации. Хочу сказать автору спасибо - я узнал что-то новое с точки зрения идейных стратегий или подходов оптимизаций в некоторых случаях. Замеры производительности именно на моих объемах данных и на моих структурах покажут, что из этих идей сработает. А если не сработает, то это не потому что автор написал что-то не то.
И спасибо автору за выбор именно Postgres, т.к. в свете импортозамещения именно эта СУБД актуальна сейчас.
Mazrigosh
03.01.2025 08:18Мда.
Соглашусь с мнением @Q3_Results. Здешним критикам надо поработать над манерой доносить свою позицию. Критика - это хорошо, но не тогда, когда она доносится языком, за который критика хочется послать... хм, самому писать статью, раз такой умный)
А автору - спасибо за материал и идеи. Filter, кстати, я давно уже полюбил за удобство и читабельность. Про exists хорошо, что написал. Я замечал, что даже довольно опытные коллеги иногда попросту забывают, что он есть.
INFITs
03.01.2025 08:18Добрый вечер, а мне не хватило информации в статье про анализ плана запроса, про описание хеш джойна, нести лупа чем отличаются, в каких случаях что лучше. А то план есть, кост есть а что в данном плане происходит и почему ни слова.
abcdsash
хорошая статья ) автору респект за возврат к традициям Хабра.
Однако, замечу, что порой ясность кода более важна для его поддержки, чем скорость выполнения. Не всегда, но тем не менее.
PB_Academy Автор
Спасибо огромное, будем стараться