慢 SQL 分析与优化，2024年最新深入剖析原理

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Golang全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Go语言开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注go）

正文

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

• 取数据的方式

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

• 数据加工的方式

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

优化思路

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

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

• 返回更少数据（减少网络传输或磁盘访问）
• 减少交互次数（减少网络传输）

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

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

优化案例

数据分页优化

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

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

范围查询

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

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 却不一定，故索引的设计还需要结合实际业务需求。

优化 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 字段有关。索引的作用一个是查找，一个是排序。

业务拆分

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
}

数据库结构优化

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

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 语句

大表优化

• 分库分表（水平、垂直）
• 读写分离
• 数据定期归档

原理剖析

MySQL 逻辑架构图：

索引的优缺点

优点

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

缺点

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

索引的数据结构

主键索引

普通索引

组合索引

索引页结构

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Go）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
1c9ecbac8c9c856ec2e.png)

索引页结构

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注Go）
[外链图片转存中…(img-zC8AtuVZ-1713219935655)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！