1. 先搞清楚DELETE到底在干什么1.1 执行一次DELETE数据库里到底发生了什么很多开发同学写了几年的DELETE其实对它的底层行为还是一知半解。DELETE在SQL里看起来就一句话但它的执行流程远比我们想象得复杂。以MySQL InnoDB引擎为例执行一次DELETE时数据库并不是真的把数据从磁盘上抹掉而是先在聚簇索引对应的记录上打一个删除标记然后等purge线程后续再去物理清理。这个过程涉及记录结构、索引维护、undo日志、redo日志等多个环节。这里有个很关键的点DELETE打标记的机制意味着执行完DELETE之后表数据文件在短期内并不会立刻变小。所以你经常看到刚删了几百万行数据表空间大小纹丝不动这就是很多人误以为删除没生效的原因。实际上数据还在那里只是被标记为不可见需要等后台线程慢慢清理。在Oracle或SQL Server里情形也类似DELETE本身是写日志重、代价高的操作每条被删的记录都要记录到事务日志中这也是为什么大规模DELETE会比TRUNCATE慢非常多的根本原因。1.2 DELETE、TRUNCATE、DROP三兄弟别再搞混很多面试和实际工作场景里大家喜欢把DELETE、TRUNCATE、DROP放在一起对比。但真正动手的时候还是经常有人用错。我见过真实的生产事故本来只想清空一张日志表结果用了DROP整张表连同表结构都没了直接从清数据变成了删表。这三者的核心区别大致如下对比项DELETETRUNCATEDROP是否可带WHERE可以不可以不可以是否可回滚事务内可以多数数据库可以多数数据库可以是否重置自增ID不会重置MySQL/部分库会重置整个表消失是否逐条记录日志逐条记录按页记录日志量极小记录结构变更是否触发DELETE触发器会触发不触发不触发执行速度慢快最快表结构是否保留保留保留不保留提示在MySQL里TRUNCATE被当作DDL处理所以事务中执行TRUNCATE虽然可以回滚取决于版本和配置但它不会触发DELETE触发器也不会逐条记录行级日志锁的粒度是表锁。在Oracle里TRUNCATE也不完全可回滚这些细节和MySQL不太一样建议先确认自己所在数据库的版本行为。从使用场景来说条件删除、精确删除永远选DELETE清空整表数据但保留表结构、重置自增选TRUNCATE连表结构都不要了才轮到DROP出手。原则就一句话能用WHERE就绝不整表操作能做精准定位就绝不模糊处理。2. 精准删除的核心写对WHERE条件2.1 先SELECT再DELETE保命级的操作习惯精准删除的前提是删对数据而删对数据的关键全在WHERE。我自己经历过的数据事故里十有八九是WHERE没写对导致的。最常见的就是漏写了条件本意只想删一条结果一执行把整表清空了。现在很多数据库客户端默认不允许不带WHERE的DELETE执行做得好的工具甚至要求你手动输入行数确认这是很好的保护机制。我的习惯是任何一条DELETE执行前先把它改写成等价的SELECT。比如你想删某个订单状态为非激活的记录那第一件事是跑这条SELECT * FROM orders WHERE status inactive;先看一眼这个查询结果集确认里面确实是要删的数据确认影响的行数符合预期然后再把SELECT * 换成DELETE FROM 去执行。如果是删量超过几千行的操作我还会把SELECT COUNT(*)的结果记录下来作为后续核对基准。这一步看起来慢但长期回报极其可观。站在生产运维的角度每一条你没验证过的WHERE都是一枚潜在的定时炸弹。2.2 复合条件与子查询的选型思路有时候删除条件比较复杂比如删除最近一年内没有任何订单的客户或者删除连续三次登录失败且已冻结的账号。这种条件用单表WHERE很难表达通常要借助子查询或JOIN来确定目标集合。以删除最近半年没有下过单的客户为例常见的做法有两种先看子查询版DELETE FROM customers WHERE customer_id NOT IN ( SELECT DISTINCT customer_id FROM orders WHERE order_date DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH) );再看JOIN版DELETE c FROM customers c LEFT JOIN ( SELECT DISTINCT customer_id FROM orders WHERE order_date DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH) ) active_customers ON c.customer_id active_customers.customer_id WHERE active_customers.customer_id IS NULL;两种本身都能用但实战中我更倾向于JOIN写法。原因是NOT IN有个隐性问题如果子查询的结果集里包含NULL值NOT IN的判断结果会变得非常诡异整条语句可能一行都删不掉或者删掉完全不该删的数据特别容易引发线上事故。JOIN版不会踩这种坑逻辑也更直观一旦执行计划走偏从执行计划里也更容易定位问题。除此之外多条件组合时要注意AND和OR的优先级。我在SQL Server上见过有人写这种删除逻辑DELETE FROM customers WHERE status blocked OR created_at 2020-01-01 AND last_login IS NULL;由于AND优先级高于OR这条语句被解析成了WHERE status blocked OR (created_at 2020-01-01 AND last_login IS NULL);这意味着所有状态是blocked的用户都会被删掉如果原本意图是删除既被阻断又长期未登录的用户语义就完全错了。所以一定要养成用括号包裹逻辑分组的习惯并且自己复述一遍SQL意图去核对。2.3 删除重复数据窗口函数与自连接的实际写法热搜词里反复出现sql语句去重可见去重删除是高频需求。删重复数据最怕的是什么是把所有重复的都删掉而不是保留一条。经典的保留方法有两种。第一种用窗口函数我用得最多DELETE FROM table_name WHERE id NOT IN ( SELECT min_keep_id FROM ( SELECT MIN(id) OVER (PARTITION BY dup_column1, dup_column2) AS min_keep_id FROM table_name ) t );这个写法的思路是按重复字段分组在每个分组内找出ID最小的一行这个ID最小可以是任意保留基准然后删除所有ID不等于最小ID的记录。因为用了窗口函数即使重复了多次也能正确保留一条。第二种用自连接原理类似适合不支持窗口函数的数据库版本DELETE t1 FROM table_name t1 INNER JOIN table_name t2 ON t1.dup_column t2.dup_column AND t1.id t2.id;思路是找到所有存在同列数据且自己的ID比对方大的行这些行就是需要删掉的重复数据。这个写法高效直观但如果重复字段有多个JOIN条件也要相应补全否则会出现误删。需要特别提醒的是执行这类去重前务必先跑一条统计SQL确认每个分组的重复基数确实大于1否则语句扫过全表后可能产生大量无效操作。3. 删除操作的事务与安全边界3.1 DELETE必须养成放进事务的习惯我见过很多人用Navicat或SQL Server Management Studio直接执行DELETE执行完才发现删错了但autocommit已经生效数据再也救不回来。真正稳妥的做法是任何DELETE都放进显式事务里执行先不提交等确认了影响行数和数据状态后再COMMIT。以MySQL为例正确的操作流程是START TRANSACTION; DELETE FROM orders WHERE order_id 10086; -- 在这里检查 SELECT ROW_COUNT(); 或者查看影响行数 -- 如果不放心还可以在相同事务内 SELECT * FROM orders WHERE order_id 10086 查一下 COMMIT; -- 确认无误后提交如果执行后发现问题把COMMIT换成ROLLBACK事务内做的删除操作全部撤销数据恢复到执行前的状态。这一步简单但极其关键相当于给自己留了一道保险门。如果是SQL Server对应使用的是BEGIN TRANSACTION / COMMIT TRANSACTION / ROLLBACK TRANSACTION。Oracle没有autocommit的概念默认就是开启事务的但同时也要求你记得提交否则会发生锁等待甚至阻塞其他会话。3.2 删除前的备份策略别把全部希望压在ROLLBACK上ROLLBACK不是万能药。如果事务已经提交了或者数据库没开启事务日志误删后想恢复往往要付出极大代价。所以更稳妥的思路是像查数据一样把要删的数据先备份出来。我们团队的操作规范是凡是删除行数超过1000的DELETE必须先执行备份。具体做法是在同一事务里先把目标结果集INSERT到备份表然后再执行DELETESTART TRANSACTION; CREATE TABLE orders_bak_20250101 AS SELECT * FROM orders WHERE status inactive; DELETE FROM orders WHERE status inactive; COMMIT;备份表按日期命名放在单独的归档库或者同一库内的_bak表等数据观察几天确认没有问题后再手动清理备份表。这套流程多花几十秒但把数据恢复时间从恐怖的几小时压缩到几秒。如果你用的是MySQL且开启了binlog还可以通过binlog解析和回滚工具实现基于时间点的恢复。不过这种操作复杂度高、风险大不到万不得已不要依赖它。把备份做在前头远远好过事后去挖日志抢救。注意MySQL的TRUNCATE不逐条记日志即使开启了binlog也无法用行级数据闪回误用TRUNCATE清数据后基本只能靠备份恢复。所以越是大批量清数越要谨慎确认操作类型。3.3 删除前的影响面评估从COUNT到ROW_COUNT的联动验证删除前还有一个实用流程就是把SQL改写为SELECT统计行数和事务内ROW_COUNT验证联动起来。我先查一次COUNTSELECT COUNT(*) FROM orders WHERE status inactive;记录下这个数字比如是5000。然后事务内执行DELETE再查START TRANSACTION; DELETE FROM orders WHERE status inactive; SELECT ROW_COUNT(); ROLLBACK;假如ROW_COUNT返回的也是5000说明删除范围和预期完全一致可以直接提交。如果数字对不上就要停下来排查WHERE条件是否被别有用心地截断、过滤器是否有隐式转换、是否有触发器额外删了关联数据等等。这套COUNT先行、ROW_COUNT比对、事务保护、确认后提交的流程是我这几年写DELETE的最强保障。4. 大面积删除的性能与锁问题4.1 DELETE之后发生产生性能问题的根源很多运维同学都遇到过这种情况一条DELETE语句跑了几十分钟把库搞死了主从延迟飙升几百秒业务抖成多米诺骨牌。根本原因不只是数据量大而是DELETE的每一个动作都比想象中重每一行删除都要记录undo、维护索引、可能触发外键检查和级联更新。数据量大时这些都是和时间赛跑的负担。还有一个连锁反应大量DELETE长时间持有行级锁导致其他UPDATE、INSERT操作积压排队。在MySQL InnoDB里删除行会持有记录锁直到事务结束才释放。如果你在一个事务里删除一千万行数据那么在事务提交之前这一千万行对应的记录锁全部被你的事务握着其他会话想操作这些行只能干等着这就是锁等待风暴的来源。SQL Server和Oracle虽然实现细节不同但大事务长期持锁同样会拖垮并发性能。4.2 分批删除的原理与具体写法解决大规模删除性能问题的通用思路叫做分批删除。也就是把一个大事务拆成多个小事务每次只删除有限行数提交后释放锁再继续下一批。这样做的好处有三点锁持有时间短、undo日志不会爆炸、主从复制延迟可控。以MySQL为例最简单的分批删除写法-- 循环分批删除每批1000行 DELETE FROM big_table WHERE create_time 2020-01-01 AND id IN ( SELECT temp.id FROM ( SELECT id FROM big_table WHERE create_time 2020-01-01 LIMIT 1000 ) temp );利用LIMIT子查询先圈定1000个待删ID然后根据这些ID删除对应记录。每执行完一次提交一个事务然后循环直到删除行数不足1000为止。之所以需要嵌套一层临时表子查询是因为MySQL的UPDATE和DELETE语句不允许直接对子查询操作进行LIMIT必须再多套一层以避免You cant specify target table for update的报错。SQL Server的写法略有不同可以用TOPDELETE TOP (1000) FROM big_table WHERE create_time 2020-01-01;这条语句会删除至多1000行符合条件的记录逐批执行效果类似。分批删除要注意两点一是每次批次的大小要结合表锁和业务低峰期调整并不是越大越好我一般从500到5000之间取经验值二是循环逻辑要写成可控脚本存储过程或后台任务并记录每次删除的行数方便中途失败后从断点续跑。4.3 索引结构如何影响DELETE的速度DELETE的速度和WHERE命中行数的定位成本强相关。如果WHERE条件没有走索引数据库就要做全表扫描逐行判断是否匹配在海量数据下表扫描本身就是灾难。所以删除前一定要看执行计划确保WHERE条件命中了合适的索引。比如频繁按create_time删除历史数据就应该有复合索引或者单列索引覆盖create_time。如果是按status create_time的组合条件删除复合索引(stat