MySQL 碎片管理

古城微笑少年丶 2023-07-08 09:29 17阅读 0赞

## 背景信息 ##

**MySQL 版本:** MySQL 5.7.20

## 碎片产的生原因 ##

(1)记录被Delete，且原空间无法复用(MySQL插入为了提高效率直接插入末尾的)；  
(2)记录被Update(通常出现在变长字段中，重新分配存储空间)，原空间无法复用；  
(3)记录插入导致页分裂，页的填充率降低；

## 碎片率高的影响 ##

(1)浪费磁盘空间；  
(2)可能导致查询扫描的IO成本提升，效率降低；  
如果表空间较小或者碎片率较小，用户无需关注，也不建议执行回收空间碎片操作。

## 回收表空间碎片的三种方法 ##

**方法一:** optimize table table\_name  
  OPTIMEZE TABLE 重新组织 table 数据和相关索引数据的物理存储，以减少访问table的存储空间并提高I/O效率。对每个 table 所做的确切更改取决于该 table 使用的存储引擎。

回收碎片的常见放大是通过optimize table tablename 来重组文件，操作过程会导致该表上的写操作无法执行(锁表)，实例负载增大，请用户谨慎操作，如果确定需要回收，建议放在业务低峰期进行。

**方法二:** alter table table\_name engine=INNODB  
定期执行”null” ALTER TABLE 操作，会导致MySQL重建table:  
ALTER TABLE tbl\_name ENGINE=INNODB  
还可以使用ALTER TABLE tbl\_name FORCE执行重建table的”null”更改操作。  
ALTER TABLE tbl\_name ENGIN=INNODB和ALTER TABLE tbl\_name FORCE都使用在线 DDL。

**方法三:** mysqldumptable、删除table、重新载入数据  
  执行碎片整理操作的另一种方法是使用mysqldump将table转储到文本文件，删除table，然后从转存文件重新加载它。

## 拓展 ##

(1) 频繁的 delete 操作导致表空间碎片率增高是不可避免的；  
(2) 更新包含可变长字段的数据也会导致表空间碎片率增高，那么在表设计的时候如果可以的话，尽量使用较小的数据类型并且选择定长的数据类型。

## 参考文献 ##

\[1\] [MySQL 5.7 Reference Manual][]  
\[2\] [阿里云文档][Link 1]  
\[3\] [MySQL 5.7 中文文档][MySQL 5.7]

[MySQL 5.7 Reference Manual]: https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html
[Link 1]: https://help.aliyun.com/knowledge_detail/57848.html
[MySQL 5.7]: https://www.docs4dev.com/docs/zh/mysql/5.7/reference/optimize-table.html