Использование оператора EXPLAIN

Для того чтобы посмотреть, как будет выполняться запрос на выборку, используется оператор EXPLAIN:

EXPLAIN SELECT опции_выборки.

В этом случае MySQL сообщит о том, как будет производиться обработка SELECT, и предоставит информацию о порядке и методе связывания таблиц. Для непростых соединений EXPLAIN возвращает строку информации о каждой из использованных в работе оператора SELECT таблиц. Таблицы перечисляются в том порядке, в котором они будут считываться. MySQL выполняет все связывания за один проход (метод называется "single-sweep multijoin").

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

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

Вывод команды EXPLAIN включает следующие столбцы:

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

type -тип связывания. (Ниже перечислены различные типы связывания,

упорядоченные от лучшего к худшему.)

• system

Информация из таблицы читается один раз (системная таблица). Это – частный случай типа связывания const.

• const

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

• eq_ref

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

• ref

Из этой таблицы будут считываться все строки с совпадающими значениями индексов для каждой комбинации строк из предыдущих таблиц. Тип ref применяется, если для связывания используется только крайний левый префикс ключа, или если ключ не является UNIQUE или PRIMARY KEY (на основании значения ключа для связывания не может быть выбрана одна строка). Этот тип связывания хорошо работает, если используемый ключ соответствует только нескольким строкам.

• range

При помощи индекса для выборки строк будут извлечены только строки, находящиеся в заданном диапазоне. Используемый индекс указывается в столбце key. Столбец key_len содержит самую длинную часть ключа, которая была использована. Столбец ref будет содержать значения NULL для этого типа.

• index

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

• ALL

Сканируется каждая строка в таблице. Для каждой комбинации строк из

предыдущих таблиц будет производиться полный просмотр этой таблицы. Это обычно плохо, если таблица - первая из не отмеченных как const, и очень плохо во всех остальных случаях. Как правило, можно избегать типа связывания ALL - путем добавления большего количества индексов таким образом, чтобы строка могла быть найдена при помощи константных значений или значений столбца из предыдущих таблиц. possible_keysслужит для указания индексов, которые может использовать MySQL для нахождения строк в таблице. Этот столбец полностью независим от порядка таблиц. Если данный столбец пуст, никаких подходящих индексов нет, и для увеличения производительности необходимо исследовать выражение WHERE, чтобы определить наличие ссылок на какой-либо столбец (столбцы), подходящие для индексации. В этом случае следует создать соответствующий индекс и снова проверить запрос с помощью EXPLAIN. ( Чтобы увидеть, какие индексы есть в таблице, используйте команду SHOW INDEX FROM имя_таблицы. )

keyсодержит ключ (индекс), который MySQL решил использовать в действительности. Если никакой индекс не был выбран, ключ будет иметь значение NULL. Если был выбран неверный индекс, можно попробовать заставить MySQL применить другой индекс. Для этого следует использовать myisamchk -analyze или оператор USE INDEX/IGNORE INDEX. key_lenсодержит длину ключа, которую решил использовать MySQL. Если key имеет значение NULL, то длина ключа (key_len) тоже NULL. (По значению длины ключа можно определить, сколько частей составного ключа в действительности будет использовать MySQL.)

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

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

Extraсодержит дополнительную информацию о том, как MySQL будет выполнять запрос в виде одной из текстовых строк:

• Distinct- после нахождения первой совпадающей строки MySQL не будет продолжать поиск строк для текущей комбинации строк.

• Not existsосуществлена оптимизация LEFT JOIN для запроса, и после нахождения одной строки, соответствующей критерию LEFT JOIN, MySQL не будет искать в этой таблице последующие строки для предыдущей комбинации строк.

Например:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; Предположим, что столбец t2.id определен как NOT NULL. В этом случае MySQL просмотрит таблицу t1 и будет искать строки в t2 соответствующие t1.id. Если MySQL находит в t2 нужную строку, он знает, что t2.id никогда не может иметь значение NULL, и не будет искать в t2 оставшуюся часть строк, имеющих тот же самый id. Другими словами, для каждой строки в t1 MySQL должен выполнить только один поиск в t2, независимо от того, сколько совпадающих строк содержится в t2.

range checked for each record (index map: #)

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

• Using filesort

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

• Using index

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

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

• Using temporary

Чтобы выполнить запрос, MySQL должен будет создать временную таблицу для хранения результата. Это обычно происходит, если предложение ORDER BY выполняется для набора столбцов, отличного от того, который используется в предложении GROUP BY.

• Where used

Выражение WHERE будет использоваться для выделения тех строк, которые будут сопоставляться со следующей таблицей или тех, которые будут посланы клиенту. Если этой информации нет, а таблица имеет тип ALL или index, то, значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь делать выборку/тестирование всех строк таблицы).

Если нужно, чтобы запросы выполнялись настолько быстро, насколько это возможно, посмотрите, есть ли строки упоминания Using filesort и Using temporary.