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

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

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. 清除碎片定期选在业务低峰时进行。