慢SQL分析与优化

作者｜吴楼华背景从系统设计的角度来说，从系统的设计和构建到数据的逐渐增长，SQL的执行效率可能会变差。为了持续支持业务发展，我们需要对慢SQL进行分析和优化，严重时甚至需要重构整个系统。因此，我们往往需要在系统设计前对业务进行充分调研，遵守系统设计规范，并在系统运行时定期结合当前业务发展分析系统瓶颈。从数据库的角度来看，每条SQL的执行都需要消耗一定的I/O资源，而SQL执行的快慢决定了资源被占用的时间长短。假设有一条慢SQL占用了30%的资源，总共1分钟。那么在这1分钟内，其他SQL可以分配的资源总量是70%。在这个循环中，当资源分配完毕后，所有新的SQL执行都会排队。所以往往一个慢的SQL就会影响整个业务。本文只讨论MySQL-InnoDB的情况。优化方法SQL语句执行效率的主要因素是SQL执行后返回给客户端的数据量；数据量越大，扫描的I/O次数越多，数据库服务器的IO越容易成为瓶颈。取数据的方式是数据在缓存中还是在磁盘上；是否可以通过全局索引快速解决；是否结合谓词条件命中全局索引，加快扫描速度。排序、子查询、聚合、关联等数据处理方式，一般需要先将数据取到临时表中，然后再对数据进行处理；对于数据量大的计算，会消耗计算节点大量的CPU资源，使得数据处理变慢；是否选择合适的join方式优化思路减少数据扫描（减少磁盘访问）尽量在查询中加入一些可以提前过滤数据的谓词条件，比如根据时间过滤数据等，可以减少数据量扫描，查询更友好；扫描大表数据时是否能命中索引，降低回表成本，避免全表扫描。返回更少的数据（减少网络传输或磁盘访问）减少交互次数（减少网络传输）将数据存储在更快的地方查询涉及大表，如果返回数据量不大，则无法进一步优化变化频率不高但是访问频率高。这时候就应该考虑将返回的数据放在应用端的缓存中，或者放在Redis等缓存中，以提高访问速度。ReduceserverCPUoverhead（减少CPU和内存开销）避免大事务操作占用更多资源（增加资源）optimizecasedatapagingoptimizationselect*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;}optimizeGROUPBY提高GROUPBY语句的效率可以通过在GROUPBY之前过滤掉不需要的记录来实现。以下两个查询返回相同的结果，但第二个查询要快得多。效率低：selectjob,avg(sal)fromtable_demogroupbyjobhavingjob='manager'效率高：selectjob,avg(sal)fromtable_demowherejob='manager'groupbyjobrangequeryif有一定的If范围（大于小于）查询中存在一列，右边的列还有意义吗？解释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时，查找索引中的所有trade_date_time从'2019-05-01'到'2020-05-01'范围内的主键id。假设有100万。对这些ids进行排序（为了优化下一步回表的I/O操作，因为在一次磁盘I/O中可能会取到很多靠在一起的主键）回表找到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'举个例子：在范围内查找时，直接找到最大值和最小值，然后遍历链表，所以只能使用trade_date_time索引，不能使用org_code索引。基于MySQL5.6+的索引下推特性，org_code字段虽然不能使用索引树，但是可以用来过滤回表的主键id号。总结：对于这种情况，索引效果[org_code,trade_date_time]>[trade_date_time,org_code]>[trade_date_time]。在实际业务场景中，trade_date_time肯定会出现在搜索条件中，但org_code可能不会出现。因此，索引的设计需要结合实际业务需求。按索引优化排序：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;optimized:执行总行数为：6行，耗时34ms。MySQL不使用索引与查询列无关，只与索引本身有关，where条件，orderbyfield，groupbyfield。索引的作用一个是查找，另一个是排序。业务拆分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)多返回查询。索引下推假设有一个索引(name,age)，执行SQL：select*fromtuserwherenamelike'Zhang%'andage=10;MySQL5.6之后，存储引擎根据(name,age)组合索引查找，由于组合索引中包含列，所以存储引擎直接根据联合索引中的age=10进行过滤。根据过滤后的数据，逐一扫描回表。索引下推使用条件只能用于range,ref,eq_ref,ref_or_null访问方法；仅适用于InnoDB和MyISAM存储引擎及其分区表；对于存储引擎，索引下推仅适用于二级索引（也称为辅助索引）；索引下推的目的是为了减少回表次数，也就是减少IO操作。对于聚簇索引，数据和索引是在一起的，不存在回表的情况。不能下推引用子查询的条件；引用存储函数的条件不能下推，因为存储引擎不能调用存储函数。思考：一张MySQL表能存储多少数据？为什么要控制单行的数据大小？为什么优化案例4中优化前的SQL上不去索引呢？总结无论数据库硬件水平如何，数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都会对SQL执行效率产生影响。只有追求极致，我们系统的可用性和扩展性才不会随着业务的增长而变差。