当前位置:网站首页>delete、truncate、drop 有什么区别,误删数据怎么办

delete、truncate、drop 有什么区别,误删数据怎么办

2020-11-10 00:31:29 HelloCoder的HaC

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

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

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/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

版权声明
本文为[HelloCoder的HaC]所创,转载请带上原文链接,感谢
https://segmentfault.com/a/1190000037780015