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

Почему-то написание 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 в основной части запроса не понадобится).

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

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

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