В этом учебнике описывается, как использовать отчеты Explain для поиска и исправления проблемных (медленных) запросов. Он использует базу данных DBT-3 и начинается с простого примера запроса.

SELECT * FROM orders
WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4
AND o_clerk LIKE '%0223';

Как показано на рисунке ниже, пример запроса был сначала выполнен в визуальном редакторе SQL. Затем отчет Explain был сгенерирован щелчком по Explain Current Statement в меню Query. Начальный отчет отображает визуальное изображение Explain с информацией, которая появляется, когда вы перемещаете указатель над таблицей orders в полном сканировании таблицы.

Рисунок 7.9 Учебник по Explain DBT-3: Визуальное Explain с полным сканированием таблицы

По желанию, можно переключиться на табличное Explain, как показано на следующем рисунке. Используйте раскрывающийся список для переключения между визуальным и табличным представлениями.

Рисунок 7.10 Учебник по Explain DBT-3: Табличное Explain с полным сканированием таблицы

Вопросы по запросу:

Более подробно, обратите внимание, что индексированный столбец используется в выражении как "WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4", поэтому индекс не используется. Для использования существующего индекса можно скорректировать запрос следующим образом.

SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE '%0223';

Обновленный пример запроса приводит к визуальному изображению Explain, в котором Index Range Scan заменяет Full Table Scan, сгенерированное последним примером запроса. Два следующих рисунка показывают визуальное и табличное представление измененного примера запроса.

Рисунок 7.11 Учебник по Explain DBT-3: Визуальное Explain с индексным диапазонным сканированием

Рисунок 7.12 Учебник по Explain DBT-3: Табличное Explain с индексным диапазонным сканированием

Обратите внимание на различия. Тип изменился с ALL на range, возможные ключи (и используемый ключ) изменились с NULL на i_o_orderdate, а количество просканированных строк изменилось с 1,5 миллиона до примерно 33 тысяч. Тем не менее, сканирование 33 тысяч строк при возвращении всего 18 избыточно, поэтому внимание можно переключить на столбец o_clerk. Следующий пример запроса (и табличный рисунок Explain) добавляет следующий индекс, который должен улучшить производительность.

CREATE INDEX i_o_clerk ON orders(o_clerk);

Рисунок 7.13 Учебник по Explain DBT-3: Табличное Explain с индексным диапазонным сканированием и после индекса

Новый индекс не рассматривается как возможный ключ, потому что запрос ищет суффикс столбца o_clerk, а индексы не работают с суффиксами (хотя они работают с префиксами). Вместо этого этот простой пример мог бы использовать весь идентификатор сотрудника. Корректировка запроса следующим образом показывает лучшие результаты.

SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';

Следующие рисунки представляют эффект обновленного примера запроса в визуальном Explain и табличном Explain соответственно.

Рисунок 7.14 Учебник по Explain DBT-3: Визуальное Explain с индексным диапазонным сканированием и полным ID

Рисунок 7.15 Учебник по Explain DBT-3: Табличное Explain с индексным диапазонным сканированием и полным ID

Новый индекс o_clerk был учтен и использован, и запрос просканировал 1546 строк вместо 32642, а выполнение запроса улучшилось с 0,281 до 0,234 секунды. Однако EXPLAIN оценивает, что этот запрос просканирует 1546 строк для возвращения 18. После повторного анализа запроса следует учесть, что индекс из нескольких столбцов может удовлетворить условиям предложения WHERE, которое основано на столбцах o_orderdate и o_clerk, как показано в следующем операторе.

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)

Теперь выполнение скорректированного запроса дает еще лучшие результаты. Оценивается, что 18 строк будут просканированы и возвращены, а время выполнения примера запроса составляет 0,234 секунды, как показано на следующих рисунках визуального Explain и табличного Explain.

Рисунок 7.16 Учебник по Explain DBT-3: Визуальное Explain с индексным диапазонным сканированием по нескольким столбцам

Рисунок 7.17 Учебник по Explain DBT-3: Табличное Explain с индексным диапазонным сканированием по нескольким столбцам

Следующая таблица обобщает результаты модификаций запроса, выполненных в этом учебнике.