大表UPDATE查询执行过慢，十万条数据场景下求SQL优化方案

• 多层嵌套子查询搭配不必要的DISTINCT操作，会生成临时表产生额外的去重、IO开销，数据量级达到十万条后运算效率会指数级下降
• 缺少覆盖索引支撑关联、分组操作，会触发全表扫描，进一步拉长执行时间
• 原SQL中写死的LIMIT 0,1000仅会处理前1000个会员的订阅数据，不符合全量更新的需求

版本1：MySQL 8.0+ 支持窗口函数（性能最优） #

UPDATE members_tb mtb
INNER JOIN (
    SELECT 
        member_id,
        subscription_id
    FROM (
        SELECT 
            member_id,
            subscription_id,
            ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY expiry_date DESC) AS rn
        FROM member_subscription_tb
    ) t
    WHERE rn = 1
) mstb ON mtb.member_id = mstb.member_id
SET mtb.subscription_id = mstb.subscription_id

该写法用窗口函数直接按会员分组取到期日最大的订阅记录，无多余嵌套和去重操作，执行效率最高。

版本2：兼容MySQL 5.x低版本 #

UPDATE members_tb mtb
INNER JOIN member_subscription_tb mst 
ON mtb.member_id = mst.member_id
LEFT JOIN member_subscription_tb mst2 
ON mst.member_id = mst2.member_id AND mst.expiry_date < mst2.expiry_date
WHERE mst2.member_id IS NULL
SET mtb.subscription_id = mst.subscription_id

该写法通过左关联排除同会员下到期日更小的记录，直接拿到最大到期日的订阅数据，比原嵌套写法性能提升明显。

• 给member_subscription_tb表创建联合覆盖索引：ALTER TABLE member_subscription_tb ADD INDEX idx_mem_exp_sub (member_id, expiry_date DESC, subscription_id);，查询可直接命中索引无需回表，性能提升数倍
• 确认members_tb表的member_id为主键或已创建唯一索引

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