带有当前日期时间表达式条件的查询效率提升方法

一、问题背景

在实际的业务需求中，经常会遇到根据业务操作的当前时间对数据进行查询的场景，例如库存管理中对保质期的查询，以便了解即将会有多少商品即将超过保质期限，类似下图所示：

在这样的报表中，往往会存在增加一个计算字段的诉求，这个字段（临期天数）是在页面加载的时候通过系统计算自动得到的，因而在数据库中不会存储。

在这样的报表开发过程中，增加一个含有日期表达式的计算字段是一个常见的诉求，比如上图中的临期天数（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中也有类似的类库，我们可以根据自己的语言需要选择使用。

 

四、效果对比

下面将对“代数转换”前后的查询性能进行一个对比测试验证。

“代数转换”前

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%以上，能够有效解决带有当前日期时间表达式条件查询效率问题。