SQL стейтмент EXPLAIN

Материал из Энциклопедия о программировании
Перейти к: навигация, поиск

SQL стейтмент EXPLAIN (объяснять, разъяснять, растолковать, толковать) показывает информацию о выполнении СУБД запроса SQL с оператором SELECT, или же структуру созданных таблиц БД.

Внимание! EXPLAIN — внутренний для MySQL, т.е. относящийся сугубо к этой СУБД, SQL оператор/стейтмент, не входящий в общую спецификацию языка SQL.
Примечание: Операторы DESCRIBE и EXPLAIN являются синонимами. На практике ключевое слово DESCRIBE используется чаще для получения информации о структуре таблицы, то время как EXPLAIN используется для получения информации выполнения запроса (то есть, объяснение того, как MySQL будет выполнять запрос). Однако MySQL анализатор DESCRIBE и EXPLAIN рассматривает полностью как синонимы.

Синтаксис

Облегчённый

EXPLAIN имя_таблицы

или

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

Полный:

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [EXTENDED] SELECT select_options

Описание

EXPLAIN имя_таблицы является синонимом операторов DESCRIBE имя_таблицы и SHOW COLUMNS FROM имя_таблицы.

Если оператор SELECT предваряется ключевым словом EXPLAIN, MySQL сообщит о том, как будет производиться обработка SELECT, и предоставит информацию о порядке и методе связывания таблиц.

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

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

Результат

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

id
Идентификатор/номер запроса. Может быть несколько, когда запрос содержит подзапросы.
select_type
Тип выборки/запроса: PRIMARY - первичный/главный, SUBQUERY - подзапрос.
table
Таблица, к которой относится выводимая строка.
partitions
Используемые партиции.
type
Тип связывания. Информация о различных типах связывания представлена ниже.
possible_keys
Столбец possible_keys служит для указания индексов, которые может использовать MySQL для нахождения строк в этой таблице. Обратите внимание: столбец полностью независим от порядка таблиц. Это означает, что на практике некоторые ключи в столбце possible_keys могут не годиться для сгенерированного порядка таблиц. Если данный столбец пуст, то никаких подходящих индексов не имеется. В этом случае для увеличения производительности следует исследовать выражение WHERE, чтобы увидеть, есть ли в нем ссылки на какой-либо столбец (столбцы), которые подходили бы для индексации. Если да, создайте соответствующий индекс и снова проверьте запрос при помощи оператора EXPLAIN.
Чтобы увидеть, какие индексы есть в таблице, используйте команду SHOW INDEX FROM имя_таблицы.
key
Столбец key содержит ключ (индекс), который MySQL решил использовать в действительности. Если никакой индекс не был выбран, ключ будет иметь значение NULL. Если был выбран неверный индекс, можно попробовать заставить MySQL применить другой индекс. Для этого следует использовать myisamchk -analyze или оператор USE INDEX/IGNORE INDEX.
key_len
Столбец key_len содержит длину ключа, которую решил использовать MySQL. Если key имеет значение NULL, то длина ключа (key_len) тоже NULL. Обратите внимание: по значению длины ключа можно определить, сколько частей составного ключа в действительности будет использовать MySQL.
ref
Столбец ref показывает, какие столбцы или константы используются с ключом, указанным в key, для выборки строк из таблицы.
rows
В столбце rows указывается число строк, которые MySQL считает нужным проанализировать для выполнения запроса.
filtered
Процент строк от общего числа строк, которые будут проверяться для получения данных. Т.е. это просто процентное отношение выбранных строк (их количество указано в rows) от общего количества строк в таблице.
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.
Where used
Выражение WHERE будет использоваться для выделения тех строк, которые будут сопоставляться со следующей таблицей или тех, которые будут посланы клиенту. Если этой информации нет, а таблица имеет тип ALL или index, то, значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь делать выборку/тестирование всех строк таблицы).
No tables used
Таблицы не использовались. Бывает, напр., когда внешний запрос не использует таблицы, а работает только с результатами подзапросов.

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

Типы связывания

Различные типы связывания, упорядоченные от лучшего к худшему:

system
Таблица содержит только одну строку (= системная таблица). Это - частный случай типа связывания const.
const
Таблица содержит не более одной соответствующей строки, которая будет считываться в начале запроса. Поскольку имеется только одна строка, оптимизатор в дальнейшем может расценивать значения этой строки в столбце как константы. Таблицы const являются очень быстрыми, поскольку они читаются только однажды!
eq_ref
Для каждой комбинации строк из предыдущих таблиц будет считываться одна строка из этой таблицы. Это наилучший возможный тип связывания среди типов, отличных от const. Данный тип применяется, когда все части индекса используются для связывания, а сам индекс - UNIQUE или PRIMARY KEY.
ref
Из этой таблицы будут считываться все строки с совпадающими значениями индексов для каждой комбинации строк из предыдущих таблиц. Тип ref применяется, если для связывания используется только крайний левый префикс ключа, или если ключ не является UNIQUE или PRIMARY KEY (другими словами, если на основании значения ключа для связывания не может быть выбрана одна строка). Этот тип связывания хорошо работает, если используемый ключ соответствует только нескольким строкам.
range
При помощи индекса для выборки строк будут извлечены только строки, находящиеся в заданном диапазоне. Используемый индекс указывается в столбце key. Столбец key_len содержит самую длинную часть ключа, которая была использована. Столбец ref будет содержать значения NULL для этого типа.
index
Данный тип аналогичен ALL, за исключением того, что просматривается только дерево индексов. Этот тип обычно более быстрый чем ALL, поскольку индексный файл, как правило, меньше файла данных.
ALL
Для каждой комбинации строк из предыдущих таблиц будет производиться полный просмотр этой таблицы. Это обычно плохо, если таблица - первая из не отмеченных как const, и очень плохо во всех остальных случаях. Как правило, можно избегать типа связывания ALL - путем добавления большего количества индексов таким образом, чтобы строка могла быть найдена при помощи константных значений или значений столбца из предыдущих таблиц.

Заметки

Существует неплохой способ определить, насколько хорошим является тип связывания. Для этого нужно перемножить все значения столбца rows, выводимого командой EXPLAIN. Результатом будет грубая оценка того, сколько строк должен просмотреть MySQL для выполнения запроса. Это же число используется для ограничения запросов в переменной max_join_size.

Далее показано как можно постепенно оптимизировать 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;

Принимается, что:

  1. Сравниваемые столбцы были объявлены следующим образом:
    ТаблицаСтолбецТип столбца
    ttActualPCCHAR(10)
    ttAssignedPCCHAR(10)
    ttClientIDCHAR(10)
    etEMPLOYIDCHAR(15)
    doCUSTNMBRCHAR(15)
  2. Таблицы проиндексированы следующим образом:
    ТаблицаИндекс
    ttActualPC
    ttAssignedPC
    ttClientID
    etEMPLOYID (primary key)
    doCUSTNMBR (primary key)
  3. Значения 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)

Поскольку каждая таблица имеет тип 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    where used
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

Это не идеально, но уже намного лучше - произведение значений строк теперь уменьшилось в 74 раза. Такое связывание выполнится за пару секунд.

Можно сделать ещё одно изменение, чтобы устранить несоответствие длин столбцов для сравнений tt.AssignedPC = et_1.EMPLOYID and 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   where used
             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 where used
             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.

Примеры

Этот раздел также можно назвать, или альтернативные заголовки раздела:
  • Примеры использования EXPLAIN

Получим информацию о таблице City:

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+