Команда объяснения в MySQL показывает, как MySQL использует индексы для обработки операторов выбора и объединения таблиц. Информация, отображаемая объяснением, может помочь вам выбрать лучшие индексы и написать более оптимизированные запросы.
1. Формат
объяснить + выбрать оператор;
Например: объясните, выберите * из tb_student;
Во-вторых, объяснить разницу между версиями 5.5 и 5.7.
Версии после 5.7 будут иметь разделы и отфильтрованные столбцы по умолчанию, но они недоступны в версии 5.5 и должны быть
Используйте объяснение разделов, выберите ..., чтобы отобразить столбцы с разделами,
Используйте расширенный выбор объяснения..., чтобы отобразить столбцы с фильтрацией.
Эта статья основана на версии 5.5.54.
3. Роль объяснения
1. Опишите, как MySQL выполняет операции запроса, порядок выполнения, используемые индексы и количество строк, которые MySQL должен выполнить, чтобы успешно вернуть набор результатов.
2. Это может помочь нам проанализировать оператор выбора, сообщить нам причину неэффективности запроса, чтобы улучшить наш запрос, чтобы оптимизатор запросов мог работать лучше.
Роль оптимизатора запросов:
1. Оптимизируйте оператор select, проанализируйте, какие выражения являются константами (например, id=1), и проанализируйте, какие выражения можно напрямую преобразовать в константы.
2. Упрощение и преобразование условий, таких как удаление бесполезных условий, корректировка структуры условий и т. д.
3. Прочтите статистику вовлеченных таблиц и выполните анализ (например, количество возвращенных строк, информацию об индексе и т. д.) и, наконец, получите план выполнения.
4. Информация, содержащаяся в Плане выполнения (QEP)
- id: идентификатор, указывающий порядок выполнения
- выберите _type: тип запроса
- Таблица: таблица, на которую ссылаются выходные строки
- разделы: какой раздел использовать, требуется комбинация разделов таблицы, можно увидеть
- тип: указывает, что запрос основан на определенном типе, таком как поиск по типу индекса и поиск по диапазону. Типы соединения от лучшего к худшему: const, eq_reg, ref, range, indexhe и все остальные.
- возможных_ключей: индекс, который можно использовать, имя индекса сохраняется, если существует несколько индексов, разделенных запятыми.
- ключ: фактический используемый индекс, имя индекса сохраняется, если имеется несколько индексов, разделенных запятыми
- key_len: указывает количество байтов в выбранной длине индекса в этом запросе.
- ref: показывает, какой столбец индекса используется, константа, если возможно
- rows: отображает количество строк, которые, по мнению mysql, должны быть возвращены при выполнении запроса.
- Фильтровали: процент от общего числа после условий фильтрации
- Дополнительно: дополнительная информация, например: с использованием сортировки файла, используя где, используя буфер соединения, используя индекс и т. Д.
5. Подробное объяснение каждого параметра в плане выполнения
1.id
Указывает идентификатор выбора и порядок выполнения, то есть идентификатор — это порядковый номер запроса, а порядковый номер запроса — это порядок, в котором выполняется оператор SQL.
(1) Когда значения id совпадают, выполнять по порядку сверху вниз
(2) Когда все идентификаторы разные, выполнять в соответствии с идентификатором от большого к меньшему
(3) Если часть идентификатора отличается, сначала выполните часть с большим идентификатором, а затем выполните часть с тем же идентификатором в порядке сверху вниз.
2.select_type
(1) простой: указывает на простой выбор без объединения и подзапросов.
(2) первичный: самый внешний запрос или основной запрос, в операторе с подзапросом самый внешний запрос выбора является первичным
(3) подзапрос: подзапрос
(4) союз: второй или последний выбор оператора союза
(5) результат объединения: результат после объединения
(6) зависимый unoin: второй или последующий запрос выбора в unoin, который зависит от набора результатов внешнего запроса.
(7) зависимый подзапрос: первый запрос выбора в подзапросе зависит от набора результатов внешнего запроса.
(8) производная: производная таблица (эта не существует в версии 5.7)
3.table
Обычно имя таблицы, или псевдоним таблицы, или маркер, который создает временную таблицу для запроса (например, последняя таблица, подзапрос, коллекция)
4.partitions
Какие разделы использовать (нуль для неразделенных таблиц), в версии 5.5 нужно добавить разделы объяснения, выберите.....
5.type
(1) const: в таблице есть не более одной совпадающей строки, и const используется для сравнения первичного ключа или уникального индекса. Поскольку сопоставляется только одна строка данных, это происходит очень быстро.
(2) eq_ref: сканирование уникального индекса, для каждой записи из предыдущей таблицы считывание уникальной строки из таблицы.
(3) ref: сканирование неуникального индекса, для каждой записи из предыдущей таблицы все совпадающие строки берутся из этой таблицы
(4) ref_or_null: аналогично ref, но вы можете искать строки, содержащие нулевые значения, например: выберите * от студента, где адрес = «xxx» или адрес равен нулю, вам нужно построить индекс по адресу.
(5) index_merge: когда оператор запроса использует несколько индексов таблицы, mysql объединит несколько индексов вместе.
(6) диапазон: очень часто выполняется поиск в соответствии с указанным диапазоном (например, в, , между и и т. д., но только в том случае, если это поле необходимо проиндексировать). Например: выберите * из студента, где идентификатор
(7) индекс: полное сканирование «таблицы», но сканирование в дереве индексов, которое обычно выполняется быстрее, чем ВСЕ, поскольку файл индекса обычно меньше, чем файл данных, сканирование индекса представляет собой сканирование двоичного дерева, и все сканируют физическая таблица. (То есть, хотя и все, и индекс читают всю таблицу, индекс читается из индекса, а все читается с жесткого диска). Например: выбрать имя у студента, но поле имени нужно проиндексировать, то есть запрашиваемое поле принадлежит полю в индексе.
(8) все: полное сканирование таблицы, полное сканирование физической таблицы, затем ее необходимо оптимизировать.
6.possible_keys
Указывает возможные индексы, которые MySQL может использовать в этой таблице. Отображается имя индекса. Несколько индексов разделяются запятыми. Если индекса нет, он равен нулю.
7.key
MySQL определяет фактический используемый индекс и отображает имя индекса.Несколько индексов разделяются запятыми.Если индекса нет, он равен нулю.
8.key_len
При использовании составного индекса определите, используется ли индекс полностью.
Пример: предположим, что в таблице учеников есть поля id int, name char(20) DEFAULT NULL, адрес varchar(20) DEFAULT NULL, примечание varchar(20) NOT NULL, а установленный индекс — idx_address_remark (комбинированный индекс, установленный по адресу и замечание))
SQL запроса: выберите * от студента, где адрес = «Шэньчжэнь» и примечание = «кодировщик Java», в настоящее время key_len в плане выполнения (203+1+2) + (203+2) = 125, так как вы это получили?
Анализ: 20 означает varchar(20) при создании таблицы, 3 означает, что набор символов utf8 занимает 3 байта, 1 означает, что MySQL требуется 1 байт для представления нуля, 2 означает поле переменной длины (varchar имеет переменную длину).
Предполагая удаление только что созданного индекса, создайте новый индекс idx_name_address (создайте комбинированный индекс по имени и адресу)
SQL запроса: выберите * от студента, где имя = «xbq» и адрес = «Шэньчжэнь», в настоящее время key_len в плане выполнения (203+1) + (203+2) = 123, так как же вы получили это значение?
Анализ: 20 означает char(20) при создании таблицы, 3 означает, что набор символов utf8 занимает 3 байта, 1 означает, что MySQL требуется 1 байт для определения нуля, то есть 20*3+1, по той же причине.
key_len вычисляет только длину индекса, используемую условием where, и даже если индекс используется для сортировки и группировки, он не будет вычисляться в key_len.
Формула для вычисления key_len:
поле переменной длины varchr(10) и разрешить NULL = 10 * (набор символов: utf8=3,gbk=2,latin1=1) + 1(NULL) + 2(поле переменной длины)
varchr(10) поле переменной длины и не допускает NULL = 10 *(набор символов: utf8=3, gbk=2, latin1=1) + 2 (поле переменной длины)
Char (10) фиксированное поле и допускает null = 10 * (набор символов: UTF8 = 3, GBK = 2, Latin1 = 1) +1 (NULL)
CHAR (10) Фиксированное поле и запретить null = 10 * (набор символов: utf8 = 3, gbk = 2, latin1 = 1)
9.ref
Показывает, какой столбец индекса используется, константа, если возможно
10.rows
Отображает количество строк, которые, по мнению mysql, должны быть возвращены при выполнении запроса. Его можно комбинировать с анализом типов и ключей. Если индекс не используется, будет выполнено полное сканирование таблицы. Чем меньше значение строк, тем лучше, что указывает на то, что меньше данных извлекается.
11.filtered
Учитывая процентное значение, это процентное значение используется вместе со значением столбца rows для оценки предыдущей таблицы в плане выполнения (предыдущая таблица относится к таблице, значение столбца id которой меньше идентификатора текущей таблицы). )) количество строк для соединения.
Этот столбец находится в версии 5.5, необходимо добавить объяснение расширенного выбора .......
12.extra
В этом поле отображается дополнительная информация, но некоторые значения в этом поле предназначены для оптимальной справки.
(1) Использование того, где: указывает, что запрос использует оператор, где обрабатывать результаты
(2) использование индекса: индикация покрытия индекса. Этот акцент на значение индекса нужно только использовать таблицу поиска для удовлетворения требований, не требует прямого доступа к данным таблицы.
(3) использование буфера соединения: это значение подчеркивает, что при получении условий соединения индекс не используется, а для хранения промежуточных результатов требуется буфер соединения. Если это значение присутствует, следует отметить, что в зависимости от специфики запроса может потребоваться добавление индекса для повышения производительности.
(4) использование файловой сортировки: это результат порядка по оператору. Это может быть процесс с интенсивным использованием ЦП. Использование файловой сортировки указывает на наличие внутрифайловой сортировки, что является очень плохим явлением. Ее необходимо оптимизировать, особенно для больших таблиц. Вы можете повысить производительность, выбрав соответствующий индекс, и использовать индекс для сортировки результатов запроса.
(5) использование временных: MySQL необходимо создать временную таблицу для хранения промежуточных результатов. То есть вам нужно сначала поместить данные во временную таблицу, а затем получить необходимые данные из временной таблицы. Внешний вид такой временной таблицы также является местом, которое необходимо оптимизировать, особенно в случае большого объема данных. Две распространенные причины: использование разных столбцов из разных таблиц или использование разного порядка и группировки по столбцам.
Добро пожаловать, чтобы обратить внимание на мою официальную учетную запись и получать последние статьи как можно скорее ~ Выполните поиск в официальной учетной записи: Code Cafe или отсканируйте QR-код ниже: