в предыдущей статье«Анализ выполнения SQL в MySQL с помощью команды объяснения»В разделе мы подробно объяснили использование команды «Объяснить». Однако он может показать только план выполнения оператора SQL и не может показать, почему некоторые другие планы выполнения не выбраны, например, указать, что есть индекс, но почему индекс не используется в запросе и т. д. С этой целью MySQL предоставляет функцию Optimizer Trace, которая позволяет нам более подробно понять все процессы анализа, оптимизации и выбора, выполняемые оператором SQL.
Трассировки оптимизатора полезны, если вы хотите лучше понять, почему был выбран определенный план запроса. В то время как EXPLAIN показывает выбранный план, Optimizer Trace показывает, почему план был выбран: вы сможете увидеть альтернативные планы, предполагаемые затраты и принятые решения. В этой статье подробно объясняется вся необходимая информация, отображаемая Optimizer Trace, и она будет дополнена некоторыми конкретными вариантами использования.
План выполнения на основе затрат
Прежде чем разбираться с Optimizer Trace, давайте узнаем, как MySQL выбирает множество планов выполнения.
MySQL использует оптимизатор на основе затрат для выбора плана выполнения. Стоимость каждого плана выполнения примерно отражает ресурсы, необходимые для запланированного запроса, причем основным фактором является количество строк, к которым будет осуществляться доступ при вычислении запроса. Оптимизатор в основном выносит суждения на основе статистических данных, полученных от механизма хранения, и информации о метаданных в словаре данных. Он решит, следует ли использовать полное сканирование таблицы или определенный индекс для сканирования, а также определит порядок соединения таблиц. Роль оптимизатора показана на рисунке ниже.
Каждой операции оптимизатор присваивает себестоимость.Основной единицей или минимумом этих затрат является стоимость чтения случайной страницы данных с диска, а стоимость остальных операций кратна этой стоимости. Следовательно, оптимизатор может рассчитать общую стоимость каждого плана выполнения на основе всех его операций, а затем выбрать из числа планов выполнения тот, у которого наименьшая стоимость, для окончательного выполнения.
Поскольку стоимость маркируется на основе статистических данных, всегда будут выборки, которые не могут корректно отразить общую ситуацию, что является одной из важных причин, по которой оптимизатор MySQL иногда делает некорректные оптимизации.
Базовое использование Optimizer Trace
Во-первых, давайте подробно рассмотрим, как использовать Optimizer Trace. По умолчанию эта функция отключена. Вы можете использовать следующий метод, чтобы открыть эту функцию, затем выполнить оператор SQL, который необходимо проанализировать, а затем найти соответствующую информацию об оптимизации выполнения оператора SQL в OPTIMIZER_TRACE схемы INFORMATION_SCHEMA.
# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
SELECT ...; # 这里输入你自己的查询语句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";
Таблица OPTIMIZER_TRACE содержит 4 следующих столбца:
-
QUERY: Указывает на наш оператор запроса. -
TRACE: текст в формате JSON, представляющий процесс оптимизации. -
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: Поскольку процесс оптимизации может выводить много, при превышении определенного предела лишний текст отображаться не будет, в этом поле отображается количество байтов текста, которые игнорируются. -
INSUFFICIENT_PRIVILEGES: Указывает, нет ли разрешения на просмотр процесса оптимизации, значение по умолчанию равно 0, только в некоторых особых случаях1, нас пока не волнует значение этого поля.
Среди них наиболее важной и самой важной информацией является вторая колонка TRACE, которая также находится в центре внимания нашего последующего анализа.
Базовый формат столбца TRACE
Содержимое столбца TRACE представляет собой очень большие данные JSON, и считается, что у всех болит голова, когда они напрямую раскрываются, а затем анализируются один за другим.
Итак, давайте взглянем на скелет этого большого JSON. Он состоит из трех основных блоков, которые также представляют собой три этапа обработки операторов SQL, а именно этап подготовки, этап оптимизации и этап выполнения.
Далее мы подробно представляем случай, в котором вводятся конкретные поля и значения.
Почему запрос идет не по индексу, а по полному скану таблицы
Во-первых, существует множество ситуаций, в которых запросы операторов SQL не используют индексы.Мы обсуждаем здесь только то, что оптимизатор, основанный на стоимости, считает, что стоимость плана выполнения запроса полной таблицы ниже, чем стоимость плана выполнения индекса.
Как показано на рисунке ниже, в столбце val есть индекс, и существующее значение val также имеет определенное отличие.Почему бы не использовать индекс для запроса?
Мы используем Optimizer Trace, как указано выше, чтобы найти данные, связанные с range_analysis, в его join_optimization, который покажет выбор индекса в процессе запроса диапазона предложения where.
Как видно из приведенного выше рисунка, MySQL сравнил стоимость полного сканирования таблицы и использования val в качестве индекса и, наконец, обнаружил, что хотя полное сканирование таблицы требует сканирования большего количества строк, стоимость ниже. Поэтому выбирается план выполнения полного сканирования таблицы.
Почему это? Очевидно, что использование индекса val может сканировать на 4 строки меньше. На самом деле это включает в себя принцип запроса строк данных с использованием индексов в InnoDB.
Когда механизм Innodb запрашивает записи, покрытие индекса использовать нельзя (то есть данные, которые необходимо запросить, превышают значение индекса. Например, в этом примере я хочу запросить имя, а столбец индекса — val), необходимо выполнить операцию возврата таблицы для получения записей.Обязательные поля, то есть узнать первичный ключ по индексу, затем найти строку данных, и вынуть соответствующий столбец, который обязательно будет стоить дороже.
Поэтому, когда количество данных, возвращаемых в таблицу, относительно велико, часто бывает так, что Mysql не использует индекс, потому что предполагаемая стоимость запроса операции возвращаемой таблицы слишком велика.
Вообще говоря, когда оператор SQL запрашивает более одной пятой записей в таблице и покрывающий индекс не может быть использован, стоимость возврата индекса в таблицу будет слишком высокой, и будет выбрано полное сканирование таблицы. И это соотношение несколько увеличивается по мере увеличения размера однострочной записи в байтах.
С помощью соответствующих данных в range_analysis вы также можете использовать несколько столбцов индекса в предложении where, чтобы проанализировать, как выбрать индекс, используемый во время выполнения.
подраздел
Наконец, вводятся объяснения и трассировка оптимизатора об анализе выполнения операторов MySQL.В следующей статье мы проанализируем конкретные сценарии взаимоблокировок.
Личный блог, добро пожаловать в игру
