Пример использования оператора EXPLAIN.

В следующем примере показано, как можно постепенно оптимизировать 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;

Для этого примера принимается, что:

o Сравниваемые столбцы были объявлены следующим образом:

Таблицы проиндексированы следующим образом:

o Значения 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 символов:

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

Можно сделать еще одно изменение - чтобы устранить несоответствие длин столбцов для сравнений tt.AssignedPC = et_1.EMPLOYID and tt.ClientID =

do.CUSTNMBR.

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