MySQL磁盘碎片整理实例演示

数据库引擎以InnoDB为主

1.磁盘碎片是什么

InnoDB表的数据存储在页中，每个页可以存放多条记录，这些记录以树形结构组织，这棵树称为B+树。

聚簇索引的叶子结点包含行中所有字段的值，辅助索引的叶子结点包含索引列和主键列。

在InnoDB中，删除一些行，这些行只是被标记已删除，而不会立即删除，个人认为和电脑的清除磁盘相同，之后可以通过覆盖旧数据实现删除，InnDB的Purge线程会异步的清理这些没用的索引键和行。但是依然不会把这些释放出来的空间还给操作系统重新使用，因此会导致页面中存在很多空洞，如果表结构中包含动态长度字段，这些空间甚至无法被InnoDB重新用来存储新的行。

另外严重的问题是删除数据会导致页page中出现空白空间，大量随机的Delete操作必然会在数据文件中造成不连续的空白空间，当插入数据时，这些空白空间则会被利用起来，造成了数据的物理存储顺序和逻辑的排序顺序不同，这就是数据碎片。

-- 查看全局变量Purge
show variables like 'innodb_purge_threads';

InnoDB后台线程：https://www.jb51.net/article/243211.htm

解释磁盘碎片的英文博客：https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/

上面这是个大能的博客，写的pretty good！

2.实验

我们首先创建一个具有一百条数据的表来进行实验：

delimiter //
create procedure insertt()
begin
declare i int DEFAULT 0;
while i<1000000 do
insert into temp values(null,'a',1);
set i:=i+1;
end while;
end;
//
delimiter ;
drop PROCEDURE insertt;
-- 尝试插入一百万条数据

call insertt()

-- mysql版本5.7.36
> OK
> 时间: 838.706s

创建后的磁盘存储大小：

DB：information_scheme中存放我们表的信息，通过下列命令来查看我们的磁盘碎片最大的前五名

-- 别人的博客中copy的，我的猪脑写不出来
SELECT CONCAT(table_schema, '.', table_name) as 'TABLE',
      ENGINE, CONCAT(ROUND(table_rows / 1000000, 2), 'M')  ROWS,
      CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
      CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') IDX,
      CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL SIZE',
      ROUND(index_length / data_length, 2)  IDXFRAC, CONCAT(ROUND(( data_free / 1024 / 1024),2), 'MB') AS data_free
FROM information_schema.TABLES  
ORDER BY data_length + index_length desc LIMIT 5;

result:

我们可以看到data_free，我们最高的free空间只有6MB

innodb_ruby工具可以直接在linux系统下运行查看.Ibd文件的结构，将B+tree以及磁盘使用暴露出来，但是我不会用，这里带上他的github链接:https://github.com/akopytov/sysbench

下面我们执行删除操作：

-- 删除前五十万条数据
delete from temp order by id LIMIT 500000

文件大小：

删除后，磁盘文件的大小并没有变化，因为删除产生了磁盘碎片，空白page残留在文件中，被删除的数据记录仍然被保持在MySQL的链接清单中，因此数据存储文件的大小并不会随着数据的删除而减小，我们再次使用上述操作查看data_free。

可以看到temp表的data_free增长了。

3.对于碎片回收操作

对MySQL进行碎片整理有两种方法：

OPTIMIZE TABLE

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2] ...

ALTER

ALTER TABLE table_name ENGINE = Innodb

OPTIMIZE可以同时对多个表格进行碎片整理，OPTIMIZE语句有两个可选的关键字：LOCAL和NO_WRITE_TO_BINLOG，默认是每次碎片整理都会被记录到BINlog二进制日志中去，如果带了关键字，就不会被记录到日志中去。

ALTER看起来是执行了一次空操作，重新设置了一遍数据库引擎，同时会进行碎片整理。

两种操作在一定程度是等价的。

使用optimize结果

磁盘文件小了一半左右

alter不再演示

4.后记

1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。

2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。

3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

参考博客：https://www.jb51.net/article/223238.htm --写的很好，就是排版不舒服

https://www.jb51.net/article/243217.htm --写的不错

补充：如何优化磁盘空间

1.优化前必看注意事项

①.优化表空间时，会造成锁表

数据量越大的表，优化耗时越长，百万条数据大约耗时30s(约25000-30000行/秒,此数据根据机器磁盘性能会有差异)。所以，在磁盘优化时，所有的增删操作将受限，请选择一个业务空档期执行。

②.间隔多久需要优化一次磁盘碎片？

Mysql官方不建议频繁进行碎片整理，比如每天都整理磁盘。可观测一次优化后，能撑多久才会产生比较大的碎片文件，然后根据这个周期，定制一个定期优化碎片的任务。

如：每周或每月凌晨3点定时清理碎片。

2.如何优化磁盘空间

判断你的数据表的引擎是什么

①.如果引擎是MyISAM,则可以通过下面SQL优化。

-- 优化表空间
optimize table tableName;

注：如果引擎是InnoDB，执行此SQL会提示：Table does not support optimize, doing recreate + analyze instead②.如果引擎是InnoDB,通过下列SQL可以代替optimize table xxx

-- 将表改为InnoDB数据库引擎
ALTER TABLE tableName ENGINE=InnoDB;

-- 分析表，查看表状态
ANALYZE TABLE tableName;

即通过重新将数据库引擎设置为InnoDB的方式，来优化磁盘空间。

来源：https://www.cnblogs.com/oldoldcoder/p/16084412.html