79 разобранных вопросов по теме «Базы данных / SQL». Каждый — с правильным ответом и пояснением.

• Что нужно знать про ACID?

  Atomicity — целиком или никак. Consistency — валидное состояние БД (constraints). Isolation — параллельные транзакции изолированы (зависит от уровня). Durability — после COMMIT данные сохранятся (WAL в PostgreSQL).

• ACID — расшифровать?

  Atomicity (всё или ничего), Consistency (целостность), Isolation (параллельные транзакции), Durability (сохранность после commit).

• ACID — расшифровать каждое?

  Atomicity — транзакция целиком или никак. Consistency — БД переходит из валидного состояния в валидное. Isolation — параллельные транзакции не мешают. Durability — после commit данные не теряются.

• CASE WHEN — для условных значений?

  SELECT name, CASE WHEN balance > 100000 THEN 'VIP' WHEN balance > 10000 THEN 'Standard' ELSE 'Basic' END AS category FROM accounts. Для AQA: CASE WHEN удобен в SQL-проверках — классифицировать данные без Java-логики.

• Covering index — что это?

  Индекс, который содержит все поля запроса (через INCLUDE в PostgreSQL 11+). Позволяет Index Only Scan — без обращения к таблице.

• В чём разница: DELETE vs TRUNCATE?

  DELETE — построчное удаление с триггерами и WAL, можно откатить в транзакции. TRUNCATE — быстрая очистка целиком, сбрасывает счётчики автоинкремента.

• Что такое DML / DDL / DCL / TCL?

  DML: SELECT, INSERT, UPDATE, DELETE (данные). DDL: CREATE, ALTER, DROP, TRUNCATE (структура). DCL: GRANT, REVOKE (права). TCL: COMMIT, ROLLBACK, SAVEPOINT (транзакции).

• Что такое EXPLAIN ANALYZE?

  EXPLAIN — оценочный план без выполнения. EXPLAIN ANALYZE — реальное выполнение запроса + actual time и число строк. Seq Scan на большой таблице — плохо. Index Scan / Index Only Scan — хорошо. Rows Removed by Filter — индекс не помогает фильтрации.

• LEFT JOIN: ON или WHERE — есть разница?

  Да! Условие в ON применяется ДО объединения — несоответствующие строки правой таблицы сохраняются, а её поля становятся NULL. Условие в WHERE применяется ПОСЛЕ — строки с NULL отфильтровываются, и LEFT JOIN превращается в INNER JOIN.

• Что нужно знать про MVCC?

  Каждая строка: xmin (создавшая транзакция), xmax (удалившая/обновившая). Читатели не блокируют писателей. Snapshot isolation. Старые версии чистит VACUUM (autovacuum). Проблема: table bloat если VACUUM не успевает.

• Что такое MVCC в PostgreSQL?

  Каждая строка: xmin (создавшая транзакция), xmax (удалившая). Читатели не блокируют писателей. Старые версии чистит VACUUM. Проблема: table bloat.

• MVCC в PostgreSQL — как работает?

  Каждая транзакция видит snapshot базы. Вместо изменений — новые версии строк с xmin/xmax. Старые версии чистит VACUUM.

• NULL в SQL — почему NULL = NULL это UNKNOWN?

  NULL — отсутствие значения, не ноль. NULL = NULL → UNKNOWN (не true и не false). Для проверки: IS NULL / IS NOT NULL. COALESCE(a, b) — первый не-NULL. Ловушка: WHERE status != 'active' НЕ вернёт строки с NULL-статусом.

• pg_stat_activity и pg_stat_statements — что показывают?

  pg_stat_activity: текущие сессии и запросы, их состояние (active/idle/waiting), PID, query_start — для поиска long-running queries и блокировок. pg_stat_statements: агрегированная статистика по нормализованным запросам (calls, total_time, mean_time, rows).

• В чём разница: PostgreSQL vs Oracle — ключевые отличия?

  PostgreSQL: SERIAL/IDENTITY (автоинкремент), LIMIT/OFFSET, Read Uncommitted фактически работает как Read Committed. Oracle: SEQUENCE, ROWNUM / FETCH FIRST N ROWS, NVL вместо COALESCE, таблица DUAL для SELECT без таблицы, CONNECT BY для иерархических запросов.

• В чём разница: PRIMARY KEY vs UNIQUE — отличия?

  PRIMARY KEY: уникальность + NOT NULL + один на таблицу + кластерный индекс. UNIQUE: уникальность (допускает один NULL) + может быть несколько. Foreign Key ссылается на PRIMARY KEY (или UNIQUE).

• В чём разница: @Query: JPQL vs native SQL?

  JPQL работает с сущностями и полями Java. Native SQL — прямой SQL, нужен для специфичных фич БД. Native помечается nativeQuery = true.

• Repeatable Read в PG решает фантомное чтение — почему?

  В стандарте SQL Repeatable Read допускает phantom reads. В PG благодаря MVCC (snapshot isolation): транзакция видит snapshot данных на момент начала. Новые строки, вставленные другими транзакциями после старта снимка, ей не видны — поэтому фантомы не появляются без всяких блокировок диапазонов.

• SQL-инъекция — что это и как проверить?

  Вставка SQL-кода в поле ввода: ' OR 1=1 -- в поле логина. Если приложение подставляет ввод напрямую в SQL — получаем доступ ко всем данным. Как проверить: ввести спецсимволы и SQL-фрагменты в поля формы и посмотреть на аномальный ответ или ошибку. Защита — параметризованные запросы (PreparedStatement).

• В чём разница: UNION vs UNION ALL?

  UNION: объединяет результаты и удаляет дубликаты (медленнее — сортировка). UNION ALL: объединяет без удаления (быстрее). Для AQA: если знаешь что дубликатов нет — UNION ALL.

• В чём разница: UUID vs Long как PK для соцсети на 2 млн пользователей?

  Long: компактный, быстрый, но раскрывает количество (user/12345 → 12345 пользователей). UUID: безопаснее, но 2x размер индекса. UUIDv7 (time-ordered): лучший выбор — безопасность UUID + последовательность для B-tree без фрагментации.

• UUID vs Long как PK: что выбрать?

  Long: компактный (8 байт), быстрый B-tree (последовательная вставка), auto-increment. UUID: скрывает количество записей (безопасность), не нужен центральный генератор (распределённые системы). Минусы UUID: 16 байт, а случайный UUIDv4 фрагментирует индекс.

• В чём разница: VIEW vs MATERIALIZED VIEW?

  VIEW: виртуальная таблица, запрос выполняется при каждом обращении. MATERIALIZED VIEW: результат сохраняется на диске, нужен REFRESH для обновления. Для AQA: VIEW для упрощения запросов в тестах, MATERIALIZED VIEW для отчётов.

• В чём разница: WHERE vs HAVING?

  WHERE — до группировки, работает с индексами. HAVING — после GROUP BY, для агрегатов. Пример: HAVING COUNT(*) > 5. Ошибка: ставить в HAVING то, что можно в WHERE — медленнее.

• В Postgres какой уровень по умолчанию?

  Read Committed. В Postgres вообще нет Read Uncommitted — это его особенность. Repeatable Read в Postgres ДОПОЛНИТЕЛЬНО защищает от phantom read через MVCC.

• В PostgreSQL нет Read Uncommitted — почему?

  Минимальный уровень — Read Committed. PostgreSQL использует MVCC: каждая транзакция видит snapshot, грязные данные физически невозможно прочитать (нет dirty read). Read Uncommitted в PG ведёт себя как Read Committed — отдельный уровень просто не нужен.

• В каком порядке выполняются части SELECT-запроса?

  FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Это важно: WHERE применяется ДО агрегации, HAVING — после. Алиасы из SELECT можно использовать в ORDER BY, но не в WHERE.

• В чём отличие MATERIALIZED VIEW?

  Хранит результат запроса физически. Быстрее на чтение, но нужно периодически обновлять (REFRESH). Используется для тяжёлых аналитических запросов.

• В чём разница PRIMARY KEY и UNIQUE?

  PRIMARY KEY — один на таблицу, NOT NULL. UNIQUE — может быть несколько UNIQUE-индексов, разрешает NULL (NULL не равно NULL, поэтому много NULL допустимо).

• В чём разница UNION и UNION ALL?

  UNION — объединяет результаты ДВУХ SELECT, убирает дубликаты. UNION ALL — то же, но БЕЗ удаления дубликатов. UNION ALL быстрее (не нужно сортировать для дедупликации). Если знаешь, что дублей не будет — всегда UNION ALL.

• В чём разница WHERE и HAVING?

  WHERE фильтрует строки ДО группировки и не может ссылаться на агрегаты. HAVING фильтрует уже сгруппированные данные по агрегатам (COUNT, SUM, AVG), например: SELECT user_id, COUNT(*) FROM orders WHERE status='OK' GROUP BY user_id HAVING COUNT(*) > 5.

• Что такое Виды JOIN?

  INNER — пересечение. LEFT — все из левой + совпадения из правой. RIGHT — наоборот. FULL OUTER — всё. CROSS — декартово произведение.

• Где передавать данные: body / path / query / headers?

  Path: идентификатор ресурса (/users/123). Query: фильтрация, пагинация (?page=2&sort=name). Body: данные для создания/обновления (POST/PUT/PATCH). Headers: метаданные (Authorization, Content-Type, Accept).

• Два одинарных индекса vs один составной — что лучше?

  Зависит от запроса. WHERE a=1 AND b=2: составной (a,b) — один Index Scan. Два одинарных: Bitmap Index Scan + Bitmap AND — медленнее. WHERE a=1 OR b=2: два одинарных — Bitmap OR, а составной (a,b) здесь почти бесполезен. Плюс составной работает по префиксу, поэтому порядок колонок важен.

• Зачем нужны индексы?

  Чтобы ускорить поиск. Без индекса — full scan O(n). С B-tree индексом — O(log n). Платим за это: дополнительная память + замедление INSERT/UPDATE/DELETE.

• Что такое Индексы PostgreSQL?

  B-tree (default), Hash, GIN (JSONB, full-text), GiST (геометрия), BRIN (большие таблицы). Покрывающий (INCLUDE).

• Как прочитать EXPLAIN ANALYZE?

  Обратите внимание на Seq Scan (плохо на больших таблицах), Index Scan, Bitmap Heap Scan, Nested Loop vs Hash Join, Rows Removed by Filter, разницу estimated vs actual rows.

• Как читать EXPLAIN ANALYZE?

  Смотри на Seq Scan (на большой таблице — плохо), Index Scan, Bitmap Heap Scan, типы Join (Nested Loop vs Hash Join), Rows Removed by Filter, разницу estimated vs actual rows.

• Какие NoSQL ты знаешь?

  MongoDB — документная (хранит JSON-подобные документы в коллекциях). Redis — key-value, в памяти, очень быстрая. Часто используется как кэш. Cassandra — column-family, для очень больших объёмов с записью.

• Какие виды JOIN ты знаешь?

  INNER JOIN — только совпадения из обеих таблиц. LEFT JOIN — все строки из левой + совпадения из правой (несовпадения = NULL). RIGHT JOIN — наоборот. FULL OUTER JOIN — все строки из обеих, несовпадения = NULL. CROSS JOIN — декартово произведение без условия.

• Какие виды индексов есть?

  B-tree (по умолчанию, для =, <, >, BETWEEN, LIKE 'abc%'). Hash (только =, в Postgres есть, но используется редко). GIN — для массивов, JSONB, full-text. GiST — геометрия, диапазоны.

• Какие группы команд SQL ты знаешь?

  DML (Data Manipulation Language) — работа с данными: SELECT, INSERT, UPDATE, DELETE. DDL (Data Definition Language) — структура: CREATE, ALTER, DROP. DCL (Data Control Language) — права: GRANT, REVOKE. TCL (Transaction Control Language) — транзакции: COMMIT, ROLLBACK.

• Какие переменные можно захватывать в лямбде?

  Только final или effectively final (не меняются после присваивания). Поля класса можно использовать без ограничений. Локальные переменные — только если они эффективно финальные, потому что захват идёт по значению (копия).

• Какие проблемы решает каждый уровень?

  Read Committed — убирает dirty read. Repeatable Read — убирает non-repeatable read. Serializable — убирает phantom read и всё остальное.

• Какие способы работы с БД в Java?

  JDBC — низкий уровень, прямой SQL и работа с ResultSet. JPA / Hibernate — ORM, маппинг объектов на таблицы (JPA — спецификация, Hibernate — её реализация). jOOQ — typesafe SQL builder, generated code. Spring Data — удобная обёртка над JPA, query methods.

• Какие уровни изоляции бывают?

  Read Uncommitted (можно читать незакоммиченные — dirty read). Read Committed (только закоммиченные, но возможен non-repeatable read). Repeatable Read (повторное чтение даст тот же результат, но возможен phantom read). Serializable — самый строгий, исключает все аномалии.

• Кейс: «навесили индексов на все поля — запись стала медленнее»?

  Каждый INSERT/UPDATE/DELETE обновляет ВСЕ индексы. 10 индексов = 10x overhead на запись. VACUUM тоже замедляется. REINDEX может понадобиться. Решение: индексы только по паттернам запросов (WHERE, JOIN, ORDER BY).

• Когда индекс НЕ поможет?

  Маленькие таблицы. Низкая selectivity (boolean). Функции (LOWER(email) — нужен expression index). LIKE '%abc' (wildcard в начале). Часто обновляемые колонки (индекс замедляет INSERT/UPDATE/DELETE).

• Когда индекс не стоит создавать?

  Маленькие таблицы (full scan быстрее). Колонки с малым числом уникальных значений (boolean, enum с 2–3 значениями). Часто меняющиеся колонки — индекс замедлит INSERT/UPDATE/DELETE.

• Можно ли везде ставить Serializable?

  Можно, но не нужно. Serializable — самый строгий уровень, и он сильно просаживает производительность: транзакции часто конфликтуют и откатываются. Для большинства операций хватает Read Committed.

• Что такое Оконные функции?

  ROW_NUMBER(), RANK(), DENSE_RANK() — нумерация/ранжирование. LAG/LEAD — предыдущая/следующая строка. SUM/AVG/COUNT OVER (PARTITION BY ... ORDER BY ...) — агрегация без GROUP BY. В отличие от GROUP BY оконные функции не сворачивают строки.

• Оконные функции — что это и зачем?

  ROW_NUMBER(), RANK(), LAG/LEAD, SUM OVER — агрегации поверх результата без сворачивания строк в группы.

• Подзапрос vs JOIN vs CTE — когда что?

  Подзапрос: в WHERE для фильтрации (WHERE id IN (SELECT ...)). JOIN: для объединения данных из нескольких таблиц. CTE (WITH ... AS): для читаемости сложных запросов и рекурсии; сам по себе запрос не ускоряет.

• Что такое Порядок выполнения SQL-запроса?

  FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Важно: WHERE выполняется ДО GROUP BY (нельзя фильтровать по агрегату в WHERE). HAVING — после GROUP BY (для агрегатов).

• Что такое Порядок колонок в составном индексе?

  Leftmost prefix rule. INDEX (a, b, c) используется для WHERE a=, WHERE a= AND b=, но НЕ для WHERE b= или WHERE c=.

• Почему B-tree, а не Hash?

  Hash: O(1) только =. B-tree: O(log n) но поддерживает <, >, BETWEEN, ORDER BY, LIKE 'abc%'. Поэтому B-tree — default.

• Почему NULL = NULL даёт UNKNOWN, а не TRUE?

  NULL — это «значение неизвестно». Два неизвестных значения — нельзя сказать, равны они или нет. Поэтому NULL = NULL это не TRUE, а UNKNOWN, в условиях ведёт себя как FALSE. Для проверки используется IS NULL.

• Почему Tree индекс в БД, а не Hash?

  Хотя Hash = O(1), Tree (B-tree) поддерживает диапазонные запросы, сортировку, BETWEEN, LIKE 'abc%'. Hash — только точное совпадение.

• Почему нельзя на все поля навесить индексы?

  (1) Каждый индекс нужно обновлять при INSERT/UPDATE/DELETE — замедление записи. (2) Индексы занимают место — на больших таблицах могут весить больше самой таблицы. (3) Они требуют обслуживания (VACUUM, REINDEX) и обновления статистики, что создаёт дополнительную нагрузку.

• Простой запрос: найти пользователей с количеством заказов больше 5?

  SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5;

• Разница между WHERE и HAVING?

  WHERE фильтрует строки ДО группировки и работает с индексами. HAVING — ПОСЛЕ группировки, для условий на агрегаты (HAVING SUM(amount) > 1000).

• Что такое Расскажи ACID?

  Atomicity — атомарность: транзакция выполняется целиком или откатывается. Consistency — консистентность: БД переходит из одного валидного состояния в другое (констрейнты соблюдаются). Isolation — изоляция параллельных транзакций друг от друга. Durability — сохранность зафиксированных данных после сбоя.

• Что такое Типы индексов PostgreSQL?

  B-tree (default) — =, <, >, BETWEEN, ORDER BY, LIKE 'abc%'. Hash — только =. GIN — массивы, JSONB, full-text. GiST — геометрия, диапазоны. BRIN — большие таблицы с порядком (timestamp). LIKE с ведущим '%' индексом не ускоряется.

• Что такое Типы индексов в PostgreSQL?

  B-tree (default — =, <, >, BETWEEN, ORDER BY), Hash (только =), GIN (массивы, JSONB, full-text), GiST (геометрия), BRIN (большие таблицы с порядком), SP-GiST.

• Что такое Уровни изоляции?

  READ_UNCOMMITTED — dirty read. READ_COMMITTED (default PG) — только закоммиченные. REPEATABLE_READ — повторное чтение = тот же результат. SERIALIZABLE — полная изоляция, исключает фантомы. Выше уровень → больше консистентности, но меньше параллелизма.

• Что такое Уровни изоляции и какие проблемы решают?

  READ_UNCOMMITTED — dirty read. READ_COMMITTED — non-repeatable read. REPEATABLE_READ — phantom read. SERIALIZABLE — полная сериализация.

• Что такое Уровни изоляции транзакций?

  READ_UNCOMMITTED, READ_COMMITTED (default в PostgreSQL), REPEATABLE_READ, SERIALIZABLE. От низкого к высокому: больше консистентности, меньше параллелизма.

• Чем отличается DELETE от TRUNCATE?

  DELETE — построчное удаление с триггерами и логированием, может быть откачено в транзакции. TRUNCATE — быстрая очистка таблицы целиком, обнуляет автоинкремент.

• Что такое ACID?

  Atomicity — транзакция выполняется целиком или не выполняется вовсе. Consistency — БД переходит из одного валидного состояния в другое с соблюдением ограничений. Isolation — параллельные транзакции не мешают друг другу. Durability — зафиксированные данные сохраняются даже после сбоя.

• Что такое Master-Slave репликация?

  Master принимает запись, Slave (replica) — копия для чтения; изменения с Master реплицируются на Slave. Помогает: (1) масштабировать чтение; (2) держать резервную копию — если Master упал, Slave можно повысить (promotion) до Master.

• Что такое PRIMARY KEY и FOREIGN KEY?

  PRIMARY KEY — уникальный идентификатор строки, автоматически NOT NULL и UNIQUE; на таблицу может быть только один. FOREIGN KEY — явная ссылка на PRIMARY KEY (или UNIQUE) другой таблицы, обеспечивающая ссылочную целостность.

• Что такое SEQUENCE?

  Oracle: SEQUENCE — отдельный объект БД, генератор уникальных чисел для auto-increment ID: INSERT VALUES (my_seq.nextval, ...). Postgres аналог — SERIAL или IDENTITY. При этом sequence не гарантирует непрерывность: при откате транзакции или кэшировании значения теряются и образуются пропуски.

• Что такое VIEW?

  Сохранённый SELECT-запрос, к которому можно обращаться как к таблице. Виртуальная таблица — данных в ней нет, при обращении выполняется исходный запрос.

• Что такое индекс? Зачем он нужен?

  Структура данных (обычно B-tree), позволяющая БД быстро находить строки по значениям колонок. Без индекса — Seq Scan (полный обход таблицы). С индексом — поиск за O(log n).

• Что такое нормализация и какие нормальные формы знаешь?

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

• Что такое транзакция?

  Последовательность операций, которая выполняется как единое целое. Либо все операции применяются (COMMIT), либо ни одна (ROLLBACK). Гарантии — ACID.

• Что такое триггер?

  Хранимая процедура, которая автоматически вызывается БД при определённом событии (BEFORE / AFTER INSERT / UPDATE / DELETE). Часто используется для аудита, валидации, поддержки целостности. В банке любят триггеры для аудита.

• В чём разница: Шардирование vs репликация?

  Репликация — копия данных для отказоустойчивости и масштабирования чтения. Шардирование — разделение данных по узлам для масштабирования записи.

• Выберите все верные утверждения про индексы PostgreSQL.

  Индекс ускоряет часть чтений, но обычно замедляет вставки и обновления.