mysql 读取并删除文件_MySql优化—删除操作

一、delete后的表文件大小

在 InnoDB 中，delete 操作，并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记，标记为删除，因此你使用 delete 删除表中的数据，表文件在磁盘上所占空间不会变小。

1、命令查看表文件所占空间大小

use information_schema;

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';

2、删除表中的所有数据，再重新查看表文件大小

delete from t;

use information_schema;

select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';

3、结果发现表数据被清空后，表所占空间大小并没有变化，delete 操作并没有真正删除数据，表空间并没有被释放。

4、被删除的记录行，只是被标记删除，是可以被复用的，下次有符合条件的记录可以直接插入到这个被标记的位置。

比如我们在 id 为 300-600 之间的记录中删除一条 id=500 的记录，这条记录就会被标记为删除，等下一次如果有一条 id=400 的记录要插入进来，那么就可以复用 id=500 被标记删除的位置，这种情况叫行记录复用。

还有一种情况是数据页复用，就是指整个数据页都被标记删除了，于是这整个数据页都可以被复用了，和行记录复用不同的是，数据页复用对要插入的数据几乎没有条件限制。

还以上面那个插入为例，假如要插入的记录是 id=1000，那么就不能复用 id=500 这个位置了，但如果有一整个数据页可复用的话，那么无论 id 值为多少都可以被复用在这个页上。

二、产生的问题

这些被标记删除的记录，其实就是一个空洞，有种占着茅坑不拉屎的感觉，浪费空间不说，还会影响查询效率。

mysql 在底层是以数据页为单位来存储和读取数据的，每次向磁盘读一次数据就是读一个数据页，然而每访问一个数据页就对应一次磁盘 IO 操作，磁盘 IO 相对内存访问速度是相当慢的。

所以，如果一个表上存在大量的数据空洞，原本只需一个数据页就保存的数据，由于被很多空洞占用了空间，不得不需要增加其他的数据页来保存数据，相应的，mysql 在查询相同数据的时候，就不得不增加磁盘 IO 操作，从而影响查询速度。

其实不仅仅是删除操作会造成数据空洞，插入和更新同样也会造成空洞，这里就不细说了，知道就行。

因此，一个数据表在经过大量频繁的增删改之后，难免会产生数据空洞，浪费空间并影响查询效率，通常在生产环境中会直接表现为原本很快的查询会变得越来越慢。

三、如何解决数据空洞问题

optimize table t;或者alter table t engine=InnoDB;

命令的原理就是重建表，就是建立一个临时表 B，然后把表 A(存在数据空洞的表) 中的所有数据查询出来，接着把数据全部重新插入到临时表 B 中，最后再用临时表 B 替换表 A 即可，这就是重建表的过程。

执行上述命令之后，再查看表空间文件大小，发现表空间被释放了，因为删除了所有数据，所以结果应该是定义表结构文件的大小了。