Какая модель хранения, индексации и выполнения запросов у MySQL влияет на архитектуру приложения?

Модель хранения: InnoDB и кластерный индекс

InnoDB хранит данные в B+Tree кластерном индексе (clustered index), где листовые узлы содержат сами строки данных, упорядоченные по первичному ключу. Это означает:

• Чтение по PK — один traversal по дереву, минимальные I/O.
• Secondary indexes хранят значение PK, а не физическое смещение строки; поиск по secondary index требует двух traversal (сначала secondary, потом clustered).
• Случайные вставки в середину диапазона PK вызывают page splits — фрагментацию. UUID v4 как PK провоцирует постоянные splits; UUID v7 или монотонный ID (BIGINT AUTO_INCREMENT) предпочтительны.

Buffer Pool и I/O

innodb_buffer_pool_size — главный параметр производительности. InnoDB кэширует страницы данных и индексов в buffer pool (рекомендуется 70–80% доступной RAM). При cache miss происходит обращение к диску (random I/O для InnoDB крайне дорого на HDD, приемлемо на NVMe SSD).

-- Текущий размер buffer pool и hit rate
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT
  FORMAT(Innodb_buffer_pool_read_requests / 
         (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100, 2)
    AS buffer_pool_hit_rate_pct
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr
CROSS JOIN (
  SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r;
-- Цель: > 99%

MVCC и транзакционная изоляция

InnoDB реализует MVCC (Multi-Version Concurrency Control) через undo log: каждая строка хранит скрытые поля DB_TRX_ID и DB_ROLL_PTR, указывающие на предыдущие версии. Читатели видят snapshot на момент начала транзакции (при REPEATABLE READ) и не блокируют писателей.

-- Проверить текущий уровень изоляции
SELECT @@transaction_isolation;

-- Установить для сессии
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Долгие транзакции накапливают undo log — мониторинг
SELECT trx_id, trx_started, trx_rows_modified, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started
LIMIT 10;

Cost-based optimizer и статистика

MySQL использует cost-based optimizer (CBO): оценивает стоимость альтернативных планов на основе статистики (кардинальность индексов, распределение данных) и выбирает наименьшую. Статистика хранится в mysql.innodb_index_stats и mysql.innodb_table_stats.

-- Обновить статистику
ANALYZE TABLE orders;

-- Просмотр статистики индексов
SELECT * FROM mysql.innodb_index_stats
WHERE table_name = 'orders';

-- Принудить использование конкретного индекса
SELECT * FROM orders FORCE INDEX (idx_status_created)
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Влияние на архитектуру приложения

• Выбор PK — AUTO_INCREMENT BIGINT минимизирует фрагментацию; UUID как PK требует дополнительной настройки или перехода на UUID v7.
• Индексная стратегия — covering index (INDEX(a, b, c) при запросе по a, b, возврате c) исключает второй traversal; лишние индексы замедляют INSERT/UPDATE.
• Пул соединений — каждое соединение потребляет память (~1 MB для thread stack); используйте PgBouncer-аналог (ProxySQL) при большом числе параллельных клиентов.
• Миграции без простоя — ALTER TABLE в MySQL 5.7 блокирует таблицу для некоторых операций; в 8.0 многие DDL выполняются онлайн, но нужно проверять ALGORITHM=INPLACE, LOCK=NONE.

Подводные камни

• UUID v4 как PK — случайный порядок вставок вызывает постоянные page splits и рост индексной фрагментации; при миллионах строк производительность вставки деградирует.
• N+1 запросы и secondary index lookup — ORM, генерирующий N+1, на MySQL выполняет N×2 traversal (secondary + clustered); используйте JOIN или eager loading.
• Устаревшая статистика optimizer — после массовой загрузки данных статистика не обновляется автоматически; без ANALYZE TABLE optimizer может выбирать плохой план.
• Long-running транзакции и undo log — забытая открытая транзакция удерживает undo log для всех последующих версий строк; приводит к росту tablespace и деградации производительности.
• Implicit commit в DDL — CREATE TABLE, ALTER TABLE, DROP INDEX автоматически коммитят текущую транзакцию; нельзя откатить DDL в рамках бизнес-транзакции.
• Нет частичных индексов — MySQL не поддерживает WHERE в определении индекса (в отличие от PostgreSQL); для фильтрации по sparse-колонке нужно использовать composite index или generated column.
• REPEATABLE READ и phantom reads — next-key locks предотвращают фантомные чтения, но расширяют диапазон блокировок; высокий параллелизм записи может привести к deadlock-шторму.
• Игнорирование sql_mode — без STRICT_TRANS_TABLES MySQL может молча усекать строки или вставлять дефолтные значения вместо ошибки; приложение думает, что данные сохранены корректно.

• Знать термины MySQL, но не понимать связи между абстракциями
• Объяснять поведение через отдельные примеры вместо причинной модели
• Не связывать mental model с диагностикой ошибок

• Понимает ключевые абстракции MySQL
• Может предсказывать поведение системы через mental model
• Связывает модель с debugging и production decisions