MySQL查询单独执行快但封装为存储过程后运行缓慢问题求解

该问题是MySQL存储过程常见的类型隐式转换、执行计划缓存异常导致的索引失效问题，对应解决方法如下：

• 修正参数数据类型
  你当前存储过程的输入参数startDate、endDate定义为varchar(50)类型，而表中from_date、to_date是日期类型，两者比较时会触发隐式类型转换，导致日期字段上的索引失效。需要把参数类型修改为和表字段一致的DATE或DATETIME类型。
• 避免参数嗅探问题
  MySQL存储过程会缓存第一次调用生成的执行计划，如果后续传入的参数范围变化大，缓存的执行计划会不再适配，导致全表扫描。可以通过两种方式解决：
  1. 将传入参数赋值给本地变量后再用于查询，规避执行计划缓存
  2. 使用动态SQL执行查询语句，每次调用都会重新生成适配当前参数的执行计划
• 验证索引有效性
  确认salesrule表上已经建立了联合索引 (rule_type, from_date, to_date)，这个索引可以直接覆盖WHERE条件的过滤逻辑，大幅提升查询效率。

修改后存储过程示例（本地变量写法） #

CREATE DEFINER=`admin`@`%` PROCEDURE `ruparupadb_report`.`InternalAudit_DataRuleVoucher`(IN p_startDate DATE, IN p_endDate DATE)
BEGIN
    -- 声明本地变量接收参数，规避参数嗅探
    DECLARE v_startDate DATE DEFAULT p_startDate;
    DECLARE v_endDate DATE DEFAULT p_endDate;

    SELECT sr.rule_id, 
    sr.rule_type, 
    srv.voucher_code,
    sr.name, 
    sr.description, 
    sr.from_date, 
    sr.to_date, 
    sr.tnc,
    CASE 
    WHEN sr.conditions LIKE '%items.RowTotal%' THEN CAST(SUBSTRING_INDEX(SUBSTRING(sr.conditions, LOCATE('"attribute":"items.RowTotal","operator":">=","value":"',sr.conditions) + CHAR_LENGTH('"attribute":"items.RowTotal","operator":">=","value":"')),'"',1) AS UNSIGNED) 
    WHEN sr.conditions LIKE '%items.SubTotal%' THEN CAST(SUBSTRING_INDEX(SUBSTRING(sr.conditions, LOCATE('"attribute":"items.SubTotal","operator":">=","value":"',sr.conditions) + CHAR_LENGTH('"attribute":"items.SubTotal","operator":">=","value":"')),'"',1) AS UNSIGNED)
    ELSE NULL END 'Minimum Transaction',
    srv.max_redemption,
    sr.discount_type,
    sr.discount_amount,
    CASE WHEN sr.conditions LIKE '%{"attribute":"items.shipping.StoreCode","operator":"%","value":"A"}%' OR sr.conditions NOT LIKE '%{"attribute":"items.shipping.StoreCode","operator":"%","value"%' THEN 'Yes' ELSE 'No' END BU_AHI,
    CASE WHEN sr.conditions LIKE '%{"attribute":"items.shipping.StoreCode","operator":"%","value":"H"}%' OR sr.conditions NOT LIKE '%{"attribute":"items.shipping.StoreCode","operator":"%","value"%' THEN 'Yes' ELSE 'No' END BU_HCI,
    CASE WHEN sr.conditions LIKE '%{"attribute":"items.shipping.StoreCode","operator":"%","value":"T"}%' OR sr.conditions NOT LIKE '%{"attribute":"items.shipping.StoreCode","operator":"%","value"%' THEN 'Yes' ELSE 'No' END BU_TGI,
    sr.conditions
    FROM ruparupadb_2.salesrule sr 
    LEFT JOIN ruparupadb_2.salesrule_voucher srv ON sr.rule_id = srv.rule_id 
    WHERE rule_type ='voucher' AND (sr.from_date <= v_endDate AND v_startDate <= sr.to_date);
END

备选优化方案（动态SQL写法） #

如果修改参数类型后性能仍不符合预期，可以改用动态SQL完全规避执行计划缓存问题：

CREATE DEFINER=`admin`@`%` PROCEDURE `ruparupadb_report`.`InternalAudit_DataRuleVoucher`(IN p_startDate DATE, IN p_endDate DATE)
BEGIN
    SET @sql = '
    SELECT sr.rule_id, 
    sr.rule_type, 
    srv.voucher_code,
    sr.name, 
    sr.description, 
    sr.from_date, 
    sr.to_date, 
    sr.tnc,
    CASE 
    WHEN sr.conditions LIKE ''%items.RowTotal%'' THEN CAST(SUBSTRING_INDEX(SUBSTRING(sr.conditions, LOCATE(''"attribute":"items.RowTotal","operator":">=","value":"'',sr.conditions) + CHAR_LENGTH(''"attribute":"items.RowTotal","operator":">=","value":"'')),''"'',1) AS UNSIGNED) 
    WHEN sr.conditions LIKE ''%items.SubTotal%'' THEN CAST(SUBSTRING_INDEX(SUBSTRING(sr.conditions, LOCATE(''"attribute":"items.SubTotal","operator":">=","value":"'',sr.conditions) + CHAR_LENGTH(''"attribute":"items.SubTotal","operator":">=","value":"'')),''"'',1) AS UNSIGNED)
    ELSE NULL END ''Minimum Transaction'',
    srv.max_redemption,
    sr.discount_type,
    sr.discount_amount,
    CASE WHEN sr.conditions LIKE ''%{"attribute":"items.shipping.StoreCode","operator":"%","value":"A"}%'' OR sr.conditions NOT LIKE ''%{"attribute":"items.shipping.StoreCode","operator":"%","value"%'' THEN ''Yes'' ELSE ''No'' END BU_AHI,
    CASE WHEN sr.conditions LIKE ''%{"attribute":"items.shipping.StoreCode","operator":"%","value":"H"}%'' OR sr.conditions NOT LIKE ''%{"attribute":"items.shipping.StoreCode","operator":"%","value"%'' THEN ''Yes'' ELSE ''No'' END BU_HCI,
    CASE WHEN sr.conditions LIKE ''%{"attribute":"items.shipping.StoreCode","operator":"%","value":"T"}%'' OR sr.conditions NOT LIKE ''%{"attribute":"items.shipping.StoreCode","operator":"%","value"%'' THEN ''Yes'' ELSE ''No'' END BU_TGI,
    sr.conditions
    FROM ruparupadb_2.salesrule sr 
    LEFT JOIN ruparupadb_2.salesrule_voucher srv ON sr.rule_id = srv.rule_id 
    WHERE rule_type =''voucher'' AND (sr.from_date <= ? AND ? <= sr.to_date)';
    PREPARE stmt FROM @sql;
    SET @p_end = p_endDate, @p_start = p_startDate;
    EXECUTE stmt USING @p_end, @p_start;
    DEALLOCATE PREPARE stmt;
END

内容的提问来源于stack exchange，提问作者victorxu2