MySQL运维35-表碎片的判断和处理

1、表碎片概述

• MySQL表碎片是指在表中存在不连续的数据块，这是由于表中的数据频繁地进行删除、更新和插入操作所导致的。这些操作可能会导致表中的数据分散在不同的物理位置上，从而降低查询性能和占用更多的存储空间。
• 更连续、更紧凑的数据块可以让性能变得更好。碎片化的表会导致一些操作比较慢，如索引范围查找，尤其是对于覆盖索引类的查询。
• 一般情况下，我们极少碰到表的碎片所导致的性能问题，但在突然的大规模的数据变更下，碎片可能会比较严重。

2、表碎片的判断方法

1. 判断方法一：一般情况下，当我们对一个大表进行全表扫描的时候，SHOW INNODB STATUS\G如果显示平均I/O SIZE比较小，比如20KB，那么这个表的碎片可能就比较多。
2. 判断方法二：在操作系统下，我们可以使用cat命令判断碎片是否比较严重，正常的情况下，6块15K转速的SAS盘所组成的RAID1+0，I/O吞吐率可以达到300MB每秒，如果我们使用如下命令检查到每秒只有几十MB，那么表的碎片可能比较严重了，命令如下：

shell> cat  /dbname/tablename.ibd > /dev/null

3、表碎片的处理方法

1. 一般有如下三种办法整理碎片。
   • OPTIMIZE TABLE（推荐使用）
   • ALTER TABLE TABLE_NAME ENGINE=ENGINE
   • 重新导出导入数据。
2. 但请注意，碎片化比较严重，不一定就是有性能问题，即使以上碎片化的比率达到20%甚至30%，你应该在确认性能问题的原因就是表的碎片化后才能采取行动。

4、总结

1. 表碎片是表中存在不连续的数据块，可能导致查询性能问题。但一般情况下表碎片并没有太大问题，可以通过查看表的数据文件吞吐量来判断是否存在碎片。
2. 表碎片一般建议用OPTIMIZE TABLE命令解决。