批量删除数据,常见的大坑!!!

·  阅读 6446

行数据批量 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 分别是多少?

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

分类:
后端
标签: