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

46次阅读

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

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

发现是 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.TABLESDATA_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、总结:

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

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

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

正文完
 0