Используйте EXISTS вместо COUNT при проверке существования записей

Использование COUNT

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

Обычный SQL-запрос:

SELECT COUNT(*)
FROM `post_likes`
WHERE `member_id` = 1
AND `post_id` = 1

В MySQL COUNT(*) оптимизирован, и он быстрее и эффективнее, чем, например, COUNT(id).

Запрос в Laravel Eloquent (с использованием отношения postLikes):

// Понравилось ли пользователю сообщение
$member->postLikes()->count() > 0;

Обратите внимание на скобки после имени отношения postLikes(). Они означают, что мы используем это отношение для создания запроса к связанной таблице и установки внешнего ключа для нас. Если бы мы использовали $member->postLikes->count() без скобок, мы бы получили все связанные записи, а затем произвели подсчёт. Это привело бы к более дорогостоящему запросу к БД, а также к увеличению объёма используемой памяти, поскольку все эти записи должны быть сохранены в памяти.

Это "заставит" БД перебрать все записи, удовлетворяющие этим условиям. И если ваша таблица достаточно велика, это может занять некоторое время. Конечно, возможно, в миллисекундах, но всё равно это будет лишняя работа, так как она не знает, что вас интересует только "существование" записи, а не точный подсчёт.

Конечно, если таблицу "правильно" проиндексировать и использовать составной индекс на столбцах member_id и post_id, результат будет довольно быстрым в этом случае, но в других случаях он всё равно может быть оптимизирован.

Использование подзапроса EXISTS

Лучшим решением будет использование подзапроса EXISTS. Он доступен в MySQL начиная с версии 5.7, поэтому нет причин не использовать его.

С документацией MySQL по EXISTS можно ознакомиться здесь. Существует также подзапрос NOT EXISTS.

EXISTS работает, заключая запрос в подзапрос SELECT:

SELECT EXISTS(
  SELECT *
  FROM `post_likes`
  WHERE `member_id` = 1
  AND `post_id` = 1
)

Неважно, извлекает ли ваш SELECT все столбцы (*) или только 1, SELECT будет отброшен в запросе EXISTS.

Этот запрос вернёт true, если в подзапросе есть хотя бы одна запись, или false, если нет ни одной записи, удовлетворяющей вашим условиям. MySQL выйдет из "цикла", когда найдёт первую запись, и именно это делает его более производительным, чем COUNT.

В Laravel вы можете использовать метод exists в конструкторе запросов:

// Понравилось ли пользователю сообщение
$member->postLikes()->exists();

Eloquent поместит запрос в подзапрос EXISTS.

Eloquent также предоставляет whereExists, whereNotExists, doesntExist, withExists и другие возможности, позволяющие построить нужный запрос.

Правильный пример использования EXISTS

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

Например, при получении списка постов для отображения на странице я хочу знать, понравился ли пост пользователю, чтобы показать соответствующую иконку UI. Это может привести к ситуации N+1, когда для каждого поста придётся выполнять отдельный SQL-запрос для проверки наличия записи.

Или можно использовать подзапрос EXISTS:

SELECT `id`, `title`, `content`, exists(
  SELECT *
  FROM `post_likes`
  WHERE `posts`.`id` = `post_likes`.`post_id`
  AND `member_id` = 1
) AND `is_liked`
FROM `posts`

Это будет выполнено в одном оптимизированном SQL-запросе и предоставит информацию о том, понравилось ли пользователю сообщение, в сгенерированном столбце is_liked. Если быть точным, MySQL будет делать N+1 подзапросов для проверки существования, но это будет оптимизировано и выполнено внутри.

В Laravel для этого можно использовать withExists:

$posts = Post::query()
  ->select(['id', 'title', 'content'])
  ->withExists([
        'postLikes as is_liked' => function ($query) {
        $query->where('member_id', $member->id);
      }
    ]);

is_liked будет добавлен как атрибут для каждой модели $post.

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