MySQL进阶突击系列(07) 如何分析优化慢SQL | 怎么看执行计划?

读书笔记：一个人能走到自信、热情这一步是非常不容易的。自信可以滋养自体；而热情可以滋养他人。全能感有四个基本变化：全能自恋、全能暴怒、彻底无助、被害妄想。当我们降服全能自恋，从孤独的想象世界进入关系的现实世界，你会发现这个世界充满爱、热情、和创造力。你会爱上这个世界，也会爱上自己。

一、前言背景

二、如何判断 OR 找到慢查询SQL

2.1 开启慢查询日志

2.2 多慢的SQL，算是慢SQL？

2.3 慢查询日志分析

2.4 透过执行计划判断慢SQL

三、如何看懂执行计划

3.1 id-执行优先级顺序

3.2 select_type-查询类型

3.3 type-执行计划关键指标-有没有走索引？

3.3.1 const的意义-命中主键或者唯一索引

3.3.2 eq_ref-命中主键或者唯一索引

3.3.3 ref-命中了非唯一性索引

3.3.4 index-查询结果列全是索引

3.3.5 range-范围查询

3.3.6 all-全表扫描

四、SQL如何优化

一、前言背景

在日常研发工作当中，系统性能优化，从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化，除了DBA在集群性能、服务器调优需要投入精力，我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后，SQL执行效率可能就会出现瓶颈，影响系统业务响应。

掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能，在工作中解决SQL性能问题显得非常关键。

此时，可以教小美怎么判断和找到慢SQL，以及详细教她看sql的执行计划，并耐心给她讲一些生产实战的SQL优化经验。

二、如何判断 OR 找到慢查询SQL

MySQL支持记录慢查询SQL日志，不过默认是关闭的。可以通过命令查看以及开启：

show variables like '%slow_query_log%';

2.1 开启慢查询日志

通过在客户端会话，采用set global 进行全局设置MySQL慢SQL查询记录。比如：

set global slow_query_log='on';

然后再次查询，慢SQL记录已经打开。

2.2 多慢的SQL，算是慢SQL？

MySQL默认执行时间大于10s，就是慢SQL。可以通过查看参数long_query_time，比如：

show variables like '%long_query_time%';

在日常工作中，慢SQL就意味着客户体验差。对于系统来说，一条普通业务SQL查询，原则上最慢不得大于1s。而涉及对客展业的后台SQL，响应时间绝对不允许大于3s。对于高级别的系统，秒级响应的SQL实际就是慢SQL，需要进行SQL优化、技术架构优化。

2.3 慢查询日志分析

慢SQL日志内容长什么样？

为了方便验证，我们把参数long_query_time设置为0s。

不过这里需要注意，在客户端会话中set GLOBAL long_query_time之后，需要关闭该会话客户端，重新打开才能看到long_query_time新值。以及需要在新会话窗口执行sql，才会被当做慢SQL记录到log日志。

修改为0，并重新打开新会话执行查询SQL，这样所有sql都被MySQL当中慢sql记录到我们的日志里：

/usr/local/var/mysql/ladingjieniu-slow.log。

在slow log里，我们可以看到慢查询sql内容，以及查询时间、查询的用户名、扫描数据行数、最后获得结果数据等信息。

2.4 透过执行计划判断慢SQL

在未发版上线前，测试环境由于数据量有限，无法及时监控发现慢SQL。此时针对高等级核心业务场景接口SQL，可以通过explain 查看SQL执行计划的type字段来评估SQL性能。

type值常见的有const、ref、eq_ref、all、range这些。一个查询sql的执行计划type要求至少是index才是优秀，如果是all就要考虑纳入优化。

接下来，我们具体分析执行计划应该如何看。

三、如何看懂执行计划

我们拿一个执行计划出来看一下。比如：

里面有id、select_type、table、type、key、ref等字段。每个都有它特定意义。

3.1 id-执行优先级顺序

在SQL架构原理那一篇文章，我们说过，sql最终如何执行是由核心组件查询优化器决定。查询优化器通过询问存储引擎、sql索引情况来生成SQL执行树。一个复杂的SQL，里面有很多联合查询、子查询。具体哪个先执行，这里就可以通过id来看到查询优化器指定的顺序。

比如上图，id值有【1、1、1、2】。这个执行顺序是2，然后是1。三个1从上往下执行。

原则是：id值越大，优先级越高。会被优先执行。如果id值相等，在执行计划前面的先执行。

3.2 select_type-查询类型

查询类型，常见的有simple、primary、subquery、derived、union、union all这些。看名字顾名思义，有简单查询、主查询、子查询、联合查询。具体展开：

simple：表示单表查询或者简单查询。比如：

primary：表示主查询。

执行计划select type是primary，往往都是SQL是复杂查询，里面有子查询。primary和subquery或者derived一起出现。比如如下：

union: sql里有union，union关键字后的select 语句就被标注select type为【union】。

derived和subquery的区别，这里也说一下。

select列表，where 条件里的子查询是subquery。而如果子查询是在from结果集，select type就是derived。

比如：

explain select p.*,(select role_id from sys_user_role) as role_id from (select user_id from sys_user) t,sys_user_post p;

from后面的这个t结果集子查询就是derived类型，而select里的子查询【(select role_id from sys_user_role)】就是subquery类型。

3.3 type-执行计划关键指标-有没有走索引？

常见的有const、all、range、index、ref、eq_ref这些。这里查询表现最好的按高到底排序:

const> eq_ref> ref > range > index > all。

3.3.1 const的意义-命中主键或者唯一索引

当我们执行计划type为const，说明命中了主键索引或者唯一索引。比如如下sql：

explain select * from sys_user where user_id=1;

where条件是唯一索引user_id：

3.3.2 eq_ref-命中主键或者唯一索引

当type为eq_ref时，说明也是命中了索引，而且和const类型，也是命中的主键或者唯一索引。sql里where条件，用到了主键索引进行多个等值匹配。

比如如下sql的查询计划type出现了 eq_ref类型:

explain select * from sys_user where user_id in (select user_id from sys_user_role);

3.3.3 ref-命中了非唯一性索引

而ref也是命中索引，但是和eq_ref不同的是，ref是命中了非唯一性索引。比如：

user_mvcc_demo表name是非唯一索引，执行sql：

explain select * from user_mvcc_demo where name='拉丁哥8';

查询计划type就是ref。

3.3.4 index-查询结果列全是索引

比如我们这个sys_user_role表，里面两个字段都是索引。查询计划，虽然是全部查询，但是tye就是index。

3.3.5 range-范围查询

查询条件里使用了大于小于，between之类的查询。

3.3.6 all-全表扫描

如果sql查询里，type有all，那就是触发了全表扫描，没有走索引。需要重点关注，尝试看能否优化。

其他的顺便说一下，possible_keys就是可能用到的索引、key表示实际用的索引。

ref就是哪些列被用来关联索引查询。

rows，表示预计要读取多少行数据。

四、SQL如何优化

其实SQL优化的前期慢sql定位、以及执行计划的分析，我们已经大概确定sql慢在哪里，尤其是复杂的sql。通过执行计划，可以清晰看到sql是怎么执行的，以及是否走索引，不管是select字段，from子集、中间结果表、where条件、还是排序order by，或者group by，还是having，每一部分都需要关注优化。

而sql优化的核心，重点是让sql尽可能的走索引+合理创建索引。通过执行计划，我们可以很快发现，未及时创建的索引。而sql如何写，才会触发走索引呢？这里有几个原则：

1、最左匹配原则。

2、sql里尽量不要做类型转换、函数计算。

3、like、范围查询要谨慎。

4、尽量不要出现*。

这几个原则非常朴实，有的大佬甚至整理了顺口溜方便大家做SQL优化。然而随着nosql、内存数据库的广泛应用，小公司数据量小sql优化需求不多；而大企业，有足够成熟的技术架构，用到sql优化的很多时候就是做大数据数仓的火伴，写sql就是写代码的数仓同学，需要对sql有深入理解。其他研发真正用到sql优化的，刚好就是中小企业研发同事，系统有一定的数据量，但是在基础设施投入有限，人力有限，就需要大家投入研发精力去进行SQL优化。

sql优化之外，能继续深入优化的方法还有数据预处理计算、以及联合索引、应用缓存的合理搭配应用。

最后，SQL优化就一句话：合理创建索引，且让SQL查询应用上索引。

推荐阅读拉丁解牛相关专题系列(欢迎交流讨论公众号搜：拉丁解牛说技术)：

1、JVM进阶调优系列(5)CMS回收器通俗演义一文讲透FullGC

2、JVM进阶调优系列(4)年轻代和老年代采用什么GC算法回收?

3、JVM进阶调优系列(3)堆内存的对象什么时候被回收?

4、JVM进阶调优系列(2)字节面试:JVM内存区域怎么划分，分别有什么用？

5、JVM进阶调优系列(1)类加载器原理一文讲透

6、JAVA并发编程系列(13)Future、FutureTask异步小王子

7、MySQL进阶突击系列(05)突击MVCC核心原理 | 左右护法ReadView视图和undoLog版本链强强联合