简述
一条MySQL的语句,在到优化器的时候,会生成执行计划,我们可以通过执行计划显示的东西来对我们的SQL进行优化。
EXPLAIN
通过EXPLAIN命令可以查看sql的执行计划。
各列解析
我们只看最常关注的列。
type
type显示该sql对存储引擎的访问方式
- system:表中只有一条记录,并且使用的存储引擎对数据的统计是精准的(InnoDB不精准,MyIsam或者Memory可以)。
- const:单表查询,条件是主键或者非NULL唯一索引的等值判断就是const(常数级别)。
- eq_ref:连接查询中,被驱动表可能出现,出现条件与单表查询的const一样。
- ref:单表查询,条件是唯一索引为NULL或者非唯一索引的等值判断就是ref(单点扫描区间)。
- ref_or_null:与ref一样,条件加上OR 列 = NULL。
- index_merge:之前有说过,在一些情况下(使用二级索引做条件,主键有序)MySQL用到多个索引可以做Intersection、union、sort-union,就会出现这个。
- range:走索引,进行范围扫描。
- index:我们要的列在二级索引树上已经存在,只要通过遍历二级索引B+树的叶子结点就可以。
- all:全表扫描。
possible_keys
可能用到的key,在优化器生成执行计划之前,优化器会生成多个方案(走哪个索引,全表扫描等),并计算成本(IO成本和CPU成本),这个字段就是记录优化器生成方案的索引,possible_keys越多,那么生成的方案就越多,对性能的消耗就越多,所以该列越少越好。
key
实际用到的索引。
key_len
用到索引的长度,该列可以用来判断我们用了联合索引的多少个列,比如我们有联合索引a,b,c,a和b是varchar(100)并可以为null,c是int不为null,表的字符集为utf-8(一个字符1-3个字节)。
- key_len为303:只用到了a
- key_len为606:只用到了a,b
- key_len为610:只用到了a,b,c
为什么走a是303,多了3,MySQL规定可以为null就多1,可变长就多2,所以是303。
extra
- No tables used:查询没有用到表,如 SELECT 1
- Impossible WHERE:查询中包含不可能成立的条件,如 1 != 1
- Using Index:查询的列全部在二级索引上存在,不需要回表(覆盖索引)。
- Using index condition:不需要server进行判断,通过二级索引就可以判断。
- Using where:要在server执行的判断。
- using join buffer:使用了join buffer来加快连接查询。
- using temporary:使用了派生表。
- using filesort:不能用索引的B+树进行排序时,ORDER BY会用到内存和磁盘来完成排序。
Using index condition和Using where
假如我们有索引列col1,普通列col2。我们执行以下sql:
SELECT * FROM TABLE WHERE col1 > 'c' AND col1 LIKE '%bc';
第一种方案:
- server(执行器)生成扫描区间(‘c’,正无穷),发送col1 > 'c’给存储引擎,存储引擎找到第一条满足col1 > 'c’的记录,并之后往后检索,每一次记录满足col1 > 'c’的条件,就回表然后把完整的记录返回给server。
- server收到返回的记录就判断col1 LIKE '%bc’是否成立,成立就返回给客户端,不成立就丢弃。
实际上由于col1 LIKE '%bc’在我们回表之前就可以进行判断了,因为我们有col1的值,所以实际方案可以如下:(索引下推)
- server(执行器)生成扫描区间(‘c’,正无穷),发送col1 > 'c’和col1 LIKE '%bc’给存储引擎,存储引擎找到第一条满足col1 > 'c’的记录,并之后往后检索,每一次记录满足col1 > 'c’的条件,就继续判断是否满足条件col1 LIKE ‘%bc’,如果是就回表,不是就丢弃,然后把完整的记录返回给server。
- server拿到判断其他条件(可能没有在索引的条件,本列子中没有了),然后返回给客户端。
本列子中第一种方案就是Using where,第二种就是Using index condition。
JSON格式的执行计划
EXPLAIN FORMAT=JSON可以生成json格式的执行计划,其中包含有成本的信息。
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.00"
},
"table": {
"table_name": "student",
"access_type": "system",
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.20",
"prefix_cost": "0.00",
"data_read_per_join": "1K"
},
"used_columns": [
"id",
"student_no",
"student_name",
"subject_no",
"subject_name",
"score"
]
}
}
}
SHOW WARNINGS
我们都知道优化器会优化我们的sql语句,然后查看优化后的方案呢?在执行一次EXPLAIN之后,立刻执行SHOW WARNINGS就可以看到。
message记录的就是优化之后的信息(该语句不一定可以直接执行)。