Оптимизация MySQL-запросов (EXPLAIN, Slow Query Log, индексы)

Мы сталкиваемся с базами, где 80% медленных запросов вызваны тремя причинами: отсутствие индексов, неправильное их использование или неоптимальные JOIN. Slow Query Log — первый инструмент, который находил эти проблемы. Он включается двумя строками в my.cnf, а дальше нужно уметь его читать — и знать, что делать с тем, что нашёл. За 5+ лет работы мы проанализировали сотни проектов и гарантируем ускорение запросов до 10 раз. Например, в одном проекте SELECT с JOIN выполнялся 12 секунд — после добавления покрывающего индекса время упало до 0.03 секунды, то есть в 400 раз быстрее. Это сокращение времени ответа снизило нагрузку на сервер и позволило сэкономить значительную сумму на инфраструктуре. Наша команда имеет 5+ лет опыта в оптимизации MySQL и провела аудит более 200 баз данных.

Slow Query Log: настройка и анализ

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit = 1000

Сырой лог неудобен. Для агрегации используйте pt-query-digest из Percona Toolkit — он группирует одинаковые запросы и показывает статистику: количество выполнений, суммарное и среднее время, соотношение rows_examined / rows_sent. Высокое значение rows_examined при малом rows_sent — признак плохого индекса или его отсутствия.

Для онлайн‑мониторинга без файлового лога используйте performance_schema.events_statements_summary_by_digest:

SELECT ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
       COUNT_STAR AS calls,
       ROUND(AVG_TIMER_WAIT/1e12, 4) AS avg_sec,
       ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS avg_rows_examined,
       LEFT(DIGEST_TEXT, 80) AS query
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Как читать EXPLAIN: план выполнения запроса

EXPLAIN FORMAT=JSON
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id;

FORMAT=JSON даёт полный план с cost-estimates. Для быстрого чтения — обычный EXPLAIN. На что смотреть в первую очередь: type колонка — тип доступа к таблице. Иерархия от лучшего к худшему:

ALL на большой таблице в середине JOIN — немедленный красный флаг. Покрывающий индекс в 100 раз эффективнее полного сканирования.

rows — оценка количества строк, которые MySQL собирается просмотреть. Если в плане 3 таблицы с rows=500, 5000, 1000 — потенциально 2.5 млрд комбинаций при nested loop без индексов.

Extra — дополнительные операции:

• Using filesort — сортировка вне индекса, в памяти или на диске
• Using temporary — временная таблица (GROUP BY, DISTINCT без индекса)
• Using index — покрывающий индекс (хорошо)
• Using index condition — index condition pushdown (хорошо)

Мы используем эти метрики для автоматизированного анализа: скрипты извлекают проблемные запросы и предлагают готовые индексы. Свяжитесь с нами, чтобы получить такой анализ для вашей базы.

Какие индексы создавать под конкретные запросы?

Отметим: когда видим type=ALL на orders при JOIN по user_id:

CREATE INDEX idx_orders_user_id ON orders(user_id); 

Порядок колонок в составном индексе критичен. Правило «leftmost prefix»: индекс (status, created_at) используется для WHERE status = ?, для WHERE status = ? AND created_at > ?, но не для WHERE created_at > ? без status.

Покрывающий индекс — запрос получает данные прямо из индекса без обращения к строкам таблицы:

CREATE INDEX idx_orders_covering ON orders(user_id, status, total); 

EXPLAIN покажет Extra: Using index — данные из индекса, без heap lookup.

Распространённые паттерны медленных запросов

LIKE с wildcard в начале. WHERE name LIKE '%pattern%' — индекс не используется, всегда full scan. Решение — FULLTEXT индекс или переход на Elasticsearch для полнотекстового поиска.

Функция от индексированной колонки. WHERE YEAR(created_at) = 2021 — индекс на created_at не работает. Переписать как WHERE created_at >= '2021-01-01' AND created_at < '2022-01-01'.

OR на разные колонки. WHERE status = 'active' OR priority = 'high' — MySQL может использовать index merge, но часто хуже двух отдельных запросов с UNION ALL.

Большой OFFSET в пагинации. LIMIT 20 OFFSET 50000 — MySQL читает 50020 строк и выбрасывает 50000. Решение — keyset pagination: WHERE id > :last_id ORDER BY id LIMIT 20.

Как автоматизировать мониторинг медленных запросов?

Мы рекомендуем настроить сбор slow-логов через Prometheus exporter или использовать плагин для анализа в реальном времени. В нашей практике скрипты на Python анализируют performance_schema и отправляют алерты при превышении порогов (например, avg_sec > 0.1). Это позволяет реагировать до жалоб пользователей.

Процесс оптимизации и что входит в работу

1. Сбор top-медленных запросов через pt-query-digest или performance_schema.
2. EXPLAIN по каждому, поиск type=ALL, Using filesort, Using temporary.
3. Создание индексов через CREATE INDEX (не блокирует в MySQL 5.6+ для InnoDB).
4. Перепроверка планов через EXPLAIN и тестирование на продакшн-данных.
5. Предоставление отчёта с результатами и рекомендациями по мониторингу.

Срок точечной оптимизации 5-10 запросов — 1-2 дня. Системный аудит базы с анализом всех медленных запросов, переработкой индексов и оптимизацией схемы — 2-3 дня. Стоимость рассчитывается после предварительного анализа.

Почему стоит обратиться к нам?

За 5+ лет мы провели аудит более 200 баз данных, ускорив критичные запросы в среднем в 15 раз. Используем проверенные инструменты: Percona Toolkit и официальную документацию MySQL EXPLAIN.

Получите консультацию по оптимизации вашей БД — закажите аудит и ускорьте запросы до 10 раз. Свяжитесь с нами для обсуждения вашего проекта. Экономия от оптимизации может быть значительной за счёт снижения нагрузки на сервер.