MySQL之表碎片化的问题解决

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)

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工作原理如下：

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

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

4. 小结

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

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

表碎片的清除手段：

• OPTIMIZE TABLE table_name;
• ALTER TABLE table_name ENGINE = InnoDB;

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

5. 参考

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

到此这篇关于MySQL之表碎片化的问题解决的文章就介绍到这了,更多相关MySQL 表碎片化内容请搜索编程网(www.lsjlt.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.lsjlt.com)！