一、问题背景
在实际的业务需求中,经常会遇到根据业务操作的当前时间对数据进行查询的场景,例如库存管理中对保质期的查询,以便了解即将会有多少商品即将超过保质期限,类似下图所示:
在这样的报表中,往往会存在增加一个计算字段的诉求,这个字段(临期天数)是在页面加载的时候通过系统计算自动得到的,因而在数据库中不会存储。
在这样的报表开发过程中,增加一个含有日期表达式的计算字段是一个常见的诉求,比如上图中的临期天数(expired_days),就是由“保质日期”(expiration_date)和“当前日期”(current_date())计算得到的,其计算公式为:
expired_days = expiration_date - current_date()
在报表展示的时候,该字段由计算引擎(前端js或者server端java)实时算出expired_days的值之后展示在界面上,因此在后台数据库中也不会存储。
这时一个问题就浮出水面了:如何支持针对字段expired_days的高效过滤?这对高时效要求的业务来说是迫切需要解决的问题。
二、思考分析
思路一:条件直接拼接
直接把expired_days的公式提交给DB,假设要查询“3天内到期的数据”,过滤条件是:“expired_days ”< 3;直接把expiration_date - current_date() 拼接到sql的where条件里提交给DB。
这样会存在一个问题:由于对expiration_date字段引入了表达式,查询时没办法走索引,会导致查询效率很低下,在数据量很大的情况下,查询接口一定会超时。
思路二:条件转换
那如果在过滤的时候,由应用程序先把current_date()的值计算好之后,再提交给DB呢?比如:
expiration_date – ‘2021-8-17’ < 3
经测试,这种条件在mysql和postgresql中,查询时同样不能使用expiration_date字段的索引,因而也不解决问题。
思路三:表达式索引
我们知道,在postgresql和mysql的高版本中已经支持了表达式索引,可不可以把expiration_date - current_date() 建立一个表达式索引呢?
这样做的结果也不行,因为这里的current_date()取值就是建立索引那一刻的值(即创建索引的DDL执行的时刻),而我们业务的实际需求是current_date()应该取查询过滤那一刻的值,不满足业务含义。
思路四:代数转换
我们的目标是在查询时能够使用到expiration_date字段的索引,而我们的过滤表达式为:
expiration_date – ‘2021-8-17’ < 3
通过前面的分析我们了解到,DB之所以不走expiration_date的索引的核心原因是:过滤表达式的左侧只能是expiration_date这个字段名,而不能是包含expiration_date的任何表达式,那么这样方案就出来了:我们可以由应用程序把上述表达式做一个简单的变化,变成以下形式:
expiration_date < 3 + ‘2021-8-17’
然后再把这个表达式提交给DB,这样就能使用索引,实现高效查询了。
三、解决方案
最终我们选择思路四来提升查询的效率,其中关键的一步是做表达式的转换,这在数学上称为“代数转换”。幸运的是,Java方面已经有开源的 代数转换 的类库,我们只要集成到我们的服务里来就可以了(参考:https://github.com/axkr/symja_android_library)。另外,在Python中也有类似的类库,我们可以根据自己的语言需要选择使用。
四、效果对比
下面将对“代数转换”前后的查询性能进行一个对比测试验证。
验证测试条件 |
|
数据库 |
PostgreSQL |
数据表 |
inventory_store |
数据量 |
2373w数据,其中expiration_date是时间戳字段,有索引 |
“代数转换”前
SQL脚本:
select * from inventory_store
where expiration_date - 1630425600000 > 748800000
limit 10
执行结果:
耗时:15.354 秒
结果分析:
由于使用了全表扫描,未能使用索引,导致查询效率较低。
“代数转换”后
SQL脚本:
select * from inventory_store
where expiration_date > 1630425600000 + 748800000
limit 10
执行结果:
耗时:0.14 毫秒
结果分析:
由于使用expiration_date索引,查询效率明显提升。
结论:通过“代数转换”方式,在本测试案例中提升查询效率99%以上,能够有效解决带有当前日期时间表达式条件查询效率问题。