SQL и базы данных

Часто путают три ортогональных признака: модель данных (реляционная / документная / key-value / графовая), ориентация хранения (строчная / колоночная) и назначение (OLTP оперативные транзакции / OLAP аналитика). Это независимые оси — одна БД может быть «реляционная + строчная + OLTP» (PostgreSQL), другая «реляционная + колоночная + OLAP» (ClickHouse), третья «документная + строчная + OLTP» (MongoDB).

Даже среди реляционных БД (одна и та же модель — таблицы) физическое хранение может быть разным. Это не про модель данных, а про то, как байты лежат на диске.

Реляционная модель, предложенная Эдгаром Коддом в 1970 году, — математически строгий способ описать данные через отношения (relations). Каждое отношение — это множество кортежей (tuples) одинаковой структуры (схемы). Этот фундамент определяет, почему SQL такой какой есть: декларативный язык запросов, работающий с множествами, а не с итерациями. Понимание модели позволяет читать SQL как алгебру, а не как набор инструкций.

CREATE TABLE users (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    email       VARCHAR(255) NOT NULL UNIQUE,
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id          BIGINT      PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT      NOT NULL,
    status      VARCHAR(32)  NOT NULL,
    total_minor INT          NOT NULL CHECK (total_minor >= 0),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

Здесь видны все три типа целостности: NOT NULL и UNIQUE для entity, CHECK для domain, FOREIGN KEY для referential.

Выбор типа данных — первое архитектурное решение в схеме. Тип определяет диапазон значений, размер на диске, скорость операций, поведение в индексах и совместимость между СУБД. Неверный тип приводит к двум классам проблем: тихая потеря данных (округление FLOAT для денег, переполнение TINYINT) и деградация производительности (CHAR(255) вместо VARCHAR, BLOB в часто читаемых строках).

Эмпирика: id BIGINT UNSIGNED для PK всегда; price_minor INT UNSIGNED для денег в копейках (хватит до ~$42M); quantity SMALLINT UNSIGNED для штук товара. Не используйте больше, чем нужно — на миллионе строк лишний байт = МБ. PostgreSQL: SMALLINT (2b), INTEGER (4b), BIGINT (8b), SERIAL/BIGSERIAL — auto-increment.

-- Никогда. FLOAT/DOUBLE — приблизительные.
CREATE TABLE orders (total FLOAT);
-- SELECT 0.1 + 0.2; → 0.30000000000000004

-- INT в минорных единицах (центы/копейки)
CREATE TABLE orders (total_minor INT UNSIGNED);

-- DECIMAL(precision, scale) — точное хранение
CREATE TABLE products (price DECIMAL(10, 2)); -- до 99 999 999.99

Если обязаны сравнить FLOAT'ы — через эпсилон: ABS(a - b) < 0.0001, никогда a = b. Для финансовых сумм правило строгое — только DECIMAL или INT в минорных единицах.

Проблема 2038: 32-битный TIMESTAMP переполнится в январе 2038. Для долговременных дат (контракты, договоры) — DATETIME. PostgreSQL: TIMESTAMP WITH TIME ZONE (timestamptz) — хранит UTC и конвертирует в TZ клиента; TIMESTAMP WITHOUT TIME ZONE — «голое» значение.

TEXT-семейство хранится off-page (отдельно от строки) — полный JOIN/SELECT * становится медленнее. PostgreSQL: TEXT без ограничения длины как универсальный тип.

Все типы делятся на две группы, и это деление важнее запоминания конкретных цифр: одни занимают место по факту данных, другие — всегда одинаково, независимо от содержимого.

DDL (Data Definition Language) — команды управления схемой: создание, изменение, удаление таблиц и ограничений. На проде это самая опасная категория операций — ALTER на гигабайтной таблице может блокировать чтение на минуты, DROP TABLE — необратим. Понимание тонкостей DDL и стратегий безопасного применения отделяет производственный опыт от учебного.

CREATE TABLE IF NOT EXISTS users (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    email       VARCHAR(255)    NOT NULL UNIQUE,
    age         TINYINT UNSIGNED CHECK (age BETWEEN 0 AND 150),
    status      VARCHAR(32)     NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_status CHECK (status IN ('pending', 'active', 'banned'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CHECK в MySQL до 8.0.16 парсился, но не применялся — ограничения молча игнорировались. С 8.0.16+ — работают. PostgreSQL — работают всегда. Именованные constraints (CONSTRAINT chk_status) удобнее для последующего DROP/изменения.

-- Полная копия с данными
CREATE TABLE users_backup_20260523 AS SELECT * FROM users;

-- Только структура без данных
CREATE TABLE users_empty AS SELECT * FROM users WHERE 1=0;

-- Подмножество по условию (миграция данных)
CREATE TABLE archived_orders AS
  SELECT * FROM orders WHERE created_at < '2020-01-01';

Важно: CREATE TABLE AS SELECT копирует данные, но не копирует PK, индексы, FK, AUTO_INCREMENT. После создания нужно добавить вручную. PostgreSQL: CREATE TABLE ... (LIKE source INCLUDING ALL) — копирует и ограничения.

-- Добавить колонку
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;

-- Изменить тип колонки (потенциально долго)
ALTER TABLE orders MODIFY COLUMN total_minor BIGINT UNSIGNED NOT NULL;

-- Переименовать колонку (MySQL 8+)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Добавить индекс
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- Добавить FK с именем
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT;

Цена операций: до MySQL 8.0 большинство ALTER требовало полной перезаписи таблицы (создание копии, копирование строк, переименование). С 8.0+ многие операции instant: добавление колонки в конец, переименование колонки, добавление виртуальной колонки. Для DROP COLUMN, изменения типа — всё равно перезапись.

-- MySQL
DESC users;                       -- структура таблицы
SHOW CREATE TABLE users;          -- полный CREATE
SHOW INDEX FROM users;            -- список индексов
SHOW TABLE STATUS LIKE 'users';     -- размер, кол-во строк, engine

-- PostgreSQL (psql)
\d users            -- структура + индексы + FK
\d+ users           -- расширенная
\di              -- все индексы

Нормализация — процесс декомпозиции таблиц для устранения аномалий обновления, вставки и удаления. Формы нормализации (1НФ, 2НФ, 3НФ, БКНФ) — последовательно более строгие критерии. Знание форм нужно не для механического применения, а для понимания, какую аномалию устраняет каждый уровень и в каких случаях осознанная денормализация оправдана.

JOIN — операция комбинирования строк двух (и более) отношений на основе условия. Несмотря на единый синтаксис, разные виды JOIN-ов имеют принципиально разную семантику: один из них (INNER) фильтрует, другой (LEFT) дополняет, третий (CROSS) умножает. Неправильный JOIN — одна из самых частых причин ложных данных в отчётах.

Условие в ON применяется до JOIN-а — контролирует, какие строки соединяются. Условие в WHERE применяется после JOIN-а — фильтрует результат. Для INNER JOIN разницы нет; для OUTER — разница принципиальная.

-- WHERE на правую таблицу превращает LEFT JOIN в INNER
SELECT u.id, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';
-- Пользователи без заказов выпадут: их status = NULL, NULL = 'paid' → false

-- Условие в ON: LEFT JOIN сохранён
SELECT u.id, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
-- Все пользователи в результате; у тех, без оплаченных заказов, order_id = NULL

Правило: условия на «правую» сторону OUTER JOIN всегда в ON, не в WHERE. Если условие надо в WHERE — вы фактически хотите INNER JOIN; запишите его явно.

-- Книги с рейтингом >= 4, доставленные в срок
SELECT b.title, o.quantity, d.delivery_status
FROM books b
JOIN orders o
  ON o.book_id = b.id
  AND o.quantity >= 2
JOIN deliveries d
  ON d.order_id = o.id
  AND d.delivery_status IN ('delivered', 'in-transit')
WHERE b.rating >= 4;

Несколько условий в ON через AND/OR/IN — нормальная практика. Это читается лучше, чем громоздкий WHERE на «склейку».

При OUTER JOIN правая сторона может быть NULL. COALESCE возвращает первое не-NULL значение — стандартный способ подставить дефолт.

SELECT u.id, u.name,
       COALESCE(SUM(o.total_minor), 0) AS total_spent,
       COALESCE(p.phone, 'не указан')  AS phone
FROM users u
LEFT JOIN orders   o ON o.user_id = u.id
LEFT JOIN profiles p ON p.user_id = u.id
GROUP BY u.id, u.name, p.phone;

-- NULLIF — наоборот: если первое = второму, вернуть NULL
-- Защита от деления на 0:
SELECT amount / NULLIF(quantity, 0) AS unit_price FROM items;

-- Двойной JOIN ломает SUM: каждый платёж умножается на количество заказов
SELECT u.id, COUNT(o.id) AS orders_count, SUM(p.amount) AS total_paid
FROM      users    u
LEFT JOIN orders   o ON o.user_id = u.id
LEFT JOIN payments p ON p.user_id = u.id
GROUP BY u.id;

-- Агрегировать каждую сторону отдельно через подзапросы
SELECT u.id,
       COALESCE(o.orders_count, 0) AS orders_count,
       COALESCE(p.total_paid,  0) AS total_paid
FROM users u
LEFT JOIN (SELECT user_id, COUNT(*) AS orders_count FROM orders   GROUP BY user_id) o ON o.user_id = u.id
LEFT JOIN (SELECT user_id, SUM(amount) AS total_paid   FROM payments GROUP BY user_id) p ON p.user_id = u.id;

В первом запросе пользователь с 3 заказами и 5 платежами породит 15 строк, и каждый платёж попадёт в SUM трижды. Это математика декартова произведения, в которое мы сами загнали запрос.

Индекс — вспомогательная структура данных, позволяющая СУБД находить строки по значениям атрибутов без полного сканирования таблицы. Без индексов запрос WHERE email = ? на таблице в миллион строк требует прочитать миллион строк. С индексом — ~20 операций обращения к B-Tree. Понимание индексов отличает middle от junior и определяет, почему один запрос выполняется за 20 мс, а похожий — за 4 секунды.

Композитный индекс INDEX (a, b, c) используется при запросах, фильтрующих по a, (a, b), (a, b, c). Не работает для (b), (c), (b, c). Из этого следует основа дизайна композитных индексов: первой ставится колонка, по которой чаще всего идёт точная фильтрация.

CREATE INDEX idx ON orders (user_id, status, created_at);

-- Используется полностью
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at;
-- Используется по первой колонке
SELECT * FROM orders WHERE user_id = 42;
-- НЕ используется: нет фильтра по user_id
SELECT * FROM orders WHERE status = 'paid';

Eloquent-миграции абстрагируют создание индексов под все три основных СУБД одним DSL. На практике почти все индексы в Laravel-проектах объявляются именно через Schema Builder. Параллельно нужно уметь читать индексы со стороны БД и находить неиспользуемые/дубликаты — чистка таких индексов даёт ускорение записи без потерь для чтения.

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->string('status', 32);
    $table->unsignedInteger('total_minor');
    $table->timestamps();

    // Одинокий индекс
    $table->index('status');

    // Композитный индекс
    $table->index(['user_id', 'created_at'], 'idx_orders_user_created');

    // Уникальный (одна колонка или композит)
    $table->unique(['user_id', 'external_ref']);

    // Full-text (MySQL)
    $table->fullText(['title', 'description']);
});

// Добавить индекс в существующую таблицу
Schema::table('orders', fn (Blueprint $t) =>
    $t->index('paid_at'));

// Удалить — по имени (всегда работает) или по колонкам
Schema::table('orders', function (Blueprint $t) {
    $t->dropIndex('idx_orders_user_created');   // по имени
    $t->dropIndex(['user_id', 'created_at']); // Laravel сгенерирует имя
    $t->dropUnique('orders_user_id_external_ref_unique');
    $t->dropForeign(['user_id']);
});

MySQL:

-- Все индексы таблицы
SHOW INDEX FROM orders;

-- Неиспользуемые индексы (требуется sys schema)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'shop';

-- Дубликаты/избыточные
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'shop';

-- Размеры индексов
SELECT database_name, table_name, index_name,
       SUM(stat_value) * 16 AS size_kb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
GROUP BY database_name, table_name, index_name;

PostgreSQL:

-- Все индексы таблицы
\d orders

-- Статистика использования
SELECT schemaname, tablename, indexname,
       idx_scan,                  -- сколько раз использовался
       idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC; -- наверху — самые неиспользуемые

-- Размер индексов
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

// Включить лог всех запросов
DB::enableQueryLog();

User::where('email', 'a@example.com')->first();

dd(DB::getQueryLog());
// [['query' => 'select * from users where email = ?', 'bindings' => ['a@...'], 'time' => 0.3]]

// EXPLAIN для конкретного запроса
$plan = DB::select('EXPLAIN SELECT * FROM users WHERE email = ?', ['a@example.com']);
dump($plan);

// Получить SQL из Eloquent без выполнения
$sql = User::where('status', 'active')->toSql();
// "select * from `users` where `status` = ?"

Эмпирика: добавление каждого индекса замедляет INSERT/UPDATE/DELETE на 5-15%. На таблице с 10 индексами запись в 2-3 раза медленнее, чем без индексов. Перед добавлением индекса задайте вопрос: «какой конкретный запрос он ускоряет?» Если ответа нет — индекс не нужен.

EXPLAIN показывает план выполнения запроса. EXPLAIN ANALYZE дополнительно реально исполняет запрос и показывает фактическое время и количество строк. Расхождение ожидаемого и фактического указывает на устаревшую статистику или неверные оценки.

Правило: если в плане type=ALL или type=index на большой таблице — вы ещё не закончили оптимизацию. Стремитесь к ref или лучше для всех «горячих» запросов.

-- Старый стиль (SQL-89): запятая в FROM, условия в WHERE
SELECT * FROM users, orders
WHERE orders.user_id = users.id;

-- Современный (ANSI): явный JOIN
SELECT * FROM users
JOIN orders ON orders.user_id = users.id;

Опасность старого синтаксиса: забыл условие в WHERE → CROSS JOIN. Запрос FROM users, orders без WHERE на миллион пользователей и миллион заказов вернёт триллион строк. ANSI JOIN требует явный ON — забыть невозможно.

EXPLAIN ANALYZE
SELECT u.id, SUM(o.total_minor) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-04-01'
GROUP BY u.id
ORDER BY total DESC
LIMIT 10;

-- Postgres plan (упрощённо):
Limit  (actual time=120.4..120.5)
  ->  Sort
        ->  HashAggregate
              ->  Hash Join
                    Hash Cond: (o.user_id = u.id)
                    ->  Index Scan using idx_orders_created_at on orders o
                    ->  Seq Scan on users u  ← полное сканирование users

План говорит: индекс по orders.created_at подобран, но затем для JOIN сделан Seq Scan по 100к users. Решение — убедиться, что users.id — PK (всегда индексирован), либо подсказать оптимизатору через SET enable_seqscan = off для сравнения.

Подзапрос — SELECT внутри SELECT. На вид одно и то же, но за этим скрыты два принципиально разных механизма: uncorrelated (независимый, выполняется один раз) и correlated (зависит от строки внешнего запроса, выполняется N раз). Непонимание разницы превращает безобидный запрос в N+1 на уровне SQL.

1. JOIN + GROUP BY — одна сортировка вместо N запросов:

SELECT u.id, u.email, MAX(o.created_at) AS last_order
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

2. CTE с предварительной агрегацией — читается лучше:

WITH last_orders AS (
    SELECT user_id, MAX(created_at) AS ts
    FROM orders GROUP BY user_id
)
SELECT u.id, u.email, lo.ts AS last_order
FROM users u LEFT JOIN last_orders lo ON lo.user_id = u.id;

3. Window function — если нужна не только агрегация, а вся строка последнего заказа:

SELECT u.id, u.email, o.id AS last_order_id, o.total_minor
FROM users u
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) o ON o.user_id = u.id AND o.rn = 1;

Оба проверяют «есть ли совпадение», но реализованы по-разному. IN вычисляет полный список и сравнивает; EXISTS останавливается на первом совпадении.

-- IN: подзапрос материализует все user_id, потом проверяет членство
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- EXISTS: для каждой строки внешнего ищет хотя бы одно совпадение, выходит на первом
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);

Современные оптимизаторы часто приводят эти запросы к одному плану, но есть два кейса, где разница остаётся:

• NOT IN с NULL — ломается (см. JOIN-pitfalls); NOT EXISTS корректно;
• Большой подзапрос — EXISTS часто эффективнее, поскольку не материализует весь список.

CTE можно последовательно цепочкой — каждый шаг видит результат предыдущего. Это лучший способ записать многошаговую логику читаемо.

WITH
recent_orders AS (
    SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
),
user_totals AS (
    SELECT user_id, SUM(total_minor) AS total, COUNT(*) AS cnt
    FROM recent_orders GROUP BY user_id
),
ranked AS (
    SELECT *, RANK() OVER (ORDER BY total DESC) AS rk
    FROM user_totals
)
SELECT u.email, r.total, r.cnt, r.rk
FROM ranked r JOIN users u ON u.id = r.user_id
WHERE r.rk <= 10;

До Postgres 12 каждый CTE был «оптимизационным барьером» — план не оптимизировался сквозь него. С 12+ — inline по умолчанию; явная материализация через WITH foo AS MATERIALIZED (...).

N+1 — антипаттерн, когда вместо одного запроса с JOIN-ом приложение делает 1 запрос на список родителей и N запросов на связанные данные. Типичный сценарий в Laravel: выборка 50 заказов и цикл с обращением к $order->user — 1 + 50 запросов. Каждый запрос — round-trip к БД, суммарно тысячи мс там, где могло быть 20.

// Классический N+1: 51 запрос
$orders = Order::where('status', 'paid')->limit(50)->get();
foreach ($orders as $order) {
    echo $order->user->name; // каждое обращение — SQL-запрос
}

// Eager loading: 2 запроса
$orders = Order::with('user')->where('status', 'paid')->limit(50)->get();

// Через JOIN (query builder): 1 запрос
$orders = DB::table('orders')
    ->join('users', 'users.id', 'orders.user_id')
    ->select('orders.*', 'users.name as user_name')
    ->get();

ACID — набор гарантий, которые транзакционная СУБД даёт приложению: Atomicity (всё или ничего), Consistency (БД переходит между корректными состояниями), Isolation (параллельные транзакции не «видят» промежуточных результатов друг друга), Durability (commit — навсегда). Самая нетривиальная буква — Isolation: за неё отвечают уровни изоляции.

-- T1                              T2
BEGIN;                          BEGIN;
SELECT balance...               SELECT balance...
-- balance = 1000             -- balance = 1000
UPDATE SET balance=800;       UPDATE SET balance=900;
COMMIT;                          COMMIT;
-- T2 затёр T1: balance = 900 вместо 700

Решения:

• Pessimistic locking: SELECT ... FOR UPDATE — блокировка строки до COMMIT.
• Atomic UPDATE: UPDATE SET balance = balance - 200 WHERE balance >= 200.
• Optimistic locking: колонка version, WHERE version = ?, retry при конфликте.

MySQL уникальна тем, что позволяет хранить разные таблицы в одной БД через разные «движки» (storage engines). У каждого — свои свойства: транзакции или нет, row-level или table-level locks, indexing, durability. Для современного приложения дефолт — InnoDB; понимание других движков нужно для редких специальных задач и для кругозора.

-- Текущий дефолтный движок
SHOW VARIABLES LIKE 'default_storage_engine';

-- Движок конкретной таблицы
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'shop';

-- Размер buffer pool и hit rate
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads%';
-- hit rate = 1 - (reads / read_requests); чем ближе к 1, тем лучше

-- Конвертация MyISAM → InnoDB
ALTER TABLE old_table ENGINE=InnoDB;

Блокировки — механизм управления конкурентным доступом. MVCC (Multi-Version Concurrency Control) — альтернативный подход с версионированием. Современные СУБД используют гибрид: MVCC для чтений, row-level locks для записей. Понимание необходимо для диагностики deadlock'ов и проектирования схем без лишней конкуренции.

Две транзакции взаимно ждут блокировки друг друга. СУБД детектирует и отменяет одну с ошибкой (MySQL: 40001, Postgres: 40P01).

-- T1                          -- T2
UPDATE id=1;                    UPDATE id=2;
UPDATE id=2;                    UPDATE id=1;
-- T1 ждёт T2, T2 ждёт T1 → DEADLOCK

Решение — всегда брать блокировки в одном порядке (например, по возрастанию id).

Оконные функции и CTE — декларативные конструкции, заменяющие самописные циклы и временные таблицы. Многие задачи «возьмём данные и пройдём в коде» решаются одним запросом — быстрее, меньше round-trip'ов. Поддержка: MySQL 8+, Postgres — полный включая рекурсивные CTE.

SELECT user_id, id, created_at, total_minor
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) latest
WHERE rn = 1;

-- Рекурсивный CTE: иерархия категорий
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.depth + 1
    FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Три доминирующих СУБД в enterprise: MySQL, PostgreSQL, Microsoft SQL Server. На базовом SQL похожи; на уровне типов, продвинутых индексов, репликации и оптимизатора — разные. Понимание различий критично для интеграционных задач, миграций между СУБД, переносимых ORM-запросов и для senior-уровня.

Eloquent и Query Builder генерируют разный SQL под разные драйверы. DB::table('users')->limit(10) на MySQL/PG превратится в LIMIT 10, на SQL Server — в SELECT TOP 10 или OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY. upsert() — соответствующий синтаксис каждой СУБД. Quoting идентификаторов автоматически: backtick/двойные/квадратные.

// Один код — три разных SQL под капотом
User::upsert(
    [['email' => 'a@example.com', 'name' => 'Anna']],
    ['email'],   // уникальный ключ
    ['name']     // что обновить при конфликте
);
// MySQL: INSERT INTO users (...) VALUES (...) ON DUPLICATE KEY UPDATE name = VALUES(name)
// PG:    INSERT INTO users (...) VALUES (...) ON CONFLICT (email) DO UPDATE SET name = excluded.name
// MSSQL: MERGE INTO users ...

RETURNING — расширение SQL, которое позволяет INSERT/UPDATE/DELETE вернуть данные изменённых строк в том же ответе, как если бы вы сделали SELECT сразу. Поддерживается в PostgreSQL нативно, MySQL добавил похожее только в 8.0.21+ и только для DELETE (для UPDATE/INSERT — нет).

Обычный ответ UPDATE в MySQL/стандартном SQL — только количество затронутых строк (affected rows). Чтобы получить сами данные, нужно делать второй запрос — SELECT. RETURNING убирает эту двухшаговость.

Задача: в таблице stock_pool лежат ключи активации (Steam-keys, лицензионные коды). При заказе надо взять один свободный и намертво привязать к order_id. В условиях 1000 rps любой race condition = «два клиента получили один и тот же ключ».

-- PostgreSQL: один атомарный оператор
UPDATE stock_pool
   SET order_id = 'ord_1',
       issued_at = now()
 WHERE id = (
       SELECT id FROM stock_pool
        WHERE sku = 'KEY-GTA5' AND order_id IS NULL
        ORDER BY id
        LIMIT 1
        FOR UPDATE SKIP LOCKED
 )
RETURNING code;

Что этот запрос делает в один оператор:

• Находит первый свободный ключ (order_id IS NULL) с блокировкой строки.
• FOR UPDATE SKIP LOCKED — если 10 параллельных воркеров уже держат первые 10 ключей, перепрыгнет их и возьмёт 11-й.
• Привязывает его к заказу (UPDATE ... SET).
• Возвращает сам код (RETURNING code) — приложение получает готовое значение в ответе.

• Получить сгенерированный ID при INSERT. INSERT INTO orders (...) VALUES (...) RETURNING id; — не нужно потом делать SELECT lastval() или currval().
• Аудит удалений. DELETE FROM sessions WHERE expires_at < now() RETURNING id, user_id; — сразу знаешь, кого выкинул.
• UPSERT с результатом. INSERT ... ON CONFLICT DO UPDATE ... RETURNING id, xmax = 0 AS inserted; — сразу видно, вставил ты новую строку или обновил старую.
• Массовые операции с обратной связью. UPDATE ... WHERE ... RETURNING id; — вернёт ID всех затронутых строк, не нужен второй запрос.

Одно слово «partial index» — две совсем разные фичи в MySQL и PostgreSQL. Классический источник недопонимания и в переводимых статьях.

PostgreSQL позволяет индексировать не всю таблицу, а только строки, удовлетворяющие условию:

-- Индексируем только 'pending' задачи (обычно 0.1% от всех записей)
CREATE INDEX ON jobs (run_at) WHERE status = 'pending';

-- Индекс на активные заказы
CREATE INDEX ON orders (created_at) WHERE status IN ('paid', 'delivering');

-- Уникальность только для не-отменённых доставок
CREATE UNIQUE INDEX ON deliveries (order_id) WHERE status <> 'cancelled';

Что даёт:

• Индекс физически меньше — только N строк из миллиона. Меньше RAM, дольше держится в кеше.
• INSERT/UPDATE быстрее — индекс обновляется только для строк, попадающих под условие.
• Запросы к «горячему подмножеству» — молниеносные (индекс маленький, всё в памяти).
• Уникальность «с условием» — обычный UNIQUE не может выразить «уникально только для активных».

В MySQL термин «partial index» — это префикс колонки, а не подмножество строк. Индексируются все записи, но не целиком — только первые N символов длинного VARCHAR/TEXT:

-- Индексируем только первые 10 символов колонки 'name'
CREATE INDEX idx_name ON users (name(10));

-- Экономит место, если N символов уже дают достаточную селективность
CREATE INDEX idx_email ON users (email(20));

• Generated columns. Создаёшь виртуальную колонку, равную полю только при условии, иначе NULL; индексируешь её. NULL-значения в B-tree занимают минимум места.

  ALTER TABLE jobs
    ADD COLUMN run_at_pending TIMESTAMP
      GENERATED ALWAYS AS (IF(status = 'pending', run_at, NULL)) VIRTUAL,
    ADD INDEX (run_at_pending);

• Секционирование (partitioning). Таблицу делят на разделы по значению колонки (например, по статусу или дате), индекс есть только на нужном разделе.
• Отдельная таблица для «горячих» строк. jobs + jobs_pending, куда триггер переносит записи при смене статуса. Радикально, но иногда лечит production.

Если новый Laravel-проект стартует с чистого листа и есть свобода выбора СУБД — PostgreSQL чаще всего выигрышный вариант. Не потому что «модно», а потому что для типичных бэкенд-задач (очереди, транзакции с побочками, атомарная выдача ресурсов, консистентность денежных операций) PG даёт из коробки несколько вещей, которых в MySQL просто нет.

PostgreSQL по умолчанию — READ COMMITTED, поведение точно такое, как в учебнике: транзакция видит все закоммиченные изменения на момент каждого SELECT.

MySQL/InnoDB по умолчанию — REPEATABLE READ с нестандартной семантикой: обычный SELECT читает снапшот на момент начала транзакции, а SELECT ... FOR UPDATE — последнюю закоммиченную версию. Две строки подряд в одной транзакции видят разные данные:

-- MySQL, REPEATABLE READ. Между двумя SELECT кто-то закоммитил UPDATE.
BEGIN;
SELECT balance FROM accounts WHERE id = 1;             -- 100 (снапшот)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 150 (последняя)
-- Классическая мина: логика решает по первому SELECT, обновление ушло по второму.

Для 90% CRUD это неважно. Для «ровно один раз под 50 параллельными запросами» — это источник багов, которые невозможно объяснить, глядя в свой код.

InnoDB при SELECT ... FOR UPDATE блокирует не только строки, но и промежутки между ними (next-key locking) — чтобы защититься от «фантомов». Побочка: дедлоки там, где их логически быть не должно, особенно при выборке из очереди по условию (WHERE status = 'pending' LIMIT 10 FOR UPDATE).

В PostgreSQL — MVCC без gap-локов: блокируются только реально найденные строки. Меньше сюрпризов, проще отлаживать конкуренцию.

Классический случай: двойная запись в бухгалтерии. Каждая проводка = минимум две строки, и сумма проводки обязана быть нулём. Но во время вставки первой строки сумма ещё не ноль — ограничение упало бы, если проверяется построчно.

PostgreSQL умеет проверять ограничения в момент COMMIT, а не после каждой строки:

ALTER TABLE ledger_entries
  ADD CONSTRAINT balanced
  CHECK (...)
  DEFERRABLE INITIALLY DEFERRED;
-- Проверка сработает только на COMMIT

В MySQL такого механизма нет — пришлось бы городить триггеры или проверять в коде приложения.

Классический кейс: «вебхук от платёжки пришёл раньше, чем создался заказ» — надо сериализовать обработчиков, но строки в БД ещё нет. PostgreSQL умеет транзакционные advisory locks — «именной» лок, который сам отпустится при COMMIT/ROLLBACK:

SELECT pg_advisory_xact_lock(hashtext('ord_1'));
-- Второй воркер с тем же 'ord_1' будет ждать первого
-- При commit/rollback лок освободится автоматически

В MySQL есть GET_LOCK('name'), но он привязан к сессии, а не к транзакции — при откате не освобождается, надо явно RELEASE_LOCK. Утечки локов после исключений — типичная беда.

INSERT IGNORE в MySQL глушит все ошибки, а не только нарушение уникальности: обрезанные строки, неверные типы, нарушение внешних ключей. Вставил мусор — молча получил 0 rows, а потом полдня ищешь, где данные пропали.

PostgreSQL ON CONFLICT (event_id) DO NOTHING реагирует только на конкретный конфликт по конкретному индексу. Всё остальное честно падает исключением. Для дедупликации платёжных событий, идемпотентности вебхуков — разница принципиальная.

SQL, который ты знаешь по MySQL, переносится почти весь. Отличия в диалекте — минут на 30 чтения:

# docker-compose.yml
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: gamer_market
      POSTGRES_PASSWORD: secret
    ports: ["5432:5432"]

Одна команда docker compose up -d — БД готова. Проверяющий делает то же самое одной командой.

В Laravel — драйвер pgsql вместо mysql, в .env: DB_CONNECTION=pgsql, DB_PORT=5432. PDO-код (prepare/execute/fetch) — не меняется. Eloquent абстрагирует диалект автоматически.

Эмпирика: до 100к документов — нативный FULLTEXT/tsvector. От 100к до 10М — Meilisearch/Typesense. Свыше — Elasticsearch с отдельной командой эксплуатации.

Большинство оптимизаций решаются индексами и переписыванием запроса. Когда этого не хватает — в арсенале остаются продвинутые механизмы: подсказки оптимизатору, физическое партиционирование, материализованные представления, хранимые процедуры. Каждый имеет цену — либо в сложности кода, либо в стоимости поддержки. Использовать их нужно только когда обычные средства исчерпаны.

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

-- MySQL: принудительный индекс
SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 42;

-- MySQL: подсказка JOIN-стратегии
SELECT /*+ JOIN_ORDER(u, o) */ *
FROM users u JOIN orders o ON ...

-- SQL Server: тип JOIN, локи
SELECT * FROM orders WITH (NOLOCK)
OPTION (LOOP JOIN, MAXDOP 4);

-- PostgreSQL: hints — через расширение pg_hint_plan, не нативно
/*+ IndexScan(orders idx_user_id) */
SELECT * FROM orders WHERE user_id = 42;

Оптимизатор принимает решения на основании статистики: сколько строк в таблице, как распределены значения колонок, селективность индексов. Если статистика устарела (после массового импорта/удаления) — план может быть в разы хуже оптимального.

-- MySQL: обновить статистику таблицы
ANALYZE TABLE orders;

-- PostgreSQL: обновить статистику
ANALYZE orders;
-- VACUUM ANALYZE — очистка bloat + статистика
VACUUM ANALYZE orders;

-- SQL Server
UPDATE STATISTICS orders;
-- С полной выборкой:
UPDATE STATISTICS orders WITH FULLSCAN;

PostgreSQL запускает autovacuum автоматически — обычно настройка по умолчанию подходит. На write-heavy таблицах может отставать, нужно тюнить autovacuum_vacuum_scale_factor. MySQL обновляет статистику InnoDB лениво при изменении 10% таблицы.

View — виртуальная таблица, запрос которой выполняется при каждом обращении. Materialized View — физически сохранённый результат запроса, обновляемый по расписанию. Идеален для тяжёлых аналитических агрегатов, читаемых часто, обновляемых редко.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_sales AS
SELECT DATE(created_at) AS day, SUM(total_minor) AS total
FROM orders WHERE status = 'paid' GROUP BY DATE(created_at);

-- Создать индекс на view (как на обычной таблице)
CREATE UNIQUE INDEX idx_daily_sales_day ON daily_sales (day);

-- Обновить — по расписанию (cron / Laravel Scheduler)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

-- SQL Server (Indexed View)
CREATE VIEW daily_sales WITH SCHEMABINDING AS ...;
CREATE UNIQUE CLUSTERED INDEX idx ON daily_sales(day);

-- MySQL не имеет нативно — workaround через summary tables + триггеры/events

Хранимая процедура — код на процедурном расширении SQL (PL/pgSQL, T-SQL), живущий внутри БД. Преимущества: меньше round-trips, доступ к данным без передачи в приложение, можно вызывать из триггеров. Недостатки: язык БД (а не язык приложения), сложнее версионировать, тестировать, отлаживать.

-- PostgreSQL: процедура с транзакцией
CREATE OR REPLACE PROCEDURE transfer_funds(
    from_id BIGINT, to_id BIGINT, amount INT
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END;
$$;

-- Вызов
CALL transfer_funds(1, 2, 500);

Современная практика: избегать stored procedures в новых проектах. Логика принадлежит приложению, БД должна оставаться «хранилищем». Исключения: критичные операции с миллионами row-операций (где избегание round-trip даёт x10 выигрыш), legacy-проекты с уже сложившейся практикой.

UNION убирает дубликаты (требует сортировки/хеширования всего результата). UNION ALL — просто склеивает строки. На больших объёмах разница огромная: UNION ALL в 5-10 раз быстрее.

-- UNION без необходимости — лишний overhead
SELECT id FROM active_users
UNION
SELECT id FROM archived_users;
-- СУБД проверяет на дубли даже если они невозможны

-- UNION ALL — если дубли невозможны/неважны
SELECT id FROM active_users
UNION ALL
SELECT id FROM archived_users;

PDO — унифицированный интерфейс работы с реляционными БД, прозрачный между MySQL/PostgreSQL/SQLite/MSSQL. Laravel оборачивает PDO в Eloquent/Query Builder; знание PDO нужно для понимания, что происходит под капотом, безопасной обработки пограничных случаев и грамотных формулировок.

use PDO;

$pdo = new PDO('mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE             => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE  => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES    => false, // настоящие prepared, не эмулированные
]);

$stmt = $pdo->prepare('SELECT id, email FROM users WHERE status = :status LIMIT :limit');
$stmt->bindValue(':status', 'active', PDO::PARAM_STR);
$stmt->bindValue(':limit',  10,       PDO::PARAM_INT);
$stmt->execute();

foreach ($stmt as $row) {
    echo $row['email'];
}

Аналитический запрос на дашборде «топ-10 пользователей по сумме платежей за последний месяц с количеством заказов» работает 4.2 секунды на проде. Таблицы: users (1.2 млн), orders (8 млн), payments (15 млн). Задача — довести время до < 50 мс без архитектурных изменений.

SELECT u.id, u.email,
       COUNT(DISTINCT o.id)  AS orders_count,
       SUM(p.amount_minor)   AS total_paid
FROM      users    u
LEFT JOIN orders   o ON o.user_id = u.id
LEFT JOIN payments p ON p.user_id = u.id
WHERE p.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY total_paid DESC
LIMIT 10;

Проблемы: (1) WHERE по p.created_at с LEFT JOIN превращает его в INNER; (2) двойной JOIN дублирует строки, COUNT(DISTINCT) и SUM работают на дублированном множестве; (3) полное сканирование 8M orders + 15M payments + 1.2M users.

SELECT u.id, u.email,
       COALESCE(o.orders_count, 0) AS orders_count, p.total_paid
FROM users u
JOIN (SELECT user_id, SUM(amount_minor) AS total_paid
       FROM payments
       WHERE created_at >= NOW() - INTERVAL '30 days'
       GROUP BY user_id) p ON p.user_id = u.id
LEFT JOIN (SELECT user_id, COUNT(*) AS orders_count FROM orders GROUP BY user_id) o ON o.user_id = u.id
ORDER BY p.total_paid DESC
LIMIT 10;

CREATE INDEX idx_payments_created_user_amount
  ON payments (created_at, user_id) INCLUDE (amount_minor);
CREATE INDEX idx_orders_user_id ON orders (user_id);

WITH top_payers AS (
    SELECT user_id, SUM(amount_minor) AS total_paid
    FROM payments
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
    ORDER BY total_paid DESC
    LIMIT 10
)
SELECT u.id, u.email, tp.total_paid,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS orders_count
FROM top_payers tp JOIN users u ON u.id = tp.user_id
ORDER BY tp.total_paid DESC;

CTE сужает множество до 10 user_id за один Index Scan по payments. Дальше — точечные lookup'ы по индексам. Итог: 4200 мс → 40 мс. Архитектура не менялась, кэш не добавлен.