поиск:
Полезные ссылки

  • Форум по MySQL

  • Статьи по MySQL

  • Вопросы по MySQL

  • MySQL.com


  • Базы данных

  • MySQL

  • PostgreSQL


  • PHP конференция 2005
    Подробности!

    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_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.

     
    © 1997-2005 PHP Club Team
    Rambler's Top100