Оптимизация MySQL-запросов: практическое руководство

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

Начните с доказательств. Используйте журнал медленных запросов, Performance Schema, трассировку приложений или инструмент мониторинга, такой как PMM, чтобы найти запросы, которые действительно замедляют работу пользователей. Затем используйте EXPLAIN и, если это безопасно, EXPLAIN ANALYZE, чтобы увидеть, что делает MySQL.

Понимание производительности запросов

Распространенные причины включают:

Отсутствие или неэффективность индексов: Без подходящих индексов MySQL вынужден выполнять полное сканирование таблиц, что крайне неэффективно для больших таблиц.
Плохо написанный SQL: Несаргабельные фильтры, ненужный SELECT *, случайные перекрестные соединения и неэффективные условия соединения могут ухудшить производительность.
Большие наборы данных: Больше данных означает больше страниц для чтения, сортировки, группировки и кэширования.
Аппаратное обеспечение и конфигурация: Неоптимальная конфигурация сервера или недостаточные аппаратные ресурсы также могут играть роль, хотя это руководство фокусируется на оптимизации на уровне запросов.

Сила `EXPLAIN`

EXPLAIN — это первый инструмент, к которому стоит обратиться, когда вы хотите понять, как MySQL планирует выполнение запроса. Для обычного EXPLAIN SELECT MySQL показывает выбранный оптимизатором план без возврата результирующего набора. EXPLAIN ANALYZE выполняет запрос и сообщает фактическое время выполнения, поэтому используйте его осторожно на production-системах.

Как использовать `EXPLAIN`

Для запроса на чтение добавьте EXPLAIN в начало:

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Интерпретация вывода `EXPLAIN`

Вывод EXPLAIN представляет собой таблицу с несколькими важными столбцами:

id: Порядковый номер SELECT в запросе. Более высокие номера обычно выполняются первыми.
select_type: Тип SELECT (например, SIMPLE, PRIMARY, SUBQUERY, DERIVED).
table: Таблица, к которой осуществляется доступ.
partitions: Используемые разделы (если включено секционирование).
type: Тип соединения. Это один из самых полезных столбцов. Стремитесь к const, eq_ref, ref или range, если это позволяет структура запроса. Будьте подозрительны к index и особенно к ALL на больших таблицах.
possible_keys: Показывает, какие индексы MySQL мог бы использовать.
key: Индекс, который MySQL фактически выбрал для использования.
key_len: Длина части индекса, которую, как ожидается, будет использовать MySQL. Меньшее значение не всегда лучше; это зависит от селективности и запроса.
ref: Столбец или константа, сравниваемые с индексом (key).
rows: Оценка количества строк, которые MySQL ожидает проверить.
filtered: Процент строк, отфильтрованных условием таблицы.
Extra: Содержит дополнительную информацию о том, как MySQL выполняет запрос. Ключевые значения, на которые стоит обратить внимание:
- Using where: Указывает, что MySQL применяет условие при обработке строк. Это распространено и не всегда плохо.
- Using index: Означает, что запрос покрывается индексом (все необходимые столбцы находятся в индексе), что хорошо.
- Using temporary: MySQL необходимо создать временную таблицу, часто для операций GROUP BY или ORDER BY. Это может быть медленно.
- Using filesort: MySQL должен выполнить внешнюю сортировку (не используя индекс для упорядочивания). Это часто является признаком неэффективного предложения ORDER BY.

Выявление узких мест с помощью `EXPLAIN`

Рассмотрим несколько распространенных сценариев и то, как EXPLAIN помогает выявить проблемы:

Сценарий 1: Полное сканирование таблицы

Рассмотрим запрос:

SELECT * FROM orders WHERE order_date = '2023-10-26';

Если столбец order_date не индексирован, EXPLAIN может показать:

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Проблема: type: ALL указывает на полное сканирование таблицы. rows: 1000000 показывает, что MySQL должен проверить каждую строку в таблице orders. key: NULL означает, что индекс не использовался.

Решение: Добавьте индекс на столбец order_date:

CREATE INDEX idx_order_date ON orders (order_date);

После добавления индекса повторно выполните EXPLAIN. Вы должны увидеть более селективный тип доступа, такой как ref или range, и оценочное количество строк должно уменьшиться, если фильтр по дате селективен.

Сценарий 2: Неэффективный `ORDER BY` или `GROUP BY`

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;

Если customer_id не индексирован, EXPLAIN может показать:

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | orders | index | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+

Проблема: Using temporary и Using filesort указывают на то, что MySQL выполняет дорогостоящие операции для сортировки и группировки данных. Это часто происходит, потому что ни один индекс не может эффективно удовлетворить требования как группировки, так и упорядочивания.

Решение: Для этого конкретного запроса индекс на (customer_id) может позволить MySQL сканировать строки в порядке группировки. Если реальный запрос сначала фильтрует по дате, статусу или арендатору, лучше подойдет составной индекс, например (tenant_id, status, customer_id).

CREATE INDEX idx_customer_id ON orders (customer_id);

Сценарий 3: Ненужное использование `SELECT *`

Когда вы выбираете все столбцы (*), но нужны только некоторые, вы передаете больше данных и можете помешать использованию покрывающего индекса. Это особенно заметно на широких таблицах с JSON-столбцами, текстовыми BLOB-объектами или множеством nullable-полей.

-- Предположим, есть индекс по 'status'
SELECT * FROM tasks WHERE status = 'pending';

EXPLAIN может показать Using where, но если запрос требует столбцы, не входящие в индекс, используемый для фильтрации, ему все равно потребуется обращаться к данным таблицы.

Решение: Укажите только необходимые столбцы:

SELECT task_id, description FROM tasks WHERE status = 'pending';

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

CREATE INDEX idx_tasks_status_id_description
  ON tasks (status, task_id, description);

Не создавайте покрывающие индексы для каждого запроса. Они ускоряют чтение ценой затрат на хранение и накладных расходов на запись.

Переписывание медленных запросов

Помимо индексации, способ структурирования SQL может изменить объем работы, которую должен выполнить MySQL.

Избегайте коррелированных подзапросов

Коррелированные подзапросы могут выполняться один раз для каждой строки, обрабатываемой внешним запросом. MySQL может оптимизировать некоторые из них, но если EXPLAIN показывает повторяющиеся зависимые поиски, соединение или производная таблица часто оказываются понятнее и быстрее.

Часто неэффективно:

SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'USA'
);

Часто лучше в виде соединения:

SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

Используйте EXPLAIN для обоих вариантов. Соединение не всегда быстрее в любой схеме, но многим командам проще его анализировать и индексировать.

Оптимизация предложений `LIKE`

Начальные подстановочные знаки (%) в предложениях LIKE обычно препятствуют использованию обычного B-дерева для поиска по диапазону.

Неэффективно:

SELECT * FROM products WHERE product_name LIKE '%widget';

Лучше (если возможно):

SELECT * FROM products WHERE product_name LIKE 'widget%';

Если вам нужно сопоставление по типу "содержит", рассмотрите полнотекстовые индексы MySQL для подходящего текстового поиска, n-граммовые подходы для определенных языков или поисковую систему, когда важны релевантность и гибкое сопоставление.

Используйте `UNION ALL` вместо `UNION`, когда это возможно

UNION удаляет дублирующиеся строки, что требует дополнительного шага сортировки и дедупликации. Если вы знаете, что дубликатов нет, или их не нужно удалять, UNION ALL будет быстрее.

Медленно:

SELECT name FROM table1
UNION
SELECT name FROM table2;

Быстро:

SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

Другие советы по оптимизации

Поддерживайте актуальность статистики: Убедитесь, что статистика таблиц актуальна, чтобы оптимизатор запросов мог принимать обоснованные решения. Обычно это обрабатывается автоматически, но может быть обновлено вручную с помощью ANALYZE TABLE.
Конфигурация сервера: Настройка запросов не компенсирует крошечный буферный пул InnoDB или перегруженные диски. В MySQL 8.0 старый кэш запросов удален, поэтому не планируйте новую настройку вокруг query_cache_size.
Регулярный мониторинг: Используйте такие инструменты, как MySQL Enterprise Monitor, Percona Monitoring and Management (PMM) или встроенные представления схемы производительности, чтобы отслеживать медленные запросы и выявлять тенденции.

Практический рабочий процесс настройки

Для production-систем настраивайте, отталкиваясь от медленного запроса:

Захватите точный SQL, связанные значения, количество строк и время выполнения.
Выполните EXPLAIN FORMAT=TREE или EXPLAIN FORMAT=JSON, если ваша версия MySQL это поддерживает.
Проверьте, соответствует ли выбранный индекс шаблону фильтрации и соединения.
Протестируйте переписывание запроса или изменение индекса на реалистичных данных.
Сравните количество проверенных строк, временные таблицы, поведение сортировки и задержку выполнения.

Это удерживает вас от добавления индексов только потому, что запрос "выглядит медленным". Индексы имеют стоимость. Каждая вставка, обновление и удаление должны их поддерживать. Таблица с десятью пересекающимися индексами может стать в целом медленнее, даже если один запрос на чтение улучшится.

Для типичного запроса мультитенантного приложения порядок индексов часто важнее количества индексированных столбцов:

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Полезный индекс может быть таким:

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

Этот индекс начинается с фильтров равенства, затем поддерживает диапазон дат и упорядочивание. Если поставить created_at первым, MySQL может сканировать много арендаторов, прежде чем найдет нужного. Если опустить status, запрос может все еще работать, но проверять много лишних строк.

Следите за несаргабельными фильтрами

Условие является саргабельным, когда MySQL может использовать индекс для поиска соответствующих строк. Обертывание индексированного столбца в функцию часто нарушает это:

-- Сложнее использовать индекс по created_at
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';

Перепишите его как диапазон:

SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at <  '2025-01-16';

Вторая версия позволяет MySQL выполнять поиск по индексу created_at. Та же идея применима к LOWER(email), математическим операциям над числовыми столбцами и неявным преобразованиям типов. Если столбец индексирован, по возможности оставляйте сторону столбца в сравнении чистой.

Будьте осторожны с пагинацией

Пагинация со смещением становится дорогой на глубоких страницах:

SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;

MySQL все равно приходится проходить через предыдущие строки, прежде чем вернуть запрошенную страницу. Для лент, журналов аудита и административных таблиц обычно лучше пагинация по ключам:

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Сочетайте это с индексом, таким как (status, published_at, id). Это немного меняет поведение продукта, поскольку пользователи перемещаются с помощью курсора, а не переходят на страницу 10 000, но это может превратить болезненный запрос в предсказуемый.

Проверяйте на реальных данных

Небольшие staging-базы данных обманчивы. Запрос, который мгновенно выполняется на 20 000 строках, может быть ужасен на 200 миллионах строк, особенно при неравномерном распределении данных. По возможности тестируйте на объеме и кардинальности, близких к production. Если вы не можете скопировать production-данные, хотя бы сгенерируйте данные с аналогичными размерами арендаторов, распределением статусов и диапазонами дат.

Еще одна полезная привычка: сохраняйте старый и новый планы в тикете. Будущий вы будете знать, почему существует индекс.

Лучшая привычка при настройке MySQL — заставлять каждое изменение оправдывать свое место. Захватите медленный запрос, изучите план, измените один запрос или индекс, затем сравните задержку и количество проверенных строк. Чистый план EXPLAIN полезен, но настоящая победа — это снижение задержки в production без создания нового давления на запись или раздувания хранилища.