昨天接到一客户反映,数据库报表查询好卡,需要我方支援,我们派工程师前往排查问题,发现客户后台有一个快递管理的功能,里面有一个多条件组合的报表查询功能,于是进行数据库监控跟踪,以下是数据库环境的基本信息:

数据库版本:MYSQL 5.7精简版
操作系统:windows2008

下面是跟踪到的有性能问题的报表SQL:

SELECT
	*
FROM
	kdinfo
WHERE
	1 = 1
AND KDType = '7'
AND From_Sheng LIKE '%黑龙江省%'
AND To_Sheng LIKE '%广东省%'
AND STR_TO_DATE(
	CONCAT(Scan_Date, ' ', Scan_Time),
	'%Y-%m-%d %H:%i:%s'
) > '2020-03-04 00:00'
AND STR_TO_DATE(
	CONCAT(Scan_Date, ' ', Scan_Time),
	'%Y-%m-%d %H:%i:%s'
) < '2020-03-04 23:59'
ORDER BY
	STR_TO_DATE(
		CONCAT(Scan_Date, ' ', Scan_Time),
		'%Y-%m-%d %H:%i:%s'
	) DESC
LIMIT 0,
 10

分析:该SQL就是报表对应的功能,执行时间大概需要8S左右,从这个语句的结构来看,有时间过滤条件,还有limit分页功能,理应不会这么慢,于是查看该表kdinfo的表结构,发现:该表将时间分成两个字段,分别是Scan_Date日期(年月天)和小时Scan_Time(小时分秒),所以才有上面的CONCAT(Scan_Date, ’ ', Scan_Time),这个设计不太合理,增加了数据范式的冗余,并且以上SQL语句加上STR_TO_DATE和CONCAT,无法通过直接字段索引方式进行优化,但迫于客户程序代码无法修改的无奈,我方考虑能否通过MYSQL的函数索引方式,将以上时间代码直接转换成索引。

由于该用户用的数据库版本是MYSQL5.7,该版本是可以支持函数索引的(5.7以下不支持):
建立函数索引需要两步
1.创建虚拟列

alter table kdinfo add column Scan_Time_index datetime GENERATED ALWAYS AS 
(STR_TO_DATE(CONCAT(Scan_Date, ' ', Scan_Time),'%Y-%m-%d %H:%i:%s'));

注意:增加虚拟列映射以上SQL的时间转换代码
上面这条语句解释

执行上面这语句后,会在表中增加一个字段也就是Scan_Time_index,这个字段其实是个虚拟的,不用管

kdinfo :表名

Scan_Time_index:列名

datetime:列类型

(STR_TO_DATE(CONCAT(Scan_Date, ’ ‘, Scan_Time),’%Y-%m-%d%H:%i:%s’)):需要加索引的函数

2.添加索引

alter table kdinfo add index index_Scan_Time_idx(Scan_Time_index);

上面语句解释

t_log:表名

index_Scan_Time_idx:索引名称

Scan_Time_index:上面创建的列名

到此函数索引建立完成!重新执行以上报表SQL,执行时间从8S下降到0.1S,前端报表性能提升非常显著,客户很满意!

总结:函数索引虽然能给数据库查询性能带来很大的提升,但这种索引由于维护开销比较大,占用空间也大,建议如果程序可以修改的情况下,还是从根本上去修改表结构和SQL代码比较合理!