Как быстро писать сложные и надежные SQL-запросы на выборку коллекций

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

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

Прошу держать в голове, что данные принципы справедливы для относительно небольших систем (миллионы, а не миллиарды записей в таблицах), когда эффект масштаба еще не оказывает значительного влияния на структуру таблиц и запросов. Впрочем к данному размеру можно отнести 90% сайтов в интернете, в том числе и системы автоматизации бизнеса, которыми в основном я занимаюсь.

Изложенные в данной статье подходы справедливы только для выборки данных в рамках некой коллекции. Вычисления же агрегатов будут строиться чуть-чуть по другому.

Итак, традиционный путь

Предположим, у нас есть интернет-магазин и нам необходимо выводить список товаров, используя набор критериев (фильтров). Мы пишем:

SELECT * FROM product
WHERE price > :start_price AND price < :end_price;

Все прекрасно, мы сделали покрывающий индекс по цене, радуемся простоте и производительности.

Но что, если нужно выводить рубрики, в которые включены товары из выборки? Отлично, можно добавить соединение с рубриками, потом GROUP BY и агрегатную функцию.

SELECT p.*, GROUP_CONCAT(c.title SEPARATOR ‘, ‘) as categories
FROM product p
LEFT JOIN product_category pc ON pc.product_id=p.id
LEFT JOIN category c ON c.id=pc.category_id
WHERE p.price > :start_price AND p.price < :end_price
GROUP BY p.id;

Ну.. это работает. А что если нам еще нужен список тегов? И обычно здесь программист добавляет еще JOIN`ов

SELECT p.*, GROUP_CONCAT(c.title SEPARATOR ‘, ‘) as categories, GROUP_CONCAT(t.tag SEPARATOR ‘, ‘) as tags
FROM product p
LEFT JOIN product_category pc ON pc.product_id=p.id
LEFT JOIN category c ON c.id=pc.category_id
LEFT JOIN product_tag pt ON pt.product_id=p.id
LEFT JOIN tag t ON t.id=pc.tag_id
WHERE p.price > :start_price AND p.price < :end_price
GROUP BY p.id;

Это уже не работает. Категории и теги начинают задваиваться, ну ладно, мы же можем сделать GROUP_CONCAT(DISTINCT ...)! А что если нужно подсчитывать количество отзывов по каждому товару? Еще JOIN? И мы оказываемся в ситуации, когда каждый следующий JOIN начинает мешать предыдущему.

Здесь нужно задуматься.

Альтернативный подход

Каждый запрос следует проанализировать и разделить на небольшие части таким образом, чтобы можно было отнести эти части к одной из двух групп:

Фильтрация данных (сужение выборки)
Представление данных (вывод данных и форматирование)

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

Рассмотрим пример соединения с фильтрацией. Например, нам нужно показать только те товары, производители которых зарегистрированы в России:

SELECT p.* FROM product p
INNER JOIN manufacturer m ON m.id=p.manufacturer_id AND m.country_key=’RU’
WHERE p.price > :start_price AND p.price < :end_price;

Здесь INNER JOIN не добавляет новые колонки, но сужает выборку. Это явное поведение по типу фильтра.

Распределив части запроса на группы, нужно собрать из них финальный запрос в три простых шага:

Построить основной запрос на выборку без GROUP BY и по возможности используя покрывающие индексы
Добавить соединения, обеспечивающие фильтрацию (сужение выборки).
Добавить соединения, обеспечивающие представление, здесь можно использовать GROUP BY

Соединение с таблицами только по уникальному ключу

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

Идея проста — не допустить разрастания выборки по количеству строчек после соединения (и таким образом GROUP BY в основной части запроса не понадобится).

В примере выше с производителями — принцип обеспечен уникальностью ключа manufacturer.id, таким образом соединение с таблицей по этому ключу не приведет к повторению строчек из основной таблицы.

Давайте посмотрим, как же быть с категориями и тегами из первых примеров. Используем подзапрос:

SELECT p.*, pc.categories, pt.tags FROM product
LEFT JOIN (
	SELECT pc.product_id, GROUP_CONCAT(c.title SEPARATOR ‘, ‘) as categories
	FROM product_category pc
	INNER JOIN category c ON c.id=pc.category_id
	GROUP BY pc.product_id;
) pc ON pc.product_id=p.id
LEFT JOIN (
	SELECT pt.product_id, GROUP_CONCAT(t.title SEPARATOR ‘, ‘) as tags
	FROM product_tag pt
	INNER JOIN tag t ON c.id=pt.tag_id
	GROUP BY pt.product_id;
) pt ON pt.product_id=p.id
WHERE p.price > :start_price AND p.price < :end_price;

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

Давайте добавим фильтр по тем товарам, у которых пользовательская оценка равна 5 звездам:

SELECT p.*, pc.categories, pt.tags FROM product
INNER JOIN (
	SELECT DISTINCT r.product_id
	FROM review r
	WHERE r.starts=5
) r ON r.product_id=p.id
LEFT JOIN (
	...
) pc ON pc.product_id=p.id
LEFT JOIN (
	...
) pt ON pt.product_id=p.id
WHERE p.price > :start_price AND p.price < :end_price

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

Порядок соединений — от фильтров к представлениям

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

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

Отказ от GROUP BY в основной части запроса

Как только мы начинаем практиковать изложенные выше принципы, GROUP BY из основной части запроса обычно исчезает сам по себе за ненадобностью.

Но если все же он после всех манипуляций остался, то, скорее всего, это обусловлено объективной ситуацией. У вас, скорее всего — агрегатная выборка, а не выборка коллекции.

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