MySQL表空间碎片产生原因和优化

冷不防 2023-07-19 08:22 31阅读 0赞

### MySQL表空间碎片产生原因和优化 ###

![format_png][]

##### 1、场景描述 #####

在使用MySQL数据库的过程当中，以下两种场景是大家经常遇到的：  
    第一，随着业务体量的增大，MySQL数据库可用空间越来越小。  
    解决方法通常有：  
    1. 扩容，利用更大的磁盘空间扩容，或者使用分布式架构；   
    2. 清理不用的数据，尽量不用delete操作以减少数据碎片的产生。  
    第二，优化MySQL时可能发现表中数据只有几千行，即使是全表扫的操作，也与实际打印出来的MySQL的执行时间相差很远。    
    总结：这两种情况都与MySQL表空间碎片化有关。那么我们如何优化？本期视频就MySQL中MySQL表空间碎片产生原因和优化做了详细分析。

##### 2、MySQL表空间碎片的产生原因 #####

* 每当删除了一行内容，该段空间就会变为空白，被留空，而一段时间大量的删除操作，会使这种留空的空间变得比存储实际数据要大。
    * 当执行插入操作时，MySQL会尝试使用空白空间，但是如果某个空白空间一直没有被大小合适的数据占用，就形成了碎片。
    * 碎片分为单行碎片、和多行碎片
    * lnnoDB为保证数据有序、在国定的位置写入新值，MyISAM是找到空位即插入
    * 当MySQL对数据进行扫描的时候，扫描的对象碎片越多，就会影响查询性能。
    例如：  
    每当删除一行内容，该段空间就会变为空白，被留空。而在一段时间内的大量删除，会使留空的空间变得比存储列表内容所使用的空间更大。    
    当执行插入撮作时，MySQL会尝试使用空白空间。但如果某个空白空间一直没有被大小合适的数据占用，就形成了碎片。  
    当MySQL对进行扫描时，扫描的对象实际是列表的容量需求上限，碎片越多，就会影响查询的性能。

##### 3、MySQL表空间碎片的优化 #####

select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,DATA_FREE from information_schema.`TABLES`   order by DATA_LENGTH desc ;

查询某个表的碎片空间: 
    table_rows 表行数   zize 数据空间   data_free碎片攻    
    首先查看某个表的碎片大小，并列出所有已经产生碎片的表。  
    对于优化表碎片，不同数据库引擎有不同的方式。  
    例如:  
    MylSAM表，通过optimize table 表名的方式；   
    lnnoDB表，通过alter table 表名 engine=lnnoDB的方式进行优化。     
    引申一下optimize table的操作:
    在MySQL5.5和5.6中，实际上执行的过程是:  
    首先创建一张新的临时表，把旧表锁住，禁止插入删除只允许读写，接着把数据不断从旧表拷贝到新临时表，  
    拷贝完成后，进行瞬间rename操作，再删除旧表。因为清除碎片操作会暂时锁表，数据量越大，耗费时间越长。			
    建议：   
    1. 清除操作首先使用percona的工具进行在线优化。		
    2. 清除碎片定期选在业务低峰时进行。

![在这里插入图片描述][20200330114835777.jpg_pic_center]

[format_png]: /images/20230528/7ec44cd95c6142d18a8ebc4deecb94e1.png
[20200330114835777.jpg_pic_center]: /images/20230528/40dd7a0c7b83453d99bc440f5702b636.png