批量删除数据，常见的大坑！！！

行数据批量 delete 时，InnoDB 如何处理自增 ID，是一个潜在的大坑。

整个实验步骤如上图：

第一步：建表，设定自增列；

第二步：指定 id=1 插入，锚定第一行是 id 是 1；

第三步：不指定 id，依赖自增机制，插入 3 行；

画外音：此时 id 应该变为 2，3，4 了？

第四步：delete 删除所有记录；

_画外音：_坑就容易出在这里。

第五步：指定 id=0 插入；

第六步：指定 id=1 插入；

第七步：不指定 id，依赖自增机制，插入 1 行；

请问，此时表中的三行记录，id 分别是多少？

是否符合大家的预期？

今天花 1 分钟，说说使用 truncate 与 delete 批量删除数据的异同。

批量删除数据有三种常见的方法

drop table

当不需要该表时，可以使用该方法。

truncate table

删除所有数据，同时保留表，速度很快。

_画外音：_可以理解为，drop table 然后再 create table。

delete from table

可以删除所有数据，也能保留表，但性能较差。

也可以带 where 条件删除部分数据，灵活性强。

虽然 truncate 和 delete 都能够删除所有数据，且保留表，但他们之间是有明显差异的。

一、

truncate 是 DDL 语句，它不存在所谓的 “事务回滚”；

delete 是 DML 语句，它执行完是可以 rollback 的。

二、

truncate table 返回值是 0；

delete from table 返回值是被删除的行数。

三、

InnoDB 支持一个表一个文件，此时：

truncate 会一次性把表干掉，且不会激活触发器，速度非常快；

delete from table 则会一行一行删除，会激活触发器，速度比较慢。

_画外音：_delete 数据，是要记录日志的，truncate 表不需要记录日志。

四、

当表中有列被其它表作为外键 (foreign key) 时：

truncate 会是失败；

delete 则会成功。

_画外音：_这类数据删除失败很容易定位问题，因为报错提示简单易懂。

五、

当表中有自增列是：

truncate 会使得自增列计数复原；

delete 所有数据后，自增列计数并不会从头开始。

_画外音：_因此，delete 所有数据后，自增列计数的这个行为，往往不是用户想要的，所以是一个潜在坑。

这一分钟，有收获吗？

请根据自己的业务场景，选择删除数据的方式哟。

架构师之路 - 分享技术思路

相关文章：

《缓冲池 (buffer pool)》

《写缓冲 (change buffer)》

《日志缓冲 (log buffer)》

 

作业题：

开头的实验，最后表中的三行记录，id 分别是多少？

_画外音：_你以为文章告诉了你原理，你就能答对么。