关于后端:deletetruncatedrop-有什么区别误删数据怎么办

5次阅读

共计 2627 个字符，预计需要花费 7 分钟才能阅读完成。

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

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

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

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

语法：

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。还会触发触发器。

语法：

TRUNCATE TABLE t_table

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

2）、不会记录日志，比拟快。

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

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

语法：

DROP TABLE t_table

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

2）、间接删掉表无关的所有（数据 / 构造 / 束缚…）， 并将表所占用的空间全副开释 。

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

新建一个表，插入 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 一步到位，间接初始化这个表。

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 的语句，相当于跳过了这个删除语句。

所以，备份很重要！

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