慢 SQL 分析与优化( 二 ) _慢SQL

使用联合索引 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'为例：
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`),

慢 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 了

优化后：执行总行数为:6 行，耗时 34ms 。

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 检查状态及分数计算逻辑