本文转载自微信公众号《Linux开发那些事》，LinuxThings。转载本文请联系Linux开发那些事儿公众号。不知道大家有没有遇到过这样的情况。线上业务对MySQL表进行增删改查操作。随着时间的推移，表中的数据越来越多，表数据文件越来越大，数据库占用的空间自然也就逐渐增大。为了减少表数据文件在磁盘上的占用空间，我们使用delete命令删除了最大业务表中一半的旧数据。删除后，磁盘上的表数据文件并没有缩小。即使删除了整个表中的数据，文件也不会变小。为什么是这样？本文将对以上问题进行详细分析，并给出正确的表空间回收方式预先说明目前MySQL数据库大多使用InnoDB引擎，所以如无特殊说明，本文示例均基于InnoDB引擎.在MySQL的配置中，有一个配置项叫innodb_file_per_table。设置为1后，每张表的数据会单独存放在一个后缀为.ibd的文件中，如果不启用innodb_file_per_table，则表的数据存放在系统的共享表空间中，所以即使表删除后，共享表空间不会释放这部分空间。因此，一般情况下，innodb_file_per_table选项设置为1，同时为了直观的看到表数据文件的大小变化，本文中的例子都是根据启用该选项所解释的问题进行复现一个新表ta。表的结构如下mysql>showcreatetableta\G*****************************1.row***************************表：taCreateTable：创建表`ta`（`id`int（11）NOTNULL，`ia`int（11）NOTNULL，PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf81rowinset(0.00sec)使用如下存储过程，批量插入数据分隔符//createproceduremultisert(inbegint,incnntint)begindeclareicntintdefault0;declaretmpintdefault0;whileicntcallmultisert(0,100000);mysql>selectcount(*)fromta;+-----------+|count(*)|+----------+|100000|+----------+1rowinset(0.02sec)查看taonthedisk表[root@ecs-centos-7test]#cd/var/lib/mysql/test/[root@ecs-centos-7test]#ls-lta的数据文件ta.ibd的大小。ibd-rw-r-----1mysqlmysql11534336January323:14ta.ibd从上面的结果我们可以知道，在ta表中插入10万条数据后，ta.ibd的大小为11534336字节（约11M）。现在我们用delete命令删除一半表数据（510000行记录）mysql>deletefromtawhereidbetween1and50000;QueryOK,10000rowsaffected(0.03sec)mysql>selectcount(*)fromta;+------------+|count(*)|+----------+|50000|+------------+1rowinset(0.02sec)删除操作完成后，查看ta的大小.ibd再次在磁盘上[root@ecs-centos-7test]#cd/var/lib/mysql/test/[root@ecs-centos-7test]#ls-lta.ibd-rw-r-----1mysqlmysql11534336January323:14ta.ibd从上面的结果我们可以知道，ta表已经删除了一半的Son，也就是5万行数据后，ta.ibd的大小为11534336字节（约11M）。也就是说，ta表删除数据前后，磁盘上的表数据文件没有缩小。要弄清楚数据文件为什么没有缩小，就知道删除数据的原理了。我们都知道InnoDB中的数据是通过B+树来组织的。B+树的知识请参考理解B+树图解（一）。以上是InnoDBDB的索引图，虚线框出的节点属于Page1数据页，叶子节点存放索引对应的数据。它们按照索引从小到大的顺序组成一个有序数组。如果我们要删除Page1中索引键值为13的数据，也就是上图中红色部分，InnoDB引擎会将索引键值为13的节点标记为已删除，不会回收节点的真实物理空间，只是将其标记为删除节点，后续可以重用。因此，如果删除表记录，磁盘上的数据文件并不会减少。你可能会说上面只是删除了Page1中某个节点的数据。如果删除Page1中的所有节点数据，Page1的空间是否需要回收？答案是，不会回收。当Page1上的所有数据都被删除后，整个数据页会被标记为已删除，整个数据页可以重新使用。因此，在这种情况下，磁盘上面的数据文件仍然不会减少。数据复用涉及数据节点的插入、删除、转移、数据页的合并等操作。具体操作过程请参考理解B+树，这里不再赘述。反复讲解数据节点的复用。在上图（1）中，当索引键值为13的节点被删除时，这个节点被标记为可重用。如果后面插入一条索引键值为7的记录时，当记录在18到18之间时，会重用原来索引键值为13的数据节点，但是如果后面插入的记录的索引键值不在7到18之间18、13的原始索引键值可能不会被重复使用也就是说，数据节点的复用需要索引键值满足一定范围的条件。数据页的复用如图（1）所示。当Page1数据页的所有数据节点都被删除后，Page1的整个页面就可以复用了。是的，当插入的记录需要使用新的page时，可以复用Page1。当相邻数据页的利用率比较低时，可以将它们合并到其中一个数据页中。这时候，又腾出一个数据页，这个腾出的数据页就变成了可复用的。哪些操作会造成数据漏洞？当我们使用delete命令删除一条记录后，InnoDB只是将对应的数据节点标记为已删除和可重用，这些等待使用的空数据节点可以一个一个的看做数据空洞。删除数据时，会造成数据空洞。前面已经解释过了，这里不再赘述。插入数据如果数据是按照索引大小的顺序插入的。此时数据页紧凑，不会有数据空洞。如果从索引中间插入，可能会造成分页。拆分后的页面可能存在数据漏洞。下图是插入的结果。页拆分示例如图所示。拆分之前，叶子页是满的，数据排列的很紧凑。现在插入了一个索引键值为15的数据。插入后Page1页面被拆分为上图中的Page1和Page2。两页拆分后，Page1页有两个洞。这两个数据节点是可重用的，Page2页面刚好满了。更新数据可以看做先删除再插入，也可能造成数据洞，比如：id是表ta的主键，语句updatetasetid=10whereid=1把id=1改成id=10，相当于先删除id=1的记录，再插入id=10的记录，这样的话会造成数据空洞，但是如果是updatetasetia=ia+1where这样的语句id=1即不改变主键值，也不会造成空洞。因此，更新数据可能会造成数据漏洞。总结就是，表的增删改查操作可能会造成数据漏洞，而线上业务会对表进行大量的增删改查操作，出现数据漏洞的可能性比较大。如何缩小表空间由于一张表经过了大量不规则的操作，增删改查后，会产生大量的数据空洞。如果我们创建一个与原表结构相同的有数据空洞的新表，然后将旧表中的数据按索引从小到大的顺序插入到新表中，等待旧表中的数据全部插入后新表，删除旧表，然后将新表重命名为旧表的名称。由于新表中的叶子节点数据是按顺序添加的，页面非常紧凑，页面利用率高。需要的页数比旧表少很多，所以旧表中索引的空洞在新表中不会存在，新表的数据文件占用的磁盘空间自然会缩小，从而达到缩小表空间的目的。下面介绍几种缩小表空间的方法。虽然方法不同，但基本原理都是通过重建表来达到目的。truncatetable表名这个操作等于drop+create，先删除表，然后新建一个同名表，当然在执行truncatetable命令之前，需要保存旧表的数据第一的。命令执行后，将这条数据导入到新表中altertable表名engine=InnoDB这个操作是遍历旧表页的主键索引的数据，为数据页中的记录生成一个B+树结构，并将它们存储在磁盘上的临时文件中。数据页遍历后，将旧表的数据文件替换为临时文件。MySQL5.6以后，这个操作是Online对于DDL，需要注意的是这种方式需要扫描表数据文件，对于大表来说非常耗时。如果是在线服务，需要避开业务高峰期，谨慎操作注意：重建表时，InnoDB不会填满整个表，每页预留10%左右的数据节点用于后续更新。也就是说，重建表后并不是最紧凑的。如果有这样一个过程：重建一次表t，插入一些数据，但是插入的数据占用了部分预留空间。这种情况下，如果再次重建表t，重建后占用的空间可能会比重建前大。综上所述，本文从一个实际问题出发，重现问题，分析问题，解决问题。每一步都进行了详细的分析。限于篇幅，部分细节未能深入，请读者自行了解
