Повышение производительности 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 iWHERE i.description LIKE '%street%';

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

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

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

Результат:

Query_Id Продолжительность Запрос
1 0.00016950 SHOW WARNINGS
2 0.00039200 SELECT * FROM homestead. images AS i nWHERE i.description LIKE ’%street%’nLIMIT 0, 1000
3 0.00037600 SHOW KEYS FROM homestead. images
4 0.00034625 SHOW DATABASES LIKE ’homestead
5 0.00027600 SHOW TABLES FROM homestead LIKE ’images’
6 0.00024950 SELECT * FROM homestead. images WHERE 0=1
7 0.00104300 SHOW FULL COLUMNS FROM homestead. images LIKE ’id’

Команда 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_type table partitions type possible_keys Key key_len ref rows filtered Extra
1 SIMPLE ‘users’ NULL ‘const’ ‘UNIQ_1483A5E9E7927C74’ ‘UNIQ_1483A5E9E7927C74’ ‘182’ ‘const’ 100.00 NULL
  • 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 usersLEFT JOIN `homestead`.`galleries` AS gal ON users.id = gal.user_idLEFT JOIN `homestead`.`images` AS img on img.gallery_id = gal.idWHERE img.description LIKE '%dog%';

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

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

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

id select_type table partitions type possible_keys Key key_len ref rows filtered Extra
1 SIMPLE ‘users’ NULL ‘index’ ‘PRIMARY,UNIQ_1483A5E9BF396750’ ‘UNIQ_1483A5E9BF396750’ ‘108’ NULL 100.00 ‘Using index’
1 SIMPLE ‘gal’ NULL ‘ref’ ‘PRIMARY,UNIQ_F70E6EB7BF396750,IDX_F70E6EB7A76ED395’ ‘UNIQ_1483A5E9BF396750’ ‘108’ ‘homestead.users.id’ 100.00 NULL
1 SIMPLE ‘img’ NULL ‘ref’ ‘IDX_E01FBE6A4E7AF8F’ ‘IDX_E01FBE6A4E7AF8F’ ‘109’ ‘homestead.gal.id’ ‘25.00’ ‘Using where’

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

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

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

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

Посмотрим на отображение explain:

id select_type Table partitions type possible_keys key key_len Ref rows filtered Extra
1 SIMPLE ‘gal’ NULL ‘ALL’ ‘PRIMARY,UNIQ_1483A5E9BF396750’ NULL NULL NULL 100.00 NULL
1 SIMPLE ‘img’ NULL ‘ref’ ‘IDX_E01FBE6A4E7AF8F’ ‘IDX_E01FBE6A4E7AF8F’ ‘109’ ‘homestead.gal.id’ ‘25.00’ ‘Using where’

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

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

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

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

EXPLAIN SELECT * FROM `homestead`.`galleries` AS galLEFT JOIN `homestead`.`users` AS u ON u.id = gal.user_idWHERE u.id = 1ORDER BY gal.created_at DESCLIMIT 5;

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

EXPLAIN SELECT * FROM `homestead`.`galleries` AS galORDER BY gal.created_at DESCLIMIT 5;

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

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

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

id select_type Table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE ‘gal’ NULL ‘ALL’ ‘IDX_F70E6EB7A76ED395’ NULL NULL NULL 100.00 ‘Using where; Using filesort’
1 SIMPLE ‘u’ NULL ‘eq_ref’ ‘PRIMARY,UNIQ_1483A5E9BF396750’ ‘PRIMARY ‘108’ ‘homestead.gal.id’ ‘100.00’ NULL

и

id select_type table partitions Type possible_keys key key_len ref rows filtered Extra
1 SIMPLE ‘gal’ NULL ‘ALL’ NULL NULL NULL NULL 100.00 ‘Using filesort’

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

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

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

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

Заключение

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *