Анализ выполнения MySQL SQL с помощью команды объяснения

в предыдущей статье«Анализ распространенных сценариев блокировки MySQL»В разделе мы говорили о том, что блокировки строк добавляются к индексам, но сложный SQL часто содержит несколько условий и включает в себя несколько индексов, поэтому очень важно выяснить, какие индексы используются во время выполнения SQL для анализа сценариев блокировки.

Например, следующий SQL:

mysql> delete from t1 where id = 1 or val = 1

Среди них id и val являются индексами, так какие индексы используются во время выполнения и какие блокировки добавляются? Для этого нам нужно использовать объяснение, чтобы получить план выполнения MySQL для выполнения этого SQL.

Что такое план выполнения? Проще говоря, это производительность SQL, когда он выполняется в базе данных. Обычно он используется в таких сценариях, как анализ производительности SQL, оптимизация и анализ блокировок. Процесс выполнения будет совместно выполняться синтаксическим анализатором, препроцессором и оптимизатором запросов. во время процесса запроса MySQL.

Процесс запроса MySQL

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

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

Процесс запроса MySQL выглядит следующим образом:

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

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

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

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

Ниже мы по очереди вводим соответствующие выходные параметры в объяснение и поясняем значение этих параметров на практических примерах.

select_type

Типы операций с данными запроса следующие:

simple — это простой запрос, который не содержит подзапросов или объединений.Как показано на следующем рисунке, это простейший оператор запроса.

первичный — это сложный подзапрос, содержащийся в SQL, и в настоящее время самый внешний запрос помечен этим значением.
производным являются подзапросы, содержащиеся в предложении from в SQL, которые помечены этим значением, и MySQL рекурсивно выполнит эти подзапросы и поместит результаты во временную таблицу. На приведенной ниже диаграмме показаны оба вышеуказанных типа.

подзапрос — это подзапрос, который SQL включает в select или where и помечен этим значением.

Зависимый подзапрос: первый выбор в подзапросе зависит от внешнего запроса, обычно подзапроса в in.

union является вторым выбором SQL после ключевого слова union и помечен этим значением; если union включен в подзапрос from, внешний выбор помечается как производный.
результат объединения Выбор, который получает результат из таблицы объединения. На следующем рисунке показан случай SQL с объединением и результатом объединения.

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

type

Тип соединения таблицы в порядке убывания производительности: system, const, eq_ref, ref, range, index и все.

system означает, что таблица имеет только одну строку записей, что эквивалентно системной таблице. Как показано на рисунке ниже, поскольку таблица, полученная из подзапроса from, имеет только одну строку данных, тип подключения к основной таблице — системный.

const находится в индексе один раз, соответствует только одной строке данных и используется для сравнения постоянных значений индекса PRIMARY KEY или UNIQUE.

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

Как показано на рисунке ниже, тип запроса к таблице t1 — ALL, что означает полное сканирование таблицы, а затем каждая строка данных в t1 сравнивается с индексом первичного ключа t2.id, поэтому запрос к таблице t2 — это eq_ref.

ref — это сканирование неуникального индекса, которое возвращает все строки, соответствующие одному значению. Отличие от eq_ref заключается в том, что индекс не является уникальным индексом. Конкретные случаи следующие.

range проверяет только заданный диапазон строк, используя индекс для выбора строк, при использовании таких операторов, как =, между, >,

Индекс подобен типу ALL, единственное отличие состоит в том, что он проходит по дереву индекса только для чтения значения индекса, что немного быстрее, чем ALL для чтения всех строк данных, поскольку файл индекса обычно меньше, чем файл данных. Это включает покрытие индекса MySQL
ВСЕ полные сканирования таблиц, как правило, имеют низкую производительность, и их следует избегать.

возможные_ключи, ключ и key_len

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

Ключевой столбец показывает, какой индекс MySQL фактически решил использовать. Если индекс не выбран, значение равно NULL.

key_len показывает длину индекса, который MySQL решил использовать. Если ключ имеет значение NULL, этот столбец также имеет значение NULL, а длина используемого индекса должна быть как можно меньше, обеспечивая при этом точность. Потому что чем короче индексный файл, тем меньше требуется операций ввода-вывода.

Как видно из рисунка выше, для оператора select * from t2, где id = 1 или val = 1, можно использовать индекс PRIMARY или idx_t2_val, фактически используется индекс idx_t2_val, а длина индекса равна 5.

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

ref

Столбец ref указывает, какой столбец или константа другой таблицы используется для выбора строк из таблицы. Как показано на рисунке ниже, при чтении данных из t2 необходимо судить, что t2.id = t1.id, поэтому ref — это mysql.t1.id

строки и отфильтровано

Столбец rows показывает количество строк, которые, по мнению MySQL, он должен проверить при выполнении запроса.

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

Как показано на рисунке выше, в таблице t1 три части данных, а строк 3, что означает, что все строки должны быть считаны. В соответствии с фильтром таблицы val = 3 возвращается только одна строка данных, поэтому коэффициент фильтрации составляет 33,33%,

extra

Содержит дополнительную информацию, не подходящую для отображения в других столбцах, но важную. Общие значения следующие

Использование индекса означает, что операция выбора использует покрывающий индекс, что позволяет избежать доступа к строкам данных таблицы, и эффективность является хорошей.
Предложение using where используется для ограничения строки. То есть, прочитав данные, используйте Table Filter для фильтрации.

Как показано на рисунке ниже, поскольку и id, и val индексируются, select * также может напрямую использовать покрывающий индекс для чтения данных, так что индекс использования используется дополнительно. И поскольку только 3 строки данных считываются с использованием индекса val, они по-прежнему фильтруются предложением where, а скорость фильтрации составляет 55%, поэтому использование where используется дополнительно.

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

использование временного Используйте временную таблицу для сохранения промежуточных результатов. Например, MySQL использует временную таблицу при сортировке результатов запроса, которая часто используется для упорядочения и группировки. Если встречается это значение, SQL следует оптимизировать. По моему опыту, группировка неиндексированного столбца или столбца ORDER BY или GROUP BY, который не входит в первую таблицу в последовательности операторов JOIN, приводит к временной таблице.
использование буфера соединения Используйте буфер соединения. Как показано на рисунке ниже, отображается кэш соединений и временные таблицы. Что касается содержимого кеша подключения, вы можете проверить это сами, и у вас будет время написать статью, чтобы объяснить это позже.

Different прекращает поиск дополнительных строк для текущей комбинации строк после того, как будет найдено первое совпадение.

постскриптум

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

Личный блог, добро пожаловать в игру