Анализ запросов: EXPLAIN онлайн урок.


Оператор EXLPAIN используется для анализа запросов. При помощи этого оператора можно увидеть какие запросы не являются оптимальными и оптимизировать их.

Для получения информации о запросе необходимо записать оператор EXPLAIN прямо перед самим запросом, например

EXPLAIN SELECT * FROM student ;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 8 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.14 sec)

Как результат, мы получаем таблицу, в которой названия столбцов - это названия параметров для анализа запроса id, select_type, table, type ...

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

EXPLAIN SELECT * FROM student \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra: NULL
1 row in set (0.00 sec)

Теперь разберем предназначение этих данных.

  • id - номер строки в таблице EXPLAIN, строк будет столько, сколько операторов SELECT в запросе.

  • select_type - это тип запроса. Он говорит о том, является ли запрос простым (как в нашем случае) или же, к примеру, вложенным - когда используются подзапросы (SUBQUERY). Среди возможных значений также могут быть:

    • PRIMARY - внешний запрос при соединении JOIN

    • DERIVED - запрос является подзапросом в предложении FROM

    • DEPENDENT SUBQUERY - подзапрос SELECT, который зависит от подзапроса

    • UNION - запрос, который записан после оператора UNION

    • DEPENDENT UNION - запрос, который записан после оператора UNION и зависит от подсапроса

    • UNION RESULT - результирующий запрос SELECT, в котором есть UNION



  • table - таблица, которая использована для запроса. Значения могут совпадать с существующими таблицами, либо могут принимать специальные значения, например <derived> если был записан запрос в предложении FROM или <union> если был использован оператор UNION.

  • type - то как система осуществляет соединение таблиц. Иногда говорят, что это то, как осуществляется доступ к значениям в таблице. Например, производился поиск по всей таблице, либо же по определенному интервалу. Или же поиск производился исключительно по индексу. Перечислим некоторые значения (от лучших к худшим):

    • system - таблица имеет не более 1 строки.

    • const - таблица содержит не более 1 совпадения по запросу. При этом критерий поиска был составлен из индексов и в нем использовались лишь постоянные величины.

    • eq_ref - при соединении таблицы были использованы индексы PRIMARY или UNIQUE NOT NULL

    • ref - при соединении таблицы были использованы индексы

    • unique_subquery - это аналог ref, когда подзапрос в IN возвращает один результат при помощи индекса

    • index_subquery - аналог unique_subquery, но результатов больше чем 1.

    • range - поиск проводился в индексе по определенному промежутку. Например, при использовании операторов сравнения.

    • all - была использована вся таблица для поиска записей. Это наиболее плохой результат.



  • possible_keys - допустимые индексы для поиска записей

  • keys - индексы, которые были использованы для запроса

  • key_len - количество байт, которое занимает индекс

  • ref - столбцы которые сравнивались с индексами в key

  • rows - количество строк таблицы, которые исследованы для результата. Очевидно, что чем меньше это число - тем лучше.

  • extra - дополнительная информация про запрос.


Рассмотрим несколько типичных запросов.

1. Вывести студента по id

 EXPLAIN SELECT * FROM student WHERE id > 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.01 sec)

EXPLAIN SELECT * FROM student WHERE id = 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)

2. Вывести студентов по группам - JOIN

EXPLAIN SELECT * FROM student s JOIN `group` g ON s.group_id = g.id  \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: g
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ref
possible_keys: group_id
key: group_id
key_len: 2
ref: my_db.g.id
rows: 1
Extra: NULL
2 rows in set (0.00 sec)

3. Подзапрос
EXPLAIN SELECT * FROM student WHERE group_id IN (SELECT id FROM `group` WHERE id > 1 )  \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: group
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: NULL
rows: 3
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ref
possible_keys: group_id
key: group_id
key_len: 2
ref: my_db.group.id
rows: 1
Extra: NULL
2 rows in set (0.01 sec)