慢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 这样的缓存当中，以提高存取速度。减少服务器 CPU 开销（减少 CPU 及内存开销）避免大事务操作利用更多资源（增加资源）优化案例数据分页优化select*fromtable_demowheretype=limit,;优化方式一：偏移 idlastId=0ormin(id)do{select*fromtable_demowheretype=andid>{#lastId}limit;lastId=max(id)}while(isNotEmpty)优化方式二：分段查询该方式较方式一的优点在于可并行查询，每个分段查询互不依赖；较方式一的缺点在于较依赖数据的连续性，若数据过于分散，代价较高。minId=min(id)maxId=max(id)for(inti=minId;i='2019-05-0100:00:00'andtrade_date_time='2019-05-0100:00:00'andtrade_date_time='2019-05-0100:00:00'andtrade_date_time='2019-05-01' and trade_date_time [trade_date_time, org_code]>[trade_date_time]。实际业务场景中，检索条件中 trade_date_time 基本上肯定会出现，但 org_code 却不一定，故索引的设计还需要结合实际业务需求。优化 Order by索引：KEY`idx_account_trade_date_time`(`account_number`,`trade_date_time`),KEY`idx_trade_date_times`(`trade_date_time`)KEY`idx_createtime`(`create_time`),慢 SQL:SELECTid,....,creator,modifier,create_time,update_timeFROMstatementWHERE(account_number='XXX'ANDcreate_time>='2022-04-2406:03:44'ANDcreate_time='2022-04-2406:03:44'ANDcreate_timeminDate){select*fromorderwhereorder_date={#date}andstatus='S'andupdate_time 18;(注意查询语句中的结果是*)在 MySQL5.5 以及之前的版本中如何查询呢？先通过非聚集索引查询到 age>18 的第一条数据，获取到了主键 id；然后根据非聚集索引中的叶子节点存储的主键 id 去聚集索引中查询行数据；根据 age>18 的数据条数每次查询聚集索引，这个过程叫做回表。上述的步骤有什么缺点呢？如何 age>18 的数据非常多，那么每次回表都需要经过 3 次 IO(假设 B+树的高度是 3)，那么会导致查询效率过低。在 MySQL5.6 时针对上述问题进行了优化，优化器先查询到 age>3 的所有数据的主键 id，对所有主键的 id 进行排序，排序的结果缓存到 read_rnd_buffer，然后通过排好序的主键在聚簇索引中进行查询。如果两个主键的范围相近，在同一个数据页中就可以之间按照顺序获取，那么磁盘 io 的过程将会大大降低。这个优化的过程就叫做 Multi Range Read(MRR) 多返回查询。索引下推假设有索引(name, age), 执行 SQL: select * from tuser where name like '张%' and age=10;MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接在联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。索引下推使用条件只能用于range、 ref、 eq_ref、ref_or_null访问方法；只能用于InnoDB和 MyISAM存储引擎及其分区表；对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;索引下推的目的是为了减少回表次数，也就是要减少 IO 操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。引用了子查询的条件不能下推；引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。思考：MySQL 一张表到底能存多少数据？为什么要控制单行数据大小优化案例 4 中优化前的 SQL 为什么走不到索引？总结抛开数据库硬件层面，数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响，我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致，才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。参考资料https://help.aliyun.com/document_detail/311122.htmlhttps://blog.csdn.net/qq_32099833/article/details/123150701https://www.cnblogs.com/tufujie/p/9413852.html