SQL
Задавайте базе данных правильные вопросы: JOIN, агрегаты, оконные функции, индексы и транзакции.
За 30 секунд
SQL — язык, на котором задают вопросы реляционным базам данных и изменяют их данные. Вы описываете, что хотите получить (такие колонки, из таких таблиц, по таким правилам), а база сама решает, как это достать. Хорошее знание SQL, включая индексы и транзакции, часто отделяет страницу, которая грузится за 50 миллисекунд, от страницы, которая падает по таймауту.
Почему я это использую
- Я пользуюсь SQL каждый день: почти каждый мой продукт хранит основные данные в MySQL, MariaDB или PostgreSQL, а ORM лишь прячут SQL, но не заменяют его.
- Отчёты и аналитику (продажи, когорты, оценка складских запасов) я пишу прямо на SQL, где агрегаты и оконные функции в одном запросе делают то, что заняло бы сотни строк кода приложения.
- Я читаю планы запросов, потому что проблемы производительности в веб-приложениях чаще всего — проблемы базы данных: отсутствующий индекс или цикл N+1.
- Я полагаюсь на транзакции, чтобы деньги, остатки на складе и бонусные балансы оставались корректными, когда множество запросов трогает одни и те же строки.
Как это работает
01Основа: SELECT, WHERE, ORDER BY и логический порядок
Реляционная база данных хранит данные в таблицах: строки — это записи, колонки — поля, а первичный ключ однозначно идентифицирует каждую строку. Запрос SELECT читает данные. Полезно знать логический порядок вычисления его частей, потому что он объясняет многие ошибки: сначала FROM и JOIN (какие строки существуют), затем WHERE (фильтрация строк), GROUP BY (формирование групп), HAVING (фильтрация групп), SELECT (вычисление колонок), ORDER BY (сортировка) и наконец LIMIT. Вот почему нельзя использовать алиас из SELECT внутри WHERE. NULL означает «неизвестно», а не ноль или пустоту: сравнивайте через IS NULL, потому что NULL = NULL не является истиной. Всегда передавайте пользовательский ввод как параметры (плейсхолдеры), а не склеивайте строки, — это защищает от SQL-инъекций.
-- Ten most recent paid orders above 50.00
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
AND total > 50.00
AND cancelled_at IS NULL
ORDER BY created_at DESC
LIMIT 10;
-- From application code, always use parameters:
-- SELECT * FROM users WHERE email = ? -- (MySQL)
-- SELECT * FROM users WHERE email = $1 -- (PostgreSQL)
02JOIN, GROUP BY и подзапросы
Данные разбиты по таблицам, чтобы избежать дублирования, а JOIN собирает их обратно. INNER JOIN оставляет только строки, совпавшие с обеих сторон; LEFT JOIN оставляет все строки левой таблицы и заполняет отсутствующие совпадения значением NULL — так находят «клиентов без заказов». GROUP BY сворачивает строки в группы, чтобы агрегатные функции (COUNT, SUM, AVG, MIN, MAX) работали по каждой группе; каждая выбранная колонка должна быть либо в группировке, либо в агрегате. HAVING фильтрует после группировки, WHERE — до неё. Подзапрос — это запрос внутри другого запроса; EXISTS — понятный способ спросить «есть ли хотя бы одна связанная строка?». Common table expressions (WITH name AS (...)) дают подзапросам читаемые имена и сильно упрощают ревью длинных отчётов.
-- Revenue per customer this year, only customers above 1,000
WITH paid AS (
SELECT customer_id, total
FROM orders
WHERE status = 'paid' AND created_at >= '2026-01-01'
)
SELECT c.id, c.name, COUNT(*) AS orders, SUM(p.total) AS revenue
FROM customers c
JOIN paid p ON p.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(p.total) > 1000
ORDER BY revenue DESC;
-- Customers who never ordered
SELECT c.id, c.email FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
03Оконные функции
Оконные функции считают по набору связанных строк, не сворачивая их, — в отличие от GROUP BY. Вы сохраняете каждую строку заказа, а рядом можете показать нарастающий итог клиента, ранг заказа или дату предыдущего заказа. Окно задаётся конструкцией OVER: PARTITION BY разбивает строки на группы (по клиенту), ORDER BY задаёт порядок внутри каждой группы. Частые функции — ROW_NUMBER (уникальная последовательность, идеальна для «последней строки в группе»), RANK и DENSE_RANK, LAG и LEAD (предыдущая или следующая строка), а также нарастающие SUM или AVG. Они поддерживаются в PostgreSQL, MySQL 8+, MariaDB 10.2+ и SQLite и заменяют множество медленных self-join'ов и циклов в коде приложения.
-- Each customer's latest order, plus days since their previous one
SELECT customer_id, id, total, created_at, days_since_previous
FROM (
SELECT
o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn,
created_at - LAG(created_at) OVER (
PARTITION BY customer_id ORDER BY created_at
) AS days_since_previous
FROM orders o
WHERE status = 'paid'
) ranked
WHERE rn = 1;
-- (date arithmetic shown in PostgreSQL syntax)
04Индексы и как работает B-tree
Без индекса базе приходится читать каждую строку, чтобы найти совпадения (полное сканирование таблицы), — как искать что-то в книге без предметного указателя. B-tree-индекс — это отсортированное сбалансированное дерево значений ключа с указателями на строки. Поскольку оно отсортировано и неглубоко (обычно три-четыре уровня даже для миллионов строк), база может перейти к значению или к началу диапазона за несколько чтений страниц, а затем идти вперёд. Это помогает и ORDER BY с LIMIT. Составной индекс по (customer_id, created_at) отсортирован сначала по первой колонке, затем по второй, поэтому он обслуживает запросы с фильтром по customer_id или по customer_id и created_at, но не по одному created_at — это правило левого префикса. Ставьте колонки с равенством первыми, а колонки диапазона или сортировки — последними. Индексы не бесплатны: каждый INSERT и UPDATE должен их поддерживать, поэтому индексируйте под реальные запросы, а не каждую колонку.
-- Serves: WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
-- Unique index also enforces a business rule
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- Not helped by the composite index above (no leftmost column):
-- SELECT * FROM orders WHERE created_at > '2026-10-01';
-- Wrapping the column in a function also prevents index use:
-- WHERE DATE(created_at) = '2026-11-05' -- slow
-- WHERE created_at >= '2026-11-05'
-- AND created_at < '2026-11-06' -- uses the index
05Чтение EXPLAIN и исправление N+1
EXPLAIN показывает план, выбранный базой: какие таблицы она читает, в каком порядке и использует ли индекс или сканирует всё. EXPLAIN ANALYZE (PostgreSQL и MySQL 8.0.18+) реально выполняет запрос и показывает фактическое время и количество строк. Ищите полные сканирования больших таблиц (Seq Scan в PostgreSQL, type ALL в MySQL), большие расхождения между ожидаемым и фактическим числом строк и дорогие сортировки. Но самый частый баг производительности в веб-приложениях — не один медленный запрос, а N+1: вы загружаете 50 заказов одним запросом, а потом в цикле делаете ещё по запросу на каждый заказ, чтобы получить клиента, — 51 запрос вместо 2. ORM позволяют легко написать такое случайно. Решение — eager loading (with() в Laravel, include в Prisma) или один JOIN, плюс счётчик запросов в разработке или тестах, чтобы ловить регрессии.
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- Good: Index Scan using idx_orders_customer_created
-- Bad: Seq Scan on orders + Sort (missing or unusable index)
-- N+1 in an ORM (pseudo-code):
-- orders = Order.limit(50) -- 1 query
-- for o in orders: o.customer.name -- +50 queries
-- Fix: load customers in one go
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC LIMIT 50;
06Транзакции и уровни изоляции
Транзакция объединяет операторы в одну единицу по принципу «всё или ничего»: либо сохраняются все изменения (COMMIT), либо ни одного (ROLLBACK). Это буква A в ACID (atomicity, consistency, isolation, durability). Изоляция определяет, что параллельные транзакции видят друг у друга. READ COMMITTED (по умолчанию в PostgreSQL) видит только закоммиченные данные, но повторное чтение строки может вернуть более новое значение. REPEATABLE READ (по умолчанию в MySQL InnoDB) даёт стабильный снимок на всю транзакцию. SERIALIZABLE ведёт себя так, будто транзакции выполнялись по одной, но может прерывать некоторые из них, поэтому приложение должно их повторять. Для счётчиков, остатков и балансов избегайте гонки «прочитать–изменить–записать»: либо обновляйте атомарно (SET stock = stock - 1 WHERE stock >= 1), либо блокируйте строку через SELECT ... FOR UPDATE. Держите транзакции короткими и никогда не ждите внешний HTTP-вызов, удерживая блокировки.
BEGIN;
-- Lock the wallet row so concurrent requests wait their turn
SELECT balance FROM wallets WHERE customer_id = 42 FOR UPDATE;
-- Atomic, guarded update: fails safely if funds are insufficient
UPDATE wallets
SET balance = balance - 1500
WHERE customer_id = 42 AND balance >= 1500;
INSERT INTO wallet_transactions (customer_id, amount, reason)
VALUES (42, -1500, 'order_1001');
COMMIT; -- or ROLLBACK if the UPDATE affected 0 rows
Когда применять
- Любые данные со связями и правилами: пользователи, заказы, товары, платежи, бронирования.
- Отчёты и аналитика, где агрегаты и оконные функции отвечают на вопросы одним запросом.
- Всё, что требует корректности при конкурентном доступе: балансы, остатки на складе, ограничения уникальности.
- Отладка медленных страниц, где EXPLAIN и лог запросов показывают, что ORM делает на самом деле.
Когда не стоит
- Очень частые простые обращения «ключ–значение» или кэширование, где Redis быстрее и проще.
- Полнотекстовый поиск с устойчивостью к опечаткам и ранжированием по релевантности в большом масштабе, где лучше подходит поисковый движок (Meilisearch, Algolia, Elasticsearch).
- Огромные аналитические нагрузки по миллиардам событий, где правильный инструмент — колоночное хранилище (ClickHouse, BigQuery).
Альтернативы
- ORM и query builder'ы
Eloquent, Prisma, Drizzle или Doctrine генерируют SQL для повседневного CRUD; SQL всё равно стоит знать, чтобы понимать и тюнить то, что они генерируют.
- Документные базы данных (MongoDB, Firestore)
Гибкие JSON-документы и простое масштабирование, но JOIN'ы и транзакции на несколько документов сложнее.
- Колоночные хранилища (ClickHouse, BigQuery)
Созданы для аналитики по огромным наборам данных; при этом тоже говорят на SQL.
Лучшие практики
- Всегда используйте параметризованные запросы; никогда не склеивайте пользовательский ввод в SQL.
- Индексируйте под реальные паттерны запросов: колонки с равенством первыми, колонки диапазона или сортировки последними, и проверяйте через EXPLAIN.
- Выбирайте только нужные колонки и пагинируйте большие списки (keyset-пагинация для глубоких страниц).
- Оборачивайте многошаговые изменения в транзакции, держите их короткими и используйте атомарные обновления или FOR UPDATE для балансов и остатков.
- В разработке следите за количеством запросов на один HTTP-запрос, чтобы рано ловить проблемы N+1.
- Храните деньги как целые числа в минимальных единицах или в DECIMAL, никогда во FLOAT.
- Закрепляйте правила и в базе данных: NOT NULL, внешние ключи и ограничения уникальности.
Частые ошибки
- Запросы N+1, спрятанные за циклом в ORM.
- Оборачивать индексированные колонки в функции (DATE(created_at), LOWER(email)), из-за чего индекс не может использоваться.
- Использовать OFFSET 100000 для глубокой пагинации, которая всё равно читает и отбрасывает все пропущенные строки.
- Обновлять балансы или остатки по схеме «прочитать, потом записать» без блокировки или атомарного UPDATE, теряя обновления под нагрузкой.
- Сравнивать через = NULL вместо IS NULL и забывать, что NULL заставляет NOT IN вести себя неожиданно.
Глубже
07MVCC: как базы данных позволяют читателям и писателям работать одновременно
И PostgreSQL, и MySQL InnoDB используют многоверсионное управление конкурентным доступом (MVCC): вместо того чтобы заставлять читателей ждать писателей, база хранит несколько версий строки и показывает каждой транзакции ту версию, которая соответствует её снимку (snapshot). Движки хранят старые версии по-разному, и это определяет их поведение в продакшене. PostgreSQL при каждом UPDATE записывает новую версию строки, а старую оставляет в таблице как dead tuple; позже autovacuum освобождает место. Если autovacuum не успевает, таблицы и индексы раздуваются, а запросы замедляются. InnoDB обновляет строки на месте, а старые версии хранит в undo log; долгая транзакция вынуждает его хранить все версии с момента её снимка, раздувая history list. Вывод для обоих движков одинаков: долгие транзакции дороги, даже если только читают, потому что удерживают старые версии. Находите их через pg_stat_activity или information_schema.innodb_trx и задавайте фоновым задачам statement timeout или idle-in-transaction timeout.
-- PostgreSQL: long or idle transactions holding old snapshots
SELECT pid, state, now() - xact_start AS age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY age DESC
LIMIT 10;
-- PostgreSQL: tables with many dead tuples (vacuum falling behind)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
-- MySQL InnoDB: open transactions and their age
SELECT trx_id, trx_started, trx_rows_locked
FROM information_schema.innodb_trx ORDER BY trx_started;
08Продвинутая индексация: covering, partial, expression и skip scan
Покрывающий (covering) индекс содержит все столбцы, нужные запросу, поэтому база может ответить только по индексу, не заглядывая в таблицу: index-only scan в PostgreSQL, «Using index» в EXPLAIN у MySQL. Конструкция INCLUDE в PostgreSQL добавляет дополнительные столбцы-нагрузку, не делая их частью ключа сортировки. Частичный (partial) индекс (PostgreSQL) индексирует только строки, подходящие под условие, например неотправленные заказы, поэтому он остаётся маленьким и быстрым, когда большинство строк не нужны. Индекс по выражению индексирует результат функции, что решает проблему «WHERE LOWER(email) = ?»; MySQL поддерживает функциональные части ключа с 8.0.13. У правила левого префикса есть и современный нюанс: MySQL 8.0 и PostgreSQL 18 умеют делать skip scan по составному индексу, когда у ведущего столбца мало различных значений, рассматривая его как несколько меньших range scan. Это помогает, но это запасной вариант, а не повод проектировать индексы в неправильном порядке.
-- Covering index (PostgreSQL): answer the list query from the index
CREATE INDEX idx_orders_cust_created_inc
ON orders (customer_id, created_at DESC) INCLUDE (total, status);
-- Partial index: only the rows the dashboard actually reads
CREATE INDEX idx_orders_unshipped
ON orders (created_at) WHERE shipped_at IS NULL;
-- Expression index for case-insensitive lookups
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));
-- MySQL 8.0.13+: CREATE INDEX idx_email_lower ON users ((lower(email)));
-- Query must match the expression to use it
SELECT id FROM users WHERE lower(email) = lower($1);
09Keyset-пагинация и большие выборки
Пагинация через OFFSET выглядит простой, но с каждой страницей становится медленнее, потому что база должна сформировать и выбросить все пропущенные строки: страница 5,000 списка по 50 строк читает 250,000 строк. Она ещё и нестабильна: если во время листания вставляется новая строка, элементы сдвигаются и появляются дважды или не появляются вовсе. Keyset-пагинация (через курсор) запоминает последнюю строку, которую видел пользователь, и запрашивает строки после неё, прыгая туда сразу по индексу, так что каждая страница стоит одинаково. Сортируйте по уникальной комбинации — обычно timestamp плюс первичный ключ как tiebreaker — и сравнивайте значением строки (created_at, id) < (?, ?), что поддерживают и PostgreSQL, и MySQL. Компромисс в том, что нельзя перейти на произвольный номер страницы, но для лент, API и бесконечной прокрутки это обычно нормально. Для экспорта миллионов строк стримьте данные keyset-пачками, а не загружайте всё в память, и запускайте это на read-реплике, если она есть.
-- First page
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Next page: pass the last row's (created_at, id) as the cursor
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
AND (created_at, id) < ('2026-10-31 18:20:00', 98123)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Supporting index
CREATE INDEX idx_orders_cust_keyset ON orders (customer_id, created_at, id);
10Блокировки, дедлоки и изменения схемы без простоя
Каждый UPDATE берёт блокировки строк до конца транзакции. Когда каждая из двух транзакций держит блокировку, нужную другой, ни одна не может продолжить — это дедлок. Базы данных обнаруживают такую ситуацию и прерывают одну из транзакций с ошибкой (PostgreSQL проверяет после deadlock_timeout, по умолчанию одна секунда; InnoDB обнаруживает сразу), поэтому приложение должно быть готово повторить всю транзакцию. Большинство дедлоков предотвращается, если трогать строки в одном и том же порядке, например всегда блокировать счета по возрастанию id, и держать транзакции короткими. InnoDB в режиме REPEATABLE READ также берёт gap lock'и на диапазоны, которые могут неожиданно блокировать вставки. Изменения схемы — ещё один источник блокировок. В PostgreSQL CREATE INDEX CONCURRENTLY строит индекс, не блокируя запись, а добавление nullable-столбца или столбца с константным default происходит мгновенно. В MySQL многие изменения поддерживают ALGORITHM=INSTANT или INPLACE с LOCK=NONE. Всегда задавайте lock_timeout для миграций, чтобы заблокированный ALTER быстро падал, а не выстраивал за собой очередь из всех запросов.
-- PostgreSQL: safe migration settings
SET lock_timeout = '5s';
SET statement_timeout = '15min';
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- (cannot run inside a transaction block; re-run if it fails: drop the INVALID index first)
-- Add a NOT NULL constraint without a long lock
ALTER TABLE orders ADD CONSTRAINT orders_currency_nn
CHECK (currency IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;
-- MySQL 8: request an online change, fail if impossible
ALTER TABLE orders ADD COLUMN source VARCHAR(20) NULL,
ALGORITHM=INSTANT;
Реальные ситуации
Два покупателя купили последний товар на складе
Проблема
Флеш-распродажа закончилась тем, что заказов оказалось больше, чем единиц на складе. Код читал остаток, проверял, что он больше нуля, затем записывал остаток минус один, и под нагрузкой несколько запросов прочитали одно и то же значение.
Как решить
Замените «прочитать, затем записать» одним атомарным выражением с условием внутри транзакции заказа: UPDATE products SET stock = stock - 1 WHERE id = ? AND stock >= 1. Если затронуто ноль строк, товар распродан: откатите транзакцию и сообщите покупателю. Для корзин с несколькими товарами блокируйте строки товаров через SELECT ... FOR UPDATE по возрастанию id, чтобы избежать дедлоков, проверьте все количества и только потом обновляйте. Добавьте ограничение CHECK (stock >= 0), чтобы база отвергала отрицательный остаток, даже если какой-то будущий участок кода забудет про проверку. При дедлоках или ошибках сериализации повторяйте несколько раз с коротким backoff.
Запрос для отчёта ставит продакшен-базу на колени
Проблема
Ежемесячный отчёт по продажам джойнит orders, order items и products за два года данных. Когда финансовый отдел его запускает, CPU базы упирается в 100%, и checkout тормозит у всех.
Как решить
Сначала разделите нагрузки: запускайте отчёты на read-реплике, чтобы аналитика не могла задушить транзакционные запросы. Затем оптимизируйте запрос: запустите EXPLAIN ANALYZE, добавьте составной индекс под фильтр по дате и ключи джойнов и выбирайте только нужные столбцы. Для отчётов за закрытые периоды считайте заранее: ночная задача записывает дневные итоги по каждому товару в сводную таблицу, и отчёт агрегирует несколько тысяч сводных строк вместо миллионов позиций. Задайте statement_timeout для пользователя отчётов. Если потребности в отчётности продолжают расти, перенесите их в колоночное хранилище вроде ClickHouse, которое наполняется из основной базы.
Добавление индекса в таблицу на 200 миллионов строк без простоя
Проблема
Медленному запросу нужен новый индекс на очень большой и нагруженной таблице orders. Предыдущая попытка с обычным CREATE INDEX заблокировала запись на двадцать минут и привела к аварии.
Как решить
В PostgreSQL используйте CREATE INDEX CONCURRENTLY, который строит индекс, позволяя читать и писать; он работает дольше и не может выполняться внутри транзакции, поэтому запускайте его вне транзакционной обёртки фреймворка миграций. Задайте lock_timeout, чтобы короткие блокировки в начале и в конце быстро падали, а не выстраивали очередь из трафика. Если операция упадёт, останется INVALID-индекс: удалите его и повторите. В MySQL 8 используйте ALGORITHM=INPLACE, LOCK=NONE или, для очень больших таблиц, инструмент онлайн-изменения схемы вроде gh-ost. Планируйте это на время низкого трафика, следите за replication lag на репликах и после этого через EXPLAIN убедитесь, что планировщик действительно использует новый индекс.
Шпаргалка
- Реальный план с таймингами и буферами (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;- Реальный план в виде дерева (MySQL 8)
EXPLAIN ANALYZE SELECT ...; -- or EXPLAIN FORMAT=TREE- Топ запросов по общему времени (PostgreSQL)
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;- Построить индекс, не блокируя запись
CREATE INDEX CONCURRENTLY idx_name ON tbl (col);- Быстро упасть вместо ожидания в очереди за блокировками
SET lock_timeout = '5s';- Следующая страница keyset-пагинации
WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50- Атомарное уменьшение с проверкой
UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock >= 1;- Вставка или обновление одним выражением (PostgreSQL)
INSERT ... ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;- Вставка или обновление одним выражением (MySQL)
INSERT ... AS new ON DUPLICATE KEY UPDATE price = new.price;- Включить slow query log в MySQL
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5;
В моих реальных проектах
- Я работаю с MySQL и MariaDB на платформах на Laravel и с PostgreSQL (через Prisma) в мультитенантном POS SaaS, схема которого насчитывает 181 модель.
- В этом SaaS каждый запрос несёт явный id тенанта, а расширение Prisma бросает исключение, если область тенанта не указана, поэтому данные не могут утечь между компаниями.
- На платформе заказов для ресторанов я сделал около 30 контроллеров отчётов и примерно 40 выгрузок в Excel — от продаж и акций до тепловой карты времени заказов.
- Платформы, над которыми я работаю, опираются на агрегированные отчёты: ежедневно обновляемые сегменты клиентов на ресторанной платформе и отчёты по дозаказу, оценке запасов и неликвиду на платформе зоотоваров.
Вопросы и ответы
Q1Чем WHERE отличается от HAVING?
Они фильтруют на разных этапах. WHERE выполняется до группировки и фильтрует отдельные строки, поэтому не может использовать агрегатные функции. HAVING выполняется после GROUP BY и фильтрует целые группы, поэтому может использовать COUNT, SUM и подобные. Например, чтобы найти клиентов с более чем пятью оплаченными заказами, вы фильтруете status = 'paid' в WHERE, группируете по клиенту, а затем оставляете группы через HAVING COUNT(*) > 5. Условия на уровне строк в WHERE, а не в HAVING, ещё и быстрее, потому что база отбрасывает строки до дорогой группировки. Знание логического порядка запроса (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) отвечает на большинство подобных вопросов.
Q2Как индекс ускоряет запрос и чего он стоит?
Типичный индекс — это B-tree: отсортированное сбалансированное дерево значений ключа, указывающих на строки. Вместо сканирования каждой строки база спускается на несколько уровней дерева, чтобы найти значение или начало диапазона, а затем читает дальше по порядку, что помогает и ORDER BY с LIMIT. Для составного индекса действует правило левого префикса: индекс по (customer_id, created_at) помогает фильтрам по customer_id, но не по одному created_at. Цена — место на диске и более медленная запись, потому что каждый INSERT, UPDATE и DELETE должен обновить каждый индекс. Поэтому я индексирую под реальные паттерны запросов, проверенные через EXPLAIN, а не каждую колонку.
Q3Что такое проблема N+1 и как её исправить?
Она возникает, когда вы одним запросом загружаете список, а потом делаете по дополнительному запросу на каждый элемент, чтобы загрузить связанные данные: 1 запрос на 50 заказов плюс 50 запросов на их клиентов. Каждый запрос быстрый, поэтому в разработке всё выглядит нормально, но в продакшене круговые пути накапливаются, а база получает лавину запросов. ORM делают это лёгким, потому что обращение к order.customer молча запускает запрос. Решение — eager loading, например with('customer') в Laravel или include в Prisma, который загружает все связанные строки одним-двумя запросами, или явный JOIN. Я также включаю предупреждения о lazy loading или счётчик запросов в разработке, чтобы регрессии ловились рано.
Q4Объясните уровни изоляции транзакций и когда стоит менять значение по умолчанию.
Изоляция определяет, что одна транзакция видит у других, выполняющихся одновременно. READ COMMITTED, уровень по умолчанию в PostgreSQL, видит только закоммиченные данные, но повторное чтение может вернуть более новое значение. REPEATABLE READ, уровень по умолчанию в MySQL InnoDB, даёт согласованный снимок на всю транзакцию. SERIALIZABLE гарантирует, что результат будет таким же, как при последовательном выполнении транзакций, ценой прерывания некоторых из них, которые приложение должно повторить. Глобально я меняю значение по умолчанию редко. Вместо этого защищаю конкретные критические участки — балансы кошельков или остатки — атомарными условными обновлениями или SELECT ... FOR UPDATE, а SERIALIZABLE с циклом повторов использую только для сложных инвариантов.
Q5Когда использовать оконную функцию вместо GROUP BY?
Когда нужен агрегат или ранжирование, но при этом нужно сохранить каждую строку. GROUP BY сворачивает строки: вы получаете одну строку на клиента с его суммой. Оконная функция добавляет вычисляемую колонку, сохраняя детали: каждый заказ рядом с нарастающим итогом клиента, его рангом или датой предыдущего заказа. Типичные применения — «последняя запись в группе» через ROW_NUMBER, top-N по категории, нарастающие итоги, сравнение периодов через LAG и доля от общего. До оконных функций для этого нужны были self-join'ы или коррелированные подзапросы, которые хуже читались и часто были медленнее. Сейчас их поддерживают все основные базы данных.
Q6Страница тормозит, и вы подозреваете базу данных. Как будете разбираться?
Сначала измерить, а не гадать: включить лог запросов или использовать инструмент отладки, чтобы увидеть каждый запрос, который выполняет страница, с таймингами и общим количеством. Если там десятки или сотни похожих запросов — это N+1, который лечится eager loading. Если доминирует один запрос, запускаю на нём EXPLAIN ANALYZE и ищу полные сканирования больших таблиц, сортировки больших наборов и плохие оценки числа строк. Обычные решения — подходящий составной индекс, переписывание условия так, чтобы оно могло использовать индекс, выбор меньшего числа колонок или пагинация. Потом снова измеряю и держу логирование медленных запросов включённым в продакшене, чтобы поймать следующий.
Вопросы уровня senior
S1Как масштабировать реляционную базу данных, когда одного сервера уже не хватает?
Поэтапно, от самого дешёвого к самому сложному. Сначала сделайте каждый запрос эффективным: индексы, меньше round-trip'ов, никакого N+1, пул соединений через PgBouncer или ProxySQL. Затем кэшируйте горячие и редко меняющиеся чтения в Redis. Дальше добавьте read-реплики и отправляйте туда отчёты и эндпоинты с интенсивным чтением, смирившись с replication lag; чтения, которые должны видеть собственную запись пользователя, остаются на primary. Вертикальное масштабирование — более мощная машина — недооценено и часто выигрывает годы. Партиционирование больших таблиц по времени держит индексы маленькими и делает архивацию дешёвой. Только после всего этого я рассматриваю шардирование по тенанту или клиенту, потому что оно колоссально усложняет транзакции, джойны, миграции и эксплуатацию. Multi-tenant дизайн с tenant id в каждой строке оставляет эту возможность открытой, не заставляя платить за неё заранее.
S2UUID или автоинкрементные целые в качестве первичных ключей: какие компромиссы?
Автоинкрементные целые маленькие, быстро сравниваются и вставляются по порядку, что держит B-tree индексы компактными; но они раскрывают количество записей, угадываются в URL и требуют координации, когда id генерируют несколько систем. Случайные UUID (v4) можно создавать где угодно, включая офлайн-клиенты, и они не выдают объёмы бизнеса, но занимают 16 байт и вставляются в случайные позиции, вызывая расщепление страниц и плохую локальность кэша в кластерных индексах вроде InnoDB. Упорядоченный по времени UUIDv7 решает большую часть этих проблем: глобально уникален, генерируется где угодно и примерно последователен, так что вставки остаются в конце индекса. В PostgreSQL 18 есть функция uuidv7(). Мой обычный выбор — внутренний целочисленный или UUIDv7 первичный ключ плюс отдельный непрозрачный публичный id там, где URL не должны угадываться.
S3Как поддерживать согласованность данных между базой и другими системами, например очередью или платёжным провайдером?
Внешнюю систему нельзя поместить внутрь транзакции базы, поэтому вы проектируете с расчётом на частичный отказ. Для событий я использую transactional outbox: бизнес-изменение и строка outbox записываются в одной транзакции, а отдельный воркер публикует строки outbox в очередь и помечает их отправленными. Консьюмеры должны быть идемпотентными, потому что доставка at-least-once означает дубликаты. Для платежей я сначала записываю намерение с уникальным ключом идемпотентности, вызываю провайдера с этим ключом и сверяюсь через вебхуки и периодическую задачу, которая проверяет записи в ожидании, так что таймаут никогда не оставляет деньги в неизвестном состоянии. Уникальные ограничения на внешние id делают дубликаты вебхуков безвредными. Распределённые транзакции вроде двухфазного коммита существуют, но в веб-системах они редко стоят своих эксплуатационных затрат.
Глоссарий
- MVCC
- Многоверсионное управление конкурентным доступом: хранение нескольких версий строки, чтобы читатели видели согласованный снимок, не блокируя писателей.
- Dead tuple
- Старая версия строки в PostgreSQL, которая больше не нужна ни одной транзакции и ждёт, пока vacuum освободит её место.
- Покрывающий индекс
- Индекс, содержащий все столбцы, нужные запросу, так что сама таблица вообще не читается.
- Частичный индекс
- Индекс только по строкам, подходящим под условие, благодаря чему он остаётся маленьким для селективных запросов.
- Селективность
- Насколько мало строк подходит под условие; от индексов больше всего выигрывают высокоселективные условия.
- Keyset-пагинация
- Постраничный вывод, при котором запоминается последняя просмотренная строка и выбираются строки после неё, вместо OFFSET.
- Дедлок
- Две транзакции, каждая из которых ждёт блокировку, удерживаемую другой; база прерывает одну из них, чтобы разорвать цикл.
- Gap lock
- Блокировка InnoDB на диапазон между записями индекса, которая не даёт вставлять строки в этот диапазон.
- Read-реплика
- Копия базы данных, получающая изменения с primary и обслуживающая запросы только на чтение.
- Статистика планировщика
- Собранные данные о распределении значений, по которым оптимизатор оценивает число строк и выбирает план.
Что изучить дальше
Есть идея продукта?Давайте создадим его.
Расскажите о своей идее, продукте или команде. Обычно отвечаю в течение одного рабочего дня.