5 рекомендаций по оптимизации запросов SQL

t.me/data_analysis_ml

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

Однако самообучение может таить в себе ряд серьезных недостатков, о которых вы даже не подозреваете.

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

Возможно, вы думаете: “Я мастерски владею SQL и пишу отличные запросы. Даже если в моем стиле и присутствуют мелкие непродуктивные навыки, почему я должен от них отказываться?”. На это есть несколько причин.

Стиль программирования формирует первое впечатление и отражает уровень профессионализма. Сумбурные запросы без четкой структуры явно свидетельствуют об отсутствии мотивации программиста к улучшению.
Запросы без логики построения усложняют совместное и повторное использование. А это особенно важно, когда речь идет о командной работе. Поскольку у каждого своя манера программирования, то наступит день, когда другие просто не поймут ваши запросы. Всегда старайтесь стандартизировать свой стиль написания кода.
Некачественные запросы сложнее подвергаются масштабированию и оптимизации. Запросы, вызывающие трудности при прочтении и в понимании, больше всего подвержены сбоям при масштабировании. Что касается запросов, лишенных четких источников данных или структуры, то придется хорошо постараться, чтобы их улучшить и дополнить новыми данными.
Некорректно сформированные навыки могут распространяться в команде и негативно сказываться на программировании восприимчивых коллег, особенно младших сотрудников под вашим руководством. Соблюдение командных стандартов оформления кода и синтаксиса крайне важно для обеспечения порядка в работе.

Также как небольшие улучшения повышают качество запросов, так и мелкие некорректные навыки могут снизить качество и понятность кода в долгосрочной перспективе.

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

1. Начните грамотно оформлять запросы

Отступы и пробелы необходимы для структурирования запросов. Так звучит основная концепция SQL. Рассмотрим данный постулат.

Начнем с совершенно экстремального примера. Надо сказать, что написать такой запрос стоило немало усилий.

Этот запрос не содержит отступов и пробелов:

select TA.id, TA.client_name, TA.client_surname, sum(TB.client_purchases) as total_client_purchases, sum(TB.client_Discounts) as total_clients_discounts 
from table_A as TA left join table_B as TB on TA.id = TB.id where TA.country = "France"
group by TA.id

Как вам далось чтение запроса? Весьма сложное испытание. Однажды я работал с человеком, который программировал запросы SQL именно так.

Попробуем добавить в запрос отступы:

select 
    TA.id, 
    TA.client_name,
    TA.client_surname, 
    sum(TB.client_spent_amount) as total_client_spent_amount, 
    sum(TB.client_Discounts) as total_clients_discounts 
from table_A as TA 
left join table_B as TB 
    on TA.id = TB.id 
where TA.country="France"
group by TA.id

Как видно, наличие отступов упрощает чтение запроса. В таком виде для его понимания хватает и беглого просмотра. Далее добавляем дополнительные пробелы:

select 

    TA.id, 
    TA.client_name,
    TA.client_surname, 
    sum(TB.client_spent_amount)   as total_client_spent_amount, 
    sum(TB.client_Discounts)      as total_clients_discounts 
    
from table_A                      as TA 
left join table_B                 as TB 
    on TA.id = TB.id 
where TA.country = "France"
group by TA.id

Отлично! Теперь без особого труда можно понять различные уровни внутри запроса.

2. Пишите синтаксис SQL в верхнем регистре

Согласно писаным и неписаным законам, синтаксис SQL пишется в верхнем регистре.

Начнем с основного положения: верхний регистр используется для ключевых слов SQL, а нижний — для таблиц и столбцов. Функции SQL также настоятельно рекомендуется писать в верхнем регистре.

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

Преобразуем предыдущий запрос с учетом новой логики:

SELECT 

    TA.id, 
    TA.client_name,
    TA.client_surname, 
    SUM(TB.client_spent_amount) AS total_client_spent_amount, 
    SUM(TB.client_Discounts)    AS total_clients_discounts 
    
FROM table_A                    AS TA 
LEFT JOIN table_B               AS TB 
    ON TA.id = TB.id 
WHERE TA.country = "France"
GROUP BY TA.id

Так намного лучше. Любой человек, связанный с миром данных, сразу поймет этот запрос.

3. Попрощайтесь с инструкцией SELECT * FROM

Многие программисты, признаться и я в их числе, привыкли работать с магической командой SQL *. Очень сложно не поддаться искушению и не воспользоваться этой эффективной командой при создании быстрых запросов для проверки интуитивных предположений.

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

Это значит, что во избежание ошибок не рекомендуется задействовать команду SQL * при написании кода итоговых версий запросов. Перетаскивая все столбцы с собой, мы легко можем спровоцировать нарушение их работы. Потеря контроля над ними чревата получением лишних невостребованных данных и дублированных столбцов, усложняющих код.

Поэтому избегаем следующего способа действия:

SELECT * FROM tableA

И берем на вооружение предлагаемый вариант:

SELECT 
  column1, 
  column2, 
  column4, 
  column6 
FROM tableA

Вероятно, вы по-прежнему рассматриваете возможность применения * EXCEPT() для исключения ненужных столбцов.

Есть вариант получше.

EXCEPT также следует избегать, поскольку эта команда заключает в себе такую же проблему, как и *. Дело в том, что вы не проверяете, какие столбцы переносите, а просто отслеживаете те, которые намерены исключить.

4. Используйте меньше подзапросов и больше CTE

Не стоит задействовать более одного подзапроса на временную таблицу или CTE. Рассмотрим на примере.

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

Сначала пишем запрос с двумя подзапросами, который вычисляет три средние зарплаты:

SELECT T1.COUNTRY, 
         AVG(T1.salary)     AS AVG_salary_per_country
         T2.AVG_salary_per_city,
         T3.AVG_salary_company

FROM salary_table AS T1
CROSS JOIN 
 
  (
      SELECT 
         T1.CITY, 
         AVG(T1.salary)     AS AVG_salary_per_city,
         T3.AVG_salary_company
      FROM salary_table AS T2
      CROSS JOIN 
      
      (
      
          SELECT AVG(salary)  AS AVG_salary_company
          FROM salary_table
          
      ) AS T3
      GROUP BY 1,3
  ) AS T2
GROUP BY 1,3,4

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

В такой ситуации важное значение приобретают команды WITH и CTE. Они позволяют создавать более понятные и переиспользуемые запросы при разделении подзапросов во временных таблицах, а также выбирать все необходимые данные в итоговом запросе:

WITH 

SALARY_COUNTRY AS 
(
    SELECT T1.COUNTRY, 
           AVG(T1.salary)     AS AVG_salary_per_country
    FROM salary_table AS T1
GROUP BY 1
),

SALARY_CITY AS 
(
    SELECT T1.CITY, 
           AVG(T1.salary)     AS AVG_salary_per_city
    FROM salary_table AS T1
    GROUP BY 1
),

SALARY_GLOBAL AS 
(
    SELECT AVG(salary)  AS AVG_salary_company
    FROM salary_table
)
SELECT 
    T1.country,
    T1.AVG_salary_per_country,
    T2.city, 
    T2.AVG_salary_per_city,
    T3.AVG_salary_company
FROM SALARY_COUNTRY       AS T1
CROSS JOIN SALARY_CITY    AS T2
CROSS JOIN SALARY_GLOBAL  AS T3

5. Присваивайте столбцам логически обоснованные имена

При создании запроса мы можем просто переносить столбцы, используя их номера:

SELECT 
    col1,
    col2,
    col3,
    col4
FROM TABLE
ORDER BY 1,2

Вместо этого рекомендуется называть их в соответствии с предполагаемым назначением. Такой прием упрощает редактирование уже созданных запросов и их масштабирование без потери логики:

SELECT 
    id,
    name,
    age,
    bank_balance,
FROM TABLE
ORDER BY bank_balance,age

Целесообразно всегда соблюдать порядок и логику запросов SQL. Выявление и самокоррекция непродуктивных навыков SQL помогает стать более профессиональным, уверенным и успешным программистом SQL.

Заключение

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

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

Доверяйте данным — они знают, как лучше!