我正在一个报表中运行一个select语句,该报表在30毫秒内返回30行。当我向select子句添加基于自定义函数的字段时,相同的查询需要几分钟才能运行。
自定义函数接受三个参数(全部来自主查询)执行自己的COUNT()查询,然后返回整数结果。自定义函数中的查询本身会在10毫秒内运行,但如果从自定义函数中运行,则需要300毫秒。
每次在查询中遇到自定义函数时是否对其进行编译?缓慢的原因可能是什么?在我的甲骨文时代(15年前),我习惯于创建自定义函数,将它们插入到查询中,然后它们就可以快速完成自己的操作。MySQL自定义函数有本质的不同吗?
编辑:
我刚刚发现:如果自定义函数从使用它的查询中获取传递给它的参数,那么它非常慢。如果我输入硬编码的参数值,那么它非常快。
例如

SELECT my_func(foo.col1, foo.col2, foo.col2)
FROM foo;
-- 120 seconds for 30 rows

SELECT my_func(1, 2, 3)
FROM foo;
-- 10 milli seconds for 30 rows

我可以SELECT my_func(x, y, z),其中x、y和z是在第一个查询示例中将传递给它的任何值的组合,数据库几乎不闪烁。这件事真让我抓狂。
我需要此功能的原因是使我能够在SugarCRM中运行报表。报表编写插件(KReports)允许我访问数据库中的某些实体和关系,但是报表不允许访问许多计数。已经创建了自定义函数来返回主报表返回的每一行的计数(其中10个),有效地为用户提供了一个10xN值透视表。
报告每次返回时只返回少数几行,这就是愚弄我的原因。这些结果是根据更大的基础数据分组的。在应用GROUPBY之前,正在对主数据集执行自定义函数(我假设是这样),因此调用的数量级比我想象的还要多。
如果我可以完全控制此报表中SQL的生成,我将选择内部查询中的30(组)行,然后将其包装在外部查询中,以便调用该较小数据集上的自定义函数。看来我得用不同的报告工具来解决这个问题。

最佳答案

总而言之,我的回答是,自定义函数在一个查询中被调用了7000次,而不是我原先认为的300次。300次就可以了,大约10秒。7000次需要3分钟以上,不好。
原因是在应用GROUPBY子句之前,SELECT子句中的自定义函数将应用于查询中检索到的所有行。该组将检索到的700行减少为仅30个唯一行。
我不能改变查询在我正在使用的报告工具中的工作方式,因此将转移到一个新的报告工具,它提供了更多的灵活性和对SQL如何构造的更多控制。

10-04 18:46