一、背景介绍
从系统设计角度看,一个系统从设计搭建到数据逐步增长,SQL 执行效率可能会出现劣化,为继续支撑业务发展,我们需要对慢 SQL 进行分析和优化,严峻的情况下甚至需要对整个系统进行重构。所以我们往往需要在系统设计前对业务进行充分调研、遵守系统设计规范,在系统运行时定期结合当前业务发展情况进行系统瓶颈的分析。
从数据库角度看,每个 SQL 执行都需要消耗一定 I/O 资源,SQL 执行的快慢,决定了资源被占用时间的长短。假如有一条慢 SQL 占用了 30%的资源共计 1 分钟。那么在这 1 分钟时间内,其他 SQL 能够分配的资源总量就是 70%,如此循环,当资源分配完的时候,所有新的 SQL 执行将会排队等待。所以往往一条慢 SQL 会影响到整个业务。
本文仅讨论 MySQL-InnoDB 的情况。
二、优化方式
SQL 语句执行效率的主要因素:
1)数据量
2)取数据的方式
3)数据加工的方式
1、优化思路
1)减少数据扫描(减少磁盘访问)
2)返回更少数据(减少网络传输或磁盘访问)
3)减少交互次数(减少网络传输)
4)减少服务器 CPU 开销(减少 CPU 及内存开销)
5)避免大事务操作
6)利用更多资源(增加资源)
2、优化案例
1)数据分页优化
select * from table_demo where type = ? limit ?,?;
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;
}
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)范围查询
联合索引中如果有某个列存在范围(大于小于)查询,其右边的列是否还有意义?
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'
a. 从索引里找到所有 trade_date_time 在'2019-05-01' 到'2020-05-01' 区间的主键 id,假设有 100 万个;
b. 对这些 id 进行排序(为的是在下面一步回表操作中优化 I/O 操作,因为很多挨得近的主键可能一次磁盘 I/O 就都取到了);
c. 回表,查出 100 万行记录,然后逐个扫描,筛选出 org_code='1020'的行记录。
以查找 trade_date_time >='2019-05-01' and trade_date_time <='2020-05-01' and org_code='1020'为例:
a. 在范围查找的时候,直接找到最大,最小的值,然后进行链表遍历,故仅能用到 trade_date_time 的索引,无法使用到 org_code 索引;
b. 基于 MySQL5.6+的索引下推特性,虽然 org_code 字段无法使用到索引树,但是可以用于过滤回表的主键 id 数。
小结:对于该 case, 索引效果[org_code,trade_date_time] > [trade_date_time, org_code]>[trade_date_time]。实际业务场景中,检索条件中 trade_date_time 基本上肯定会出现,但 org_code 却不一定,故索引的设计还需要结合实际业务需求。
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`),
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;
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;
MySQL使不使用索引与所查列无关,只与索引本身,where条件,order by 字段,group by 字段有关。索引的作用一个是查找,一个是排序。
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
}
3、数据库结构优化
1)范式优化:表的设计合理化(符合 3NF),比如消除冗余(节省空间);
2)反范式优化:比如适当加冗余等(减少 join)
3)拆分表:分区将数据在物理上分隔开,不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样,当对这个表进行查询时,只需要在表分区中进行扫描,而不必进行全表扫描,明显缩短了查询时间,另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O,一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的表可采取此方法,可按月建表分区。
4、SQL 语句优化
SQL 检查状态及分数计算逻辑
5、大表优化
三、原理剖析
MySQL 逻辑架构图:
1、索引的优缺点
1)优点
2)缺点
2、索引的数据结构
1)主键索引
2)普通索引
3)组合索引
3、索引页结构
索引页由七部分组成,其中 Infimum 和 Supremum 也属于记录,只不过是虚拟记录,这里为了与用户记录区分开,还是决定将两者拆开。
数据行格式
MySQL 有 4 种存储格式:
Dynamic 行存储格式下,对于处理行溢出(当一个字段存储长度过大时,会发生行溢出)时,仅存放溢出页内存地址。
4、索引的设计原则
1)哪些情况适合建索引
a. 连接表数量最好不要超过 3 张,每增加一张表就相当于增加了一次嵌套循环,数量级增长会非常快
b. 对多表查询时的 where 条件创建索引
c. 对连接字段创建索引,并且数据类型保持一致
a. 能节省索引的空间;
b. 虽然不能精确定位,但是能够定位到相同的前缀,然后通过主键查询完整的字符串,这样既能节省空间,又减少了字符串的比较时间,还能解决排序问题。
2)哪些情况下不需要使用索引
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) 多返回查询。
6、索引下推
假设有索引(name, age), 执行 SQL: select * from tuser where name like '张%' and age=10;
MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到,由于联合索引中包含列,所以存储引擎直接在联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。
索引下推使用条件
索引下推的目的是为了减少回表次数,也就是要减少 IO 操作。对于的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。
7、思考
1)MySQL 一张表到底能存多少数据?
2)为什么要控制单行数据大小?
3)优化案例 4 中优化前的 SQL 为什么走不到索引?
四、总结
抛开数据库硬件层面,数据库表设计、索引设计、业务代码逻辑、分库分表策略、数据归档策略都对 SQL 执行效率有影响,我们只有在整个设计、开发、运维阶段保持高度敏感、追求极致,才能让我们系统的可用性、伸缩性不会随着业务增长而劣化。
>>>>参考资料
作者丨伍楼华
来源丨公众号:字节跳动技术团队(ID:BytedanceTechBlog)