一、背景介绍
从系统设计角度看,一个系统从设计搭建到数据逐步增长,SQL 执行效率可能会出现劣化,为继续支撑业务发展,我们需要对慢 SQL 进行分析和优化,严峻的情况下甚至需要对整个系统进行重构 。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范,在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析 。
从数据库角度看,每个 SQL 执行都需要消耗一定 I/O 资源,SQL 执行的快慢,决定了资源被占用时间的长短 。假如有一条慢 SQL 占用了 30%的资源共计 1 分钟 。那么在这 1 分钟时间内,其他 SQL 能够分配的资源总量就是 70%,如此循环,当资源分配完的时候,所有新的 SQL 执行将会排队等待 。所以往往一条慢 SQL 会影响到整个业务 。
本文仅讨论 MySQL-InnoDB 的情况 。
二、优化方式
SQL 语句执行效率的主要因素:
1)数据量
- SQL 执行后返回给客户端的数据量的大小;
- 数据量越大需要扫描的 I/O 次数越多,数据库服务器的 IO 更容易成为瓶颈 。
- 数据在缓存中还是在磁盘上;
- 是否能够通过全局索引快速寻址;
- 是否结合谓词条件命中全局索引加速扫描 。
- 排序、子查询、聚合、关联等,一般需要先把数据取到临时表中,再对数据进行加工;
- 对于数据量比较多的计算,会消耗大量计算节点的 CPU 资源,让数据加工变得更加缓慢;
- 是否选择了合适的 join 方式 。
1)减少数据扫描(减少磁盘访问)
- 尽量在查询中加入一些可以提前过滤数据的谓词条件,比如按照时间过滤数据等,可以减少数据的扫描量,对查询更友好;
- 在扫描大表数据时是否可以命中索引,减少回表代价,避免全表扫描 。
3)减少交互次数(减少网络传输)
- 将数据存放在更快的地方;
- 某条查询涉及到大表,无法进一步优化,如果返回的数据量不大且变化频率不高但访问频率很高,此时应该考虑将返回的数据放在应用端的缓存当中或者 redis 这样的缓存当中,以提高存取速度 。
5)避免大事务操作
6)利用更多资源(增加资源)
2、优化案例
1)数据分页优化
select * from table_demo where type = ? limit ?,?;
- 优化方式一:偏移 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;
}
2)优化 GROUP BY
提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多 。
- 低效:
- 高效:
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 的情况下
b. 对这些 id 进行排序(为的是在下面一步回表操作中优化 I/O 操作,因为很多挨得近的主键可能一次磁盘 I/O 就都取到了);
c. 回表,查出 100 万行记录,然后逐个扫描,筛选出 org_code='1020'的行记录 。
推荐阅读
- 贫血调理的方法有哪些
- 九号、雅迪、爱玛、台铃电动车怎么选?都有什么优缺点?一次性分析明白
- Java|HR傲慢对待求职者,还“诅咒”对方找不到工作,大学生也太难了
- 手机信号满格,4G网速却一直很慢?多半是这4种原因,望周知
- 真心对一个人好,不一定有回报 慢慢的才知道
- ?慢性胃炎不吃药能好吗
- 关于上不了网的原因分析和解决办法 不能上网的原因
- 孕晚期胎心变慢的原因
- 水质分析中重金属检测技术 水质重金属分析
- 移动硬盘速度慢怎么办 移动硬盘读取速度慢