Оптимизация SQL запросов03.01.2025 11:00

Оптимизация SQL-запросов является одной из ключевых задач при работе с реляционными базами данных. Эффективные SQL-запросы позволяют значительно улучшить производительность приложений и обеспечить более быстрый доступ к данным. В данной статье мы рассмотрим как переписать запрос, чтобы выполнялся быстрее. В статье пойдет речь о PostgreSQL, хотя применять данные советы к любой базе данных SQL Ниже будут представлены термины и операторы, о которых пойдет в данной статье.

cost — стоимость выполнения этого узла и всех его дочерних узлов. Первое число показывает стоимость до получения первой строки результата, а второе — всех строк полностью. Стоимость выполнения измеряется в неких условных единицах. Они нужны в основном для сравнения планов между собой — это может пригодиться, когда есть несколько вариантов написания одного и того же запроса, и из них нужно выбрать самый производительный. EXPLAIN — Ожидаемый план запроса, без выполнения

Оптимизация SQL — это процесс улучшения производительности запросов к базе данных с целью уменьшения времени выполнения и использования ресурсов (таких как CPU и память).

Оптимизация сравнения IN

В SQL оператор IN используется для проверки того, содержится ли значение в списке значений. Это более удобный способ записи, чем использование нескольких условий OR. Обнаружилось, что при большом количестве значений скорость запроса сильно падает, если используется IN. Это происходит потому, что значение колонки каждой строки поочерёдно сравнивается с каждым из возможных вариантов, тем самым нагружая процессор.

Оптимизация через виртуальные таблицы

Чтобы избежать полного сканирования таблицы, можно воспользоваться JOIN с виртуальной таблицей. Команда VALUES представляет список значений в виде таблицы.

В SQL оператор VALUES используется для создания виртуальной таблицы, которая может быть использована в запросах. Это может быть полезно, когда вы хотите выполнить выборку, вставку или другие операции с набором данных, который не хранится в физической таблице.

Скорость запроса с оператором IN

Скорость запроса с JOIN виртуальной таблицы

Код

/*до рефакторинга*/
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

Коррелирующий подзапрос

Коррелирующий подзапрос — это подзапрос, который ссылается на столбцы внешнего запроса. В отличие от некоррелирующих подзапросов, которые могут быть выполнены независимо от внешнего запроса, коррелирующие подзапросы требуют контекста из внешнего запроса для выполнения.

Основная проблема запроса — в многократном считывании данных. Является антипаттерном.

Скорость коррелирующего подзапроса

Скорость запроса с JOIN виртуальной таблицы

Код

/*до рефакторинга*/
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 используется для выбора значений в указанном диапазоне. Он позволяет фильтровать результаты выборки, определяя интервал между двумя значениями. Оператор может использоваться с числовыми, строковыми и временными данными.

Скорость выполнения запроса оператора BETWEEN

Скорость выполнения запроса c EXTRACT

Код

/*до рефакторинга*/
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, потому что сервер не считывает лишние строки из таблицы, если необходимо убедиться, что запись существует, в какой-либо таблице.

Скорость выполнения запроса c JOIN для проверки существования записи в другой таблице

Скорость выполнения запроса c EXISTS

Код

/*до рефакторинга*/
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 используется в сочетании с агрегирующими функциями для ограничения набора данных, к которому применяются эти функции. Это позволяет вам выполнять агрегацию лишь по определённым строкам, удовлетворяющим заданным условиям

Скорость выполнения запроса с помощью CASE

Скорость выполнения запроса c помощью 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 является важным процессом, который помогает улучшить производительность баз данных и снизить время выполнения запросов. Применяя описанные в статье о рефакторинге запроса, можно добиться значительного улучшения отклика приложений и уверенности в масштабируемости системы в условиях растущих нагрузок.