Расскажите о случае, когда вам приходилось решать проблему с MySQL: slow query, migration, incident, replication, backup или tuning.

Кейс: диагностика и исправление медленного запроса в MySQL

На production-сервисе с ~5 млн строк в таблице orders появились алерты: p99 latency API-эндпоинта вырос до 8 секунд. Задача — найти и устранить узкое место.

Шаг 1: включение slow query log

-- Временно, без перезапуска сервера
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- логировать запросы > 1с
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Через несколько минут в логе обнаружился запрос:

SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;
-- Query_time: 7.842  Rows_examined: 4983201

Шаг 2: анализ через EXPLAIN

EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20\G

-- type: ALL  (full scan!)
-- rows: 4983201
-- Extra: Using where; Using filesort

Таблица имела только PRIMARY KEY на id. Индекс на user_id был, но оптимизатор выбирал full scan из-за низкой селективности status.

Шаг 3: создание составного индекса

-- Составной индекс: user_id + status + created_at
-- Порядок важен: сначала equality-колонки, потом ORDER BY
ALTER TABLE orders
  ADD INDEX idx_user_status_created (user_id, status, created_at);

-- На живом production использовали pt-online-schema-change:
pt-online-schema-change \
  --alter "ADD INDEX idx_user_status_created (user_id, status, created_at)" \
  D=mydb,t=orders \
  --execute

Шаг 4: проверка результата

EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20\G

-- type: ref
-- key: idx_user_status_created
-- rows: 12
-- Extra: Using index condition
-- (filesort исчез!)

После деплоя p99 упал с 8с до 18мс. Параллельно добавили мониторинг через performance_schema:

SELECT digest_text, count_star, avg_timer_wait/1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

Дополнительные меры

• Настроили innodb_buffer_pool_size = 4G (70% RAM сервера) — hot-данные перестали вытесняться.
• Добавили алерт в Grafana на Slow_queries из SHOW GLOBAL STATUS.
• Ввели code review checklist: каждый новый запрос с WHERE/JOIN требует комментария с EXPLAIN-планом.

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

• ALTER TABLE на большой таблице блокирует её на время операции в старых версиях MySQL — всегда используйте pt-online-schema-change или ALTER ... ALGORITHM=INPLACE, LOCK=NONE.
• Составной индекс работает только если запрос использует prefix: индекс (a, b, c) не поможет запросу только по b или c.
• Добавление слишком многих индексов замедляет INSERT/UPDATE — каждый дополнительный индекс обновляется при каждой записи.
• SELECT * тянет все колонки и мешает использованию covering index — выбирайте только нужные поля.
• Статистика таблицы может устареть после bulk-загрузки данных; запустите ANALYZE TABLE orders вручную.
• Оптимизатор игнорирует индекс при WHERE LOWER(email) = ? на колонке без функционального индекса — добавьте ADD INDEX (( LOWER(email) )).
• slow query log на HDD-сервере с высоким QPS сам становится узким местом — используйте log_output=TABLE или выключайте после диагностики.

• Выдумывать опыт или говорить слишком общими фразами
• Не объяснять свою личную роль в работе с MySQL
• Не показывать результат, метрики или извлечённые уроки

• Может подготовить честный пример использования MySQL
• Показывает свою роль, решения и результат
• Умеет рефлексировать над trade-offs и уроками