Mysql碎片清理

为什么会产生碎片

简单的说,删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片.实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。这样不但会浪费空间，并且查询速度也更慢。

https://www.cnblogs.com/xibuhaohao/p/10216078.html

查询所有有碎片的表

SELECT
    table_schema db,
    table_name,
    concat( TRUNCATE ( data_free / 1024 / 1024, 2 ), ' MB' ) AS DATA_FREE 
FROM
    information_schema.TABLES 
WHERE
    table_schema NOT IN ( 'information_schema', 'mysql' ) 
    AND TRUNCATE ( data_free / 1024 / 1024, 2 ) > 1024;

清理sql

语法：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 , table_name2

 

说明：

• optimize table在操作的准备阶段和提交阶段期间短暂地进行独占表锁定，对于InnoDB表，OPTIMIZE TABLE映射到 ALTER TABLE ... FORCE，重建表以更新索引统计信息并释放聚簇索引中未使用的空间；

查询information_schema.FILES 的FILE_ID 和 information_schema.TABLES的CREATE_TIME 可知OPTIMIZE TABLE相当于表删除重建

 

• optimize table的本质，就是alter table

mysql 5.5 的改表过程如下

1.创建一张新的临时表 tmp 

2.把旧表锁住，禁止插入删除，只允许读写 （这就是为什么上面的insert语句都停留在waiting for table metadata lock）

3.把数据不断的从旧表，拷贝到新的临时表，(这就是上面报copy to tmp table)

4.等表拷贝完后，进行瞬间的rename操作

5.旧表删除掉

• OPTIMIZE TABLE可以一次性对多个表进行碎片整理，只需要在OPTIMIZE TABLE后面接多个表名，并以英文逗号隔开即可
• OPTIMIZE TABLE语句有两个可选的关键字：LOCAL和NO_WRITE_TO_BINLOG。在默认情况下，OPTIMIZE TABLE语句将会被记录到二进制日志中，如果我们指定了LOCAL或NO_WRITE_TO_BINLOG关键字，则不会记录。当然，一般情况下，我们也无需关注这两个关键字。

如果mysql是主从复制模式，不需要使用这两个关键字，master机器清理后slave机器会自动同步清理

optimize table bitmap_enterprise.activity_pageuser,bitmap_enterprise.activity_pageuser_all;

InnoDB引擎会返回如下语句，但是一样可以正常清理碎片

也可以直接使用下边的方式进行清理InnoDB引擎的表

ALTER TABLE tablename ENGINE=InnoDB

注意事项

optimize table执行时会造成锁表，所以执行前一定要注意，不要有dml语句，保证业务暂停或切换

optimize table执行的时间会很长，表越大时间越长，执行前要考虑到时长的问题