MySQL碎片整理小节--实例演示

MYSQL之磁盘碎片整理 清澈,细流涓涓的爱

数据库引擎以InnoDB为主
1.磁盘碎片是什么
? InnoDB表的数据存储在页中,每个页可以存放多条记录,这些记录以树形结构组织,这棵树称为B+树。
MySQL碎片整理小节--实例演示
文章图片

? 聚簇索引的叶子结点包含行中所有字段的值,辅助索引的叶子结点包含索引列和主键列。
? 在InnoDB中,删除一些行,这些行只是被标记已删除,而不会立即删除,个人认为和电脑的清除磁盘相同,之后可以通过覆盖旧数据实现删除,InnDB的Purge线程会异步的清理这些没用的索引键和行。但是依然不会把这些释放出来的空间还给操作系统重新使用,因此会导致页面中存在很多空洞,如果表结构中包含动态长度字段,这些空间甚至无法被InnoDB重新用来存储新的行。
? 另外严重的问题是删除数据会导致页page中出现空白空间,大量随机的Delete操作必然会在数据文件中造成不连续的空白空间,当插入数据时,这些空白空间则会被利用起来,造成了数据的物理存储顺序和逻辑的排序顺序不同,这就是数据碎片。
-- 查看全局变量Purge show variables like 'innodb_purge_threads';

InnoDB后台线程:https://www.cnblogs.com/abclife/p/5062008.html
解释磁盘碎片的英文博客: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

创建后的磁盘存储大小:
MySQL碎片整理小节--实例演示
文章图片

? 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:
MySQL碎片整理小节--实例演示
文章图片

我们可以看到data_free,我们最高的free空间只有6MB
innodb_ruby工具可以直接在linux系统下运行查看.Ibd文件的结构,将B+tree以及磁盘使用暴露出来,但是我不会用,这里带上他的github链接:https://github.com/akopytov/sysbench
下面我们执行删除操作:
-- 删除前五十万条数据 delete from temp order by id LIMIT 500000

文件大小:
MySQL碎片整理小节--实例演示
文章图片

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

可以看到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结果
MySQL碎片整理小节--实例演示
文章图片

磁盘文件小了一半左右
MySQL碎片整理小节--实例演示
文章图片

alter不再演示
4.后记
【MySQL碎片整理小节--实例演示】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.cnblogs.com/kerrycode/p/10943122.html --写的很好,就是排版不舒服
https://www.cnblogs.com/lovebing/p/7463532.html --写的不错

    推荐阅读