mysql-explain用法以及实践

欢迎大家来到IT世界,在知识的湖畔探索吧!

引言

最近在给公司招聘java开发，发现很多人都提到了熟练掌握数据库优化。实际一问，基本上都是加个索引，用explain也是为了发现索引是否生效(ˉ▽ˉ；)…，有点尬住，既然如此，写几篇最近项目中如何利用explain做数据库优化的文章帮助复习下。

explain涉及的列

首先先梳理下explain查询结果的列，以及每列含义，千万别只知道一个type字段(●ˇ∀ˇ●)。首先来张图，字段解释如下（包含个人理解非官方解释）：

explain效果图

• id：select语句执行顺序，id的数量与select出现次数一致，且id越大，执行优先级越高，作用不是很大
• select_type：表示对应的查询类型，包含simple：简单查询；primary：复杂查询中最外层的 查询语句；subquery：在select中存在的子查询，图中的defendent subquery和subquery区别在于是否依赖primary查询结果；derived：临时表，即查询的结果集，mysql需要建一个临时表先存起来
• table：访问的表名，方便检查哪张表语句存在问题
• type：查询访问类型，最耳熟能详的字段，决定查找方式，排序为：system > const > eq_ref > ref > range > index > ALL。如果是index，那就说明要对索引逐个检索。如果是all，就是对全表检索了，一般优化重点就是这了。
• possible_keys：mysql推测可能用到的索引，实际mysql内部计算有可能不用索引可能更快，所以只是可能
• key：mysql实际采用的索引，可以用来检查索引是否正确生效了，尤其是索引设置的是联合索引的时候，比较好用
• key_len：使用索引的字节数（注意，5.7或以后的mysql里面字段中varchar(n)，n对应的是字符数，不再是字节数了！！所以这里的字节数是要将n进行转换的，一般int占用4字节，其他请自行百度。），使用联合索引就可以看出来自己联合索引生效了几个字段了
• ref：查询结果所用到哪些列或者常量，见下图：

ref为字段名的图

• rows：作用不大，先放着吧
• Extra：mysql给的一些提示信息，主要要掌握Using index：说明使用了覆盖索引，且查询的字段可以直接在索引数据里面获取，不需要再获取其他非索引字段；Using where：说明索引没有使用，且查询列没有被索引覆盖，需要通过主键查询其他字段值；Using index condition：说明查询列为完全被索引覆盖，还可以优化下；Using temporary，说明mysql使用了临时表处理查询，这块可以优化下，部分场景，比如通过某字段去重查询，通过建立索引来避免使用临时表；Using filesort：使用外部排序，为使用索引排序；上述两种情况建议都优化至using index。

索引使用

explain介绍完了，那在代码编写中有一些最基本的避免索引失效或者提升效率的方法，在这记录下，希望工作中能形成条件反射，主动避免。大家查询下列事项前，首先要理解索引失效的原因是，因为mysql的索引数据结构为一棵b+树，如果mysql查询时，无法通过b+树的数据结构来快速定位数据位置，则会导致全表扫描，索引失效。

1. 最左前缀法，项目中使用联合索引是司空见惯的，为提升效率，我们设置索引以及编写查询语句时，应充分考虑多字段检索场景，查询应从联合索引的最左侧列开始，且不能跳过索引字段，即索引为name、unit_code、dept_code三个字段，那查询时，应写name=” and unit_code=” and dept_code=”,如直接写dept_code=”,则联合索引失效。如确需根据deot_code进行检索，则应单独给dept_code字段建立索引，以提升效率。
2. 不在索引列上做任何操作，索引列部要增加一些函数，比如date、substring等，会直接导致索引失效。
3. 尽量使用覆盖索引（只访问索引的查询），减少 select * 语句，毕竟能通过二级索引搞定的事情，没必要再让mysql去聚集索引那查询一次整体数据
4. 在使用不等于（！=或者<>），not in ，not exists，is null,is not null等情况下，一般是无法使用索引的。一般解决is null或者is not null，可以通过设置数据库默认值以及java校验结合的方式进行解决。
5. like以%开头会导致索引失效，这个想必大家很清楚，不过实际项目中用%name%也是非常需要的，因此我们可以通过建立覆盖索引的方式来提升性能，将查询的字段建立联合索引，可以使索引生效。
6. 字符串查询务必加单引号，尤其是身份证号码查询，以防mysql自动转型，导致索引失效。所以开发如果是用mybatis ，请尽量使用#{name}进行查询(●’◡’●)

以上就是一些explain的详细用法以及一些基本实践，下一篇针对项目中常见问题，来对特定场景进行mysql优化。