自定义函数带来的性能问题优化实例

现象描述

首先描述一下表结构：

create table test 
(
    id int primary key,
    account varchar(32),
    busiTime datetime,
    status tinyint,
    amount bigint,
    ...
    key (account, busiTime, status, amount)
);

而查询的SQL如下：

select sum(amount)
from test 
where account = '123456'
and change_to_unixtime(busiTime) between change_to_unixtime(X) and change_to_unixtime(Y)
and status in (0, 1);

其中change_to_unixtime是一个自定义函数，其作用是将datetime类型转化为long型的Unix时间戳。

这个SQL需要执行27s：

image.png

优化方案

熟悉数据库优化的人都知道一个原则：

不要对索引列进行计算

这个原则可以说是区分专业DBA和数据库票友的一大依据。

书归正传，根据上面的原则，把自定义函数change_to_unixtime去掉，得到SQL如下：

select sum(amount)
from test 
where account = '123456'
and busiTime between X and Y
and status in (0, 1);

此时的执行时间是多少呢？

image.png

下降到了300ms，其差距是90倍。对了这个表的规模是133万行记录。

此时静下心来分析一下执行计划，原先那个堪称ugly的SQL，其执行计划是这样的：

image.png

下面是优化过的SQL的执行计划：

image.png

不管从用到的索引，还是扫描的行数上来说，都是一样的，区别在于type。

第一个SQL能够用到索引，全赖索引最左前缀原则，因为account也在where子句中，但是这个样例不太好的地方是所有的数据都集中在一两个account上，首先索引扫描的行数多原因在于此；另外，因为使用了自定义函数，系统无法对这个索引进行范围扫描，即无法降低IO的规模，那么其查询时间长，也是可以理解的；另外一点，系统还需要额外的分出计算资源来将类型进行转换，这又是一个查询时间长的原因。

这些问题，第二个SQL都不会遇到，第二个SQL不能达到毫秒级的原因其实还是在于样例数据的设计上，数据过分倾斜了。

小结

自定义函数的设计初衷是消除Oracle和MySQL的差异，因为Oracle没有from_unixtime之类的函数，但是业务上需要。

不过回到这个SQL就有点不应该了，因为传入的参数还是date，而数据库里保存的也是datetime，其实即便是Oracle，这样传参，Java层面上是可以的，数据库也会正常的执行我优化过的SQL。