MySQL 查看执行计划

mysql的执行计划：通过EXPLAIN SELECT * from A where X=? and Y=?

执行计划一般包括如下：

1。id

id相同,执行顺序由上而下，id不同,值越大越先被执行

2。selectType

• SIMPLE： 表示此查询不包含 UNION 查询或子查询
• PRIMARY： 表示此查询是最外层的查询
• SUBQUERY： 子查询中的第一个 SELECT
• UNION： 表示此查询是 UNION 的第二或随后的查询
• DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
• UNION RESULT, UNION 的结果
• DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
• DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

3.table

table表示查询涉及的表或衍生的表

4.type

这个字段是优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：

• const:当查询最多匹配一行时，常出现于where条件是＝的情况，一般常用于等值扫描或者唯一性索引扫描，比如select * from A where A.id=2，这种非常快
• system: 是const的特殊情况，此时说明表中只有一行数据，直接返回。
• eq_ref:这种相当于多表之间关联查询，select A.* from A,B where A.id=B.userId,其中A中id对于B.userId是一一对应的，此时查询效率较高
• ref: 此类型通常出现在多表的联合 查询，使用了非唯一或非主键索引，或者匹配最左前缀规则索引的语句。比如select A.* from A where A.name='a' and A.sex='m'，name和sex构成一个组合索引
• range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。比如select A.* from A where A.time between a and c
• index: 表示全索引扫描(full index scan)， index 类型扫描所有的索引， 而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据，但是索引本身数量很多，内容很大，那么执行效率很低。
• ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多，执行效率越慢。

执行效率：

ALL < index < range< ref < eq_ref < const < system。对于我们平时查询执行计划，优化sql来说，最好是避免ALL和index

5.possible_keys

它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。

6.key

此字段是 mysql 在当前查询时所真正使用到的索引。 他是possible_keys的子集

7.key_len

表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标

9.rows

rows 也是一个重要的字段，mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明要么是索引设置不对，要么是字段传入的类型之类的问题，说明要优化空间越大

10.filtered

filtered参数，它指返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到数据越准确， 百分比越小，说明查询到的数据量大，而结果集很少

11.extra

• using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
• using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
• using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。
• using where ：sql使用了where过滤,效率较高。

实际案例优化

这是建表的sql信息：通过 show create table A;

有一个查询语句，这是历史遗留的sql，

select id from trade_iapreceipt where selectUserId=0 and selectOrderNo='2' and (status= '1' or status = '1') limit 1;
表面一看，没啥问题，应该会命中索引userStatus, 在实际生产上，执行时间较长，看看它的执行计划，

 

 

仿佛一看，也没啥问题，索引也用到了，key字段显示，Extra显示使用index排序了，但是根据类型type为index，说明这是个全索引扫描，效率很低的，最后命中的索引userStatus，这是在测试环境上数据比较少，到了生产环境数据量相当大，rt就升高了，这是看着没有问题，然后仔细观察，发现原来在表中，selectUserId是varchar类型，结果查询的时候，传入的是整数，mysql做了类型转换，导致了全索引扫描。

之后sql进行了修改，发现明显提升，用了索引，而且扫描的行数明显减少，线上执行效率很高