Перейти к основному содержимому

8. MySQL и базы данных

Как подключиться к базе данных MySQL из PHP?​

Junior

Ответ: Можно использовать расширение mysqli или PDO (PHP Data Objects).

// Пример с PDO
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');

В чём разница между mysqli и PDO?​

Junior

Ответ:

  • mysqli работает только с MySQL, тогда как PDO поддерживает несколько систем управления базами данных (PostgreSQL, SQLite и др.).
  • PDO поддерживает именованные плейсхолдеры в подготовленных выражениях.

В чём разница между WHERE и HAVING?​

Junior

Ответ: WHERE фильтрует строки до агрегации, а HAVING фильтрует после агрегации (обычно совместно с GROUP BY).

Что такое Primary Key?​

Junior

Ответ: Первичный ключ — это столбец (или набор столбцов), который однозначно идентифицирует каждую запись в таблице. Он не может быть null.

В чём разница между CHAR и VARCHAR?​

Junior

Ответ: CHAR имеет фиксированную длину (дополняется пробелами), тогда как VARCHAR имеет переменную длину (использует только необходимое пространство плюс 1–2 байта на длину). CHAR немного быстрее для очень коротких данных одинаковой длины (например, коды стран), а VARCHAR экономит место для текста переменной длины.

Какие основные числовые типы данных есть в MySQL?​

Junior

Ответ:

  • TINYINT: 1 байт (от -128 до 127).
  • SMALLINT: 2 байта (от -32 768 до 32 767).
  • INT: 4 байта (от -2.1 млрд до 2.1 млрд).
  • BIGINT: 8 байт.
  • DECIMAL: Числа с фиксированной запятой для финансовых данных (точная точность).
  • FLOAT/DOUBLE: Числа с плавающей запятой для приблизительных значений.

В чём разница между DATETIME и TIMESTAMP?​

Junior

Ответ:

  • DATETIME: 8 байт. Диапазон: от 1000-01-01 до 9999-13-31. Не зависит от часового пояса.
  • TIMESTAMP: 4 байт. Диапазон: от 1970-01-01 до 2038-01-19. Автоматически конвертируется в UTC и обратно в зависимости от текущего часового пояса.

Что такое типы ENUM и SET?​

Junior

Ответ:

  • ENUM: Строковый объект, который может принимать только одно значение из списка разрешённых.
  • SET: Строковый объект, который может принимать ноль или более значений из списка разрешённых (до 64).

В чём разница между BLOB и TEXT?​

Junior

Ответ: Оба типа хранят большие объёмы данных, но TEXT предназначен для символьных строк (учитывает кодировки и правила сравнения), а BLOB (Binary Large Object) — для бинарных данных (изображения, файлы) и обрабатывается как строка байтов.

Какой порт по умолчанию у MySQL?​

Junior

Ответ: 3306.

В чём разница между CHAR_LENGTH и LENGTH?​

Junior

Ответ: CHAR_LENGTH считает количество символов, а LENGTH — количество байтов.

Что означают % и _ в выражении LIKE?​

Junior

Ответ:

  • % соответствует любой последовательности символов (ноль или более).
  • _ соответствует ровно одному символу.

Какие существуют типы соединений (Join) в MySQL?​

Junior

Ответ:

  • (INNER) JOIN: Возвращает записи, имеющие совпадающие значения в обеих таблицах.
  • LEFT (OUTER) JOIN: Возвращает все записи из левой таблицы и соответствующие записи из правой таблицы.
  • RIGHT (OUTER) JOIN: Возвращает все записи из правой таблицы и соответствующие записи из левой таблицы.
  • CROSS JOIN: Возвращает декартово произведение двух таблиц (каждая строка первой таблицы соединяется с каждой строкой второй).
  • Self Join: Обычное соединение, но таблица соединяется сама с собой.

В чём разница между неявным (Implicit) и явным (Explicit) соединением?​

Junior

Ответ:

  • Неявное соединение: Использует предложение FROM для перечисления нескольких таблиц и WHERE для указания условия соединения (например, SELECT * FROM TableA, TableB WHERE TableA.id = TableB.a_id).
  • Явное соединение: Использует ключевое слово JOIN и предложение ON для указания условия соединения (например, SELECT * FROM TableA JOIN TableB ON TableA.id = TableB.a_id). Явные соединения предпочтительнее для читаемости и предотвращения случайных декартовых произведений.

Какие основные строковые функции есть в MySQL?​

Junior

Ответ:

  • CONCAT(str1, str2, ...): Объединяет две или более строки.
  • LENGTH(str) / CHAR_LENGTH(str): Возвращает длину строки в байтах/символах.
  • LEFT(str, len) / RIGHT(str, len): Возвращает указанное количество символов с начала/конца.
  • SUBSTRING(str, start, len): Извлекает подстроку.
  • UPPER(str) / LOWER(str): Преобразует в верхний/нижний регистр.
  • TRIM(str) / LTRIM(str) / RTRIM(str): Удаляет начальные/конечные пробелы.
  • REPLACE(str, from_str, to_str): Заменяет вхождения подстроки.

Какие основные числовые функции есть в MySQL?​

Junior

Ответ:

  • ABS(n): Возвращает абсолютное значение.
  • CEIL(n) / FLOOR(n): Округляет до ближайшего целого вверх/вниз.
  • ROUND(n, d): Округляет до указанного количества десятичных знаков.
  • TRUNCATE(n, d): Усекает до указанного количества десятичных знаков.
  • RAND(): Возвращает случайное число с плавающей запятой от 0 до 1.
  • SQRT(n): Возвращает квадратный корень.

Какие основные функции даты и времени есть в MySQL?​

Junior

Ответ:

  • NOW() / SYSDATE() / CURRENT_TIMESTAMP(): Возвращает текущую дату и время.
  • CURDATE() / CURRENT_DATE(): Возвращает текущую дату.
  • CURTIME() / CURRENT_TIME(): Возвращает текущее время.
  • UTC_DATE() / UTC_TIME(): Возвращает текущую дату/время по UTC.
  • DATE_ADD(date, INTERVAL expr unit) / DATE_SUB(...): Добавляет/вычитает временные интервалы.
  • DATEDIFF(expr1, expr2): Возвращает разницу в днях между двумя датами.
  • DATE_FORMAT(date, format): Форматирует дату согласно шаблону.
  • EXTRACT(unit FROM date): Извлекает часть даты (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND и т. д.).
  • DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), LAST_DAY(): Возвращает информацию о конкретном дне.
  • MONTH(), YEAR(), QUARTER(), WEEK(): Возвращает части даты.
  • HOUR(), MINUTE(), SECOND(): Возвращает части времени.
  • TIME_TO_SEC(time): Преобразует время в количество секунд с начала суток.

Какие функции управления потоком есть в MySQL?​

Junior

Ответ:

  • IF(expr1, expr2, expr3): Если expr1 истинно, возвращает expr2, иначе expr3.
  • IFNULL(expr1, expr2): Если expr1 не NULL, возвращает expr1, иначе expr2.
  • COALESCE(list): Возвращает первое значение из списка, не равное NULL.
  • CASE WHEN condition THEN result [ELSE result] END: Вычисляет список условий и возвращает соответствующий результат.

В чём разница между WHERE и HAVING?​

Junior

Ответ: WHERE фильтрует строки до агрегации, а HAVING фильтрует после агрегации (обычно совместно с GROUP BY).

В чём разница между предложением WHERE и условием JOIN ... ON?​

Junior

Ответ:

  • JOIN ... ON: Определяет, как таблицы связываются друг с другом. Обрабатывается во время операции соединения.
  • WHERE: Фильтрует результирующие строки после того, как соединение было логически сформировано.
  • Производительность: Для INNER JOIN они часто функционально эквивалентны, так как оптимизатор может перемещать условия между ними. Для OUTER JOIN (LEFT/RIGHT) расположение критично: условие в ON определяет, какие строки соединяются (сохраняя все строки из «внешней» таблицы), тогда как условие в WHERE фильтрует финальный результат (потенциально превращая LEFT JOIN в INNER JOIN, если оно отфильтровывает NULL-значения).

Что такое оператор EXPLAIN и как читать его вывод?​

Middle

Ответ: Оператор EXPLAIN предоставляет информацию о том, как MySQL выполняет запрос. Он показывает, какие индексы используются, как соединяются таблицы и оценочное количество просмотренных строк. Ключевые столбцы включают type (тип соединения, где const и ref — это хорошо, а ALL — полное сканирование таблицы), key (фактически используемый индекс) и Extra (дополнительная информация, такая как Using filesort или Using temporary, которые часто указывают на проблемы с производительностью).

Подробное руководство по EXPLAIN

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

Middle

Ответ: Последовательность операций с базой данных, которые рассматриваются как единая единица работы. Они должны соответствовать свойствам ACID.

Какие основные движки хранения данных есть в MySQL?​

Middle

Ответ:

  • InnoDB: Поддерживает транзакции, блокировку на уровне строк и внешние ключи (используется по умолчанию).
  • MyISAM: Не поддерживает транзакции, использует блокировку на уровне таблиц, быстрее при нагрузке с преобладанием чтения.
  • MEMORY: Хранит данные в оперативной памяти для очень быстрого доступа, но данные теряются при перезапуске сервера.

Что такое представление (View)?​

Middle

Ответ: Виртуальная таблица, основанная на результате SQL-запроса. Она не хранит данные самостоятельно.

Что такое триггер (Trigger)?​

Middle

Ответ: Объект базы данных, который автоматически выполняется в ответ на определённые события (INSERT, UPDATE, DELETE) в конкретной таблице.

Что такое InnoDB?​

Middle

Ответ: Движок хранения данных для MySQL, поддерживающий транзакции, внешние ключи и блокировку на уровне строк. Является движком по умолчанию.

Что такое индекс и зачем он нужен?​

Middle

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

Как хранятся индексы?​

Middle

Ответ: Большинство индексов баз данных (включая InnoDB в MySQL) хранятся в виде B-деревьев (точнее, B+Trees). Они «горизонтально широкие» (высокий коэффициент ветвления) и неглубокие, что минимизирует количество операций дискового ввода-вывода для поиска значения. Такая структура обеспечивает быструю фильтрацию (O(log n)) и эффективное сканирование диапазонов. Правильный порядок столбцов в составных индексах критически важен: как правило, столбцы с условиями равенства и высокой селективностью должны идти первыми, за ними — столбцы с условиями диапазона.

В чём разница между UNION и UNION ALL?​

Middle

Ответ: UNION удаляет дублирующиеся строки из результата, тогда как UNION ALL включает все строки, в том числе дубликаты. Для использования UNION количество и типы столбцов в обоих операторах SELECT должны совпадать.

Как выполнять условные вычисления в запросе SELECT?​

Middle

Ответ: Используйте оператор CASE или функцию IF(). Например, для начисления разных процентов в зависимости от суммы:

SELECT name, balance,
  CASE 
    WHEN balance < 3000 THEN balance * 1.1
    ELSE balance * 1.3
  END AS total
FROM accounts;

В чём разница между IN и BETWEEN?​

Middle

Ответ:

  • IN: Проверяет, соответствует ли значение любому значению из указанного списка или подзапроса.
  • BETWEEN: Проверяет, попадает ли значение в указанный диапазон (включая начальное и конечное значения).

Что такое агрегатные функции в MySQL?​

Middle

Ответ: Функции, которые выполняют вычисления на наборе значений и возвращают одно значение. Основные: COUNT(), SUM(), AVG(), MIN(), MAX().

Какова цель GROUP BY и HAVING?​

Middle

Ответ: GROUP BY группирует строки с одинаковыми значениями в итоговые строки. HAVING используется для фильтрации этих групп по условию (так как WHERE не может использоваться с агрегатными функциями).

Что такое подзапрос?​

Middle

Ответ: Запрос, вложенный в другой запрос (SELECT, INSERT, UPDATE или DELETE). Они могут быть коррелирующими (зависящими от внешнего запроса) или некоррелирующими (независимыми).

Что такое оператор EXISTS?​

Middle

Ответ: Логический оператор, используемый в предложении WHERE для проверки, возвращает ли подзапрос какие-либо строки. Он возвращает true, если подзапрос возвращает одну или более записей. Часто он эффективнее, чем IN, для проверки существования.

В чём разница между ANY и ALL при использовании с подзапросами?​

Middle

Ответ:

  • ANY (или SOME): Возвращает true, если условие сравнения истинно хотя бы для одного значения из результата подзапроса.
  • ALL: Возвращает true только в том случае, если условие сравнения истинно для всех значений из результата подзапроса.

Какие существуют общие ограничения (атрибуты) столбцов?​

Middle

Ответ:

  • NOT NULL: Гарантирует, что столбец не может содержать значение NULL.
  • UNIQUE: Гарантирует, что все значения в столбце различны.
  • PRIMARY KEY: Комбинация NOT NULL и UNIQUE.
  • FOREIGN KEY: Предотвращает действия, которые могут разрушить связи между таблицами.
  • DEFAULT: Устанавливает значение по умолчанию для столбца, если оно не указано.
  • CHECK: Гарантирует, что значения в столбце соответствуют определённому условию.
  • AUTO_INCREMENT: Автоматически генерирует уникальное число для каждой новой строки.

Каковы действия ON DELETE и ON UPDATE для внешних ключей?​

Middle

Ответ:

  • CASCADE: Автоматически удаляет или обновляет строки в дочерней таблице при удалении/обновлении строки в родительской таблице.
  • SET NULL: Устанавливает значение NULL в столбце внешнего ключа в дочерней таблице.
  • RESTRICT / NO ACTION: Отклоняет операцию удаления или обновления в родительской таблице, если существуют связанные строки в дочерней таблице.

Для чего используется оператор CONSTRAINT?​

Middle

Ответ: Он используется для определения и именования ограничений (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK) таблицы. Именование ограничений позволяет легко удалять или изменять их в дальнейшем, используя эти имена.

В чём разница между LIKE и REGEXP?​

Middle

Ответ:

  • LIKE: использует простые подстановочные знаки (% для любой строки, _ для любого одиночного символа).
  • REGEXP: использует мощные регулярные выражения (^, $, ., [ ], | и т. д.) для сложного сопоставления с шаблоном.

Как можно изменить структуру существующей таблицы?​

Middle

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

В чём разница между MariaDB и MySQL?​

Middle

Ответ: MariaDB — это форк MySQL с открытым исходным кодом, созданный оригинальными разработчиками после приобретения MySQL компанией Oracle. Сохраняя высокую совместимость, MariaDB предлагает больше движков хранения, улучшения производительности и остаётся полностью открытой (GPL), тогда как MySQL имеет как community-, так и enterprise-версию. Сравнение MariaDB и MySQL

Что такое шардирование и партиционирование?​

Senior

Ответ:

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

Что такое свойства ACID?​

Senior

Ответ:

  • Атомарность (Atomicity): Всё или ничего (либо все операции выполняются успешно, либо ни одна).
  • Согласованность (Consistency): Транзакции должны переводить базу данных из одного допустимого состояния в другое.
  • Изолированность (Isolation): Транзакции выполняются независимо друг от друга.
  • Долговечность (Durability): Зафиксированные данные сохраняются навсегда, даже при сбое системы.

В чём разница между кластеризованным и некластеризованным индексом?​

Senior

Ответ:

  • Кластеризованный индекс: Определяет физический порядок данных в таблице. В таблице может быть только один (обычно это первичный ключ).
  • Некластеризованный индекс: Отдельная структура, содержащая индексированные значения и указатели на строки данных.

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

Senior

Ответ: Стандартные уровни изоляции: Read Uncommitted, Read Committed, Repeatable Read и Serializable. Они определяют баланс между согласованностью и производительностью при параллельных транзакциях. Подробное руководство по уровням изоляции

В чём разница между Read Committed и «грязным чтением» (Dirty Read)?​

Senior

Ответ: «Грязное чтение» (Dirty Read) возникает на уровне Read Uncommitted, когда транзакция видит незафиксированные данные другой транзакции. Read Committed предотвращает это, гарантируя, что транзакция видит только уже зафиксированные данные. Подробное руководство по уровням изоляции

Приведите примеры уровней изоляции транзакций и проблем, которые они решают.​

Senior

Ответ:

  • Read Uncommitted: Чтение баланса банковского счёта, который обновляется, но ещё не зафиксирован (грязное чтение).
  • Read Committed: Отчёт дважды читает одни и те же данные и получает разные результаты, потому что другая транзакция зафиксировала обновление между чтениями (неповторяемое чтение).
  • Repeatable Read: Обеспечение согласованного снимка данных на протяжении долгой транзакции, даже если другие транзакции фиксируют изменения.
  • Serializable: Финансовые системы, где требуется абсолютная корректность, предотвращение всех проблем параллелизма, включая фантомное чтение, ценой производительности. Подробное руководство по уровням изоляции

В чём разница между MySQL и PostgreSQL?​

Senior

Ответ: MySQL известен своей скоростью и простотой использования в веб-разработке, тогда как PostgreSQL — это объектно-реляционная база данных, известная своими расширенными возможностями, строгим соответствием стандарту SQL и лучшей обработкой сложных запросов и высоконагруженных параллельных сценариев. Сравнение MySQL и PostgreSQL

Есть фильтр по 3 столбцам одной таблицы: sku (уникальный), creation_date и integer_field (значения 1, 2 или 3). Как проиндексировать для максимальной производительности?​

Senior

Ответ: Создать составной индекс, включающий все три поля. Для максимальной производительности порядок должен быть: sku (наибольшая селективность), затем integer_field (точные совпадения), и наконец creation_date (фильтр по диапазону). Такой порядок обеспечивает немедленное сужение результатов по самому селективному фильтру, а фильтр по диапазону идёт последним, чтобы предшествующие части индекса использовались полностью. Стратегия составных индексов

У вас есть такой SQL-запрос. Что в нём не так и как его улучшить?​

SELECT * FROM table ORDER BY id LIMIT :limit OFFSET :offset

  • id — это Primary Key, AutoIncrement, с индексом.
Senior

Ответ:

Использование OFFSET для пагинации неэффективно для больших объёмов данных, поскольку MySQL должен просканировать и отбросить все строки до смещения. Например, OFFSET 1000000 требует прочитать 1 миллион строк, прежде чем вернуть запрошенные, что ведёт к высокой нагрузке на ввод-вывод и процессор.

Оптимизация (курсорная пагинация):

  • Начальный запрос: SELECT * FROM table ORDER BY id LIMIT :limit
  • Последующие запросы: Берём последний ID с текущей страницы (например, :last_id) и используем его для пропуска предыдущих строк: SELECT * FROM table WHERE id > :last_id ORDER BY id LIMIT :limit

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

У вас есть такие SQL-запросы, какие индексы вы бы использовали?​

  1. SELECT * FROM transactions WHERE account_id = :account_id AND transaction_type = :transaction_type AND created_at BETWEEN :start AND :end
  2. SELECT * FROM transactions WHERE account_id = :account_id AND transaction_type = :transaction_type ORDER BY created_at
  • account_id: bigint, ~100 000 вариантов
  • transaction_type: int, 15 вариантов
  • created_at: DATETIME, DEFAULT NOW()
Senior

Ответ:

Использовать составной индекс: idx (account_id, transaction_type).

Обоснование:

  • Лучше сначала фильтровать по account_id, чтобы значительно сузить область поиска (высокая селективность).
  • Затем фильтровать по transaction_type.
  • Во втором запросе created_at используется для сортировки. Хотя добавление его в индекс может избежать filesort, это не всегда целесообразно, если столбец не сильно сужает выборку в большинстве запросов, или если отфильтрованный набор данных достаточно мал для эффективной сортировки после выборки.

У вас есть такой код, что в нём не так?​

public function addBalance(float $sum, User $user) {
    $balance = $this->repo->getUserBalance($user->getId());
    $balance += $sum;
    $this->repo->updateUserBalance($balance, $user->getId());
}
// В getUserBalance: SELECT balance FROM Users WHERE id = :id
// В updateUserBalance: UPDATE Users SET balance = :balance WHERE id = :id
Senior

Ответ:

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

Подробное объяснение:

  • На самом слабом уровне изоляции (Read Uncommitted) может произойти грязное чтение (чтение незафиксированных данных).
  • Даже при уровнях изоляции по умолчанию параллельные циклы «чтение — изменение — запись» приводят к потере данных.
  • Решение: Использовать SELECT ... FOR UPDATE в MySQL. Это блокирует конкретную строку, заставляя другие транзакции ждать освобождения блокировки (после фиксации UPDATE).

Правильная реализация:

$this->db->transaction(function() use ($sum, $user) {
    // SELECT balance FROM Users WHERE id = :id FOR UPDATE
    $balance = $this->repo->getUserBalanceForUpdate($user->getId());
    $balance += $sum;
    $this->repo->updateUserBalance($balance, $user->getId());
});

Можете привести конкретные SQL-примеры поведения различных уровней изоляции и блокировок в параллельной среде?​

Senior

Ответ:

Понимание точного поведения уровней изоляции и блокировок критически важно для отладки состояний гонки. Пошаговые сценарии (включая временные линии T1/T2) для грязного чтения, неповторяемого чтения и фантомов, а также конкретные примеры эксклюзивных (FOR UPDATE) и разделяемых (LOCK IN SHARE MODE) блокировок и взаимных блокировок (Deadlocks) представлены в руководстве. Подробные сценарии изоляции и блокировок MySQL

Как обеспечить идемпотентность при вставке записей (например, в консьюмере брокера сообщений)?​

Senior

Ответ: Используйте Unique Constraints на соответствующих бизнес-ключах (например, (order_id, user_id)). При повторной доставке сообщения (гарантия "at-least-once") база данных заблокирует вторую вставку. Приложение должно перехватывать ошибку Duplicate Key и обрабатывать её как успех или известное состояние, чтобы обеспечить корректный результат для клиента.

Как управлять лимитами (например, максимальным количеством участников) в условиях высокой конкуренции?​

Senior

Ответ:

  1. Пессимистическая блокировка: SELECT * FROM meetups WHERE id = 1 FOR UPDATE; для сериализации всех регистраций на этот конкретный митап.
  2. Денормализация + CHECK Constraint: Добавьте колонку booked_seats и ограничение CHECK (booked_seats <= total_seats). Обновляйте счётчик в той же транзакции, что и вставку посетителя. Это позволяет БД самой контролировать соблюдение лимита. Подробное руководство по конкурентности и лимитам в SQL

Что такое xmin и xmax в контексте строк базы данных (например, в PostgreSQL)?​

Senior

Ответ: Это скрытые системные колонки, используемые для реализации MVCC (Multi-Version Concurrency Control).

  • PostgreSQL:
    • xmin: хранит ID транзакции, которая вставила строку.
    • xmax: хранит ID транзакции, которая удалила или обновила строку.
  • MySQL (InnoDB):
    • DB_TRX_ID: аналогично xmin, хранит ID транзакции, которая создала версию строки.
    • DB_ROLL_PTR: указатель на предыдущую версию в Undo Log (используется для восстановления старых версий).
    • DB_ROW_ID: скрытый ID строки (если нет первичного ключа). Понимание этих колонок помогает объяснить, как разные транзакции «видят» разные версии данных одновременно без блокировки друг друга.

Когда следует использовать блокировки Redis вместо встроенных возможностей SQL?​

Senior

Ответ: Используйте Redis для Rate Limiting (ограничения частоты запросов), Circuit Breakers (предохранителей) или синхронизации задач между узлами (например, запуск Cron-задачи один раз). Избегайте использования Redis для обеспечения консистентности данных внутри базы данных. Функции SQL (уникальные индексы, ограничения, блокировки строк) более надёжны, лучше справляются со сбоями (нет проблем с TTL) и не создают лишних точек отказа или накладных расходов на уровне данных.