Повышение производительности MySQL с помощью индексов и Explain

Профилирование запроса

Включение профилирования - это доступный способ получить точную оценку времени выполнения запроса. Сначала нужно включить профилирование и вызвать show profiles, чтобы получить время выполнения запроса.

Например, у нас есть следующая операция добавления данных. Предположим, что User1 и Gallery1 уже созданы:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');   

Выполнение этого запроса не вызовет проблем. Но рассмотрим следующую команду:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

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

Чтобы получить точное время выполнения этого запроса, можно использовать следующий SQL-код:

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

Результат:

Команда show profiles отображает время выполнения не  только исходного запроса, но и всех остальных. Таким образом, можно точно профилировать запросы.

Оптимизация

Но как их оптимизировать? Для этого можно использовать MySQL- команду explain и улучшить производительность запросов на основе фактической информации.

Explain используется для получения плана выполнения запроса. Того, как MySQL будет выполнять запрос. Эта команда работает с операторами SELECT, DELETE, INSERT, REPLACE и UPDATE. Официальная документация описывает команду explain  следующим образом:

С помощью EXPLAIN можно увидеть, куда следует добавлять индексы в таблице, чтобы оператор выполнялся быстрее. Вы также можете использовать EXPLAIN, чтобы проверить, объединяет ли оптимизатор таблицы в оптимальном порядке.

В качестве примера мы рассмотрим запрос, выполняемый UserManager.php для нахождения пользователя по адресу электронной почты:

SELECT * FROM `homestead`.`users` WHERE email = 'claudio.ribeiro@examplemail.com';

Чтобы использовать команду explain, добавьте ее перед запросом на выборку:

EXPLAIN SELECT * FROM `homestead`.`users` WHERE email = 'claudio.ribeiro@examplemail.com';

Результат работы:

• id: это последовательный идентификатор для каждого из запросов SELECT.
• select_type: тип запроса SELECT. Это поле может принимать различные значения:
  • SIMPLE: простой запрос без подзапросов или объединений
  • PRIMARY: select находится во внешнем запросе;
  • DERIVED: select является частью подзапроса;
  • SUBQUERY: первый select в подзапросе;
  • UNION: select является вторым или последующим оператором объединения.

Полный список значений, которые могут указываться в поле select_type, можно найти здесь.

• table: название таблицы базы данных.
• type: указывается, как MySQL объединяет используемые таблицы. Значение может указывать на отсутствующие индексы и как должен быть переписан запрос. Возможные значения для этого поля:
  • system: таблица имеет ноль или одну строку.
  • const: таблица имеет только одну соответствующую строку, которая проиндексирована. Это самый быстрый тип объединения.
  • eq_ref: все части индекса используются объединением. Используется индекс PRIMARY_KEY  или UNIQUE NOT NULL.
  • ref: из таблицы будут считаны все строки с совпадающим индексом для каждой комбинации строк из предыдущей. Этот тип объединения отображается для индексированных столбцов, сравниваемых с помощью операторов=или<=>.
  • fulltext: объединение использует индекс таблицы FULLTEXT.
  • ref_or_null: это то же самое, что и ref, но также содержит строки со значением NULL.
  • index_merge: объединение использует список индексов для получения результирующего набора. Столбец KEY  будет содержать используемые ключи.
  • unique_subquery: подзапрос IN возвращает только один результат из таблицы и использует первичный ключ.
  • range: индекс используется для поиска подходящих строк в определенном диапазоне.
  • index: сканируется все дерево индексов, чтобы найти соответствующие строки.
  • all: таблица сканируется, чтобы найти подходящие строки для объединения. Это наименее оптимальный тип объединения. Он часто указывает на отсутствие соответствующих индексов в таблице.
• possible_keys: показывает ключи, которые могут быть использованы MySQL для поиска строк в таблице.
• keys: фактический индекс, используемый MySQL. СУБД всегда ищет оптимальный ключ, который можно использовать для запроса. При объединении многих таблиц она может определить другие ключи, которые не перечислены в списке possible_keys,  но являются более оптимальными.
• key_len: указывает длину индекса, который оптимизатор запросов выбрал для использования.
• ref: показывает столбцы или константы, которые сравниваются с индексом, указанным в столбце ключей.
• rows: количество записей, которые были проверены, чтобы произвести вывод. Это важный показатель; чем меньше проверенных записей, тем лучше.
• Extra: содержит дополнительную информацию. Такие значения, как Using filesort или Using temporary в этом столбце, могут указывать на проблемный запрос.

Полную документацию по формату вывода explain можно найти на официальной странице MySQL.

Возвращаясь к нашему запросу. Он имеет тип выборки SIMPLE с типом объединения const. Это наиболее оптимальное сочетание. Но что произойдет при выполнении более сложных запросов?

Например, когда нужно получить все изображения галереи. Или вывести только фотографии, которые содержат слово «cat» в описании. Рассмотрим следующий запрос:

SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

В этом случае у нас будет больше информации для анализа explain:

EXPLAIN SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`users` AS users
LEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

Результат работы запроса:

Основными столбцами, на которые мы должны обратить внимание, являются type
и . Цель заключается в том, чтобы получить лучшее значение в столбце type и как можно меньшее число в столбце rows.

Результат первого запроса index плохой. Это означает, что мы можем оптимизировать запрос.

Таблица Users не используется. Поэтому можно расширить запрос, чтобы убедиться, что мы охватываем пользователей, или удалить часть запроса users. Но это только увеличит сложность и время выполнения.

SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.id
WHERE img.description LIKE '%dog%';

Посмотрим на вывод explain:

У нас все равно осталось значение type ALL. Это один из худших вариантов объединения, но иногда это единственно возможный тип.

Нам нужны все изображения галереи, поэтому следует просмотреть всю таблицу галереи. Индексы подходят для поиска конкретных данных в таблице. Но не для выборки всей информации из таблицы.

Последнее, что мы можем сделать, добавить в поле описания индекс FULLTEXT. Так мы изменим LIKE на match() и повысим производительность. Подробнее о полнотекстовых индексах можно узнать здесь.

Вернемся к функционалу разрабатываемого нами приложения: newest и related. Они применяются в галереях. В них используются следующие запросы:

EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`users` AS u ON u.id = gal.user_id
WHERE u.id = 1
ORDER BY gal.created_at DESC
LIMIT 5;

Приведенный выше код предназначен для related.

EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
ORDER BY gal.created_at DESC
LIMIT 5;

Приведенный выше код предназначен для newest.

На первый взгляд эти запросы быстрые, потому что используют . К сожалению, в нашем приложении эти запросы также используют оператор ORDER BY. Поэтому мы теряем преимущества использования LIMIT.

Работа с может ухудшать производительность. Чтобы проверить это, выполним команду explain.

и

Как мы видим, у нас наихудший тип объединения: ALL для обоих запросов.

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

Рекомендации для решения данной проблемы

• Используйте индексы. В нашем случае created_at - отличный вариант. Таким образом, мы выполняем , и  LIMIT без сканирования и сортировки полного набора результатов.
• Сортировка по столбцу в ведущей таблице. Если ORDER BY указывается после поля из таблицы, которое не является первым в порядке объединения, индекс не может быть использован.
• Не сортируйте по выражениям. Выражения и функции не позволяют использовать индексы по ORDER BY.
• Остерегайтесь большого значения . Большие значения LIMIT приводят к сортировке ORDER BY по большему количеству строк. Это влияет на производительность.

Заключение

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

Дайте знать, что вы думаете по этой теме статьи в комментариях. За комментарии, лайки, отклики, подписки, дизлайки огромное вам спасибо!