SQL оптимизация

Нюансы по оптимизации SQL скриптов

Наткнулся на небольшую методичку по оптимизации SQL скриптов и решил выложить перевод, чтобы всегда был под рукой. Может быть, кому-то ещё пригодится.

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

Сперва привожу пример плохих практик с обозначением "Bad Practice", а затем - хороших "Good Practice".

# 1

Используйте 'REGEXP_LIKE' вместо оператора 'LIKE'

❌Bad Practice

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%'

✅ Good Practice

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

# 2

Используйте 'REGEXP_EXTRACT' вместо 'CASE-WHEN LIKE'

❌Bad Practice

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'
...
END AS brand
FROM item_list

✅ Good Practice

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

# 3

Преобразуйте длинный список условий в операторе IN во временную таблицу

❌Bad Practice

SELECT *
FROM table1 AS t1
WHERE
    item_id IN (3363134, 5189076, ..., 4062349)

✅ Good Practice

SELECT *
FROM table1 AS t1
JOIN (
      SELECT
          item_id    
      FROM (
            SELECT
                SPLIT('3363134, 5189076, ...,' ,',') AS bar
           )
           CROSS JOIN
               UNNEST(bar) AS t(item_id)
     ) AS table2 as t2
ON
    t1.item_id = t2.item_id

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

# 4

Всегда сортируйте ваши JOIN от больших таблиц к меньшим таблицам

❌Bad Practice

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

✅ Good Practice

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

# 5

Используйте простые соединения в JOIN

Две таблицы со строкой даты, например, ‘2020-09-01’, но в одной из таблиц есть столбцы только для отдельных значений года, месяца, дня.

❌Bad Practice

SELECT *
FROM
    table1 t1
JOIN
    table2 t2
ON t1.date = CONCAT(t2.year, '-', t2.month, '-', t2.day)

✅ Good Practice

SELECT *
FROM
    table1 t1
JOIN (
      SELECT
          name,
          CONCAT(t2.year, '-', t2.month, '-', t2.day) as date
      FROM
          table2 t2
     ) new
ON t1.date = new.date

# 6

Всегда сортируйте "GROUP BY" по убыванию от атрибута/столбца с наибольшим количеством уникальных сущностей/значений к атрибуту/столбцу с наименьшим количеством уникальных сущностей/значений

❌Bad Practice

SELECT
    main_category,
    sub_category,
    item_id,
    SUM(price)
FROM
    table1
GROUP BY
    main_category, sub_category, item_id

✅ Good Practice

SELECT
    main_category,
    sub_category,
    item_id,
    SUM(price)
FROM
    table1
GROUP BY
    item_id, sub_category, main_category

# 7

Избегайте подзапросов в операторе WHERE

❌Bad Practice

SELECT
    SUM(price)
FROM
    table1
WHERE
    item_id IN (
                SELECT
                    item_id
                FROM
                    table2
               )

✅ Good Practice

WITH t2 AS
          (
           SELECT
               item_id
           FROM
               table2
          )

SELECT
    SUM(price)
FROM
    table1 AS t1
JOIN 
    t2
ON t1.item_id = t2.item_id

# 8

Используйте MAX вместо RANK

❌Bad Practice

SELECT *
FROM (
      SELECT
          user_id,
          RANK() OVER(ORDER BY prdate DESC) AS ranking
      FROM
          table1
     ) new
WHERE ranking = 1

✅ Good Practice

SELECT
    user_id,
    MAX(prdate)
FROM table1
GROUP BY 1

# 9

Другие советы

Используйте approx_distinct() вместо count(distinct) для очень больших
наборов данных
Используйте approx_percentile(метрика, 0,5) для медианы
Избегайте UNION, где это возможно
Используйте оператор WITH вместо вложенных подзапросов

Перевод и адаптация:
@sinister_stark
December 2022