Оптимізація 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 разів. Зв'яжіться з нами для обговорення вашого проєкту. Економія від оптимізації може бути значною за рахунок зниження навантаження на сервер.