MySQL и MariaDB

01InnoDB: как MySQL хранит данные

InnoDB — движок хранения по умолчанию и единственный, который стоит использовать для данных приложения. Каждая таблица хранится как кластерный индекс: строки физически упорядочены по первичному ключу внутри B-tree. Вторичные индексы хранят индексируемые столбцы плюс первичный ключ и используют его, чтобы найти полную строку. Отсюда два следствия. Короткий, постоянно растущий первичный ключ (bigint) держит каждый индекс маленьким, а вставки дешёвыми, тогда как случайные UUID вызывают разбиение страниц и раздувание. И поиск через вторичный индекс стоит двух проходов, если индекс не покрывает все нужные столбцы. Кроме того, InnoDB ведёт redo log для восстановления после сбоев, undo log для откатов и согласованного чтения и buffer pool, который кэширует страницы в памяти.

• innodb_buffer_pool_size: около 50–70% RAM на выделенном сервере базы данных.
• Каждой таблице нужен явный первичный ключ.
• Используйте utf8mb4 везде; старая кодировка utf8 не умеет хранить эмодзи и некоторые письменности.

02Индексы и EXPLAIN

Индексы MySQL — это B-tree, и правило левого префикса действует: индекс по (status, created_at) помогает WHERE status = 'paid' и WHERE status = 'paid' AND created_at > ..., но не одному created_at. Покрывающий индекс содержит все столбцы, нужные запросу, поэтому MySQL вообще не обращается к таблице; EXPLAIN показывает 'Using index'. Функции над столбцами, например WHERE DATE(created_at) = ..., мешают использованию индекса; переписывайте их как диапазоны. Ведущие wildcard'ы (LIKE '%term') не могут использовать B-tree, поэтому применяйте FULLTEXT или поисковый движок. Используйте EXPLAIN, чтобы увидеть план, и EXPLAIN ANALYZE (MySQL 8), чтобы увидеть реальные тайминги. Следите за столбцом type: const и ref — хорошо, ALL означает полное сканирование таблицы.

1ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
2 
3EXPLAIN ANALYZE
4SELECT id, total_minor
5FROM orders
6WHERE status = 'paid'
7  AND created_at >= '2026-10-01'
8  AND created_at <  '2026-11-01';

03Транзакции и блокировки

Транзакция группирует операторы так, что все они либо выполняются, либо нет (ACID: атомарность, согласованность, изолированность, долговечность). Уровень изоляции InnoDB по умолчанию — REPEATABLE READ: внутри транзакции вы видите согласованный снимок. Обычные SELECT никогда не блокируют писателей благодаря MVCC (многоверсионному управлению конкурентным доступом). Когда нужно прочитать строку, а затем безопасно её обновить, например остаток на складе или баланс кошелька, заблокируйте её через SELECT ... FOR UPDATE или обновите атомарно одним оператором с условием. Держите транзакции короткими: длинные удерживают блокировки, мешают другим и раздувают undo log. Дедлоки случаются, когда две транзакции блокируют строки в противоположном порядке; InnoDB их обнаруживает и откатывает одну из них, поэтому ваш код должен повторять попытку.

1START TRANSACTION;
2 
3SELECT stock FROM variants WHERE id = 42 FOR UPDATE;
4 
5UPDATE variants SET stock = stock - 2
6WHERE id = 42 AND stock >= 2;   -- 0 rows affected = out of stock
7 
8INSERT INTO order_items (order_id, variant_id, qty, unit_price_minor)
9VALUES (1001, 42, 2, 2490);
10 
11COMMIT;

04Пользователи и привилегии

Учётная запись в MySQL — это пользователь плюс хост: 'app'@'localhost' и 'app'@'%' — разные учётные записи. Следуйте принципу наименьших привилегий. Пользователь приложения получает только права на данные (SELECT, INSERT, UPDATE, DELETE) в своей базе. Отдельный пользователь для миграций получает права на схему (CREATE, ALTER, INDEX, DROP), а для бэкапов заводится своя учётная запись только на чтение. Никогда не позволяйте приложениям подключаться как root. Начиная с MySQL 8.4 старый плагин mysql_native_password по умолчанию отключён; по умолчанию используется caching_sha2_password, и он работает с актуальными драйверами. Привяжите сервер к 127.0.0.1 или адресу приватной сети через bind-address и держите порт 3306 закрытым в файрволе. Если соединения идут через сеть, используйте TLS.

1CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'use-a-long-random-secret';
2GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
3 
4CREATE USER 'shop_migrate'@'localhost' IDENTIFIED BY 'another-long-secret';
5GRANT ALL PRIVILEGES ON shop.* TO 'shop_migrate'@'localhost';
6 
7SHOW GRANTS FOR 'shop_app'@'localhost';

05Slow query log

Slow query log записывает каждый оператор, который выполняется дольше long_query_time секунд. Это самый быстрый способ найти то, что реально болит в продакшене, вместо того чтобы гадать. Включите его с порогом примерно от половины секунды до секунды, при желании логируйте на staging-копии запросы, которые не используют индексы, а затем сведите файл в отчёт. mysqldumpslow поставляется вместе с MySQL; pt-query-digest из Percona Toolkit даёт отчёт получше, сгруппированный по отпечатку запроса, и показывает, какие запросы потребляют больше всего суммарного времени. Исправьте первые несколько, обычно индексом или переписанным запросом, и повторите. performance_schema и схема sys дают ту же картину в реальном времени, например sys.statements_with_runtimes_in_95th_percentile.

1# /etc/mysql/mysql.conf.d/mysqld.cnf
2[mysqld]
3slow_query_log      = ON
4slow_query_log_file = /var/log/mysql/slow.log
5long_query_time     = 0.5
6 
7# then summarise:
8# sudo pt-query-digest /var/log/mysql/slow.log | less

06Основы репликации

Репликация копирует изменения с сервера-источника на одну или несколько реплик. Источник записывает каждое изменение в свой бинарный лог (binlog); каждая реплика забирает binlog и воспроизводит его. Используйте построчный формат (по умолчанию) и GTID (глобальные идентификаторы транзакций), которые сильно упрощают failover и перенастройку реплик. По умолчанию репликация асинхронная, поэтому реплика может отставать; чтение с неё может быть слегка устаревшим, что важно для сценариев «прочитай свою запись», например показа заказа сразу после оформления. Реплики полезны для масштабирования чтения, отчётов и как тёплый резерв. Репликация — это не бэкап: DROP TABLE или ошибочный UPDATE реплицируются мгновенно. Начиная с MySQL 8.4 используются термины source и replica, с командами вроде SHOW REPLICA STATUS.

07Бэкапы и MySQL vs MariaDB

Бэкапы бывают двух видов. Логические бэкапы, сделанные через mysqldump или утилиты дампа MySQL Shell, экспортируют SQL или файлы данных; они переносимы между версиями и легко восстанавливаются частично, но медленны для больших баз. Используйте --single-transaction для согласованного снимка InnoDB без блокировок. Физические бэкапы копируют сами файлы данных с помощью Percona XtraBackup для MySQL или mariadb-backup для MariaDB; для больших баз их гораздо быстрее делать и восстанавливать, и они поддерживают инкрементальные бэкапы. Храните binlog'и, чтобы можно было восстановиться на момент времени, держите копии вне сервера и регулярно проверяйте восстановление. MariaDB отделилась от MySQL в 2009 году. В повседневном SQL они всё ещё близки, но разошлись: JSON, аутентификация, репликация и возможности оптимизатора различаются, поэтому относитесь к ним как к разным продуктам и тестируйте перед переходом.

1# consistent logical backup, compressed, off to another location
2mysqldump --single-transaction --routines --triggers --events \
3  -u backup_user -p shop | gzip > shop-$(date +%F).sql.gz
4 
5# restore into a test database to prove the backup works
6gunzip < shop-2026-11-05.sql.gz | mysql -u root -p shop_restore_test

08Память и надёжность: buffer pool, redo log и настройки сброса

Большая часть тюнинга MySQL сводится к нескольким настройкам InnoDB. Buffer pool кэширует страницы данных и индексов; на выделенном сервере базы ему обычно отдают от 50 до 75 процентов RAM, и цель в том, чтобы горячий рабочий набор помещался целиком и чтения редко шли на диск. innodb_dedicated_server = ON автоматически выбирает размеры buffer pool и redo log по памяти машины — хороший вариант по умолчанию для хоста, где работает только MySQL. Redo log записывает изменения до того, как пишутся страницы данных; начиная с MySQL 8.0.30 его размер задаётся одной настройкой, innodb_redo_log_capacity, а слишком маленький журнал под нагрузкой записи вызывает постоянный сброс. Надёжность — это компромисс, который вы выбираете явно. innodb_flush_log_at_trx_commit = 1 и sync_binlog = 1 сбрасывают данные на каждом коммите и переживают отключение питания без потери закоммиченных транзакций; значения 2 и 0 быстрее, но при сбое можно потерять около секунды коммитов. Для всего, что связано с деньгами, оставляйте 1 и 1.

1# /etc/mysql/mysql.conf.d/mysqld.cnf  (dedicated DB host, 16 GB RAM)
2[mysqld]
3innodb_buffer_pool_size        = 10G
4innodb_redo_log_capacity       = 4G
5innodb_flush_log_at_trx_commit = 1     # full durability
6sync_binlog                    = 1
7max_connections                = 300   # each connection also uses memory
8binlog_expire_logs_seconds     = 604800  # keep 7 days for point-in-time recovery
9 
10# check how often reads miss the buffer pool:
11# SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

09Онлайн-изменения схемы: INSTANT, INPLACE и metadata locks

У ALTER TABLE в MySQL 8 три алгоритма. INSTANT меняет только метаданные и завершается за миллисекунды независимо от размера таблицы; начиная с 8.0.29 он умеет добавлять и удалять колонки в любой позиции, а также переименовывать колонки и менять значения по умолчанию. INPLACE перестраивает или изменяет таблицу, не блокируя чтение и запись для многих операций, например добавления вторичного индекса с LOCK=NONE, но всё равно требует времени и I/O, а на репликах изменение потом проигрывается заново и вызывает отставание. COPY перестраивает всю таблицу и блокирует запись. Указывайте алгоритм явно: тогда MySQL откажется, а не будет молча откатываться на что-то медленнее. Скрытая опасность — metadata lock. Каждому ALTER нужна короткая эксклюзивная блокировка метаданных в начале и в конце. Если на таблице открыта длинная транзакция, ALTER ждёт, а каждый новый запрос к этой таблице становится в очередь за ALTER: сайт выглядит зависшим. Задавайте короткий lock_wait_timeout и повторяйте. Для огромных таблиц или рискованных изменений gh-ost или pt-online-schema-change копируют таблицу в фоне небольшими порциями с троттлингом.

1SET SESSION lock_wait_timeout = 5;   -- give up quickly instead of freezing traffic
2 
3ALTER TABLE orders ADD COLUMN gift_note VARCHAR(255) NULL, ALGORITHM=INSTANT;
4 
5ALTER TABLE orders ADD INDEX idx_orders_customer (customer_id, created_at),
6  ALGORITHM=INPLACE, LOCK=NONE;
7 
8-- who is holding or waiting for metadata locks?
9SELECT * FROM sys.schema_table_lock_waits\G

10Внутреннее устройство блокировок и анализ deadlock

InnoDB блокирует записи индекса, а не строки в абстрактном смысле, и это объясняет большинство сюрпризов. Блокирующее чтение или UPDATE блокирует каждую просканированную запись индекса, а не только изменённые, поэтому UPDATE, чей WHERE не может использовать индекс, может заблокировать большую часть таблицы. При REPEATABLE READ InnoDB также берёт gap- и next-key-блокировки на диапазоны, чтобы предотвратить фантомы, а значит, INSERT может ждать диапазон, который просканировала другая транзакция. Практические правила: убедитесь, что UPDATE и DELETE фильтруют по индексу; трогайте строки в одном и том же порядке, например отсортированными по первичному ключу, чтобы две транзакции не могли блокировать в противоположном порядке; держите транзакции короткими. Когда случается deadlock, InnoDB откатывает более дешёвую транзакцию с ошибкой 1213, и приложению нужно просто повторить всю транзакцию. Чтобы разобраться в конкретном случае, читайте раздел LATEST DETECTED DEADLOCK в SHOW ENGINE INNODB STATUS или включите innodb_print_all_deadlocks = ON, чтобы логировать каждый deadlock. Текущие ожидания видны в performance_schema.data_locks и sys.innodb_lock_waits.

1-- who blocks whom right now
2SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age
3FROM sys.innodb_lock_waits;
4 
5-- last deadlock, with both transactions and the locks they held
6SHOW ENGINE INNODB STATUS\G
7 
8-- log every deadlock to the error log
9SET PERSIST innodb_print_all_deadlocks = ON;

11Высокая доступность: параллельная репликация, semi-sync и кластеры

Базовая асинхронная репликация может потерять последние транзакции, если источник упадёт, а реплика может отставать под тяжёлой записью. С этим помогают несколько возможностей. Многопоточная репликация применяет транзакции на реплике параллельно; replica_parallel_workers по умолчанию равен 4 начиная с 8.0.27, а replica_preserve_commit_order сохраняет безопасный порядок коммитов. Полусинхронная репликация заставляет источник ждать, пока хотя бы одна реплика получит каждую транзакцию, прежде чем подтвердить коммит: немного задержки в обмен на то, что подтверждённые записи не теряются при failover. Для автоматического failover MySQL InnoDB Cluster объединяет Group Replication — протокол консенсуса между тремя и более узлами — с MySQL Router, который направляет соединения на текущий primary. Пользователи MariaDB часто выбирают вместо этого Galera Cluster. Что бы вы ни выбрали, мониторьте отставание (SHOW REPLICA STATUS, а лучше heartbeat-таблицу), избегайте огромных одиночных транзакций, которые реплики должны проигрывать последовательно, и отправляйте запросы read-your-own-write на primary.

• Разбивайте большие UPDATE или DELETE на порции по несколько тысяч строк, чтобы реплики не отставали.
• Первичный ключ в каждой таблице обязателен для Group Replication и полезен для построчной репликации.
• Репетируйте failover; кластер, на котором никто не делал переключение, — это непроверенное предположение.