不使用JOIN更新表的运行逻辑及大数据量执行报错问题咨询

运行原理 #

你使用的UPDATE employee SET empname_new = Empname;是标准的无过滤条件单表UPDATE语句，运行逻辑如下：

• 数据库引擎首先锁定目标employee表，扫描全表的所有数据行（无WHERE条件时不会做行过滤）
• 针对每一条数据行，读取当前行Empname字段的存储值，直接赋值给同一行的empname_new字段，不会跨行关联其他数据
• 支持事务的存储引擎（比如InnoDB）会先将每一行的修改记录写入事务日志，所有行处理完成且事务提交后，修改才会持久化到数据表文件中
  补充：如果你需要实现Empname末尾带.时自动去掉.的需求，当前语句无法满足，需要加字符串处理逻辑，参考语句为UPDATE employee SET empname_new = TRIM(TRAILING '.' FROM Empname);

海量数据场景下的常见错误及原因 #

• 事务日志溢出报错
  全表更新会产生远超普通操作的事务日志量，千万级及以上数据量的更新很容易超过数据库配置的事务日志大小上限，触发日志满错误，整个更新事务会全部回滚。
• 锁等待超时/死锁
  无WHERE条件的UPDATE会给全表所有行加排他锁，执行期间所有对该表的增删改操作都会被阻塞，如果此时有其他长事务同时持有该表的部分行锁，会直接触发锁等待超时，极端情况会产生死锁。
• 主从同步延迟陡增
  主从架构下该语句产生的大量binlog需要同步到从库，低版本MySQL（5.6之前）默认单线程重放binlog，会导致主从延迟大幅升高，影响从库读请求的时效性，严重时会导致主从同步中断。
• 磁盘IO耗尽导致实例不可用
  全表扫描+批量写操作会在短时间内占用极高的磁盘IO资源，如果磁盘IO性能不足，会拖慢整个数据库实例的所有请求，甚至触发请求超时、服务不可用。
• 字段长度溢出报错
  若新增的empname_new字段定义的最大长度小于Empname字段，更新时遇到Empname值长度超过新字段限制的情况，会触发数据截断或者溢出报错。

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