慢 SQL 分析与优化

发布时间：2025-11-05 16:01:28 来源：云智核 作者：应用开发

作者 ｜ 伍楼华

背景介绍

从系统设计角度看，分析一个系统从设计搭建到数据逐步增长，优化SQL 执行效率可能会出现劣化，分析为继续支撑业务发展，优化我们需要对慢 SQL 进行分析和优化，分析严峻的优化情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、分析遵守系统设计规范，优化在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析分析。

从数据库角度看，优化每个 SQL 执行都需要消耗一定 I/O 资源，分析SQL 执行的优化快慢，决定了资源被占用时间的分析长短。假如有一条慢 SQL 占用了 30%的优化资源共计 1 分钟。那么在这 1 分钟时间内，分析其他 SQL 能够分配的资源总量就是 70%，如此循环，当资源分配完的时候，所有新的 SQL 执行将会排队等待。所以往往一条慢 SQL 会影响到整个业务。

本文仅讨论 MySQL-InnoDB 的情况。

优化方式

SQL 语句执行效率的主要因素

数据量

取数据的方式

数据加工的方式

优化思路

优化案例

优化方式一：偏移 id

lastId = 0 or min(id)

do {

select * from table_demo where type = ? and id >{#lastId} limit ?;

lastId = max(id)

优化方式二：分段查询

该方式较方式一的优点在于可并行查询，每个分段查询互不依赖；较方式一的缺点在于较依赖数据的连续性，若数据过于分散，代价较高。

minId = min(id) maxId = max(id)

for(int i = minId; i<= maxId; i+=pageSize){

select * from table_demo where type = ? and id between i and i+ pageSize;

提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多。

低效:

高效:

联合索引中如果有某个列存在范围（大于小于）查询，其右边的列是否还有意义？

explain select count(1) from statement where org_code=1012 and trade_date_time >= 2019-05-01 00:00:00 and trade_date_time<=2020-05-01 00:00:00

explain select * from statement where org_code=1012 and trade_date_time >= 2019-05-01 00:00:00 and trade_date_time<=2020-05-01 00:00:00 limit 0, 100

以查找 trade_date_time >=2019-05-01 and trade_date_time <=2020-05-01 and org_code=1020为例：

小结：对于该 case, 索引效果[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`)

慢 SQL:

SELECT id,....,creator,modifier,create_time,update_time FROM statement

优化前：SQL 执行超时被 kill 了

SELECT id,....,creator,modifier,create_time,update_time FROM statement

优化后：执行总行数为:6 行，耗时 34ms。

拆分优化：

随着业务数据的增长 status=S的数据基本占据数据的 90%以上，此时该条件无法走索引。我们可以结合业务特征，对数据获取按日期进行拆分。

date = now; minDate = now - 10 days

while(date > minDate) {

select * from order where order_date={#date} and status=S and update_time < now-5min limit 500

date = data + 1

数据库结构优化

SQL 语句优化

大表优化

原理剖析

MySQL 逻辑架构图：

索引的优缺点

优点

缺点

索引的数据结构

索引页结构

索引页由七部分组成，其中 Infimum 和 Supremum 也属于记录，只不过是虚拟记录，这里为了与用户记录区分开，还是决定将两者拆开。

MySQL 有 4 种存储格式：

Dynamic 行存储格式下，对于处理行溢出（当一个字段存储长度过大时，会发生行溢出）时，仅存放溢出页内存地址。

索引的设计原则

索引优化之 MRR

例如有一张表 user，主键 id，普通字段 age，为 age 创建非聚集索引，有一条查询语句 select* user from table where age > 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过滤。按照过滤后的数据再一一进行回表扫描。

索引下推的目的是为了减少回表次数，也就是要减少 IO 操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

思考：

总结

抛开数据库硬件层面，数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响，我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致，才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。

相关文章