MySQL 8.0如何高效实现当前行与上下相邻行记录值对比

性能问题根因 #

当前使用的循环+limit offset方案性能差的核心原因是：每次通过偏移量取行时都需要扫描偏移量之前的所有记录，2万条数据对应数万次表扫描，时间复杂度为O(n²)，数据量越大耗时增长越快。
以下两种方案均为O(n)时间复杂度的实现，2万条数据可在毫秒级完成处理：

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

MySQL 8.0内置的LAG()、LEAD()窗口函数是专门用于获取相邻行数据的原生能力，仅需单次表扫描即可完成计算。

示例代码 #

假设业务表为business_table，行排序依据为id（可替换为业务需要的排序字段如create_time等），待对比字段为value：

SELECT 
    id,
    value AS current_value,
    -- 获取上一行的value值，1为偏移量，缺省时无匹配行返回NULL
    LAG(value, 1) OVER (ORDER BY id) AS previous_row_value,
    -- 获取下一行的value值
    LEAD(value, 1) OVER (ORDER BY id) AS next_row_value,
    -- 自定义对比逻辑示例
    CASE WHEN value = LAG(value, 1) OVER (ORDER BY id) THEN '与上一行相等' ELSE '与上一行不等' END AS compare_with_previous,
    CASE WHEN value = LEAD(value, 1) OVER (ORDER BY id) THEN '与下一行相等' ELSE '与下一行不等' END AS compare_with_next
FROM business_table;

如果需要按业务维度分组对比（比如每个用户单独对比自己的前后行），可在OVER子句中增加PARTITION BY规则：

-- 按user_id分组，每个用户内部按id排序对比前后行
LAG(value, 1) OVER (PARTITION BY user_id ORDER BY id) AS previous_row_value

方案2：MySQL 5.x 用户变量兼容实现 #

如果使用的是不支持窗口函数的MySQL 5.x版本，可以通过用户变量模拟相邻行取值，性能同样远高于循环遍历方案。

示例代码 #

SELECT 
    t1.id,
    t1.current_value,
    t1.previous_row_value,
    t2.next_row_value,
    -- 自定义对比逻辑示例
    CASE WHEN t1.current_value = t1.previous_row_value THEN '与上一行相等' ELSE '与上一行不等' END AS compare_with_previous,
    CASE WHEN t1.current_value = t2.next_row_value THEN '与下一行相等' ELSE '与下一行不等' END AS compare_with_next
FROM (
    -- 子查询1：计算每行的上一行值
    SELECT 
        t.id,
        t.value AS current_value,
        @prev_val AS previous_row_value,
        @prev_val := t.value AS dummy
    FROM business_table t, (SELECT @prev_val := NULL) init
    ORDER BY t.id
) t1
LEFT JOIN (
    -- 子查询2：倒序计算每行的下一行值
    SELECT 
        t.id,
        @next_val AS next_row_value,
        @next_val := t.value AS dummy
    FROM business_table t, (SELECT @next_val := NULL) init
    ORDER BY t.id DESC
) t2 ON t1.id = t2.id
ORDER BY t1.id;

注意事项 #

• 所有查询中的ORDER BY规则必须和业务要求的行顺序完全一致，否则会出现前后行匹配错误
• 如果表数据量极大，建议给排序字段加索引，可进一步提升查询性能

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