Mysql的explain，你真的会用吗？（二）

 

（3）eq_ref

在进行数据查询的过程中，如果SQL语句中在表连接情况下可以基于聚簇索引或者非null值的唯一索引记性数据扫描，那么此时type对应的值就会显示为eq_ref。

 

（4）ref

数据查询的时候如果命中的索引是二级索引不是唯一索引，测试查询速度也会很快，但是type是ref。另外如果是多字段的联合索引，那么根据最左匹配原则，从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref的类型。

（5）ref_or_null

这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。

 

（7）unique_subquery

在where条件中的关于in的子查询条件集合

 

（8）index_subquery

区别于unique_subquery，用于非唯一索引，可以返回重复值。

 

（9）range

使用索引进行行数据检索，只对指定范围内的行数据进行检索。换句话说就是针对一个有索引的字段，在指定范围中检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

（10）index

Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。

 

（11）all

遍历全表进行数据匹配，此时的数据查询性能最差。

 

6、possible_keys

表示哪些索引可以被Mysql的优化器进行选择，也就是索引候选者有哪些。

 

7、key

在possible_keys中实际选择的索引

 

8、key_len

表示索引的长度，和实际的字段属性以及是否为null都有关系。

 

9、ref

当使用字段进行常量等值查询时ref此处为const，当查询条件中使用了表达式或者函数则ref显示为func，则其他的显示为null。

 

10、rows

rows列显示MySQL认为它执行查询时必须检查的行数。行数越少，效率越高！

 

11、filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下的记录满足条件的记录数量的比例。

 

12、extra

在其他列不显示额外信息在此列进行展示。

 

（1）Using index

在进行数据查询的时候，数据库使用了覆盖索引，就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快。不是使用select * ，而是使用select phone_number，就会用到覆盖索引。

（2）Using where

 查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。

（3）Using temporary

表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

 

（4）Using filesort

此类型表示无法利用索引完成指定的排序操作，也就是ORDER BY的字段实际没有索引，因此此类SQL是需要进行优化的。

 

explain分析实战
上文中我们阐述了explain在分析SQL语句时，可以通过12个属性来分析SQL的大致执行过程，并以此来判断SQL存在的性能问题。那么接下来我们通过一个实际的例子，来具体看下如何结合explain来实现SQL的性能分析。

 

其实所谓的Mysql性能问题，大部分都指的是平台出现了慢查询问题。慢查询实际上是可以通过配置进行记录的，把执行时间超过某个设定的阈值的sql都记录下来，当出现问题的时候可以通过记录的慢查询日志进行问题的定位。但是有的时候，出现大量慢查询会导致数据库连接被占满，导致整个平台的出现异常。

 

实际上我们在产品评价表product_evaluation中是建立了索引的，正常来说应该是可以使用到对应的索引字段进行查询的。但是实际上查询耗时有几十秒的时间，远远超过我们的预期。那我们猜测是不是由于某种原因导致Mysql优化器没有选择对应的索引进行数据检索，最后造成慢查询的发生。到底执行计划是怎样的，还是得借助于explain来看下。

如上文所说，虽然explain有12个字段属性帮助我们进行执行计划的分析，但是实际上常用的核心字段也就几个。我们可以看的出来在possible_key中实际上包含了我们设置的索引的，但是实际上Mysql却选择了PRIMARY作为其实际使用的。那么问题来了，为什么明明设置了索引，但是实际并没有用上，被Mysql吃了吗？另外为什么之前的业务中没有出现这个问题，而现在出现了？我们需要进行进一步的分析。

 

我们所建立的idx_evaluation_type实际上是一个二级索引（叶子节点是主键id），对于数千万一张的大表来说，实际上这个二级索引也是非常大的，而且这个字段本身的值就三个，变化不大。因此Mysql的优化器在分析这个SQL的时候发现，如果按照SQL中的索引来获取数据后再根据where条件进行筛选，筛选后的数据还需要回表到聚簇索引中获取实际的数据。

 

假如通过二级索引筛选出来的数据有几万条，而后还需要进行排序，这些操作都是基于临时磁盘我恩建进行的，Mysql判断这种方式的性能可能会很差，因此优化器放弃了原有的数据查询方式，直接通过主键id对应的聚簇索引来进行数据的获取，因为id本身就是有序的。

那么知道了查询慢的原因，我们应该怎么进行优化呢？实际上可以在SQL语句中增加force idnex，强制Mysql使用我们设置的二级索引。

SELECT * FROM product_evaluation force index(idx_product_id)WHERE product_id =1 and evaluation_type='GOOD'  ORDER BY id desc LIMIT 200SELECT * FROM product_evaluation force index(idx_product_id)WHERE product_id =1 and evaluation_type='GOOD'  ORDER BY id desc LIMIT 200

 

总结
通过上文对于explain使用的介绍，大家在遇到慢SQL问题的时候，可以先通过explain来进行初步的分析，主要明确SQL在Mysql中实际的执行过程是怎样的，如果查询字段没有索引则增加索引，如果有索引就要分析为什么没有用到索引。只要明确具体的执行过程，我们才能确定具体的查询优化方案。

 

文章转自公众号：慕枫技术笔记