本文转载自微信公众号“微科技”，作者汤哥。转载本文请联系微科公众号。一个千万级别的数据表，删了一半的数据。你觉得B+树索引文件会变小吗？（答案在文章里！！）先做个实验，看看表的大小是如何变化的做个实验，让数据说话1、首先在mysql中创建一张user表。表结构如下：CREATETABLE`user`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`user_name`varchar(128)NOTNULLDEFAULT''COMMENT'username',`age`int(11)NOTNULLCOMMENT'age',`address`varchar(128)COMMENT'address',PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='usertable';2.创建数据。向用户表批量插入1000W条数据@GetMapping("/insert_batch")publicObjectinsertBatch(@RequestParam("batch")intbatch){//设置batchbatch=100000，总共插入1000W条数据for(intj=1;j<=batch;j++){ListuserList=newArrayList<>();for(inti=1;i<=100;i++){Useruser=User.builder().userName("汤姆哥-"+((j-1)*100+i)).age(29).address("上海").build();userList.add(user);}userMapper.insertBatch(userList);}返回“成功”;}BatchInsert，每批100条记录，10万个批次，共1000万条数据。3.检查表文件的大小。索引文件大小约为595M，最后修改时间为02:17。注意：在MySQL8.0之前，表结构存在于后缀为.frm的文件中。独占表空间存储方式使用.ibd文件存储数据和索引，每张表有一个.ibd文件。表数据可以存在于共享表空间中，也可以作为单独的文件存在。由innodb_file_per_table参数控制。MySQL5.6.6之后，默认为ON，这样每一个InnoDB表的数据都存储在一个后缀为.ibd的文件中。4、删除约500万条数据@GetMapping("/delete_batch")publicObjectdeleteBatch(@RequestParam("batch")intbatch){for(intj=1;j<=batch;j++){ListidList=newArrayList<>();for(inti=1;i<=100;i+=2){idList.add((long)((j-1)*100+i));}userMapper.deleteUser(idList);}return"success";}一开始user表有1000W条数据。删了几个，目前还剩550W条左右的数据。5.删除约500万条记录后，再次查看表文件大小。索引文件大小约为595M，最后修改时间为10:34。实验结论：对于千万级的表数据存储，删除大量记录后，表文件大小不会增加，然后变小。很奇怪，是什么原因造成的？别着急，接下来我们深入分析原因。数据表操作包括增删改查，查询是读操作，不修改文件内容。修改文件内容属于写操作，分为删除、添加、修改三种。接下来，我们开始一一分析。删除数据InnoDB中的数据采用B+树来组织结构。如果对B+树的存储结构不是很清楚，可以看看我之前写的一篇文章，巩固一下基础知识。面试题：一个mysqlB+树可以存储多少条数据？如果表中已经插入了几条记录，构建的B+树的结构如下图所示：删除id=7的记录，InnoDB引擎只是将id=7的记录保存为删除标记，但空间是保留的。如果后面插入id在区间(6,19)的数据，这个空间可以被重用。上图显示新插入了一条id=16的记录。除了记录可以重复使用外，数据页也可以重复使用。当整个页面从B+树中移除后，它可以在任何地方重新使用。例如，在删除第number=5页的所有记录后，该页面被标记为可重用。这时候如果需要用新的页面插入一条id=100的记录，那么pagenumber=5就可以复用了。如果相邻的两个页面的利用率很低，数据库会将这两个页面的数据合并到其中一个页面中，而另一个页面将被标记为可重用。当然，如果像我们上面做的实验一样，把整个表的数据全部删除呢？所有的数据页都会被标记为可重用，但空间并没有释放，所以表文件的大小保持不变。总结：delete命令只是将数据页或记录位置标记为可重用，不回收表空间。我们称这种现象为“洞”。如果插入新添加的数据，且数据是随机排序且非主键的，可能会导致数据页分裂。从上图可以看出，如果pagenumber=5的数据页满了，此时插入一条id=15的记录，需要申请一个pagenumber=6的新页来保存数据。分页完成后，在pagenumber=5的最后位置会留下一个可复用的空洞。反之，如果按索引递增的顺序插入数据，则索引紧凑，不会发生数据页拆分。修改数据如果修改了非索引值，不会影响B+树的结构。比如更新id=7的其他字段值，主键id不变。整个B+树没有进行结构调整。但是，如果修改的内容包括索引，则操作步骤是先删除一个旧值，然后再插入一个新值。可能会导致空洞。分析发现增加、修改、删除数据都可能造成表空洞，那么有什么办法可以压缩表空间呢？客官，请继续看新表。我们可以创建一个新的影子表B和原表A的结构一致，然后根据主键id从小到大将数据从A表迁移到B表。由于B表是新表，不会有空洞，数据页的利用率更高。A表数据全部迁移完成后，将A表替换为B表。在MySQL5.5之前，提供一键式命令，快捷方式完成整个过程，转储数据、交换表名、删除旧表。altertabletablenameengine=InnoDB但是，这个解决方案有一个致命的缺陷。在表重建过程中，如果向A表写入新数据，则不会迁移，导致数据丢失。OnlineDDL为了解决以上问题，MySQL5.6版本开始引入OnlineDDL来优化流程。执行步骤：新建一个临时文件，扫描A表主键的所有数据页，生成一棵B+树，存放在临时文件中。在生成临时文件的过程中，如果有对A表的写操作，会记录在日志文件中。生成临时文件后，重放日志文件并将操作应用于临时文件。用临时文件替换表A的数据文件。删除旧表A数据文件。新表与新表最大的区别在于增加了日志文件记录和回放功能。迁移过程中允许对A表进行增删改查。
