MySQL 如何加速查询中的 UDF

Question

我有一张表，其中包含用户 ID、部门，呃。我创建了一个简单的查询来收集所有这些信息。

SELECT table.userID, table.department, table.er FROM table;

现在，我想将属于同一部门的所有 er 分组并执行此计算

select sum(table.er)/3 as department_er from table group by table.department;

然后将此结果添加为我的第一个查询中的新列。为此，我创建了一个如下所示的 UDF

BEGIN
  DECLARE department_er FLOAT;
  set department_er = (select sum(er) from table where table.department = dpt);
  RETURN department_er;
END

然后我在此查询中使用了该 UDF

SELECT table.userID, table.department, (select dptER(table.department)/3) as department_er FROM table

我已经对表建立了索引，更复杂的查询从 4 分钟以上减少到不到 1 秒。这看起来很简单，但运行需要 10 分钟。有没有更好的方法来做到这一点或优化我的 UDF？

原谅我的无知:)

Answer 1

尝试在 SELECT 子句中不使用依赖聚合子查询的查询:

select table.userID, 
       table.department as dpt,
       x.department_er 
from table 
join (
  select department,
         (sum(table.er)/3) As department_er 
  from table
  group by department
) x
ON x.department = table.department

此 UDF 函数无法优化。也许它似乎适用于简单的查询，但通常它会损害您的数据库性能。

想象一下我们有一个像这样的查询:

SELECT ....., UDF( some parameters )
FROM table
....

MySql 必须为此查询中从表中检索的每条记录调用此函数
如果表包含 1000 条记录 - 该函数将被触发 1000 次。
并且函数内的查询也被触发 1000 次。
如果有 10.000 条记录 - 则该函数将被调用 10.000 次。

即使您以这种方式优化此函数，使 UDF 速度提高 2 倍，上述查询仍将触发该函数 1000 次。
如果 500 个用户具有相同的部门 - 仍然会为每个用户调用 500 次，并为每个用户计算相同的值。 499 次冗余调用，因为只需要 1 次调用即可计算该值。

优化此类查询的唯一方法是将“内部”查询从 UDF 函数中取出，并使用联接等将其与主查询结合起来。