【MySQL】慢 SQL 分析与优化

1、背景介绍

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

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

日常开发中，我们经常会遇到数据库慢查询。那么导致数据慢查询都有哪些常见的原因呢？

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

2、基础巩固

2.1 优化方式

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

(1) 数据量

• SQL 执行后返回给客户端的数据量的大小；
• 数据量越大需要扫描的 I/O 次数越多，数据库服务器的 IO 更容易成为瓶颈。

(2) 取数据的方式

• 数据在缓存中还是在磁盘上；
• 是否能够通过全局索引快速寻址；
• 是否结合谓词条件命中全局索引加速扫描。

(3) 数据加工的方式

• 排序、子查询、聚合、关联等，一般需要先把数据取到临时表中，再对数据进行加工；
• 对于数据量比较多的计算，会消耗大量计算节点的 CPU 资源，让数据加工变得更加缓慢；
• 是否选择了合适的 join 方式

2.2 优化思路

（1）减少数据扫描（减少磁盘访问）

• 尽量在查询中加入一些可以提前过滤数据的谓词条件，比如按照时间过滤数据等，可以减少数据的扫描量，对查询更友好；
• 在扫描大表数据时是否可以命中索引，减少回表代价，避免全表扫描。

（2）返回更少数据（减少网络传输或磁盘访问）

（3）减少交互次数（减少网络传输）

• 将数据存放在更快的地方
• 某条查询涉及到大表，无法进一步优化，如果返回的数据量不大且变化频率不高但访问频率很高，此时应该考虑将返回的数据放在应用端的缓存当中或者 Redis 这样的缓存当中，以提高存取速度。

（4）减少服务器 CPU 开销（减少 CPU 及内存开销）
（5）避免大事务操作
（6）利用更多资源（增加资源）

2.3 数据库结构优化

1. 范式优化：表的设计合理化（符合 3NF），比如消除冗余（节省空间）；
2. 反范式优化：比如适当加冗余等（减少 join）
3. 拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O，一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法，可按月建表分区。

2.4 SQL 语句优化

SQL 检查状态及分数计算逻辑

1. 尽量避免使用子查询
2. 用 IN 来替换 OR
3. 读取适当的记录 LIMIT M,N，而不要读多余的记录
4. 禁止不必要的 Order By 排序
5. 总和查询可以禁止排重用 union all
6. 避免随机取记录
7. 将多次插入换成批量 Insert 插入
8. 只返回必要的列，用具体的字段列表代替 select * 语句
9. 区分 in 和 exists
10. 优化 Group By 语句
11. 尽量使用数字型字段
12. 优化 Join 语句

大表优化

1. 分库分表（水平、垂直）
2. 读写分离
3. 数据定期归档

3、优化案例

3.1 数据分页优化

select * from table_demo where type = ?  limit ?,?;

优化方式一：偏移 id

lastId = 0 or min(id)
do {
select * from table_demo where type = ? and id >{#lastId}  limit ?;
lastId = max(id)
} while (isNotEmpty)

优化方式二：分段查询

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

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;
}

3.2 优化 GROUP BY

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

-- 低效:
select job , avg(sal) from table_demo group by job having  job = ‘manager';
-- 高效
select job , avg(sal) from table_demo where  job = ‘manager' group by job

3.3 范围查询

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

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
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'

使用单键索引 trade_date_time 的情况下

• 从索引里找到所有 trade_date_time 在’2019-05-01’ 到’2020-05-01’ 区间的主键 id。假设有 100 万个。
• 对这些 id 进行排序（为的是在下面一步回表操作中优化 I/O 操作，因为很多挨得近的主键可能一次磁盘 I/O 就都取到了）
• 回表，查出 100 万行记录，然后逐个扫描，筛选出 org_code='1020’的行记录

使用联合索引 trade_date_time, org_code -联合索引 trade_date_time, org_code 底层结构推导如下：

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

1. 在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历，故仅能用到 trade_date_time 的索引，无法使用到 org_code 索引
2. 基于 MySQL5.6+的索引下推特性，虽然 org_code 字段无法使用到索引树，但是可以用于过滤回表的主键 id 数。

小结：对于该 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。实际业务场景中，检索条件中 trade_date_time 基本上肯定会出现，但 org_code 却不一定，故索引的设计还需要结合实际业务需求。

3.4 优化 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:

SELECT  id,....,creator,modifier,create_time,update_time  
FROM statement
WHERE (account_number = 'XXX' 
AND create_time >= '2022-04-24 06:03:44' 
AND create_time <= '2022-04-24 08:03:44' AND dc_flag = 'C') 
ORDER BY trade_date_time DESC,id DESC LIMIT 0,1000;

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

SELECT  id,....,creator,modifier,create_time,update_time  
FROM statement
WHERE (account_number = 'XXX'
 AND create_time >= '2022-04-24 06:03:44' 
 AND create_time <= '2022-04-24 08:03:44' 
 AND dc_flag = 'C') 
 ORDER BY create_time DESC,id DESC LIMIT 0,1000;

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

MySQL使不使用索引与所查列无关，只与索引本身，where条件，order by 字段，group by 字段有关。索引的作用一个是查找，一个是排序。

3.5 业务拆分

select * from order where status='S' and update_time < now-5min  limit 500

拆分优化：
随着业务数据的增长 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
}

4、原理剖析

MySQL 逻辑架构图：

4.1 索引的优缺点

优点

• 提高查询语句的执行效率，减少 IO 操作的次数
• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 加了索引的列会进行排序，在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间

缺点

• 索引需要占物理空间
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 当对表中的数据进行增删改查时，索引也要动态的维护，这样就降低了数据的更新效率

4.2 索引的数据结构

主键索引

普通索引

组合索引

4.3 索引页结构

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

数据行格式：

MySQL 有 4 种存储格式：

• Compact
• Redundant （5.0 版本以前用，已废弃）
• Dynamic （MySQL5.7 默认格式）
• Compressed
  
  Dynamic 行存储格式下，对于处理行溢出（当一个字段存储长度过大时，会发生行溢出）时，仅存放溢出页内存地址。

4.4 索引的设计原则

哪些情况适合建索引

• 数据有数值还有唯一性的限制
• 频繁作为 where 条件的字段
• 经常使用 group by 和 order by 的字段，既有 group by 又有 order by 的字段时，建议建联合索引
• 经常作为 update 或 delete 条件的字段
• 经常需要 distinct 的字段
• 多表连接时的字段建议创建索引，也有注意事项
  • 连接表数量最好不要超过 3 张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快
  • 对多表查询时的 where 条件创建索引
  • 对连接字段创建索引，并且数据类型保持一致
• 在确定数据范围的情况下尽量使用数据类型较小的，因为索引会也会占用空间
• 对字符串创建索引时建议使用字符串的前缀作为索引，这样做的好处是：
  • 能节省索引的空间，
  • 虽然不能精确定位，但是能够定位到相同的前缀，然后通过主键查询完整的字符串，这样既能节省空间，又减少了字符串的比较时间，还能解决排序问题。
• 区分度高（散列性高）的字段适合作为索引。
• 在多个字段需要创建索引的情况下，联合索引优先于单值索引。使用最频繁的列作为索引的最左侧

哪些情况下不需要使用索引

• 在 where 条件中用不到的字段不需要
• 数据量小的不需要建索引，比如数据少于 1000 条。
• 由大量重复数据的列上不要建索引，比如性别字段中只有男和女时。
• 避免在经常更新的表或字段中创建过多的索引。
• 不建议主键使用无序的值作为索引，比如 uuid。
• 不要定义冗余或重复的索引
  • 例如：已经创建了联合索引 key(id,name)后就不需要再单独建一个 key(id)的索引

4.5 索引优化之 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) 多返回查询。

4.6 索引下推

假设有索引(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 操作。对于的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

• 引用了子查询的条件不能下推；
• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

5、思考

5.1 单表数据量太大为什么会变慢？

一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

5.2 一棵B+树可以存多少数据量？

大家是否还记得，一个B+树大概可以存放多少数据量呢？
InnoDB存储引擎最小储存单元是页，一页大小就是16k。

B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

5.3 如何解决单表数据量太大，查询变慢的问题

一般超过千万级别，我们可以考虑分库分表了。

分库分表可能导致的问题：

• 事务问题
• 跨库问题
• 排序问题
• 分页问题
• 分布式ID

因此，大家在评估是否分库分表前，先考虑下，是否可以把部分历史数据归档先，如果可以的话，先不要急着分库分表。如果真的要分库分表，综合考虑和评估方案。比如可以考虑垂直、水平分库分表。水平分库分表策略的话，range范围、hash取模、range+hash取模混合等等。

5.4 为什么要控制单行数据大小?

因为单行最多存一页（16k）的数据，超过限定的数据量后需要 页分裂或者行溢出存储，导致回表查询，影响查询效率。

总之，MySQL 控制单行数据大小的原因如下：

1. 数据存储和查询效率：MySQL 存储和查询数据时，需要将数据从磁盘读取到内存中。单行数据过大会占用更多的磁盘空间，导致读取时间加长。同时，如果单行数据过大，也会导致查询效率下降。

2. 内存使用效率：在 MySQL 中，每个查询都会占用一定的内存，如果单行数据过大，每个查询占用的内存也会增加，从而影响 MySQL 的内存使用效率。

3. 索引效率：MySQL 的索引是基于 B-Tree 结构实现的，单行数据过大会导致索引节点过大，影响索引效率。

因此，为了保证 MySQL 的数据存储和查询效率、内存使用效率和索引效率，需要控制单行数据大小。通常情况下，建议单行数据大小不超过 8KB。

6、总结

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

相关资料

1. 盘点MySQL慢查询的12个原因