MySQL查询性能求助：test_cdr表最佳前缀匹配批量更新优化

一、先排查现有问题 #

• 检查执行计划：用EXPLAIN分析你的UPDATE语句，确认是否用到索引。如果type列显示ALL（全表扫描），说明索引未生效或创建逻辑错误。
• 查看语句状态：去掉LIMIT后执行SHOW PROCESSLIST，观察语句处于的状态（如Sending data、Creating tmp table）。若卡在临时表创建阶段，大概率是内存不足导致磁盘临时表生成缓慢，或关联逻辑过于复杂。
• 验证索引有效性：
  • 确认test_cdr表已创建(dest, POINT_TARGET)联合索引，该索引可快速筛选出dest IS NULL的行。
  • 确认codes表的area_codes字段有普通索引，前缀匹配LIKE CONCAT(area_codes, '%')需要依赖此索引避免全表扫描。

二、高效更新方案 #

方案1：预计算匹配结果（临时表批量更新） #

先把需要更新的POINT_TARGET和对应最长前缀的dest预计算到临时表，再批量更新，避免重复匹配：

-- 创建临时表存储匹配结果
CREATE TEMPORARY TABLE tmp_updates (
    point_target VARCHAR(20) PRIMARY KEY,
    dest VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

-- 批量计算每个未填充dest的POINT_TARGET对应的最长前缀dest
INSERT INTO tmp_updates
SELECT t.POINT_TARGET, c.dest
FROM test_cdr t
LEFT JOIN codes c ON t.POINT_TARGET LIKE CONCAT(c.area_codes, '%')
WHERE t.dest IS NULL
GROUP BY t.POINT_TARGET
HAVING LENGTH(c.area_codes) = MAX(LENGTH(c.area_codes));

-- 批量更新test_cdr
UPDATE test_cdr t
JOIN tmp_updates tu ON t.POINT_TARGET = tu.point_target
SET t.dest = tu.dest
WHERE t.dest IS NULL;

优点：仅做一次关联匹配，避免每条更新都执行子查询，大幅提升效率。

方案2：按前缀长度从长到短分批更新 #

优先匹配最长前缀，避免短前缀覆盖长前缀结果，同时缩小每次更新的范围：

-- 先查询codes表中最长的前缀长度
SELECT MAX(LENGTH(area_codes)) FROM codes;

-- 从最长到最短循环执行更新（示例假设最长长度为10）
UPDATE test_cdr t
JOIN codes c ON t.POINT_TARGET LIKE CONCAT(c.area_codes, '%')
SET t.dest = c.dest
WHERE t.dest IS NULL
AND LENGTH(c.area_codes) = 10;

UPDATE test_cdr t
JOIN codes c ON t.POINT_TARGET LIKE CONCAT(c.area_codes, '%')
SET t.dest = c.dest
WHERE t.dest IS NULL
AND LENGTH(c.area_codes) = 9;

-- 依次递减到最短前缀长度

优点：每次仅处理未匹配的行，且优先匹配长前缀，避免重复计算，锁表时间更短。

方案3：优化单条更新的子查询逻辑 #

若必须用LIMIT分批更新，优化子查询的匹配效率：

UPDATE test_cdr t
SET t.dest = (
    SELECT c.dest
    FROM codes c
    WHERE t.POINT_TARGET LIKE CONCAT(c.area_codes, '%')
    ORDER BY LENGTH(c.area_codes) DESC
    LIMIT 1
)
WHERE t.dest IS NULL
LIMIT 50000; -- 增大批次到5万，根据服务器性能调整

关键：确保codes表的area_codes有索引，让子查询的LIKE前缀匹配能用到索引，同时ORDER BY LENGTH(area_codes) DESC可快速拿到最长前缀。

三、索引优化建议 #

• test_cdr表：创建联合索引idx_null_dest_point (dest, POINT_TARGET)，快速定位dest IS NULL的行。
• codes表：创建索引idx_area_code (area_codes)，让前缀匹配LIKE CONCAT(area_codes, '%')能利用索引加速查询。
• 若codes表前缀长度差异大，可额外创建索引idx_area_code_len (LENGTH(area_codes) DESC, area_codes)，进一步优化排序逻辑。

四、其他注意事项 #

• 关闭自动提交：执行更新前执行SET autocommit = 0;，每批次更新后执行COMMIT;，减少事务日志的写入开销。
• 监控磁盘IO：若更新时磁盘IO过高，可降低批次大小，或调整MySQL的innodb_buffer_pool_size参数，让更多数据缓存到内存。
• 避免锁冲突：尽量在业务低峰期执行更新，减少对线上业务的影响。

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