MySQL存储过程性能问题：文本类型逗号分隔参数优化咨询

我太懂你的困扰了——直接跑查询300ms搞定，一调用存储过程就卡到没响应，核心问题就是参数嗅探在搞怪，而且你的参数是逗号分隔的Text类型，得调整局部变量的用法来绕开这个坑。

为什么会出现这个问题？ #

MySQL的查询优化器会根据存储过程第一次被调用时传入的参数生成执行计划，之后不管你传什么参数，都会复用这个计划。当你用find_in_set这种函数时，优化器很难判断参数里的内容分布，生成的执行计划可能完全不适合后续的参数，直接导致性能雪崩。而你直接运行查询时，优化器是针对当前参数实时生成最优计划，所以速度快。

解决方法：用局部变量绕开参数嗅探 #

你之前以为没法用局部变量接收逗号分隔的Text参数？其实完全可以，只要把输入参数赋值给局部变量，就能让优化器每次执行时都重新评估执行计划，和你直接跑查询的逻辑一致。修改后的存储过程如下：

CREATE DEFINER=`Admin`@`%` PROCEDURE `MyReport`( p_myparameter_HK Text )
BEGIN
    -- 声明和输入参数类型一致的局部变量
    DECLARE v_myparameter_HK Text DEFAULT '';
    -- 将输入参数的值传递给局部变量，关键一步：绕开参数嗅探
    SET v_myparameter_HK = p_myparameter_HK;
    
    -- 用局部变量替代原输入参数执行查询
    SELECT * FROM MyTable 
    WHERE (find_in_set(MyTable.column_HK, v_myparameter_HK) <> 0 OR MyTable.column_HK IS NULL) ;
END

额外优化建议 #

1. 给column_HK加索引：如果这个字段上没有索引，即使解决了参数嗅探，查询性能还是会受限。建议创建普通索引：

CREATE INDEX idx_mytable_col_hk ON MyTable(column_HK);

2. 拆分OR查询提升性能：如果column_HK的NULL值数量不多，可以把OR条件拆成两个查询用UNION ALL合并，这样能让索引更好地发挥作用：

CREATE DEFINER=`Admin`@`%` PROCEDURE `MyReport`( p_myparameter_HK Text )
BEGIN
    DECLARE v_myparameter_HK Text DEFAULT '';
    SET v_myparameter_HK = p_myparameter_HK;
    
    SELECT * FROM MyTable WHERE find_in_set(column_HK, v_myparameter_HK) <> 0
    UNION ALL
    SELECT * FROM MyTable WHERE column_HK IS NULL;
END

这样修改后，调用存储过程的性能应该就能和你直接运行查询时一致了。

内容的提问来源于stack exchange，提问作者Hello.World