MYSQL5.7函数索引优化案例【企业数据库实战优化项目】

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

数据库版本：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代码比较合理！