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



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

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


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


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


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



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

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

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

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


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


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


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



87   0  
    Ничего не найдено.

Добавить ответ:
Отменить.