03.11.2024 09:43
vbersenev72 2 906 Источник

Введение

В этой серии статей мы рассмотрим топ-10 трюков для оптимизации SQL запросов, которые помогут вам повысить скорость выполнения запросов, тем самым снизив нагрузку на сервер. Let's roll))

#1 "regexp_like" > "LIKE"

Настойтельно рекомендую заменять использование оператора "LIKE" на "regexp_like".

Пример:

SELECT *
FROM
    table1
WHERE
    lower(item_name) LIKE '%samsung%' OR
    lower(item_name) LIKE '%xiaomi%' OR
    lower(item_name) LIKE '%iphone%' OR
    lower(item_name) LIKE '%huawei%' OR
   -- и т.д

Заменить на:

SELECT *
FROM
    table1
WHERE
    REGEXP_LIKE(lower(item_name), 'samsung|xiaomi|iphone|huawei')

Если вы работаете над большим проектом и гибкость SQL запросов для вас является приоритетной задачей, а так же если ваши критерии поиска сложные и разнообразные, использование REGEXP_LIKE является более предпочтительным выбором. Т.к данный оператор позволяет использовать более гибкие и динамические подходы к фильтрации данных.

#2 "regexp_extract" > "Case-when Like"

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

Пример:

SELECT
CASE
   WHEN concat(' ',item_name, ' ') LIKE '%acer%' then 'Acer'
   WHEN concat(' ',item_name, ' ') LIKE '%advance%' then 'Advance'
   WHEN concat(' ',item_name, ' ') LIKE '%alfalink%' then 'Alfalink'
AS brand
FROM item_list

Заменить на:

SELECT
   regexp_extract(item_name, '(asus|lenovo|hp|acer|dell|zyrex| ...)')
AS brand
FROM item_list

Использование REGEXP_EXTRACT предоставляет множество преимуществ для извлечения данных и работы с текстом по сравнению с подходом CASE...WHEN LIKE, который больше подходит для простых условий. Если ваша задача заключается в извлечении конкретных подстрок и работе со сложными шаблонами, REGEXP_EXTRACT станет более подходящим инструментом.

#3 JOIN и UNNEST превосходят статические списки в SQL

Пример: 

SELECT *
FROM Table1 as t1
WHERE
    itemid in (3363134, 5189076, ... , 4062349)

Заменить на:

SELECT *
FROM Table1 as t1
JOIN (
  SELECT
    itemid
  FROM (
    SELECT
      split('3363134, 5189076,,', ', ')
        as bar
  )
  CROSS JOIN
    UNNEST (bar) AS t(itemid)
) AS Table2 as t2
ON
  t1.itemid= t2.itemid

Здесь, используя split, вы можете легко изменять список itemid, извлекая его из другого источника данных или формируя его программно. Это полезно, если itemid изменяется часто или производится во время выполнения.

Этот подход может быть полезен, если itemid может поступать в виде массива, что позволяет избежать жесткого кодирования значений. Если вам нужно работать с большим объемом данных, эта конструкция может упростить процесс обработки.

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

#4 Использование JOIN от бÓльших таблиц к меньшим

Пример:

SELECT
    *
FROM
    small_table
JOIN
    large_table
ON small_table.id = large_table.id

Заменить на:

SELECT
    *
FROM
    large_table
JOIN
    small_table
ON small_table.id = large_table.id

В большинстве SQL-движков производительность джоинов не зависит от порядка таблиц, так как оптимизатор запросов, в большинстве случаев, может переупорядочить операции для повышения эффективности. Однако в некоторых случаях (как например MYSQL), особенно когда у вас есть большие таблицы и определенные индексы, порядок может иметь значение. Например, если small_table ЗНАЧИТЕЛЬНО меньше, оптимизатор может выбрать более эффективный план выполнения запроса.

#5 Динамическое формирование данных с использованием подзапросов

Пример:

SELECT *
FROM
  tablel a
JOIN
  table2 b
ON a.date= CONCAT(b. year, b.month, '-', b.day)

Заменить на:

SELECT *
FROM
  tablel a
JOIN (
  select
    name, CONCAT(b. year, '-', b.month, '-', b.day) as date
  from
    table2 b
) new
ON a.date = new.date

В контексте производительности и читаемости оба запроса могут быть адекватными в зависимости от ситуации. Если в table2 достаточно много записей и вам нужна дополнительная обработка, второй вариант может быть более предпочтительным, так как он предоставляет больше гибкости. Если же запрос простой и «легкий», то первый варинт может оказаться достаточно хорошим вариантом, и его проще читать. Это уже мое субьективное мнение))

Так же отмечу, что MSSQL и pgSQL спокойно оптимизируют первый вариант запроса, поэтому разницы особо не будет

Надеюсь вам понравилась эта статья). Во второй части статьи будут собраны еще 5 фишек для улучшения ваших SQL запросов. Всем удачи!

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