环境 #

• OceanBase 社区版 4.2.1（MySQL 模式）
• transactions 表包含100万+行数据，按id分区

问题概述 #

执行存储过程处理约1万行pending状态事务时，耗时超60秒（预期<5秒）且CPU负载高，性能远低于直接运行等效SQL。

存储过程代码 #

CREATE PROCEDURE process_transactions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE trans_id BIGINT;
    DECLARE trans_amount DECIMAL(20,2);
    DECLARE cur CURSOR FOR 
        SELECT id, amount FROM transactions WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO trans_id, trans_amount;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 业务逻辑：更新余额并记录日志
        UPDATE accounts SET balance = balance + trans_amount 
        WHERE account_id = (SELECT account_id FROM transactions WHERE id = trans_id);
        
        INSERT INTO transaction_log(trans_id, amount, exec_time)
        VALUES (trans_id, trans_amount, NOW());
        
        UPDATE transactions SET status = 'processed' WHERE id = trans_id;
    END LOOP;
    CLOSE cur;
END;

观察到的异常现象 #

• 游标操作速度远慢于直接执行同逻辑SELECT语句
• 循环内DML未触发并行优化（表现不同于Oracle/MySQL）
• 并发场景下出现大量锁等待超时

已尝试的无效优化 #

• 为transactions表添加(status, id)联合索引
• 在游标查询中加入LIMIT 1000做批量处理
• 禁用相关触发器

排查与优化方案 #

1. 替换逐行游标为批量SQL操作 #

OceanBase对PL存储过程的逐行循环优化支持有限，单条SQL的解析、事务交互开销会被放大。建议将逻辑改写为批量处理：

-- 分批处理示例，每批次处理1000行
REPEAT
    -- 1. 临时存储待处理事务（关联account_id避免重复查询）
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_pending AS
    SELECT t.id, t.amount, t.account_id
    FROM transactions t
    WHERE t.status = 'pending'
    LIMIT 1000;

    -- 2. 批量更新账户余额
    UPDATE accounts a
    JOIN tmp_pending t ON a.account_id = t.account_id
    SET a.balance = a.balance + t.amount;

    -- 3. 批量插入操作日志
    INSERT INTO transaction_log(trans_id, amount, exec_time)
    SELECT id, amount, NOW() FROM tmp_pending;

    -- 4. 批量更新事务状态
    UPDATE transactions t
    JOIN tmp_pending tmp ON t.id = tmp.id
    SET t.status = 'processed';

    -- 5. 清理临时表并判断是否还有待处理数据
    DROP TEMPORARY TABLE tmp_pending;
UNTIL ROW_COUNT() = 0 END REPEAT;

2. 优化游标性能（若必须使用） #

• 验证索引有效性：执行EXPLAIN SELECT id, amount FROM transactions WHERE status = 'pending';，确认是否命中(status, id)覆盖索引，避免回表扫描。
• 声明只读游标：OceanBase默认游标为敏感游标，实时读取最新数据，声明为只读可降低开销：

  DECLARE cur CURSOR FOR 
      SELECT id, amount FROM transactions WHERE status = 'pending'
      FOR READ ONLY;
  

• 关闭不必要的游标属性：避免使用SCROLL属性，减少额外开销。

3. 锁问题排查与缓解 #

• 缩短锁持有时间：将原单事务改为每批次提交一次，避免长事务持有大量行锁：

  -- 在批量处理的每一轮循环中加入事务边界
  BEGIN;
  -- 批量更新逻辑...
  COMMIT;
  

• 排查锁等待详情：通过系统视图定位锁冲突类型：

  -- 查看当前锁等待
  SELECT * FROM oceanbase.v$lock_wait;
  -- 查看事务锁信息
  SELECT * FROM oceanbase.v$transaction;
  

• 调整隔离级别：若为行锁冲突，可将事务隔离级别从REPEATABLE READ改为READ COMMITTED，缩小锁范围。

4. 存储过程性能诊断 #

• 开启PL性能追踪：定位存储过程内耗时最长的语句：

  SET ob_pl_profile = ON;
  CALL process_transactions();
  -- 查看各语句执行耗时
  SELECT * FROM oceanbase.v$pl_profile;
  

• 确保本地编译：检查存储过程编译类型，若为解释执行则重新编译：

  SELECT name, compile_type FROM oceanbase.dba_procedures WHERE name = 'PROCESS_TRANSACTIONS';
  -- 重新编译为本地编译
  ALTER PROCEDURE process_transactions COMPILE;
  

5. 分区表优化 #

transactions表按id分区，需验证查询是否跨过多分区：

• 执行EXPLAIN PARTITIONS SELECT id, amount FROM transactions WHERE status = 'pending';，检查是否存在全分区扫描。
• 若pending数据分布分散，可考虑将分区键调整为(status, id)，让同状态事务集中在少数分区，减少扫描范围。

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