Оптимизация SQL — анализ планов выполнения SQL с объяснением

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

Продукт: Почему главная страница загружается так медленно?

Разработка: Объем данных слишком велик, нет возможности

Продукт: Нет, должен быть оптимизирован

Разработка: ....

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

Цель этой статьи

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

что может объяснить

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

Как видно из рисунка, нашему sql в основном нужно пройти следующие шаги при запросе:

установить соединение с mysql
Запросите, существует ли кеш, и если да, верните результат напрямую.
Парсер, в основном для разбора sql
Оптимизатор запросов в основном выполняет различные оптимизации SQL, включая переписывание запросов, определение порядка чтения таблиц и выбор соответствующих индексов. . и сгенерировать план выполнения
Перейти к результатам запроса механизма хранения

И мы используем объяснение, чтобы перейти к оптимизатору запросов, чтобы запросить план выполнения.

объяснить объяснение поля

Посмотрите на простой план выполнения

explain select * from t_user where id = 1;

Мы видим, что план выполнения будет отображать связанные поля 12. Ниже мы объясним основные поля и общие значения этих полей:

id

Значение: это набор чисел, указывающий порядок, в котором предложение select или таблица операций выполняются в запросе.

правило:

Если идентификатор не совпадает, чем больше значение идентификатора, тем быстрее выполнение
Один и тот же идентификатор выполняется в порядке сверху вниз

select_type

Общие значения и описания следующие

ценность	описывать
SIMPLE	Простые операторы SELECT (за исключением операций UNION или операций подзапросов)
PRIMARY	Самый внешний SELECT в запросе (например, если две таблицы — UNION или есть подзапрос, операция внешней таблицы — PRIMARY, а операция внутреннего слоя — UNION)
UNION	В операции UNION внутренний SELECT в запросе, то есть SELECT по объединению
SUBQUERY	SELECT в подзапросе
DERIVED	Представляет запрос Select, содержащийся в предложении From.
UNION RESULT	Результат объединения, в настоящее время id равен NULL

table

Задействованные таблицы

тип (важно)

Этот столбец очень важен, он показывает, какой тип соединения используется, используется индекс или нет, Общие значения от лучшего к худшему следующие: система> const> eq_ref> ссылка> диапазон> индекс> все

Описание каждого значения выглядит следующим образом.

ценность	описывать
system	В таблице есть только одна строка, принадлежащая движку MyISAM.
const	Постоянное соединение, таблица имеет не более одной совпадающей строки, обычно используемой для сравнения первичного ключа или уникального индекса, например: select * from t_user where id = 1;
eq_ref	Когда таблица связана с запросом, для каждой строки в первой таблице ей соответствует только одна строка во второй таблице. (1) запрос на присоединение (2) Удар по первичному ключу или ненулевому уникальному индексу
ref	Используется только крайний левый префикс индекса или используемый индекс не является уникальным индексом, индексом не с первичным ключом.
range	между, внутри, > и т. д. являются типичными запросами диапазона
index	Все данные в индексе должны быть просканированы, например: select count(*) from t_user;
all	полное сканирование таблицы

possible_keys

Указывает возможные индексы

key

Указывает последний используемый ключ

ref

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

rows

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

filtered

Количество строк в результате запроса в процентах от указанных выше строк.

Дополнительно (важно)

Этот столбец также очень важен, в основном он показывает описание дополнительной информации, которая может дать нам дополнительные сведения для понимания плана выполнения.

Общие значения и описания следующие

ценность	описывать
Using filesort	Когда порядок по не может использовать индекс для завершения сортировки, оптимизатор должен выбрать подходящий алгоритм для сортировки из памяти или с диска.
Using temporary	Используется временная таблица
Using index	Поля запроса после выборки можно получить в индексе, и нет необходимости возвращаться в таблицу, то есть так называемый покрывающий индекс, который имеет хорошую производительность запросов.
Using index condition	ICP (нажатие условия индекса) было введено после mysql5.6.
Using where	Фильтры сервера Mysql после извлечения строки механизмом хранения

Принцип оптимизации

Обычно существуют следующие принципы оптимизации:

Пусть основной оператор запроса использует соответствующий индекс, тип ALL (полное сканирование таблицы) требует особого внимания, и в то же время установите соответствующий индекс, чтобы уменьшить количество возможных_ключей.
Тип желательно должен достигать уровня ref
Использование временных файлов и использование файловой сортировки в столбце «Дополнительно» необходимо удалить.

Идеи оптимизации

В соответствии с упомянутыми выше принципами оптимизации предлагаются следующие идеи оптимизации.

Для принципов оптимизации 1, 2

Вышеупомянутые пункты 1 и 2 на самом деле могут быть достигнуты путем оптимизации индекса, и чтобы индекс, который мы строим, был оптимальным, нам нужно следовать принципу: принцип индекса Samsung.

Простое описание

☆: Чем больше столбцов индекса соответствуют условию после where, тем меньше данных будет просканировано.

Например, комбинированный индекс (a, b, c), лучше всего использовать три столбца a, b, c в индексе одновременно, после где

☆: Избегайте повторной сортировки

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

☆: Строка индекса содержит все столбцы в операторе запроса, то есть покрывающий индекс

Исходя из этого, мы должны использовать меньше select* для запроса, чтобы увеличить возможность покрытия индексов.

Если ваш индекс может собрать все вышеперечисленные три звезды, это означает, что ваш индекс является лучшим индексом!

Для принципа оптимизации 3

Мы создаем следующую таблицу и вставляем некоторые данные

пользовательская таблица

CREATE TABLE `t_user`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `group_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1240277101395107842 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

Групповая таблица

CREATE TABLE `t_group`  (
  `id` bigint(20) NOT NULL,
  `group_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

Using filesort

Поле order by не находится в состоянии where

Появится следующий sql. Использование файлов
```
select * from t_user where group_id = 2 and age = 32 order by name;
```
Но следующий sql не будет
```
select * from t_user where group_id = 2 and age = 32 order by group_id ;
```
Составной индекс по столбцам

Пример: создайте индекс (имя, возраст, group_id) для таблицы t_user

Появится следующая сортировка sql. Использование файлов
```
select * from t_user where name= '李A' order by group_id;
```
Но следующее не будет
```
select * from t_user where name = '李A' order by age;
```
Поскольку первый порядок запросов пропускает возраст и использует group_id напрямую, удалите индекс (имя, возраст, group_id);
Так как первый шаг группировки сортируется по умолчанию, когда поля группировки удовлетворяют указанным выше условиям, также появится сообщение Использование файловой сортировки.Вы можете добавить нулевой порядок после группировки, чтобы отменить сортировку.

Using temporary

Внешний вид временных таблиц сильно влияет на производительность, в основном в следующих ситуациях

Поле группировки не находится за условием where, а группировка по полю не является окончательным используемым индексом, причина в чем-то аналогична приведенной выше сортировке файлов.

Появится следующий sql. Использование временного
```
select * from t_user where group_id = 2 and name= '李A' group by age;
```
Но следующий sql не будет
```
select * from t_user where name = '李A' and age = 21 group by age;
```
Вывод: где какие поля, просто сгруппируйте по каким полям
При объединении таблиц столбец порядка отсутствует в таблице драйверов.

Следующий sql создаст временную таблицу
```
explain select * from t_user t1 left join t_group t2 on t1.group_id = t2.id order by t2.id;
```
Потому что, когда t1 и t2 соединены, t1 является ведущей таблицей, но при сортировке используются поля ведомой таблицы t2. Если изменить сортировку полей t1, временной таблицы не будет, поэтому здесь нет примера.

Вывод: При подключении запросов поле сортировки использует поле таблицы драйвера
Когда предложения order by и group by различны
```
explain select * from t_user group by group_id order by `name`;
```
В этом случае можно использовать только одно и то же поле для группировки и сортировки, иначе не избежать
Когда отдельный запрос и порядок добавления
```
explain select DISTINCT(`name`) from t_user order by age;
```
Эта ситуация иногда неизбежна, и мы можем только попытаться использовать один и тот же индекс для отдельного поля и порядок по полю. Также бывают ситуации, в которых будут появляться временные таблицы: подзапросы и объединения в from, и я не буду приводить здесь примеры по одному.

Суммировать

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