Как понять, что проблема связана со схемой, индексом, запросом, настройками, диском или инфраструктурой?

Диагностика деградации производительности MySQL: системный подход

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

Шаг 1 — Установите точку отсчёта

Сначала выясните, когда всё было нормально и что изменилось. Проверьте slow query log и Performance Schema:

-- Включить slow query log (если ещё не включён)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- секунды
SET GLOBAL log_queries_not_using_indexes = 1;

-- Топ-10 самых дорогих запросов за всё время
SELECT digest_text, count_star, avg_timer_wait/1e9 AS avg_sec,
       sum_rows_examined, sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

Шаг 2 — Схема

Ошибки схемы проявляются при росте данных или смене паттернов доступа:

• Тип данных не совпадает с паттерном: VARCHAR(255) для UUID вместо BINARY(16) — увеличивает размер индекса в 2–3 раза.
• Отсутствие или неправильный charset: utf8mb4 vs latin1 — implicit conversion при JOIN уничтожает использование индексов.
• Ненормализованная схема с огромными JSON-полями, которые сканируются WHERE JSON_EXTRACT(...).

-- Проверить коллации — несоответствие ломает использование индексов
SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'your_db'
  AND data_type IN ('varchar', 'char', 'text');

Шаг 3 — Индексы

-- Неиспользуемые индексы (MySQL 8.0+)
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema')
ORDER BY object_name;

-- Анализ конкретного запроса
EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Смотрим: type=ALL → нет индекса, type=index → полный скан индекса, type=ref → хорошо

-- Создать составной индекс по паттерну (equality, then range)
ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status, created_at);

Шаг 4 — Сам запрос

Частые anti-patterns:

• Функция на индексируемой колонке: WHERE YEAR(created_at) = 2024 — индекс не используется.
• Implicit conversion: WHERE user_id = '123' при INT колонке — планировщик приводит типы, иногда со сканом.
• SELECT * в подзапросе, где нужен только COUNT.
• OFFSET в пагинации: LIMIT 20 OFFSET 100000 — сканирует и выбрасывает 100 000 строк.

-- Вместо OFFSET используйте keyset pagination
SELECT id, title FROM articles
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

Шаг 5 — Настройки сервера

-- Главные переменные InnoDB
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- должно быть 70-80% RAM
SHOW VARIABLES LIKE 'innodb_io_capacity';        -- IOPS диска
SHOW VARIABLES LIKE 'innodb_log_file_size';      -- для write-heavy: 1–4 GB

-- Статус буферного пула
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 0.01 — хорошо

Если innodb_buffer_pool_size меньше размера «горячих» данных — будет постоянный physical I/O. Увеличьте до 70–80% доступной RAM.

Шаг 6 — Диск

iostat -xm 2 10          # await > 5 ms для SSD — проблема
iostat -xm 2 10 | awk 'NR>1 && /^[a-z]/{print $1, $9, $10, $16}'
# Device, r_await, w_await, %util

# Размер tablespace и фрагментация

SELECT table_name,
       ROUND(data_length/1024/1024, 1) AS data_mb,
       ROUND(data_free/1024/1024, 1)  AS free_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY data_length DESC
LIMIT 10;
-- data_free большой -> OPTIMIZE TABLE или REBUILD INDEX

Шаг 7 — Инфраструктура

• CPU steal time > 5% на VPS означает конкуренцию с соседями — мигрируйте на dedicated или bare metal.
• Network latency между приложением и БД: даже 1 мс на каждый из 50 запросов в транзакции = +50 мс.
• Репликационный лаг: приложение читает с реплики, которая отстала на 30 с.

# Проверить CPU steal на Linux
top -b -n 1 | head -5
# или
sar -u 1 5 | tail -1  # %steal

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

• EXPLAIN без ANALYZE показывает план, а не факт — строки оценочные. Используйте EXPLAIN ANALYZE (MySQL 8.0.18+) для реального времени выполнения.
• Новый индекс может замедлить запись на таблице с высоким write-throughput — всегда профилируйте INSERT/UPDATE после добавления индекса.
• innodb_buffer_pool_size применяется при рестарте, а не сразу (в MySQL 5.7+ есть динамическое изменение, но с осторожностью).
• OPTIMIZE TABLE создаёт полную копию таблицы и блокирует её — используйте pt-online-schema-change или ALTER TABLE ENGINE=InnoDB через gh-ost.
• Performance Schema сама потребляет CPU и память (5–10%) — включайте только нужные инструменты, не все сразу.
• Медленный slow log на диске может маскировать дисковую проблему: если сам лог пишется на тот же диск, что и данные, iostat покажет завышенные цифры.
• Автоматический ANALYZE TABLE при перезапуске обновляет статистику — план может поменяться без изменения кода.
• Проблема инфраструктуры (CPU steal, сетевой джиттер) часто маскируется под проблему запроса — всегда проверяйте системные метрики параллельно с MySQL.

• Сразу обвинять MySQL, не проверив соседние слои системы
• Чинить симптом без минимального воспроизведения и evidence
• Не учитывать версии, конфигурацию, окружение и recent changes

• Умеет локализовать проблему вокруг MySQL
• Двигается от симптома к гипотезам и проверкам
• Отличает баг инструмента от ошибки использования или окружения