MySQL百万级数据量下更新查询变慢优化方案

核心性能瓶颈定位 #

结合表结构、慢SQL逻辑和执行计划，性能衰减的核心原因如下：

• 子查询存在无效关联：子查询仅需要获取person表的id和number_type字段，却无端关联了存量近1000万行的client表。按执行计划统计，单条person记录关联平均要扫描153条client记录，单轮10000行的批次就要额外扫描150万+条无效数据，数据规模越大这部分IO开销线性上涨。
• 大量重复无效更新：原更新语句没有过滤client.number_type is null的条件，会对已经赋值过number_type的client行重复执行写入，产生大量无意义的行锁、磁盘刷盘和binlog写入开销。
• 索引匹配度不足：子查询关联client时使用的idx_or_p_entity是面向(PERSON_ID, ENTITY_ID, ENTITY_TYPE)场景的联合索引，当前查询完全用不到后两个字段，每次关联都需要回表判断；person表侧没有针对过滤条件的覆盖索引，每轮循环都要全量扫描person表找符合条件的记录，循环越靠后无效扫描占比越高。
• 批次逻辑不合理：10000行的单批次更新容易触发大事务问题，持锁时间长，和业务读写请求冲突时会进一步拉长执行时间；固定的sleep逻辑在无数据可更新时也会空等，浪费执行窗口。

可落地优化方案 #

1. 精简查询逻辑，去掉冗余关联 #

直接删除子查询中对client表的无意义join，同时增加重复更新过滤条件，避免无效写入：

UPDATE client target
JOIN (
    SELECT p.number_type AS person_number_type, p.id AS person_id
    FROM person p
    WHERE p.number_type IS NOT NULL -- 修正原逻辑中p.number_type is null的笔误，避免将空值写入client
    ORDER BY p.id
    LIMIT 2000
) tmp ON target.person_id = tmp.person_id
SET target.number_type = tmp.person_number_type
WHERE target.number_type IS NULL; -- 仅更新未赋值的行，杜绝重复写

2. 补充针对性覆盖索引，消除回表 #

针对更新场景建联合索引，让查询全程走索引不需要回表：

-- 给client表建关联+过滤场景的覆盖索引
CREATE INDEX IDX_CLIENT_PERSON_NUMTYPE ON client(PERSON_ID, NUMBER_TYPE);

-- 给person表建过滤+排序场景的覆盖索引
CREATE INDEX IDX_PERSON_NUMTYPE_ID ON person(number_type, id);

3. 调整循环批次逻辑，降低锁冲突 #

• 将单批次更新大小从10000调整为2000~5000，缩短单事务持锁时间，避免大事务导致的undo、binlog刷盘阻塞
• 取消固定sleep逻辑，每轮更新后判断受影响行数，若受影响行数为0直接终止循环，避免空跑
• 按person_id顺序取数，将随机IO转为顺序IO，降低磁盘寻址开销

调整后的核心循环代码如下：

REPEAT
    START TRANSACTION;
    UPDATE client target
    JOIN (
        SELECT p.number_type AS person_number_type, p.id AS person_id
        FROM person p
        WHERE p.number_type IS NOT NULL
        ORDER BY p.id
        LIMIT 2000
    ) tmp ON target.person_id = tmp.person_id
    SET target.number_type = tmp.person_number_type
    WHERE target.number_type IS NULL;

    SET @affected_cnt = ROW_COUNT();
    COMMIT;

    IF @affected_cnt = 0 THEN
        SET i = 0;
    ELSE
        SET i = i - 1;
        SELECT SLEEP(0.01);
    END IF;
UNTIL i = 0 END REPEAT;

4. 超大数据量可选优化 #

如果调整后性能仍不满足要求，可以先把待更新的person_id和对应number_type全量导出到临时表，按client主键ID分段做范围更新，完全避免join操作的开销，单批次更新速度还能再提升40%以上。

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