开头 很多朋友一提SQL就下意识分成增删改查然后觉得DML不过就是insert、update、delete、select那点语法没啥好学的。但我在实际开发和排障里见过太多因为DML没写利索导致的事故一条update把全表字段洗成同一个值、delete删完发现忘了带条件、批量insert把生产库搞到锁等待飙升、一个看起来没啥毛病的查询因为用了不该用的函数导致索引失效。所以我决定把DML这块掰开揉碎从操作语义、事务边界、性能影响三个角度重新梳理一遍尽量把这几个命令的使用场景、坑点、优化方向都讲到位。这套内容比较适合刚接触数据库开发的同学打基础也适合天天写业务SQL、想回头补一补底层逻辑的工程师参考。1. 动手之前先把DML的账算清楚1.1 哪些操作属于DML哪些不归它管DML全称是Data Manipulation Language中文叫数据操作语言。它负责的是对表中已有数据的增、删、改、查。标准SQL里INSERT、UPDATE、DELETE属于DMLSELECT因为只是读取严格来说属于DQLData Query Language但在MySQL、SQL Server、PostgreSQL这些主流数据库的官方文档里DML章节通常也把SELECT收进去毕竟业务需求中查询占大头开发时也总是把读和写放在一起讨论。DDLCREATE、ALTER、DROP这些管的是表结构DCLGRANT、REVOKE管的是权限DML管的是数据本身这套边界要清楚因为排查问题的时候第一步就是判断你说的慢到底是慢在哪一层。日常工作里最常见的误区是把DML等同于写数据忽略了读取也是DML的重要部分。很多慢查询优化、索引设计其实都是围绕SELECT展开的所以后面我会单独开一节讲查询里的DML思维。另外要注意不同数据库对DML语句的语法和事务行为有一些细节差异。比如SQL Server默认是隐式事务执行完一条语句如果没有显式提交会自动提交而MySQL在InnoDB引擎下autocommit默认是开启的PostgreSQL则是在未提交前只有当前会话可见。写代码的时候如果混着用很容易在事务边界上翻车。1.2 所有DML操作都绕不开的两个底层机制DML不是简单的字符串拼装它的背后有两个核心机制日志机制和锁机制。日志机制上几乎所有主流数据库在处理DML时都会先写日志再改数据页这叫WALWrite-Ahead Logging或者redo log undo log组合。redo log保证崩溃恢复undo log保证回滚。所以一条update改了一万行不光是改一万行数据还要生成一万行undo记录这个开销比你以为的大得多。锁机制则是为了保证并发下数据一致。行锁、间隙锁、表锁不同隔离级别下的加锁范围完全不一样。很多新人问我为什么一条delete明明删一行却把整张表锁住了多半是没走索引导致锁升级成了表锁。理解这两个机制之后很多问题就说得通了为啥批量insert比一行一行insert快因为一个事务只提交一次日志刷盘次数少为啥delete之后表文件没有变小因为undo日志和碎片还在为啥别人在更新数据时你的select会超时因为锁等待超过了阈值。DML的每一个选择都是在和底层机制博弈这也是为什么我劝大家别死记语法先弄清楚语法背后的账。2. INSERT写进去容易写扎实难2.1 一条INSERT语句的完整生命周期INSERT看起来就是往表里塞一行数据其实它要做的事很多解析SQL、检查权限、校验约束、分配存储空间、写入数据页、更新索引、写redo log。如果你插入的列上有默认值、自增id、业务唯一键那还要额外做计算和冲突检测。比如SQL Server里如果列设置了默认值约束INSERT时不指定该列数据库会自动取默认约束的表达式MySQL里同样支持DEFAULT关键字但要小心如果列定义是NOT NULL DEFAULT而你显式传了NULL很多版本会报错或者直接存进NULL这点要看sql_mode。我最常跟同事强调的一点是INSERT语句里尽量列名写得明明白白不要偷懒写INSERT INTO t VALUES(...)。一旦表结构发生调整比如多了个字段不带列名的INSERT立即就错位插入的数据全部错列而且这种错误在测试环境很难发现因为类型不匹配时数据库才会报错类型刚好对上了就直接脏数据入库了。批量插入是另一个大坑。业务上经常需要一次性写入几千几万条数据。有人喜欢写一条INSERT包含多组VALUESMySQL支持也有人喜欢在代码里循环执行单条INSERT。前者明显效率更高因为只发起一次SQL解析和一次事务提交后者每次都要走完整的解析、执行、提交流程性能差异可以到十倍以上。但如果数据量太大一条INSERT的VALUES太多反而会让binlog和redo log体积巨大还可能撑爆undo。经验值一般控制在500到1000条一组比较稳分批提交避免单次事务过大。2.2 默认值、空值和唯一约束的实操陷阱先说默认值。热词里有sql 默认值guid这个场景很典型表主键设计成varchar默认值用NEWID()或者UUID()生成。逻辑没问题但执行INSERT的时候如果不指定主键列数据库会替你把GUID算出来这个过程会消耗随机数生成和字符串处理资源。如果表数据量很大比自增int的写入性能差不少。后来我们优化过一个日志表就是把GUID主键改成自增bigint加上一个单独的UUID业务列写入性能提升了接近一倍。再说空值。NULL和空字符串是两个完全不同的东西。NULL表示未知空字符串表示长度为0的字符串。排序、去重、索引统计时这两者行为都不一样。比如用COUNT(*)不会统计NULL列但会统计空字符串用DISTINCT去重时NULL值在MySQL和SQLite里会被当作同一个值合并但在Oracle里NULL会参与唯一约束判断多行NULL是可以共存的。老实说我见过太多因为空值和NULL没处理好导致的统计数据对不上。建议在建表时就想清楚业务中那个字段是否允许NULL如果允许查询的时候就用IS NULL或IS NOT NULL去判断不要用 NULL 或者 ! NULL 这种写法因为这两个表达式的结果永远是UNKNOWNWHERE里面会被过滤掉查不到任何东西。唯一约束也是INSERT常见问题的来源。比如你写入了一个已存在的唯一键数据库会报Duplicate entry或unique constraint violation。代码里不认真捕获这个异常可能剩下来的一整批数据都回滚。开发接口时对这种唯一冲突要有预期要么提前查一次做幂等判断要么捕获异常后换一种策略比如ON DUPLICATE KEY UPDATE或者INSERT ... ON CONFLICTPostgreSQL的upsert当然用这些语法时要小心它们的并发语义不是简单的不存在就插。3. UPDATE改对数据比改数据本身难十倍3.1 先解决怎么只改我该改的那几行UPDATE最让人紧张的就是WHERE条件。我见过生产事故里最痛的几次全是UPDATE没有WHERE或者WHERE写错导致全表数据被覆盖。这事的根源不是手滑而是对条件命中的语义理解不透。SQL里UPDATE的WHERE和SELECT的WHERE在过滤逻辑上完全一样但在执行时UPDATE会带着锁去扫描匹配行。所以如果你的WHERE不能走索引数据库就可能做全表扫描然后把每一行都锁住改掉哪怕最终只有几行匹配。为此我在团队里立过一个规矩上线前任何UPDATE语句必须EXPLAIN看执行计划必须确认rows预估值不超过预期范围。另外UPDATE一个很大的隐藏问题是更新的复合语义。比如你写UPDATE t SET a a 1 WHERE id 1多数数据库执行时是先把原值取出来加1再写回整个过程在行锁保护下完成不会丢更新。但如果先SELECT出来在应用层加1再UPDATE回去就存在并发覆盖的风险必须引入悲观锁或者乐观锁版本号机制。如果业务里需要根据一张表的结果去更新另一张表各个数据库的写法不一样MySQL是UPDATE t1 JOIN t2 ON ... SET t1.col t2.colSQL Server和PostgreSQL支持UPDATE ... FROM ... WHEREOracle则用MERGE。我不建议在这种跨表更新里写太复杂的关联逻辑因为优化器对关联更新执行计划的掌控不如普通查询那么精准出问题时还难排查。简单粗暴的做法是小数据量先SELECT到应用层再逐条UPDATE大数据量则考虑临时表加JOIN总之别一上来就写一个跨三个表的UPDATE。3.2 数据没变时也要小心锁和日志很多开发同学不知道UPDATE即使SET的值和原值一样数据库依然会执行整行更新流程写入新的数据版本生成redo日志并触发相关索引更新。你要问为什么不能跳过因为数据库无法在不读取原值的情况下判断相同而读取原值本身就可能需要IO加上判断逻辑复杂数据库设计上就不做这种优化。所以在高并发场景下频繁执行值没变但也要set一次的应用会白白增加压力。还有一个容易忽略的是UPDATE和事务的交互。热词里提到sql server windows nt占用内存其实是SQL Server内存管理与其他进程混淆的问题但DML上也有关联。UPDATE大量数据时SQL Server需要维护行版本和TempDB空间如果TempDB不够大或内存压力高事务可能被报错中止。MySQL中长事务会导致undo膨胀而这背后同样是内存和磁盘的博弈。处理大批量UPDATE的正确姿势是:如果数据总量很大别一条语句更新几十万行分段更新更好比如一次更新一万行然后sleep一下提交事务再继续。这样既避免长事务占用锁又减轻undo和日志的压力。但分段更新也有坑如果在循环里用了相同的WHERE条件而没有把已更新数据排除就会反复扫描甚至死循环。经验写法是加上一个状态字段或者按主键范围切片。4. DELETE与数据清理的正确姿势4.1 DELETE的语法简单删除的代价不简单DELETE是标准的DML操作它按行删除数据每条被删的记录在事务回滚时都能恢复所以每删一行都要写undo或日志。如果你执行DELETE FROM t WHERE create_time 2024-01-01假设命中100万行这一条语句会产生海量undo执行时间会远大于你的预期甚至导致主库拉垮。TRUNCATE为什么快因为它不去逐行记undo而是直接把表的数据页释放掉属于DDL范畴所以速度飞快但代价是不能按条件删也不能回滚到行级。所以清理数据时要想清楚需要事务保护吗需要保留一部分条件吗需要之后还能恢复吗如果只是把整张表清空TRUNCATE更合适如果是要删一个月前的过期数据那必须用DELETE并且分批跑。分批删除的做法我用过很多次。一个简单模板是这样每次先查出一个主键范围或符合条件的TOP N条然后DELETE WHERE主键 IN (这些id)提交再循环。注意这里不要用LIMIT加OFFSET去翻页删除因为OFFSET越翻越慢还会因为数据变化出现重复或遗漏。稳妥的做法是基于主键做游标比如每次删除WHERE id last_id ORDER BY id LIMIT 500然后不断更新last_id直到删除完。这个方案既能控制单次事务大小又能让索引扫描保持稳定。4.2 大表删除后的空间管理问题DELETE完成之后表的数据文件不会立刻变小。数据库只是把那些数据页标记为可复用但物理文件还占着空间。这在MySQL InnoDB里特别明显很多人删了几GB数据却看到ibd文件大小不变以为删除没生效。其实你需要执行OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB来重建表回收空间。SQL Server里可以用DBCC SHRINKDATABASE或DBCC SHRINKFILE但收缩操作本身很耗资源最好安排在维护窗口。PostgreSQL里则用VACUUM FULL解决注意它也会锁表。所以有效姿势是如果业务确定要大量清理数据干脆在低峰期进行重建表清数据一步到位而不是天天小批量删但从不整理碎片。DELETE还有另一个隐藏点外键约束。如果删除父表的一行而子表有外键引用数据库会去检查子表并可能加锁导致删除速度骤降。这种情况我建议先处理好子表数据或者临时禁用外键检查生产环境慎用必须有完整方案。在ORM层面很多框架的级联删除其实是先在应用层查出记录再逐条删关联表数据这种效率更低但至少可控。你要是敢直接在数据库层开级联一旦误删关系链全断恢复极其痛苦。5. SELECT里的DML思维去重、空值与窗口函数5.1 去重别只会DISTINCT防重才是核心热词里关于sql语句去重的搜索特别多可见这是个高频需求。DISTINCT很简单它会对查询结果集去重但它的代价是把结果集排序或者哈希去重数据量一大就容易变成慢查询。更常见的问题是业务里要防止重复插入仅仅靠SELECT DISTINCT是不够的。比如订单系统为了防止重复提交很多团队会选择先SELECT判断再INSERT但并发场景下两条请求同时查到不存在就都执行INSERT了。真正可靠的是数据库唯一约束或者在INSERT时使用INSERT ... ON DUPLICATE KEY UPDATEMySQL或ON CONFLICTPostgreSQL。不过DISTINCT还是有它的用武之地。比如快速看一个状态字段到底有几种值或者统计分布时GROUP BY比DISTINCT更灵活。在我的经验里DISTINCT适合小结果集、列少的场景如果去重列很多比如十几列结果集本身不明确DISTINCT的性能通常不会好。一个优化思路是先用聚合查询把可能重复的业务键收敛到一行再去关联获取明细数据。这样网络传输和排序量都会小得多。5.2 NULL处理与窗口函数的实战细节NULL在前面INSERT那里说过一部分但SELECT中空值处理更容易让人头大。COALESCE是最常用的空值处理函数它接受多个参数返回第一个非NULL值。用COALESCE(column, 默认值)可以在结果集中把NULL替换掉。但这个替换只影响输出不会改变存储值。如果你要把NULL和空字符串统一转换建议用CASE WHEN column IS NULL OR column THEN N/A ELSE column END。需要注意的是索引对NULL的利用在某些数据库里会打折MySQL的普通索引不存储NULL值PostgreSQL可以部分索引专门处理NULLOracle中NULL不被B树索引包含。所以在WHERE里判断IS NULL可能会走全表扫描这就是为什么很多查空值的SQL变成慢查询。窗口函数是另一个和DML关系密切的内容。热词里也出现了sql窗口函数因为它确实太常用了。ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time DESC)可以给每组数据标号然后用WHERE rn 1取出每个用户的最新记录。这种写法在数据去重、取TopN、做分页排序中的效果远好于GROUP BY关联子查询。窗口函数还有一个容易被忽略的点它是在查询结果集已经生成之后才计算的所以它不会减少IO但它可以把很多本来要写子查询或者自连接的复杂逻辑变成简单的表达式。如果窗口函数需要的排序量太大也会成为慢SQL解决办法是建立匹配PARTITION BY和ORDER BY的联合索引。6. DML事务控制与并发问题6.1 几条DML被事务包裹后行为就完全不一样单独执行一条INSERT时autocommit模式下它会自动提交如果执行失败就自动回滚问题不大。但如果一个业务操作需要先INSERT主表再INSERT子表再UPDATE一个统计字段这三条DML就必须放在同一个事务里否则中途挂了数据就不一致。事务的ACID原则虽然老生常谈但落到DML上要注意原子性由undo保证隔离性由锁或MVCC保证持久性由redo保证。你只有在事务里才能利用这些机制去组合DML而不是每天在应用里拼接SQL。事务还有个特点它会影响锁的释放时机。一条UPDATE只有在事务提交后锁才会释放。如果你在一个显式事务里先SELECT ... FOR UPDATE锁了一行然后去做网络请求或者等待用户输入这期间其他事务都会被卡住这属于典型的长事务拖垮并发。我之前排查过一个线上问题某接口频繁超时后来发现是事务里在UPDATE之后调了一个第三方HTTP接口整个事务要等HTTP返回才提交导致行锁被持有好几秒后面的请求全部排队。解决思路很简单把网络请求挪到事务外面或者先提交事务后再调用外部服务。6.2 隔离级别和行锁间隙锁DML冲突的根源数据库事务隔离级别有四级READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。默认情况下MySQL InnoDB是REPEATABLE READSQL Server是READ COMMITTEDPostgreSQL也是READ COMMITTED。不同的隔离级别影响DML的可见性和锁范围。比如REPEATABLE READ下一条UPDATE修改了某条件范围的数据其他事务再往这个范围插入新数据可能被间隙锁挡住这就是幻读的一部分防护。热词里并行sql优化可以理解为通过加提示词比如SQL Server里的WITH (NOLOCK)或者优化器hint降低锁竞争但NOLOCK会导致脏读这种优化方式生产环境慎用。如果你发现两个事务互相等待对方持有的锁就是死锁。数据库的死锁检测器会选择一个牺牲者回滚应用层经常报deadlock found when trying to get lock; try restarting transaction。避免死锁的最好办法是让所有事务按相同的顺序访问表或行。比如事务A先更新订单再更新用户事务B先更新用户再更新订单两边顺序相反就容易死锁。改成一致的顺序后死锁概率大幅降低。另外单条UPDATE内部有多个索引更新时也可能因为索引顺序导致死锁这时候可以用强制索引或者调整更新逻辑去规避。7. DML性能优化与避坑实战7.1 慢SQL排查先看执行计划再谈优化热词里慢sql优化是搜索大头。DML中SELECT、UPDATE、DELETE都可能变成慢SQL。拿到慢SQL第一步不是改写而是EXPLAIN。MySQL的EXPLAIN会显示type字段从system、const、eq_ref、ref、range、index到ALL访问效率依次递减。理想情况下至少要到range最好能到ref或const。如果看到ALL说明在走全表扫描。这时要检查WHERE条件里的列是否有索引以及函数、隐式转换、前导通配符是否导致索引失效。比如WHERE create_time NOW() - INTERVAL 1 DAY这种写法就可能没问题但WHERE DATE(create_time) CURDATE()会让索引无法使用因为列套了函数。正确做法是改成范围条件create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。UPDATE的慢SQL还有一种常见原因更新大量行时SQL Server里可能因为TOP不支持下推MySQL里可能因为使用了JOIN但执行计划把驱动表选错。解决办法是分段更新或用EXISTS代替IN有时候改写一下就能把扫描量降下来。DELETE同理大数据删除务必分段。我还见过一个SQL语句去重查询的慢SQL原本写法是SELECT DISTINCT a.* FROM a JOIN b ON a.idb.aid结果a表有几十万行b表几百万行DISTINCT要对整个结果集去重。优化后改为EXISTS子查询提前过滤性能翻了几倍。7.2 DML实操中遇到的高频Error与速查这里整理一个我实际工作中收集的常见报错速查表原因和解决方式都写清楚了省得你每次去翻文档。报错信息常见原因处理建议Duplicate entry x for key idx唯一键冲突改用INSERT ON DUPLICATE KEY UPDATE或提前做幂等校验Data truncation: Data too long for column字符串超过列长度检查字段字符集和业务截断逻辑必要时改列类型为TEXT或加大varchar长度Column xxx cannot be nullNOT NULL约束被违反插入前调用COALESCE给默认值或检查业务字段是否漏传Out of range value for column数值超出范围检查应用层类型转换必要时改用DECIMAL/BIGINTDeadlock found when trying to get lock并发事务加锁顺序不一致统一更新顺序减少事务持锁时间Lock wait timeout exceeded行锁被长事务持有定位长事务并优化或把SQL改成分批次小事务SQLSTATE[HY000]: General error: 1267 Illegal mix of collations关联字段字符集不同在JOIN条件中显式转collation如COLLATE utf8mb4_general_ciORA-01704: string literal too longOracle里字符串字面量超过4000字符用CLOB参数绑定或把大文本拆分成多段写入Solaris/Windows连接SQL Server报登录失败登录方式或网络配置问题检查混合认证模式、端口和防火墙策略表中这些错误里锁等待超时在DML场景中出现率最高。一次UPDATE执行了超过innodb_lock_wait_timeout默认50秒还没拿到锁就报这个错。线上处理办法是先查询information_schema.innodb_trx找到阻塞源杀掉它或者等待它提交。更主动的办法是优化事务里的DML让锁持有时间尽量短比如先处理大批量操作的前置查询再快速提交事务。7.3 让DML更省心的几个编程习惯最后分享几个我写业务代码时的DML习惯。第一所有写操作必须显式列出字段名杜绝SELECT *或INSERT无列名。第二写UPDATE或DELETE前先用SELECT确认命中行数可以在测试环境开启事务再执行确认无误后提交。第三批量操作使用分批提交每批控制在500到2000行中间记录过程日志和进度。第四合理利用数据库提供的upsert语法减少应用层的先查再插逻辑从根上避免并发重复数据。第五对核心业务表加字段变更或触发器之前先评估对INSERT/UPDATE既有SQL的影响尤其是默认值约束、生成列和索引的变化会直接影响DML执行路径。在真实项目里DML的坑通常不在语法而在工程判断。你光知道INSERT怎样写能插入数据是不够的还得知道插入时锁怎么加、日志怎么记、失败怎么处理。把这七个章节里的概念串起来看其实核心就一句话每一次DML操作都要清楚它会在数据库内部引发什么样的连锁反应。这也是我把这篇文章的重点放在日志、锁、事务和性能上的原因。我自己每次写复杂的DELETE或UPDATE之前也会多花几分钟把执行计划过一遍这个习惯帮我躲过了不少生产问题也希望它能给你带来同样的帮助。
DML深层解析：增删改查的底层原理、锁与事务避坑指南
开头 很多朋友一提SQL就下意识分成增删改查然后觉得DML不过就是insert、update、delete、select那点语法没啥好学的。但我在实际开发和排障里见过太多因为DML没写利索导致的事故一条update把全表字段洗成同一个值、delete删完发现忘了带条件、批量insert把生产库搞到锁等待飙升、一个看起来没啥毛病的查询因为用了不该用的函数导致索引失效。所以我决定把DML这块掰开揉碎从操作语义、事务边界、性能影响三个角度重新梳理一遍尽量把这几个命令的使用场景、坑点、优化方向都讲到位。这套内容比较适合刚接触数据库开发的同学打基础也适合天天写业务SQL、想回头补一补底层逻辑的工程师参考。1. 动手之前先把DML的账算清楚1.1 哪些操作属于DML哪些不归它管DML全称是Data Manipulation Language中文叫数据操作语言。它负责的是对表中已有数据的增、删、改、查。标准SQL里INSERT、UPDATE、DELETE属于DMLSELECT因为只是读取严格来说属于DQLData Query Language但在MySQL、SQL Server、PostgreSQL这些主流数据库的官方文档里DML章节通常也把SELECT收进去毕竟业务需求中查询占大头开发时也总是把读和写放在一起讨论。DDLCREATE、ALTER、DROP这些管的是表结构DCLGRANT、REVOKE管的是权限DML管的是数据本身这套边界要清楚因为排查问题的时候第一步就是判断你说的慢到底是慢在哪一层。日常工作里最常见的误区是把DML等同于写数据忽略了读取也是DML的重要部分。很多慢查询优化、索引设计其实都是围绕SELECT展开的所以后面我会单独开一节讲查询里的DML思维。另外要注意不同数据库对DML语句的语法和事务行为有一些细节差异。比如SQL Server默认是隐式事务执行完一条语句如果没有显式提交会自动提交而MySQL在InnoDB引擎下autocommit默认是开启的PostgreSQL则是在未提交前只有当前会话可见。写代码的时候如果混着用很容易在事务边界上翻车。1.2 所有DML操作都绕不开的两个底层机制DML不是简单的字符串拼装它的背后有两个核心机制日志机制和锁机制。日志机制上几乎所有主流数据库在处理DML时都会先写日志再改数据页这叫WALWrite-Ahead Logging或者redo log undo log组合。redo log保证崩溃恢复undo log保证回滚。所以一条update改了一万行不光是改一万行数据还要生成一万行undo记录这个开销比你以为的大得多。锁机制则是为了保证并发下数据一致。行锁、间隙锁、表锁不同隔离级别下的加锁范围完全不一样。很多新人问我为什么一条delete明明删一行却把整张表锁住了多半是没走索引导致锁升级成了表锁。理解这两个机制之后很多问题就说得通了为啥批量insert比一行一行insert快因为一个事务只提交一次日志刷盘次数少为啥delete之后表文件没有变小因为undo日志和碎片还在为啥别人在更新数据时你的select会超时因为锁等待超过了阈值。DML的每一个选择都是在和底层机制博弈这也是为什么我劝大家别死记语法先弄清楚语法背后的账。2. INSERT写进去容易写扎实难2.1 一条INSERT语句的完整生命周期INSERT看起来就是往表里塞一行数据其实它要做的事很多解析SQL、检查权限、校验约束、分配存储空间、写入数据页、更新索引、写redo log。如果你插入的列上有默认值、自增id、业务唯一键那还要额外做计算和冲突检测。比如SQL Server里如果列设置了默认值约束INSERT时不指定该列数据库会自动取默认约束的表达式MySQL里同样支持DEFAULT关键字但要小心如果列定义是NOT NULL DEFAULT而你显式传了NULL很多版本会报错或者直接存进NULL这点要看sql_mode。我最常跟同事强调的一点是INSERT语句里尽量列名写得明明白白不要偷懒写INSERT INTO t VALUES(...)。一旦表结构发生调整比如多了个字段不带列名的INSERT立即就错位插入的数据全部错列而且这种错误在测试环境很难发现因为类型不匹配时数据库才会报错类型刚好对上了就直接脏数据入库了。批量插入是另一个大坑。业务上经常需要一次性写入几千几万条数据。有人喜欢写一条INSERT包含多组VALUESMySQL支持也有人喜欢在代码里循环执行单条INSERT。前者明显效率更高因为只发起一次SQL解析和一次事务提交后者每次都要走完整的解析、执行、提交流程性能差异可以到十倍以上。但如果数据量太大一条INSERT的VALUES太多反而会让binlog和redo log体积巨大还可能撑爆undo。经验值一般控制在500到1000条一组比较稳分批提交避免单次事务过大。2.2 默认值、空值和唯一约束的实操陷阱先说默认值。热词里有sql 默认值guid这个场景很典型表主键设计成varchar默认值用NEWID()或者UUID()生成。逻辑没问题但执行INSERT的时候如果不指定主键列数据库会替你把GUID算出来这个过程会消耗随机数生成和字符串处理资源。如果表数据量很大比自增int的写入性能差不少。后来我们优化过一个日志表就是把GUID主键改成自增bigint加上一个单独的UUID业务列写入性能提升了接近一倍。再说空值。NULL和空字符串是两个完全不同的东西。NULL表示未知空字符串表示长度为0的字符串。排序、去重、索引统计时这两者行为都不一样。比如用COUNT(*)不会统计NULL列但会统计空字符串用DISTINCT去重时NULL值在MySQL和SQLite里会被当作同一个值合并但在Oracle里NULL会参与唯一约束判断多行NULL是可以共存的。老实说我见过太多因为空值和NULL没处理好导致的统计数据对不上。建议在建表时就想清楚业务中那个字段是否允许NULL如果允许查询的时候就用IS NULL或IS NOT NULL去判断不要用 NULL 或者 ! NULL 这种写法因为这两个表达式的结果永远是UNKNOWNWHERE里面会被过滤掉查不到任何东西。唯一约束也是INSERT常见问题的来源。比如你写入了一个已存在的唯一键数据库会报Duplicate entry或unique constraint violation。代码里不认真捕获这个异常可能剩下来的一整批数据都回滚。开发接口时对这种唯一冲突要有预期要么提前查一次做幂等判断要么捕获异常后换一种策略比如ON DUPLICATE KEY UPDATE或者INSERT ... ON CONFLICTPostgreSQL的upsert当然用这些语法时要小心它们的并发语义不是简单的不存在就插。3. UPDATE改对数据比改数据本身难十倍3.1 先解决怎么只改我该改的那几行UPDATE最让人紧张的就是WHERE条件。我见过生产事故里最痛的几次全是UPDATE没有WHERE或者WHERE写错导致全表数据被覆盖。这事的根源不是手滑而是对条件命中的语义理解不透。SQL里UPDATE的WHERE和SELECT的WHERE在过滤逻辑上完全一样但在执行时UPDATE会带着锁去扫描匹配行。所以如果你的WHERE不能走索引数据库就可能做全表扫描然后把每一行都锁住改掉哪怕最终只有几行匹配。为此我在团队里立过一个规矩上线前任何UPDATE语句必须EXPLAIN看执行计划必须确认rows预估值不超过预期范围。另外UPDATE一个很大的隐藏问题是更新的复合语义。比如你写UPDATE t SET a a 1 WHERE id 1多数数据库执行时是先把原值取出来加1再写回整个过程在行锁保护下完成不会丢更新。但如果先SELECT出来在应用层加1再UPDATE回去就存在并发覆盖的风险必须引入悲观锁或者乐观锁版本号机制。如果业务里需要根据一张表的结果去更新另一张表各个数据库的写法不一样MySQL是UPDATE t1 JOIN t2 ON ... SET t1.col t2.colSQL Server和PostgreSQL支持UPDATE ... FROM ... WHEREOracle则用MERGE。我不建议在这种跨表更新里写太复杂的关联逻辑因为优化器对关联更新执行计划的掌控不如普通查询那么精准出问题时还难排查。简单粗暴的做法是小数据量先SELECT到应用层再逐条UPDATE大数据量则考虑临时表加JOIN总之别一上来就写一个跨三个表的UPDATE。3.2 数据没变时也要小心锁和日志很多开发同学不知道UPDATE即使SET的值和原值一样数据库依然会执行整行更新流程写入新的数据版本生成redo日志并触发相关索引更新。你要问为什么不能跳过因为数据库无法在不读取原值的情况下判断相同而读取原值本身就可能需要IO加上判断逻辑复杂数据库设计上就不做这种优化。所以在高并发场景下频繁执行值没变但也要set一次的应用会白白增加压力。还有一个容易忽略的是UPDATE和事务的交互。热词里提到sql server windows nt占用内存其实是SQL Server内存管理与其他进程混淆的问题但DML上也有关联。UPDATE大量数据时SQL Server需要维护行版本和TempDB空间如果TempDB不够大或内存压力高事务可能被报错中止。MySQL中长事务会导致undo膨胀而这背后同样是内存和磁盘的博弈。处理大批量UPDATE的正确姿势是:如果数据总量很大别一条语句更新几十万行分段更新更好比如一次更新一万行然后sleep一下提交事务再继续。这样既避免长事务占用锁又减轻undo和日志的压力。但分段更新也有坑如果在循环里用了相同的WHERE条件而没有把已更新数据排除就会反复扫描甚至死循环。经验写法是加上一个状态字段或者按主键范围切片。4. DELETE与数据清理的正确姿势4.1 DELETE的语法简单删除的代价不简单DELETE是标准的DML操作它按行删除数据每条被删的记录在事务回滚时都能恢复所以每删一行都要写undo或日志。如果你执行DELETE FROM t WHERE create_time 2024-01-01假设命中100万行这一条语句会产生海量undo执行时间会远大于你的预期甚至导致主库拉垮。TRUNCATE为什么快因为它不去逐行记undo而是直接把表的数据页释放掉属于DDL范畴所以速度飞快但代价是不能按条件删也不能回滚到行级。所以清理数据时要想清楚需要事务保护吗需要保留一部分条件吗需要之后还能恢复吗如果只是把整张表清空TRUNCATE更合适如果是要删一个月前的过期数据那必须用DELETE并且分批跑。分批删除的做法我用过很多次。一个简单模板是这样每次先查出一个主键范围或符合条件的TOP N条然后DELETE WHERE主键 IN (这些id)提交再循环。注意这里不要用LIMIT加OFFSET去翻页删除因为OFFSET越翻越慢还会因为数据变化出现重复或遗漏。稳妥的做法是基于主键做游标比如每次删除WHERE id last_id ORDER BY id LIMIT 500然后不断更新last_id直到删除完。这个方案既能控制单次事务大小又能让索引扫描保持稳定。4.2 大表删除后的空间管理问题DELETE完成之后表的数据文件不会立刻变小。数据库只是把那些数据页标记为可复用但物理文件还占着空间。这在MySQL InnoDB里特别明显很多人删了几GB数据却看到ibd文件大小不变以为删除没生效。其实你需要执行OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB来重建表回收空间。SQL Server里可以用DBCC SHRINKDATABASE或DBCC SHRINKFILE但收缩操作本身很耗资源最好安排在维护窗口。PostgreSQL里则用VACUUM FULL解决注意它也会锁表。所以有效姿势是如果业务确定要大量清理数据干脆在低峰期进行重建表清数据一步到位而不是天天小批量删但从不整理碎片。DELETE还有另一个隐藏点外键约束。如果删除父表的一行而子表有外键引用数据库会去检查子表并可能加锁导致删除速度骤降。这种情况我建议先处理好子表数据或者临时禁用外键检查生产环境慎用必须有完整方案。在ORM层面很多框架的级联删除其实是先在应用层查出记录再逐条删关联表数据这种效率更低但至少可控。你要是敢直接在数据库层开级联一旦误删关系链全断恢复极其痛苦。5. SELECT里的DML思维去重、空值与窗口函数5.1 去重别只会DISTINCT防重才是核心热词里关于sql语句去重的搜索特别多可见这是个高频需求。DISTINCT很简单它会对查询结果集去重但它的代价是把结果集排序或者哈希去重数据量一大就容易变成慢查询。更常见的问题是业务里要防止重复插入仅仅靠SELECT DISTINCT是不够的。比如订单系统为了防止重复提交很多团队会选择先SELECT判断再INSERT但并发场景下两条请求同时查到不存在就都执行INSERT了。真正可靠的是数据库唯一约束或者在INSERT时使用INSERT ... ON DUPLICATE KEY UPDATEMySQL或ON CONFLICTPostgreSQL。不过DISTINCT还是有它的用武之地。比如快速看一个状态字段到底有几种值或者统计分布时GROUP BY比DISTINCT更灵活。在我的经验里DISTINCT适合小结果集、列少的场景如果去重列很多比如十几列结果集本身不明确DISTINCT的性能通常不会好。一个优化思路是先用聚合查询把可能重复的业务键收敛到一行再去关联获取明细数据。这样网络传输和排序量都会小得多。5.2 NULL处理与窗口函数的实战细节NULL在前面INSERT那里说过一部分但SELECT中空值处理更容易让人头大。COALESCE是最常用的空值处理函数它接受多个参数返回第一个非NULL值。用COALESCE(column, 默认值)可以在结果集中把NULL替换掉。但这个替换只影响输出不会改变存储值。如果你要把NULL和空字符串统一转换建议用CASE WHEN column IS NULL OR column THEN N/A ELSE column END。需要注意的是索引对NULL的利用在某些数据库里会打折MySQL的普通索引不存储NULL值PostgreSQL可以部分索引专门处理NULLOracle中NULL不被B树索引包含。所以在WHERE里判断IS NULL可能会走全表扫描这就是为什么很多查空值的SQL变成慢查询。窗口函数是另一个和DML关系密切的内容。热词里也出现了sql窗口函数因为它确实太常用了。ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time DESC)可以给每组数据标号然后用WHERE rn 1取出每个用户的最新记录。这种写法在数据去重、取TopN、做分页排序中的效果远好于GROUP BY关联子查询。窗口函数还有一个容易被忽略的点它是在查询结果集已经生成之后才计算的所以它不会减少IO但它可以把很多本来要写子查询或者自连接的复杂逻辑变成简单的表达式。如果窗口函数需要的排序量太大也会成为慢SQL解决办法是建立匹配PARTITION BY和ORDER BY的联合索引。6. DML事务控制与并发问题6.1 几条DML被事务包裹后行为就完全不一样单独执行一条INSERT时autocommit模式下它会自动提交如果执行失败就自动回滚问题不大。但如果一个业务操作需要先INSERT主表再INSERT子表再UPDATE一个统计字段这三条DML就必须放在同一个事务里否则中途挂了数据就不一致。事务的ACID原则虽然老生常谈但落到DML上要注意原子性由undo保证隔离性由锁或MVCC保证持久性由redo保证。你只有在事务里才能利用这些机制去组合DML而不是每天在应用里拼接SQL。事务还有个特点它会影响锁的释放时机。一条UPDATE只有在事务提交后锁才会释放。如果你在一个显式事务里先SELECT ... FOR UPDATE锁了一行然后去做网络请求或者等待用户输入这期间其他事务都会被卡住这属于典型的长事务拖垮并发。我之前排查过一个线上问题某接口频繁超时后来发现是事务里在UPDATE之后调了一个第三方HTTP接口整个事务要等HTTP返回才提交导致行锁被持有好几秒后面的请求全部排队。解决思路很简单把网络请求挪到事务外面或者先提交事务后再调用外部服务。6.2 隔离级别和行锁间隙锁DML冲突的根源数据库事务隔离级别有四级READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。默认情况下MySQL InnoDB是REPEATABLE READSQL Server是READ COMMITTEDPostgreSQL也是READ COMMITTED。不同的隔离级别影响DML的可见性和锁范围。比如REPEATABLE READ下一条UPDATE修改了某条件范围的数据其他事务再往这个范围插入新数据可能被间隙锁挡住这就是幻读的一部分防护。热词里并行sql优化可以理解为通过加提示词比如SQL Server里的WITH (NOLOCK)或者优化器hint降低锁竞争但NOLOCK会导致脏读这种优化方式生产环境慎用。如果你发现两个事务互相等待对方持有的锁就是死锁。数据库的死锁检测器会选择一个牺牲者回滚应用层经常报deadlock found when trying to get lock; try restarting transaction。避免死锁的最好办法是让所有事务按相同的顺序访问表或行。比如事务A先更新订单再更新用户事务B先更新用户再更新订单两边顺序相反就容易死锁。改成一致的顺序后死锁概率大幅降低。另外单条UPDATE内部有多个索引更新时也可能因为索引顺序导致死锁这时候可以用强制索引或者调整更新逻辑去规避。7. DML性能优化与避坑实战7.1 慢SQL排查先看执行计划再谈优化热词里慢sql优化是搜索大头。DML中SELECT、UPDATE、DELETE都可能变成慢SQL。拿到慢SQL第一步不是改写而是EXPLAIN。MySQL的EXPLAIN会显示type字段从system、const、eq_ref、ref、range、index到ALL访问效率依次递减。理想情况下至少要到range最好能到ref或const。如果看到ALL说明在走全表扫描。这时要检查WHERE条件里的列是否有索引以及函数、隐式转换、前导通配符是否导致索引失效。比如WHERE create_time NOW() - INTERVAL 1 DAY这种写法就可能没问题但WHERE DATE(create_time) CURDATE()会让索引无法使用因为列套了函数。正确做法是改成范围条件create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。UPDATE的慢SQL还有一种常见原因更新大量行时SQL Server里可能因为TOP不支持下推MySQL里可能因为使用了JOIN但执行计划把驱动表选错。解决办法是分段更新或用EXISTS代替IN有时候改写一下就能把扫描量降下来。DELETE同理大数据删除务必分段。我还见过一个SQL语句去重查询的慢SQL原本写法是SELECT DISTINCT a.* FROM a JOIN b ON a.idb.aid结果a表有几十万行b表几百万行DISTINCT要对整个结果集去重。优化后改为EXISTS子查询提前过滤性能翻了几倍。7.2 DML实操中遇到的高频Error与速查这里整理一个我实际工作中收集的常见报错速查表原因和解决方式都写清楚了省得你每次去翻文档。报错信息常见原因处理建议Duplicate entry x for key idx唯一键冲突改用INSERT ON DUPLICATE KEY UPDATE或提前做幂等校验Data truncation: Data too long for column字符串超过列长度检查字段字符集和业务截断逻辑必要时改列类型为TEXT或加大varchar长度Column xxx cannot be nullNOT NULL约束被违反插入前调用COALESCE给默认值或检查业务字段是否漏传Out of range value for column数值超出范围检查应用层类型转换必要时改用DECIMAL/BIGINTDeadlock found when trying to get lock并发事务加锁顺序不一致统一更新顺序减少事务持锁时间Lock wait timeout exceeded行锁被长事务持有定位长事务并优化或把SQL改成分批次小事务SQLSTATE[HY000]: General error: 1267 Illegal mix of collations关联字段字符集不同在JOIN条件中显式转collation如COLLATE utf8mb4_general_ciORA-01704: string literal too longOracle里字符串字面量超过4000字符用CLOB参数绑定或把大文本拆分成多段写入Solaris/Windows连接SQL Server报登录失败登录方式或网络配置问题检查混合认证模式、端口和防火墙策略表中这些错误里锁等待超时在DML场景中出现率最高。一次UPDATE执行了超过innodb_lock_wait_timeout默认50秒还没拿到锁就报这个错。线上处理办法是先查询information_schema.innodb_trx找到阻塞源杀掉它或者等待它提交。更主动的办法是优化事务里的DML让锁持有时间尽量短比如先处理大批量操作的前置查询再快速提交事务。7.3 让DML更省心的几个编程习惯最后分享几个我写业务代码时的DML习惯。第一所有写操作必须显式列出字段名杜绝SELECT *或INSERT无列名。第二写UPDATE或DELETE前先用SELECT确认命中行数可以在测试环境开启事务再执行确认无误后提交。第三批量操作使用分批提交每批控制在500到2000行中间记录过程日志和进度。第四合理利用数据库提供的upsert语法减少应用层的先查再插逻辑从根上避免并发重复数据。第五对核心业务表加字段变更或触发器之前先评估对INSERT/UPDATE既有SQL的影响尤其是默认值约束、生成列和索引的变化会直接影响DML执行路径。在真实项目里DML的坑通常不在语法而在工程判断。你光知道INSERT怎样写能插入数据是不够的还得知道插入时锁怎么加、日志怎么记、失败怎么处理。把这七个章节里的概念串起来看其实核心就一句话每一次DML操作都要清楚它会在数据库内部引发什么样的连锁反应。这也是我把这篇文章的重点放在日志、锁、事务和性能上的原因。我自己每次写复杂的DELETE或UPDATE之前也会多花几分钟把执行计划过一遍这个习惯帮我躲过了不少生产问题也希望它能给你带来同样的帮助。
