如何验证MySQL用户自定义函数每次调用都编译而存储过程不会每次编译

核心逻辑说明 #

• MySQL存储过程仅在第一次被调用时完成编译，编译后的执行计划会缓存到全局存储过程缓存池中，后续调用直接复用缓存结果，不会重复编译（仅在存储过程被修改、服务重启、缓存被主动清理时才会重新编译）
• 普通SQL编写的用户自定义函数（UDF，不含C语言编译的原生动态链接库UDF）每次被调用时都会重新解析编译，没有全局执行计划缓存机制，每次调用都会产生编译开销

具体验证示例 #

验证核心思路是通过performance_schema的统计指标，对比两次调用相同对象的编译次数、执行耗时差异，也可以通过批量调用的总耗时直观判断差异。

前置配置 #

首先开启相关统计开关，清空历史统计数据：

-- 临时开启performance_schema（永久开启需要在my.cnf中配置performance_schema=ON后重启服务）
SET GLOBAL performance_schema = ON;
-- 开启SQL语句统计
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'statement/sql/%';
-- 清空历史统计
TRUNCATE TABLE performance_schema.events_statements_history;
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

验证1：存储过程仅编译一次 #

1. 创建测试存储过程

DELIMITER //
CREATE PROCEDURE test_proc()
BEGIN
    SELECT SLEEP(0.001);
END //
DELIMITER ;

2. 连续调用两次存储过程

CALL test_proc();
CALL test_proc();

3. 查看统计结果

SELECT DIGEST_TEXT, COUNT_STAR, SUM_COMPILE_TIME, SUM_TIMER_WAIT/1000000000 AS SUM_EXEC_TIME_MS 
FROM performance_schema.events_statements_summary_by_digest 
WHERE DIGEST_TEXT LIKE '%CALL `test_proc`%';

结果说明：COUNT_STAR显示为2（共两次调用），但SUM_COMPILE_TIME仅对应1次编译的耗时，第一次调用的总耗时明显高于第二次，因为第一次包含了编译开销，第二次直接复用了缓存的执行计划。

验证2：用户自定义函数每次调用都编译 #

1. 创建测试自定义函数

DELIMITER //
CREATE FUNCTION test_func() RETURNS INT
DETERMINISTIC
BEGIN
    RETURN SLEEP(0.001);
END //
DELIMITER ;

2. 连续调用两次函数

SELECT test_func();
SELECT test_func();

3. 查看统计结果

SELECT DIGEST_TEXT, COUNT_STAR, SUM_COMPILE_TIME, SUM_TIMER_WAIT/1000000000 AS SUM_EXEC_TIME_MS 
FROM performance_schema.events_statements_summary_by_digest 
WHERE DIGEST_TEXT LIKE '%SELECT `test_func`%';

结果说明：COUNT_STAR显示为2（共两次调用），SUM_COMPILE_TIME是两次独立编译的耗时之和，两次调用的单次执行耗时差异很小，都包含了各自的编译开销。

简化验证方案：批量调用对比耗时 #

如果不方便操作performance_schema，可以通过批量调用1000次的总耗时直观判断差异：

-- 批量调用存储过程1000次的耗时
SET @start = NOW(6);
SET @i = 0;
WHILE @i < 1000 DO
    CALL test_proc();
    SET @i = @i +1;
END WHILE;
SELECT TIMEDIFF(NOW(6), @start) AS proc_cost_time;

-- 批量调用自定义函数1000次的耗时
SET @start = NOW(6);
SET @i = 0;
WHILE @i < 1000 DO
    SELECT test_func();
    SET @i = @i +1;
END WHILE;
SELECT TIMEDIFF(NOW(6), @start) AS func_cost_time;

结果说明：批量调用函数的耗时会比调用存储过程高2~10倍（差异取决于MySQL版本和服务器性能），核心原因就是函数每次调用都要重新编译，而存储过程仅编译一次。

注意事项 #

• 以上验证适用于MySQL 5.7及以上版本，低版本可能没有performance_schema的编译时间统计字段
• 结论仅针对SQL编写的自定义函数，C语言编译的原生UDF本身是预编译的动态链接库，不存在每次调用编译的情况
• 存储过程如果执行ALTER PROCEDURE修改、FLUSH TABLES等操作会清空缓存，下次调用会重新编译
  内容的提问来源于stack exchange，提问作者Sudhir