Oracle存储过程lpUsers_master迁移MySQL遇游标性能瓶颈求助

问题背景 #

将Oracle存储过程lpUsers_master迁移至MySQL时，使用游标后运行时间从2分钟飙升至6小时。原Oracle存储过程通过遍历LEAGUE_POOL表数据，循环内执行多次查询和存储过程调用，适配MySQL游标后性能急剧下降，需彻底重构逻辑以适配MySQL的执行特性。

原Oracle存储过程代码 #

create or replace PROCEDURE lpUsers_master AS
    v_SEASON_NUMBER_DERIVED NUMBER;
    v_LEAGUE_COUNTRY VARCHAR2(100);
    v_LEAGUE_LEVEL NUMBER;
    v_ALPHA2CODE_DERIVED VARCHAR2(2);
    v_count NUMBER;
    v_count_plus NUMBER;
BEGIN
    -- Step 1: Derive the Season Number
    ADMIN.sp_deriveSeasonNumber(v_SEASON_NUMBER_DERIVED);

    -- Step 2: Scan the LEAGUE_POOL table
    FOR r IN (SELECT lp.LEAGUE_POOL_ID, lp.LEAGUE_COUNTRY, lp.LEAGUE_LEVEL FROM LEAGUE_POOL lp) LOOP
        v_LEAGUE_COUNTRY := r.LEAGUE_COUNTRY;
        v_LEAGUE_LEVEL := r.LEAGUE_LEVEL;

        -- Step 3: Derive ALPHA2CODE_DERIVED and check for existing records
        CASE r.LEAGUE_COUNTRY
            WHEN 'Rest of Africa' THEN v_ALPHA2CODE_DERIVED := 'BJ';
            ELSE 
                -- Fetch from the COUNTRIES table
                SELECT ct.ALPHA2CODE INTO v_ALPHA2CODE_DERIVED
                FROM COUNTRIES ct WHERE ct.LEAGUE_COUNTRY = r.LEAGUE_COUNTRY;
        END CASE;

        -- Step 4: Check initial count to see if the league exists in current season
        SELECT COUNT(*) INTO v_count FROM LEAGUE_POOL_USERS lpu
        WHERE lpu.LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND lpu.SEASON_NUMBER = v_SEASON_NUMBER_DERIVED;

        IF v_count = 0 THEN
                SELECT COUNT(*) INTO v_count_plus 
                FROM LEAGUE_POOL_USERS 
                WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;

                IF v_count_plus = 0 THEN
                -- Step 6: If the LEAGUE_POOL_ID doesn't exist in LEAGUE_POOL_USERS (and has not been added already to the next season), 
                -- THEN add NEW users to the database and create new leagues for new season
              sp_insertLeaguePoolUsers(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY, v_LEAGUE_LEVEL, r.LEAGUE_POOL_ID, v_ALPHA2CODE_DERIVED);
                END IF;

        ELSIF v_count > 0 THEN
               -- if it exists, check if the players have already been added to the next season to prevent duplication
            SELECT COUNT(*) INTO v_count_plus 
            FROM LEAGUE_POOL_USERS 
            WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;   
            IF v_count_plus = 0 THEN
          -- Step 5: add all league levels to the next season
                CASE r.LEAGUE_LEVEL
                    WHEN 1 THEN
                        ADMIN.sp_handleleaguelevel1(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                         WHEN 2 THEN
                        ADMIN.sp_handleLeagueLevel2(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                        WHEN 3 THEN
                        ADMIN.sp_handleLeagueLevel3(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                         WHEN 4 THEN
                        ADMIN.sp_handleLeagueLevel4(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
                END CASE; 
            END IF;
        END IF;
    END LOOP;

END lpUsers_master;

重构优化方案 #

1. 核心问题分析 #

MySQL游标性能远低于Oracle的主要原因是：游标遍历属于逐行处理，循环内的多次SELECT COUNT(*)和存储过程调用会产生大量上下文切换和磁盘IO，无法利用MySQL的批量处理优势。

2. 具体优化措施 #

（1）预计算所有必要数据，替代逐行游标遍历

使用集合式查询一次性获取LEAGUE_POOL关联COUNTRIES的数据，同时预计算当前赛季和下赛季LEAGUE_POOL_USERS的存在状态，避免循环内的重复查询。

（2）优化索引，降低查询耗时

给以下字段添加复合索引：

• LEAGUE_POOL_USERS(LEAGUE_POOL_ID, SEASON_NUMBER)：加速存在性判断的COUNT查询
• COUNTRIES(LEAGUE_COUNTRY)：加速ALPHA2CODE的查询

执行索引创建语句：

CREATE INDEX idx_lpu_pool_season ON LEAGUE_POOL_USERS(LEAGUE_POOL_ID, SEASON_NUMBER);
CREATE INDEX idx_ct_league_country ON COUNTRIES(LEAGUE_COUNTRY);

（3）重构存储过程逻辑，批量处理分支

将原游标循环内的条件判断转为批量数据筛选，再分别调用对应的存储过程，减少逐行调用的开销。

3. 重构后的MySQL存储过程示例 #

DELIMITER //

CREATE PROCEDURE lpUsers_master()
BEGIN
    DECLARE v_SEASON_NUMBER_DERIVED INT;
    
    -- Step 1: 获取赛季编号
    CALL ADMIN.sp_deriveSeasonNumber(v_SEASON_NUMBER_DERIVED);
    
    -- 创建临时表预存所有需要处理的联盟池数据及状态
    CREATE TEMPORARY TABLE tmp_league_process (
        LEAGUE_POOL_ID INT,
        LEAGUE_COUNTRY VARCHAR(100),
        LEAGUE_LEVEL INT,
        ALPHA2CODE_DERIVED VARCHAR(2),
        has_current_season TINYINT(1),
        has_next_season TINYINT(1)
    );
    
    -- 批量插入预处理数据：关联COUNTRIES，计算当前/下赛季存在状态
    INSERT INTO tmp_league_process
    SELECT
        lp.LEAGUE_POOL_ID,
        lp.LEAGUE_COUNTRY,
        lp.LEAGUE_LEVEL,
        CASE 
            WHEN lp.LEAGUE_COUNTRY = 'Rest of Africa' THEN 'BJ'
            ELSE ct.ALPHA2CODE 
        END AS ALPHA2CODE_DERIVED,
        CASE WHEN lpu_current.LEAGUE_POOL_ID IS NOT NULL THEN 1 ELSE 0 END AS has_current_season,
        CASE WHEN lpu_next.LEAGUE_POOL_ID IS NOT NULL THEN 1 ELSE 0 END AS has_next_season
    FROM LEAGUE_POOL lp
    LEFT JOIN COUNTRIES ct ON lp.LEAGUE_COUNTRY = ct.LEAGUE_COUNTRY
    LEFT JOIN LEAGUE_POOL_USERS lpu_current 
        ON lp.LEAGUE_POOL_ID = lpu_current.LEAGUE_POOL_ID 
        AND lpu_current.SEASON_NUMBER = v_SEASON_NUMBER_DERIVED
    LEFT JOIN LEAGUE_POOL_USERS lpu_next 
        ON lp.LEAGUE_POOL_ID = lpu_next.LEAGUE_POOL_ID 
        AND lpu_next.SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;
    
    -- Step 2: 处理当前赛季无记录且下赛季也无记录的情况，调用批量插入存储过程
    CALL sp_insertLeaguePoolUsers_batch(v_SEASON_NUMBER_DERIVED);
    
    -- Step 3: 处理当前赛季有记录且下赛季无记录的情况，按联赛级别批量调用对应存储过程
    -- 级别1
    CALL ADMIN.sp_handleleaguelevel1(v_SEASON_NUMBER_DERIVED, (SELECT GROUP_CONCAT(DISTINCT LEAGUE_COUNTRY) FROM tmp_league_process WHERE has_current_season = 1 AND has_next_season = 0 AND LEAGUE_LEVEL = 1));
    -- 级别2
    CALL ADMIN.sp_handleLeagueLevel2(v_SEASON_NUMBER_DERIVED, (SELECT GROUP_CONCAT(DISTINCT LEAGUE_COUNTRY) FROM tmp_league_process WHERE has_current_season = 1 AND has_next_season = 0 AND LEAGUE_LEVEL = 2));
    -- 级别3
    CALL ADMIN.sp_handleLeagueLevel3(v_SEASON_NUMBER_DERIVED, (SELECT GROUP_CONCAT(DISTINCT LEAGUE_COUNTRY) FROM tmp_league_process WHERE has_current_season = 1 AND has_next_season = 0 AND LEAGUE_LEVEL = 3));
    -- 级别4
    CALL ADMIN.sp_handleLeagueLevel4(v_SEASON_NUMBER_DERIVED, (SELECT GROUP_CONCAT(DISTINCT LEAGUE_COUNTRY) FROM tmp_league_process WHERE has_current_season = 1 AND has_next_season = 0 AND LEAGUE_LEVEL = 4));
    
    -- 清理临时表
    DROP TEMPORARY TABLE tmp_league_process;
END //

DELIMITER ;

4. 补充说明 #

• 若sp_insertLeaguePoolUsers原仅支持单条数据处理，需重构为批量插入逻辑，接收临时表或批量参数，避免逐行调用。
• 存储过程sp_handleleaguelevel1等若支持批量国家参数，可通过GROUP_CONCAT传递后在内部拆分处理，进一步减少调用次数。
• 若MySQL版本支持CTE（8.0+），可替换临时表为CTE，简化语法。

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