MySQL高性能实现同id_solicitud行与后续行关联（非连续ID）——计算状态切换耗时

当然可以用纯SQL实现这个需求，而且完全能做到高性能！你的核心问题是要为每条solicitud记录匹配同一id_solicitud下按时间排序的下一条状态记录，进而计算状态切换耗时。之前用的CROSS JOIN会产生大量笛卡尔积，在百万级数据量下性能必然崩盘，我们换用更高效的方案：

最优方案：使用窗口函数（MySQL 8.0+） #

从MySQL 8.0开始支持的LEAD()窗口函数专门用来获取分组内的下一条记录，性能远超自连接/交叉连接，适合大数据量场景：

SELECT
    id,
    id_solicitud,
    fecha AS estado_inicio_fecha,
    estado AS estado_inicial,
    LEAD(fecha) OVER (PARTITION BY id_solicitud ORDER BY fecha) AS estado_siguiente_fecha,
    LEAD(estado) OVER (PARTITION BY id_solicitud ORDER BY fecha) AS estado_siguiente,
    -- 计算时间差，这里用秒为单位，可按需替换为MINUTE/HOUR等
    TIMESTAMPDIFF(SECOND, fecha, LEAD(fecha) OVER (PARTITION BY id_solicitud ORDER BY fecha)) AS tiempo_transcurrido_segundos
FROM solicitud
ORDER BY id_solicitud, fecha;

代码解释： #

• PARTITION BY id_solicitud：将数据按请求ID分组，确保只在同一个请求的范围内查找下一条记录
• ORDER BY fecha：每个分组内按时间戳排序，保证匹配的是时间上的下一个状态
• LEAD(fecha)/LEAD(estado)：分别提取当前记录的下一条记录的时间和状态
• TIMESTAMPDIFF：直接计算两个时间的间隔，返回你需要的时间单位

关键性能优化：添加联合索引 #

为了让窗口函数的分组和排序操作直接利用索引，避免全表扫描或临时排序，必须创建以下联合索引：

CREATE INDEX idx_solicitud_id_fecha ON solicitud(id_solicitud, fecha);

这个索引会让MySQL直接按id_solicitud分组并按fecha排序，不需要额外计算，百万级数据下也能快速返回结果，彻底解决超时问题。

兼容MySQL 5.x的备选方案（无窗口函数） #

如果你的MySQL版本低于8.0，可以使用用户变量来实现，但性能略逊于窗口函数，不过仍远优于交叉连接：

SELECT
    id,
    id_solicitud,
    estado_inicio_fecha,
    estado_inicial,
    estado_siguiente_fecha,
    estado_siguiente,
    TIMESTAMPDIFF(SECOND, estado_inicio_fecha, estado_siguiente_fecha) AS tiempo_transcurrido_segundos
FROM (
    SELECT
        id,
        id_solicitud,
        fecha AS estado_inicio_fecha,
        estado AS estado_inicial,
        -- 利用变量存储下一条记录的信息
        @next_fecha := IF(@current_id = id_solicitud, @next_fecha, NULL) AS estado_siguiente_fecha,
        @next_estado := IF(@current_id = id_solicitud, @next_estado, NULL) AS estado_siguiente,
        -- 更新变量为当前记录的值，供下一行使用
        @next_fecha := fecha,
        @next_estado := estado,
        @current_id := id_solicitud
    FROM solicitud,
         -- 初始化变量
         (SELECT @current_id := NULL, @next_fecha := NULL, @next_estado := NULL) vars
    -- 按请求ID+时间倒序排列，确保变量能正确传递下一条记录
    ORDER BY id_solicitud, fecha DESC
) t
-- 最后按正常顺序输出
ORDER BY id_solicitud, estado_inicio_fecha;

注意事项： #

• 这个方法依赖于MySQL的变量赋值顺序，不同版本可能有差异，测试后再投入生产
• 同样需要创建上面提到的idx_solicitud_id_fecha索引来提升性能

为什么你的原方案性能差？ #

你之前用的CROSS JOIN会为每个id_solicitud的每条记录匹配所有时间更晚的记录，比如一个请求有5条状态，就会产生5*4=20条中间结果，百万级数据下会产生数十亿条临时数据，自然会超时。而窗口函数是线性扫描数据，只处理每条记录一次，效率天差地别。

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