一、前言 最近通过SGM监控发现有两个SQL的执行时间占该任务总执行时间的90，通过对该SQL进行分析和优化的过程中，又重新对SQL语句的执行顺序和SQL语句的执行计划进行了系统性的学习，整理的相关学习和总结如下； 二、SQL语句执行顺序 要想优化慢SQL语句首先需要了解SQL语句的执行顺序，SQL语句中的各关键词执行顺序如下： 首先执行from、join来确定表之间的连接关系，得到初步的数据。 然后利用where关键字后面的条件对符合条件的语句进行筛选。 fromjoinwhere：用于确定要查询的表的范围，涉及到哪些表。 选择一张表，然后用join连接：fromtable1jointable2ontable1。idtable2。id 选择多张表，用where做关联条件：fromtable1，table2wheretable1。idtable2。id 最终会得到满足关联条件的两张表的数据，不加关联条件会出现笛卡尔积。 然后利用groupby对数据进行分组。 按照SQL语句中的分组条件对数据进行分组，但是不会筛选数据。 下面用按照id的奇偶进行分组： 然后分组后的数据分别执行having中的普通筛选或者聚合函数筛选。 havingwhere having中可以是普通条件的筛选，也能是聚合函数，而where中只能是普通函数；一般情况下，有having可以不写where，把where的筛选放在having里，SQL语句看上去更丝滑。 使用where再groupby：先把不满足where条件的数据删除，再去分组。 使用groupby在having：先分组再删除不满足having条件的数据。（该两种几乎没有区别） 比如举例如下：100250，此时我们把100拆分（1010101010）2555550，只要筛选条件没变，即便是分组了也得满足筛选条件，所以where后groupby和groupby再having是不影响结果的！ 不同的是，having语法支持聚合函数，其实having的意思就是针对每组的条件进行筛选。我们之前看到了普通的筛选条件是不影响的，但是having还支持聚合函数，这是where无法实现的。 当前的数据分组情况 执行having的筛选条件，可以使用聚合函数。筛选掉工资小于各组平均工资的havingsalary： 然后再根据我们要的数据进行select，普通字段查询或者聚合函数查询，如果是聚合函数，select的查询结果会增加一条字段。 分组结束之后，我们再执行select语句，因为聚合函数是依赖于分组的，聚合函数会单独新增一个查询出来的字段，这里我们两个id重复了，我们就保留一个id，重复字段名需要指向来自哪张表，否则会出现唯一性问题。最后按照用户名去重。selectemployee。id，distinctname，salary，avg（salary） 将各组having之后的数据再合并数据。 然后将查询到的数据结果利用distinct关键字去重。 然后合并各个分组的查询结果，按照orderby的条件进行排序。 比如这里按照id排序。如果此时有limit那么查询到相应的我们需要的记录数时，就不继续往下查了。 最后使用limit做分页。 记住limit是最后查询的，为什么呢？假如我们要查询薪资最低的三个数据，如果在排序之前就截取到3个数据。实际上查询出来的不是最低的三个数据而是前三个数据了，记住这一点。 假如SQL语句执行顺序是先做limit再执行orderby，执行结果为3500，5500，7000了（正确SQL执行的最低工资的是3500，5500，5500）。 SQL查询时需要遵循的两个顺序： 1、关键字的顺序是不能颠倒的。SELECT。。。FROM。。。WHERE。。。GROUPBY。。。HAVING。。。ORDERBY。。。LIMIT 2、select语句的执行顺序（在MySQL和Oracle中，select执行顺序基本相同）。FROMWHEREGROUPBYHAVINGSELECT的字段DISTINCTORDERBYLIMIT 以SQL语句举例，那么该语句的关键字顺序和执行顺序如下：SELECTDISTINCTplayerid，playername，count（）asnum顺序5FROMplayerJOINteamONplayer。teamidteam。teamid顺序1WHEREheight1。80顺序2GROUPBYplayer。teamid顺序3HAVINGnum2顺序4ORDERBYnumDESC顺序6LIMIT2顺序7 三、SQL执行计划 为什么要学习SQL的执行计划？ 因为一个sql的执行计划可以告诉我们很多关于如何优化sql的信息。通过一个sql计划，如何访问表中的数据（是使用全表扫描还是索引查找？）一个表中可能存在多个不同的索引，表中的类型是什么、是否子查询、关联查询等 如何获取SQL的执行计划？ 在SQL语句前加上explain关键词皆可以得到相应的执行计划。其中：在MySQL8。0中是支持对selectdeleteinsterreplaceupdate语句来分析执行计划，而MySQL5。6前只支持对select语句分析执行计划。replace语句是跟instert语句非常类似，只是插入的数据和表中存在的数据（存在主键或者唯一索引）冲突的时候，replace语句会把原来的数据替换新插入的数据，表中不存在唯一的索引或主键，则直接插入新的数据。 如何分析SQL语句的执行计划？ 下面对SQL语句执行计划中的各个字段的含义进行介绍并举例说明。 id列 id标识查询执行的顺序，当id相同时，由上到下分析执行，当id不同时，由大到小分析执行。 id列中的值只有两种情况，一组数字（说明查询的SQL语句对数据对象的操作顺序）或者NULL（代表数据由另外两个查询的union操作后所产生的结果集）。explainselectcourseid，classname，levelname，title，studycntfromimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidwherestudycnt3000 返回3行结果，并且ID值是一样的。由上往下读取sql的执行计划，第一行是tablec表作为驱动表，等于是以C表为基础来进行循环嵌套的一个关联查询。（41001400总共扫描400行等到数据） selecttype列 值 含义 SIMPLE 不包含子查询或者UNION操作的查询（简单查询） PRIMARY 查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY SUBQUERY select列表中的子查询 DEPENDENTSUBQUERY 依赖外部结果的子查询 UNION union操作的第二个或者之后的查询值为union DEPENDENTUNION 当union作为子查询时，第二或是第二个后的查询的值为selecttype UNIONRESULT union产生的结果集 DERIVED 出现在from子句中的子查询（派生表） 例如：查询学习人数大于3000，合并课程是MySQL的记录。EXPLAINSELECTcourseid，classname，levelname，title，studycntFROMimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidWHEREstudycnt3000unionSELECTcourseid，classname，levelname，title，studycntFROMimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidWHEREclassnameMySQL 分析数据表：先看id等于2 id2则是查询mysql课程的sql信息，分别是b，a，c3个表，是union操作，selecttype为是UNION。 id1为是查询学习人数3000人的sql信息，是primary操作的结果集，分别是c，a，b3个表，selecttype为PRIMARY。 最后一行是NULL，selecttype是UNIONRESULT代表是2个sql组合的结果集。 table列 指明是该SQL语句从哪个表中获取数据 值 含义 展示数据库表名（如果表取了别名显示别名） unionM，N 由ID为M、N查询union产生的结果集 由ID为N的查询产生的结果（通常也是一个子查询的临时表）EXPLAINSELECTcourseid，classname，levelname，title，studycntFROMimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidWHEREstudycnt3000unionSELECTcourseid，classname，levelname，title，studycntFROMimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidWHEREclassnameMySOL type列 注意：在MySQL中不一定是使用JOIN才算是关联查询，实际上MySQL会认为每一个查询都是连接查询，就算是查询一个表，对MySQL来说也是关联查询。 type的取值是体现了MySQL访问数据的一种方式。type列的值按照性能高到低排列systemconsteqrefrefrefornullindexmergerangeindexALL 值 含义 system const连接类型的特例，当查询的表只有一行时使用 const 表中有且只有一个匹配的行时使用，如队逐渐或唯一索引的查询，这是效率最高的连接方式 eqref 唯一索引或主键查询，对应每个索引建，表中只有一条记录与之匹配【A表扫描每一行B表只有一行匹配满足】 refornull 类似于ref类型的查询，但是附加了对NULL值列的查询 indexmerge 表示使用了索引合并优化方法 range 索引范围扫描，常见于between、、这样的查询条件 index FULLindexScan全索引扫描，同ALL的区别是，遍历的是索引树 ALL FULLTABLEScan全表扫描，效率最差的连接方式 如果wherelikeMySQL，type类型为？ 虽然classname加了索引，但是使用where的like右统配，所以会走索引范围扫描。EXPLAINSELECTcourseid，classname，levelname，title，studycntFROMimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidWHEREclassnamelikeMySQL 如果wherelikeMySQL，type类型为？ 虽然classname加了索引，但是使用where的like左右统配，所以会走全索引扫描，如果不加索引的话，左右统配会走全表扫描。EXPLAINSELECTcourseid，classname，levelname，title，studycntFROMimccourseajoinimcclassbonb。classida。classidjoinimclevelconc。levelida。levelidWHEREclassnamelikeMySQL possiblekey、key列 possiblekeys说明表可能用到了哪些索引，而key是指实际上使用到的索引。基于查询列和过滤条件进行判断。查询出来都会被列出来，但是不一定会是使用到。 如果在表中没有可用的索引，那么key列展示NULL，possiblekeys是NULL，这说明查询到覆盖索引。 keylen列 实际用的的索引使用的字节数。 注意，在联合索引中，如果有3列，那么总字节是长度是100个字节的话，那么keylen值数据可能少于100字节，比如30个字节，这就说明了查询中并没有使用联合索引的所有列。而只是利用到某一些列或者2列。 keylen的长度是由表中的定义的字段长度来计算的，并不是存储的实际长度，所以满足数据最短的实际字段存储，因为会直接影响到生成执行计划的生成。 ref列 指出那些列或常量被用于索引查找 rows列 （有2个含义）1、根据统计信息预估的扫描行数。 2、另一方面是关联查询内嵌的次数，每获取匹配一个值都要对目标表查询，所以循环次数越多性能越差。 因为扫描行数的值是预估的，所以并不准确。 filtered列 表示返回结果的行数占需读取行数的百分比。 filtered列跟rows列是有关联的，是返回预估符合条件的数据集，再去取的行的百分比。也是预估的值。数值越高查询性能越好。 Extra列 包括了不适合在其他列中所显示的额外信息。 值 含义 Distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值得动作 Notexists 使用notexisits来优化查询 Usingfilesort 使用文件来进行排序，通常会出现在orderby或groupby查询中 Usingindex 使用了覆盖索引进行查询【查询所需要的信息用所用来获取，不需要对表进行访问】 Usingtemporary MySQL需要使用临时表来处理，常见于排序、子查询和分组查询 Usingwhere 需要在MySQL服务器层使用where条件来过滤数据 selecttablesoptimizedaway 直接通过索引来获取数据，不用访问表 四、SQL索引失效 最左前缀原则：要求建立索引的一个列都不能缺失，否则会出现索引失效。 索引列上的计算，函数、类型转换（列类型是字符串在条件中需要使用引号，否则不走索引）、均会导致索引失效。 索引列中使用isnotnull会导致索引列失效。 索引列中使用like查询的前以开头会导致索引列失效。 索引列用or连接时会导致索引失效。 五、实际优化慢SQL中遇到问题 下面是在慢SQL优化过程中所遇到的一些问题。 MySQL查询到的数据排序是稳定的么？ forceindex的使用方式？ 为什么有时候orderbyid会导致索引失效？ 。。。。。。。。未完整理中。。。。。。 六、总结 通过本次对慢SQL的优化的需求进而发现有关SQL语句执行顺序、执行计划、索引失效场景、底层SQL语句执行原理相关知识还存在盲区，得益于此次需求的开发，有深入的对相关知识进行学习和总结。接下来会对SQL底层是如何执行SQL语句
图掀起自动聚焦革命佳能70d快速上手指南图趵突泉菊花展精彩吗景色路线推介郑凯和苗苗，这才是神仙夫妻图青海湖住宿推荐来这里感受蔚蓝水面的美景图谷歌新语言Kotlin成为新一代安卓一级开发语言图从西塘到上海感受历史名镇与现代化都市的区别可以分享一个最喜欢的句子吗？图蜈支洲岛到亚龙湾热带海滨风景等你来图续航能力超强商务范十足的thinkpadx230雁荡山，人间胜景无数，只此山水奇秀，号称中国东南第一山图颜值和体验的平衡选择领略oppor9s的拍照实力图雷峰塔地宫好玩吗带你走进白蛇传满月情书如果家人有人得癌症，接下来几年该怎么度过？是什么意思可怕的工作模式让人绝望兵镜类编主要内容简介及赏析会计工作总结存在不足以小博大看新加坡怎样占取印尼三倍于其国土面积智能家居哪些真的用得上？液化气灶与液化气罐安全距离冷门且好听的个性名字独特稀少女孩昵称大全洗衣房领班年终工作总结舒婷神女峰诗歌赏析惊蛰是什么意思？2019惊蛰是什么时候应该注意什么？