MySQL EXPLAIN 解读

EXPLAIN 解释了 MySQL 是如何执行 SQL 语句的。使用的方法很简单,在 SQL 语句前加上 EXPLAIN 关键字就可以。
下面是一个简单的例子,测试数据在文章末尾。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 例 1
mysql> EXPLAIN SELECT name FROM users WHERE id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)

EXPLAIN 列的解释:

  • id:SELECT 标识符,下面具体分析
  • select_type: SELECT 类型,下面会具体分析
  • table: 查询所使用的表
  • type: JOIN 的类型,下面会具体分析
  • possible_keys: 可能使用的索引,但不一定会真正使用
  • key: 真正使用的索引
  • key_len: 所使用的索引长度
  • ref: 与索引比较的列
  • rows: 预估需要扫描的行数
  • Extra: 额外信息

id

个人理解表示了 SELECT 的执行顺序。id 大的优先执行,id 相同的从上往下执行。

select_type

select_type 表示查询的类型,具体种类见官方图表。
select_type.png-来自官方文档
SIMPLE 是最常见的种类,表示它未使用 UNION 及子查询。例 1 的查询就属于 SIMPLE。

当使用了关键字 UNION,查询的类型就会发生变化。
select_union.png-41.3kB

在这个查询中,我们可以看到 3 种类型的查询。 PRIMARY 表示最外层的查询,也就是 UNION 之前的 SELECT name FROM users WHERE id = 1。UNION 之后的 SELECT name FROM users WHERE id = 2 归为 UNION 类型。最后 UNION RESULT 将两次查询的结果归总。

下面是其他查询类型的例子。

PRIMARY & SUBQUERY

PRIMARY 为最外层的查询,而 SUBQUERY 则指子查询。

1
EXPLAIN SELECT * FROM users WHERE id = (SELECT user_id FROM orders WHERE id = 3);

PRIMARY & DEPENDENT SUBQUERY

1
EXPLAIN SELECT * FROM users WHERE EXISTS (SELECT user_id FROM orders WHERE orders.id = 3 and orders.user_id = users.id);

MATERIALIZED

1
EXPLAIN SELECT DISTINCT user_id FROM orders WHERE id IN (SELECT DISTINCT order_id FROM order_items WHERE product_name = 'p1');

DERIVED

1
EXPLAIN SELECT * FROM (SELECT * FROM orders WHERE id = 3) o;

type

type 表示 JOIN 的类型,是查询是否高效的重要依据。
效率从高到低排列为 system > const > eq_ref > ref > range > index > all。

  • system: 表中只有一条数据,const 连接的特殊类型。
  • const: 主键或唯一索引的等值比较,由于表中至多有一条符合的数据,所以速度很快。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> EXPLAIN SELECT * FROM users WHERE id = 2\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: users
    type: const
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: const
    rows: 1
    Extra: NULL
    1 row in set (0.00 sec)
  • eq_ref: 上表的每一个行至多会匹配到该表的一行,是除 system 和 const 之外最高效的 join type。当索引为 PRIMARY KEY 或 UNIQUE NOT NULL 且被全部使用时会用到。常见于索引列的等值比较。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    mysql> EXPLAIN SELECT * FROM users, orders WHERE orders.user_id = users.id\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: orders
    type: index
    possible_keys: index_orders_on_user_id_and_price
    key: index_orders_on_user_id_and_price
    key_len: 8
    ref: NULL
    rows: 4
    Extra: Using index
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: users
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: explain_test.orders.user_id
    rows: 1
    Extra: NULL
    2 rows in set (0.00 sec)
  • ref: 如果 join 不能根据键值只匹配一行时则会使用该 join type。常见于不是 UNIQUE 或 PRIMARY KEY的索引等值比较,或者是最左前缀规则的索引查询。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    mysql> EXPLAIN SELECT orders.id FROM orders JOIN users ON orders.user_id = users.id WHERE users.name = 'Amy'\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: users
    type: ref
    possible_keys: PRIMARY,index_users_on_names
    key: index_users_on_names
    key_len: 152
    ref: const
    rows: 1
    Extra: Using where; Using index
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: orders
    type: ref
    possible_keys: index_orders_on_user_id_and_price
    key: index_orders_on_user_id_and_price
    key_len: 4
    ref: explain_test.users.id
    rows: 1
    Extra: Using index
    2 rows in set (0.00 sec)
  • range: 使用索引进行范围查询,输出的 key 字段表示使用哪个索引,key_len 表示所使用索引中最长的索引长度。注意此类型下 ref 字段为 NULL。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> EXPLAIN SELECT * FROM users WHERE users.id IN (2, 3)\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: users
    type: range
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: NULL
    rows: 2
    Extra: Using where
    1 row in set (0.00 sec)
  • index: 全索引扫描, index 类型仅仅扫描所有的索引, 而不扫描数据。一般两种情况会出现。
    一种是出现在所要查询的数据直接在索引树中就可以获取, 此时 Extra 字段会显示 Using index。另一种是全表扫描时按索引的顺序查找数据,此时 Extra 字段不会显示 Using index。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> EXPLAIN SELECT users.name FROM users\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: users
    type: index
    possible_keys: NULL
    key: index_users_on_names
    key_len: 152
    ref: NULL
    rows: 10
    Extra: Using index
    1 row in set (0.00 sec)
  • ALL: 全表扫描。

Extra

Extra 字段提供了关于查询的额外信息,种类很多,具体可以看官方文档
除了上文提到的 Using index,这里再额外说两种。
Using filesort 表示 MySQL 需要遍历所有符合条件的行然后按照排序的 key 来使得最终的查询结果是有序的。
Using temporary 表示 MySQL 需要创建一个临时表来存储结果,通常发生在查询包含不同列的 GROUP BY 和 ORDER BY 子句。
看到这两者时,可以考虑对查询进行优化。

使用的测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_users_on_names` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO users(name) VALUES ('Amy');
INSERT INTO users(name) VALUES ('Bob');
INSERT INTO users(name) VALUES ('Cindy');
INSERT INTO users(name) VALUES ('Duke');
INSERT INTO users(name) VALUES ('Kay');
INSERT INTO users(name) VALUES ('Lucy');
INSERT INTO users(name) VALUES ('Mike');
INSERT INTO users(name) VALUES ('Nancy');
INSERT INTO users(name) VALUES ('Ted');
INSERT INTO users(name) VALUES ('Van');


CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`price` decimal(8,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_orders_on_user_id_and_price` (`user_id`,`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO orders(user_id, price) VALUES (1, 80);
INSERT INTO orders(user_id, price) VALUES (1, 100);
INSERT INTO orders(user_id, price) VALUES (2, 90);
INSERT INTO orders(user_id, price) VALUES (2, 120);


CREATE TABLE `order_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`product_name` varchar(50) NOT NULL,
`quantity` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_order_items_on_order_id` (`order_id`),
KEY `index_order_items_on_product_name` (`product_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO order_items(order_id, product_name, quantity) VALUES (1, 'p1', 1);
INSERT INTO order_items(order_id, product_name, quantity) VALUES (1, 'p2', 2);
INSERT INTO order_items(order_id, product_name, quantity) VALUES (2, 'p3', 1);
INSERT INTO order_items(order_id, product_name, quantity) VALUES (2, 'p4', 1);
INSERT INTO order_items(order_id, product_name, quantity) VALUES (3, 'p5', 3);
INSERT INTO order_items(order_id, product_name, quantity) VALUES (4, 'p6', 2);

参考