阅读量:9
工作当中时常会遇到mysql实例磁盘空间告警等问题，常见的解决方式：
今天这里讲讲mysql碎片，首先一个问题，mysql为什么会产生碎片？
总的来说：MySQL碎片是由于数据库表的行或索引被删除而留下的未使用空间造成的。这通常发生在频繁插入、更新，删除操作的数据库表中。碎片化可能会降低查询性能，因为数据库需要更多时间来处理已满的磁盘块或索引的不连续部分。
1.MySQL中insert与update都可能导致页分裂，这样就存在碎片，对于大量的update也可能导致页分裂, 频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
2. delete 语句实际上只是给数据打个标记（innodb中，删除一些行，这些行只是被标记“删除”，而不是真的从索引中物理删除，因而空间没有真的被释放，而是等purge线程异步来清理这些没用的索引和行），并且记录到链表中，这样就形成了留白空间。
3. 当执行insert 操作时，MySQL会尝试使用空白空间，但如果留白空间大小不合适，仍然无法将其彻底占用，这块空间也就成了碎片。
如何整理碎片呢？MySQL删除数据是否会释放磁盘空间呢？这就要分情况了：
1.删除表，清空表，当执行删除表和清空表的全部数据时，都会释放相应的磁盘空间，适用于innodb还是myisam
drop table xx;truncate table xx;#truncate table 有点类似drop table后再create table, 所以速度会接近drop table的速度，比delete from清理全表要快很多
2.删除表数据，不会彻底释放空间，上面已经提过，MySQL并没有把数据文件删除，而是将数据文件标识位删除，没有整理文件，因此不会立马释放空间，当有新数据写入时，MySQL会利用这些已删除的空间再次写入。
delete from table_name [where xxx];接下来的问题：当出现碎片时，如何整理呢？在整理碎片之前先要找到碎片？
a. 查看整体的碎片情况：
select table_schema, TABLE_NAME, data_free, concat(data_free/1024/1024, 'M') as data_free_human from information_schema.tables where data_free > 3*1024*1024 and engine='innodb' order by data_free desc;b. 查看某张表的碎片：
show table status like 'table name';...Data_free:xxx (Data_free就是碎片参数值)
c. 如果你使用的是腾讯云或者阿里云paas，会提供相应的cdb实例空间分析
找到碎片的表后，接下来可以使用下述方法清理碎片：
日常我最常用的alter table table_name engine=innodb和optimize table table_name，当然操作时切记选择系统闲时来操作，希望看完这篇文章能对您有一点点帮助：），每天学习一点，加油。
