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

发现是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;

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

查一下这个空洞大小：

show table status from test like 't_coke_copy2';

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

执行delete语句后，并不会立刻开释空间，此时能够应用 optimize table 指令，但该指令会锁表；或者也能够利用MySQL的主动清理，须要肯定的工夫。

OPTIMIZE TABLE t_coke_copy2;

OPTIMIZE之后，再查一下大小：

变成了初始化大小了。

尽管删除了，然而你的索引还是在原来的根底上持续递增的：

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

5、复原

delete复原比拟容易（前提是开启了binlog）：

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

找到binlog就能够了，参考：

https://blog.csdn.net/huangba...

还能够应用 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、总结：

deletetruncatedrop
安全性低中高
空间MyISAM 会立即开释磁盘空间 ，而InnoDB 不会开释磁盘空间立即开释磁盘空间 ，不论是 Innodb和MyISAM立即开释磁盘空间 ，不论是 Innodb和MyISAM
复原可复原不可复原不可复原
速度慢中快
事务走事务，触发 trigger不走事务，不触发 trigger不走事务，不触发 trigger

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

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