Декодирование результатов запроса MySQL EXPLAIN для повышения производительности (часть 2)

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

* Столбец type предоставляет информацию о том, как таблицы соединяются. Различные типы type описаны ниже.

Я использовал две таблицы student_details и таблицу address. Я приложил структуру таблицы и соответствующую структуру индекса в конце этого блога.

* const: если type имеет значение const, это означает, что существует не более одной соответствующей строки. Поскольку максимальное количество совпавших строк равно единице, оно считается константой. Когда мы использовали все первичные ключи (если первичный ключ является составным первичным ключом) или все «уникальные» индексы для сравнения с постоянным значением для запроса данных, тогда тип был const< /сильный>. Например, в приведенном ниже запросе я использовал уникальный индекс (класс, roll_no) для запроса данных.

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12 and class = 4;

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

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12;

Здесь сканируются все строки таблиц student_detail, потому что roll_no не индексируется (индексируется class, roll_no, но не только roll_no. MySql может использовать class или class и roll_no для индекса, но не только roll_no). В таблице адресов есть только одна строка для каждой комбинации в таблице student_detail (мы использовали идентификатор адреса для объединения, и это первичный ключ в таблице адресов).

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

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE state = 'Karnataka'; 

состояние не является первичным или уникальным индексом, поэтому тип таблицы адресов — ref, поскольку для каждого состояния может быть несколько строк.

EXPLAIN SELECT * FROM address ad WHERE ad.state = 'Karnataka';

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

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

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

* Если индекс является покрывающим индексом для запросов и может использоваться для получения всех данных, требуемых из таблицы, сканируется только дерево индексов. В этом случае в столбце «Дополнительно» указано «Использование индекса». Сканирование только индекса обычно выполняется быстрее, чем сканирование ALL, поскольку размер индекса обычно меньше размера данных таблицы.

* Полное сканирование таблицы выполняется с использованием чтения из индекса для поиска строк данных в порядке индекса. Использование индекса не отображается в столбце «Дополнительно».

EXPLAIN SELECT class FROM student_detail;

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

ВСЕ: полное сканирование таблицы выполняется для каждой комбинации строк из предыдущих таблиц. Обычно это нехорошо, если таблица является первой таблицей, не помеченной константой, и обычно очень плохо во всех остальных случаях. Обычно вы можете избежать ALL, добавив индексы, которые позволяют извлекать строки из таблицы на основе постоянных значений или значений столбцов из более ранних таблиц.

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

В следующем блоге я расскажу, как использовать вывод EXPLAIN для оптимизации запроса.

Примечание.

структура таблицы student_detail

индексы student_details

структура таблицы адресов

индексы таблицы адресов

Также опубликовано здесь