共计 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.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、总结:
delete | truncate | drop | |
---|---|---|---|
安全性 | 低 | 中 | 高 |
空间 | MyISAM 会立即开释磁盘空间,而 InnoDB 不会开释磁盘空间 | 立即开释磁盘空间,不论是 Innodb 和 MyISAM | 立即开释磁盘空间,不论是 Innodb 和 MyISAM |
复原 | 可复原 | 不可复原 | 不可复原 |
速度 | 慢 | 中 | 快 |
事务 | 走事务,触发 trigger | 不走事务,不触发 trigger | 不走事务,不触发 trigger |
简略的说,我手里有一瓶可乐,delete 就像把可乐藏起来了,其实瓶子还在,可乐也还在;truncate 就是把可乐喝完,留下了个瓶子;drop 就是把可乐喝完而且把瓶子也丢掉了。
不说了,大佬又要催我了,我这就去喝可乐,不,执行 truncate
。