EXPLAIN имя_таблицы или EXPLAIN SELECT опции_выборки
EXPLAIN имя_таблицы
является синонимом операторов DESCRIBE имя_таблицы
и
SHOW COLUMNS FROM имя_таблицы
.
Если оператор SELECT
предваряется ключевым словом EXPLAIN
, MySQL сообщит о
том, как будет производиться обработка SELECT
, и предоставит информацию о
порядке и методе связывания таблиц.
При помощи EXPLAIN
можно выяснить, когда стоит снабдить таблицы индексами,
чтобы получить более быструю выборку, использующую индексы для поиска
записей.
Вы должны почаще запускать ANALYZE TABLE
с тем, чтобы обновлялась статистика по
таблицам, такая как кардинальность ключей, которые могут повлиять на выбор оптимизатора.
See Раздел 4.5.2, «Синтаксис команды ANALYZE TABLE
».
Можно проверить, насколько удачный порядок связывания
таблиц был выбран оптимизатором. Заставить оптимизатор связывать таблицы в
заданном порядке можно при помощи указания STRAIGHT_JOIN
.
Для непростых соединений EXPLAIN
возвращает строку информации о каждой из
использованных в работе оператора SELECT
таблиц. Таблицы перечисляются в
том порядке, в котором они будут считываться.
MySQL выполняет все связывания за один проход (метод называется
"single-sweep multi-join"). Делается это так: MySQL читает строку из
первой таблицы, находит совпадающую строку во второй таблице, затем - в
третьей, и так далее. Когда обработка всех таблиц завершается, MySQL
выдает выбранные столбцы и обходит в обратном порядке список таблиц до тех
пор, пока не будет найдена таблица с наибольшим совпадением строк.
Следующая строка считывается из этой таблицы и процесс продолжается в
следующей таблице.
В MySQL 4.1, вывод EXPLAIN
был изменен с тем, чтобы работать лучше с конструкциями типа
UNION
, подзапросами, и наследованными (вторичными, derived) таблицами.
Наиболее заметным изменением стало введение двух новых столбцов: id
и select_type
.
Вывод команды EXPLAIN
включает следующие столбцы:
-
id
Идентификатор
SELECT
, последовательный номер этого конкретногоSELECT
в запросе. -
select_type
Тип оператора
SELECT
, который может быть один из следующих:-
SIMPLE
Простая выборка (
SELECT
безUNION
или подзапросов). -
PRIMARY
Крайний
SELECT
. -
UNION
Второй и дальнейшие
UNION SELECT
. -
DEPENDENT UNION
Второй и дальнейшие
UNION SELECT
, зависящие от внешнего подзапроса. -
SUBSELECT
Первый
SELECT
в подзапросе. -
DEPENDENT SUBSELECT
Первый
SELECT
, зависящий от внешнего подзапроса. -
DERIVED
Наследованная (вторичная) таблица
SELECT
.
-
-
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
Столбец
possible_keys
служит для указания индексов, которые может использовать MySQL для нахождения строк в этой таблице. Обратите внимание: этот столбец полностью независим от порядка таблиц. Это означает, что на практике некоторые ключи в столбцеpossible_keys
могут не годиться для сгенерированного порядка таблиц.Если данный столбец пуст, то никаких подходящих индексов не имеется. В этом случае для увеличения производительности следует исследовать выражение
WHERE
, чтобы увидеть, есть ли в нем ссылки на какой-либо столбец (столбцы), которые подходили бы для индексации. Если да, создайте соответствующий индекс и снова проверьте запрос при помощи оператораEXPLAIN
. See Раздел 6.5.4, «Синтаксис оператораALTER TABLE
».Чтобы увидеть, какие индексы есть в таблице, используйте команду
SHOW INDEX FROM имя_таблицы
. -
key
Столбец
key
содержит ключ (индекс), который MySQL решил использовать в действительности. Если никакой индекс не был выбран, ключ будет иметь значениеNULL
. Чтобы заставить MySQL применить индекс изpossible_keys
, следует использовать операторUSE INDEX/IGNORE INDEX
в запросе. See Раздел 6.4.1, «Синтаксис оператораSELECT
».Также, выполнение
myisamchk --analyze
(see Раздел 4.4.6.1, «Синтаксис запускаmyisamchk
») илиANALYZE TABLE
(see Раздел 4.5.2, «Синтаксис командыANALYZE TABLE
») по таблицам даст возможность оптимизатору принимать более правильные решения. -
key_len
Столбец
key_len
содержит длину ключа, которую решил использовать MySQL. Еслиkey
имеет значениеNULL
, то длина ключа (key_len
) тожеNULL
. Обратите внимание: по значению длины ключа можно определить, сколько частей составного ключа в действительности будет использовать MySQL. -
ref
Столбец
ref
показывает, какие столбцы или константы используются с ключом, указанным вkey
, для выборки строк из таблицы. -
rows
В столбце
rows
указывается число строк, которые MySQL считает нужным проанализировать для выполнения запроса. -
Extra
Этот столбец содержит дополнительную информацию о том, как MySQL будет выполнять запрос. Ниже объясняется значение каждой из текстовых строк, которые могут находиться в этом столбце:
-
Distinct
После нахождения первой совпадающей строки MySQL не будет продолжать поиск строк для текущей комбинации строк.
-
Not exists
MySQL смог осуществить оптимизацию
LEFT JOIN
для запроса и после нахождения одной строки, соответствующей критериюLEFT JOIN
, не будет искать в этой таблице последующие строки для предыдущей комбинации строк.Например:
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
. -
Using where
Выражение
WHERE
будет использоваться для выделения тех строк, которые будут сопоставляться со следующей таблицей или тех, которые будут посланы клиенту. Если этой информации нет, а таблица имеет типALL
илиindex
, то, значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь делать выборку/тестирование всех строк таблицы).
Если нужно, чтобы запросы выполнялись настолько быстро, насколько это возможно, посмотрите, есть ли строки упоминания
Using filesort
иUsing temporary
. -
Существует неплохой способ определить, насколько хорошим является тип
связывания. Для этого нужно перемножить все значения столбца rows
,
выводимого командой EXPLAIN
. Результатом будет грубая оценка того, сколько
строк должен просмотреть MySQL для выполнения запроса. Это же число
используется для ограничения запросов в переменной max_join_size
.
See Раздел 5.5.2, «Настройка параметров сервера».
В следующем примере показано, как можно постепенно оптимизировать JOIN
при
помощи информации, выводимой оператором EXPLAIN
.
Предположим, что имеется представленный ниже оператор SELECT
, который
нужно исследовать при помощи команды EXPLAIN
:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;
Для этого примера принимается, что:
-
Сравниваемые столбцы были объявлены следующим образом:
Таблица Столбец Тип столбца tt
ActualPC
CHAR(10)
tt
AssignedPC
CHAR(10)
tt
ClientID
CHAR(10)
et
EMPLOYID
CHAR(15)
do
CUSTNMBR
CHAR(15)
-
Таблицы проиндексированы следующим образом:
Таблица Индекс tt
ActualPC
tt
AssignedPC
tt
ClientID
et
EMPLOYID
(primary key)do
CUSTNMBR
(primary key) Значения
tt.ActualPC
распределены не равномерно.
На начальном этапе перед выполнением какой-либо оптимизации оператор
EXPLAIN
выведет следующую информацию:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked for each record (key map: 35)
Поскольку каждая таблица имеет тип (type
) ALL
, из приведенного выше вывода видно,
что MySQL будет делать полное связывание всех таблиц! Это займет долгое
время, поскольку для выполнения такого связывания должно быть рассмотрено
произведение числа строк в каждой таблице! Для нашего случая такое
произведение - 74 * 2135 * 74 * 3872 = 45268558720 строк. Если таблицы
большие, трудно даже представить себе, как долго они будут связываться.
Одна проблема здесь состоит в том, что MySQL не может (пока еще)
эффективно применять индексы к столбцам, если они объявлены по-разному. В
этом контексте тип VARCHAR
и тип CHAR
- одинаковы, если они не объявлены с
различной длиной. Поскольку столбец tt.ActualPC
объявлен как CHAR(10)
, а
et.EMPLOYID
- как CHAR(15)
, имеется несоответствие по длине значений.
Чтобы устранить это несоответствие между длинами столбцов, следует
использовать команду ALTER TABLE
для удлинения столбца ActualPC
от 10
символов до 15 символов:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
Теперь оба столбца tt.ActualPC
и et.EMPLOYID
имеют тип VARCHAR(15)
. При
повторном выполнении оператора EXPLAIN
будет выведен следующий результат:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 Using where do ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1) et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
Это не идеально, но уже намного лучше (произведение значений строк (rows
) теперь
уменьшилось в 74 раза). Такое связывание выполнится за пару секунд.
Можно сделать еще одно изменение - чтобы устранить несоответствие длин
столбцов для сравнений tt.AssignedPC = et_1.EMPLOYID
и tt.ClientID = do.CUSTNMBR
.
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), -> MODIFY ClientID VARCHAR(15);
Теперь оператор EXPLAIN
будет выводить такую информацию:
table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using where ClientID, ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Это почти идеально.
Осталась еще одна проблема. Она заключается в том, что по умолчанию MySQL
принимает, что значения в столбце tt.ActualPC
распределены равномерно, но
в таблице tt
это не так. К счастью, проинформировать MySQL об этом можно
очень просто:
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt shell> mysqladmin refresh
Теперь связывание совершенно, и оператор EXPLAIN
выведет такой результат:
table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 Using where ClientID, ActualPC et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Обратите внимание: столбец rows в выводе оператора EXPLAIN
- опытное
предположение оптимизатора связей MySQL. Чтобы оптимизировать запрос,
нужно проверить, являются ли числа близкими к действительным. Если нет,
можно получить лучшую производительность, используя в операторе SELECT
соединение STRAIGHT_JOIN
и попытаться задать другой порядок таблиц в
выражении FROM
.