0%

MySQL执行计划分析

执行计划是在SQL性能优化时关注的重点,通过执行计划我们能够知道MySQL如何处理SQL,分析性能瓶颈并作出相应优化。

1
2
3
4
5
6
root@employees 11:40:  explain select * from titles where emp_no=10001;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+

上述是一个非常简单的执行计划结果,接下来就对其中的各个字段的含义进行解读

id

id用于表示执行顺序。id相同执行顺序由上至下;id不同执行顺序则由大至小

select_type

select_type表示查询类型:

  • SIMPLE:简单查询,即查询中不包含子查询或union
  • PRIMARY:查询中包含子部分,最外层查询则被标记为PRIMARY
  • SUBQUERY:子查询中的第一个select
  • DERIVED:FROM列表中的子查询会被标记为DERIVED
  • UNION:union关联中的第二个或后面的select语句
  • UNION RESULT:select从UNION的结果集获取数据
  • MATERIALIZED:物化子查询

type

type表示访问方式。从好到差依次排序为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

  • system:表示只有一行记录,不太常见
  • const:通过索引扫描一次就查找到对应记录,常见于主键或唯一键作为条件
  • eq_ref:唯一索引扫描,常见于主键或唯一键作为条件
  • ref:非唯一索引扫描
  • range:检索指定范围内的行,通过索引进行范围扫描
  • index:FULL INDEX SCAN,遍历整个索引树
  • ALL:FULL TABLE SCAN,全表扫描

possible_keys

列出SQL涉及到的字段上存在的索引,但不一定会实际应用

key

SQL实际使用的索引,如果为空,则表示未使用索引,这种情况往往是值得重点关注的

key_len

表示索引数据的字节长度,其是根据表定义计算得出的,并非实际长度,理论上长度越短越好

ref

显示索引的那一列被使用了,也可能是一个常量const

rows

根据统计信息以及索引情况,估算需要扫描多少行记录

Extra

Extra记录了一些额外的扩展信息,常见的有Using filesort、Using index、Using temporary、Using where等

  • Using filesort:查询需要额外的排序操作,但并非一定使用了物理文件排序
  • Using temporary:查询用到了临时表保存中间结果
  • Using index:表示查询使用到了覆盖索引的特性
  • Using where:查询使用where进行条件过滤
  • Using join buffer:查询使用到了join_buffer缓存
  • Using mrr:查询使用到了MRR优化特性
  • Using index condition:表示 SQL 操作命中了索引,但不是所有的列数据都在索引树上,还需要回表查询

我们也可以对执行计划进行格式化显示更多的信息,例如JSON格式,其中会包含一些关于COST的信息

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
root@employees 15:15:  explain format=json select * from titles where emp_no=10001;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "titles",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"emp_no"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "216"
},
"used_columns": [
"emp_no",
"title",
"from_date",
"to_date"
]
}
}
}