Подробное объяснение плана выполнения объяснения исследования Mysql.

предисловие

• Как писать эффективные операторы SQL, это неизбежно упоминаетсяExplainАнализ плана выполнения, что такое план выполнения, как написать эффективный SQL, эта статья представит их один за другим.

План реализации

• План выполнения — это план запроса, составленный базой данных на основе оператора SQL и статистики связанных таблиц, который автоматически анализируется оптимизатором запросов.

• использоватьexplainКлючевые слова могут имитировать оптимизатор для выполнения оператора SQL-запроса, чтобы узнать, как MySQL обрабатывает ваш оператор SQL, проанализировать узкое место в производительности вашего оператора выбора или структуры таблицы, сообщить нам причину низкой эффективности выбора, чтобы улучшить наш запрос. .

• Результат объяснения выглядит следующим образом:

• Ниже приводится подробное введение в каждую колонку.id,type,key,rows,extra.

id

• Номер столбца id — это порядковый номер выборки, который также можно понимать как идентификатор порядка выполнения SQL.Для нескольких выборок существует несколько идентификаторов.
  • Значение id отличается: если это только запрос, то порядковый номер id будет увеличиваться, чем больше значение id, тем выше приоритет и выполняться первым;
  • Значения id одинаковые: выполнить сверху вниз;
  • Столбец id имеет значение null: указывает, что это набор результатов, и его не нужно использовать для запросов.

select_type

• Тип запроса, который в основном используется для различения сложных запросов, таких как обычные запросы, совместные запросы и подзапросы;

  • простой: указывает, что запрос не включает операции объединения или подзапросы, а select_type самого внешнего запроса является простым и существует только один;

    explain select * from t3 where id=3952602;
  

  • первичный: выбор, который требует операции объединения или содержит подзапрос, select_type самого внешнего запроса является первичным, и он только один;

  explain select * from (select * from t3 where id=3952602) a ;
  

  • производные: подзапросы, появляющиеся в списке from, также известные как производные таблицы; mysql или рекурсивно выполняют эти подзапросы и помещают результаты во временную таблицу.

    explain select * from (select * from t3 where id=3952602) a ;
  

  • подзапрос: Подзапросы, появляющиеся где-либо, кроме тех, которые содержатся в предложении from, могут быть подзапросами.

  explain select * from t3 where id = (select id from t3 whereid=3952602 ) ;
  

• union: если второй выбор появляется после объединения, он помечается как объединение; если объединение включено в подзапрос предложения from, внешний выбор будет помечен как производный.

  explain select * from t3 where id=3952602 union all select * from t3;
  

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

    explain select * from t3 where id=3952602 union all select * from t3;
  

• зависимое объединение: как и объединение, оно появляется в операторе union или union all, но на этот запрос влияет внешний запрос;
• зависимый подзапрос: Подобно зависимому объединению, первый SELECT в подзапросе, запрос этого подзапроса зависит от запроса внешней таблицы.

table

• Указывает, к какой таблице обращается строка объяснения.
  • Если в запросе используется псевдоним, он отображается здесь;
  • Если никакие операции над таблицей данных не выполняются, это отображается как null;
  • Если он отображается в угловых скобках, это означает, что это временная таблица, а N за ней — это id в плане выполнения, указывающий, что результат исходит от этого запроса;
  • Аналогично, если это , заключенный в угловые скобки, это также временная таблица, указывающая, что результат исходит из набора результатов, id которого равен M,N запроса на объединение.

type

• Тип доступа, с помощью которого MySQL решает, как искать строки в таблице.

• В порядке от хорошего к плохому: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL, кроме всех, другие типы могут использовать индексы, кроме index_merge, другие типы могут использовать только один индекс. Вообще говоря, необходимо добиться того, чтобы запрос достиг хотя бы уровня диапазона, желательно ref.

  1. system: в таблице есть только одна строка данных (эквивалентная системной таблице), которая является частным случаем типа const, который обычно не появляется и может быть проигнорирован.

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

  3. eq_ref: уникальное сканирование индекса, для каждого ключа индекса ему соответствует только одна строка данных в таблице. Обычно встречается при сканировании первичного ключа или уникального индекса.

  4. ref: сканирование неуникального индекса, которое возвращает все строки, соответствующие одному значению. По сути, это тоже индекс.

  5. fulltext: поиск полнотекстового индекса, приоритет полнотекстового индекса очень высок, если полнотекстовый индекс и обычный индекс существуют одновременно, mysql предпочитает использовать полнотекстовый индекс независимо от стоимости.

  6. ref_or_null: аналогично методу ref, но с добавленным сравнением нулевых значений.

  7. index_merge: указывает, что запрос использует более двух индексов, метод оптимизации слияния индексов и, наконец, принимает пересечение или объединение.Общие условия и , или используют разные индексы.

  8. unique_subquery: используется в подзапросе, где подзапрос возвращает уникальные значения с уникальными значениями;

  9. index_subquery: используется для подзапросов в форме, которые используют вспомогательные индексы или в постоянных списках. Подзапросы могут возвращать повторяющиеся значения. Индексы можно использовать для дедупликации подзапросов.

  10. диапазон: сканирование диапазона индексов, обычно используется при использовании>,<,between ,in ,likeв запросе оператора.

  11. index: полное сканирование таблицы индексов, сканирование дерева индексов от начала до конца;

  12. all: пройтись по всей таблице, чтобы найти совпадающие строки (хотя Index и ALL читают всю таблицу, index читает из индекса, а ALL читает с жесткого диска)

  13. NULL: MySQL разбирает оператор во время оптимизации и выполняет его, даже не обращаясь к таблицам или индексам.

possible_keys

• Отображает индексы, которые могут использоваться запросом.

key

• Показывает, какой индекс фактически использует запрос для оптимизации доступа к этой таблице;

• Когда select_type равен index_merge, здесь может отображаться более двух индексов, а другие select_types будут отображаться здесь только один.

key_len

• Длина индекса, используемого для обработки запроса, представляющая количество байтов, используемых в индексе. По этому значению можно определить, какая часть многоколоночного индекса фактически используется.
• Примечание. Значение, отображаемое key_len, представляет собой максимально возможную длину поля индекса, а не фактическую длину, то есть key_len вычисляется в соответствии с определением таблицы, а не извлекается из таблицы. Кроме того, key_len вычисляет только длину индекса, используемого условием where, и даже если индекс используется для сортировки и группировки, он не будет вычисляться в key_len.

ref

• Показывает, какое поле или константа используется с ключом.

  1. Если используется постоянный эквивалентный запрос, здесь будет отображаться const.

  2. Если это запрос на соединение, план выполнения управляемой таблицы будет отображать связанные поля управляющей таблицы.

  3. Если условие использует выражение или функцию, или столбец условия имеет внутреннее неявное преобразование, оно может отображаться здесь как func.

rows

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

extra

• Дополнительная информация, не подходящая для отображения в других столбцах, но важная.

• В этой колонке может отображаться много информации, их десятки, наиболее часто используемые:

Типы	иллюстрировать
Using filesort	У MySQL есть два способа генерировать упорядоченные результаты: с помощью операций сортировки или с помощью индексов.Когда «Использование файловой сортировки» появляется в «Дополнительно», это означает, что MySQL использует последний, но обратите внимание, что, хотя это называется файловой сортировкой, это не означает, что файлы используются для сортировки. Сортировка выполняется в памяти, когда это возможно. В большинстве случаев быстрее использовать индекс для сортировки, поэтому обычно рассмотрите возможность оптимизации запроса на данном этапе. Операция сортировки выполняется с использованием файла, который может быть результатом оператора ordery by, group by, который может быть процессом с интенсивным использованием ЦП, а производительность можно повысить, выбрав соответствующий индекс, который используется для сортировки результатов запроса. .
Using temporary	Временные таблицы используются для сохранения промежуточных результатов, которые часто используются в операциях GROUP BY и ORDER BY. Как правило, это означает необходимость оптимизации запроса. Даже если использование временных таблиц невозможно избежать, использование временных столов следует избегать, насколько это возможно.
Not exists	MYSQL оптимизирует LEFT JOIN, как только он находит строку, соответствующую критериям LEFT JOIN, он больше не выполняет поиск.
Using index	Это означает, что запрос охватывает индекс, и информацию можно получить из индексного дерева (индексного файла) без чтения файла данных. Если указание "где" появляется одновременно, это указывает на то, что индекс используется для поиска значений ключа индекса. Без использования "где" это указывает на то, что индекс используется для чтения данных, а не для выполнения действий поиска. Это делается сервисным уровнем MySQL, но нет необходимости возвращаться к таблице для запроса записей.
Using index condition	Это новая функция в MySQL 5.6, называемая "передача условия индекса". Проще говоря, MySQL раньше не мог выполнять такие операции, как с индексами, но теперь он может, что уменьшает количество ненужных операций ввода-вывода, но может использоваться только со вторичными индексами.
Using where	Предложение WHERE используется для ограничения того, какие строки будут сопоставлены со следующей таблицей или возвращены пользователю.Уведомление: Использование where отображается в столбце Extra, указывая, что сервер MySQL возвращает механизм хранения на уровень службы, а затем применяет фильтр условия WHERE.
Using join buffer	Кэш подключения используется:Block Nested Loop, алгоритм соединения представляет собой блочное соединение с вложенным циклом;Batched Key Access, алгоритм объединения представляет собой массовое объединение индексов.
impossible where	Значение предложения where всегда ложно и не может использоваться для получения каких-либо кортежей.
select tables optimized away	При отсутствии предложения GROUP BY оптимизация операций MIN/MAX на основе индексов или оптимизация операций COUNT(*) для механизма хранения MyISAM не требует ожидания фазы выполнения для выполнения вычислений и создания плана выполнения запроса. Этап завершает оптимизацию.
distinct	Оптимизируйте отдельную операцию, перестаньте находить одно и то же значение после нахождения первого совпадающего кортежа.

filtered

• Этот столбец появится при использовании расширенного объяснения.В версиях после 5.7 это поле есть по умолчанию, поэтому нет необходимости использовать расширенное объяснение.
• В этом поле указывается доля количества записей, оставшихся для удовлетворения запроса после того, как данные, возвращенные механизмом хранения, отфильтрованы на уровне сервера.Обратите внимание, что это процент, а не конкретное количество записей.

Об ограничениях планов выполнения MySQL

1. EXPLAIN не расскажет вам о триггерах, хранимых процедурах или о том, как определяемые пользователем функции влияют на запрос;
2. EXPLAIN не учитывает различные кэши;
3. EXPLAIN не может показать работу по оптимизации, проделанную MySQL при выполнении запроса;
4. Некоторые статистические данные являются приблизительными, а не точными значениями;
5. EXPALIN может объяснить только операцию SELECT, а остальные операции следует переписать как SELECT.

Пример плана запроса

исполнительный лист

1. (id = 4): [идентификатор выбора, имя из t2]: select_type — это объединение, указывающее, что выборка с id=4 является второй выборкой в ​​объединении.

2. (id = 3): [выбрать идентификатор, имя из t1, где адрес = '11']: поскольку это подзапрос, содержащийся в операторе from, он помечен как DERIVED (производный), где адрес = '11' через составной index idx_name_email_address может быть получен, поэтому тип index.

3. (id = 2): [идентификатор выбора из t3]: он помечен как ПОДЗАПРОС, потому что это подзапрос, содержащийся в select.

4. (id = 1): [выберите d1.name, … d2 из … d1]: если select_type имеет значение PRIMARY, это указывает, что запрос является самым внешним запросом, а столбец таблицы помечен как «derived3», что указывает на то, что запрос результат поступает из производной таблицы (id = 3 выберите результаты).

5. (id = NULL): [ ... union ... ]: представляет собой этап чтения строк из временной таблицы объединения, "union 1, 4" столбца таблицы указывает, что операция объединения выполняется с выбором результаты id=1 и id=4.