MySQL DML进阶：批量写入、冲突处理与事务锁避坑指南

DML系列写到第三篇前两篇我们聊了INSERT、UPDATE、DELETE的基本写法也顺手处理了WHERE条件、LIMIT限制这类细节。这篇要说的东西在实际业务里踩坑率更高——批量写入、冲突处理、多表更新删除、事务与锁的配合还有误操作的补救思路。说实话DML的真正难度不在语法本身而在于你写出来的每条语句到了线上环境会怎么执行、锁住哪些行、影响多少数据。这篇文章就当是DML的进阶补充内容都是我在实际项目里反复验证过的希望能帮你把SQL从“能跑”提升到“跑得稳”。1. 批量写入INSERT的多行与冲突处理1.1 一次INSERT多条记录的正确姿势新手通常喜欢在循环里一条条INSERT比如用Python的for循环往MySQL里写1000行数据。这不是不能用但性能差距非常明显。每执行一次INSERTMySQL都要走一遍完整的SQL解析、权限校验、执行计划生成、InnoDB事务提交逻辑网络往返一次哪怕数据量很小这些固定开销也省不掉。换成一次INSERT多条VALUES记录一条SQL把所有数据带过去开销几乎只算一次写入速度能快上好几倍。-- 推荐一次插入多条 INSERT INTO order_item (order_id, goods_id, goods_name, price, qty) VALUES (1001, 501, 机械键盘, 399.00, 2), (1001, 502, 无线鼠标, 129.00, 1), (1002, 503, 显示器支架, 259.00, 1);这个写法本身没难度但有几个细节值得注意。首先单条SQL的总长度会受max_allowed_packet参数限制默认通常是64MB或者4MB要看具体版本和配置。如果你一次性插入几十万行SQL文本极其庞大会直接撞到这个上限报Packets larger than max_allowed_packet are not allowed错误。我实际测过一个常见场景一条100万行的多行INSERT生成的SQL文本有好几十MB线上环境默认配置根本扛不住。解决思路有两个一是分批插入每批500到2000行二是适当调大max_allowed_packet但我不建议调到太大单条SQL过大本身也会造成主从复制延迟。另一个很多人忽略的点多行INSERT只要有一条记录违反约束整条INSERT的所有行都会回滚。比如VALUES里有100行其中第99行主键冲突那前面98条也会一起失败。这在业务逻辑里往往不是你想要的尤其当你从别的系统导数据时只想跳过脏数据、留下干净数据。这时就得靠下面说的INSERT IGNORE。1.2 INSERT IGNORE与ON DUPLICATE KEY UPDATE的选择先说INSERT IGNORE。它的语义很简单插入时遇到主键冲突、唯一键冲突或者其他可以忽略的错误直接跳过这一行不影响其他行插入。这个特性在批量导入、数据同步场景里非常实用。INSERT IGNORE INTO user (id, mobile, nickname) VALUES (1, 13800001111, 张三), (2, 13800002222, 李四), (3, 13800001111, 王五);假设mobile字段有唯一索引那么(id1)和(id3)的mobile一样id3这行会被静默跳过id1和id2正常写入。注意“静默”这两个字——INSERT IGNORE不会报错但也不会明确告诉你到底跳过几行。想确认跳过情况可以看程序返回的affected rows也可以用SHOW WARNINGS查看执行期间的警告。再说ON DUPLICATE KEY UPDATE。它的逻辑是插入时如果碰到唯一键冲突就转成执行UPDATE。这个语句非常适合“存在就更新、不存在就插入”的幂等写入场景。INSERT INTO user_login_stat (user_id, login_date, login_count) VALUES (10001, 2024-11-01, 1) ON DUPLICATE KEY UPDATE login_count login_count 1;这条SQL的含义当天该用户没有登录记录就插入一条计数为1已有记录则把login_count加1。注意UPDATE子句里可以用两个特殊引用VALUES(login_count)代表本次试图插入的值而直接写login_count代表当前表中已有的值。这个区别在实际业务中极易出错。比如你想“让累计值加上本次值”应该写login_count login_count VALUES(login_count)如果写成login_count VALUES(login_count)那就变成了覆盖式更新之前累计的数据就丢了。有一点需要特别提醒ON DUPLICATE KEY UPDATE不只是主键冲突会触发任何唯一索引冲突都会触发。如果你的表上既有主键又有多个唯一索引两条不同记录分别撞了不同唯一索引也可能都触发更新。这个行为要仔细核对表结构不然会出现“我只想按手机号更新结果邮箱冲突也把记录改了”的问题。1.3 REPLACE INTO能不用就尽量不用REPLACE INTO是另一个处理冲突的语法。它的执行逻辑是先尝试INSERT如果发生唯一键冲突就先把旧记录DELETE掉再INSERT新记录。这句话看着简单实际执行时有两个隐藏代价。第一DELETE加INSERT是两步操作相当于所有相关索引都要先删后插。InnoDB在删除记录时要处理版本链和回滚段索引也要同步维护性能开销明显高于ON DUPLICATE KEY UPDATE的“就地更新”。在写入频繁的线上表上滥用REPLACE INTO很容易把InnoDB的写放大拉高。第二如果表上有外键其他表引用了这个主键REPLACE INTO的DELETE操作会把引用数据的完整性破坏直接报错。更隐蔽的问题是自增主键——旧记录被删除后新记录插入消耗一个新的自增ID这意味着你的主键序列会因为REPLACE操作产生越来越大的空洞。数据量小无所谓数据量大了以后主键和索引页的碎片化会越来越严重。所以我的结论很明确同等场景下优先选ON DUPLICATE KEY UPDATEREPLACE INTO只有在你确定要“整行替换所有字段、包括可能已经变化的列”时才合适而且必须确认没有外键引用。日常开发里我基本只在临时清洗数据时用REPLACE INTO线上业务代码坚决不用。2. 更新与删除涉及多表时的写法差异2.1 多表UPDATE的JOIN写法单表UPDATE大家都熟但业务里经常出现“根据A表的数据去更新B表”的需求。比如订单表里保存了用户等级快照用户等级调整后要把所有未发货订单里的等级快照同步更新。这种需求如果先SELECT出来再逐条UPDATE既慢又容易遗漏。MySQL提供了多表UPDATE语法可以直接在UPDATE语句里做JOIN关联。UPDATE orders o JOIN users u ON o.user_id u.id SET o.user_level u.level WHERE o.order_status 0;这条语句的含义很直观把orders表中所有未发货订单的user_level更新为对应用户的最新等级。MySQL会先做JOIN找出匹配行再对匹配结果执行SET操作。用这个语法有个必须注意的点JOIN的结果集如果是多对一即一张订单关联多个用户记录那UPDATE可能执行多次最终取哪条记录存在不确定性。虽然按正常外键关系这种场景不会出现但你在写JOIN条件时还是要想清楚一对一的联系是否成立。另外UPDATE子句里的列可能来自任意一张JOIN表但只有目标表的列才能作为被更新列其他表的列只能用来取值。这一点在表名比较多时容易搞混建议养成习惯SET后面只写第一张目标表的列。关于执行效率可以提一个经验多表UPDATE通常会走BKA或者嵌套循环连接其中驱动表的选择很关键。一般来说MySQL会倾向于用小表驱动大表但也会参考索引情况。如果你的UPDATE执行很慢可以用EXPLAIN先看执行计划确认JOIN的驱动顺序是否合理必要时用STRAIGHT_JOIN强制指定驱动表的顺序。注意STRAIGHT_JOIN不能滥用只在明确知道优化器选错驱动表时才用。2.2 DELETE多表关联只删你想删的表DELETE的多表操作比UPDATE更反直觉。DELETE语法中DELETE后面可以直接指定要删除哪个表的记录。这个设计不是可有可无的它决定了多表JOIN时哪些表的数据会被删掉。-- 只删订单保留用户 DELETE o FROM orders o JOIN users u ON o.user_id u.id WHERE u.status 2; -- 只删用户保留订单 DELETE u FROM orders o JOIN users u ON o.user_id u.id WHERE u.status 2; -- 两个表都删 DELETE o, u FROM orders o JOIN users u ON o.user_id u.id WHERE u.status 2;第一段的意思是找到所有status为2比如已注销的用户删掉他们名下的订单但用户本身不删。第二段反过来删掉用户但保留订单。第三段是双删。这里最常踩的坑是有些新手以为多表DELETE只要JOIN了所有参与JOIN的记录都会删除。实际上如果不写清楚表别名MySQL默认删除目标表的记录其他表只是参与筛选和连接不会动数据。这个特性在团队协作里容易被误解建议在SQL注释里写明“本语句只删除xxx表数据”。另外一个重要经验是多表DELETE和外键约束的相互作用。如果orders表有外键关联users表的id你想“删掉所有已注销用户及其关联订单”双删时DELETE顺序如果先删users再删orders会直接因为外键约束报错。所以要么先删子表数据再删主表数据要么用一条多表DELETE同时处理。这也是为什么多表DELETE在这个场景里有优势——它在同一个语句里同时删除关联表的数据InnoDB会按依赖顺序处理。2.3 TRUNCATE与DELETE清零还是逐行删TRUNCATE TABLE经常和DELETE搞混。两者效果上都是清空表数据但底层执行逻辑完全不同。DELETE是逐行删除每行删除都会记录到事务日志触发相应的触发器可以被事务回滚TRUNCATE是直接重建表结构级别的操作InnoDB实现上类似于重建表空间整体速度极快但不会被普通事务回滚也不会逐行触发DELETE触发器。选型建议看场景需要保留自增计数、只是想删掉部分数据显然用DELETE配合WHERE。想彻底清空表、重置自增到1且确认不需要回滚用TRUNCATE。表上有外键被引用时TRUNCATE会失败必须改用DELETE或者先处理外键关系。写TRUNCATE时要格外谨慎因为一旦执行数据基本没有找回的可能除非你有备份或binlog可以回溯。很多公司在生产环境做了权限控制TRUNCATE和DROP命令需要审批或通过专门的运维平台执行就是为了防止这种“一键清空”造成不可逆事故。就我个人建议开发环境随便用预发和线上环境尽量别依赖命令行直接跑TRUNCATE。3. 事务、锁与DML的配合3.1 隐式提交一个容易被忽略的坑MySQL的每条DML语句都默认包含在一个隐式事务里。也就是说如果你没有显式开启事务单条UPDATE会视为一个事务执行完自动提交。这个特性日常没什么问题但在需要多条DML原子生效的场景里比如“转账扣A账户加B账户”你绝不能分成两条独立的UPDATE让它自动提交否则中间一旦第二条失败第一条已经生效数据就错了。正确做法是显式开启事务START TRANSACTION; UPDATE account SET balance balance - 1000 WHERE user_id 1; UPDATE account SET balance balance 1000 WHERE user_id 2; COMMIT;如果第二条UPDATE报错或者业务校验失败你可以执行ROLLBACK两条更新一起撤销。这里有一个我经常提醒同事的细节START TRANSACTION之后很多人在代码里忘记检查每一条DML的返回值或者异常。如果你的第一条UPDATE执行成功第二条UPDATE因为网络超时抛了异常但你的事务提交代码还在继续往下走最后COMMIT依然会把第一条提交掉。好的实践是所有DML必须确认成功后再COMMIT一旦任何一步抛出异常立即ROLLBACK。另一个和隐式提交相关的坑是DDL语句。MySQL里执行ALTER TABLE、CREATE INDEX这类DDL会导致当前事务被隐式提交。这意味着如果你在一个事务里先做了DML再执行DDL前面未提交的DML直接变成已提交没法回滚了。事务边界一定要在代码逻辑里画清楚别把DDL混进去。3.2 行锁与间隙锁DML慢和死锁的真正原因InnoDB默认使用行级锁。看起来行级锁比表锁精细但实际业务里因为索引用不上行锁很容易升级成表级锁或者退化为锁住大量行。UPDATE或DELETE的WHERE条件如果没有走索引InnoDB只能全表扫描扫描过程中会把扫描到的行全部加锁这直接导致并发急剧下降。举个实际案例某运营后台执行了UPDATE orders SET status 2 WHERE order_no xxx但order_no列上没建索引这条SQL直接把整张表的行都扫了一遍期间所有涉及orders表的写操作全部阻塞。线上报警数据是“连接数飙升事务等待时间超阈值”。排查后发现就是缺索引导致的锁范围失控。所以写DML之前一个很重要的自查习惯是WHERE条件是否命中了索引如果命中执行EXPLAIN里应该看到ref或者eq_ref级别的访问类型。间隙锁gap lock更隐蔽。在可重复读隔离级别下InnoDB在索引记录之间还会加间隙锁目的是防止幻读。当你的UPDATE或DELETE的WHERE条件命中一个范围比如WHERE age BETWEEN 20 AND 30除了满足条件的行InnoDB还可能给范围内不存在的间隙加锁这会让其他事务在间隙内插入新记录时阻塞。高并发写入场景下两条事务分别锁定相邻间隙彼此都在等对方释放间隙锁就可能演变成死锁。MySQL检测到死锁后会主动回滚其中代价较小的一条事务但你的业务代码必须能接住“死锁重试”的异常。这也是为什么很多资深开发会在写入逻辑里加一个重试机制遇到死锁报错就过一会儿再执行一次。3.3 影响行数与自增主键消耗DML执行后程序返回值里有一个affected rows影响行数。很多人只把它当“成功了几行”看但在ON DUPLICATE KEY UPDATE这一类的场景里受影响的判断规则有些特殊。比如MySQL客户端执行INSERT...ON DUPLICATE KEY UPDATE时如果因为冲突走了UPDATE更新且新值和旧值相同影响行数可能显示为0如果值有变化则显示为2。这个“2”的来源是旧行的删除加新行的插入尽管底层并不是真正的删除但计数规则沿用了这种逻辑。如果程序依赖这个返回值做判断必须分清楚语义。自增主键的消耗也是一个常见话题。DELETE删除记录后自增计数并不会回退。即便你删掉全表最后一条数据下次INSERT也会继续用递增后的ID。TRUNCATE则会重置自增。还有一个容易被忽视的场景INSERT失败时如果执行过程中已经申请过自增值哪怕最终记录插入失败回滚自增计数也不会归还。这意味着你的主键之间会出现空洞。如果业务层对主键连续性有要求这是一种典型的技术债应在业务设计阶段就放弃“连续主键”这个不成立的需求。4. DML常见问题与防误操作清单4.1 高频报错速查表报错信息常见原因解决思路Data too long for column xxx字符串超出列定义长度检查字符集与字段长度UTF-8下注意字节占用Out of range value for column数值超出字段范围调整字段类型或校验写入值Duplicate entry xxx for key xxx主键或唯一索引冲突使用INSERT IGNORE或ON DUPLICATE KEY UPDATELock wait timeout exceeded行锁等待超时默认50秒检查是否有慢事务未提交优化索引必要时分批执行Deadlock found多个事务互相持锁等待统一加锁顺序使用事务时保持操作顺序一致Packets larger than max_allowed_packet单条SQL超出包大小上限分批写入或调大max_allowed_packetCannot delete or update a parent row外键约束阻止删除先清理子表引用数据或改用多表DELETEColumn count doesnt match value countVALUES列数对不上检查INSERT列和值数量是否一致这张表里的报错大部分我都在实际环境里见过不止一次。尤其Data too long很多人以为是字段长度不够其实是字符集陷阱——比如VARCHAR(10)存入10个汉字在utf8mb4字符集下占10个字符但如果存的是表情符号可能超过长度限制。判断时要先看字符集和字节数的换算关系。4.2 防误操作的三道保险DML误操作最常见的就是UPDATE和DELETE忘加WHERE条件结果全表数据被改动或者清空。遇到过几次之后我现在给自己立了几条规矩也推荐给团队里的新人。第一道保险先SELECT确认范围。在执行UPDATE或DELETE之前先跑一条相同的WHERE条件的SELECT如SELECT COUNT(*) FROM orders WHERE ...确认影响行数和预期一致。这个习惯成本很低但对数据安全的价值极高。尤其是DELETE这种不可逆操作连这条检查都不做就直接执行等于裸奔。第二道保险在事务里执行先不COMMIT。UPDATE或DELETE先在事务中执行然后执行SELECT查看结果确认无误再COMMIT有问题就ROLLBACK。这件事的原理是利用事务的原子性和隔离性把“看效果”和“真正生效”分开比直接执行完再恢复安全得多。第三道保险备份或快照。对生产环境执行批量DML之前尤其是影响行数可能很大的时候提前对涉及的表做一次备份或者至少确认有可用的备份和binlog。某些公司的运维平台还能对特定表做闪回但这依赖binlog格式是ROW且开启了完整日志平时就要保证配置正确。4.3 排查慢DML的思路当一个UPDATE或DELETE执行特别慢先别急着骂数据库按下面的顺序排查基本能定位问题。第一步看EXPLAIN执行计划。重点确认WHERE条件是否走了索引访问类型是const、ref还是ALL。如果是ALL说明全表扫描慢是必然的优先考虑建索引。第二步看锁等待。用SHOW ENGINE INNODB STATUS查看是否有事务长时间占用锁。常见情况是“一个事务开了没提交把行锁占着其他事务全部排队等”。这种问题不在SQL本身在事务生命周期管理。第三步看数据量和批量方式。如果单条UPDATE要更新几十万行InnoDB内部会逐行加锁并记录UNDO日志事务会越来越大提交时刷盘成本也高。这种规模的数据更新改成小批量循环执行通常更稳。比如每次更新5000行循环100次逐次提交能明显降低锁竞争和UNDO膨胀。第四步看主从延迟。如果线上该表有大量读流量一个慢UPDATE触发了长时间行锁可能会让从库的并行复制线程排队出现主从延迟报警。这种情况往往要回源头看是否索引缺失或者事务过长。最后再说一个实战中的体会写DML时脑子里一定要有一张“这条SQL要触碰多少行数据”的图。很多问题不是语法层面能看出来的而是对这个表的数据规模、索引结构、事务隔离级别、并发访问量没有整体认知。多花一分钟看执行计划能省下后面一整夜的排查时间。这算是DML系列里我最想强调的一句话。