MySQL之表碎片化

1. 前言

周一在对线上表进行数据清除时，发现一个问题，我要清除的单表大概有2500w条数据，清除数据大概在1300w条左右，清除之前通过查询语句获取到的表大小约为7000MB。

SELECT table_name as Table, round(((data_length + index_length) / 1024 / 1024), 5) as Size(MB) FROM information_schema.tables WHERE table_schema ='db_name' AND table_name = 'table_name'\G

通过脚本清除之后，再通过查询语句获取表大小，发现表仍然有6000MB的数据剩余。感觉肯定是有对应的一些索引数据没有被删除掉，仍然保存在表中，导致表空间仍然很大。

后面了解到这个是MySQL的数据碎片，加上使用的是MySQL的InnoDB引擎，导致即使我们删除数据，表空间也不会缩小，需要通过一些额外的表优化手段来清除这些数据碎片，因为用的是InnoDB引擎，所以就看了下关于InnoDB引擎表碎片相关的知识。

2. InnoDB表碎片

InnoDB表的数据存储在页(page)中，每个页可以存放多条记录，InnoDB默认使用B+树作为索引结构，表中的数据和辅助索引都是使用B+树结构，每个InnoDB表中都有一个称为聚簇索引的特殊索引，用于存储行数据。通常聚簇索引与主键索引同义。

通过聚簇索引访问行的速度很快，以为索引搜索会直接找到包含行数据的页面，如果表很大，与使用与索引记录不同的页面存储行数据的存储组织相比，聚簇所以架构通常可以节省磁盘I/O操作。

除了聚簇索引之外，还有一个二级索引，我们也叫做辅助索引。在InnoDB中，辅助索引中的每个记录都包含行的主键列以及为二级索引指定的列，InnoDB使用此主键值在聚簇索引中搜索行。如果主键很长，则辅助索引将使用更多的空间，因此使用较短的主键是比较好的。

对于InnoDB而言，随机插入或者删除辅助索引可能会导致索引碎片化，碎片化意味着磁盘上索引页的物理顺序与页面上记录的索引顺序不接近，或者分配给索引的64页块中有许多未使用的页面。

**碎片的一个症状是表占用的空间比它“应该”占用的空间要多，**具体会多多少很难确定。所有InnoDB数据和索引都存储在B树种，它们的填充因子可能从50%到100%不等。碎片的另一个症状是表扫描花费的时间比它“应该”花费的时间要多。

在InnoDB中，删除一些行，InnoDB并不会真正的删除它们，只是会将这些行标记为“已删除”(同时也称为可复用的位置，即后续如果有对应的主键数据插在这段区域，会复用位置)，而不是真的从索引中物理删除，因此存储空间也没有真的被释放。

删除数据会导致页中出现空白空间，大量随机的DELETE操作会在数据文件中造成不连续的空白空间，当插入数据的时候，这些可复用的空白空间会被利用起来，但这会造成数据存储位置的不连续，即物理存储顺序与逻辑上的排序顺序不同，于是就产生了表数据碎片。

对表进行大量的UPDATE操作也可能会导致页分裂，频繁地页分裂，页会变得稀疏，并且被不规则的填充，继而产生表碎片，

另外，表的数据存储也可能会碎片化，数据存储的碎片化比索引更加复杂，主要有三种类型的数据碎片：

1. 行碎片(Row fragmetation)
   1. 指数据行被存储在多个地方的片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降
2. 行间碎片(Intra-row fragmetaion)
   1. 行间碎片是指逻辑上顺序的页或者行在磁盘上不是顺序存储的，行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能从磁盘顺序存储的数据中获益
3. 剩余空间碎片(Free space fragmenation)
   1. 指数据页中有大量的空余空间，会导致服务器读取大量不需要的数据，从而造成浪费。

对于MyISAM表，上述三类碎片化都有可能发生，但InnoDB不会出现短小的行碎片，InnoDB会移动短小的行并重写到一个片段中。

3. 清除表碎片

删除了数据而空间没有得到释放，于是我们需要采取一些手段来清除删除的数据留下的表碎片，从而释放存储空间，同时提升查询效率。

3.1 查找碎片化严重的表

对于表中是否含有碎片，可以通过下面的命令直接查看表信息。

show table status from db_name like '%table_nam%'\G

mysql> show table status like '%user_tab%'\G
*************************** 1. row ***************************
           Name: user_tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4   -- 可以看到有4行数据
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0  -- 可释放的空间为0
 Auto_increment: 5
    Create_time: 2023-08-12 16:58:07
    Update_time: 2024-06-23 16:38:08
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
-- 插入一些数据，然后看Data Free，发现有许多可释放的数据       
mysql> show table status like 'user_tab'\G
*************************** 1. row ***************************
           Name: user_tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 351
 Avg_row_length: 10502
    Data_length: 3686400
Max_data_length: 0
   Index_length: 1589248
      Data_free: 9437184
 Auto_increment: 41282
    Create_time: 2023-08-12 16:58:07
    Update_time: 2024-06-23 17:00:29
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

3.2 清除碎片

清理碎片主要有两种方式：

第一种是优化表，即OPTIMIZE TABLE，这种方式会重组表和索引的物理存储，减少对存储空间的使用和提升访问表的I/O效率。OPTIMIZE操作会暂时锁住表，数据量越大，则耗时越长。对每个表所做的确切更改取决于该表使用的存储引擎。

对于InnoDB表，OPTIMIZE TABLE会映射到ALTER TABLE … FORCE, 这将重建表以更新索引统计信息并释放聚簇索引中未使用的空间。

对刚刚的user_tab采用OPTIMIZE的命令，可以看到空间被释放了。

mysql> optimize table user_tab\G
*************************** 1. row ***************************
   Table: duanxi.user_tab
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
-- 实际采用的事recreate + analyze方式
*************************** 2. row ***************************
   Table: duanxi.user_tab
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.04 sec)

mysql> show table status like 'user_tab'\G
*************************** 1. row ***************************
           Name: user_tab
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0   -- 优化后Data Free变为0了
 Auto_increment: 41282
    Create_time: 2024-06-23 17:02:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

InnoDB的OPTIMIZE TABLE对常规表和分区表使用在线DDL方式，从而减少并发DML操作的停机时间。由OPTIMIZE TABLE触发的表重建会在原地完成，在操作的准备阶段和提交阶段，只短暂地采用排它表锁，在准备阶段，更新元数据并创建中间表，在提交阶段，提交表元数据更改。

在线DDL(MySQL 8.0+)
在线 DDL 功能支持即时、就地表更改和并发 DML。此功能的优点包括：

• 在繁忙的生产环境中提高响应能力和可用性，因为让表不可用几分钟或几小时是不切实际的。
• 对于就地操作，可以使用LOCK子句在 DDL 操作期间调整性能和并发之间的平衡。
• 与表复制方法相比，磁盘空间使用量和 I/O 开销更少。

OPTIMIZE TABLE在以下情况下使用表赋值方法重建表

• 当old_alter_table系统变量启用时
• 当服务器使用—skip-new选项启动时

InnoDB使用页面分配方法存储数据，不会像传统存储引擎(例如MyISAM)那样收到碎片的影响，在考虑是否运行优化时，请考虑你的服务器预计要处理的事务的工作负载。

• 预计会出现一定程度的碎片，InnoDB仅填充93%的页面，以便留出更新空间，而无需拆分页面
• 删除操作可能会留下空隙，导致页面填充不足，这可能会使优化表变得有价值
• 当有足够的空间时，对行的更新通常会重写同一页内的数据，具体取决于数据类型和行格式。
• 高并发工作负载可能会随着时间的推移在索引中留下空白，因为InnoDB通过其MVCC机制保留了同一数据的多个版本。

对于MyISAM，OPTIMIZE TABLE工作原理如下：

1. 如果表有删除或拆分行，则修复该表。
2. 如果索引页未排序，则对其进行排序。
3. 如果表的统计信息不是最新的（并且无法通过对索引进行排序来完成修复），则更新它们。

第二种操作则是使用ALTER TABLE table_name ENGINE= InnoDB; 的方式，此方式看起来没有执行什么操作，实际上重新整理碎片了，当执行这个优化操作时，InnoDB会重建整个表并释放聚簇索引中未使用的空间。

4. 小结

因为删除表数据发现表使用空间未被释放，继而发现有表碎片问题，查找一些资料去了解表碎片的产生以及表碎片的处理，最终让自己学习到了关于InnoDB表碎片相关的知识。

表碎片的产生主要是InnoDB删除非物理删除，而是标记”删除”，且这些被“删除”的空间后续还可复用，进而导致磁盘上索引页的物理顺序与页面上记录的索引顺序不接近，引发表的碎片化。同时表的大量更新、表的数据存储页都会产生不同的表碎片。

表碎片的清除手段：

1. OPTIMIZE TABLE table_name;
2. ALTER TABLE table_name ENGINE = InnoDB;

需要注意的是，无论我们采用哪种手段清除表碎片，都会有锁表的时间，我们需要根据自己服务器要处理的事务的工作负载分析，研判这种锁表时间对于业务是否接受，如果可以接受则可以对表碎片进行优化，如果不能接受，则无需进行优化，等待后续再进行优化。(思考再三，我选择放弃优化，让碎片继续留在表中)

5. 参考

• Defragmenting a Table
• Overview of fragmented MySQL InnoDB tables
• OPTIMIZE TABLE Statement