SQL优化篇如何成为一位写优质SQL语句的绝顶高手！

　　标准结构化查询语言简称SQL，编写SQL语句是每位后端开发日常职责中，接触最多的一项工作，SQL是关系型数据库诞生的产物，无论是什么数据库，MySQL、Oracle、SQLServer、DB2、PgSQL。。。。，只要还处于关系型数据库这个范畴，都会遵循统一的SQL标准，这句话简单来说也就是：无论什么关系型数据库，本质上SQL的语法都是相同的，因为它们都实现了相同的SQL标准，不同数据库之间仅支持的特性不同而已。
　　写SQL语句不难，稍微系统学习过数据库相关技术的人都能做到，但想要写好SQL却也不是一件易事，在大多数编写SQL的时候，很多人都是以实现需求为原则去撰写的，当一条SQL写出来之后，只要能满足业务需求就行，不会考虑它有没有优化点，能不能让它跑的更快。
　　而所谓的SQL优化，就是指将一条SQL写的更加简洁，让SQL的执行速度更快，易读性与维护性更好。
　　但要记住！SQL优化是建立在不影响业务的前提之上的，毕竟技术是为业务提供服务，如果为了提高执行效率，把SQL改成了不符合业务需求的样子，这是不行的，这就好比一个流行的梗：记者：你有什么特长吗？路人：我心算特别快！记者：哪请问5658486542244154141415145444566644546？路人：51354545452314！记者：（拿出计算器，算了一下）你这算的不对啊。路人：对啊，我也知道不对，但你就说快不快吧！
　　从这个经典的网络流行梗中，就能看出一个问题，如果一件事违背了初衷，就算再好也无济于事，比如心算特别快，但如果算的不准，再快也没意义，这个道理放在SQL优化中亦是同理，优化一定要建立在不违背业务需求的情况下进行！一、编写SQL的基本功
　　对于简单的SQL语句编写工作，相信这点对于每位略有经验的程序员都是手到拈来的事情，但往往实际业务场景中，咱们需要编写一些逻辑较为复杂的SQL语句，有可能涉及很多表、很多字段的复杂运算，这时编写SQL时就会出现卡壳情况，包括我在内也不例外，日常开发中也会遇到这类情况。
　　那当遇到卡壳情况时，该如何处理才好呢？很多人在这种情况下，首先会试图在网上查找是否有类似业务的实现可参考，如果没有的情况下，会选择去问身边的同事或技术Leader，或者也会去技术交流群问问潜水大佬。但这种方式都属于借助外力来解决问题，一旦外力也无法提供帮助时，卡壳情况就会演变为死机情况，彻底的陷入僵局，最终导致项目进度无法继续推进。
　　在这里我教大家一个比较实用的SQL编写技巧，即：拆解业务需求，先以定值推导SQL。学习过算法的小伙伴应该知道有一种算法思想叫做分而治之，也包括之前聊《并发编程ForkJoin线程池》时，该线程池就是分治思想的落地产物，当一个任务较为庞大且复杂时，在ForkJoin内部会对任务进行拆分，然后分别执行拆分后的小任务，最终将所有小任务结果合并，最终得出大任务的执行结果。
　　我所谓的SQL编写技巧亦是如此，面对一个较为复杂或较难实现的业务需求时，就可以按照需求进行逐步拆分，化繁为简后逐步实现。其实对于这个道理很多人都懂，但往往在实际编写SQL时却想着一步到位，这也是我接触很多程序员后发现的问题：经验尚未丰富的开发，面对一个需求时通常都想着从头写到尾。但这样写就很容易卡壳，对于简单的业务需求可以这样做，但面对复杂业务时一定要先拆解需求后再逐步实现。
　　同时前面还提到一句：先以定值推导SQL，这是啥意思呢？因为有些情况下，一个查询条件会依赖于另一条SQL的执行结果来决定，很多人在这种情况下会直接组合起来一起写，但这会导致编写SQL的复杂度再次提升，因此在这种情况下，可以先用指定值作为条件去查询，例如xxxxx，后面等整体SQL完成后，再套入SQL。
　　当然，说了这么多都是理论，在编程中有句话叫做：扎实的基础理论知识，会决定一个人水平飞得有多高，但能够将相应的理论用于实践，这才能真正体现出一个人的水平有多牛，只懂理论不懂实践，这无异于纸上谈兵，所以下面上一个简单的SQL练习题，实践一下上述的理论：selectfromzzusers；useridusernameusersexpasswordregistertime1熊猫女66662022081415：22：012竹子男12342022091416：17：443子竹男43212022091607：42：214黑熊男88882022091723：48：298猫熊女88882022092717：22：299棕熊男03692022101723：48：29复制代码
　　上面是本次练习题会用到的一张用户表，需求如下：基于性别字段分组，然后ID排序，最后显示各组中的所有姓名，每个姓名之间用，隔开。
　　这里大家可以先自己动手实操一下这个练习题，然后再看文章后面的解析。
　　这个需求看起来不太复杂，但如果直接开写也会令人有些许懵逼，所以先来拆解一下这个需求：首先要基于性别分组，因此需要对usersex字段使用groupby关键字。要对ID字段做排序，因此需要对userid字段使用orderby关键字。将排序语句应用于分组查询的结果中，然后再根据userid排序输出姓名。
　　拆解明白了需求之后，接下来逐步实现每个小需求：首先要基于性别分组，因此需要对usersex字段使用groupby关键字。selectusersex，useridfromzzusersgroupbyusersex；usersexuserid女1男3复制代码
　　上述这条SQL在MySQL5。x版本会得到如上结果，放在MySQL8。x版本则会报错，但不管是任何版本，似乎都未曾得到咱们需要的数据，因为现在我们想要的是先根据性别对userid做分组，那此时需要用到一个新的函数来辅助实现该功能，即groupconcat（），它可以给我们返回指定字段分组组合返回的结果，如下：selectusersexas性别，convert（groupconcat（userid）usingutf8）asIDfromzzusersgroupbyusersex；执行结果如下：性别ID女1，8男2，3，4，9复制代码
　　OK，在上面就基于性别实现了ID分组，接着是需要对ID做排序工作，排序其实比较简单，大家应该都学习过orderby关键字，如下：要对ID字段做排序，因此需要对userid字段使用orderby关键字。selectuseridfromzzusersorderbyuseriddesc；userid984321复制代码
　　这个效果很容易理解，但问题在于如何套入到之前的分组语句中呢？这里会令人有些费脑，其实很简单，如下：selectusersexas性别，convert（groupconcat（useridorderbyuseriddescseparator，）usingutf8）asIDfromzzusersgroupbyusersex；执行结果如下：性别ID女8，1男9，4，3，2复制代码
　　直接把orderby语句套入到groupconcat（）函数中即可，最后声明一下各个值之间的分隔符即可，到这一步为止已经实现了ID分组排序工作，接着是需要按照排序好的ID，将对应的姓名按顺序显示出来，在这里第一时间有小伙伴可能想到的是嵌套子查询，使用in来做，如下：selectusernamefromzzuserswhereuseridin（8，1）；username熊猫猫熊复制代码
　　然后对两个不同的ID分组，分别in一次，然后使用union合并结果，再一次做分组，这样也可以，但实际上会复杂很多很多，其实实现远远没有那么复杂，只需要基于之前的SQL，换个字段即可，如下：将排序语句应用于分组查询的结果中，然后再根据userid排序输出姓名。selectusersexas性别，convert（groupconcat（usernameorderbyuseriddescseparator，）usingutf8）as姓名fromzzusersgroupbyusersex；执行结果如下：性别姓名女猫熊，熊猫男棕熊，黑熊，子竹，竹子复制代码
　　此时一步步的推敲，就达到了最开始的需求：基于性别字段分组，然后ID排序，最后显示各组中的所有姓名，每个姓名之间用，隔开：
　　同时也可以根据上图中的完整数据，来对比看看查询出的是否正确，观察后会发现没有任何问题！
　　上面经过一个例子的熏陶后，咱们逐步拆解了需求，并且套入了实现，最终会发现编写SQL的过程异常顺利，这还仅仅只是一些简单的需求拆解，当业务需求越发复杂时，这套拆解法的作用越大，所以拆解实现法也是写SQL的一大基本功。二、SQL优化的小技巧
　　前面聊了一些写SQL的基本功后，接着来聊一聊本文的核心：SQL优化，所谓的高手和普通人之间，最大的不同在于能将相同的事情做到更好，比如送外卖，相同的时间内一个人能够送的更多，这是个送外卖的高手。比如玩游戏，相同的角色和装备，一个人的战绩能够更出色，那这是个打游戏的高手。。。。。。。
　　上述的道理放在编程中同样适用，一个人代码敲得更快、代码敲的更多、执行效率越高，这也可以被称为是一个写代码的高手，俗称码农ProMax，那作为一个普通码农，如何达到码农Pro、码农Plus、码农ProMax。。。。。的境界呢？首先你得能够写出一手好SQL！
　　掌握了写SQL的基本功后，足以让你写代码的效率提升，但引言中就聊到过：写的快不代表写的好，就算你能够日码三万行，并且还能满足业务需求，这也不见得的能被称之为高手，真正的SQL高手除开编写效率够高之外，对于每条SQL的执行效率也要可控。如果写的多，但有些业务SQL在大数据的情况下，一跑就是十多秒，这是万万不可的！
　　那么问题又来了：如何让自己的SQL又快又好呢？答案其实非常简单，减小查询的数据量、提升SQL的索引命中率即可，接着先来说说撰写SQL时的一些注意点。2。1、编写SQL时的注意点
　　在写SQL的时候，往往很多时候的细节不注意，就有可能导致索引失效，也因此会造成额外的资源开销，而我们要做的就是避开一些误区，确保自己的SQL在执行过程中能够最大程度上节省资源、缩短执行时间，下面罗列一些经典的SQL注意点。2。1。1、查询时尽量不要使用
　　一般在写SQL为了方便，所以通常会采用来代替所有字段，毕竟用号只要按键盘一下，写字段则需要一个个字段名去写。写的确能让程序员更省力，但对机器就不太友好了，因此在写查询语句时一律不要使用代替所有字段，这条准则相信大家都知道，但到底是为什么呢？
　　其实主要有如下几方面的原因：分析成本变高。
　　在《SQL执行篇》中聊过，一条SQL在执行前都会经过分析器解析，当使用时，解析器需要先去解析出当前要查询的表上表示哪些字段，因此会额外增加解析成本。但如果明确写出了查询字段，分析器则不会有这一步解析的开销。网络开销变大。
　　当使用时，查询时每条数据会返回所有字段值，然后这些查询出的数据会先被放到结果集中，最终查询完成后会统一返回给客户端，但线上Java程序和MySQL都是分机器部署的，所以返回数据时需要经过网络传输，而由于返回的是所有字段数据，因此网络数据包的体积就会变大，从而导致占用的网络带宽变高，影响数据传输的性能和资源开销。但实际上可能仅需要用到其中的某几个字段值，所以写清楚字段后查询，能让网络数据包体积变小，从而减小资源消耗、提升响应速度。内存占用变高。
　　在《MySQL内存篇》中曾详细讲到了InnoDB引擎的工作原理，当查询一条数据时都会将其结果集放入到BufferPool的数据缓冲页中，如果每次用来查询数据，查到的结果集自然会更大，占用的内存也会越大，单个结果集的数据越大，整个内存缓冲池中能存下的数据也就越少，当其他SQL操作时，在内存中找不到数据，又会去触发磁盘IO，最终导致MySQL整体性能下降。维护性变差。
　　用过MyBatis框架的小伙伴应该都知道一点，一般为了对应查询结果与实体对象的关系，通常都需要配置resultMap来声明表字段和对象属性的映射关系，但如果每次使用来查询数据，当表结构发生变更时，就算变更的字段结构在当前业务中用不到，也需要去维护已经配置好的resultMap，所以会导致维护性变差。但声明了需要的字段时，配置的resultMap和查询字段相同，因此当变更的表结构不会影响当前业务时，也无需变更当前的resultMap。
　　综上所述，使用的情况下反而会带来一系列弊端，所以能显示写明所需字段的情况下，尽量写明所需字段，除开上述原因外，还有一点最关键的原因：基于非主键字段查询可能会产生回表现象，如果是基于联合索引查询数据，需要的结果字段在联合索引中有时，可能通过索引覆盖原理去读数据，从而减少一次回表查询。但使用查询所有字段数据时，由于联合索引中没有完整数据，因此只能做一次回表从聚簇索引中拿数据，对于索引覆盖感兴趣的可参考之前的《索引应用篇索引覆盖机制》。2。1。2、连表查询时尽量不要关联太多表
　　对于这点的原因其实很简单，一旦关联太多的表，就会导致执行效率变慢，执行时间变长，原因如下：数据量会随表数量呈直线性增长，数据量越大检索效率越低。当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。
　　一般来说，交互型的业务中，关联的表数量应当控制在5张表之内，而后台型的业务由于不考虑用户体验感，有时候业务比较复杂，又需要关联十多张表做查询，此时可以这么干，但按照《高性能MySQL》上的推荐，最好也要控制在1618张表之内（阿里开发规范中要求控制在3张表以内）。2。1。3、多表查询时一定要以小驱大
　　所谓的以小驱大即是指用小的数据集去驱动大的数据集，说简单一点就是先查小表，再用小表的结果去大表中检索数据，其实在MySQL的优化器也会有驱动表的优化，当执行多表联查时，MySQL的关联算法为NestLoopJoin，该算法会依照驱动表的结果集作为循环基础数据，然后通过该结果集中一条条数据，作为过滤条件去下一个表中查询数据，最后合并结果得到最终数据集，MySQL优化器选择驱动表的逻辑如下：如果指定了连接条件，满足查询条件的小数据表作为驱动表。如果未指定连接条件，数据总行数少的表作为驱动表。
　　如果在做连表查询时，你不清楚具体用谁作为驱动表，哪张表去join哪张表，这时可以交给MySQL优化器自己选择，但有时候优化器不一定能够选择正确，因此写SQL时最好自己去选择驱动表，小表放前，大表放后！
　　举个例子感受一下两者之间的区别，假设zzstudent学生表中有10000条数据，zzclass班级表中有100条数据，当需要关联这两张表查询数据时，SQL如下：大表在前，小表在后selectfromzzstudentassleftjoinzzclassascons。classidc。classid；小表在前，大表在后selectfromzzclassascleftjoinzzstudentassonc。classids。classid；复制代码
　　上述是两种联查的SQL语法，如果学生表在前作为驱动表，根据NestLoopJoin算法会循环一万次查询数据，而反之如果班级表在前，则只需要循环100次即可查询出数据，因此诸位在写SQL时一定要记得将小表作为驱动表。
　　这个道理不仅仅只存在于多表关联查询中，只要涉及到多表查询的情况，都需遵循该原则，比如使用子查询进行多表查询时，请确保结果集小的SQL先执行。
　　举个子查询的小表驱动大表的例子：selectfromxxxwhereyyyin（selectyyyfromzzzwhere。。。。）；复制代码
　　MySQL在执行上述这条SQL时，会先去执行in后面的子查询语句，这时尽量要保证子查询的结果集小于in前面主查询的结果集，这样能够在一定程度上减少检索的数据量。通常使用in做子查询时，都要确保in的条件位于所有条件的最后面，这样能够在最大程度上减小多表查询的数据匹配量，如下：优化前：selectxxx，xxx，xxxfromtablewherecolumin（sql）andid10；优化后：selectxxx，xxx，xxxfromtablewhereid10andcolumin（sql）；复制代码
　　以小驱大这个规则也可以进一步演化，也就是当查询多张表数据时，如果有多个字段可以连接查询，记得使用and来拼接多个联查条件，因为条件越精准，匹配的数据量就越少，查询速度自然会越快。
　　对于单表查询时也是如此，比如要对数据做分组过滤，可以先用where过滤掉一部分不需要的数据后，再对处理后的数据做分组排序，因为分组前的数据量越小，分组时的性能会更好！
　　可以把SQL当成一个链式处理器，每一次新的子查询、关联查询、条件处理。。。。等情况时，都可以看成一道道的工序，我们在写SQL时要注意的是：在下一道工序开始前尽量缩小数据量，为下一道工序尽可能提供更加精准的数据。2。1。4、不要使用like左模糊和全模糊查询
　　对于这点的原因十分明显，因为在之前《索引应用篇索引失效场景》中聊到过，如若like关键字以号开头会导致索引失效，从而导致SQL触发全表查询，因此需要使用模糊查询时，千万要避免xxx、xxx这两种情况出现，实在需要使用这两类模糊查询时，可以适当建立全文索引来代替，数据量较大时可以使用ES、Solr。。。。这类搜索引擎来代替。2。1。5、查询时尽量不要对字段做空值判断selectfromxxxwhereyyyisnull；selectfromxxxwhereyyynotisnull；复制代码
　　当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，因此切记要避免这样的情况，一般在设计字段结构的时候，请使用notnull来定义字段，同时如果想为空的字段，可以设计一个0、这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，同时也能避免MyBatis注入对象属性时触发空指针异常。2。1。6、不要在条件查询前对字段做任何运算selectfromzzuserswhereuserid28；selectfromzzuserswheretrim（username）熊猫；复制代码
　　zzusers用户表中userid、username字段上都创建了索引，但上述这类情况都不会走索引，因为MySQL优化器在生成执行计划时，发现这些前面涉及到了逻辑运算，因此就不会继续往下走了，会将具体的运算工作留到执行时完成，也正是由于优化器没有继续往下走，因此不会为运算完成后的字段选择索引，最终导致索引失效走全表查询。
　　从这里可以得出一点，千万不要在条件查询的前，对字段做任何运算，包括了函数的使用也不允许，因为经过运算处理后的字段会变成一个具体的值，而并非字段了，所以压根无法使用到索引！2。1。7、！、！、notin、notlike、or。。。要慎用
　　这点可参考《索引应用篇索引失效场景》中给出的示例，简单来说就是这类写法也可能导致索引失效，因此在实际过程中可以使用其他的一些语法代替，比如or可以使用unionall来代替：selectusernamefromzzuserswhereuserid1oruserid2；可以替换成：selectusernamefromzzuserswhereuserid1unionallselectusernamefromzzuserswhereuserid2；复制代码
　　虽然这样看起来SQL变长了，但实际情况中查询效率反而更高一些，因为后面的SQL可以走索引（对于其他的一些关键字也一样，可以使用走索引的SQL来代替这些关键字实现）。2。1。8、必要情况下可以强制指定索引
　　在表中存在多个索引时，有些复杂SQL的情况下，或者在存储过程中，必要时可强制指定某条查询语句走某个索引，因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能，有时可能选择的索引并不是最好的，这时我们可以通过forceindex，如下：selectfromzzusersforceindex（uniteindex）whereusername熊猫；复制代码
　　这样就能够100强制这条SQL走某个索引查询数据，但这种强制指定索引的方式，一定要建立在对索引结构足够熟悉的情况下，否则效果会适得其反。2。1。10、避免频繁创建、销毁临时表
　　临时表是一种数据缓存，对于一些常用的查询结果可以为其建立临时表，这样后续要查询时可以直接基于临时表来获取数据，MySQL默认会在内存中开辟一块临时表数据的存放空间，所以走临时表查询数据是直接基于内存的，速度会比走磁盘检索快上很多倍。但一定要切记一点，只有对于经常查询的数据才对其建立临时表，不要盲目的去无限制创建，否则频繁的创建、销毁会对MySQL造成不小的负担。2。1。11、尽量将大事务拆分为小事务执行
　　经过之前《MySQL事务机制》、《MySQL锁机制》、《MySQL事务与锁实现原理》这几章的学习后，咱们应该会知道：一个事务在执行事，如果其中包含了写操作，会先获取锁再执行，直到事务结束后MySQL才会释放锁。
　　而一个事务占有锁之后，会导致其他要操作相同数据的事务被阻塞，如果当一个事务比较大时，会导致一部分数据的锁定周期较长，在高并发情况下会引起大量事务出现阻塞，从而最终拖垮整个MySQL系统。showstatuslikeinnodblogwaits；查看是否有大事务由于redologbuffer不足，而在等待写入日志。
　　大事务也会导致日志写入时出现阻塞，这种情况下会强制触发刷盘机制，大事务的日志需要阻塞到有足够的空间时，才能继续写入日志到缓冲区，这也可能会引起线上出现阻塞。
　　因此基于上述原因，在面对一个较大的事务时，能走异步处理的可以拆分成异步执行，能拆分成小事务的则拆成小事务，这样可以在很大程度上减小大事务引起的阻塞。2。1。12、从业务设计层面减少大量数据返回的情况
　　之前在做项目开发时碰到过一些奇葩需求，就是要求一次性将所有数据全部返回，而后在前端去做筛选展现，这样做虽然也可以，但如果一次性返回的数据量过于巨大时，就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，因此如果项目中存在这类业务，一定要记住拆分掉它，比如分批返回给客户端。
　　分批查询的方式也被称之为增量查询，每次基于上次返回数据的界限，再一次读取一批数据返回给客户端，这也就是经典的分页场景，通过分页的思想能够提升单次查询的速度，以及避免大数据量带来的一系列后患问题。2。1。13、尽量避免深分页的情况出现
　　前面刚刚聊过分页，分页虽然比较好，但也依旧存在问题，也就是深分页问题，如下：selectxx，xx，xxfromyyylimit100000，10；复制代码
　　上述这条SQL相当于查询第1W页数据，在MySQL的实际执行过程中，首先会查询出100010条数据，然后丢弃掉前面的10W条数据，将最后的10条数据返回，这个过程无异极其浪费资源。
　　哪面对于这种深分页的情况该如何处理呢？有两种情况。
　　如果查询出的结果集，存在递增且连续的字段，可以基于有序字段来进一步做筛选后再获取分页数据，如下：selectxx，xx，xxfromyyywhere有序字段nnnlimit10；复制代码
　　也就是说这种分页方案是基于递增且连续字段来控制页数的，如下：第一页selectxx，xx，xxfromyyywhere有序字段1limit10；第二页selectxx，xx，xxfromyyywhere有序字段11limit10；第N页。。。。。第10000页selectxx，xx，xxfromyyywhere有序字段100001limit10；复制代码
　　这种情况下，MySQL就会先按where条件筛选到数据之后，再获取前十条数据返回，甚至还可以通过between做优化：selectxx，xx，xxfromyyywhere有序字段between1000000and1000010；复制代码
　　这种方式就完全舍弃了limit关键字来实现分页，但这种方式仅适合于基于递增且连续字段分页。
　　那么例如搜索分页呢？这种分页情况是无序的，因为搜索到的数据可以位于表中的任意行，所以搜索出的数据中，就算存在有序字段，也不会是连续的，这该如何是好？这种情况下就只能在业务上限制深分页的情况出现了，以百度为例：
　　虽然搜索mysql关键字之后，显示大约搜索到了一亿条数据，但当咱们把分页往后拉就会发现，最大只能显示76页，当你再尝试往后翻页时就会看到一个提示：限于网页篇幅，部分结果未予显示。
　　上述百度的这个例子中，就从根源上隔绝了深分页的出现，毕竟你都没给用户提供接下来的分页按钮了，这时自然也就无法根据用户操作生成深分页的SQL。
　　但上述这种思想仅局限于业务允许的情况下，以搜索为例，一般用户最多看前面30页，如果还未找到他需要的内容，基本上就会换个更精准的关键词重新搜索。
　　哪如果业务必须要求展现所有分页数据，此时又不存在递增的连续字段咋办？哪这种情况下要么选择之前哪种很慢的分页方式，要么就直接抛弃所有！每次随机十条数据出来给用户，如果不想重复的话，每次新的分页时，再对随机过的数据加个标识即可。2。1。14、SQL务必要写完整，不要使用缩写法
　　很多开发者，包含我在内，往往都喜欢缩写语法，能够简写的绝不写全，比如：为字段取别名的简单写法selectusername姓名fromzzusers；为字段取别名的完整写法selectusernameas姓名fromzzusers；内连表查询的简单写法selectfrom表1，表2。。。where表1。字段表2。字段。。。；内连表查询的完整写法selectfrom表1别名1innerjoin表2别名2on别名1。字段别名2。字段；。。。。。。复制代码
　　这类情况下还有很多，在写的时候为了图简单，都会将一些能简写的SQL就简写，但其实这种做法也略微有些问题，因为隐式的这种写法，在MySQL底层都需要做一次转换，将其转换为完整的写法，因此简写的SQL会比完整的SQL多一步转化过程，如果你考虑极致程度的优化，也切记将SQL写成完整的语法。2。1。15、基于联合索引查询时请务必确保字段的顺序性
　　在之前聊到过《联合索引的最左前缀原则》，想要基于建立的联合索引查询数据，就必须要按照索引字段的顺序去查询数据，否则可能导致所以完全利用联合索引，虽然MySQL8。0版本中推出了《索引跳跃扫描机制》，但这种方案也会存在较大的开销，同时还有很强的局限性，所以最好在写SQL时，依旧遵循索引的最左前缀原则撰写。2。1。16、客户端的一些操作可以批量化完成
　　批量新增某些数据、批量修改某些数据的状态。。。。。，这类需求在一个项目中也比较常见，一般的做法如下：for（xxObjectobj：xxObjs）｛xxDao。insert（obj）；｝xxDao。insert（obj）对应的SQL如下：insertintotbxxxvalues（。。。。。。）；复制代码
　　这种情况确实可以实现批量插入的效果，但是每次都需要往MySQL发送SQL语句，这其中自然会带来额外的网络开销以及耗时，因此上述实现可以更改为如下：xxDao。insertBatch（xxObjs）；xxDao。insertBatch（xxObjs）对应的SQL如下：insertintotbxxxvalues（。。。。。。），（。。。。。。），（。。。。。。），（。。。。。。），。。。。。；复制代码
　　这样会组合成一条SQL发送给MySQL执行，能够在很大程度上节省网络资源的开销，提升批量操作的执行效率。
　　这样的方式同样适用于修改场景，如果一个业务会出现批量修改的情况时，也切记不要用for循环来调用update语句对应的接口，而是应该再写一个updatereplace语句的批量修改接口。2。1。17、明确仅返回一条数据的语句可以使用limit1selectfromzzuserswhereusername竹子；selectfromzzuserswhereusername竹子limit1；复制代码
　　上述这两条SQL语句都是根据姓名查询一条数据，但后者大多数情况下会比前者好，因为加上limit1关键字后，当程序匹配到一条数据时就会停止扫描，如果不加的情况下会将所有数据都扫描一次。所以一般情况下，如果确定了只需要查询一条数据，就可以加上limit1提升性能。
　　但在一些极端情况下，性能可能相差不大，比如要查询的数据位于表索引文件的最后面，那么依旧会全部扫描一次。还有一种情况是基于主键唯一索引字段查询数据时，由于这些字段值本身具备唯一性，因此MySQL在执行时，当匹配到第一个值时就会自动停止扫描，因此上述这个方案只适用于普通索引字段、或表中的普通字段。2。2、SQL优化的业内标准
　　评判任何一件事情到底有没有做好都会有标准，而SQL语句的执行时间也一样，业内也早有了相应的标准，相信大家一定都听说过下述这个用户体验原则：
　　客户端访问时，能够在1s内得到响应，用户会觉得系统响应很快，体验非常好。
　　客户端访问时，13秒内得到响应，处于可以接受的阶段，其体验感还算不错。
　　客户端访问时，需要等待35秒时才可响应，这是用户就感觉比较慢了，体验有点糟糕。
　　客户端访问时，一旦响应超过5秒，用户体验感特别糟糕，通常会选择离开或刷新重试。
　　上述这四条是用户体验感的四个等级，一般针对于C端业务而言，基本上都需要将接口响应速度控制到第二等级，即最差也要三秒内给用户返回响应，否则会导致体验感极差，从而让用户对产品留下不好的印象。
　　所谓的三秒原则通常是基于C端业务而言的，对于B端业务来说，通常用户的容忍度会高一些，也包括B端业务的业务逻辑会比C端更为复杂一些，所以可将响应速度控制到第三等级，也就是5s内能够得到响应。针对于一些特殊类型的业务，如后台计算型的业务，好比跑批对账、定时调度。。。。等，这类因为本身业务就特殊，因此可不关注其响应速度。
　　回归前面的用户三秒体验原则，似乎三秒也不难做到对嘛？基本上SQL语句在13秒内都能执行完成呀，但请牢记：这个三秒并不能全部分配给SQL执行，为什么呢？因为用户感受到的响应速度会由多方面的耗时组成，如下：
　　从上图观察中可得知，所谓给用户的响应时间其实会包含各方面的耗时，也就是这所有的过程加一块儿，必须要在13s内给出响应，而SQL耗时属于系统耗时数据操作耗时这部分，因此留给SQL语句执行的时间最多只能有500ms，一般在用户量较大的门户网站中，甚至要求控制在10ms、30ms、50ms以内。三、MySQL索引优化
　　1050ms听起来是个很难抵达的标准，但实际大部分走索引查询的语句基本上都能控制在该标准内，那又该如何判断一条SQL会不会走索引呢？这里需要使用一个工具：explain，下面一起来聊一聊。3。1、explain分析工具
　　在之前的《索引应用篇》中曾简单聊到过ExPlain这个工具，它本身是MySQL自带的一个执行分析工具，可使用于select、insert、update、delete、repleace等语句上，需要使用时只需在SQL语句前加上一个explain关键字即可，然后MySQL会对应语句的执行计划列出，比如：
　　上述这些字段在之前也简单提到过，但并未展开细聊，所以在这里就先对其中的每个字段做个全面详解（MySQL8。0版本中才有12个字段，MySQL5。x版本只有10个字段）。3。1。1、id字段
　　这是执行计划的ID值，一条SQL语句可能会出现多步执行计划，所以会出现多个ID值，这个值越大，表示执行的优先级越高，同时还会出现四种情况：ID相同：当出现多个ID相同的执行计划时，从上往下挨个执行。ID不同时：按照ID值从大到小依次执行。ID有相同又有不同：先从到到小依次执行，碰到相同ID时从上往下执行。ID为空：IDnull时，会放在最后执行。3。1。2、selecttype字段
　　当前执行的select语句其具体的查询类型，有如下取值：SIMPLE：简单的select查询语句，不包含union、子查询语句。PRIMARY：union或子查询语句中，最外层的主select语句。SUBQUEPY：包含在主select语句中的第一个子查询，如select。。。xx（select。。。）。DERIVED：派生表，指包含在from中的子查询语句，如select。。。from（select。。。）。DEPENDENTSUBQUEPY：复杂SQL中的第一个select子查询（依赖于外部查询的结果集）。UNCACHEABLESUBQUERY：不缓存结果集的子查询语句。UNION：多条语句通过union组成的查询中，第二个以及更后面的select语句。UNIONRESULT：union的结果集。DEPENDENTUNION：含义同上，但是基于外部查询的结果集来查询的。UNCACHEABLEUNION：含义同上，但查询出的结果集不会加入缓存。MATERIALIZED：采用物化的方式执行的包含派生表的查询语句。
　　这个字段主要是说明当前查询语句所属的类型，以及在整条大的查询语句中，当前这个查询语句所属的位置。3。1。3、table字段
　　表示当前这个执行计划是基于哪张表执行的，这里会写出表名，但有时候也不一定是物理磁盘中存在的表名，还有可能出现如下格式：：基于idN的查询结果集，进一步检索数据。unionM，N：会出现在查询类型为UNIONRESULT的计划中，表示结果由idM，N。。。的查询组成。：基于idN的子查询结果，进一步进行数据检索。：基于磁盘中已创建的某张表查询。
　　一句话总结就是：这个字段会写明，当前的这个执行计划会基于哪个数据集查询，有可能是物理表、有可能是子查询的结果、也有可能是其他查询生成的派生表。3。1。4、partitions字段
　　这个字段在早版本的explain工具中不存在，这主要是用来显示分区的，因为后续版本的MySQL中支持表分区，该列的值表示检索数据的分区。3。1。5、type字段
　　该字段表示当前语句执行的类型，可能出现的值如下：all：全表扫描，基于表中所有的数据，逐行扫描并过滤符合条件的数据。index：全索引扫描，和全表扫描类似，但这个是把索引树遍历一次，会比全表扫描要快。range：基于索引字段进行范围查询，如between、、、in。。。。等操作时出现的情况。indexsubquery：和上面含义相同，区别：这个是基于非主键、唯一索引字段进行in操作。uniquesubquery：执行基于主键索引字段，进行in操作的子查询语句会出现的情况。indexmerge：多条件查询时，组合使用多个索引来检索数据的情况。refornull：基于次级（非主键）索引做条件查询时，该索引字段允许为null出现的情况。fulltext：基于全文索引字段，进行查询时出现的情况。ref：基于非主键或唯一索引字段查找数据时，会出现的情况。eqref：连表查询时，基于主键、唯一索引字段匹配数据的情况，会出现多次索引查找。const：通过索引一趟查找后就能获取到数据，基于唯一、主键索引字段查询数据时的情况。system：表中只有一行数据，这是const的一种特例。null：表中没有数据，无需经过任何数据检索，直接返回结果。
　　这个字段的值很重要，它决定了MySQL在执行一条SQL时，访问数据的方式，性能从好到坏依次为：完整的性能排序：nullsystemconsteqrefreffulltextrefornullindexmergeuniquesubqueryindexsubqueryrangeindexall常见的性能排序：systemconsteqrefreffulltextrangeindexall
　　一般在做索引优化时，一般都会要求最好优化到ref级别，至少也要到range级别，也就是最少也要基于次级索引来检索数据，不允许出现index、all这类全扫描的形式。3。1。6、possiblekeys字段
　　这个字段会显示当前执行计划，在执行过程中可能会用到哪些索引来检索数据，但要注意的一点是：可能会用到并不代表一定会用，在某些情况下，就算有索引可以使用，MySQL也有可能放弃走索引查询。3。1。7、key字段
　　前面的possiblekeys字段表示可能会用到的索引，而key这个字段则会显示具体使用的索引，一般情况下都会从possiblekeys的值中，综合评判出一个性能最好的索引来进行查询，但也有两种情况会出现keynull的这个场景：possiblekeys有值，key为空：出现这种情况多半是由于表中数据不多，因此MySQL会放弃索引，选择走全表查询，也有可能是因为SQL导致索引失效。possiblekeys、key都为空：表示当前表中未建立索引、或查询语句中未使用索引字段检索数据。
　　默认情况下，possiblekeys有值时都会从中选取一个索引，但这个选择的工作是由MySQL优化器自己决定的，如果你想让查询语句执行时走固定的索引，则可以通过forceindex、ignoreindex的方式强制指定。3。1。8、keylen字段
　　这个表示对应的执行计划在执行时，使用到的索引字段长度，一般情况下都为索引字段的长度，但有三种情况例外：如果索引是前缀索引，这里则只会使用创建前缀索引时，声明的前N个字节来检索数据。如果是联合索引，这里只会显示当前SQL会用到的索引字段长度，可能不是全匹配的情况。如果一个索引字段的值允许为空，keylen的长度会为：索引字段长度1。3。1。9、ref字段
　　显示索引查找过程中，查询时会用到的常量或字段：const：如果显示这个，则代表目前是在基于主键字段值或数据库已有的常量（如null）查询数据。select。。。where主键字段主键值；select。。。where索引字段isnull；显示具体的字段名：表示目前会基于该字段查询数据。func：如果显示这个，则代表当与索引字段匹配的值是一个函数，如：select。。。where索引字段函数（值）；3。1。10、rows字段
　　这一列代表执行时，预计会扫描的行数，这个数字对于InnoDB表来说，其实有时并不够准确，但也具备很大的参考价值，如果这个值很大，在执行查询语句时，其效率必然很低，所以该值越小越好。3。1。11、filtered字段
　　这个字段在早版本中也不存在，它是一个百分比值，意味着表中不会扫描的数据百分比，该值越小则表示执行时会扫描的数据量越大，取值范围是0。00100。00。3。1。12、extra字段
　　该字段会包含MySQL执行查询语句时的一些其他信息，这个信息对索引调优而言比较重要，可以带来不小的参考价值，但这个字段会出现的值有很多种，如下：Usingindex：表示目前的查询语句，使用了索引覆盖机制拿到了数据。Usingwhere：表示目前的查询语句无法从索引中获取数据，需要进一步做回表去拿表数据。Usingtemporary：表示MySQL在执行查询时，会创建一张临时表来处理数据。Usingfilesort：表示会以磁盘内存完成排序工作，而完全加载数据到内存来完成排序。Selecttablesoptimizedaway：表示查询过程中，对于索引字段使用了聚合函数。Usingwhere；Usingindex：表示要返回的数据在索引中包含，但并不是索引的前导列，需要做回表获取数据。NULL：表示查询的数据未被索引覆盖，但where条件中用到了主键，可以直接读取表数据。Usingindexcondition：和Usingwhere类似，要返回的列未完全被索引覆盖，需要回表。Usingjoinbuffer（BlockNestedLoop）：连接查询时驱动表不能有效的通过索引加快访问速度时，会使用joinbuffer来加快访问速度，在内存中完成Loop匹配。ImpossibleWHERE：where后的条件永远不可能成立时提示的信息，如where1！1。ImpossibleWHEREnoticedafterreadingconsttables：基于唯一索引查询不存在的值时出现的提示。constrownotfound：表中不存在数据时会返回的提示。distinct：去重查询时，找到某个值的第一个值时，会将查找该值的工作从去重操作中移除。Starttemporary，Endtemporary：表示临时表用于DuplicateWeedout半连接策略，也就是用来进行semijoin去重。UsingMRR：表示执行查询时，使用了MRR机制读取数据。Usingindexforskipscan：表示执行查询语句时，使用了索引跳跃扫描机制读取数据。Usingindexforgroupby：表示执行分组或去重工作时，可以基于某个索引处理。FirstMatch：表示对子查询语句进行Semijoin优化策略。Notablesused：查询语句中不存在from子句时提示的信息，如desctablename；。。。。。。。
　　除开上述内容外，具体的可参考《explainExtra字段详解》，其中介绍了Extra字段可能会出现的所有值，最后基于Extra字段做个性能排序：UsingindexNULLUsingindexconditionUsingwhereUsingwhere；UsingindexUsingjoinbufferUsingfilesortUsingMRRUsingindexforskipscanUsingtemporaryStrarttemporary，EndtemporaryFirstMatch
　　上面这个排序中，仅列出了一些实际查询执行时的性能排序，对于一些不重要的就没有列出了。3。2、索引优化参考项
　　在上面咱们简单介绍了explain工具中的每个字段值，字段数量也比较多，但在做索引优化时，值得咱们参考的几个字段为：key：如果该值为空，则表示未使用索引查询，此时需要调整SQL或建立索引。type：这个字段决定了查询的类型，如果为index、all就需要进行优化。rows：这个字段代表着查询时可能会扫描的数据行数，较大时也需要进行优化。filtered：这个字段代表着查询时，表中不会扫描的数据行占比，较小时需要进行优化。Extra：这个字段代表着查询时的具体情况，在某些情况下需要根据对应信息进行优化。
　　PS：在explain语句后面紧跟着showwarings语句，可以得到优化后的查询语句，从而看出优化器优化了什么。3。3、索引优化实践
　　上面了解了索引优化时的一些参考项，接着来聊聊索引优化的实践，不过在优化之前要先搞清楚什么是索引优化，其实无非就两点：把SQL的写法进行优化，对于无法应用索引，或导致出现大数据量检索的语句，改为精准匹配的语句。对于合适的字段上建立索引，确保经常作为查询条件的字段，可以命中索引去检索数据。
　　总归说来说去，也就是要让SQL走索引执行，但要记住：并非走了索引就代表你的执行速度就快，因为如果扫描的索引数据过多，依旧可能会导致SQL执行比较耗时，所以也要参考type、rows、filtered三个字段的值，来看看一条语句执行时会扫描的数据量，判断SQL执行时是否扫描了额外的行记录，综合分析后需要进一步优化到更细粒度的检索。
　　索引优化其实本质上，也就是遵循前面第二阶段提出的SQL小技巧撰写语句，以及合理的使用与建立索引，对于索引怎么建立和使用才最好，具体可参考《索引应用篇建立与使用索引的正确姿势》。
　　一般来说，SQL写好了，索引建对了，基本上就已经优化到位了，对于一些无可避免的慢SQL执行，比如复杂SQL的执行、深分页等情况，要么就从业务层面着手解决，要么就接受一定的耗时，毕竟凡事不可能做到十全十美。四、SQL优化篇总结
　　到这里《SQL优化篇》又接近尾声了，其实所谓的SQL优化，本质上是改善SQL的写法，理解一些SQL导致索引失效的场景，以及撰写SQL时的一些技巧，就能写出一手优质SQL，当你写的所有语句执行效率都还不错，那你就能够被称得上是一位写SQL的高手。
　　不过做过SQL优化的小伙伴，其实应该能够发现这里还少写了一个十分重要的内容，也就是慢查询语句优化，这里是刻意为之，对于慢查询语句的优化，本质上脱离了SQL优化的范畴，更多属于线上问题的一种情况，有些SQL在开发环境中执行时，可能效率并不算低，但放到线上时可能会偶尔出现的执行缓慢的情况，因此对于这类SQL语句该如何排查呢？具体的方法会放到下篇文章：《MySQL线上排查篇》来详细阐述