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

 explain

Это результат (прокрутите вправо, чтобы увидеть все):

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

• EXPLAIN SELECT * FROM `homestead`.`users` WHERE email = '[email protected]';


• id Это поле может принимать различные значения, поэтому мы сосредоточимся на наиболее важных:

  • select_type
  • SIMPLE
  • PRIMARYDERIVED
  • SUBQUERY
  • UNION

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

• table

• type Это, вероятно, самое важное поле в выводе объяснения. Это может указывать на отсутствующие индексы, а также на то, как запрос должен быть переписан. Возможные значения для этого поля следующие (упорядочены от лучшего типа к худшему):

  • system
  • const Это самый быстрый тип соединения.
  • eq_ref
  • ref Этот тип объединения обычно отображается для индексированных столбцов по сравнению с операторами =<=>
  • fulltext
  • ref_or_null
  • index_merge Столбец KEY explain
  • unique_subquery
  • range
  • index
  • all Это худший тип объединения, который часто указывает на отсутствие соответствующих индексов в таблице.

• possible_keys Эти ключи могут или не могут быть использованы на практике.

• keys MySQL всегда ищет оптимальный ключ, который можно использовать для запроса. Присоединяясь ко многим таблицам, он может определить некоторые другие ключи, которые не перечислены в possible_keys

• key_len

• ref

• rows Это очень важный показатель; чем меньше проверенных записей, тем лучше.

• Extra Такие значения, как Using filesortUsing temporary

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

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

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

 SIMPLE

В этом более сложном случае мы должны иметь больше информации для анализа нашего 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 Цель должна получить лучшее значение в столбце rowstype

Наш результат по первому запросу — 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 Хотя ALL В соответствии с нашими требованиями нам нужны все изображения галерей, поэтому нам нужно просмотреть всю таблицу галерей. Хотя индексы действительно хороши при поиске конкретной информации в таблице, они не могут помочь нам, когда нам нужна вся информация в ней. Когда у нас есть такой случай, мы должны прибегнуть к другому методу, например, кешированию.

Последнее улучшение, которое мы можем внести, поскольку мы имеем дело с ALL Таким образом, мы могли бы изменить LIKELIKE Подробнее о полнотекстовых индексах можно узнать здесь .

Есть также два очень интересных случая, которые мы должны рассмотреть: 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;

Выше для новейших галерей.

На первый взгляд, эти запросы должны быть быстрыми, потому что они используют EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
ORDER BY gal.created_at DESC
LIMIT 5;
И это относится к большинству запросов, использующих LIMIT К сожалению для нас и нашего приложения, эти запросы также используют LIMIT Поскольку нам нужно упорядочить все результаты, прежде чем ограничивать запрос, мы теряем преимущества использования ORDER BY

Поскольку мы знаем, что LIMITORDER BY

И,

Как мы видим, у нас наихудший случай типа соединения: explain

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

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

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

Вот некоторые из мер, которые мы должны предпринять, когда у нас есть LIMITORDER BY

Вывод

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

Наше приложение имеет все необходимые индексы, и оно довольно быстрое, но теперь мы знаем, что всегда можем прибегнуть к explain