TeoreticQuiz

MySQL, PostgreSQL

Материал для подготовки к собеседованию (Middle / Senior). Реляционные СУБД: общие концепции SQL + различия MySQL (InnoDB) и PostgreSQL. Термины на английском там, где это принято.

Содержание


Реляционная модель и ключи

Реляционная БД хранит данные в таблицах (отношениях): строки (записи) и столбцы (атрибуты). Связи между таблицами задаются ключами.

  • Первичный ключ (PRIMARY KEY) — уникальный идентификатор строки, не может быть NULL. Обычно суррогатный (auto-increment / serial / identity).
  • Внешний ключ (FOREIGN KEY) — ссылка на первичный ключ другой таблицы; обеспечивает ссылочную целостность (нельзя сослаться на несуществующую строку). Каскады: ON DELETE CASCADE, ON DELETE SET NULL, RESTRICT.
  • UNIQUE — уникальность значений в столбце (может быть NULL).
  • CHECK — ограничение на значения (CHECK (age >= 0)).
  • NOT NULL — запрет NULL.
CREATE TABLE users (
    id         BIGINT PRIMARY KEY AUTO_INCREMENT,   -- MySQL
    email      VARCHAR(255) NOT NULL UNIQUE,
    age        INT CHECK (age >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id      BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

В PostgreSQL автоинкремент — BIGSERIAL или GENERATED ALWAYS AS IDENTITY.

Суррогатный vs естественный ключ. Суррогатный (искусственный id) стабилен и не зависит от бизнес-данных; естественный (например, email) несёт смысл, но может меняться — обычно предпочитают суррогатный PK + UNIQUE на естественный атрибут.


Нормальные формы

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

1NF (первая нормальная форма). Все значения атомарны (в ячейке одно значение, нет списков/массивов), нет повторяющихся групп столбцов, у каждой строки уникальный ключ.

Плохо (нарушение 1NF):
| id | name | phones            |
|  1 | Вася | 111-11, 222-22    |   ← несколько значений в одной ячейке

Хорошо (1NF): вынести телефоны в отдельную таблицу phones(user_id, phone)

2NF (вторая нормальная форма). 1NF + нет частичной зависимости неключевого атрибута от части составного ключа (актуально только при составном первичном ключе). Каждый неключевой атрибут зависит от всего ключа целиком.

Плохо: таблица order_items(order_id, product_id, product_name, qty)
  PK = (order_id, product_id), но product_name зависит только от product_id (часть ключа)
Хорошо: product_name вынести в таблицу products(product_id, product_name)

3NF (третья нормальная форма). 2NF + нет транзитивной зависимости: неключевой атрибут не должен зависеть от другого неключевого атрибута.

Плохо: employees(id, name, dept_id, dept_name)
  dept_name зависит от dept_id (неключевого), а не напрямую от id
Хорошо: departments(dept_id, dept_name) отдельно

BCNF (форма Бойса–Кодда) — усиленная 3NF: для любой функциональной зависимости X → Y детерминант X должен быть суперключом. Решает редкие случаи, когда 3NF выполнена, но остаётся аномалия из-за перекрывающихся кандидатных ключей.

Дальше есть 4NF (многозначные зависимости) и 5NF, но на практике почти всегда стремятся к 3NF/BCNF.

Кратко-мнемонически: 1NF — атомарность; 2NF — зависимость от всего ключа; 3NF — «ничего, кроме ключа» (никаких зависимостей между неключевыми полями).


Денормализация

Денормализация — намеренное добавление избыточности (дублирование данных, предвычисленные поля) ради производительности чтения: меньше JOIN-ов, меньше запросов.

Примеры: хранить comments_count в таблице постов вместо COUNT(*) каждый раз; дублировать user_name в заказе, чтобы не джойнить users.

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


Типы данных: различия MySQL и PostgreSQL

Общие: целые (INT, BIGINT), числа с точкой (DECIMAL/NUMERIC — точные, для денег; FLOAT/DOUBLE — приблизительные), строки (VARCHAR, TEXT), дата/время (DATE, TIMESTAMP), булев.

Различия:

MySQL PostgreSQL
Boolean TINYINT(1) (нет настоящего bool) настоящий BOOLEAN
JSON JSON (бинарный формат с 5.7, с проверкой; индекс — через generated column / functional index) JSON (текст) и JSONB (бинарный, индексируемый GIN)
Массивы нет есть (INTEGER[], TEXT[])
Enum ENUM('a','b') через CREATE TYPE ... AS ENUM
UUID хранят в BINARY(16)/CHAR(36) нативный тип UUID
Диапазоны нет int4range, tsrange и др.
Геоданные SPATIAL-типы PostGIS (мощнейшее расширение)
Автоинкремент AUTO_INCREMENT SERIAL / IDENTITY
Свои типы нет да (расширяемость)

Для денег всегда DECIMAL/NUMERIC, никогда FLOAT (ошибки округления).


JOIN — виды соединений

JOIN объединяет строки из нескольких таблиц по условию.

-- INNER JOIN — только совпадающие строки в обеих таблицах
SELECT u.name, o.id
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN (LEFT OUTER) — все строки слева + совпавшие справа (нет пары → NULL справа)
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- RIGHT JOIN — все строки справа + совпавшие слева
SELECT u.name, o.id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

-- FULL OUTER JOIN — все строки из обеих таблиц (нет пары → NULL с соответствующей стороны)
SELECT u.name, o.id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

-- CROSS JOIN — декартово произведение (каждая с каждой)
SELECT u.name, p.title FROM users u CROSS JOIN products p;

-- SELF JOIN — таблица соединяется сама с собой (иерархии, пары)
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Схема «на пальцах»:

INNER      : только пересечение A ∩ B
LEFT       : весь A + совпадения из B
RIGHT      : весь B + совпадения из A
FULL OUTER : A ∪ B (всё, с NULL где нет пары)
CROSS      : все комбинации A × B

Важное различие: MySQL не поддерживает FULL OUTER JOIN напрямую — его эмулируют через LEFT JOIN UNION RIGHT JOIN. PostgreSQL поддерживает нативно.

Anti-join: найти строки без пары — через LEFT JOIN ... WHERE b.id IS NULL (например, пользователи без заказов); это нормальный приём, эквивалентный NOT EXISTS (оптимизатор обычно строит одинаковый план).

Как БД физически выполняет JOIN (это показывает EXPLAIN):

  • Nested Loop Join — для каждой строки одной таблицы ищем совпадения в другой (хорошо, когда одна таблица маленькая и есть индекс на второй).
  • Hash Join — строит хеш-таблицу по одной стороне, проходит по другой (хорошо для больших наборов без сортировки).
  • Merge Join — обе стороны отсортированы по ключу и сливаются (хорошо, когда данные уже отсортированы/есть индексы).

Подзапросы и EXISTS

Подзапрос (subquery) — запрос внутри запроса. Виды по месту:

-- В WHERE (скалярный / со списком)
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);        -- скалярный (одно значение)

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);  -- список

-- В FROM (производная таблица, derived table)
SELECT dept, avg_sal FROM (
    SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
) AS t WHERE avg_sal > 50000;

-- В SELECT (скалярный подзапрос на строку)
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders_count
FROM users u;

Коррелированный подзапрос — ссылается на внешний запрос и выполняется для каждой строки внешнего (может быть медленным). Пример выше в SELECT — коррелированный.

EXISTS vs IN:

-- EXISTS — есть ли хоть одна связанная строка (часто эффективнее на больших данных)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NOT EXISTS — пользователи без заказов
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
  • EXISTS останавливается на первом совпадении (не материализует весь список); хорош для проверки наличия.
  • IN со списком удобен, но осторожно с NOT IN, если в подзапросе есть NULL — вернёт неожиданно пусто (из-за трёхзначной логики). NOT EXISTS безопаснее.
  • Часто подзапрос можно переписать через JOIN — оптимизатор во многих случаях сам это делает, но EXISTS/JOIN обычно предсказуемее по плану, чем коррелированный подзапрос.

GROUP BY, HAVING, агрегаты

Агрегатные функции сворачивают набор строк в одно значение: COUNT(), SUM(), AVG(), MIN(), MAX(). GROUP BY группирует строки, агрегат считается по каждой группе.

SELECT status, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
WHERE created_at >= '2024-01-01'    -- фильтр СТРОК (до группировки)
GROUP BY status
HAVING SUM(amount) > 10000          -- фильтр ГРУПП (после группировки)
ORDER BY total DESC;

Ключевое различие: WHERE фильтрует строки до группировки, HAVING — группы после агрегации (в HAVING можно использовать агрегаты, в WHERE — нельзя).

Порядок выполнения SQL (логический): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Поэтому алиас из SELECT не виден в WHERE, но виден в ORDER BY.

Нюанс: в строгом SQL (и PostgreSQL) в SELECT при GROUP BY можно указывать только столбцы из GROUP BY или агрегаты. MySQL исторически это позволял (режим ONLY_FULL_GROUP_BY в 5.7+ по умолчанию включён и это запрещает). COUNT(*) считает все строки, COUNT(col) — только не-NULL, COUNT(DISTINCT col) — уникальные.


CTE (WITH) и рекурсивные запросы

CTE (Common Table Expression) — именованный временный результат внутри запроса через WITH. Делает сложные запросы читаемыми (вместо вложенных подзапросов) и позволяет переиспользовать промежуточный результат.

WITH paid_orders AS (
    SELECT * FROM orders WHERE status = 'paid'
),
by_user AS (
    SELECT user_id, SUM(amount) AS total FROM paid_orders GROUP BY user_id
)
SELECT u.name, b.total
FROM by_user b
JOIN users u ON u.id = b.user_id
WHERE b.total > 5000;

Рекурсивный CTE (WITH RECURSIVE) — для иерархий/деревьев (категории, оргструктура, графы). Состоит из «якоря» (базовая часть) и рекурсивной части, объединённых UNION ALL:

WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories WHERE parent_id IS NULL      -- якорь: корни
    UNION ALL
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id            -- рекурсия: дети
)
SELECT * FROM tree ORDER BY level;

Так одним запросом обходят всё дерево категорий любой глубины. Поддерживается и в MySQL 8+, и в PostgreSQL. CTE удобны для читаемости; в PostgreSQL старые версии материализовали CTE (барьер оптимизации), с 12-й — инлайнят, если можно.


Оконные функции

Оконные функции (window functions) делают вычисления по «окну» строк, не схлопывая их в одну (в отличие от GROUP BY). Синтаксис — функция + OVER (...) с PARTITION BY (разбить на группы) и ORDER BY (порядок внутри окна).

SELECT
    name, department, salary,
    AVG(salary) OVER (PARTITION BY department)                AS dept_avg,   -- средняя по отделу, но строки сохранены
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    ROW_NUMBER() OVER (ORDER BY salary DESC)                  AS overall_row,
    salary - LAG(salary) OVER (ORDER BY salary)               AS diff_prev   -- разница с предыдущей строкой
FROM employees;

Частые функции:

  • ROW_NUMBER() — сквозная нумерация строк.
  • RANK() / DENSE_RANK() — ранг с пропусками / без пропусков при равенстве.
  • LAG() / LEAD() — значение из предыдущей/следующей строки (например, сравнить с прошлым месяцем).
  • SUM()/AVG()/COUNT() OVER (...) — агрегаты по окну (в т.ч. нарастающий итог с ORDER BY + рамкой ROWS).
  • FIRST_VALUE()/LAST_VALUE()/NTILE().

Типовые задачи: «топ-N в каждой группе» (через ROW_NUMBER() в подзапросе/CTE), нарастающий итог, сравнение с предыдущим периодом, дедупликация. Поддерживаются в MySQL 8+ и PostgreSQL. Разница с GROUP BY: агрегат схлопывает строки, оконная функция добавляет вычисленный столбец к каждой строке.


Представления (VIEW)

VIEW (представление) — сохранённый именованный запрос, к которому обращаются как к таблице. Данные не хранит (это «виртуальная таблица») — при обращении выполняется его запрос.

CREATE VIEW active_customers AS
SELECT id, name, email FROM users WHERE active = true;

SELECT * FROM active_customers WHERE name LIKE 'А%';   -- как к таблице

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

Materialized View (материализованное представление) — хранит результат физически (кэш запроса). Быстрое чтение тяжёлой аналитики, но данные не обновляются автоматически — нужно REFRESH:

CREATE MATERIALIZED VIEW sales_report AS
SELECT date_trunc('day', created_at) AS day, SUM(amount) AS total
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW sales_report;                  -- обновить (можно CONCURRENTLY)

Материализованные VIEW есть в PostgreSQL (в MySQL нативно нет — эмулируют таблицей + обновлением по расписанию/триггеру). Применение — дашборды и тяжёлые агрегаты, где допустима небольшая задержка актуальности.


Триггеры и хранимые процедуры

Триггер (trigger) — код, автоматически выполняемый БД в ответ на событие (INSERT/UPDATE/DELETE), BEFORE или AFTER строки. Применение: аудит/история изменений, автозаполнение полей, поддержка денормализованных счётчиков, валидация на уровне БД.

-- PostgreSQL: функция + триггер обновляют updated_at при UPDATE
CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_updated
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Хранимая процедура / функция — код (на PL/pgSQL в PostgreSQL, SQL/процедурном языке в MySQL), хранящийся и выполняющийся в БД. Функция возвращает значение и вызывается в запросе; процедура (CALL) может не возвращать и управлять транзакциями.

-- PostgreSQL: функция
CREATE FUNCTION total_for_user(uid int) RETURNS numeric AS $$
    SELECT COALESCE(SUM(amount), 0) FROM orders WHERE user_id = uid;
$$ LANGUAGE sql;

SELECT total_for_user(1);

Плюсы: логика близко к данным (меньше сетевых round-trip для пакетных операций), переиспользование, атомарность. Минусы (важно для собеседования): бизнес-логику в БД тянуть не стоит — её труднее тестировать, версионировать (миграции), отлаживать и переносить между СУБД; она «размазывает» логику между приложением и базой. Триггеры полезны для аудита/технических полей, но избыток скрытой логики в триггерах усложняет отладку («почему данные изменились сами?»).


Запросы по JSON / JSONB

Обе СУБД умеют хранить JSON, но в PostgreSQL это мощнее (JSONB — бинарный, индексируемый).

PostgreSQL (JSONB):

-- Операторы доступа
SELECT data->'address'->>'city' FROM users;   -- -> вернёт json, ->> вернёт text
SELECT data#>>'{address,city}' FROM users;     -- по пути

-- Поиск "содержит" (@>) — очень быстро с GIN-индексом
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM users WHERE data ? 'phone';       -- есть ли ключ

CREATE INDEX idx_data ON events USING GIN (data);   -- индекс под @>, ? и т.д.

-- Обновление/сборка
UPDATE users SET data = jsonb_set(data, '{active}', 'true');
SELECT jsonb_build_object('id', id, 'name', name) FROM users;

MySQL (JSON):

SELECT JSON_EXTRACT(data, '$.address.city') FROM users;   -- или data->>'$.address.city'
SELECT * FROM users WHERE data->>'$.city' = 'Москва';
UPDATE users SET data = JSON_SET(data, '$.active', true);

Ключевое: JSON в PostgreSQL хранит текст с валидацией (как есть, с пробелами и дублями ключей); JSON в MySQL (с 5.7) — тоже внутренний бинарный формат, но GIN-аналога нет (индексируют через generated column / functional index, multi-valued index для массивов с 8.0.17); JSONB (PostgreSQL) — бинарный, чуть дороже на запись, но быстрый поиск и индексируется GIN (оператор @> «содержит»). JSON удобен для гибких/полуструктурированных данных, но не заменяет нормальную схему для того, что часто фильтруют/джойнят.


Полнотекстовый поиск в PostgreSQL

PostgreSQL имеет встроенный полнотекстовый поиск (с учётом морфологии, стоп-слов, ранжирования) — без внешнего Elasticsearch для средних объёмов.

  • tsvector — документ, разобранный в лексемы (нормализованные слова).
  • tsquery — поисковый запрос.
  • @@ — оператор соответствия.
-- Поиск с учётом морфологии (русская конфигурация)
SELECT * FROM articles
WHERE to_tsvector('russian', title || ' ' || body) @@ to_tsquery('russian', 'поиск & база');

-- Ранжирование по релевантности
SELECT title, ts_rank(to_tsvector('russian', body), q) AS rank
FROM articles, to_tsquery('russian', 'postgresql') q
WHERE to_tsvector('russian', body) @@ q
ORDER BY rank DESC;

Для скорости хранят готовый tsvector в отдельной колонке (обновляя триггером или generated column) и строят по ней GIN-индекс:

ALTER TABLE articles ADD COLUMN search tsvector
    GENERATED ALWAYS AS (to_tsvector('russian', title || ' ' || body)) STORED;
CREATE INDEX idx_search ON articles USING GIN (search);

Когда хватает PG-полнотекста, а когда Elastic/OpenSearch: встроенный поиск хорош для умеренных объёмов и когда не хочется отдельной системы; для больших объёмов, сложного ранжирования, фасетов, автокомплита, опечаток и векторного поиска — специализированные движки (см. раздел 13). Расширение pg_trgm добавляет поиск по опечаткам/похожести (similarity, ILIKE с индексом).


Индексы: как устроены

Индекс — вспомогательная структура, ускоряющая поиск строк ценой места на диске и замедления записи (при INSERT/UPDATE/DELETE индексы тоже обновляются).

B-Tree (точнее B+Tree) — основной тип индекса в обеих СУБД. Это сбалансированное дерево:

  • Все листья на одном уровне, поиск/вставка/удаление за O(log n).
  • В листьях хранятся значения ключа и указатели на строки; листья связаны в список — отсюда быстрые диапазонные запросы (BETWEEN, <, >) и сортировка (ORDER BY).
  • Значения в узлах упорядочены, поэтому поддерживаются префиксный поиск (LIKE 'abc%'), сравнения, ORDER BY, MIN/MAX. В PostgreSQL B-Tree используется для LIKE 'abc%' только при collation C или индексе с text_pattern_ops/varchar_pattern_ops.

Что ускоряет B-Tree: =, <, >, BETWEEN, IN, ORDER BY, LIKE 'prefix%'. Что НЕ ускоряет: LIKE '%suffix' (нет префикса), функции над столбцом без функционального индекса.

             [ 50 ]
           /        \
     [20 40]        [70 90]
    /   |   \      /   |   \
 листья с упорядоченными ключами → указатели на строки

Индексы в MySQL (InnoDB)

Ключевая особенность InnoDB — кластерный индекс (clustered index):

  • Таблица физически хранится как B+Tree по первичному ключу: сами данные строки лежат в листьях этого дерева. То есть первичный ключ = кластерный индекс, отдельного «heap» нет.
  • Вторичные (secondary) индексы в листьях хранят значение первичного ключа (а не физический указатель). Поэтому поиск по вторичному индексу — это: найти PK во вторичном индексе → затем найти строку в кластерном по PK (два обхода дерева). Это называется «bookmark lookup».
  • Отсюда: короткий первичный ключ выгоден (он дублируется во всех вторичных индексах); случайные UUID как PK плохи (фрагментация вставки), лучше авто-инкремент.

Типы индексов MySQL/InnoDB:

  • B-Tree — по умолчанию (кластерный и вторичные).
  • FULLTEXT — полнотекстовый поиск (MATCH ... AGAINST).
  • SPATIAL — для геоданных (R-Tree).
  • HASH — только в движке MEMORY (не InnoDB).
  • Adaptive Hash Index — InnoDB сам автоматически строит хеш поверх часто используемых страниц B-Tree (прозрачно для разработчика).

Индексы в PostgreSQL

Ключевая особенность — таблицы хранятся в куче (heap), а индексы отдельно ссылаются на физическое расположение строки (TID/ctid). PostgreSQL не имеет кластерного индекса по умолчанию (команда CLUSTER делает разовую физическую пересортировку, но она не поддерживается автоматически).

Типы индексов PostgreSQL (богаче, чем в MySQL):

  • B-Tree — по умолчанию; для =, диапазонов, сортировки. 90% случаев.
  • Hash — только для = (равенство). Раньше были ненадёжны, с версии 10 — WAL-логируемые и рабочие, но B-Tree обычно не хуже.
  • GiST — обобщённое дерево поиска: геоданные (PostGIS), диапазоны, полнотекст, ближайшие соседи (KNN).
  • SP-GiST — для несбалансированных структур (quad-tree, разреженные данные).
  • GIN (Generalized Inverted Index) — «обратный индекс»: для составных значений — массивы, JSONB, полнотекстовый поиск (tsvector). Быстрый поиск «содержит».
  • BRIN (Block Range Index) — очень компактный индекс для огромных таблиц, где данные физически упорядочены (например, по времени): хранит min/max по блокам. Маленький и дешёвый на гигантских объёмах.
-- GIN для JSONB — поиск по содержимому
CREATE INDEX idx_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"type": "click"}';

-- BRIN для таблицы логов, отсортированной по времени
CREATE INDEX idx_ts ON logs USING BRIN (created_at);

Также PostgreSQL умеет index-only scan — если все нужные столбцы есть в индексе и строка «видима» (visibility map), данные берутся прямо из индекса, без обращения к heap.


Виды индексов по назначению

Работает в обеих СУБД (поверх B-Tree):

  • Уникальный (UNIQUE) — гарантирует уникальность + ускоряет поиск.
  • Составной (композитный) — по нескольким столбцам. Работает правило «крайнего левого префикса» (leftmost prefix): индекс (a, b, c) помогает запросам по a, (a, b), (a, b, c), но НЕ по b или c отдельно. Порядок столбцов важен: сначала те, по которым равенство, потом диапазон.
CREATE INDEX idx_user_status_date ON orders (user_id, status, created_at);
-- ускорит: WHERE user_id = 1 AND status = 'paid' ORDER BY created_at
-- НЕ ускорит: WHERE status = 'paid' (нет левого префикса user_id)
  • Покрывающий (covering) — индекс содержит все столбцы, нужные запросу, поэтому не нужно обращаться к таблице. В PostgreSQL — INCLUDE, в MySQL — просто входят в составной индекс.
-- PostgreSQL: значение email лежит прямо в индексе
CREATE INDEX idx_cov ON users (id) INCLUDE (email);
  • Частичный (partial) — индексирует только часть строк по условию (меньше и быстрее). PostgreSQL — полноценно; MySQL — нет (эмулируют иначе).
CREATE INDEX idx_active ON users (email) WHERE is_active = true; -- PostgreSQL
  • Функциональный (по выражению) — индекс по результату функции.
CREATE INDEX idx_lower_email ON users (LOWER(email));    -- PostgreSQL; поиск WHERE LOWER(email) = ...
CREATE INDEX idx_lower_email ON users ((LOWER(email)));  -- MySQL 8.0.13+: выражение в двойных скобках
  • Префиксный (MySQL) — индекс по первым N символам строки: INDEX (name(10)).

Когда индекс не используется

Частая тема на собеседовании — почему индекс есть, а он «не работает»:

  • Функция/преобразование над столбцом: WHERE YEAR(created_at) = 2024 — индекс по created_at не сработает (нужен функциональный индекс или переписать как диапазон created_at BETWEEN ...).
  • LIKE '%text' — с ведущим % префиксный поиск невозможен.
  • Неявное приведение типов: WHERE phone = 12345, если phone — строка.
  • Нарушен левый префикс составного индекса.
  • Низкая селективность: если условие выбирает большую часть таблицы (например, status с двумя значениями), оптимизатор посчитает полное сканирование дешевле индекса.
  • Устаревшая статистика — оптимизатор ошибается в оценках (нужно ANALYZE в PostgreSQL / актуальная статистика в MySQL).
  • OR по разным столбцам без подходящих индексов.

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


EXPLAIN и EXPLAIN ANALYZE

EXPLAIN показывает план выполнения запроса (как СУБД собирается его выполнять) — без реального выполнения. EXPLAIN ANALYZE реально выполняет запрос и показывает фактическое время и число строк (осторожно с UPDATE/DELETE — они реально выполнятся; оборачивайте в транзакцию с откатом).

PostgreSQL

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

Что смотреть:

  • Тип узла: Seq Scan (полное сканирование — плохо на больших таблицах), Index Scan, Index Only Scan (хорошо), Bitmap Heap Scan (много совпадений через индекс), Nested Loop / Hash Join / Merge Join.
  • cost=0.00..25.88 — оценка стоимости (условные единицы): стартовая..полная. Меньше — лучше.
  • rows — оценка числа строк. В ANALYZE появляется actual rows и actual time — если оценка сильно расходится с фактом, статистика устарела или запрос сложный для оценки.
  • BUFFERS — сколько страниц прочитано (shared hit — из кэша, read — с диска).
Index Scan using idx_user on orders  (cost=0.42..8.44 rows=1 width=..) (actual time=0.03..0.05 rows=1 loops=1)
  Index Cond: (user_id = 42)

MySQL

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
EXPLAIN FORMAT=JSON SELECT ...;         -- подробный план
EXPLAIN ANALYZE SELECT ...;             -- с 8.0.18: реальное выполнение

Что смотреть (ключевые колонки):

  • type — способ доступа, от лучшего к худшему: system / const (по PK/unique, одна строка) → eq_ref → ref (по индексу) → range (диапазон по индексу) → index (полный проход индекса) → ALL (полное сканирование таблицы — обычно плохо).
  • key — какой индекс реально выбран (possible_keys — какие рассматривались).
  • rows — оценка числа просмотренных строк.
  • Extra — важные пометки: Using index (покрывающий индекс — хорошо), Using where, Using filesort (сортировка без индекса — потенциально дорого), Using temporary (временная таблица — дорого).

Практика использования: смотрим, нет ли Seq Scan/type: ALL на большой таблице; появляется ли filesort/temporary; используется ли ожидаемый индекс; сходятся ли оценки строк с фактом (в ANALYZE). По результатам — добавляем/меняем индекс, переписываем запрос, обновляем статистику.


Транзакции и ACID

Транзакция — набор операций, выполняемых как единое целое. Свойства ACID:

  • Atomicity (атомарность) — всё или ничего: либо все операции применились, либо ни одной (rollback при ошибке).
  • Consistency (согласованность) — БД переходит из одного корректного состояния в другое (соблюдаются ограничения).
  • Isolation (изолированность) — параллельные транзакции не мешают друг другу (степень зависит от уровня изоляции).
  • Durability (устойчивость) — после commit данные сохранены даже при сбое (за счёт журнала: WAL в PostgreSQL, redo log в InnoDB).
BEGIN;                       -- START TRANSACTION
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                      -- или ROLLBACK при ошибке

Уровни изоляции и аномалии

Аномалии параллельного доступа:

  • Dirty read (грязное чтение) — чтение незакоммиченных данных другой транзакции.
  • Non-repeatable read (неповторяемое чтение) — повторное чтение той же строки даёт другой результат (её изменили и закоммитили между чтениями).
  • Phantom read (фантомное чтение) — повторный запрос по условию возвращает новые/исчезнувшие строки (кто-то вставил/удалил подходящие).

Уровни изоляции (по стандарту SQL, от слабого к строгому):

Уровень Dirty read Non-repeatable Phantom
READ UNCOMMITTED возможно возможно возможно
READ COMMITTED нет возможно возможно
REPEATABLE READ нет нет возможно*
SERIALIZABLE нет нет нет

Различия по умолчанию:

  • PostgreSQL — по умолчанию READ COMMITTED. На REPEATABLE READ использует snapshot на всю транзакцию — фантомов нет вовсе (строже стандарта), но возможен write skew (две транзакции читают пересекающиеся данные и пишут в разные строки); его ловит только SERIALIZABLE — через SSI (serializable snapshot isolation).
  • MySQL (InnoDB) — по умолчанию REPEATABLE READ: обычные SELECT (consistent read) читают снимок, поэтому фантомов не видят; gap/next-key locks (блокировки промежутков) работают только для блокирующих чтений и записи (SELECT ... FOR UPDATE/FOR SHARE, UPDATE, DELETE) и не дают вставить фантом в диапазон (* — поэтому в InnoDB и PostgreSQL REPEATABLE READ на практике строже стандарта).

MVCC

MVCC (Multi-Version Concurrency Control) — многоверсионность: вместо блокировки на чтение СУБД хранит несколько версий строки, и каждая транзакция видит «снимок» данных на определённый момент. Итог: читатели не блокируют писателей, писатели не блокируют читателей.

  • PostgreSQL: при UPDATE создаётся новая версия строки, старая остаётся до тех пор, пока её видят другие транзакции. Мёртвые версии потом убирает VACUUM (и autovacuum). Минус — «раздувание» (bloat) таблиц и необходимость вакуума.
  • MySQL (InnoDB): старые версии хранятся в undo log; читатели видят консистентный снимок (consistent read). Очистка старых версий — фоновый purge.

На собеседовании: MVCC объясняет, почему в обеих БД чтение обычно не ждёт запись.


Блокировки

  • Пессимистичная блокировка — заранее блокируем строки, чтобы никто не изменил, пока мы работаем. SELECT ... FOR UPDATE (эксклюзивная, на запись), SELECT ... FOR SHARE (разделяемая, на чтение).
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- строка заблокирована
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
  • Оптимистичная блокировка — не блокируем, а проверяем при записи, не изменилась ли строка (через колонку версии/updated_at): UPDATE ... WHERE id = 1 AND version = 5. Если 0 строк обновлено — кто-то опередил, повторяем.

  • Deadlock (взаимоблокировка) — две транзакции ждут ресурсы друг друга по кругу. СУБД обнаруживает и откатывает одну из них (нужно ловить ошибку и повторять). Профилактика — блокировать ресурсы всегда в одном порядке.

  • Гранулярность: блокировки строк (row-level, InnoDB/Postgres) лучше, чем блокировки таблиц. Gap locks в InnoDB блокируют промежутки между строками (защита от фантомов при блокирующих чтениях FOR UPDATE/FOR SHARE, UPDATE/DELETE на REPEATABLE READ).


Оптимизация запросов

  • Индексы под конкретные запросы (WHERE, JOIN, ORDER BY), с учётом селективности и левого префикса.
  • Проблема N+1 (на уровне ORM) — вместо запроса на каждую связанную сущность делать один JOIN / IN.
  • EXPLAIN для поиска Seq Scan/ALL, filesort, temporary.
  • Избегать SELECT * — тянуть только нужные столбцы (помогает покрывающим индексам).
  • Батчинг — вставки пачками (INSERT ... VALUES (...), (...)), а не по одной.
  • Актуальная статистика — ANALYZE (PostgreSQL); оптимизатор опирается на неё.
  • Денормализация / материализованные представления — предвычислять тяжёлые агрегаты. В PostgreSQL есть MATERIALIZED VIEW.
  • Партиционирование (partitioning) — разбить огромную таблицу на части (по дате, по диапазону id) — запросы работают с нужной партицией. Есть в обеих СУБД.
  • Пул соединений — PgBouncer (PostgreSQL), т.к. соединения дорогие (см. ниже).

Пагинация на больших таблицах

Классический LIMIT ... OFFSET ... на больших смещениях медленный: БД всё равно перебирает и отбрасывает OFFSET строк.

-- Медленно на больших offset:
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;

Keyset / cursor-пагинация — вместо offset запоминаем последнее значение и берём «после» него (нужен индекс по ключу сортировки):

-- Быстро: используем индекс, не перебираем отброшенные строки
SELECT * FROM posts WHERE id > :last_seen_id ORDER BY id LIMIT 20;

Минус keyset — нельзя прыгнуть на произвольную страницу, только «дальше/назад». Плюс — стабильная скорость и нет пропусков/дублей при вставках между запросами.


MySQL vs PostgreSQL: плюсы и минусы

Обе — зрелые бесплатные реляционные СУБД. Различия важны для собеседования.

PostgreSQL — сильные стороны:

  • Богатая типизация: JSONB (индексируемый), массивы, диапазоны, UUID, собственные типы, ENUM.
  • Продвинутые индексы: GIN, GiST, BRIN, SP-GiST, частичные, функциональные, INCLUDE.
  • Строже соответствует стандарту SQL; полноценные FULL OUTER JOIN, оконные функции, CTE (в т.ч. рекурсивные), SERIALIZABLE через SSI.
  • Расширяемость (расширения: PostGIS для гео, pg_trgm, и т.д.).
  • Лучше для сложной аналитики, гео, полнотекста, нестандартных запросов.

PostgreSQL — минусы:

  • MVCC даёт bloat → нужен VACUUM/autovacuum и мониторинг.
  • Модель «процесс на соединение» → дорого много коннектов, нужен пул (PgBouncer).
  • Тяжелее по памяти на соединение.

MySQL (InnoDB) — сильные стороны:

  • Очень быстр на простых read-heavy нагрузках и точечных выборках по PK (кластерный индекс).
  • Проще в освоении и эксплуатации, огромная распространённость и хостинг-поддержка.
  • Зрелая репликация, много инструментов; популярен в вебе (LAMP).

MySQL — минусы:

  • Исторически меньше стандарта (CTE, оконные функции, CHECK заработали полноценно только в 8.0).
  • Нет нативного FULL OUTER JOIN, массивов, JSONB; меньше типов индексов.
  • Нюансы движков хранения; некоторые особенности поведения (например, utf8 ≠ полноценный юникод — нужен utf8mb4).

Кратко: PostgreSQL — когда нужны сложные запросы, аналитика, гибкие типы, строгие гарантии. MySQL — когда нужен простой быстрый бэкенд под типовые CRUD/read-heavy нагрузки. В MySQL 8.0 разрыв по фичам сильно сократился.


Масштабирование: репликация и шардирование

  • Репликация — копии БД. Master-slave (primary-replica): запись на мастер, чтение с реплик (масштабирование чтения). В PostgreSQL — потоковая (streaming) репликация через WAL; в MySQL — binlog-репликация. Бывает синхронная (надёжнее, медленнее) и асинхронная (быстрее, риск отставания реплики — replication lag).
  • Failover — при падении мастера одна из реплик становится новым мастером.
  • Шардирование (sharding) — горизонтальное разбиение данных по нескольким серверам по ключу шардирования (например, по user_id). Масштабирует запись и объём, но усложняет запросы между шардами и транзакции. Инструменты: Vitess (MySQL), Citus (PostgreSQL).
  • Партиционирование — разбиение одной таблицы на части внутри одной БД (не путать с шардированием на разные серверы).
  • Пул соединений — PgBouncer (PostgreSQL), ProxySQL (MySQL).

Порядок обычно такой: сначала индексы и оптимизация запросов → кэш (Redis) → реплики для чтения → партиционирование → и только при реальной необходимости шардирование.


Вероятные вопросы

Что такое нормализация и зачем? Организация данных для устранения избыточности и аномалий. 1NF — атомарность; 2NF — нет частичной зависимости от части составного ключа; 3NF — нет транзитивной зависимости между неключевыми полями; BCNF — усиленная 3NF.

Когда денормализуют? Ради скорости чтения (меньше JOIN/агрегатов), точечно, после того как профилирование показало узкое место; ценой сложности поддержки целостности.

Какие бывают JOIN? INNER (пересечение), LEFT/RIGHT OUTER (все с одной стороны + совпавшие), FULL OUTER (всё), CROSS (декартово), SELF (сам с собой). В MySQL нет нативного FULL OUTER JOIN.

Как физически выполняется JOIN? Nested Loop, Hash Join, Merge Join — выбор делает оптимизатор (видно в EXPLAIN).

Как устроен индекс B-Tree? Сбалансированное дерево, поиск за O(log n); листья упорядочены и связаны — быстрые диапазоны и сортировка. Ускоряет =, <, >, BETWEEN, LIKE 'prefix%' (в PostgreSQL — при collation C или *_pattern_ops), ORDER BY.

Кластерный индекс в MySQL — что это? В InnoDB таблица хранится как B+Tree по первичному ключу (данные в листьях). Вторичные индексы хранят значение PK → поиск по ним требует второго обхода до кластерного. Поэтому важен короткий PK.

Чем отличаются индексы PostgreSQL? Таблица хранится в heap (нет кластерного по умолчанию). Типы: B-Tree, Hash, GiST, SP-GiST, GIN (массивы/JSONB/полнотекст), BRIN (огромные упорядоченные данные). Есть index-only scan.

Что такое покрывающий индекс? Индекс, содержащий все нужные запросу столбцы — не нужно обращаться к таблице. В PostgreSQL — INCLUDE.

Что такое составной индекс и правило левого префикса? Индекс по нескольким столбцам; используется для запросов по левому префиксу столбцов ((a,b,c) → по a, (a,b), (a,b,c), но не по b).

Почему индекс может не использоваться? Функция над столбцом, LIKE '%...', приведение типов, нарушение левого префикса, низкая селективность, устаревшая статистика.

Зачем EXPLAIN и EXPLAIN ANALYZE? EXPLAIN — план без выполнения; ANALYZE — с реальным выполнением и фактическим временем/строками. Ищем Seq Scan/type: ALL, filesort, temporary, расхождение оценок с фактом.

Что смотреть в EXPLAIN MySQL? type (плохо — ALL), key (выбранный индекс), rows, Extra (Using index хорошо, Using filesort/Using temporary — дорого).

Что такое ACID? Atomicity, Consistency, Isolation, Durability — гарантии транзакций.

Уровни изоляции и аномалии? READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE; аномалии — dirty read, non-repeatable read, phantom. По умолчанию: PostgreSQL — READ COMMITTED, MySQL InnoDB — REPEATABLE READ.

Что такое MVCC? Многоверсионность: читатели не блокируют писателей. В PostgreSQL мёртвые версии убирает VACUUM, в InnoDB старые версии — в undo log.

Пессимистичная vs оптимистичная блокировка? Пессимистичная — SELECT ... FOR UPDATE (блокируем заранее); оптимистичная — проверка версии при UPDATE (без блокировки). Deadlock СУБД обнаруживает и откатывает одну транзакцию.

Чем MySQL отличается от PostgreSQL? PostgreSQL — богаче типы (JSONB, массивы), продвинутые индексы, строже стандарт, сложная аналитика; минус — VACUUM, дорогие соединения. MySQL — проще и очень быстр на простых read-heavy нагрузках, кластерный индекс по PK; исторически меньше стандартных фич (до 8.0).

Как ускорить медленный запрос? EXPLAIN → подходящий индекс (селективность, левый префикс, покрывающий) → убрать SELECT * и функции над столбцами → обновить статистику → при необходимости денормализация/партиционирование/кэш.

Как правильно делать пагинацию на больших данных? Keyset/cursor (WHERE id > :last ORDER BY id LIMIT n) вместо больших OFFSET.

Как масштабировать БД? Индексы/оптимизация → кэш → реплики для чтения → партиционирование → шардирование (в крайнем случае). Соединения — через пул (PgBouncer/ProxySQL).

Почему для денег нельзя FLOAT? Приблизительный тип даёт ошибки округления; нужен точный DECIMAL/NUMERIC.

WHERE vs HAVING? WHERE фильтрует строки до группировки (без агрегатов); HAVING — группы после агрегации (можно агрегаты). Порядок: FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY.

EXISTS vs IN? EXISTS останавливается на первом совпадении (часто эффективнее, безопасен с NULL); IN со списком удобен, но NOT IN ломается на NULL — предпочитать NOT EXISTS.

Что такое CTE и рекурсивный CTE? WITH — именованный временный результат для читаемости; WITH RECURSIVE — обход иерархий/деревьев (якорь + рекурсивная часть через UNION ALL). Есть в MySQL 8+/PostgreSQL.

Оконные функции — зачем и чем отличаются от GROUP BY? Считают по «окну» строк (OVER (PARTITION BY ... ORDER BY ...)), не схлопывая строки. ROW_NUMBER/RANK (топ-N в группе), LAG/LEAD (сравнение с соседней), нарастающий итог. GROUP BY схлопывает, оконная — добавляет столбец к каждой строке.

VIEW vs Materialized View? VIEW — сохранённый запрос (виртуальная таблица, всегда актуальна, не ускоряет); Materialized View — хранит результат физически (быстрое чтение тяжёлой аналитики, нужно REFRESH). MatView — в PostgreSQL.

Когда триггеры/хранимые процедуры, а когда нет? Триггеры — для аудита/технических полей/счётчиков; процедуры — для пакетных операций близко к данным. Бизнес-логику в БД тянуть не стоит: труднее тестировать, версионировать, отлаживать и переносить.

JSON vs JSONB? JSON в PG — текст с валидацией (в MySQL JSON с 5.7 хранится в бинарном формате); JSONB (PostgreSQL) — бинарный, быстрый поиск, индексируется GIN (оператор @>). Для часто фильтруемых полей лучше нормальные колонки, JSON — для гибких данных.

Как сделать полнотекстовый поиск в PostgreSQL? to_tsvector/to_tsquery + оператор @@, ранжирование ts_rank; хранить tsvector в колонке + GIN-индекс; pg_trgm — поиск по опечаткам. Для больших объёмов/фасетов/векторов — Elastic/OpenSearch.

Что такое коррелированный подзапрос? Подзапрос, ссылающийся на внешний запрос и выполняемый для каждой его строки (может быть медленным); часто переписывается через JOIN/EXISTS.