MySQL存储过程/事件调度器执行查询慢于直接查询的优化问题

我有一条查询语句在MySQL Workbench中直接执行耗时<1秒，但作为存储过程执行或通过事件调度器执行时性能极差。服务器版本为8.0.36，tbl_s表当前有620万条数据。

简化后的表定义 #

CREATE TABLE `tbl_s` (
  `host_id` tinyint NOT NULL,
  `AuditTime_eastern` datetime(6) NOT NULL,
  `UserId` char(3) COLLATE utf8mb4_bin NOT NULL,
  `AuditDate_eastern` date NOT NULL,
  `AppointmentId_old` int DEFAULT NULL,
  `AppointmentId_new` int DEFAULT NULL,
  `StartTime_eastern_old` datetime DEFAULT NULL,
  `StartTime_eastern_new` datetime DEFAULT NULL,
   PRIMARY KEY (`host_id`,`AuditTime_eastern`,`UserId`),
  KEY `IX1` (`host_id`,`AuditDate_eastern`,`AppointmentTypeId_old`,`AppointmentTypeId_new`,`StartTime_eastern_old`,`StartTime_eastern_new`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

查询语句 #

SELECT 
    s1.appointmentid_old, s2.appointmentid_new
FROM
     tbl_s as s1
        LEFT JOIN
     tbl_s as s2 ON
             s1.host_id = s2.host_id
         AND s1.StartTime_eastern_old = s2.StartTime_eastern_new
         AND s2.Auditdate_eastern >= '2024-09-14'
   WHERE
            s1.auditdate_eastern >= '2024-09-14'
        AND s1.host_id = 1
        AND s1.AppointmentTypeId_old = 2

直接执行时的EXPLAIN结果 #

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, s1, , range, PRIMARY,IX1, IX1, 9, , 493, 10.00, Using index condition
1, SIMPLE, s2, , range, PRIMARY,IX1, IX1, 4, , 634, 100.00, Using where; Using join buffer (hash join)

存储过程执行时的EXPLAIN结果 #

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, s1, , range, PRIMARY,IX1, IX1, 9, , 493, 10.00, Using index condition
1, SIMPLE, s2, , ref, PRIMARY,IX1, PRIMARY, 1, const, 1391, 100.00, Using where

可以看到存储过程未自动为s2表使用IX1索引。在JOIN中添加USE INDEX (IX1)后，存储过程的EXPLAIN结果变为：

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, s1, , range, PRIMARY,IX1, IX1, 9, , 493, 10.00, Using index condition
1, SIMPLE, s2, , ref, IX1, IX1, 1, const, 2358, 100.00, Using where

已排除排序规则差异导致的问题（未使用文本字段）。

补充说明 #

• '2024-09-14'为测试用日期，实际需求为更早的固定日期
• 最终目标是基于此查询实现事件调度器的INSERT操作，已简化问题至上述场景

解决方法 #

以下几种方法可以让存储过程的执行性能与直接查询一致：

1. 显式指定索引：在JOIN语句中添加USE INDEX (IX1)，这已经验证能让存储过程使用正确的索引，直接对齐性能。
2. 更新表统计信息：执行ANALYZE TABLE tbl_s;更新表的统计数据，确保优化器能基于最新数据生成最优执行计划，避免因过时统计信息选错索引。
3. 统一SQL_MODE设置：检查Workbench直接执行和存储过程执行时的SQL_MODE是否一致，部分模式差异会影响优化器决策。可在存储过程开头显式设置一致的SQL_MODE，例如：

   SET SQL_MODE = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
   

4. 重新编译存储过程：若存储过程创建时间较早，可能存在执行计划缓存问题。执行ALTER PROCEDURE 你的存储过程名 COMPILE;重新编译，让优化器重新生成执行计划。
5. 调整优化器参数：确保哈希连接可用（直接执行时使用了哈希连接），可在存储过程开头设置SET optimizer_switch='hash_join=on';，或根据实际情况调整join_buffer_size等参数，提升连接效率。

内容的提问来源于stack exchange，提问作者Big Al Dente