前几天有个朋友刚从腾讯二面出来垂头丧气地跟我说面试官问了一个看似基础的问题一张千万级大表要快速删除大量数据你会怎么做他当时下意识回答“分批delete”结果被一连串追问问到卡壳批次多大为什么这个批次不会锁死删除期间主从延迟怎么办binlog会放大多少删完磁盘空间为什么不释放最后他几乎是红着脸结束那一轮的。其实这道题在数据库面试里属于典型的高频综合题考察的绝不只是delete语法而是你对锁、日志、索引、主从架构和业务建模的整体理解。我今天不打算给你背一篇“标准答案”而是把这道题拆碎了讲清楚千万级大表删除慢的根因是什么几种主流方案各自的适用边界在哪里以及面试现场怎么回答才能让面试官觉得你是真做过、不是背过。1. 面试官抛出“千万级大表快速删除”时他真正在考察什么1.1 一个delete背后有多少隐藏成本很多人对delete的理解停留在“从表里把不要的行删掉”但服务端收到一条delete语句时实际干的事情远比想象中多。以MySQL InnoDB为例一条普通delete的执行路径大概包括根据where条件定位到目标行对应的B树索引位置对涉及的行记录加锁把删除前的数据写入undo log以便事务回滚在change buffer没有命中的情况下维护二级索引向redo log写入物理变更如果开启了binlog还会把这条delete语句或对应的row格式事件写入binlog最后对于标记删除的行InnoDB并不是物理上立刻清除磁盘空间而是先打上删除标记等后台purge线程慢慢清理。这个链条里任何一个环节被放大都会让性能肉眼可见地恶化。千万级大表之所以“删不动”通常不是单条语句本身有多重而是大量行同时涌入这几个环节导致磁盘IO、锁竞争和日志落盘被打满。面试官问这道题本质上不是想知道你知不知道delete怎么写而是想看你能不能识别出这个完整链条里的瓶颈点。1.2 回答问题的三维坐标系锁、日志、索引维护我在复盘这道题时习惯把考察点归纳成三个维度面试中只要围绕这三个维度展开基本不会跑偏。第一是锁。删除操作会触发行锁、间隙锁、甚至表锁某些条件下锁的粒度直接影响并发能力。一条delete删100万行意味着这100万行可能要持有锁直到事务结束期间所有对该范围的读写都会被堵住。第二是日志。InnoDB的redo log和MySQL的binlog都会记录删除操作如果binlog格式是ROW每条删除记录都会变成“删除前镜像删除后镜像”的完整事件一次删除100万行binlog可能膨胀到几个GB。这在主从复制架构下会直接拉高从库的回放压力。第三是索引维护。删除一行数据不只是删掉聚簇索引里的记录所有相关的二级索引也要同步更新。如果表上有五六个索引删除100万行实际上要更新的索引条目可能是几百万甚至上千万这是很多人容易忽略的隐藏代价。从这三个维度出发任何删除方案好不好都可以用一句话评价它到底把压力转移到了哪里又避免了哪些环节的瓶颈。1.3 先别急着说方案反问需求才是加分项回到我朋友的那个回答“分批delete”本身没错但问题在于他说得太急没有先问清楚删除场景。同样是“删除大量数据”实际情况可能截然不同是要删除一张表里90%的历史数据只保留最近三个月还是要删除一张表里10%的异常数据这些数据删除是一次性任务还是每天都要执行被删除的数据跟正在写入的数据是否存在时间上的重叠这些需求的差异决定了方案的选择。比如只保留少量数据时新表替换法可能远比分批delete高效如果数据本身就是按时间分区的日增量表直接删分区可能才是最优解如果是一次性清理分批delete加上合理的停机窗口也能接受。所以一个好的回答第一步永远是确认需求删除的数据占多少比例、删除条件是什么、线上是否允许停写、是否有维护窗口。面试官抛出这个场景时通常给你留了反问的空间你能把这个空间用起来本身就在展示你处理过真实问题而不是一个只会背方案的应届生。2. 直接delete为什么会慢到让人怀疑人生2.1 一条delete语句在InnoDB里的完整代价当你真的在千万级大表上执行一条不带limit的delete时最直观的感受就是语句跑起来之后要么会话一直卡在执行中要么响应时间随数据量直线上升严重时还可能把整个实例的IO打满。我见过一个生产案例某表数据量在1200万行左右一条delete条件能命中其中400万行DBA直接在凌晨执行单条delete结果跑了接近40分钟期间主库的QPS从8000掉到几百其他业务接口全部超时。后来查慢日志发现这条语句的扫描行数是400万实际删除行数也是400万但Rows_examined和Rows_affected看起来接近为什么还会这么慢根本原因在于一条delete是一个大事务它持有的锁不会在删完一行后释放而是在整个事务提交时统一释放。这意味着执行过程中所有被扫描到的行都处于锁定状态任何针对这些行的更新、删除、甚至某些查询走一致性读但遇到锁等待都会被阻塞。同时400万行产生的undo log、redo log、binlog会把这期间的磁盘IO完全占满主从同步延迟也会飙到几百秒。所以千万不要在生产环境执行这种“全量条件删除”的语句这基本等于给自己找事故。2.2 删除条件命中大小与索引选择的关系另一个被反复问到的细节是delete语句的where条件到底能不能走索引。如果where条件没有用到索引MySQL只能全表扫描这意味着即使你只想删几行也要把整张千万级大表扫一遍。这个开销在数据量上来之后是不可接受的更别提扫描过程中还会对扫描过的记录加锁某些隔离级别下会有间隙锁直接影响在线业务。此外删除条件是否“均匀”也很重要。假设你按某个普通索引列筛选数据但该列的可选择性很低比如status字段只有0和1两个值优化器可能认为走索引代价远高于全表扫描于是选择全表扫。一旦走到这一步delete就变成了一个重量级操作扫描、加锁、日志全部都会被打满。这也是为什么在大表删除场景中我非常强调按主键范围删除主键索引是聚簇索引数据物理存储顺序跟主键顺序一致按主键范围定位目标行时扫描路径最短锁定的范围最可控也最容易实现时间片式的分批处理。2.3 那些“删着删着就锁死”的典型案例说一个我处理过的线上问题表结构和业务大概是这样的订单流水表数据量800万业务方要删除半年前已完结的订单删除条件是两个普通索引列的联合筛选。开发同学写了一条delete执行了大概30秒后监控报警“锁等待超时”。当时现场状态是这条delete持有了大量行锁和间隙锁随后所有需要插入新订单的事务全部陷入等待最终触发了innodb_lock_wait_timeout造成大面积业务报错。恢复过程也费劲因为要等那笔大事务回滚回滚又要重新处理undo日志耗时比正常执行还长。这个案例给我最大的教训是批量删除必须自己控制“删除节奏”把一个大事务拆成无数个小事务哪怕总体耗时变长也不能让单个delete覆盖过多行。所谓“快速删除”在线上的真实含义不是“单条语句秒回”而是“整个删除过程不拖垮业务”。3. 分批删除的完整设计方案与执行细节3.1 批次大小、主键范围和sleep的配合逻辑分批删除的核心思想很简单化整为零。但落到实操时批次大小、主键范围、sleep时间这三个参数怎么配才是真正体现经验的地方。批次大小直接决定单事务持有的锁数量。我一般建议单批次删除的行数控制在1000到5000之间具体取决于表上的索引数量、行宽度以及当前实例的IO能力。如果表上有很多二级索引批次要偏小因为二级索引维护的开销会放大实际工作量如果表比较瘦字段少可以适当放大。主键范围的选择有一个容易踩的坑不要用“limit 偏移量”这种翻页式删除。每次limit 5000然后不断翻页前面的数据被删掉后MySQL仍然要扫描并跳过已经被删除的数据越到后面越慢而且相同条件下可能会重复扫描大量行。正确做法是先定位一批主键id区间删除这个区间里的目标数据然后往前走保证每个id范围只被处理一次。sleep时间的设置则需要观察删除期间的主从延迟。删除会产生binlog从库需要回放这些日志如果一批删除特别猛主库执行很快从库的SQL线程却跟不上延迟就会持续累加。常规做法是每删完一批sleep几十到几百毫秒给从库留出同步时间。如果发现延迟超过设定阈值应该让删除任务自动暂停等待直到延迟回落后再继续。3.2 一种可落地的分批删除脚本下面这段Python脚本是我在类似场景下用过的简化版思路核心就是基于主键范围切片每轮删除指定行数并动态检测主从延迟。import pymysql import time def batch_delete_by_range(table_name, min_id, max_id, batch_size2000, sleep_sec0.2): conn pymysql.connect(host主库地址, userapp_user, password***, databasetest_db) cursor conn.cursor() current_start min_id while current_start max_id: current_end current_start batch_size - 1 sql ( DELETE FROM %s WHERE id BETWEEN %%s AND %%s AND create_time 2024-01-01 00:00:00 LIMIT %%s ) % table_name affected cursor.execute(sql, (current_start, current_end, batch_size)) conn.commit() print(fdeleted range {current_start}-{current_end}, affected rows: {affected}) time.sleep(sleep_sec) current_start current_end 1 # 这里可以加入主从延迟检查延迟超过设定值就sleep更久 cursor.close() conn.close() if __name__ __main__: batch_delete_by_range(order_flow, 1, 20_000_000)注意脚本里的where条件同时带了删除过滤字段如果不加这个条件等于把整个主键区间物理删除这跟需求往往不一致。加一个过滤条件后每个区间内真正被删除的行数量不稳定因此上面用LIMIT做兜底避免单次事务过大。LIMIT在DELETE语句里不影响扫描范围但可以控制实际删除行数这也是一个小知识点。3.3 批量删除中的死锁、主从延迟与binlog放大问题分批删除虽然比一次性delete安全但在高并发场景下仍然可能遇到死锁。典型情况是两个删除任务或一个删除任务与业务写事务同时申请多个行锁由于加锁顺序不一致互相等待。规避死锁最有效的方式是让所有删除操作都按照相同的主键顺序执行而不是让每个批次内部随机排序。只要大家加锁顺序一致死锁概率会大幅下降。另外单批次delete时加order by id asc也能让MySQL按明确的物理顺序扫描加锁降低间隙锁互相冲突的可能。至于binlog放大更准确的说法是如果binlog_formatROW删除行产生的binlog事件会包含每一行的完整前镜像所以删除100万行数据binlog量级可能接近表数据体积的1.5到2倍。这个问题无论用单条delete还是分批delete都无法避免只要走DML放大就在所难免。意识到这个放大效应你就知道为什么有些场景下“改一张表”比“删一张表”更优雅。4. 新表替换法用DDL思维代替DML思维4.1 为什么“建新表rename”可以秒级完成删除当你需要删除的表里的大部分数据只保留一小部分时分批delete其实是很低效的。比如一张2000万行的流水表业务只需要保留最近一个月的200万行按主键范围分批删除剩下的1800万行即使每批删2000行也要执行9000次事务再加上sleep和主从延迟的处理耗时可能几个小时。但反过来想如果不删旧数据而是把需要保留的200万行“搬”到一张新表里然后把旧表整体rename成临时表再把新表rename成正式表这个过程中的“删除”操作就变成了“元数据修改”速度是秒级的。这个思路的本质是用DDL思维替代DML思维。数据的清理不是通过逐行删除完成而是通过表的重建与切换完成让旧表作为一个离线对象留给后续处理。4.2 保留少量数据时的迁移步骤新表替换法在MySQL里的常见操作路径大概是这样的-- 1. 创建新表结构包含原表结构和索引 CREATE TABLE order_flow_new LIKE order_flow; -- 2. 将需要保留的数据插入新表这一步是代价最高的部分 INSERT INTO order_flow_new SELECT * FROM order_flow WHERE create_time 2024-01-01 00:00:00; -- 3. 切换表名 RENAME TABLE order_flow TO order_flow_archive_bak, order_flow_new TO order_flow; -- 4. 确认无误后删除备份表 DROP TABLE order_flow_archive_bak;第一步用LIKE建表能直接复制表结构和索引不用手写一遍建表语句。第三步的RENAME TABLE在InnoDB里是原子性的多个表的改名可以在同一个语句里完成业务侧几乎感知不到表切换的中间状态。第二步是唯一可能出问题的环节。INSERT INTO ... SELECT 会读取200万行并写入新表这个过程中源表的数据可能还在被业务写入导致最终迁移数据不完整。所以这个方案在迁移期间需要对业务做短暂只读控制或者通过时间范围二次校验。很多团队的实践是在凌晨低峰期执行同时通过锁表或改写入口规避写流量。4.3 在线无锁工具gh-ost的适用边界与风险新表替换法再进一步就是借助gh-ost这类在线表结构调整工具来操作。gh-ost的思路是创建一个影子表通过binlog将源表的增量变更持续同步到影子表等到数据追平后在某个时间点完成最终切换。用gh-ost来做“大量数据删除”本质上是把“删除大多数行”和“重建表”合并成一次在线DDL操作。你在gh-ost中执行alter语句但语句内容可以是一种“间接删除”的写法比如把需要删除的数据通过重建表过程过滤掉。这样操作不会长时间持有元数据锁对线上业务影响小。但gh-ost不是万能药。它对binlog格式有要求必须使用ROW格式并开启binlog操作过程中需要额外的磁盘空间保存影子表和临时文件而且它的运行机制很吃主库IO如果实例本身负载很高跑gh-ost可能把主库压垮。另外gh-ost重新建表后不会自动帮你处理外键关系如果表上有复杂的触发器或外键约束需要额外评估。所以新表替换法适合“存量数据基本归档、保留比例较小”的场景。如果业务上无法接受停机窗口但又必须清理大量数据gh-ost可以作为备选但上线前一定要做压测和回退演练。5. 分区表把删除数据变成删除文件的操作5.1 drop partition为什么比delete快几个数量级如果一张大表的删除需求是“周期性清除某段时间的历史数据”比如按天生成日志表、按周清理过期订单分区表会是一个非常优雅的选项。分区表的核心思想是数据按照分区键被物理拆分成多个独立的存储段。执行delete删除某个时间段的数据时需要逐行扫描、加锁、写日志但如果是按分区键设计的表直接执行ALTER TABLE tbl DROP PARTITION p202401实际上只是删除该分区对应的整个数据段相当于删除底层物理文件级别的数据。这个速度差异往往是几个数量级的delete删1000万行可能需要几十分钟DROP PARTITION删一个包含1000万行的分区可能只需要几秒到几十秒取决于磁盘IO和分区文件大小。而且DROP PARTITION不会产生大量的undo log和binlog行事件它是DDL操作日志量小得多对主从同步的压力也远小于大量DML。5.2 哪些业务形态适合用分区表支撑快速删除用分区表解决删除问题的前提是业务数据有清晰的时间或其他可枚举的维度并且删除范围能和分区边界对齐。最常见的形态是流水表和日志表。比如支付流水每天一个分区数据保留90天那么删除60天前的数据时直接连续DROP掉对应的几十个分区即可。这种业务模型下“删除数据”这个概念几乎被彻底消灭了剩下的只是定期维护分区。实现方式很简单建表时通过PARTITION BY RANGE定义分区间隔比如CREATE TABLE pay_record ( id BIGINT NOT NULL AUTO_INCREMENT, pay_time DATETIME NOT NULL, user_id BIGINT, amount DECIMAL(10,2), PRIMARY KEY (id, pay_time) ) PARTITION BY RANGE (TO_DAYS(pay_time)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)) );需要注意分区键必须包含在主键或唯一键里这个限制常让很多人踩坑。如果主键只有id想按pay_time分区MySQL会直接报错。解决办法是把分区键并入主键或者使用复合主键但这会带来查询和业务代码的改动需要提前评估。5.3 分区表不是银弹裁剪失效与分区数量失控分区表也有不少争议这个需要客观看待。第一分区裁剪不是万能的。如果查询条件里没有带分区键MySQL需要扫描所有分区性能可能比不分区的普通表更差因为每个分区相当于一张独立的子表扫描总代价不会降低反而增加。很多团队使用分区表后查询变慢就是因为业务SQL没有全部带上分区键。第二分区数量不能失控。如果一张表有几千个分区MySQL在启动、元数据管理、统计信息更新时都可能出现性能退化某些版本甚至会发生“分区表打开文件数过多”的问题。一般建议分区数量控制在几百到一千以下分区分得太碎反而得不偿失。第三运维复杂度增加。分区表在做备份恢复、跨版本升级、字段变更时都需要额外考虑分区策略的兼容性。如果只是为了删除数据而引入分区表但业务查询根本用不到分区裁剪这个设计很可能不划算甚至让后续维护更痛苦。所以分区表适合在“建表之初就按生命周期管理来设计”的场景。如果已经有一张大表没做分区想通过后续改造来实现快速删除代价就很大了这时候不如回到分批删除或新表替换的思路上来。6. 这套面试题的最佳回答路径与复盘心得6.1 从需求澄清到方案选型的决策树现在回过头来如果把这道题回答给面试官我会这么做第一步确认需求。问清楚要删除的数据占表的数据比例大概是多少删除条件是什么是不是主键/时间范围删除是一次性任务还是周期性任务线上能否接受短时间只读或停写第二步根据删除比例和条件给方案。如果删除比例很小比如只删几万行直接按主键范围分批delete批次控制在1000到5000行配合sleep和主从延迟检测。如果删除比例很大比如大部分数据都不要了优先考虑新表替换法创建新表、保留有效数据、rename切换。如果数据天然按时间分区并且删除范围与分区边界一致直接ALTER TABLE ... DROP PARTITION。如果这些方案都因为某些限制走不通再考虑pt-archiver或gh-ost但要说明工具原理和风险。第三步补充风险控制评估binlog放大、主从延迟、磁盘空间、备份策略和回滚方案。能把这些边界条件讲清楚比背十个命令都管用。6.2 面试官追问环节的常见问题与应对我在帮朋友复盘时整理了一些追问很多都是实际项目中会面临的问题。追问一delete和truncate有什么区别truncate能不能用来删除大量数据TRUNCATE是DDL不是DML它直接删除表的所有行并重建表空间不会产生逐行的undo log速度非常快。但TRUNCATE不能加where条件也不能在事务里回滚部分数据库行为和隔离级别不同而且如果是主从复制需要确认binlog格式对TRUNCATE的兼容性。所以TRUNCATE只能用于“清空整张表”的场景。追问二为什么我delete之后表文件大小没有变化这是InnoDB的常见特性delete是标记删除物理空间由后台purge线程回收而且即便行被清理表空间文件的大小不一定会立即收缩。想收缩表空间需要执行OPTIMIZE TABLE而这是一个很重的DDL操作会重建整张表。所以大量删除数据之后要有“磁盘空间不会立刻释放”的预期。追问三如果开启并行删除更快吗并行删除如果调度不好会加剧锁竞争和主从延迟。同一张表的并行删除必须切分互不重叠的主键范围否则会死锁。而且要关注实例的整体IO和CPU水位。多数场景下单线程分批删除比粗暴的并行删除更可控。追问四能不能把binlog调成非ROW格式来减少日志量如果用了row格式确实binlog会大但STATEMENT格式在批量删除上也不安全存在主从数据不一致的风险。生产环境建议坚持ROW格式用其他手段降低删除日志量比如用分区删除、甚至用新表替换法来减少DML行数。6.3 我踩过的那些跟大表删除有关的坑最后分享几个我真实踩过的坑希望你们不用再踩一遍。第一个坑是低峰期执行delete时忽略了从库延迟。当时以为凌晨业务量低就可以放开手删结果主库删得很快从库回放跟不上延迟到了几百秒导致当天早上一部分临时报表数据异常。后来我在所有删除任务里都加了主从延迟检查脚本超过阈值自动暂停。第二个坑是删除过程中遇到大事务回滚。有一次删到中途发现where条件搞错了手动kill了delete语句结果InnoDB回滚一个已经执行到一半的大事务时间比正常执行还要长。从此我学会了先通过SELECT确认要删除的数据范围再决定是调整条件还是继续不要盲目kill。第三个坑是清理完数据后没有回收表空间。删了一千多万行开发反馈数据库磁盘还是快满了一查才发现表空间文件没有收缩。后来在删除需求确认时我会明确告诉业务方删完是否允许执行OPTIMIZE TABLE这会影响磁盘释放方案。其实删除数据的场景在技术栈里随处可见。SQL Server里要删除重复数据只保留一条时通常会用到ROW_NUMBER()加窗口函数来定位重复行本质上也是“先精确圈定要删的记录再分批下手”前端要按id删除localStorage里的数据时也得警惕按索引遍历删除后index错位的问题。思路都是一样的先搞明白你面对的数据范围和组织方式再选最合适的操作粒度。如果下次还有人问你千万级大表怎么快速删除大量数据我希望你第一反应不再是“用delete”而是先想清楚我要删的是哪部分数据保留的是哪部分数据以及我对锁和日志带来的连锁反应有没有预案。能把这些问题想明白不管面试结果如何你已经比大多数候选人强了。