30 вопросов
JOIN объединяет строки из двух или более таблиц по условию. Типы:
SELECT u.name, o.amount FROM users u
LEFT JOIN orders o ON u.id = o.user_id;WHERE фильтрует строки до группировки; не может использовать агрегатные функции.
HAVING фильтрует группы после GROUP BY; может использовать агрегаты (COUNT, SUM и т.д.).
SELECT department_id, COUNT(*) FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING COUNT(*) > 5;Порядок: WHERE -> GROUP BY -> агрегаты -> HAVING -> ORDER BY.
COUNT - количество строк (COUNT(*) или COUNT(column), NULL не считаются). SUM, AVG - сумма и среднее. MIN, MAX - минимум и максимум. GROUP_CONCAT (MySQL) / STRING_AGG (PostgreSQL) - объединение строк в одну.
SELECT category_id, COUNT(*), AVG(price), SUM(amount) FROM products GROUP BY category_id;Группирует строки с одинаковыми значениями в указанных столбцах. С GROUP BY в SELECT можно включать только столбцы из GROUP BY или агрегатные функции.
SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status;В MySQL с ONLY_FULL_GROUP_BY негруппированные столбцы в SELECT запрещены; в PG то же по стандарту.
Сортирует результат по одному или нескольким столбцам. ASC (по умолчанию) - по возрастанию, DESC - по убыванию. Можно сортировать по выражению, по номеру столбца (не рекомендуется), по NULLS FIRST/LAST (PG).
SELECT * FROM users ORDER BY name ASC, created_at DESC;
SELECT * FROM items ORDER BY price NULLS LAST;Запрос внутри другого запроса. Может быть в WHERE, FROM, SELECT или с операторами IN, EXISTS, ALL, ANY.
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
SELECT * FROM (SELECT id, name FROM users WHERE active = 1) AS active_users;
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users;Коррелированный подзапрос выполняется для каждой строки внешнего запроса.
EXPLAIN (EXPLAIN ANALYZE в PG) показывает план выполнения запроса: какие индексы используются, тип доступа (index, seq scan, range), оценка строк, стоимость. Ключевые поля: type (ALL = full scan, плохо), key (используемый индекс), rows, Extra (Using temporary, Using filesort - настораживают).
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
EXPLAIN ANALYZE SELECT ... FROM orders WHERE created_at > '2024-01-01';Вычисления по строке "окна" (набору строк) без схлопывания в одну строку - в отличие от GROUP BY. Синтаксис: OVER (PARTITION BY ... ORDER BY ...).
SELECT id, name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;Примеры: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER. Доступно в PostgreSQL, MySQL 8+, SQL Server.
Именованное временное множество результатов в рамках одного запроса. Синтаксис: WITH name AS (SELECT ...) SELECT ... FROM name. Улучшает читаемость и позволяет рекурсию.
WITH recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days'
)
SELECT u.name, COUNT(r.id) FROM users u
LEFT JOIN recent_orders r ON u.id = r.user_id GROUP BY u.id, u.name;Рекурсивный CTE: WITH RECURSIVE для деревьев, последовательностей.
Убирает дубликаты строк из результата. DISTINCT применяется ко всем столбцам в SELECT. Для уникальности по части столбцов используют GROUP BY или подзапрос.
SELECT DISTINCT status FROM orders;
SELECT DISTINCT user_id, status FROM orders; -- пары (user_id, status)DISTINCT ON (expr) в PostgreSQL оставляет одну строку на группу по expr (с указанием ORDER BY для выбора какой оставить).
UNION объединяет результаты двух запросов и удаляет дубликаты (сортировка/дедупликация, дороже). UNION ALL просто склеивает результаты без проверки дубликатов - быстрее.
SELECT id, name FROM users
UNION ALL
SELECT id, name FROM archived_users;Столбцы должны быть совместимы по типу и количеству. UNION ALL предпочтителен, если дубликаты невозможны или не важны.
Вставка с обработкой конфликта по ключу: при конфликте - обновление (или игнор). В PostgreSQL: INSERT ... ON CONFLICT (column) DO UPDATE SET ... или DO NOTHING.
INSERT INTO users (id, email, name) VALUES (1, 'a@b.com', 'A')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, name = EXCLUDED.name;В MySQL аналог: INSERT ... ON DUPLICATE KEY UPDATE. Удобно для идемпотентной синхронизации.
Подготовленные запросы: SQL разбирается один раз, параметры подставляются безопасно. Защита от SQL-инъекций (параметры не интерпретируются как SQL). Плюс возможность переиспользования плана выполнения (кеш планов).
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$id]);
// или именованные: WHERE id = :id, execute(['id' => $id])Никогда не подставлять пользовательский ввод в строку запроса - только через плейсхолдеры.
Представление - именованный запрос (виртуальная таблица). Данные не хранятся отдельно, при обращении к VIEW выполняется лежащий в основе SELECT. Упрощает сложные запросы, ограничивает доступ к столбцам/строкам.
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE active = 1;Материализованное представление (Materialized View) хранит результат в таблице и обновляется по расписанию или вручную.
Представление, результат которого физически сохранен в таблице. Обновление: REFRESH MATERIALIZED VIEW (полное или CONCURRENTLY в PG). Используется для тяжелых агрегатов, денормализации для ускорения чтения. Минус - данные могут быть неактуальны до обновления.
CREATE MATERIALIZED VIEW mv_orders_summary AS
SELECT user_id, COUNT(*), SUM(total) FROM orders GROUP BY user_id;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_orders_summary;Привязанная к таблице процедура, выполняемая при INSERT/UPDATE/DELETE (BEFORE или AFTER). Используются для аудита, поддержания целостности, денормализации. Минусы: скрытая логика, сложнее отладка и тесты, производительность.
CREATE TRIGGER log_changes AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_log();Часто логику лучше выносить в код приложения (события, очереди).
Код (SQL + расширения) хранится в БД и выполняется на стороне СУБД. Вызов: CALL procedure_name(args). Плюсы: меньше сетевых round-trip, централизация логики. Минусы: привязка к конкретной СУБД, сложнее версионирование и тесты, масштабирование приложения может быть сложнее.
В современной разработке часто предпочитают держать бизнес-логику в приложении, а в БД - только данные и ограничения.
Ограничение ссылочной целостности: значение столбца (или набора) должно существовать в другой таблице (уникальный ключ). ON DELETE/ON UPDATE задают поведение при удалении/обновлении родителя: CASCADE, SET NULL, RESTRICT, NO ACTION.
ALTER TABLE orders ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;Индекс на столбце внешнего ключа часто нужен для производительности при JOIN и CASCADE.
Ограничение уникальности и NOT NULL на столбец или набор столбцов; одна запись на таблицу. Обычно один первичный ключ на таблицу; по нему создается уникальный индекс. Используется как идентификатор строки и по умолчанию как кластерный индекс (в части СУБД).
CREATE TABLE users (id BIGSERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL);Первичный или уникальный ключ из нескольких столбцов. Один столбец может повторяться, комбинация - уникальна.
PRIMARY KEY (user_id, role_id)
UNIQUE (order_id, line_number)Составной индекс полезен для запросов по префиксу (например, по user_id или по user_id + role_id). Порядок столбцов в индексе важен.
PRIMARY KEY - уникальность + NOT NULL. UNIQUE - уникальность (NULL обычно не считается дубликатом). NOT NULL. CHECK - условие на значение. FOREIGN KEY - ссылка на другую таблицу. DEFAULT - значение по умолчанию.
CHECK (price >= 0), CHECK (status IN ('draft','paid','shipped'))One-to-many: внешний ключ на стороне "многих" (orders.user_id -> users.id). One-to-one: уникальный внешний ключ или общий первичный ключ (users.id = profiles.user_id). Many-to-many: связующая таблица с двумя внешними ключами (users <- user_roles -> roles).
NULL - отсутствие значения. Сравнения с NULL дают NULL (не true/false): WHERE col = NULL не сработает, нужен IS NULL / IS NOT NULL. Агрегаты (COUNT, SUM, AVG) игнорируют NULL. COALESCE(col, default) и NULLIF(a, b) для подстановки и сравнения. В уникальных индексах в PG несколько NULL считаются разными (по стандарту), в MySQL - один NULL на уникальный ключ (зависит от версии).
VARCHAR(n) - строка переменной длины с лимитом n (байт или символов в зависимости от СУБД и кодировки). В MySQL до 65535 байт для строки; индексы можно строить по полному или префиксному ключу.
TEXT - длинный текст без лимита в определении (в MySQL TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT). В PostgreSQL TEXT и VARCHAR без ограничения почти одинаковы. Для текста без ограничения длины обычно используют TEXT; для коротких кодов/логинов - VARCHAR с разумным лимитом.
LIMIT n - вернуть не более n строк. OFFSET m - пропустить первые m строк. Порядок: ORDER BY обязателен для предсказуемого результата.
Проблема OFFSET: при больших m БД все равно "проходит" первые m строк (сканирует/сортирует), поэтому производительность падает. Для постраничной навигации лучше ключ пагинации: WHERE id > :last_seen_id ORDER BY id LIMIT 20.
Вместо OFFSET используется курсор - значение ключа последней полученной строки. Следующая страница: WHERE (created_at, id) < (:last_created, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. Стабильная производительность при большом количестве страниц, нет "прыжков" при появлении новых данных. Нельзя перейти на произвольную страницу по номеру - только вперед/назад по курсору.
Подзапрос в FROM может ссылаться на столбцы предыдущих таблиц в том же FROM - выполняется для каждой строки (как коррелированный подзапрос). В PostgreSQL: FROM t1, LATERAL (SELECT ... FROM t2 WHERE t2.a = t1.a) sub.
SELECT u.name, o.* FROM users u,
LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 5) o;Удобно для "top-N по группе" и подобных запросов без оконных функций.
На живой таблице: ADD COLUMN с DEFAULT (в PG 11+ не переписывает таблицу для volatile default). Без DEFAULT или с NOT NULL: в PG может потребоваться полный переписывание таблицы (блокировка). Подходы: добавить колонку как nullable без default; заполнить в батчах; затем добавить NOT NULL/default при необходимости. В MySQL 8.0 ALGORITHM=INSTANT для некоторых ADD COLUMN. Использовать ONLINE-операции и низкую нагрузку.
1) EXPLAIN/EXPLAIN ANALYZE - план и фактические строки. 2) Искать full scan (seq scan по большой таблице) - добавить индекс под WHERE/ORDER BY/JOIN. 3) Проверить избыточные JOIN, подзапросы в SELECT, выбор лишних столбцов. 4) Разбить сложный запрос (CTE, временные таблицы). 5) Обновить статистику (ANALYZE в PG). 6) Рассмотреть партиционирование, денормализацию, материализованные представления. 7) Настройки СУБД (work_mem, cache).
Получение списка сущностей, затем для каждой - отдельный запрос за связями (например, заказы и для каждого заказа - запрос строк). Итого 1 + N запросов. Решение: eager loading - подгружать связи одним запросом (JOIN или отдельный IN-запрос). В ORM: Eloquent with(), Doctrine join/select. Или один запрос с JOIN и ручной сборкой графа объектов.
User::with('orders')->get(); // два запроса вместо 1+N