Firebird 'FOR SELECT ... INTO ... DO'在MySQL中的高效替代方案

问题背景 #

将Firebird数据库迁移至MySQL时，在存储过程环节遇到性能瓶颈：Firebird中用于遍历结果集的FOR SELECT ... INTO ... DO语句，在MySQL中用游标+临时表实现后性能比原版本慢10倍，复杂场景下问题更突出。

Firebird原存储过程示例 #

create or alter procedure get_services(
    companyID integer
) returns (
     personid integer, 
     personname varchar(100), 
     companyname varchar(50), 
     serviceID integer,
     servicetimestamp timestamp, 
     description varchar(100)
) as
begin
    for select distinct a.personid, a.personname, a.serviceID, b.companyname
            from persons a 
            join companies b on (a.companyid = b.companyid)
           where b.companyId = :companyid 
           into :personid, :personname, :companyname, :serviceID
      do begin
            select first 1 servicetimestamp, description 
              from services
             where serviceId = :serviceId
             order by servicetimestamp
           into :servicetimestamp, :description;
        suspend;
      end
end

尝试的MySQL游标方案（性能较差） #

BEGIN 
    DECLARE bDone INT;
    DECLARE personid INT; 
    DECLARE personname VARCHAR(100); 
    DECLARE companyname VARCHAR(50); 
    DECLARE serviceID INT;
    DECLARE servicetimestamp TIMESTAMP;
    DECLARE description VARCHAR(100);

    DECLARE curs CURSOR FOR 
        select distinct a.personid, a.personname, a.serviceID, b.companyname
                from persons a 
                join companies b on (a.companyid = b.companyid)
               where b.companyId = companyid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

    DROP TEMPORARY TABLE IF EXISTS tblTemp;
    CREATE TEMPORARY TABLE IF NOT EXISTS tblTemp (
        personid INT, 
        personname VARCHAR(100), 
        companyname VARCHAR(50), 
        serviceID INTEGER,
        servicetimestamp TIMESTAMP, 
        description VARCHAR(100)
    );  

    OPEN curs;
    SET bDone = 0;

    REPEAT 
        FETCH curs INTO personid, personname, companyname, serviceID;
        
        SELECT servicetimestamp, description 
        FROM services
        WHERE serviceId = serviceID
        ORDER by servicetimestamp
        LIMIT 1                       
        INTO servicetimestamp, description;

        INSERT INTO tblTemp VALUES(personid, personname, companyname, serviceID, servicetimestamp, description);
    UNTIL bDone END REPEAT;

    CLOSE curs;
    SELECT * FROM tblTemp;
END

高效解决方案 #

MySQL没有与Firebird FOR SELECT ... DO完全等价的语句，但可以通过基于集合的查询优化彻底替代游标，避免逐行处理的性能损耗。以下是针对你的场景的两种最优方案：

方案1：窗口函数实现（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数直接筛选每个serviceID的最新记录，再与主查询关联，完全不需要游标或临时表：

CREATE OR ALTER PROCEDURE get_services(IN p_companyID INT)
BEGIN
    SELECT 
        a.personid,
        a.personname,
        b.companyname,
        s.serviceID,
        s.servicetimestamp,
        s.description
    FROM (
        SELECT DISTINCT 
            personid, 
            personname, 
            serviceID,
            companyid
        FROM persons
    ) a
    JOIN companies b ON a.companyid = b.companyid
    JOIN (
        SELECT 
            serviceID,
            servicetimestamp,
            description,
            ROW_NUMBER() OVER (PARTITION BY serviceID ORDER BY servicetimestamp DESC) AS rn
        FROM services
    ) s ON a.serviceID = s.serviceID
    WHERE b.companyId = p_companyID
      AND s.rn = 1;
END;

核心优势：

• 纯集合操作，MySQL优化器可充分利用索引，性能远超游标方案
• 代码简洁，逻辑清晰，维护成本低
• 无临时表IO开销

方案2：聚合函数+关联查询（兼容MySQL 5.x） #

如果使用MySQL 5.x版本（不支持窗口函数），可以通过聚合函数找到每个serviceID的最新时间戳，再关联获取详情：

CREATE OR ALTER PROCEDURE get_services(IN p_companyID INT)
BEGIN
    SELECT 
        a.personid,
        a.personname,
        b.companyname,
        s.serviceID,
        s.servicetimestamp,
        s.description
    FROM (
        SELECT DISTINCT 
            personid, 
            personname, 
            serviceID,
            companyid
        FROM persons
    ) a
    JOIN companies b ON a.companyid = b.companyid
    JOIN services s ON a.serviceID = s.serviceID
    JOIN (
        SELECT 
            serviceID,
            MAX(servicetimestamp) AS latest_ts
        FROM services
        GROUP BY serviceID
    ) s_latest ON s.serviceID = s_latest.serviceID 
              AND s.servicetimestamp = s_latest.latest_ts
    WHERE b.companyId = p_companyID;
END;

注意：

• 若同一serviceID存在多条相同最新时间戳的记录，会返回多条结果；如需仅返回一条，可结合DISTINCT或在子查询中增加额外排序条件。

游标方案性能差的原因 #

你的游标实现存在多个性能瓶颈：

• 逐行FETCH和INSERT属于行级操作，效率远低于集合式批量处理
• 临时表的创建、插入操作带来额外磁盘IO开销
• 循环内的独立子查询无法被优化器批量处理，重复执行次数过多

复杂场景通用优化思路 #

对于更复杂的业务逻辑（需逐行处理但要避免游标性能问题）：

• 优先将逻辑转化为集合操作，利用MySQL的JOIN、子查询、窗口函数等特性
• 若必须逐行处理，可使用WHILE循环结合LIMIT/OFFSET（仍不如集合操作高效），或优化游标逻辑：减少循环内的独立查询，尽量将查询合并为批量操作

内容的提问来源于stack exchange，提问作者p.kirkovski