SQL-запросы. SELECT, JOIN, WHERE и математика

Категория: Код

– Автор: Игорь (Администратор)

В рамках данной статьи, я расскажу вам о том, что стоит всегда помнить о такой науке как математика, когда вы составляете свои sql-запросы и используете такие операторы, как select, join и where.

Каждый из нас хотя бы слышал такое слово как математика. Однако, не все понимают, как ее можно и зачем нужно применять при построении sql-запросов. И отчасти этих людей можно понять, ведь существующие базы данных, пусть даже и в упрощенном виде, но умеют строить планы запросов и оптимизировать процесс формирования результатов, исходя из существующей статистики и наличия ключей с индексами.

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

Примечание: Статья в основном предназначена для тех, кто не особо хорошо знаком с устройством БД. Многие опытные люди итак это прекрасно знают. Тем не менее, ничего плохого в повторении нет.

Оператор WHERE и простая математика SQL-запросов

И первым, что я хотел бы обсудить это привычку выносить условия склеивания строк из оператора join в оператор where, не учитывая простую математику. Так делать никогда не стоит. Конечно, когда перед вами все условия отсеивания это удобно. Однако, периодически это может приводить к элементарным проблемам с производительностью.

Дело в том, что изначально (как задумано по стандарту), правила фильтрации в операторе where применяются уже после того, как были склеены все записи таблиц из операторов join. Это означает, что вместо того, чтобы фильтровать только нужные строки, БД будет фильтровать в запросе абсолютно все строки, как при cross join (когда каждая строка одной таблицы соединяется с каждой записью другой таблицы).

Утрируя, представим что у вас есть две таблицы - user и profile, никаких индексов не сделано, так как банально в таблицах по 100 строк, так же допустим, что планировщик БД выполняет запрос ровно таким образом, как вы его написали. При этом известно, что одному пользователю соответствует один профиль. Задача sql-запроса, получить пользователей только с нужными данными в профиле и основной таблице.

Вы радостно пишите select и выносите все правила фильтрации в where. Что происходит? В такой ситуации у вас получится, что при выдаче нужно будет фильтровать 100 * 100 = 10 000 строк, вместо 100 склеенных. Вроде различие небольшое, а разница производительности в два порядка, вот вам и простая математика.

Поэтому стоит сразу завести себе хорошую привычку - оставлять в where только те правила фильтрации, которые необходимо применять в самом конце select, то есть после склейки таблиц по join.

Примечание: Пример хоть и простой, но весьма показательный. Дело в том, что такое легко происходит в сложных sql-запросах, когда имеется большое количество взаимосвязей между таблицами. В данной ситуации, всегда есть шанс, что планировщик БД не все корректно учтет, например, изменит порядок склейки таблиц (об этом далее). Кроме того, в where становится настолько много условий, что разобрать "что к чему относится" становится труднее, чем при обычных запросах.

Оператор JOIN в SELECT и простая математика при подсчете операций в SQL-запросах

Кажется, чего может быть проще, чем наляпать join-ов внутри select-а, указать корректные правила склейки и получить супер оптимальный sql-запрос. Так то оно так, но на практике не так. Начну немного издалека. Вы когда нибудь видели воронку? А пользовались? Тогда легко представляете, что если вливать воду в большем количестве, чем она убывает из воронки, то воронка быстро заполнится и вода начнет переливаться через края. Простая математика и немного начальной физики.

И какое это отношение имеет к join спросите вы? Самое, что ни на есть прямое, так как суть join не только в склейке данных, но и в том, чтобы самих записей при получении select-а оставалось как можно меньше.

Рассмотрим пример. Возьмем те же таблицы user и profile, только будем считать, что все индексы есть. Так же добавим таблицу user_log, где будут храниться действия каждого пользователя. Кроме того, известно следующее. Пользователей 100 человек. Примерно у половины пользователей нет профиля. И каждый сделал в среднем 10 действий. Задача в том, чтобы получить все действия пользователей, у которых есть профиль.

В принципе, весьма тривиальная задачка. Однако, в зависимости от порядка следования join производительность может сильно разниться. Напишем следующие два запроса.

Первый запрос:

select *
from `user` u
-- Вначале склеим все записи действий
join `user_log` ul
on ul.user_id = u.user_id
-- Теперь выберем тех пользователей, у которых есть профиль
join `profile` p
on p.user_id = u.user_id

И второй запрос:

select *
from `user` u
-- Вначале отсеем тех пользователей, у которых есть профиль
join `profile` p
on p.user_id = u.user_id
-- Теперь склеим все записи действий
join `user_log` ul
on ul.user_id = u.user_id

Теперь, воспользуемся операциями "умножить" и "сложить" из элементарной математики.

Рассмотрим первый запрос. Что происходит? Вначале вы получаете 100 * 10 = 1000 строк, склеивая таблицы пользователя и его действий, а затем фильтруете эти 1000 строк по наличию профиля и получаете необходимые 500. Итого, не вдаваясь в детали, вы получаете 1000 + 1000 = 2000 операций для одного sql-запроса.

Рассмотрим второй запрос. Вначале вы фильтруете 100 записей и получаете 50 записей. Затем вы соединяете эти записи с логами пользователя и получаете 50 * 10 = 500 строк. Итого, опять же не вдаваясь в детали, вы получаете 100 + 500 = 600 операций в select запросе.

Примечание: Помните про индексы. Считаем, что при их использовании выполняется всего одна операция. В реальности, конечно, там все немного сложнее, но для показательно примера считаем поиск и склейку равным 1.

Думаю, несложно заметить, что второй запрос (600 операций) выполнится в 3,3 раза быстрее, чем первый запрос (2000 операций), несмотря на то, что по сути они выполняют одно и тоже, а изменен лишь порядок склейки.

Как видите, составляя sql-запросы, всегда необходимо помнить об элементарной математике при использовании операторов where и join в select-ах.

• PHP - динамические имена переменных в чем проблема безопасности?
• SQL-запросы. Нужно ли перечислять конкретные поля в SELECT?