粗心！平时不关注慢SQL，现在系统要重构了……

1。背景从系统设计的角度来看，一个系统从设计到数据逐渐成长，SQL的执行效率可能会变差。为了持续支撑业务的发展，我们需要对慢SQL进行分析和优化，严重的时候甚至需要重构整个系统。因此，我们往往需要在系统设计前对业务进行充分调研，遵守系统设计规范，并在系统运行时定期结合当前业务发展分析系统瓶颈。从数据库的角度来看，每条SQL的执行都需要消耗一定的I/O资源，而SQL执行的快慢决定了资源被占用的时间长短。假设有一条慢SQL占用了30%的资源，总共1分钟。那么在这1分钟内，其他SQL可以分配的资源总量是70%。在这个循环中，当资源分配完毕后，所有新的SQL执行都会排队。所以往往一个慢的SQL就会影响整个业务。本文只讨论MySQL-InnoDB的情况。2、优化方法SQL语句执行效率的主要影响因素：1）数据量SQL执行后返回给客户端的数据大小；数据量越大，扫描的I/O次数越多，数据库服务器的IO越容易成为瓶颈。2）取数据的方式是数据在缓存中还是在磁盘上；是否可以通过全局索引快速解决；是否可以结合谓词条件命中全局索引，加快扫描速度。3）排序、子查询、聚合、关联等数据处理方式，一般需要先将数据取到临时表中，然后再对数据进行处理；对于大数据量的计算，会消耗大量计算节点的CPU资源。使数据处理变慢；是否选择合适的join方式。1.优化思路1）减少数据扫描（减少磁盘访问）尽量在查询中加入一些可以提前过滤数据的谓词条件，比如根据时间过滤数据等，可以减少扫描的数据量对查询更友好；表数据大时是否可以命中索引，降低回表成本，避免全表扫描。2）返回更少的数据（减少网络传输或磁盘访问）3）减少交互次数（减少网络传输），将数据存储在更快的地方；查询涉及大表，无法进一步优化。如果返回的数据量不大，变化频率不高，但访问频率很高。这时候就应该考虑将返回的数据放在应用端的缓存中，或者放在Redis等缓存中，以提高访问速度。4)ReduceserverCPUoverhead(降低CPU和内存开销)5)避免大事务操作6)Utilizemoreresources(增加资源)2.优化案例1)数据分页优化select*fromtable_demowheretype=?限制？，？;优化方法一：OffsetidlastId=0ormin(id)do{select*fromtable_demowheretype=?andid>{#lastId}limit?;lastId=max(id)}while(isNotEmpty)优化方式二：分段查询方式相比方式一，优点是可以并行查询，每个分段查询做不依赖彼此；与方法1相比的缺点是更依赖于数据的连续性。如果数据过于分散，成本很高。minId=min(id)maxId=max(id)for(inti=minId;i<=maxId;i+=pageSize){select*fromtable_demowheretype=?andidbetweeniandi+pageSize;}2)优化GROUPBY通过在GROUPBY之前过滤掉不需要的记录来提高GROUPBY语句的效率。以下两个查询返回相同的结果，但第二个查询要快得多。效率低下：selectjob,avg(sal)fromtable_demogroupbyjobhavingjob='manager'高效selectjob,avg(sal)fromtable_demowherejob='manager'groupbyjob3)如果有某个列存在于一个范围（大于小于）的查询，右边的列还有意义吗？解释selectcount(1)from语句whereorg_code='1012'andtrade_date_time>='2019-05-0100:00:00'andtrade_date_time<='2020-05-0100:00:00'解释select*fromstatementwhereorg_code='1012'andtrade_date_time>='2019-05-0100:00:00'andtrade_date_time<='2020-05-0100:00:00'limit0,100说明select*fromstatementwhereorg_code='1012'andtrade_date_time>='2019-05-0100:00:00'andtrade_date_time<='2020-05-0100:00:00'在使用单键索引trade_date_time的情况下从索引中查找'2019-05-01'和'2020-05-01'之间trade_date_time的所有主键id，假设有100万个；b.对这些ids进行排序（为了优化I/O操作，因为在一次磁盘I/O中可能会取到很多closeprimarykey）；C。返回表，找出100万行，然后逐条扫描，过滤掉org_code='1020'的行。使用联合索引trade_date_time,org_code——联合索引trade_date_time,org_code的底层结构推导如下：取trade_date_time>='2019-05-01'andtrade_date_time<='2020-05-01'andorg_code的搜索='1020'为例：a.搜索范围时，直接求最大值和最小值，然后遍历链表，所以只能使用trade_date_time索引，不能使用org_code索引；b.基于MySQL5.6+的索引下推特性，虽然org_code字段不能使用索引树，但是可以用来过滤回表的主键id号。总结：对于这种情况，索引效果[org_code,trade_date_time]>[trade_date_time,org_code]>[trade_date_time]。在实际业务场景中，trade_date_time肯定会出现在搜索条件中，但org_code可能不会出现。因此，索引的设计需要结合实际业务需求。4)按索引优化排序：KEY`idx_account_trade_date_time`(`account_number`,`trade_date_time`),KEY`idx_trade_date_times`(`trade_date_time`)KEY`idx_createtime`(`create_time`),slowSQL:SELECTid,...。,creator,modifier,create_time,update_timeFROMstatementWHERE(account_number='XXX'ANDcreate_time>='2022-04-2406:03:44'ANDcreate_time<='2022-04-2408:03:44'ANDdc_flag='C')ORDERBYtrade_date_timeDESC,idDESCLIMIT0,1000;优化前：SQL执行超时被killSELECTid,....,creator,modifier,create_time,update_timeFROMstatementWHERE(account_number='XXX'ANDcreate_time>='2022-04-2406:03:44'ANDcreate_time<='2022-04-2408:03:44'ANDdc_flag='C')ORDERBYcreate_timeDESC,idDESCLIMIT0,1000;优化后：执行总行数为：6行，耗时34ms。MySQL不使用索引与查询列无关，只与索引本身有关，where条件，orderbyfield，groupbyfield。索引的作用一个是查找，另一个是排序。5）业务拆分select*fromorderwherestatus='S'andupdate_timeminDate){select*fromorderwhereorder_date={#date}andstatus='S'andupdate_time18;（注意查询语句中的结果是*）MySQL5.5及之前版本如何查询？先通过非聚集索引查询age>18的第一条数据，得到主键id；然后根据非聚簇索引中叶子节点存储的主键id查询聚簇索引中的行数据；根据age>18的数据项个数，每次查询聚簇索引，都会回调这个过程回表。上述步骤的缺点是什么？如果age>18的数据很多，那么每次回表需要3次IO（假设B+树的高度为3），会导致查询效率低下。在MySQL5.6中，对上述问题进行了优化。优化器首先查询所有age>3的数据的主键id，对所有主键的id进行排序，将排序后的结果缓存到read_rnd_buffer中，然后在聚簇索引上的查询中使用排序后的主键。如果两个主键的范围相似，可以在同一个数据页中依次获取，磁盘io的过程会大大减少。此优化过程称为多范围读取(MRR)多返回查询。6.索引下推假设有一个索引(name,age)，执行SQL：select*fromtuserwherenamelike'Zhang%'andage=10;MySQL5.6以后，存储引擎根据(name,age)联合索引查找，由于联合索引包含列，所以存储引擎直接根据联合索引中的age=10进行过滤。根据过滤后的数据，逐一扫描回表。索引下推使用条件只能用于range、ref、eq_ref、ref_or_null访问方式；仅适用于InnoDB和MyISAM存储引擎及其分区表；对于存储引擎，索引下推只适用于二级索引（也叫辅助索引）；索引下推的目的是为了减少回表次数，也就是减少IO操作。对于聚簇索引，数据和索引是在一起的，不存在回表的情况。不能下推引用子查询的条件；引用存储函数的条件不能下推，因为存储引擎不能调用存储函数。七、思考1）一张MySQL表能存储多少数据？2）为什么要控制单行的数据大小？3）为什么优化案例4中优化前的SQL上不去索引？4.总结无论数据库硬件水平如何，数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都会对SQL执行效率产生影响。只有保持敏锐，追求极致，我们系统的可用性和扩展性才不会随着业务的增长而下降。