对辅助索引进行随机插入或删除操作可能会导致索引变得碎片化。碎片化意味着索引页在磁盘上的物理排序与页面上记录的索引排序不接近，或者在分配给索引的 64 页块中存在许多未使用的页面。Random insertions into or deletions from a secondary index can cause the index to become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

碎片化的一个症状是表占用的空间比它“应该”占用的空间要大。具体有多大，很难确定。所有InnoDB数据和索引都存储在B 树中，它们的填充因子可能从 50% 到 100% 不等。另一个碎片化的症状是这样的表扫描所花费的时间比它“应该”花费的时间要长:One symptom of fragmentation is that a table takes more space than it “should” take. How much that is exactly, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%. Another symptom of fragmentation is that a table scan such as this takes more time than it “should” take:

SELECT COUNT(*) FROM t WHERE non_indexed_column <> 12345;

前面的查询要求 MySQL 执行全表扫描，这是大表中最慢的一种查询类型。The preceding query requires MySQL to perform a full table scan, the slowest type of query for a large table.

为了加快索引扫描，您可以定期执行一次“null” ALTER TABLE 操作，这会导致 MySQL 重建表：To speed up index scans, you can periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table:

ALTER TABLE tbl_name ENGINE=INNODB

您也可以使用 ALTER TABLE tbl_name FORCE 来执行一次“null”的 alter 操作，从而重建表。You can also use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table.

无论是 ALTER TABLE tbl_name ENGINE=INNODB 还是 ALTER TABLE tbl_name FORCE 都使用 在线 DDL。更多信息，请参见 第 17.12 节，“InnoDB 与在线 DDL”。Both ALTER TABLE tbl_name ENGINE=INNODB and ALTER TABLE tbl_name FORCE use online DDL. For more information, see Section 17.12, “InnoDB and Online DDL”.

执行碎片整理操作的另一种方法是使用 mysqldump 将表导出到文本文件，删除表，然后从导出文件重新加载表。Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

如果向索引的插入始终是递增的，并且记录仅从末尾删除，InnoDB 文件空间管理算法保证索引中不会发生碎片化。If the insertions into an index are always ascending and records are deleted only from the end, the InnoDB filespace management algorithm guarantees that fragmentation in the index does not occur.