Немного советов о том, как правильно писать запросы к SQL-базе. И немного про ORM.

0. Очевидное

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

1. Не бойся делать SELECT

Если у тебя просто по id выбираются записи из базы - это ок. Не нужно бояться сделать один лишний простой SELECT к базе.

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

SELECT * FROM user WHERE id=:id

2. Избегай цикличных запросов

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

SELECT * FROM user WHERE id=1
SELECT * FROM user WHERE id=2
SELECT * FROM user WHERE id=3

у тебя должен быть всего один запрос

SELECT * FROM user WHERE id IN (1,2,3)

3. Не оптимизируй джойнами

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

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

Выборка по нескольким таблицам

Например, нужно выбрать сколько у пользователя машин:

SELECT user.id, COUNT(car.id) AS cars_count FROM user
JOIN car ON car.user_id = user.id
GROUP BY user.id

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

Транзитивные условия

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

SELECT user.* FROM user
JOIN city ON city.id = user.city_id
JOIN region ON region.id = city.region_id
JOIN country ON country.id = region.country_id
WHERE country.id=:id

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

Если твой запрос не попадает под эти 2 пункта, то вероятно тебе не нужен джойн и его лучше не использовать.

4. Один запрос - один тип сущности

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

Пример возврата двух типов сущностей:

SELECT user.*, city.* FROM user
JOIN city ON city.id = user.city_id
WHERE user.id IN (<ids>)

Данный запрос породит дублирование данных, которое нужно будет как-то разруливать. Плюс за джойн база тебе спасибо не скажет.

А должно быть так:

SELECT * FROM user WHERE id IN (<ids>)
SELECT * FROM city WHERE id IN (<ids>)

Мы делаем селект юзеров. Затем собираем необходимый список id городов, и по ним делаем второй селект. Просто и прозрачно.

5. Не бойся работать руками

Этот пункт - логическое продолжение 4го пункта (или даже повторение).

Нам нужно выбрать большое количество связанных сущностей. Вопрос: какое оптимальное количество запросов нам для этого нужно? Ответ простой - по одному запросу на каждый тип сущности, ни больше ни меньше.

Пример: нужно выбрать Страны с Областями, с Городами и с Улицами. Для того, чтобы это сделать нужно ровно 4 простых селекта:

SELECT * FROM country
SELECT * FROM region WHERE country_id IN (<ids>)
SELECT * FROM city WHERE region_id IN (<ids>)
SELECT * FROM street WHERE city_id IN (<ids>)

Но сложность в том, что между этими запросами вам нужно вычислить промежуточные id сущностей на клиенте. И текущий пункт о том, что не нужно бояться этого делать. Почти всегда это делается простыми map-функциями.

Пример кода на гипотетическом js:

countries = countriesRepo.selectAll()
countriesIds = countries.map(it => it.id)

regions = regionsRepo.selectAllByCountries(countriesIds)
regionsIds = regions.map(it => it.id)

cities = citiesRepo.selectAllByRegions(regionsIds)
citiesIds = cities.map(it => it.id)

streets = streetsRepo.selectAllByCities(citiesIds)

Все данные есть, можно делать с ними теперь что угодно. Промежуточная манипуляция это совсем не страшно. Лучше отправить простой запрос к базе, и потом на клиенте все разложить как надо, чем пытаться делать запрос-франкенштейн, чтобы он вернул тебе данные в каком-то удобном для тебя виде, или потому что так работает твоя ORM.

6. Именование таблиц со связями many-many

В таблицах со связями не должно быть данных, кроме id (и возможно временных меток создания).

Есть таблицы, которые хранят связи many-many, и их именуют обычно так: users_has_cars, user_to_car, user_car и т.д. По названию сразу понятно, что там хранятся связи в виде списков id.

Когда в эти таблицы добавляются какие-то поля с другими данными (например crashes_count, insurance_cost), эти таблицы нужно превращать в отдельные сущности, и соответственно, переименовывать таблицы. В противном случае ты рискуешь столкнуться в проекте с такими полноценными сущностями, как UserHasCar. И тебе придется с ними работать наравне с User и Car. Согласись, что CarOwnership лучше чем UserHasCar, хотя бы потому, что это существительное, а не утверждение.

Как только в таблицу many-many добавляется поле с данными - переименовывай таблицу в полноценную сущность.

Вместо заключения, про ORM

Иногда программисты ошибочно возводят в абсолют разные технологии. Технология объявляется чем-то идеальным, а все что выходит за ее рамки применимости - считается чем-то дурным и неправильным. ORM - не исключение, и я бы хотел развеять эту догматичность.

Большинство ORM не умеют решать проблему N+1 (цикличные запросы при получении связанных сущностей), часто джойнят запросы, пытаясь получить много сущностей из одного запроса. Когда эти пункты накладываются в проде под нагрузкой, ты внезапно обнаруживаешь, что твой сервер начинает потеть и долго отвечать.

ORM - не панацея, а может быть даже и антипаттерн (холиварный момент). Мне неизвестны ORM, которые бы учитывали все пункты, которые я привел выше. Поэтому я считаю ORM плохим подходом. А, например связка репозитории+object-мапперы полностью заменяет ORM, но при этом является более гибкой, прозрачной и простой.

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