Что такое Объяснить

Объяснение называется планом выполнения. Добавьте ключевое слово объяснения перед оператором. MySQL установит отметку в запросе, чтобы имитировать оптимизатор MySQL для выполнения оператора SQL. Когда запрос будет выполнен, будет возвращена информация о плане выполнения, и SQL не будет выполняться. (Обратите внимание, что если from содержит подзапрос, этот подзапрос все равно будет выполнен, а результаты будут помещены во временную таблицу).

Объяснение можно использовать для анализа узких мест производительности в операторах SQL и структурах таблиц. Благодаря результату объяснения вы можете узнать порядок запроса таблицы данных, тип операции операции запроса данных, какие индексы могут быть затронуты, какие индексы будут фактически затронуты и сколько строк записей в каждой таблице данных запрашивается.

Объясните расширение команды

explain extended

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

explain partitions

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

Два важных примечания

1. Объясните, что результаты основаны на существующих данных в таблице данных.

2. Объясните, что результаты во многом зависят от версии MySQL, а оптимизаторы разных версий используют разные стратегии оптимизации.

Таблицы базы данных, используемые в примерах в этой статье

Команда объяснения (ключевое слово)

объяснить простой пример

mysql>explain select * from t_user;

Каждая "таблица" в запросе выводит строку. "Таблица" здесь имеет очень широкое значение, не только таблица базы данных, но также подзапрос, результат объединения и так далее.

объяснить описание столбца результатов

[идентификатор столбца]

Столбец id представляет собой порядковый номер, который является порядковым номером запроса. Существует несколько вариантов выбора для отображения нескольких строк. Порядок идентификаторов увеличивается в порядке появления выбора. Чем больше значение столбца id, тем выше приоритет выполнения будет выполняться первым.Если значение столбца id одинаково, выполнение будет выполняться сверху вниз, а значение столбца id будет NULL и выполняется последним.

[столбец select_type]

Значение столбца select_type указывает тип запроса:

1) простой: указывает, что выбор, соответствующий текущей строке, является простым запросом, исключая подзапросы и объединения.

2) первичный: указывает, что выбор, соответствующий текущей строке, является самым внешним выбором в сложном запросе.

3) подзапрос: указывает, что выбор, соответствующий текущей строке, является подзапросом, содержащимся в выборе (а не в предложении from).

4) производный: указывает, что выбор, соответствующий текущей строке, является подзапросом, содержащимся в предложении from.

MySQL создает временную таблицу для хранения результатов запроса подзапроса. Для иллюстрации используйте следующий пример оператора:

explain select (select 1 fromt_user where user_id=1) from (select * from t_group where group_id=1) tmp;

*Обратите внимание, что в процессе сбора данных было обнаружено, что производительность разных версий MySQL была непоследовательной.После повторного сравнения вывод 5.7 и более поздних версий выглядит следующим образом:

Ясно, что MySQL оптимизирован для этого.

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

5) объединение: указывает, что выбор, соответствующий текущей строке, является вторым и последующим выбором в объединении

6) результат объединения: указывает, что выбор, соответствующий текущей строке, является выбором, который извлекает результат из временной таблицы объединения.

explain select 1 union all select 2 fromdual;

MySQL 5.7 и более поздние версии также были оптимизированы.

[столбец таблицы]

Результат столбца таблицы указывает, к какой таблице обращается выбор, соответствующий текущей строке. Когда в предложении запроса есть подзапрос, столбец таблицы имеет формат, указывающий, что текущий выбор зависит от запроса, соответствующего строке результата id=N, и запрос с id серийный номер=N должен выполняться первым. При наличии объединения значение столбца таблицы UNION RESULT равно , а N1 и N2 представляют собой идентификационные номера выбранных строк, участвующих в объединении.

[тип столбца]

Результат столбца типа указывает связанный тип или тип доступа выбора, соответствующий текущей строке, то есть оптимизатор решает, как найти строку в таблице данных, и приблизительный диапазон записи строки данных. Степень оптимизации значений этого столбца, от лучшего к худшему, выглядит следующим образом: null>system>const>eq_ref>ref>range>index>ALL. В общем, чтобы убедиться, что запрос достигает уровня диапазона, лучше всего достичь ref.

1) null, оптимизатор MySQL декомпозирует оператор запроса на этапе оптимизации, и результат можно получить уже в процессе оптимизации, поэтому нет необходимости обращаться к таблице или индексу на этапе выполнения.

explain select min(user_id) from t_user;

В это время функция min выбирает минимальное значение в столбце индекса user_id, что может быть выполнено прямым поиском по индексу, без доступа к таблице данных при выполнении.

2) const и system: const возникает, когда все столбцы первичного ключа (primary key) или уникального ключа (unique key) сравниваются с константами, оптимизатор оптимизирует запрос и преобразует часть запроса в константу. Не более одной совпадающей строки, прочитать 1 раз, очень быстро. А система — это частный случай константы, когда в таблице есть только одно совпадение данных — это система. На этом этапе вы можете использовать предупреждения объяснения расширенного + показа для просмотра результатов выполнения.

explain extended select * from (select * from t_user where user_id = 1) tmp;

show warnings;

После оптимизации MySQL 5.7 и более поздних версий:

3) eq_ref: первичный ключ (первичный ключ) или уникальный ключ (уникальный ключ). При соединении используются все компоненты индекса, и будет возвращена только одна подходящая строка данных. Это тип соединения после const.

explain select * from t_group_user gu left join t_group g ong.group_id = gu.group_id;

4. ref: по сравнению с eq_ref тип ref использует не уникальный индекс, такой как первичный ключ (первичный ключ) или уникальный ключ (уникальный ключ), а общий индекс или частичный префикс объединенного уникального индекса. по сравнению с определенным значением, возможно, найдено несколько строк данных, соответствующих критериям.

1. В следующем примере используемое group_name является общим индексом.

explain select * from t_group where group_name= 'group1';

2. Запрос таблицы ассоциаций

explain select g.group_id from t_group gleft join t_group_user gu on gu.group_id = g.group_id;

5) диапазон: появляется в таких операторах, как in(),between,> ,=. Используйте индекс для запроса заданного диапазона строк.

6) индекс: сканировать полный индекс таблицы (индекс читается из индекса, все поля имеют индексы, и все читается с жесткого диска), что быстрее, чем ALL.

explain select * from t_group;

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

explain select * from t_user;

[столбец possible_keys]

Результаты этого столбца указывают, какие индексы может использовать запрос. Но иногда в столбце возможных_ключей будет результат, а в следующем ключевом столбце отображается ноль. Это связано с тем, что в данный момент в таблице не так много данных, и оптимизатор считает, что индекс запроса бесполезен для запроса, поэтому он не использует индексный запрос.Было выполнено полное сканирование таблицы.

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

[ключевой столбец]

В этом столбце указано, какой индекс оптимизатор фактически использует для оптимизации доступа к этой таблице. Если индекс не используется, столбец имеет значение null.

[столбец key_len]

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

Правила расчета key_len здесь не повторяются, количество байтов, занимаемых разными типами данных, несовместимо.

[столбец ссылки]

Этот столбец указывает столбец или константу, используемую в значении поиска таблицы в индексе записи ключевого столбца, общие из них: const (константа), имя поля, например user.user_id

【столбец строк】

В этом столбце указано приблизительное количество строк, которые оптимизатор прочитает и проверит. В большинстве случаев оно не соответствует фактическому количеству строк данных.

【Дополнительный столбец】

Как следует из названия, в этом столбце указывается дополнительная информация, и значение этого столбца очень полезно для оптимизации SQL. Общие важные значения следующие:

1) с использованием индекса: все запрашиваемые поля являются столбцами индекса (называемые покрывающими индексами), а условие where является ведущим столбцом индекса. Такой результат является высокой производительностью.

explainselect group_id,group_name from t_group;

2) с использованием where: запрашиваемый столбец не покрывается индексом, а условие where не является ведущим столбцом индекса, что указывает на то, что исполнитель MySQL получает данные запроса от механизма хранения, а затем выполняет «пост- фильтрация". Так называемая «постфильтрация» заключается в том, чтобы сначала прочитать всю строку данных, а затем проверить, соответствует ли строка условиям предложения where.

explain select * from t_user whereuser_name='user1';

3) использование где Использование индекса: запрашиваемый столбец покрывается индексом, а условие где является одним из столбцов индекса, но не ведущим столбцом индекса, то есть нет возможности напрямую запросить квалифицированные данные через индекс

explain select * from t_group where group_name = 'group1';

4) null: запрашиваемый столбец не покрывается индексом, но условие where является ведущим столбцом индекса. В настоящее время индекс используется, но некоторые столбцы не покрываются индексом. Когда индекс достигнуто, это не совсем бесполезно

explain select * from t_user where user_id='1';

5) использование условия индекса: аналогично использованию where, столбец запроса не полностью покрывается индексом, а условие where представляет собой диапазон ведущих столбцов; эту ситуацию нельзя показать в примере, который может быть связан с версией MySQL. .

6) использование временных: это указывает на то, что запрос необходимо обработать путем создания временной таблицы. В этом случае вообще необходимо оптимизировать, и использовать индекс для оптимизации. Ситуация создания временных таблиц: отдельные, групповые, упорядоченные, подзапросы и т. д.

explain select distinct user_name from t_user;

объясните, выберите отличное имя_группы от_группы; --имя_группы — индексный столбец

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

explain select * from t_user orderby user_name;

объясните select * from t_group в порядке bygroup_name; --group_name — столбец индекса

Рекомендации по оптимизации запросов

В сочетании с предыдущим описанием сначала посмотрите на результат столбца type.Если тип all, это означает, что ожидается полное сканирование таблицы. Как правило, стоимость полного сканирования таблицы относительно велика, поэтому рекомендуется создавать соответствующие индексы и избегать полного сканирования таблицы с помощью извлечения индекса.

Посмотрим на результаты столбца «Дополнительно».

Using temporary, указывающее, что для удовлетворения потребностей необходимо создать временную таблицу, обычно потому, что столбцы GROUP BY не имеют индексов или столбцы GROUP BY и ORDER BY отличаются, и необходимо создать временную таблицу. рекомендуется добавить соответствующие индексы.

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

Using where, обычно потому, что условие WHERE добавляется во время полного сканирования таблицы или полного сканирования индекса (столбец type отображается как ALL или index), и рекомендуется добавить соответствующий индекс.

Анализ использования индекса

Таблица базы данных

Индекс первичного ключа: demo_id

Совместный индекс: c1,c2,c3

Пример описания

Пример первый:

explain select * from t_demo where c1='d1'and c2='d2' and c3='d3';

explain select * from t_demo where c2='d2'and c1='d1' and c3='d3';

explain select * from t_demo where c3='d3'and c1='d1' and c2='d3';

Несколько Sql ведут себя одинаково

type=ref,ref=const,const,const

При выполнении константно-эквивалентного запроса изменение порядка столбцов индекса не изменит результат выполнения объяснения, а оптимизатор оптимизирует его.Рекомендуется писать операторы SQL в соответствии с порядком столбцов индекса.

Пример второй:

explain select * from t_demo where c1='d1'and c2>'d2' and c3='d3';

explain select * from t_demo where c1='d1'and c3>'d3' and c2='d2';

В первом примере индекс справа от диапазона недействителен, и используются два индекса.

Во втором примере используются все три индекса из-за оптимизации оптимизатором.

Пример третий:

explain select * from t_demo wherec1>'c' and c2='d2' and c3='d3';

explain select * from t_demo wherec1>'e' and c2='d2' and c3='d3';

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

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

1. Относится к сохраненным данным

2. При условии больше чем, если условные данные меньше, чем данные столбца, индекс недействителен, если условные данные больше, чем данные столбца, индекс действителен;

При разработке условий запроса обратите внимание на избегание.

В первом примере вы можете использовать покрывающий индекс для оптимизации.

Пример четвертый:

explain select * from t_demo where c1='d1'and c2='d2' order by c3;

explain select * from t_demo where c1='d1'order by c3;

explain select * from t_demo where c1='d1'and c3='d3' order by c2;

порядок сортировкой использует индекс и не использует индекс

Пример пятый:

explain select * from t_demo where c1='d1'and c4='d4' order by c1,c2;

Столбец условия содержит столбец без индекса, появляется сообщение Использование сортировки файлов

Пример шестой:

explain select * from t_demo where c1='d1'and c4='d4' group by c1,c2;

Сценарии с очень низкой производительностью, как с использованием временных файлов, так и с использованием сортировки файлов

Суммировать

1. Существует два способа сортировки файлов и индекса: Использование индекса означает, что MySQL просматривает сам индекс, чтобы завершить сортировку. Эффективность индекса высокая, файловая сортировка неэффективна.

2. Использование индекса будет использоваться, когда порядок по удовлетворяет двум условиям.

1) Порядок по оператору использует крайний левый передний столбец индекса.

2) Используйте предложение where и предложение order by, чтобы объединить условные столбцы, чтобы удовлетворить самый левый передний столбец индекса.

3. Попробуйте выполнить сортировку в столбце индекса и следуйте правилу наилучшего левого префикса при создании индекса (порядок создания индекса).

4. Группировка по очень похожа на сортировку по. Сначала они сортируются, а затем группируются в соответствии с правилом наилучшего левого префикса для порядка создания индекса.

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