MySQL 5.6执行跨表条件UPDATE时遇Error 2006（服务器断开）求助

问题背景 #

执行以下SQL时触发MySQL Error 2006 (server gone away)错误，该错误通常因查询负载过高导致连接超时：

UPDATE user_items 
SET A = A + 1, B = B + 1 
WHERE uid IN 
(SELECT uid FROM user_achievements WHERE C >= 10)

查询优化方案 #

1. 用JOIN替代IN子查询 #

MySQL 5.6中IN子查询可能生成低效执行计划，尤其当子查询返回大量数据时。改用JOIN可减少临时表开销，提升执行效率：

UPDATE user_items ui
JOIN user_achievements ua ON ui.uid = ua.uid
SET ui.A = ui.A + 1, ui.B = ui.B + 1
WHERE ua.C >= 10;

2. 分批更新 #

如果涉及记录数过多，一次性更新会占用大量资源引发超时。可按批次拆分操作，例如每次处理1000条：

-- 示例：按uid范围分批，循环执行直到无更新
UPDATE user_items ui
JOIN user_achievements ua ON ui.uid = ua.uid
SET ui.A = ui.A + 1, ui.B = ui.B + 1
WHERE ua.C >= 10
  AND ui.uid > 上次最大uid
ORDER BY ui.uid
LIMIT 1000;

也可编写存储过程实现自动循环分批，避免手动重复执行。

3. 添加必要索引 #

确保相关字段存在索引，加速查询匹配：

• 给user_achievements表的C和uid字段创建联合索引：

CREATE INDEX idx_ua_c_uid ON user_achievements(C, uid);

• 检查user_items表的uid字段是否有主键或普通索引，无则添加：

CREATE INDEX idx_ui_uid ON user_items(uid);

规避错误的配置调整 #

针对MySQL 5.6，可调整以下配置参数（修改my.cnf后重启服务，或临时全局设置）：

• 延长连接超时时间：增大wait_timeout和interactive_timeout，若查询耗时久可调整为600秒：

SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

• 增大允许的数据包大小：避免因数据包过大导致连接断开：

SET GLOBAL max_allowed_packet = 67108864; -- 64M

• 检查连接数上限：确保max_connections足够，避免连接耗尽：

SHOW VARIABLES LIKE 'max_connections';

数值偏低时可适当调高。

注意事项 #

• 优化操作前先在测试环境验证，避免影响生产数据；
• 分批更新时注意事务控制，必要时手动提交事务，防止长时间锁表；
• 添加索引会增加写入操作开销，需权衡查询性能与写入性能。

内容的提问来源于stack exchange，提问作者Gin.A