Популярные вопросы по базам данных с собеседований веб-разработчика — ниже. Вопросы разбиты по категориям, что упрощает навигацию. Отдельно разобраны вопросы по разным видам БД (MySQL, Postgres, NoSQL). Теорию СУБД охватить крайне сложно, но ниже — наиболее популярные вопросы, знание которых уже поможет разобраться в основах грамотной работы с БД.

/**/

Общее

---

Как выбрать нужную БД под задачу пользователя ?

Чтобы выбрать оптимальную базу данных для решения задачи пользователя, рекомендуется учитывать следующие критерии:

• Тип данных и структура — реляционные, документные, графовые и т.д.

• Требования к производительности чтения/записи и response time.

• Необходимый уровень масштабируемости — вертикальная или горизонтальная.

• Требования к доступности и отказоустойчивости.

• Наличие транзакций и поддержка ACID.

• Удобство использования — наличие готовых драйверов, сложность интеграции.

• Стоимость лицензирования и поддержки выбранной СУБД.

• Требования к хранению больших объемов данных.

• Необходимость в geo-распределении и мультирегионе.

• Возможности администрирования и мониторинга.

Для небольших проектов часто подходит MySQL или PostgreSQL, для крупных высоконагруженных — Oracle, MS SQL, MongoDB, Cassandra и т.д.

---

Что такое триггер в БД?

Триггер в базах данных — это специальный объект, который срабатывает автоматически при наступлении определенного события в таблице или представлении.

Основные возможности триггеров:

• Срабатывают на INSERT, UPDATE, DELETE операциях с данными.

• Могут выполнять дополнительные действия до или после события.

• Дают возможность реализовать целостность и валидацию данных на уровне БД.

• Позволяют автоматизировать различные операции и процессы.

Примеры использования триггеров:

• Логирование изменений данных.

• Валидация данных перед вставкой или обновлением.

• Автоматический расчет и обновление связанных данных.

• Реализация сложной бизнес-логики в БД.

• Аудит, уведомления пользователей.

Таким образом, триггеры — мощный механизм автоматизации в БД. Их нужно использовать с осторожностью, чтобы не усложнять архитектуру.

---

Что такое нормализация и денормализация БД ?

Нормализация в реляционных базах данных — это процесс организации структуры БД, при котором данные разбиваются на несколько связанных таблиц. Цель нормализации — минимизировать дублирование данных и упростить структуру БД.

Основные формы нормализации:

• 1NF (Первая нормальная форма) — атомарность данных, исключение повторяющихся групп; исключает дублирующиеся строки

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

• 3NF (Третья нормальная форма) — отсутствие транзитивных зависимостей, не ключевые атрибуты зависят только от ключа; устраняет транзитивные зависимости

• BCNF (Нормальная форма Бойса-Кодда) — более строгий вариант 3НФ.

• 4NF (Четвертая нормальная форма) — отсутствие нетривиальных многозначных зависимостей.

• 5NF (Пятая нормальная форма) — отсутствие зависимостей от части категорий (join dependencies).

• 6NF (Шестая нормальная форма) — отсутствие нетривиальных зависимостей.

На практике обычно нормализуют до 3НФ, реже до 4НФ или 5НФ. Избыточная нормализация не всегда оправдана.

Денормализация — обратный процесс, когда в нормализованную БД целенаправленно вносятся избыточные данные. Это делается для повышения производительности за счет уменьшения операций JOIN. Но при этом возрастает риск несогласованности данных.

Поэтому нормализация важна на этапе проектирования, а денормализация применяется по необходимости в критичных к производительности системах. Нужен баланс между структурированностью и оптимальностью.

---

В чем минус избыточной нормализации в БД?

Избыточная нормализация в реляционных базах данных может привести к следующим негативным последствиям:

• Снижение производительности из-за увеличения количества операций JOIN для связывания таблиц. Особенно при сложных запросах.

• Усложнение структуры БД из-за большого числа таблиц. Сложность для понимания и разработки.

• Нарушение целостности данных при частых изменениях из-за распределения данных по многим таблицам.

• Увеличение накладных расходов на поддержание целостности связей между таблицами.

• Сложная реализация некоторой бизнес-логики, которая требует данных из разных таблиц.

• Избыточное дублирование и несогласованность данных в некоторых случаях.

Поэтому полная нормализация до 5-6 НФ не всегда оправдана. Лучше найти баланс и применять частичную нормализацию до 3НФ с элементами денормализации.

---

Что такое шардирование в БД?

Шардирование (sharding) — это метод масштабирования баз данных путём горизонтального разделения данных на несколько частей, называемых шардами (shards).

Основная идея шардирования:

• Данные делятся по определённому признаку на логические части — шарды.

• Каждая шарда хранится на отдельном физическом сервере.

• Запросы приложения маршрутизируются в нужную шарду.

Преимущества шардирования:

• Линейная масштабируемость за счёт добавления новых серверов.

• Ограничение объёма данных на одном сервере.

• Уменьшение конкуренции за ресурсы на сервере.

• Параллельная обработка данных из разных шард.

Недостатки: сложность query-оптимизации, неатомарные транзакции, сложность перемещения данных.

---

Что такое репликация БД?

Репликация в базах данных — это процесс синхронизации данных между главным сервером (мастер) и одним или несколькими резервными серверами (слейвы).

Основные сценарии использования репликации:

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

• Масштабируемость чтения — слейвы используются для распределенных SELECT запросов.

• Резервное копирование — слейв служит резервной копией данных мастера.

• Аналитика — слейв доступен для аналитических и отчетных запросов.

• Географическое распределение — слейвы могут находиться в разных регионах.

Репликация бывает однонаправленная (только с мастера на слейв), двунаправленная и каскадная (через иерархию слейвов).

Настройка репликации усложняет архитектуру, но позволяет масштабировать БД.

---

Что делать, если запрос в БД долго работает?

Если SQL запрос к базе данных начинает работать слишком долго, то можно предпринять следующее:

1. Проанализировать запрос с помощью EXPLAIN — проверить используемые индексы, тип соединений, возможные файлтеры.

2. Внести необходимые изменения в запрос — добавить индексы, перейти к более эффективным JOIN, оптимизировать предикаты.

3. Проверить статистику по таблицам — при необходимости сделать ANALYZE таблиц.

4. Добавить расширенные параметры запроса — LIMIT, OFFSET для ограничения выборки.

5. Разбить запрос на несколько простых, выполнив сложные операции в коде приложения.

6. Увеличить ресурсы БД — оперативную память, количество ядер ЦП, быстродействие дисков.

7. Перенести непроизводительные запросы в хранилище данных, предназначенное для отчётов.

8. В крайнем случае — оптимизировать запрос на уровне SQL путём денормализации или материализованных представлений.

---

Индексы в БД

Что такое индексы? По какому принципу функционируют индексы в БД?

Индексы в базах данных функционируют на основе следующих принципов:

• Индекс представляет собой дополнительную структуру данных, содержащую отсортированный ключ и указатель на строку таблицы.

• При создании индекса для столбца происходит вычисление значения ключа и сортировка записей по этому ключу.

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

• При запросе данных с использованием индекса, сначала находится подходящая запись в индексе, а затем происходит обращение к таблице по полученному указателю.

• Индекс позволяет значительно ускорить операции SELECT, WHERE, ORDER BY, GROUP BY на индексированных столбцах.

• Обновление данных требует одновременного обновления индекса, что замедляет INSERT и UPDATE.

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

---

Для чего нужны индексы в БД?

Индексы в базах данных нужны для ускорения операций чтения записей из таблиц.

Основные причины использовать индексы:

• Быстрый поиск записей по индексированным столбцам, используя деревья поиска.

• Ускорение операций соединения таблиц по индексированным ключам.

• Возможность проверки уникальности значений столбцов при помощи уникальных индексов.

• Ускорение сортировки по индексированным столбцам, так как данные уже отсортированы.

• Ускорение агрегатных функций (MIN, MAX, SUM и т.д.) благодаря хранению агрегированных значений в индексе.

• Возможность покрытия запроса индексом без обращения к таблице.

• Сокращение объема операций ввода-вывода за счет кэширования индексов.

Но индексы также накладывают дополнительные накладные расходы при вставке и изменении данных. Поэтому их нужно создавать с умом.

---

Как устроены составные индексы ?

Составной индекс (composite index) в базах данных строится сразу по нескольким столбцам таблицы.

Особенности работы составных индексов:

• Порядок столбцов в индексе имеет значение. Индекс в первую очередь оптимизирует поиск по первому столбцу.

• При поиске значения используются все столбцы индекса слева направо.

• Индекс может подходить для фильтрации по части столбцов, если они идут слева.

• Сортировка происходит сначала по первому столбцу, потом по второму и т.д.

• Для работы индекса значения в начальных столбцах должны быть селективными.

• Число столбцов в индексе лучше ограничивать 3-5 из соображений производительности.

Таким образом, порядок столбцов в составном индексе критически важен для эффективности его использования.

---

Какие есть основные типы индексов в БД?

В базах данных используются такие виды индексов:

• B-tree — индексы на основе сбалансированных деревьев (B-деревьев)

• Hash-индекс — индекс на основе хеш-таблицы, быстрый поиск по равенству.

• R-дерево — пространственный индекс для геометрических данных.

• Индексы на основе bitmask — устанавливают биты по определенным правилам.

• Плоские файлы — простой индекс в виде отсортированного файла значений.

• Индексы по триграммам — для поиска похожих текстовых значений.

• Полнотекстовые индексы — инвертированные списки слов для поиска в тексте.

• Multi-index — несколько вложенных индексов разных типов.

• Индексы по выражениям — виртуальные индексы, зависящие от логики приложения.

• Кластерные индексы — сортируют данные физически.

Выбор типа индекса зависит от типов данных, запросов и необходимой производительности.

---

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

Основные виды B-Tree индексов:

• Обычный индекс (index) — наиболее распространенный вид, строится по одному или нескольким столбцам таблицы.

• Уникальный индекс (unique index) — гарантирует уникальность значений индексированного столбца, не допускает дубликатов.

• Составной индекс (composite index) — строится сразу по нескольким столбцам.

• Кластерный индекс (clustered index) — физически сортирует записи таблицы по ключам индекса. Бывает только один кластерный индекс.

• Покрывающий индекс (covering index) — содержит все столбцы, фигурирующие в запросе. Запрос выполняется только по индексу.

• Частичный индекс (partial index) — строится только для части строк таблицы по некоторому условию.

• Пространственный индекс (spatial index) — для работы с геоданными, например R-деревья.

Правильный подбор типов индексов позволяет оптимизировать работу СУБД для конкретных запросов.

---

В чем минус добавления избыточного множества индексов в БД?

Избыточное количество индексов в базе данных может привести к следующим негативным последствиям:

• Снижение производительности операций INSERT и UPDATE, так как при каждой модификации данных нужно обновлять множество индексов.

• Увеличение объема данных и нагрузки на I/O подсистему из-за накладных расходов на хранение самих индексов.

• Замедление операций SELECT из-за того, что оптимизатору Query плана приходится анализировать множество вариантов использования индексов.

• Повышенная фрагментация индексов, что снижает эффективность их использования.

• Усложнение обслуживания и настройки СУБД.

Поэтому рекомендуется создавать индексы только на наиболее часто используемых для поиска столбцах, а не индексировать всё подряд. Это поможет оптимизировать производительность БД.

---

Как правильнее сделать вставку в большую таблицу с индексами в БД?

Несколько способов оптимизировать массовую вставку данных в большую таблицу с индексами:

• Отключить индексы перед вставкой и перестроить после:

ALTER TABLE table_name DISABLE KEYS;

-- bulk insert here

ALTER TABLE table_name ENABLE KEYS;

• Выполнить вставку во временную таблицу, затем одним оператором перенести данные в основную таблицу.

• Разбить данные на пакеты и выполнять небольшие транзакционные вставки последовательно.

• Использовать оптимизированные утилиты наподобие pg_bulkload, mysqlimport.

• Отключить журналирование временно для сокращения накладных расходов.

• Увеличить значение параметра innodb_buffer_pool_size.

Главная цель — минимизировать случайные чтения с диска, кешировать данные. Это позволит значительно ускорить массовую вставку данных.

---

Что такое селективность ?

Селективность — это мера того, насколько эффективно индекс позволяет исключать неподходящие строки в таблице при поиске или фильтрации.

Чем выше селективность:

• Тем меньше строк нужно будет просмотреть, используя индекс.

• Тем быстрее будет работать поиск по индексу.

Факторы, влияющие на селективность:

• Кардинальность данных в столбце — чем больше уникальных значений, тем выше селективность.

• Распределение значений — чем равномернее, тем лучше.

• Выбор столбцов для индекса — лучше использовать колонки с высокой селективностью.

• Тип запросов — для аналитических запросов важно строить индексы по столбцам в условиях WHERE и JOIN.

Таким образом, выбор индексов с учетом селективности данных позволяет оптимизировать производительность БД.

---

Какие индексы нужно добавить в таблицу из двух колонок, где поиск осуществляется по обеим колонкам: два отдельных индекса или один составной ?

Если поиск осуществляется по двум столбцам таблицы, то оптимальным решением будет создание одного составного (compound) индекса по этим двум столбцам.

Причины:

• Один составной индекс вместо двух отдельных экономит место на диске и в памяти.

• Запрос с фильтрацией по двум столбцам будет использовать только один индекс.

• Сортировка данных в составном индексе позволит ускорить запросы с ORDER BY.

• Составной индекс в аналитических запросах может заменить использование соединения таблицы.

• Составной индекс эффективен для поиска по части столбцов.

При этом порядок столбцов в индексе важен — лучше ставить наиболее селективный столбец первым.

Таким образом, один составной индекс оптимален для запросов с фильтрацией и сортировкой сразу по нескольким столбцам.

---

Какой вид дерева используется в btree индексах БД? Является ли это дерево бинарным, сбалансированным?

В B-деревьях, используемых как индексы в базах данных, применяется сбалансированное дерево поиска, отличное от классических бинарных деревьев.

Основные свойства B-деревьев:

• Узел может содержать несколько ключей (в отличие от два в бинарном дереве)

• Ключи в узле всегда отсортированы

• Все листья находятся на одном уровне

• Высота сбалансирована путем разделения переполненных узлов

• Поиск аналогичен бинарному дереву, но за счет большей ветвистости высота меньше

• Высота дерева пропорциональна log(n) от числа элементов

Таким образом, балансировка и множественные ключи в узлах позволяют B-деревьям эффективнее масштабировать поиск по сравнению с классическими двоичными деревьями.

---

Что такое кластерные и некластерные индексы в БД?

Индексы в базах данных делятся на кластерные (clustered) и некластерные (non-clustered).

Кластерный индекс:

• Определяет физический порядок хранения данных таблицы на диске.

• Данные таблицы хранятся в том же порядке, что и кластерный индекс.

• В таблице может быть только один кластерный индекс.

Некластерный индекс:

• Является отдельной структурой данных, не влияющей на физическое расположение данных.

• Позволяет быстро находить данные по индексированным столбцам.

• В таблице может быть несколько некластерных индексов.

• Работает быстрее, так как не требует сортировки таблицы.

Таким образом, кластерные индексы упорядочивают данные, а некластерные ускоряют поиск.

---

В чем важное отличие индексов типа primary key от unique?

Основное: В полях с уникальным индексом (unique) может быть значение null , в primary key — не может.

Основные отличия индексов PRIMARY KEY и UNIQUE в базах данных:

• Таблица может иметь только один PRIMARY KEY, но несколько UNIQUE индексов.

• Столбцы в PRIMARY KEY не могут принимать NULL значения, в UNIQUE — могут.

• PRIMARY KEY задаёт идентификатор строки таблицы, UNIQUE — нет.

• По умолчанию PRIMARY KEY создаёт кластеризованный индекс, UNIQUE — некластеризованный.

• PRIMARY KEY автоматически создаёт индекс, для UNIQUE индекс нужно создавать явно.

• PRIMARY KEY гарантирует идентификацию строки и уникальность значения, UNIQUE только уникальность.

• Для оптимизации JOIN лучше использовать PRIMARY KEY.

Таким образом, главное отличие в том, что PRIMARY KEY идентифицирует строку и должен быть уникален, а UNIQUE только устанавливает уникальность значения в столбце.

---

SQL

Влияет ли регистр текста в sql-запросах ?

В большинстве SQL реализаций регистр текста в запросах не имеет значения.

Например, следующие запросы эквивалентны:

SELECT * FROM Users; 

select * from users;

Однако есть несколько исключений:

• Имена объектов, такие как таблицы и столбцы, чувствительны к регистру в некоторых СУБД. Например, в PostgreSQL имена таблиц по умолчанию чувствительны к регистру.

• Строковые литералы чувствительны к регистру:

SELECT 'abc' // не то же самое, что SELECT 'ABC'

• При сравнении строк регистр учитывается, если не использовать оператор ILIKE вместо LIKE.

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

---

Влияет ли регистр в sql-запросах на их кэширование?

Регистр текста в SQL запросах не влияет на кэширование результатов запросов в большинстве СУБД.

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

Поэтому следующие запросы будут эквивалентны с точки зрения кэширования:

SELECT * FROM Users;

select * from users;

Исключения могут быть в некоторых СУБД, где регистр имен объектов (таблиц, столбцов) учитывается.

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

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

---

В чем разница между where и having?

Основное отличие where и having в SQL запросах:

• WHERE ставит условия на строки, возвращаемые из исходных таблиц запроса.

• HAVING ставит условия на строки, возвращаемые из сформированного результата (например, после группировки с GROUP BY).

Например:

SELECT name, SUM(salary) 
FROM employees
GROUP BY name
HAVING SUM(salary) > 10000

Здесь HAVING отфильтрует строки уже после группировки, оставив только те имена, у которых сумма зарплат больше 10000.

WHERE же фильтрует строки из исходной таблицы employees до любых операций над ними.

Обычно HAVING используется вместе с GROUP BY, тогда как WHERE — без него.

Но возможны и более сложные случаи использования.

---

В чем разница между left / right / inner / outer / join ?

Основное отличие между внутренним (INNER) и внешним (OUTER) соединением в SQL:

INNER JOIN:

• Возвращает записи, в которых совпадают условия соединения из обеих таблиц.

• Если в таблице нет совпадений, то запись не возвращается.

LEFT/RIGHT OUTER JOIN:

• Возвращает все записи из левой/правой таблицы, даже если нет совпадений.

• Для несовпадающих записей значения из правой таблицы будут NULL.

FULL OUTER JOIN:

• Объединяет результаты LEFT и RIGHT JOIN, возвращая все строки из обеих таблиц.

• Для отсутствующих совпадений будут выводиться NULL значения.

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

Чем cross join отличается от outer join ?

Основные отличия между CROSS JOIN и OUTER JOIN в SQL:

• CROSS JOIN — это перекрестное соединение, которое производит декартово произведение строк из двух таблиц и возвращает все возможные комбинации.

• OUTER JOIN (LEFT/RIGHT/FULL) возвращает строки из одной таблицы, даже если нет совпадений по условию соединения в другой таблице.

• CROSS JOIN не проверяет условия соединения, просто комбинирует каждую строку первой таблицы с каждой строкой второй.

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

• Результирующая таблица CROSS JOIN обычно содержит бОльшее количество строк, чем в OUTER JOIN.

• CROSS JOIN может привести к картезианскому взрыву при больших таблицах.

Таким образом, главное отличие в том, что CROSS JOIN не использует условий соединения, а OUTER JOIN использует.

Зачем нужны курсоры в БД? Приведи примеры использования курсоров.

Курсоры в базах данных нужны для построчной обработки результатов запроса. Основные примеры использования:

1. Постепенная обработка большого результирующего набора данных.

Например:

sqlCopy codeDECLARE cur CURSOR FOR SELECT * FROM large_table;
OPEN cur;

WHILE row = FETCH cur DO
   -- обработка строки
END WHILE; 

CLOSE cur;

2. Реализация сложной бизнес-логики, проходящей по строкам результата.

3. Итеративное выполнение DML операций для каждой строки результата запроса.

4. Курсоры могут применяться для оптимизации сложных операций вроде рекурсивных CTE.

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

Таким образом, курсоры полезны, когда требуется пройтись по строкам результата SQL запроса.

---

БД Postgres и форки MySQL (сравнение)

В чем ключевые отличия БД Postgres от MySql ?

Ключевое: в Postgres — объектно-реляционная модель: можно создавать структуры из нескольких простых типов (альтернатива json). Основные отличия PostgreSQL от MySQL:

---

В чем разница использования оператора «group by» в mySql и Postgres ?

Вкратце: в mysql нужно указывать все колонки из select, в Postgres — нет:

# ok for mysql, error in pg:
SELECT name, code, AVG(price) 
FROM products
GROUP BY name       # add ,code in PG

Подробнее: Есть несколько отличий в реализации GROUP BY в MySQL и PostgreSQL:

1. По умолчанию в MySQL разрешена неявная сортировка результатов GROUP BY. В PostgreSQL нужно явно использовать ORDER BY.

2. В PostgreSQL запрещены операции агрегации в предложении WHERE. В MySQL это разрешено.

3. При группировке по выражению, а не столбцу, в MySQL и PostgreSQL разный порядок выполнения операций.

4. В MySQL допускается использование неагрегированных столбцов в SELECT, не участвующих в GROUP BY. В PostgreSQL будет ошибка.

5. При сортировке по невыбранному в SELECT столбцу PostgreSQL требует добавить этот столбец в GROUP BY. MySQL этого не требует.

6. По умолчанию в PostgreSQL включен режим ONLY_FULL_GROUP_BY, запрещающий некорректные запросы. В MySQL он отключен.

Таким образом, в целом PostgreSQL придерживается стандартов SQL более строго в использовании GROUP BY.

---

Чем отличается автоинкрементное поле в pg и mysql ?

Есть несколько отличий в реализации автоинкрементных полей в PostgreSQL и MySQL:

• В PostgreSQL автоинкрементное поле обычно объявляется как SERIAL. В MySQL — AUTO_INCREMENT.

• В PostgreSQL значения sequence (Serial) генерируются отдельно от таблицы. В MySQL значение AUTO_INCREMENT генерируется внутри таблицы.

• При удалении записи в PostgreSQL значение sequence не «сгорает». В MySQL удаленное AUTO_INCREMENT значение теряется.

• В PostgreSQL можно определить откуда начинать отсчёт, последовательность может быть отрицательной. MySQL инкремент начинается с 1.

• В MySQL после перезагрузки значение AUTO_INCREMENT сбрасывается в 1. В PostgreSQL значение sequence сохраняется.

• В PostgreSQL можно задать, чтобы столбец заполнялся из нескольких последовательностей. В MySQL одно поле AUTO_INCREMENT связано с одной последовательностью.

Таким образом, в целом механизм последовательностей в PostgreSQL более гибкий и независимый от таблиц.

---

В чем разница форков mysql: Percona, MariaDB?

Вот сравнение основных форков MySQL — Percona и MariaDB:

Основные отличия:

• Percona нацелена в первую очередь на производительность.

• MariaDB хочет быть полноценной заменой MySQL с расширенными возможностями.

Оба форка сохраняют совместимость и преемственность с исходным MySQL. Но дополняют его различными улучшениями и оптимизациями.

---

Какой Exception генерируют sql-запросы в php в разных СУБД?

В PHP запросы к базам данных, выполняемые через расширения как PDO или mysqli, в случае ошибки генерируют исключение PDOException или mysqli_sql_exception соответственно.

---

БД Postgres

Для чего используется sql-оператор «select for update» в Postgres?

Оператор SELECT FOR UPDATE в SQL используется для блокировки выбранных строк на чтение и запись в целях предотвращения конкурентного изменения данных.

Основные применения:

• Блокировка строк перед последующим обновлением:

SELECT * FROM table_name FOR UPDATE;

UPDATE table_name SET column = value WHERE id = 1;

• Блокировка строк на чтение в одной транзакции перед чтением и изменением в другой транзакции.

• Реализация пессимистичной блокировки для предотвращения гонки записи.

• Атомарное получение уникального значения из таблицы, например, для autoincrement id.

FOR UPDATE блокирует строки до конца текущей транзакции или отката (rollback).

Это позволяет избежать ошибок при параллельном изменении данных. Но может привести к замедлению выполнения запросов при частом применении.

---

Перечисли основные виды индексов в БД Postgres

• B-tree — стандартный индекс по одному или нескольким столбцам.

• Hash — хорошо работает на равенство значений, но не на диапазоны.

• Partial — для частичного индексирования, когда нужен индекс только для части таблицы.

• Expression — индекс может строиться по выражению со столбцами.

• Unique — гарантирует уникальность значений индексируемых столбцов.

Специфичные:

• GiST — для столбцов сложных типов, например текста или геометрии.

• GIN — для значений в виде массивов или объектов. Эффективен для jsonb.

• BRIN — хранит обобщенную информацию о блоках таблицы.

• SP-GiST — для разного рода пространственных данных и диапазонов.

Подбор типов индексов важен для производительности и оптимизации запросов.

---

БД MySQL

В чем отличие Myisam от InnoDB ?

Основные отличия движков таблиц MyISAM и InnoDB в MySQL:

Поэтому для транзакционных систем лучше подходит InnoDB, а для хранилищ и аналитики — MyISAM.

---

Перечисли основные виды индексов в БД Mysql

Вот основные типы индексов в MySQL:

• B-tree — наиболее распространенный тип индекса по одному или нескольким столбцам.

• Hash — хорошо работает на точное равенство значений, но не подходит для диапазонов.

• Full text — для полнотекстового поиска по строковым столбцам.

• Spatial — для хранения и поиска геоданных. Работает через специальные пространственные типы.

• Composite — мульти-столбцовый индекс. Столбцы в нем задаются в определенном порядке.

• Unique — гарантирует уникальность значений индексируемых столбцов.

• Clustered — физически хранит данные таблицы в порядке индекса (поддерживает только InnoDB)

• Covering — включает все столбцы запроса. Избавляет от дополнительного чтения данных.

Правильный подбор индексов в MySQL критически важен для производительности запросов.

---

Какая алгоритмическая сложность поиска в btree индексе?

Поиск в B-дереве (B-tree) имеет алгоритмическую сложность O(log n), где n — количество элементов в индексе. Это связано с тем, как устроено само B-дерево:

• Данные хранятся в узлах дерева в сортированном порядке

• Каждый узел содержит не более определенного числа элементов (порядок B дерева)

• При добавлении нового элемента, если узел полон, он разделяется на два дочерних

• Высота дерева зависит от количества элементов как O(log n)

Поиск элемента в B-дереве начинается с корня и сравнением значения определяется в какую ветку спуститься (бинарный поиск). Так как глубина дерева O(log n), то и сложность поиска конкретного значения в индексе будет O(log n).

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

---

Какое время поиска в сбалансированном бинарном дереве в БД? А в хеш-таблицах?

Время поиска элемента в структурах данных:

• В сбалансированном бинарном дереве — O(log n), где n — число элементов в дереве. Например, для 1 000 000 элементов время будет примерно равно 20 операциям сравнения.

• В хеш-таблице — O(1) или O(n) в среднем и худшем случае соответственно. То есть в среднем поиск занимает константное время, не зависящее от размера таблицы. Но при коллизиях хешей возможен линейный поиск.

Преимущества структур:

• Бинарное дерево эффективно при поиске интервалов и сортировке данных.

• Хеш-таблицы обеспечивают очень быстрый поиск в среднем случае за счёт хеширования.

• Хеш-таблицы требуют больше памяти и подвержены коллизиям хеш-функций.

• Деревья требуют дополнительных затрат на балансировку при модификации.

Таким образом, для поиска одного элемента хеш-таблицы обычно быстрее, чем деревья. Но деревья полезны, когда нужна упорядоченность данных.

---

Есть ли транзакционность в Myisam?

Нет, транзакционная поддержка отсутствует в таблицах MyISAM в MySQL.

Основные ограничения MyISAM с точки зрения транзакций:

• Нет поддержки коммитов и откатов (rollback). Операции с данными сразу записываются на диск.

• Невозможно объединять операции в одну транзакцию. Каждая команда выполняется отдельно.

• При сбое во время операции данные могут остаться в несогласованном состоянии.

• Блокировки на чтение/запись ограничивают параллельный доступ и масштабирование.

• Отсутствует изоляция транзакций. Изменения сразу видны другим соединениям.

В отличие от этого, в таблицах InnoDB в MySQL полная поддержка ACID транзакций с коммитами, откатами, изоляцией и консистентностью данных.

Поэтому для задач, требующих надежных транзакций, лучше использовать InnoDB вместо MyISAM.

---

Если в таблице хранится json в поле, то как вернуть сразу массив (вместо json) при выборке данных из него?

Если в поле таблицы в MySQL хранится JSON строка, то при выборке данных её можно преобразовать в массив с помощью функции JSON_PARSE():

Например:

SELECT 
   id, 
   JSON_PARSE(json_field) as json_data
FROM table

Это преобразует значение поля json_field в массив PHP при выборке данных:

$result = $query->fetchAll();

echo $result[0]['json_data'][0]; // доступ к элементам массива

Аналогично, для преобразования массива в JSON используется функция JSON_OBJECT():

$data = ['foo', 'bar'];

$sql = "UPDATE table SET json_field = JSON_OBJECT(:data) WHERE id = 1";

$stmt = db->prepare($sql);
$stmt->execute(['data' => $data]);

Это позволит хранить структурированные данные в виде JSON и удобно преобразовывать при записи и чтении.

еще, в фреймворке Yii это можно/удобнее делать через behaviors

---

Какие есть виды триггеров в MySQL?

Триггеры в MySQL — это специальные объекты, которые срабатывают автоматически при наступлении определенного события, такого как insert, update или delete в таблице.

Основные возможности триггеров MySQL:

• BEFORE — срабатывают до выполнения операции

• AFTER — срабатывают после выполнения операции

• INSERT — срабатывают при добавлении записи

• UPDATE — срабатывают при изменении записи

• DELETE — срабатывают при удалении записи

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

Однако следует использовать их с осторожностью, чтобы не усложнять архитектуру приложения.

---

Какие есть движки MySQL, в чем их различие ?

Основные движки баз данных (storage engines) в MySQL:

• InnoDB — самый популярный движок, поддерживает транзакции и внешние ключи. Хорошо масштабируется.

• MyISAM — простой и быстрый движок, не поддерживает транзакции. Хорош для аналитических рабочих нагрузок.

• Memory — все данные хранятся в ОЗУ, очень быстрый доступ. Данные не сохраняются на диске.

• Archive — оптимизирован для минимального размера данных и низкой скорости записи.

• CSV — позволяет использовать CSV-файлы как таблицы.

• Blackhole — запросы выполняются, но результаты не сохраняются, для репликации.

• Merge — консолидация данных из нескольких идентичных таблиц в одну логическую.

Выбор движка зависит от требований — производительность, транзакции, объем данных и т.д.

---

Расскажи про движок MariaDB в Mysql. Чем он хорош?

MariaDB — это форк (ответвление) популярной СУБД MySQL. Основные особенности движка MariaDB:

• Совместим с MySQL на уровне API, но включает ряд улучшений внутри.

• Разрабатывается и поддерживается компанией MariaDB Corporation после поглощения Sun Microsystems.

• Включает замены популярных движков: Aria вместо MyISAM, XtraDB вместо InnoDB.

• Поддерживает динамические столбцы, вложенные транзакции.

• Улучшенная масштабируемость, оптимизация запросов, параллелизм.

• Поддерживает плагины для расширения функциональности.

• Имеет открытую модель разработки и бесплатную версию СУБД.

• Полностью заменяет оригинальный MySQL, сохраняя совместимость.

Таким образом, MariaDB предоставляет более современную альтернативу MySQL с открытой моделью разработки.

---

Зачем нужно журналирование в InnoDB ?

Журналирование (redo log) в InnoDB нужно для обеспечения атомарности транзакций и восстановления после сбоев. Основные задачи:

• Запись информации о изменениях данных до их физического применения. Это гарантирует атомарность — транзакции либо завершатся полностью, либо все изменения будут отменены.

• Восстановление после сбоев. Если были потеряны какие-то незакоммиченные изменения, сервер использует журнал для повторного применения транзакций и восстановления состояния.

• Фиксация порядка транзакций в многопоточной среде для предотвращения проблем с частично примененными транзакциями.

• Оптимизация записи в оперативную память с последующим сбросом групповых изменений на диск.

Журналирование несколько снижает скорость транзакций, но критически важно для надежности и целостности данных в InnoDB.

---

Как блокируются данные в БД InnoDB и MyISAM во время записи?

InnoDB и MyISAM по-разному реализуют блокировки данных при записи:

• InnoDB использует рядовые блокировки (row-level locking). Блокируется только конкретная обновляемая строка или набор строк запроса. Это позволяет избежать блокировки всей таблицы.

• MyISAM использует табличные блокировки (table-level locking). При записи блокируется вся таблица целиком. Другие запросы будут ждать, пока транзакция не завершится.

Поэтому в InnoDB при параллельном выполнении запросов конкуренция и блокировки возникают значительно реже. Но InnoDB сложнее масштабировать при очень высокой нагрузке на запись.

MyISAM проще реализована и быстрее во многих случаях, но полная блокировка таблицы сильно ограничивает параллелизм запросов.

Таким образом, каждый подход имеет компромиссы между конкурентностью, простотой и производительностью.

---

Как получить список пользователей и список процессов в Mysql?

Для получения списка пользователей в MySQL можно использовать запрос к информационной схеме:

SELECT User, Host FROM mysql.user;

Это выведет всех пользователей БД и хосты, с которых они могут подключаться.

Для получения списка активных процессов в MySQL нужно обратиться к таблице процесслиста:

SELECT * FROM information_schema.processlist;

Это выведет ID процесса, пользователя, хост, текущее состояние (например, ожидание, запрос данных) и другую информацию по running процессам.

Также можно использовать утилиту командной строки:

mysqladmin processlist

Она также выводит список текущих процессов в более удобном виде.

---

Транзакции

Что такое транзакции в БД?

• Транзакции в базах данных — это последовательность операций, которая удовлетворяет свойствам ACID (что это — см. следующий вопрос)

• Транзакции позволяют надежно модифицировать данные в многопользовательских системах, обеспечивают целостность и непротиворечивость данных.

• Транзакции имеют чёткие границы — начало (BEGIN TRANSACTION), фиксация (COMMIT) и отмена (ROLLBACK).

---

В каких СУБД возможны вложенные транзакции?

Возможность использования вложенных транзакций (nested transactions) поддерживается не во всех СУБД. Основные СУБД, поддерживающие вложенные транзакции:

• Oracle

• SQL Server

• PostgreSQL

• DB2

• SQLite (начиная с версии 3.6.19)

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

Это дает дополнительную гибкость при организации сложной бизнес-логики.

Например, в Oracle за счет PL/SQL можно реализовывать вложенные транзакции, управляя их фиксацией (commit) или откатом.

В то же время некоторые популярные СУБД, как MySQL, mariaDb — не имеют возможности вложенных транзакций.

---

Что такое master/slave транзакции?

Master/slave транзакции (мульти-транзакции) — это механизм распределенных транзакций в базах данных, когда транзакция охватывает несколько разных БД.

Основная идея:

• Одна БД выступает в роли master, остальные — slave.

• Транзакция инициируется на master и реплицируется на все slave БД.

• Все БД фиксируют транзакцию (commit) или откатывают (rollback) атомарно.

Преимущества:

• Атомарность транзакций между разными БД.

• Синхронизация данных в распределенной системе.

• Повышение отказоустойчивости за счет репликации.

Недостатки:

• Увеличенная нагрузка из-за синхронной репликации.

• Усложнение логики и повышенная вероятность дедлоков.

Таким образом master/slave транзакции позволяют синхронизировать данные между разными СУБД в рамках единой транзакции.

---

Для чего нужны транзакции в БД ? Что такое ACID ?

Транзакции в базах данных нужны для обеспечения логической целостности данных при конкурентных обновлениях, а также для возможности отката изменений в случае ошибок.

Основные задачи, которые решают транзакции (~ принципы ACID):

• A — Atomicity (Атомарность) — гарантия, что все операции транзакции выполнятся полностью или не выполнятся вообще.

• C — Consistency (Согласованность) — транзакция переводит базу из одного валидного состояния в другое.

• I — Isolation (Изолированность) — изменения внутри транзакции не видны другим до коммита.

• D — Durability (Долговечность) — после успешного коммита результат транзакции гарантированно сохраняется.

• Восстановление — при ошибках транзакция может быть отменена (rollback), что вернёт БД в прежнее состояние.

Таким образом, транзакции критически важны для целостности данных в многопользовательских БД.

---

Большие транзакции : как закоммитить транзакцию, где N+1я итерация возможно некорректная?

Чтобы безопасно закоммитить транзакцию, в которой N+1 итерация потенциально может быть некорректной, можно применить следующий подход:

1. Начать транзакцию

2. Выполнить первые N итераций, предполагая, что они корректные

3. Сделать промежуточный коммит транзакции, чтобы зафиксировать уже выполненные N итераций

4. Выполнить N+1 потенциально некорректную итерацию

5. Если N+1 итерация прошла успешно — сделать полный коммит транзакции

6. Если в N+1 итерации произошла ошибка — откатить транзакцию к промежуточному коммиту.

7. Таким образом мы сохраним результаты первых N итераций, даже если следующая может быть некорректной.

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

---

Как появляются дедлоки в БД и как их устранять?

Дедлоки (взаимные блокировки транзакций) в базах данных могут возникать из-за:

• Одновременного обновления одних и тех же строк разными транзакциями

• Некорректного выбора уровней изоляции транзакций

• Неоптимальной структуры запросов, когда они блокируют друг друга

Чтобы предотвратить и устранить дедлоки, можно:

• Использовать более строгие уровни изоляции транзакций

• Оптимизировать порядок операций и структуру запросов

• Устанавливать таймауты ожидания блокировки

• Выявлять взаимозависимые транзакции и прерывать дедлок

• Перезапускать одну из зависших транзакций

• Разбивать долгие транзакции на более короткие

• Использовать более гранулярные блокировки (рядов, а не таблиц)

Главная цель — оптимизировать структуру транзакций и минимизировать время удержания блокировок данных.

---

Какие есть уровни изоляции транзакций? Расскажи про каждый уровень.

В базах данных существуют различные уровни изоляции транзакций, которые определяют, как одна транзакция взаимодействует с другими транзакциями и какие виды блокировки применяются для обеспечения целостности данных. Вот некоторые из наиболее распространенных уровней изоляции транзакций:

• READ UNCOMMITTED (неподтвержденное чтение): Этот уровень позволяет транзакциям читать данные, которые еще не были подтверждены другими транзакциями. Это может привести к проблемам с целостностью данных, так как изменения, сделанные другими транзакциями, могут быть видны в текущей транзакции.

• READ COMMITTED (подтвержденное чтение): Этот уровень гарантирует, что транзакции будут видеть только данные, которые были подтверждены другими транзакциями. Однако другие транзакции могут изменять данные, что может привести к непоследовательным результатам при повторном чтении.

• REPEATABLE READ (повторяемое чтение): Этот уровень гарантирует, что все чтения в пределах транзакции будут видеть один и тот же набор данных. Другие транзакции не смогут изменять данные, прочитанные текущей транзакцией, до ее завершения.

• SERIALIZABLE (сериализуемость): Этот уровень обеспечивает максимальную изоляцию транзакций. Он гарантирует, что все операции чтения и записи будут выполняться последовательно, как если бы они были выполнены последовательно одной за другой. Это предотвращает любые аномалии, связанные с параллельным доступом к данным.

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

---

Что такое дедлоки и как с ними бороться?

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

Пример:

• Поток 1 блокирует ресурс A и ждет ресурс B.

• Поток 2 блокирует ресурс B и ждет ресурс A.

Оба потока зависли в ожидании и не могут продолжить работу — возник дедлок.

Чтобы избежать дедлоков, нужно:

• Использовать взаимоисключающие блокировки

• Налагать блокировки в фиксированном порядке

• Избегать вложенных блокировок

• Использовать таймауты

• Распознавать дедлоки и принудительно освобождать блокировки

• Перепроектировать алгоритмы для минимизации блокировок

Главная идея — минимизировать время удержания блокировок и избегать ситуаций взаимного ожидания потоков.

---

NoSQL

В чем преимущество nosql БД (зачем они нужны) ?

Основные преимущества NoSQL баз данных по сравнению с реляционными БД:

• Горизонтальная масштабируемость — возможность легко добавлять новые узлы и серверы.

• Большая производительность и доступность за счет распределенной структуры.

• Поддержка больших объемов и высоких нагрузок.

• Более гибкая схема данных, возможность хранить разнотипные данные.

• Высокая скорость разработки за счет простого API по сравнению с SQL.

• Хорошо подходят для хранения семи-структурированных данных (JSON, графы).

• Автоматическое распределение и репликация данных по серверам.

• Встроенные механизмы кэширования данных.

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

Поэтому NoSQL хорошо подходит для быстрых пишущих нагрузок и больших объемов данных, когда скорость и масштабирование важнее строгого соответствия схеме.

---

ЗАДАЧИ ПО БД

Найти моду в таблице (наиболее часто встречающееся значение)

Пример:

Вот SQL запрос для вычисления моды (наиболее часто встречающегося значения) возраста пользователей из таблицы Users:

SELECT 
  FLOOR(DATEDIFF(CURRENT_DATE, birth_date) / 365) AS age,
  COUNT(*) AS freq
FROM Users
GROUP BY age
ORDER BY freq DESC 
LIMIT 1;

Разберём запрос:

• С помощью DATEDIFF вычисляем разницу в днях между текущей датой и датой рождения.

• Делим на 365, чтобы получить приблизительный возраст в годах.

• Округляем с помощью FLOOR до целого числа.

• Группируем по возрасту и подсчитываем число пользователей для каждого возраста.

• Сортируем по убыванию и берём запись с максимальным количеством (первая запись).

Это и будет самый часто встречающийся возраст пользователей, то есть мода.

---

Как грамотно реализовать резервирование билетов в БД в системе с ажиотажным спросом?

При реализации системы бронирования билетов со значительной нагрузкой нужно учитывать следующее:

• Использовать пессимистическую блокировку записей при помощи SELECT ... FOR UPDATE или эквивалентных механизмов. Это предотвратит двойное бронирование.

• Применять короткие транзакции — зарезервировать билет и сразу коммитить. Не оставлять транзакцию открытой.

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

• Масштабировать количество реплик БД для роста пропускной способности обработки транзакций.

• Выносить несущественные операции за транзакцию — логирование, уведомления.

• Использовать тизированные библиотеки вроде Redlock для распределённых блокировок.

• Тестировать систему под высокой нагрузкой заранее. Выявлять и оптимизировать узкие места.

Главная цель — минимизировать время блокировки записей и увеличить пропускную способность системы.

---

Есть таблица с иерархических деревом категорий: Categoty (id, name, parent_id). Какие минусы у данного подхода? Как избавиться от N подзапросов при вычислении предков категории?

Основные минусы хранения деревьев в реляционных БД:

• При получении всей ветки дерева приходится использовать множество соединений (JOIN) с самой таблицей. Это неэффективно при большом количестве уровней.

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

• Плохо масштабируется при большом количестве элементов дерева.

Чтобы оптимизировать получение предков категории, можно:

• Хранить путь от корня к узлу в виде строки разделенных запятыми id. И индексировать его.

• Использовать материализованные представления — таблицы с предвычисленными путями.

• Применить библиотеки вроде Ancestry для эффективной работы с деревьями.

• Заменить реляционную БД на СУБД специально оптимизированную под деревья, например MongoDB, Cassandra.

Идеальным решением будет переход на более подходящую для деревьев модель данных.

---

Как быстрее сделать выборку из таблицы Users (id, name, age, sex) — по возрасту или по полу? Почему?

Выборка по полу (sex) будет быстрее, чем по возрасту (age).

Причины:

• Пол, вероятно, хранится в виде строки или числового значения из небольшого диапазона (M/F, 0/1). По строке или числу индексировать и искать быстрее, чем по большим числам.

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

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

• Для проверки пола достаточно простого сравнения. Для возраста часто требуется сложное вычисление или преобразование.

Чтобы ускорить выборку по возрасту, можно:

• Создать индекс по хеш-значению возраста.

• Разбить возраст на категории и хранить в виде строки (0-17, 18-35 и т.д.).

• Поместить возраст в отдельную таблицу с id и связать через внешний ключ.

---

Примечание от автора

Подготовка к техническому собеседованию на позицию разработчика — ответственный этап для любого специалиста. Я предлагаю вам ознакомиться с моим практическим пособием , которое поможет качественно подготовиться к предстоящим собеседованиям. В нем вы найдете актуальные вопросы и типовые задачи с разбором решений, с которыми часто сталкиваются кандидаты на собеседованиях. Весь материал основан на реальном опыте прохождения технических собеседований за последние годы. Эта книга станет для вас практичным и полезным помощником, позволяющим уверенно пройти собеседование и получить желаемую работу. Рекомендую ознакомиться.