您的位置:首页 > 教程 > 服务端类 > Golang > MySQL磁盘碎片整理实例演示

MySQL磁盘碎片整理实例演示

2022-04-02 14:21:22 来源:易采站长站 作者:

MySQL磁盘碎片整理实例演示

目录
1.磁盘碎片是什么2.实验3.对于碎片回收操作4.后记补充:如何优化磁盘空间

数据库引擎以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的方式,来优化磁盘空间。

到此这篇关于MySQL磁盘碎片整理的文章就介绍到这了,更多相关MySQL碎片整理内容请搜索易采站长站以前的文章或继续浏览下面的相关文章希望大家以后多多支持易采站长站!

如有侵权,请联系QQ:279390809 电话:15144810328

相关文章

  • golang用什么开发工具?

    golang用什么开发工具?

    golang用的开发工具有:1、Go Revive,是一个Go语言的代码质量检测工具;2、Go Callvis,可以用来可视化Go程序的调用图;3、Gaia,高效,快速,轻量级,并且对开发人员友好。golang用的开发工具有:1、Go Reviverevive 是一个 Go 语言的代码质量检测工具(Linter for Go),具有快速、可配置、可扩展、灵活和美观等特性,可作为 golint 的替
    2020-08-07
  • golang是多线程模式吗?

    golang是多线程模式吗?

    golang是多线程模式的,golang的线程模型是M P G模型,整体上Go程与内核线程是多对多对应的,因此首先来讲就一定是多线程的。golang是多线程模式。 由于gmp中的p与m是将p绑定与m内核线程上,而后p的最大数量有GOPROCESS确定,而M内核线程的数量会由go去限制为10K个,但是由于内核原因做不到这么多,所以这个限制就当做没有吧。拿个图明确一下Golang有些所谓的M比N模型,
    2020-08-07
  • golang用什么开发工具?

    golang用什么开发工具?

    golang用的开发工具有:1、Go Revive,是一个Go语言的代码质量检测工具;2、Go Callvis,可以用来可视化Go程序的调用图;3、Gaia,高效,快速,轻量级,并且对开发人员友好。golang用的开发工具有:1、Go Reviverevive 是一个 Go 语言的代码质量检测工具(Linter for Go),具有快速、可配置、可扩展、灵活和美观等特性,可作为 golint 的替
    2020-08-07
  • golang是单进程的吗?

    golang是单进程的吗?

    golang不是单进程的,而是多线程;golang的线程模型是M P G模型,整体上Go程与内核线程是多对多对应的,因此首先来讲就一定是多线程的。golang不是单进程的,而是多线程。Golang有些所谓的M比N模型,M个线程下可以创建N个go routine,一般而言N远大于M,本质上属于多线程模型,但是协程的调度由Go的runtime决定,强调开发者应该使用channel进行协程之间的同步。至
    2020-08-07
  • 代码详解使用Go基于WebSocket构建视频直播弹幕系统

    代码详解使用Go基于WebSocket构建视频直播弹幕系统

    (1)业务复杂度介绍开门见山,假设一个直播间同时500W人在线,那么1秒钟1000条弹幕,那么弹幕系统的推送频率就是: 500W * 1000条/秒=50亿条/秒 ,想想B站2019跨年晚会那次弹幕系统得是多么的NB,况且一个大型网站不可能只有一个直播间!使用Go做WebSocket开发无非就是三种情况:使用Go原生自带的库,也就是 golang.org/x/net ,但是这个官方库真是出了奇Bu
    2020-08-07
  • PHP语法和Go语法有什么差异?对比介绍

    PHP语法和Go语法有什么差异?对比介绍

    本篇文章给大家对比一下PHP语法和Go语法,带大家了解一下PHP语法和Go语法之间的差异。有一定的参考价值,有需要的朋友可以参考一下,希望对大家有所帮助。Go 是由 Google 设计的一门静态类型的编译型语言。它有点类似于 C,但是它包含了更多的优点,比如垃圾回收、内存安全、结构类型和并发性。它的并发机制使多核和网络机器能够发挥最大的作用。这是 GoLang 的最佳卖点之一。此外,Go 速度快,
    2020-08-07
  • golang需要什么基础?

    golang需要什么基础?

    golang需要什么基础?golang需要的基础是:Go语言语法特别简单简洁,有C的底子更好,差一些也没关系。前提是你要真心想学,才有足够的动力去学。1、初学Go语言首先弄懂基础语法和概念:基本数据类型、Struct、Array、map、Slice、指针、接口、map、内置函数,常用工具包等,还有接口和Slice的底层数据结构。这些不需要弄特别懂,能自己理解并自己描述我觉得就可以了,关键在实践和应
    2020-08-07
  • golang为什么那么火?

    golang为什么那么火?

    golang为什么那么火?golang那么火的原因:1, Concurrency的原生支持通过语言原生的Goroutine和Channel,很好的支持了Concurrency。你可以把Goroutine理解为非常轻量级的Thread。一个Goroutine只占用2KB的内存,但是一个Thread要占用1MB的内存。Goroutine的创建、销毁和切换的开销,相对于线程来说特别低。你可以随时起上千个
    2020-08-07