亲宝软件园·资讯

展开

慢SQL分析优化

字节跳动技术团队 人气:0

背景介绍

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

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

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

优化方式

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

优化思路

优化案例

数据分页优化

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, 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 逻辑架构图：

795528d6d10ad3135cf934c78289500d.png

索引的优缺点

优点

提高查询语句的执行效率，减少 IO 操作的次数

创建唯一性索引，可以保证数据库表中每一行数据的唯一性

加了索引的列会进行排序，在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间

缺点

索引需要占物理空间

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

当对表中的数据进行增删改查时，索引也要动态的维护，这样就降低了数据的更新效率

索引的数据结构

主键索引

b532510a0c6bce3090d7662e2b9493bb.png

普通索引

组合索引

索引页结构

665da3090b54870f13ba680a4934183b.png

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

5579704b3271972f0299187ec6fa9b18.png

数据行格式：

MySQL 有 4 种存储格式：

  1. Compact
  2. Redundant （5.0 版本以前用，已废弃）
  3. Dynamic （MySQL5.7 默认格式）
  4. Compressed

ad7de04275271d428f1815014ae0b9f7.png

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

思考：

  1. MySQL 一张表到底能存多少数据？
  2. 为什么要控制单行数据大小?
  3. 优化案例 4 中优化前的 SQL 为什么走不到索引？

总结

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

参考资料

https://help.aliyun.com/document_detail/311122.html

https://blog.csdn.net/qq_32099833/article/details/123150701

https://www.cnblogs.com/tufujie/p/9413852.html

加载全部内容
相关教程
猜你喜欢
用户评论