а) 10 последних постов с именами авторов
Запрос:
SELECT p.id, p.user_id, p.created_at, u.name
FROM posts p
JOIN users u ON p.user_id = u.id
ORDER BY p.created_at DESC
LIMIT $10$;
Пояснения:
- JOIN связывает пост с автором по ключу $p.user\_id=u.id$.
- ORDER BY p.created_at DESC выбирает самые свежие; LIMIT $10$ возвращает только первые $10$ записей (т.н. top‑N).
- Если есть одинаковые created_at и нужен детерминизм, добавьте вторичный критерий, например ORDER BY p.created_at DESC, p.id DESC.
Оптимизация индекса и план:
- Без индекса по created_at СУБД обычно выполнит полное сканирование таблицы posts и сортировку — дорого при большом объёме.
- Полезные индексы:
- простой: CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
- лучше (покрывающий): CREATE INDEX idx_posts_created_at_userid ON posts(created_at DESC, user_id);
- или в СУБД, поддерживающих INCLUDE: CREATE INDEX ... ON posts(created_at DESC) INCLUDE(user_id).
Покрывающий индекс позволяет найти top‑N по индексу и затем только обратиться к таблице за данными пользователей (или вообще избежать обращения, если все нужные колонки в индексе).
- Индекс на users.id (обычно PK) обязателен для быстрого поиска имени по user_id.
- План: цель — получить Index Scan (или Index Only Scan) по posts с ограничением top‑N и затем nested loop probe по users. Без индекса вы увидите Seq Scan + Sort в EXPLAIN. Запуск EXPLAIN (или EXPLAIN ANALYZE) покажет, делает ли СУБД Top‑N оптимизацию или полную сортировку.
б) Пользователи без постов
Вариант LEFT JOIN:
SELECT u.id, u.name
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL;
Вариант NOT EXISTS (рекомендуется, безопаснее с NULL):
SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM posts p WHERE p.user_id = u.id
);
Пояснения:
- LEFT JOIN ... WHERE p.id IS NULL — классический anti‑join: берём всех пользователей и фильтруем тех, у кого нет соответствующих постов.
- NOT EXISTS делает логически то же самое и часто даёт такой же или более оптимальный план (анти‑джоин) в современных СУБД.
- Не рекомендую NOT IN (SELECT user_id FROM posts) если user_id может быть NULL — поведение отличается и может вернуть пустой набор.
Индексы и план:
- Ключевой индекс: CREATE INDEX idx_posts_user_id ON posts(user_id). Он делает проверку наличия постов по пользователю быстрой (индексный поиск вместо полного сканирования).
- План может быть:
- Nested Loop + Index Scan по posts (для небольшого числа users или когда для каждого user делается быстрый индексный поиск в posts).
- Hash Anti Join или Merge Anti Join (при больших объёмах и соответствующих условиях/статистиках).
- Важно: актуальная статистика (ANALYZE) помогает оптимизатору выбрать правильный план. При неправильной статистике СУБД может выбрать дорогой Seq Scan.
Короткие рекомендации по производительности
- Для запроса (а) делайте индекс на created_at (или покрывающий индекс с user_id) — это даёт большую выигрышность при больших объёмах, потому что можно выполнить Top‑N по индексу без сортировки всей таблицы.
- Для запроса (б) делайте индекс на posts.user_id — это резко ускоряет анти‑джойн/NOT EXISTS.
- Всегда проверяйте EXPLAIN / EXPLAIN ANALYZE: ищите Seq Scan+Sort (плохо) vs Index Scan / Index Only Scan / Top‑N (хорошо). Если план плохой — смотрите статистику, cardinality, или добавляйте/перестраивайте индекс.
- Не создавайте избыточные индексы; ориентируйтесь на реальные профили запросов и нагрузку на запись.