你真的知道怎么清空表数据吗?
参数innodb_file_per_tabel
Mysql官方说明了innodb_file_per_tabel
大致意思是,每个表都可以配置自己独立的数据存储文件,在表的目录下以,表名.ibd结尾的就是。
可以通过innodb_file_per_tabel配置:
1.OFF:表示存放在系统共享表中;
2.ON:表示单独存放在以 表名.ibd文件中。
单行数据删除
以这张图为例,我要删除row6的记录,innodb只会把row6这条记录标记一下为删除状态,没有进行物理删除,如果我们要在row5和row7直接在新插入一行记录的话,就会复用到之前标记为删除的空间。
上面删除的数据row6,如果我在插入一天不在row6前后范围之间的记录的话,就不能进行复用了。如果插入的是7,就可以复用到row6的记录上。
页数据删除
mysql默认是页存储方式,如果把整个page2的数据页删除掉,那么整个数据页都会得到复用,这时就可以随意插入新值。
如果之间有数据页的利用率很小,就会进行合并,合并到一个数据也上,另外空的数据页标记可复用。
现在知道了如果单靠delete删除的话是没用的,删除的记录只会被标记为可复用,空间大小不会改变,容易造成大量的碎片。
上图中,在page2中插入id为7的一条记录由于page2满了,所以为产生新的page3页,然后把id=7的记录插入,后面的记录转到新的数据页中,这就是页分裂,分裂完成后page2就会产生碎片。
不管怎么样 增删改都会产生碎片,减低利用率。
解决碎片问题
可以创建一张与旧表一样的表结构的数据表,把旧表中的数据复制查询再插入到新表中,这样新表就会更加的紧凑,没有碎片。
可以使用alter table A engine=InnoDB
语句来重建表,它会自动的帮我们完成上面的操作,建临时表,数据copy之类的。
但是这个过程中有新数据写入的话就会造成数据丢失。
Mysql5.6中引入了一个online的操作,允许再对以上的操作时,进行不间断的修改。
引入了 Online 之后,重建表的流程:
- 建立临时文件,扫描表中的所有数据;
- 用数据生成B+树,存到临时文件中;
- 生成临时文件过程中,把所有对表操作都记录到一个日志文件中;
- 临时文件生成完成后,把日志文件的操作加到临时文件中;
- 用临时文件替换表中的数据。
到此结束,有了日志文件的记录操作,再对表进行重建的时候就可以对表进行增删改的操作。
如果已经进行了alter table A engine=InnoDB
重建表的操作后,不建议多次使用:
因为重新收缩的过程中,页会按90%满的比例来重新整理页数据(10%留给UPDATE使用),
未整理之前页已经占用90%以上,收缩之后,文件就反而变大了,也就是说每次都会空出10%的大小。
换个说法就是 每次innodb不会把整个表沾满,而会每个页留了 1/16 给后续的更新用,多次执行重建表就会导致预留的空间越来越大。