Практически от любого специалиста IT-сферы сегодня требуется хотя бы базовое знание SQL. Рынок онлайн-образования реагирует соответствующе: на любом сайте по теме можно найти что-то связанное с SQL. В теории такие курсы действительно позволяют узнать основы языка, а иногда даже и немного больше, но на практике они не готовят специалиста ко взаимодействию с реальной базой данных. Многие стажеры и младшие (а иногда и грейдом повыше) специалисты допускают одни и те же ошибки.
В этот статье я бы хотела отметить важные моменты из работы с базами данных с помощью SQL, которые не изучают на курсах, но которые могут быть важны для эффективной работы с самого начала карьеры. Примеры в статье будут приведены для PostgreSQL.
1. Эквивалентность запросов
Обычно на курсах обучение выстроено следующим образом: изучил теорию по определенной теме - по ней решил пару задач. Это классическая методология, но у нее есть проблема: человек при решении задачи привязывается к определенной теме. Это не плохо и не хорошо. Не плохо, потому что такая методология позволяет отработать тему на практике. Не хорошо, потому что одни и те же результаты можно получить разными по синтаксису и плану выполнения запросами, которые так же будут значительно различаться по производительности.
Эквивалентные запросы - запросы, которые отличаются по тексту и плану выполнения, но при этом позволяют получить одинаковый результат.
Пусть у нас есть таблица с заказами (order_data) с датой заказа (order_date) и суммой заказа (sales) с примерно 5 млн записей с 2015 до 2025 года.
create table order_data (
order_date date,
sales integer
);
insert into order_data
select date_trunc('day', dd), random() * 50000
from generate_series('2015-01-01'::date, '2025-01-01'::date, '1 minute') dd;Предположим, что перед нами стоит задача найти разницу год к году по месяцам для 2016-2017 годов (то есть, отобразить, например, разницу между продажами в январе 2017 и январе 2016). Для упрощения задачи будем использовать абсолютную разницу.
Эту задачу можно решить разными способами. Например, мы ходим сджойнить таблицу на 2017 год с самой собой в 2016 году:
select t1.mnth as month_num,
t1.mnth_sls as monthly_sales_2017,
t2.mnth_sls as monthly_sales_2016,
t1.mnth_sls - t2.mnth_sls as difference
from (select extract(month from order_date) as mnth, sum(sales) as mnth_sls
from order_data
where extract(year from order_date) = 2017
group by extract(month from order_date)
) t1
join (select extract(month from order_date) as mnth, sum(sales) as mnth_sls
from order_data
where extract(year from order_date) = 2016
group by extract(month from order_date)
) t2 on t1.mnth = t2.mnth;Фактическое время выполнения такого запроса составит 1481,325 миллисекунды.
Посмотрим на другой запрос: сначала мы считаем сумму продаж для каждого года и месяца затем считаем сумму на прошедший месяц как предыдущее значение, сортируем данные и получаем первые 12 строк.
with monthly_sales_data as (
select extract(year from order_date) as yr,
extract(month from order_date) as mnth,
sum(sales) as monthly_sales
from order_data
where extract(year from order_date) in (2016, 2017)
group by extract(year from order_date), extract(month from order_date)
)
select mnth as mnth_num,
monthly_sales,
lag(monthly_sales) over (partition by mnth order by yr),
monthly_sales - lag(monthly_sales) over (partition by mnth order by yr) as difference
from monthly_sales_data
order by yr desc, mnth
limit 12;Данный запрос выполняется 1068,246 миллисекунд. Оба запроса позволяют получить один и тот же результат, но второй запрос работает быстрее, поскольку операция JOIN сама по себе является достаточно дорогой. На реальных данных разница может быть ещё более очевидна.
Не стоит хвататься за первый пришедший в голову результат. Попробуйте использовать больше своих знаний по теме и придумать несколько различных способов решения задачи (если, конечно, вам позволяют дедлайны).
2. Distinct как решение всех проблем
Бывает такое, что мы пишем запрос и ожидаем возврата только уникальных записей, но почему-то так не получается. Я часто видела, как в таких случаях используют distinct (или аналогичный group by). На практике появление дубликатов может быть связано с разными причинами: например, неверной реализацией логики запроса (и, соответственно, неверными результатами) или неверным представлением о данных.
Предположим, что у нас есть таблица, связывающая штатную должность и сотрудника (positions_employees), таблица для связи штатной единицы к отделу компании (position_department) и справочник отделов (departments).
create table positions_employees (
position_code int, -- код штатной должности (ШД)
position_name text, -- наименование должности
position_share int, -- % от получаемой сотрудником суммы от ставки ШД
position_salary int, -- ставка (заработная плата)
position_employee_quantity decimal, -- количество сотрудников на ШД
employee_id int -- ID сотрудника
);
create table position_department (
position_code int, -- код сотрудника
department_code int -- код отдела
);
create table departments (
department_code int, -- код отдела
department_name text -- наименование отдела
);
insert into positions_employees values
(1, 'SMM менеджер', 100, 10000, 1, 1),
(2, 'Sales менеджер', 25, 30000, 4, 2),
(2, 'Sales менеджер', 25, 30000, 4, 3),
(2, 'Sales менеджер', 25, 30000, 4, 4),
(2, 'Sales менеджер', 25, 30000, 4, 5),
(3, 'Финансовый менеджер', 0, 50000, 1, 6),
(3, 'Финансовый менеджер', 100, 50000, 1, 7),
(4, 'Backend разработчик', 0, 43000, 0, 8);
insert into position_department values
(1, 1),
(2, 1),
(3, 2),
(4, 3);
insert into departments values
(1, 'Отдел маркетинга и продаж'),
(2, 'Бухгалтерия'),
(3, 'Отдел разработки ПО');Пусть перед нами стоит задача: вывести список уникальных комбинаций названий отделов (department_name) и действующих штатных должностей (position_name). Мы точно знаем, что 1 штатная должность (то есть 1 полная ставка) может быть закреплена за одним сотрудником. Попробуем сделать все в "лоб", не задумываясь о бизнес-смысле используемых данных:
select position_name, department_name
from positions_employees
join position_department using(position_code)
join departments using(department_code)Получаем результат с дубликатами:
Попробуем теперь избавиться от дубликатов, используя distinct:
select distinct position_name, department_name
from positions_employees
join position_department using(position_code)
join departments using(department_code)Получаем, казалось бы, результат без повторов:
Дубликатов нет и нам кажется, что все выглядит правильно. Но если бы мы стали использовать эти данные и далее, то могли бы возникнуть проблемы. Например, предоставляя отчет о действующих должностях, можно сделать неверные выводы и упустить должности, за которыми не закреплен сотрудник и для которых необходимо публиковать вакансию. Разобравшись немного в данных, мы узнаем, что в таблице positions_employees колонка position_share показывает, какой % от ставки выплачивается сотруднику, а position_employee_quantity - количество числящихся на должности сотрудниках. То есть если на должности не числится ни один сотрудник - на эту должность необходимо искать кандидата, если у сотрудника нулевая ставка - он не числится на должности (уволен или переведен на другую штатную должность).
Таким образом, с точки зрения бизнеса более корректным будет следующий запрос:
with aggr_position_data as (select position_code, position_name,
sum(position_employee_quantity) as sum_of_emp,
sum(position_share) as sum_of_sal
from positions_employees
group by position_code, position_name)
select position_name, department_name
from aggr_position_data
join position_department using(position_code)
join departments using(department_code)
where sum_of_emp >= 1 and sum_of_sal = 100В данном случае получаем следующий результат:
Видим, что и без вызова "distinct" мы получаем уникальные записи, исключаем при этом должности, на которых никто не числится, и убираем дубликаты для позиций, рассчитанных на частичную занятость.
Этим примером я хочу показать, что возникновение повторов там, где их не должно быть согласно бизнес-логике, может иметь более глубинные причины и требовать более сложные запросы для выборки данных, и distinct может только усыплять нашу бдительность к контролю ошибок. На примере с небольшим количеством данных и 6 колонками в таблице это кристально понятно, но на реальных данных это становится уже не таким очевидным.
3. Непонимание порядка выполнения запроса (или непонимание, зачем этому учили на курсе)
Часто на некоторых курсах упоминают порядок выполнения операций в SQL-запросе. Напомню, что в обычном select-запросе порядок следующий:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
Однако не всегда студенты курсов понимают, насколько эта информация важна для формирования оптимального запроса и для качественной работы с базой данных. Даже без понимания алгоритмов объединения/сканирования/группировки, зная этот порядок выполнения, можно ускорить запрос.
Представим, что у нас есть 2 таблицы: первая с кредитами клиентов (loans), включающую id клиента, сумму задолженности, дату начала задолженности и флаг, указывающий на просрочку кредита (просрочен, если expired = 1, иначе 0). Поскольку данные синтетические, флаг был расставлен согласно придуманной логике.
create table loans (
client_id serial,
loan_sum int,
loan_start date,
expired int
);
insert into loans (loan_sum, loan_start)
select random()*1000000, dd
from generate_series('2015-01-01'::date, '2025-01-01'::date, '1 minute') dd;
update loans
set expired = 1
where client_id::text like '%29%';
update loans
set expired = 0
where client_id::text not like '%29%';Такая же таблица у нас есть и для депозитов клиентов, только с флагом, указывающим на то, может ли клиент снять депозит без потери дохода по процентам (can_be_withdrawn):
create table deposits (
client_id serial,
dep_sum int,
dep_start date,
can_be_withdrawn int
);
insert into deposits (dep_sum, dep_start)
select random()*1000000, dd
from generate_series('2015-01-01'::date, '2025-01-01'::date, '1 minute') dd;
update deposits
set can_be_withdrawn = 1
where client_id::text like '%290%';
update deposits
set can_be_withdrawn = 0
where client_id::text not like '%290%';В обеих таблицах примерно 5,2 миллиона записей. Предположим, что задача следующая: нужно найти клиентов с просроченными кредитами и с "готовыми" для снятия депозитами. Обычно на курсах решение такой задачи выглядит вот так:
select loans.client_id, loan_sum, dep_sum
from loans
join deposits on loans.client_id = deposits.client_id
where expired = 1 and can_be_withdrawn = 1;Видим, что запрос выполнялся 1534,617 миллисекунд. Что происходит в запросе? Сначала мы объединяем две таблицы по 5 млн строк, потом из суммарной выборки мы отбираем строки по нужному фильтру (expired = 1 and can_be_withdrawn = 1). Как уже было сказано выше, объединение таблиц - достаточно "дорогая операция".
Однако если мы перенесем фильтрацию в условие для объединения, можно увеличить скорость выполнения в 2 раза:
select loans.client_id, loan_sum, dep_sum
from loans
join deposits on loans.client_id = deposits.client_id
and expired = 1 and can_be_withdrawn = 1;Такой запрос выполнился за 863,213 миллисекунд, что почти в 2 раза быстрее, чем в предыдущем случае. Аналогичного результата можно добиться, если мы сначала отфильтруем таблицы, а затем объединим таблицы:
select loans_expired.client_id, loan_sum, dep_sum
from (select *
from loans
where expired = 1) loans_expired
join (select *
from deposits
where can_be_withdrawn = 1) deposits_withdrawn
on loans_expired.client_id = deposits_withdrawn.client_id;Время выполнения запроса будет примерно такое же: 860,418 миллисекунд. То есть простым действием мы смогли значительно увеличить скорость. Таких же улучшений можно добиться не только на этапе from-where, но на других этапах выполнения запросов и подзапросов.
P.S.: для совсем новичков мне хочется обратить внимание на порядок выполнения запросов ещё раз, особенно на слово LIMIT. Не забывайте, что это слово в большинстве случаев бесполезно и лучше использовать его только при тестировании запросов (например, можно в подзапросах ограничивать выборку, если работаете с большими объемами и не хочется в процессе формирования запроса особо долго ждать или нагружать базу).
4. «Давайте, навесим здесь индексов…»
Это уже тема не совсем начинающих курсов, но мне кажется, упомянуть её важно. Очень большая проблема рунета в том, что когда начинаешь гуглить «оптимизация запросов», то в основном натыкаешься на материал об индексах. В курсах аналогично: наличие темы «оптимизация», часто предполагает разговор по большей части про индексы. Есть, конечно, преподаватели, которые упоминают понятия «селективность» и «кардинальность» запросов и use case-ы индексов. Но от этого тема оптимизации не становится полнее. Я не отрицаю пользу для производительности при грамотно спроектированных индексах. Я лишь хочу сказать, что важно в целом комплексно подходить к работе с базой и не только индексировать нужные колонки, но и стараться писать эффективные запросы.
Я не буду сейчас рассказывать все, что я знаю про оптимизацию. Отмечу лишь, что в курсах, которые хотя бы где-то говорят про оптимизацию, важно добавить (или хотя бы мельком затронуть, чтобы студенты курса знали, что такое существует) следующие темы:
explain, чтение плана запроса, сбор и обновление статистики
алгоритмы выполнения запроса (merge/hash/nested loop join, group/hash aggregation и так далее) и использование хинтов
основы реляционной алгебры и теории множеств
стратегии оптимизации
Вместо заключения
Список проблем здесь не исчерпывающий. При работе с реальными данными возникает много проблем и с самими данными, и со скоростью запросов, которых часто не хватает новичкам, работающим с данными.
При этом я не считаю курсы каким-то злом. Я сама проходила курсы по SQL, когда мне хотелось попрактиковаться или узнать что-то новое. И я не спорю, что курсы действительно помогают освоить базовый синтаксис языка (некоторые курсы - даже больше и учат не допускать вышеупомянутых ошибок). Но, к сожалению, часто на курсах написание запросов и зубрежка синтаксиса становится самоцелью, в то время как SQL - это инструмент, а реальная цель - работа с базой и анализ данных.
На habr и вне его достаточно статей по приведенным выше темам: про оптимизацию, реляционную алгебру, поэтому дополнить знания синтаксиса, полученного на курсах, можно достаточно быстро, а эффективность запросов может значительно увеличиться.
P.S.: запросы, приведенные в статье, не претендуют на звание самых оптимальных. Принимаю предложения по улучшению в комментариях :)
