5.2. Оптимизация SELECT и других запросов
5.2.1. Синтаксис оператора EXPLAIN (получение информации о SELECT)

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_keyskey  key_len ref  rows  Extra
etALL  PRIMARYNULL NULLNULL 74
doALL  PRIMARYNULL NULLNULL 2135
et_1  ALL  PRIMARYNULL NULLNULL 74
ttALL  AssignedPC,ClientID,ActualPC NULL NULLNULL 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 rowsExtra
ttALLAssignedPC,ClientID,ActualPC NULL NULL NULL 3872Using where
doALLPRIMARY NULLNULLNULL2135
range checked for each record (key map: 1)
et_1  ALLPRIMARY NULLNULLNULL74
range checked for each record (key map: 1)
eteq_ref PRIMARY PRIMARY 15tt.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   keykey_len ref   rows Extra
etALLPRIMARY NULL NULLNULL  74
ttrefAssignedPC, ActualPC 15et.EMPLOYID   52   Using where
 ClientID,
 ActualPC
et_1  eq_ref PRIMARY PRIMARY  15tt.AssignedPC 1
doeq_ref PRIMARY PRIMARY  15tt.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
ttALLAssignedPCNULLNULLNULL  3872 Using where
 ClientID,
 ActualPC
eteq_ref PRIMARY PRIMARY 15tt.ActualPC   1
et_1  eq_ref PRIMARY PRIMARY 15tt.AssignedPC 1
doeq_ref PRIMARY PRIMARY 15tt.ClientID   1

Обратите внимание: столбец rows в выводе оператора EXPLAIN - опытное предположение оптимизатора связей MySQL. Чтобы оптимизировать запрос, нужно проверить, являются ли числа близкими к действительным. Если нет, можно получить лучшую производительность, используя в операторе SELECT соединение STRAIGHT_JOIN и попытаться задать другой порядок таблиц в выражении FROM.