Продвинутые концепции MySQL

1. Движки хранения: InnoDB vs MyISAM

• InnoDB:
  • Поддерживает транзакции (ACID-совместимый).
  • Блокировка на уровне строк (лучше для конкурентной записи).
  • Поддерживает внешние ключи (Foreign Keys).
  • Данные хранятся в кластерном индексе (B+Tree).
  • Лучшее восстановление после сбоев.
• MyISAM:
  • Не поддерживает транзакции.
  • Блокировка на уровне таблицы (медленная при конкурентной записи).
  • Не поддерживает внешние ключи.
  • Может быть быстрее для нагрузок с преобладанием чтения и минимальной записью.
  • Поддерживает полнотекстовый поиск (InnoDB также поддерживает его начиная с версии 5.6).

2. Типы индексов

• B-Tree Index: наиболее распространённый тип (в InnoDB используются B+Trees).
  • Структура: они «горизонтально широкие» (высокий коэффициент ветвления, fan-out) и неглубокие. Каждый узел может содержать сотни указателей на дочерние элементы, что минимизирует дисковый ввод-вывод за счёт малой глубины дерева.
  • Производительность: быстрая фильтрация (O(log n)), поскольку пространство поиска уменьшается вдвое (или более) на каждом уровне.
  • Применение: хорош для точных совпадений (=), диапазонных запросов (>, <, BETWEEN) и сортировки (ORDER BY).
• Hash Index: очень быстрый для точных совпадений, но не может использоваться для диапазонных запросов или сортировки.
• Full-text Index: используется для поиска текста в больших столбцах.
• Spatial Index: используется для географических данных (R-деревья).

3. Свойства ACID

• Atomicity (атомарность): «Всё или ничего». Гарантирует, что все операции в рамках транзакции завершены; если какая-либо операция не удалась, вся транзакция откатывается к предыдущему состоянию.
• Consistency (согласованность): данные должны соответствовать всем правилам базы данных (ограничения, триггеры и т.д.). Гарантирует, что транзакция переводит базу данных из одного корректного состояния в другое.
• Isolation (изоляция): каждая транзакция независима. Предотвращает взаимное влияние конкурентных транзакций, гарантируя, что промежуточное состояние одной транзакции невидимо для других.
• Durability (долговечность): зафиксированные данные сохраняются навсегда. После фиксации транзакции её изменения сохраняются даже при сбое системы.

4. Команды управления транзакциями

• START TRANSACTION или BEGIN: обозначает начало блока транзакции.
• COMMIT: сохраняет все изменения, сделанные в текущей транзакции, делая их постоянными и видимыми другим пользователям.
• ROLLBACK: отменяет все изменения, сделанные в текущей транзакции, возвращая базу данных к состоянию до начала транзакции.
• SAVEPOINT name: создаёт точку внутри транзакции, к которой можно вернуться позже, не затрагивая всю транзакцию.
• ROLLBACK TO name: откатывает транзакцию к указанной точке сохранения.
• RELEASE SAVEPOINT name: удаляет точку сохранения из текущей транзакции.

Режим Autocommit

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

1. Использовать START TRANSACTION;
2. Отключить autocommit: SET autocommit = 0;

5. Уровни изоляции транзакций

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

• Read Uncommitted: самый низкий уровень изоляции. Транзакция может читать данные, изменённые другой транзакцией, но ещё не зафиксированные. Это приводит к грязным чтениям (Dirty Reads).
• Read Committed: гарантирует, что транзакция может читать только зафиксированные данные. Предотвращает грязные чтения, но допускает неповторяемые чтения (Non-Repeatable Reads) — если данные изменяются между двумя чтениями в одной транзакции.
• Repeatable Read: гарантирует, что если транзакция прочитала строку, она увидит те же данные при повторном чтении, даже если другая транзакция зафиксировала изменения между ними. Это уровень по умолчанию для InnoDB. Предотвращает грязные и неповторяемые чтения.
• Serializable: самый высокий уровень изоляции. Заставляет транзакции выполняться так, как если бы они шли последовательно (одна за другой). Предотвращает все перечисленные проблемы, включая фантомные чтения (Phantom Reads), но существенно влияет на производительность из-за интенсивной блокировки.

Для более глубокого понимания уровней изоляции ознакомьтесь с этой статьёй: Understanding Database Isolation Levels

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

1. Грязное чтение (Read Uncommitted)

• Сценарий: транзакция A обновляет баланс пользователя со $100 до $200. Транзакция B читает баланс как $200. Затем транзакция A сталкивается с ошибкой и откатывает изменение.
• Проблема: транзакция B теперь имеет «грязные» данные ($200), которые фактически никогда не были зафиксированы в базе данных.
• Решение: используйте Read Committed или выше.

2. Неповторяемое чтение (Read Committed)

• Сценарий: транзакция A читает цену товара — $50. Транзакция B затем обновляет цену до $60 и фиксирует изменение. Транзакция A снова читает цену того же товара и видит $60.
• Проблема: в рамках одной транзакции два одинаковых запроса вернули разные результаты.
• Решение: используйте Repeatable Read или выше.

3. Фантомное чтение (Repeatable Read)

• Сценарий: транзакция A запрашивает всех активных пользователей (находит 10). Транзакция B добавляет нового активного пользователя и фиксирует изменение. Транзакция A повторно запрашивает активных пользователей и находит 11.
• Проблема: появилась «фантомная» строка, которой не было при первом чтении.
• Решение: используйте Serializable (или полагайтесь на Next-Key Locking InnoDB в Repeatable Read, который справляется со многими случаями фантомных чтений).

4. Когда что использовать?

• Read Committed: наиболее распространённый выбор для большинства приложений (по умолчанию в PostgreSQL/Oracle). Обеспечивает хороший баланс между производительностью и согласованностью, предотвращая грязные чтения.
• Repeatable Read: уровень по умолчанию для MySQL (InnoDB). Лучше всего подходит, когда необходимо согласованное представление данных на протяжении всей транзакции (например, формирование сложного отчёта).
• Serializable: используйте только тогда, когда требуется абсолютная целостность данных и конкурентный доступ к одним и тем же строкам невелик (например, критические финансовые переводы).

5.2. Подробные сценарии уровней изоляции

В этих примерах используется таблица accounts с полями id и balance.

1. Грязное чтение (Read Uncommitted)

Возникает, когда транзакция 2 читает данные, которые транзакция 1 изменила, но ещё не зафиксировала.

Шаг	Транзакция 1 (T1)	Транзакция 2 (T2)
1	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2	START TRANSACTION;	START TRANSACTION;
3	UPDATE accounts SET balance = 200 WHERE id = 1;
4		SELECT balance FROM accounts WHERE id = 1; -- Результат: 200
5	ROLLBACK;
6		SELECT balance FROM accounts WHERE id = 1; -- Результат: 100

Анализ: T2 принимала решения на основе $200, но это значение «никогда не существовало», потому что T1 откатилась.

2. Неповторяемое чтение (Read Committed)

Транзакция 1 читает одну и ту же строку дважды и получает разные результаты, потому что транзакция 2 зафиксировала изменение между чтениями.

Шаг	Транзакция 1 (T1)	Транзакция 2 (T2)
1	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2	START TRANSACTION;	START TRANSACTION;
3	SELECT balance FROM accounts WHERE id = 1; -- Результат: 100
4		UPDATE accounts SET balance = 150 WHERE id = 1;
5		COMMIT;
6	SELECT balance FROM accounts WHERE id = 1; -- Результат: 150

Анализ: несмотря на то, что T1 всё ещё находится в той же транзакции, данные изменились. Это нарушает согласованность, если T1 выполняла многоэтапное вычисление.

3. Фантомное чтение (Repeatable Read)

Транзакция 1 выполняет диапазонный запрос дважды и получает разное количество строк, потому что транзакция 2 вставила/удалила строку.

Шаг	Транзакция 1 (T1)	Транзакция 2 (T2)
1	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2	START TRANSACTION;	START TRANSACTION;
3	SELECT COUNT(*) FROM accounts WHERE balance > 100; -- Результат: 5
4		INSERT INTO accounts (id, balance) VALUES (99, 500);
5		COMMIT;
6	SELECT COUNT(*) FROM accounts WHERE balance > 100; -- Результат: 5

Анализ: в MySQL InnoDB (Repeatable Read) результат по-прежнему 5 благодаря MVCC (Multi-Version Concurrency Control). Однако если T1 попытается выполнить UPDATE accounts SET balance = balance WHERE balance > 100, она может «обнаружить» новую строку. Для полного предотвращения этого необходим Serializable или явная блокировка.

---

6. Блокировки и конкурентный доступ

• Пессимистическая блокировка: предполагает, что конфликт произойдёт, и блокирует строку/таблицу перед модификацией.
  • SELECT ... FOR UPDATE (эксклюзивная блокировка / X-Lock): блокирует выбранные строки. Другие транзакции не могут ни модифицировать их, НИ получить какую-либо блокировку (разделяемую или эксклюзивную), пока T1 не завершится.
  • SELECT ... LOCK IN SHARE MODE (разделяемая блокировка / S-Lock): другие транзакции могут читать строку и получать собственные разделяемые блокировки, но НИ ОДНА транзакция не может модифицировать строку, пока все разделяемые блокировки не будут сняты.
  • Когда использовать: FOR UPDATE — для «я точно буду менять эти данные». LOCK IN SHARE MODE — для «мне нужно убедиться, что данные не изменятся, пока я читаю связанные данные».

6.1. Сценарии блокировок

1. Конфликт эксклюзивных блокировок (FOR UPDATE)

Шаг	Транзакция 1 (T1)	Транзакция 2 (T2)
1	BEGIN;	BEGIN;
2	SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
3		SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- ЗАБЛОКИРОВАНО
4	UPDATE accounts SET balance = 300 WHERE id = 1;
5	COMMIT;
6		T2 теперь получает результат и может продолжить.

2. Разделяемая блокировка vs Обновление

Шаг	Транзакция 1 (T1)	Транзакция 2 (T2)
1	BEGIN;	BEGIN;
2	SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
3		UPDATE accounts SET balance = 400 WHERE id = 1; -- ЗАБЛОКИРОВАНО
4	COMMIT;
5		T2 теперь продолжает обновление.

3. Сценарий Deadlock

Шаг	Транзакция 1 (T1)	Транзакция 2 (T2)
1	START TRANSACTION;	START TRANSACTION;
2	UPDATE accounts SET balance = 10 WHERE id = 1;
3		UPDATE accounts SET balance = 20 WHERE id = 2;
4	UPDATE accounts SET balance = 30 WHERE id = 2; -- ОЖИДАНИЕ
5		UPDATE accounts SET balance = 40 WHERE id = 1; -- DEADLOCK!

Результат: MySQL обнаруживает цикл (T1 ждёт T2, T2 ждёт T1) и завершает одну транзакцию (обычно ту, которая выполнила меньше работы) с ошибкой: Deadlock found when trying to get lock; try restarting transaction.

• Оптимистическая блокировка: предполагает, что конфликта не будет; перед обновлением проверяет, изменились ли данные с момента чтения (обычно через столбец версии/временной метки).
• Advisory Locks: блокировки, определяемые приложением (например, GET_LOCK('my_lock_name', 10)), которые не блокируют строки базы данных, но предоставляют способ синхронизации логики между разными процессами.
• Deadlock: ситуация, когда две или более транзакций ожидают друг друга для освобождения блокировок, создавая вечную блокировку. MySQL автоматически обнаруживает взаимоблокировки и откатывает одну из транзакций для разрыва цикла.

7. Практическое управление базой данных

• Selectivity (селективность): отношение уникальных значений в столбце к общему количеству записей. Чем выше селективность, тем эффективнее будет индекс по этому столбцу.
• ANALYZE vs EXPLAIN:
  • EXPLAIN: показывает план выполнения без фактического запуска запроса.
  • ANALYZE TABLE: обновляет статистику таблицы для помощи оптимизатору в принятии лучших решений. EXPLAIN ANALYZE (MySQL 8.0.18+) запускает запрос и предоставляет профилирование в реальном времени.
• CHAR vs VARCHAR: CHAR — фиксированной длины (дополняется пробелами), VARCHAR — переменной длины. CHAR немного быстрее для очень коротких, однородных данных (например, кодов стран), тогда как VARCHAR экономит место для текста переменной длины.
• ALTER TABLE на «живых» таблицах: для больших таблиц (миллионы строк) ALTER TABLE может заблокировать таблицу и вызвать простой. Решения:
  • pt-online-schema-change: инструмент от Percona, который создаёт теневую таблицу, синхронизирует данные через триггеры и подменяет таблицы.
  • gh-ost (GitHub Online Schema Migrations): выполняет миграции схемы без триггеров, читая бинарный лог.
  • Online DDL: начиная с MySQL 5.6+, многие операции ALTER TABLE могут выполняться без блокировки таблицы для записи.
  • Оптимизация пагинации: избегайте LIMIT 1000000, 10, так как MySQL должен прочитать и отбросить 1 млн строк. Используйте курсорную пагинацию (например, WHERE id > :last_id LIMIT 10) для производительности O(log n) на больших наборах данных.

8. Настройка производительности

Принципы CEQS

• Connection Polling: повторное использование существующих подключений к базе данных, снижение накладных расходов и исключение точек отказа из-за открытых подключений.
• EXPLAIN: используйте команду EXPLAIN, чтобы убедиться, что вы правильно составили запрос, и проверить время его выполнения, параметры, правильность выбора соединений и индексов.
• Query Caching: используйте кэширование на уровне приложения и инвалидацию, чтобы не беспокоить таблицу ради известных результатов (давно написанные статьи, которые не будут меняться, категории товаров и теги и т. д.).
• Slow Query Log: используйте эту функцию и добавьте к ней инструмент мониторинга для выявления запросов, выполнение которых занимает слишком много времени. Оптимизируйте эти запросы и добавьте необходимые индексы, если нужно, или используйте FORCE INDEX и ANALYZE TABLE, если оптимизатор не увидел ваши индексы и перешел в режим полнотекстового поиска.

9. MariaDB vs MySQL

MariaDB — это форк MySQL, созданный его первоначальными разработчиками в 2009 году после приобретения MySQL компанией Oracle.

• Философия Open Source: MariaDB полностью открыта (GPL) и обещает оставаться такой. MySQL имеет модель двойного лицензирования: Community-версия (GPL) и коммерческая Enterprise-версия.
• Производительность: MariaDB часто внедряет оптимизации производительности раньше — улучшенные пулы потоков (в community-версии) и продвинутая оптимизация запросов.
• Движки хранения: MariaDB поддерживает больше движков хранения «из коробки» (например, Aria, MyRocks, ColumnStore, Cassandra).
• Функциональность: MariaDB добавляла такие функции, как Window Functions (раньше MySQL), Dynamic Columns и Roles раньше, чем MySQL.
• Совместимость: MariaDB разработана как «drop-in replacement» для MySQL. Например, команда mysql работает с MariaDB, и файлы данных часто совместимы между версиями (хотя это стало сложнее с MySQL 8.0 и MariaDB 10.x).

10. MySQL vs PostgreSQL

MySQL — это реляционная СУБД (RDBMS), тогда как PostgreSQL — объектно-реляционная СУБД (ORDBMS).

• Соответствие стандарту SQL: PostgreSQL строго следует стандартам SQL. MySQL исторически был менее строг, хотя новые версии (8.0+) значительно улучшили совместимость.
• Сложность: PostgreSQL лучше для сложных запросов, продвинутого индексирования (GIN, GiST, BRIN) и массивных наборов данных. MySQL часто предпочитают для более простых, ориентированных на чтение веб-приложений благодаря его исторической скорости и простоте настройки.
• Конкурентный доступ: PostgreSQL использует Multi-Version Concurrency Control (MVCC), что обеспечивает лучшую производительность записи в некоторых сценариях, так как читатели не блокируют писателей и наоборот. InnoDB в MySQL также использует MVCC, но ведёт себя иначе в некоторых случаях высокой конкурентности.
• Типы данных: PostgreSQL нативно поддерживает более широкий набор продвинутых типов данных, включая JSONB (индексированный JSON), Arrays, геометрические типы и пользовательские типы.
• Расширяемость: PostgreSQL высоко расширяем, позволяя разработчикам создавать собственные функции, типы и даже типы индексов без модификации ядра.

11. Стратегия составных индексов

При фильтрации по нескольким столбцам (например, sku, creation_date и поле status с низкой кардинальностью) составной индекс (индекс по нескольким столбцам) обычно является наиболее производительным выбором.

Почему составной индекс лучше отдельных индексов:

1. Один индекс на запрос: по умолчанию MySQL обычно использует только один индекс на таблицу для запроса. Если у вас отдельные индексы по sku, creation_date и status, MySQL должен выбрать наиболее «селективный», отфильтровать результаты, а затем сканировать их на соответствие двум другим условиям.
2. Накладные расходы Index Merge: хотя MySQL иногда может использовать «Index Merge» для объединения нескольких индексов, это значительно менее эффективно, чем сканирование одного составного индекса, поскольку требует пересечения или объединения множеств указателей на строки.
3. Правило левого префикса: составной индекс по (sku, creation_date, status) также может использоваться для запросов, фильтрующих только по sku или по sku и creation_date. Однако он не может быть использован для запросов, фильтрующих только по status.
4. Селективность и порядок:
   • Высокая селективность первой: обычно наиболее селективный столбец (например, sku) ставится первым. Если столбец уникален, индекс становится крайне быстрым для точечных поисков.
   • Равенство перед диапазоном: столбцы, используемые для точных совпадений (=), должны стоять перед столбцами, используемыми для диапазонных фильтров (>, BETWEEN, LIKE 'abc%').
   • Высокая селективность vs Низкая кардинальность для равенства: среди столбцов, используемых для точного совпадения, первым следует ставить столбец с наибольшей селективностью.
   • Пример: для фильтра по sku (уникальный), status_field (значения 1-15) и creation_date (диапазон) идеальный порядок — (sku, status_field, creation_date). Поскольку sku уникален, он сразу сузит поиск до одной строки. Если бы sku не был уникальным, но имел высокую селективность, он всё равно должен был бы стоять перед status_field.
5. Покрывающий индекс (Covering Index): если ваш SELECT запрашивает только эти три столбца (или их подмножество), MySQL может вернуть данные непосредственно из B-дерева индекса, не обращаясь к строкам таблицы (страницам данных). Это называется «покрывающий индекс», и он чрезвычайно быстр.

Почему он производителен, даже если используется только creation_date:

Даже если запрос не фильтрует по ведущему столбцу составного индекса, он всё равно может быть производительным по двум причинам:

1. Index Skip Scan (MySQL 8.0.13+): если ведущий столбец (например, status_field со значениями 1-15) имеет низкую кардинальность, MySQL может «пропускать» элементы индекса. Фактически он выполняет диапазонное сканирование для каждого отдельного значения префиксного столбца. Если мы упорядочим индекс как (status_field, creation_date, sku), запрос, фильтрующий только по creation_date, будет очень быстрым, поскольку MySQL нужно выполнить всего 15 под-сканирований.
2. Full Index Scan vs Full Table Scan: если запрос «покрывается» индексом (все выбираемые столбцы входят в индекс), MySQL может просканировать весь индекс вместо таблицы. Поскольку индекс значительно меньше и с большей вероятностью кэширован в памяти (innodb_buffer_pool), полное сканирование индекса значительно быстрее полного сканирования всех данных таблицы.

Сравнение с отсутствием индекса:

• Без индекса: требуется Full Table Scan — чтение каждой строки с диска. Для таблицы с миллионами строк это катастрофически медленно.
• Отдельные индексы: лучше, чем без индекса, но всё равно требует чтения большего количества данных из таблицы, чем необходимо.
• Составной индекс: минимизирует ввод-вывод, сужая набор результатов до именно тех строк, которые нужны, в рамках самой структуры индекса.