通过explain分析低效SQL的执行计划
序言
使用的数据库依然是mysql示例库sakila
接着上篇的MySQL之SQL优化系列（一）
当通过慢查询日志定位到执行效率较低的SQL之后，使用explain进行低效SQL的分析。
使用EXPLAIN分析低效SQL的执行计划
使用EXPLAIN可以获取MySQL是如何执行SELECT语句的，包括在SELECT语句执行过程中表如何连接以及连接的顺序，如下示例SQL执行：
1 | mysql> explain select sum(amount) from customer a,payment b where 1=1 and a.customer_id = b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G |
字段解释：
- select_type:表示select的类型，常见取值有SIMPLE(简单表，不使用表连接或子查询)、PRIMARY(主查询，外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)
- table:输出结果集的表
- type:表示MySQL在表中找到所需行的方法，或者称为访问类型，常见的有：从做到右，性能由最差到最好。示例：
1
ALL —— index —— range —— ref —— eq_ref —— const,system —— NULL
- type=ALL，全表扫描，MySQL遍历全表来找到匹配行：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> explain select * from film limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) - type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行：获取得到film表的索引：
1
mysql> show index from film;
film_id(主键),title,language_id,original_language_id1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> explain select title from film\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec) - type=range,索引范围扫描，常见的有
between,>,<等。 - type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配单独值得记录行，例如：索引idx_fk_customer_id是非唯一索引，查询条件为等值查询条件customer_id=24，所以扫描索引的类型为ref。ref还经常出现在join操作中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> explain select * from payment where customer_id=24\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: const
rows: 25
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) - type=eq_ref，类似ref，区别在于使用的索引是唯一索引，对于每一个索引的键值，表中只有一条记录匹配。换句换说，就是在进行多表连接时，使用得失primary key或者unique index作为关联条件。
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
28mysql> explain select * from film a,film_text b where a.film_id = b.film_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.b.film_id
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec) - type=const/system，表单中有最多一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理，例如，根据主键primary key或者唯一索引unique index进行的查询。通过唯一索引uk_email访问的时候，类型type为const。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16alter table customer add unique index uk_email(email);
mysql> explain select * from (select * from customer where email='LINDA.WILLIAMS@sakilacustomer.org') a\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: const
possible_keys: uk_email
key: uk_email
key_len: 153
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
- possible_keys：表示查询时可能使用的索引
- key：表示实际使用的索引
- key_len：使用到索引字段的长度、
- rows：扫描行的数量
- Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。