如何优化MySQL中product-usage表last_inactive列批量填充操作？

嘿，这个场景我太有经验了！你现在用的相关子查询，本质上是每一行都要单独执行一次子查询，20k个product_id加上每行的查询量，总计算量直接爆炸，难怪单product_id都要2-3秒，全量跑起来估计要天荒地老。咱们换个思路，用MySQL的窗口函数LEAD()就能一次性搞定所有分组的下一行数据，效率直接拉满！

核心优化思路：用窗口函数替代相关子查询 #

LEAD()是专门用来在分组内获取下一条符合排序规则的记录字段的窗口函数，完美匹配你的需求：按product_id分组，按rank升序取更高rank的下一条checked_at，然后处理减1秒和默认值的逻辑。

第一步：验证查询逻辑 #

先跑这个查询确认结果符合预期：

SELECT 
    id,
    `rank`, -- 注意rank是MySQL关键字，要用反引号包裹
    product_id,
    checked_at,
    -- 分组内取下一条的checked_at，减1秒；无下一条则用当前时间
    COALESCE(DATE_SUB(LEAD(checked_at) OVER (PARTITION BY product_id ORDER BY `rank` ASC), INTERVAL 1 SECOND), NOW()) AS last_inactive
FROM product_usage;

• PARTITION BY product_id：把数据按产品ID分组，确保只在同产品内找下一条记录
• ORDER BY rank ASC：保证按rank从小到大排序，这样LEAD()取到的就是rank更高的下一条记录
• COALESCE()：处理没有下一条记录的情况，直接用NOW()填充

第二步：批量更新到新增列 #

如果已经新增了last_inactive列（没加的话先执行ALTER TABLE product_usage ADD COLUMN last_inactive TIMESTAMP NULL;），用下面的语句批量更新：

UPDATE product_usage pu
JOIN (
    SELECT 
        id,
        COALESCE(DATE_SUB(LEAD(checked_at) OVER (PARTITION BY product_id ORDER BY `rank` ASC), INTERVAL 1 SECOND), NOW()) AS new_last_inactive
    FROM product_usage
) pu_update ON pu.id = pu_update.id
SET pu.last_inactive = pu_update.new_last_inactive;

这里依赖id是主键/唯一键，确保能精准关联每一行的更新值。

第三步：加索引让速度再上一个台阶 #

为了让窗口函数的分组和排序操作更快，建议创建复合覆盖索引：

CREATE INDEX idx_product_rank_checked ON product_usage (product_id, `rank`, checked_at);

这个索引直接覆盖了PARTITION BY、ORDER BY和需要读取的checked_at字段，MySQL不用回表查原始数据，速度会进一步提升。

为什么这个方法比原来快？ #

原来的相关子查询是O(n²)的时间复杂度（每一行都要遍历同product_id的所有记录找下一条），而窗口函数是O(n log n)（只需要一次排序分组），处理20k个product_id的话，耗时会从几小时级直接降到几秒/几十秒级，差距非常明显。

内容的提问来源于stack exchange，提问作者mysql-noob