1。背景在完成一个分表项目后，发现分表数据迁移后，新数据库需要的存储容量远大于原来两张表的大小。做了一些查询和了解后，优化就完成了。回过头来看，我们需要进一步理解为什么会这样。和题主的问题类似的问题也是为什么表数据内容被删除了，但是表的大小没有变化。它的本质是一样的。要回答这些问题，我们需要从mysql的索引模型说起。2.InnoDB的索引模型在MySQL中，索引是在存储引擎层实现的，因此没有统一的索引标准，即不同存储引擎的索引工作方式不同。即使多个存储引擎支持相同类型的索引，它们的底层实现也可能不同。由于InnoDB存储引擎是MySQL数据库中使用最广泛的，所以我们以InnoDB为例分析一下索引模型。在InnoDB中，表按照主键的顺序以索引的形式存储，这种存储方式的表称为索引组织表。InnoDB中使用了B+树索引模型，所以数据存储在B+树中，每个索引对应一棵B+树。假设我们有一个主键是ID的表。表中有一个字段k，在k上有一个索引。建表语句如下CREATETABLE`t`(`id`int(11)NOTNULL,`k`int(11)NOTNULL,`name`varchar(16)DEFAULTNULL,PRIMARYKEY(`id`),KEY`k`(`k`))ENGINE=InnoDBDEFAULTCHARSET=utf8tableR1~R5(ID,k)值为(10,1),(20,2),(30,3),(分别为50,5)和(70,7)，索引id和索引k的B+树示例图如下。根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存放的是整行数据R1~R5，非主键索引的叶子节点的内容就是主键的值。从图中可以看出，基于非主键索引的查询需要多扫描一棵索引树才能找到对应的数据。顺便说一句，我们应该尝试在我们的应用程序中使用主键查询。3、索引维护为了维护索引的顺序，B+树在增删改查数据时需要做必要的维护。假设我们要删除R4记录，InnoDB引擎只会将R4记录标记为已删除。如果要插入ID介于300和600之间的另一条记录，则稍后可以重用此位置。如果一个数据页上的所有记录都被删除，那么整个数据页就可以被重新使用。再者，如果我们使用delete命令删除整张表的数据呢？结果，与该表相关的所有数据页都将被标记为可重用。但是，磁盘文件大小无论如何都不会缩小。这些被标记为可重复使用，但实际未使用的空间是一些“存储孔”。其实不仅是删除数据会产生空洞，插入数据也会产生空洞。以上图为例，如果插入一个ID值为80的新行，只需要在R5记录后插入一条新记录即可。如果新插入的ID值为60，就比较麻烦了，后面的数据需要逻辑移动，为它腾出空间。更糟糕的是，如果R5所在的数据页满了，按照B+树的算法，这时候就需要申请一个新的数据页，然后再把一些数据搬过来。此过程称为页面拆分。在这种情况下，性能自然会受到影响。除了性能，分页操作也会影响数据页的利用率。原来放在一页的数据，现在分成两页，插入一条记录，其实整体空间利用率降低了50%左右。可以看出，由于page2满了，再插入一个ID为60的数据时，必须申请新的pagepage3来保存数据。分页完成后，在第2页的末尾留下了一个空洞（注：实际上可能不止1个记录位置是空洞）。另外，更新索引上的值可以理解为删除一个旧值，插入一个新值。不难理解，这也会造成空洞。因此，大量增删改表可能会出现较大的“数据洞”。因此，我们可以解释为什么表分区后总存储量变大了。因为分表后，需要将数据从旧库完全同步到新库。数据同步平台启动多个线程进行同步，每个分表的插入不是按递增顺序插入的。因此，会产生大量的“数据洞”，从而产生更大的存储空间。如果能把这些洞去掉，就可以达到缩小表空间的目的。而重建表可以达到这个目的。4.重建表如果我们手动重建一个表，我们可以新建一个和A表结构相同的B表，然后按照primary递增的顺序逐行（也就是增量）从A表中读取数据密钥ID并将它们插入到表B中。由于B表是新创建的表，A表主键索引的空洞在B表中是不存在的，显然B表的主键索引更紧凑，数据页利用率更高。如果我们使用B表作为临时表，将A表的数据导入到B表后，将A替换为B表，从效果上看，会缩小A表的空间，这里可以使用altertable一个engine=InnoDB命令重建表。在MySQL5.5之前，这条命令的执行过程和我们上面描述的差不多，不同的是不需要自己创建临时表B，MySQL会自动完成转储数据，交换表名，并删除旧表。显然，耗时最多的步骤就是向临时表插入数据的过程。如果在这个过程中有新的数据要写入A表，就会丢失数据。因此，在整个DDL过程中，表A不能有任何更新。也就是说，这个DDL是不Online的。MySQL5.6引入的OnlineDDL优化了这个操作过程。创建一个临时文件，扫描A表主键的所有数据页；使用数据页中表A的记录生成B+树，存入临时文件；在生成临时文件的过程中，将对A的所有操作记录在一个日志文件（rowlog）中；临时文件生成后，将日志文件中的操作应用到临时文件中，得到与A表逻辑数据相同的数据文件；（应用行日志的过程可能会返回到分页）使用临时文件替换表A的数据文件。可以看出，在这个过程中，由于日志文件记录和重放操作功能的存在，这个scheme允许在重建表的过程中对A表进行增删改查操作。这就是OnlineDDL这个名字的由来。需要补充的是，上面提到的重建方式都会扫描原表数据，建立临时文件。对于非常大的表，此操作会消耗IO和CPU资源。所以，如果是在线服务，就得谨慎控制运营时间。optimizetable、a??nalyzetable和altertable重建表的区别：从MySQL5.6开始，altertabletengine=InnoDB（即recreate）默认为上述在线DDL流程；analyzetablet实际上并不是重建Table，只是重新统计了该表的索引信息，没有修改数据，这个过程中加入了MDL读锁；优化表t等于recreate+analyze。参考资料：《MySQL实战45讲》《MySQL技术内幕》
