MySQL优化从执行计划开始(explain超详细)

前言

小伙伴一定遇到过这样反馈:这页面加载数据太慢啦,甚至有的超时了,用户体验极差,需要赶紧优化;

反馈等同于投诉啊,多有几次,估计领导要找你谈话啦。

于是不得不停下手里头的活,赶紧进行排查,最终可能是程序处理的问题、也可能是并发量大导致排队问题、也可能是SQL查询性能导致等;而在很多时候,SQL查询缓慢是最直接拖慢系统的罪魁祸首,同样是实现一个功能,有的小伙伴毫秒级呈现效果,有的却要好几秒,而调优需要的花费时间不容小觑,最终可能就体现到个人业务能力上和形象上:哇,真牛逼,分分钟搞定; 菜鸟,居然写出这样的SQL;

而对于SQL调优,搜索引擎一查,72般绝技绝对够秀,于是照着开始实操,运气好一下就解决啦,运气差的时候怎么用都不行;所以更重要的是业务场景,要学会分析原因,最后才知道用什么方式解决;而这个系列就来聊聊数据库优化,聊聊原因,聊聊方法。

1. MySQL逻辑结构先知

关于MySQL的逻辑结构,将其理解为四层,就像项目分层一样,每一层处理不同的业务逻辑,先看图后说话:

上图概述:

  • 客户端:这里指连接MySQL各种形式,如.Net中使用的ADO连接、Java使用JDBC连接等;MySQL是客户端和服务器模式,前提先建立连接,才能传输数据,处理相关逻辑;

  • 业务逻辑:在MySQL内部有很多模块组成,分别处理相关业务逻辑;

    连接管理:负责连接认证、连接数判断、连接池处理等业务逻辑处理;

    查询缓存:当一个SQL进来时,如果开启查询缓存功能,MySQL会优先去查询缓存中检查是否有数据匹配,如果匹配上,就不会再去解析对应的SQL啦,但如果语句中有用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表时,都不会走缓存; 对于查询缓存来说,在MySQL8.0已经去除,官方回应的是在一定场景上,查询缓存会导致性能上的瓶颈。

    解析器:对于一个SQL语句,MySql根据语法规则需要对其进行解析,并生成一个内部能识别的解析树;

    优化器:负责对解析器得到的解析树进行优化,MySQL会根据内部算法找到一个MySQL认为最优的执行计划,后续就按照这个执行计划执行。所以后续我们分析的就是MySQL针对SQL语句选择出来的最优执行计划,结合业务,根据规则对SQL进行优化,从而让SQL语句在MySQL内部达到真正的最优。

    执行器:得到执行计划之后,就会找到对应的存储引擎,根据执行计划给出的指令依次执行。

  • 存储引擎:数据的存储和提取最后是靠存储引擎;MySQL内部实现可插拔式的存储引擎机制,不同的存储引擎执行不同的逻辑;

  • 物理文件:数据存储的最终位置,即磁盘上;协同存储引擎对数据进行读写操作。

关于MySql的逻辑结构,以上只是简单描述,业务逻辑层的功能模块远不止上面提到的,小伙伴有兴趣可以专门研究一下,这里的目的就是为了体现SQL语句到服务器上时经过的几个关键步骤,方便后续优化的理解。

2. SQL语句的中关键字执行顺序须知

在编写一条查询语句时,习惯性的从头到尾开始敲出来,应该都是从select 开始吧,但似乎没太注意它们真正的执行顺序;既然要优化,肯定需要得知道一条SQL语句大概的执行流程,结合执行计划,目的就更加清晰啦;上一张一看就明白的图:

关键字简述:

  • FROM:确定数据来源,即指定表;
  • JOIN…ON:确定关联表和关联条件;
  • WHERE:指定过滤条件,过滤出满足条件的数据;
  • GROUP BY:按指定的字段对过滤后的数据进行分组;
  • HAVING:对分组之后的数据指定过滤条件;
  • SELECT:查找想要的字段数据;
  • DISTINCT:针对查找出来的数据进行去重;
  • ORDER BY:对去重后的数据指定字段进行排序;
  • LIMIT:对去重后的数据限制获取到的条数,即分页;

好啦,大概了解MySQL的逻辑结构和SQL查询关键字执行顺序之后,接下来就可以好好说说执行计划啦。

3. 好好说说执行计划

通过上面的逻辑结构,当一个SQL发送到MySQL执行时,需要经过内部优化器进行优化,而使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL的,即SQL的执行计划;根据explain提供的执行计划信息分析SQL语句,然后进行相关优化操作。接下来的示例演示用到五张表:USER(用户表)、MENU(菜单表)、ROLE(角色表)、USER_ROLE(用户角色关系表)、ROLE_MENU(角色菜单关系表)、ADDR(用户地址表,这里认为和用户一一对应)、FRIEND(朋友表,一对多关系),它们的关系这里就不详细说了吧,小伙伴肯定都明白,这是管控菜单权限的五张基础表和两个基础信息表;

演示用的版本是MySql5.5,各版本之间会有不同,所以小伙伴用的版本测试结果不一样的时候,千万别骂我渣哦;其实重要的是查看的思路,整体是大同小异。(求原谅……)

通过explain会输出如下信息,很多小伙伴只关注红框标注部分(即索引),但其实是不够的,接下来就一个一个好好说说。

  • id

    这个id和咱们平时表结构设计的主键ID不太一样,这里的id代表了每一条SQL语句执行计划中表加载的顺序,分为三种情况:

    id相同的时候:这时是从上到下依次执行;

    EXPLAIN SELECT t.ID,t.USER_NAME,r.ROLE_NAME FROM USER t 
    	JOIN USER_ROLE tr ON t.ID = tr.USER_ID
    	JOIN ROLE r ON tr.ROLE_ID = r.ID
    

    执行如下语句,得如下结果:

    如上图所示,id一样,从上到下依次执行,所对应表加载顺序为t->tr->r(这里的表是别名)

    id不同的时候:当id不同的时,id越大的越先执行;

    EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
    	WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm 
    		WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1))
    

    子查询会导致id递增,结果如下:

    如上图所示,id递增啦,所对应表的加载顺序为ur->rm->t(这里的表是别名)

    id相同和不同同时存在时:id相同的认为是同一组,还是从上往下加载;不一样的情况还是越大越优先执行

    EXPLAIN SELECT t.ROLE_ID,m.ID,m.MENU_NAME,m.MENU_URL FROM 
    	(SELECT ROLE_ID FROM USER_ROLE WHERE USER_ID=3) t,ROLE_MENU rm,MENU m
    	WHERE t.ROLE_ID=rm.ROLE_ID
    	AND rm.MENU_ID=m.ID
    

    执行结果如下:

    如上图所示,id有一样的,也有不同的,则对应表的加载顺序为USER_ROLE->derived2 (衍生表)->rm->m;衍生表表名后面的2代表的是id,所以可以通过衍生表表名后面的id知道是哪一步产生的,即derived2衍生表是id为2的这一步产生的。

  • select_type

    select_type 是表示每一步的查询类型,方便分析人员很直接的看到当前步骤执行的是什么查询,有多种类型,见下图:

    1> SIMPLE:简单的SELECT查询,不包含子查询或UNION的那种;

    EXPLAIN SELECT * FROM USER;
    

    输出结果如下:

    2> PRIMARY:查询语句中包含其他子查询或UNION操作,那最外层的SELECT就被标记为该类型;

    如上图所示,查询中包含子查询,最外层查询被标记为PRIMARY;

    3> SUBQUERY:在SELECT或WHERE中包含的子查询会被标记为该类型;

    PRIMARY图,当存在子查询时,会将子查询标记为SUBQUERY

    4> MATERIALIZED:被物化的子查询,即针对对应的子查询将其物化为一个临时表;

    EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
    	WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm 
    		WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1));
    

    测试物化用的是MySQL8.0,和5.*版本有所不同,输出结果如下:

    如上图所示,将子查询物化为一个临时表subquery2,这个功能是可以通过设置优化器对应的开关的。

    5> DERIVED:在FROM之后的子查询会被标记为该类型,同样会把结果放在一个临时表中;

    EXPLAIN SELECT tm.MENU_NAME,rm.ROLE_ID FROM 
    	(SELECT * FROM MENU WHERE ID >3 ) tm ,ROLE_MENU rm 
    	WHERE tm.ID=rm.MENU_ID AND rm.ROLE_ID=1
    

    输出结果:

    如图所示,FROM后面跟的子查询就被标记为DERIVED,对应步骤产生的衍生表为derived2。高版本好像对其进行了优化,8.0版本这种形式认为是简单查询。

    6> UNION:UNION操作中,查询中处于内层的SELECT;

    EXPLAIN SELECT * FROM USER_ROLE T1 WHERE T1.USER_ID=1
    UNION
    SELECT * FROM USER_ROLE T2 WHERE T2.USER_ID=2
    

    输出结果如下:

    如上图所示,将第二个SELECT标注为UNION ,即对应加载的表为T2。

    7> UNIOIN RESULT:UNION操作的结果,对应的id为空,代表的是一个结果集;

    UNIOIN图,UNIOIN RESULT代表的是UNION之后的结果,对应id为空。

  • table

    table代表对应步骤加载的是哪张表,中间会出现一些临时表,比如subquery2、derived2等这种,最后的数字代表产生该表对应步骤的id。

  • type

    代表访问类型,MySQL内部将其分为多类型,常用的类型从好到差的顺序展示如下:

    system->const->eq_ef->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL;

    而在实际开发场景中,比较常见的几种类型如下:const->eq_ref->ref->range->index->ALL(顺序从好到差),通常优化至少在range级别或以上,比如ref算是比较不错的啦;

    上面说到的从好到差指的是查询性能。

    1>const:表示通过索引一次就找到数据,用于比较primary key或者unique索引,很快就能找到对应的数据;

    2>eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常用于主键或唯一索引扫描;

    3>ref:非唯一索引扫描,返回匹配的所有行,如建立一个朋友维护表,维护用户对应的朋友,而在用户ID建立非唯一索引;

    4>range:使用一个索引检索指定范围的行,一般在where语句中会出现between、<、>、in等范围查询;

    5>index:全索引扫描,只遍历索引树;

    6>ALL:全表扫描,找到匹配行。与index比较,ALL需要扫描磁盘数据,index值需要遍历索引树。

  • possible_keys

    显示可能被用到的索引,但在实际查询中不一定能用到; 查询涉及到字段,如果存在索引,会被列出,但如果使用的是覆盖索引,只会在key中列出;

  • key

    实际使用到的索引,如果为NULL代表没有使用到索引;这也是平时小伙伴判断是否用上索引的关键。

  • key_len

    key_len表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到,非常重要;key_len是根据表定义计算而得。这里测试在USER表中对USER_NAME创建一个非唯一索引,如下:

    这里key_len是这么计算的,前提是指定的字符串集是utf8,可变长 且允许为空,计算过程如下:

    128(设置的可变长度)*3(utf8占3字节)+1(允许为空标识占一个字节)+2(长度信息占两个字节)=387;

    key_len针对不同类型字段的计算规则不一样,这里用USER(用户表)简单计算为例:

    字段 Key_len 说明
    ID(int,不为空) 4 int为4个字节,不为空
    USER_NAME(varchar(128),utf8,可为空) 128*3+1+2=387 可变为128,utf8每个占3字节,1个字节标识可控,两个字节标识长度

    不同类型占用的字节不一样,字符集不一样占用的字节也不一样,允许为空的字段需要1个字节做标识,可变长度的字段需要2个字节标识长度。小伙伴照着这个思路就可以计算其他类型啦。

  • ref

    显示索引的哪些列被引用了,通常是对应字段或const;

  • rows

    根据表统计信息和索引的使用情况,大概估算出找到所需记录数据所扫描的数据行数不是所需数据的行数。

  • Extra

    这个字段里包含一些其他信息,但也是优化SQL的重要参考,通常会出现以下几种信息:

    Using index:表示查询语句中用到了覆盖索引,不访问表的数据行,查询效率比较好。

    如果用SELECT *进行查询,就不会有Using index,关于索引的介绍下篇好好说说。

    Using filesort:代表MySQL会使用一个外部索引对数据进行排序(文件排序),而不是使用表内索引。这种情况在SQL查询需要避免,最好不要在Extra中出现此类型:

    通常会是使用ORDER BY语句导致,上图中使用无索引的字段进行排序会出现,同样如果使用有索引的字段,但用法不对也会出现,比如使用组合索引不规范时。

    Using temporary:产生临时表保存中间结果,这种SQL是不允许的,遇见数据量大的场景,基本就跑不动啦;

    这种类型常常因为ORDER BY 和 GROUP BY导致,所以在进行数据排序和分组查询时,要注意索引的合理利用。

    Using where:使用where过滤数据,小伙伴试一把。

    Using join buffer:表示使用到了表连接缓存; 当表数据量大,可能导致buffer过大,查询效率比较低,这种情况注意在表连接字段上正确使用索引。

    如果表连接查询慢时,在连接字段上加个索引试试,药到病除;

    impossible where:代表where后面的条件永远为false,匹配不到数据;

    用到的表及数据从Gitgub中获取:https://github.com/zyq025/SQL_Optimize

总结

看完这篇文章之后,小伙伴再去找些SQL看看对应的执行计划,是不是看懂啦,对于优化意义非凡;但是这还不够,接下来还要聊聊索引,聊聊索引失效情况,聊聊除了EXPALIN其他优化方式等,最后日常的开发优化应该都能搞定,远离低效SQL,是不是又有更多时间学习啦。

一个被程序搞丑的帅小伙,关注”Code综艺圈”,跟我一起学~~~

给TA买糖
共{{data.count}}人
人已赞赏
经验教程

ECMAScript 2017(ES8)新特性简介

2021-3-17 7:55:00

经验教程

搜代码费时又费力?这里有一个开源神器帮你快速搞定!

2021-3-17 9:02:00

⚠️
免责声明:根据《计算机软件保护条例》第十七条规定“为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬。”您需知晓本站所有内容资源均来源于网络,仅供用户交流学习与研究使用,版权归属原版权方所有,版权争议与本站无关,用户本人下载后不能用作商业或非法用途,需在24个小时之内从您的电脑中彻底删除上述内容,否则后果均由用户承担责任;如果您访问和下载此文件,表示您同意只将此文件用于参考、学习而非其他用途,否则一切后果请您自行承担,如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。 本站为个人博客非盈利性站点,所有软件信息均来自网络,所有资源仅供学习参考研究目的,并不贩卖软件,不存在任何商业目的及用途,网站会员捐赠是您喜欢本站而产生的赞助支持行为,仅为维持服务器的开支与维护,全凭自愿无任何强求。本站部份代码及教程来源于互联网,仅供网友学习交流,若您喜欢本文可附上原文链接随意转载。
无意侵害您的权益,请发送邮件至 momeis6@qq.com 或点击右侧 私信:momeis 反馈,我们将尽快处理。
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索