delete 会不会锁表_【大佬】delete、truncate、drop 有什么区别，误删数据怎么办

事情是这样的，上周大佬和我说测试服务器的磁盘满了。他找到我叫我帮忙清理一下。

发现是mysql的data目录太大了，于是我进入mysql，发现有好几个百万级别的表太大了，于是我就删了，一顿操作猛如虎，空间占用还是95%。

大佬：“你怎么删的？”
我：“我一个 rm -rf / * ”
大佬：“？？？”
我：“不对，我是用 delete from 删的呀 ”
大佬：“你明天不用来上班了。”

后来为了不被大佬嫌弃，HaC我又补了一下知识，mysql中删除数据原来不止delete，还有 drop 、truncate。

1、delete

语法：

DELETE FROM t_table;

1) 、DELETE语句是DML(Data Manipulation Language, 数据操纵语言)，所以它只会删除数据，不删除表结构。不会减少表或索引所占用的空间。

2)、执行的过程就是每次从表中删除一行，需要记录到日志(binlog，如果开启了binlog)，执行比较慢，可以加上where条件。

养成好习惯：delete加 limit 条件，更快。
假如加limit 1 命中第一条就返回，不用全表扫描再返回。

3)、不会重置索引，插入索引还是从你删除的上一条+1 开始。

4)、MyISAM 会立刻释放磁盘空间 ，而InnoDB 不会释放磁盘空间，数据只是对你不可见。会产生空洞，标记为可复用，下次你执行insert，会覆盖这部分空间。

5)走事务，如果你没有commit，可以使用 rollback 。还会触发触发器。

2、truncate

语法：

TRUNCATE TABLE t_table

1)、DDL(Data Definition Language，数据库定义语言)操作，表和索引所占用的空间会恢复到初始大小。

2)、不会记录日志，比较快。

3)、只能删table，不能回滚。

4)、删除表中所有记录。重置索引位置。

3、drop

语法：

DROP TABLE t_table

1)、DDL(Data Definition Language,数据库定义语言)操作。

2)、直接删掉表有关的一切(数据/结构/约束…)，并将表所占用的空间全部释放。

3)、不能回滚，不会触发触发器。

---

4、空间释放测试

新建一个表，插入10w条数据，

CREATE TABLE `t_coke` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(500) DEFAULT NULL COMMENT '可乐',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='可乐';

# 存储过程，插入 10w数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `coke`()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t_coke values(i, "1984的可口可乐");
    set i=i+1;
  end while;
end

复制多两个表：

CREATE TABLE t_coke_copy1 SELECT * FROM t_coke;
CREATE TABLE t_coke_copy2 SELECT * FROM t_coke;

MySQL的 information_schema.TABLES 的 DATA_LENGTH 记录了表的数据大小，我们可以利用这个计算表的大小。

SELECT
    concat( round( sum( DATA_LENGTH / 1024 / 1024 ), 2 ), 'M' ) AS table_size 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'test'  # 数据库名称
    AND table_name = 't_coke_copy2'; # 表名

执行delete：

DELETE FROM t_coke_copy2;

delete后变大了

哦豁，空间反而大了，这就是这个表产生了空洞。

查一下这个空洞大小：

show table status from test like 't_coke_copy2';

查空洞

这个空洞竟然有10m那么大，一定是之前也delete过数据，没来得及释放。

执行delete语句后，并不会立即释放空间，此时可以使用 optimize table 指令，但该指令会锁表；或者也可以利用MySQL的自动清理，需要一定的时间。

OPTIMIZE TABLE t_coke_copy2;

OPTIMIZE

OPTIMIZE之后，再查一下大小：

变成了初始化大小了。

虽然删除了，但是你的索引还是在原来的基础上继续递增的：

而truncate 一步到位，直接初始化这个表。

5、恢复

delete恢复比较容易(前提是开启了binlog)：

# binlog是否开启
show variables like 'log_bin';
# binlog 的格式
SHOW VARIABLES LIKE 'binlog_format';

找到binlog就可以了，参考：

https://blog.csdn.net/huangbaokang/article/details/89634683

还可以使用 Flashback  工具进行恢复。

假如生产一不小心使用了truncate和drop，怎么恢复数据呢？

准备跑路？

duck不必！如果你的MySQL有全量备份，并且实时备份binlog。

1、假如HaC我在中午12点删除了一个表。昨晚凌晨0点备份了一个库。
2、恢复到0点的库，
3、拿12点到0点之间的binlog进行还原。(找到binlog的drop table、truncate table 的时间节点)
4、再执行drop table、truncate table 的时间节点 到目前时间节点 的binlog的语句，相当于跳过了这个删除语句。

所以，备份很重要！

6、总结：

	delete	truncate	drop
安全性	低	中	高
空间	MyISAM 会立刻释放磁盘空间 ，而InnoDB 不会释放磁盘空间	立刻释放磁盘空间 ，不管是 Innodb和MyISAM	立刻释放磁盘空间 ，不管是 Innodb和MyISAM
恢复	可恢复	不可恢复	不可恢复
速度	慢	中	快
事务	走事务，触发 trigger	不走事务，不触发 trigger	不走事务，不触发 trigger

简单的说，我手里有一瓶可乐，delete就像把可乐藏起来了，其实瓶子还在，可乐也还在；truncate就是把可乐喝完，留下了个瓶子；drop 就是把可乐喝完而且把瓶子也丢掉了。

不说了，大佬又要催我了，我这就去喝可乐，不，执行truncate。

--end--