数据库三范式-SQL 分析与优化

背景介绍

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

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

本文仅讨论 MySQL- 的情况。

优化方式

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

取数据的方式数据加工的方式优化思路返回更少数据（减少网络传输或磁盘访问）减少交互次数（减少网络传输）减少服务器 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= '2019-05-01 00:00:00' and trade_date_time= '2019-05-01 00:00:00' and trade_date_time<='2020-05-01 00:00:00'

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

数据库范式bcnf_数据库三范式_数据库范式的概念

以查找 >='2019-05-01' and [, ]>[]。实际业务场景中，检索条件中基本上肯定会出现，但却不一定，故索引的设计还需要结合实际业务需求。

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

拆分优化：

随着业务数据的增长 ='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
}

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

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

尽量避免使用子查询用 IN 来替换 OR读取适当的记录 LIMIT M,N，而不要读多余的记录禁止不必要的 Order By 排序总和查询可以禁止排重用 union all避免随机取记录将多次插入换成批量插入只返回必要的列，用具体的字段列表代替 * 语句区分 in 和优化 Group By 语句尽量使用数字型字段优化 Join 语句大表优化原理剖析

MySQL 逻辑架构图：

数据库范式bcnf_数据库三范式_数据库范式的概念