mysql剩余表空间大小(MySQL 表空间碎片的概念及相关问题解决)
mysql剩余表空间大小
MySQL 表空间碎片的概念及相关问题解决背景
经常使用 mysql 的话,会发现 mysql 数据文件的磁盘空间一般会不停的增长,而且有时候删了数据或者插入一批数据的时候,磁盘空间有时候还会毫无变化。引发这个其妙现象的就是 mysql 的表空间碎片。
什么是表空间碎片?
表空间碎片指的是表空间中存在碎片,形象一点来比喻的话,就像是一张 a4 纸,“表空间碎片”就像是把这张 a4 纸撕碎,再重新拼起来,各个碎片之间都会有一些缝隙存在,这些缝隙就是“表空间碎片”。重新拼起来的碎片实际上会比完整的 a4 纸大上一圈,这也代表着表空间容易引发的问题:空间浪费。
对于背景中描述的现象,可以用一张图来进行解释:
图中的数字代表真实的数据行,圆角矩形代表一个表的表空间。从左往右,第一次操作是删除数据,由于 mysql 在设计上是不会主动释放空间的,因此当表中的数据行被删除时,虽然数据被“删除”了,但是实际上这部分空间是没有释放的,依旧会被 table a 占用,因此也就出现了这样子的情景:删除了日志表的很多数据,但是 mysql 的磁盘空间并没有降低。
ps:这种不释放空间的设计多半和惰性删除有关,早期设计数据库时,使用的 io 设备一般是机械盘,读写性能比 ssd 差很多,所以删除操作一般不会直接触发磁盘上的数据删除。
可以看到数据删除之后,原本连续的空间中出现了两个空白的区域,这种一般就叫做表空间空洞,空洞太多了就叫做表空间碎片化(对应的是表空间连续)。这部分的空间虽然不会释放,但是会被标记为可重复利用,参考最右边的表空间示意图(第三个圆角矩形),当新插入数据的时候新数据会重新写入到表空间空洞中,这也代表着:在大规模删除过数据的表上,写入数据时,表空间可能不会明显增长或者不会增长。
实际上产生表空间空洞的操作并不只有 delete,update 也会引起这个问题,比如在 varchar 这种变长的字符型列中修改数据,改短一些的时候就会出现非常小的空洞,改长的话就有可能会因为空间不足导致把数据行的一些数据迁移到其他地方去。
怎么查看表空间碎片
mysql 的系统表记录了表空间的使用情况,可以用如下查询检查:
|
select concat(table_schema, '.' ,table_name) as 'table_name' , table_rows as 'number of rows' , concat(round(data_length/(1024*1024),2), ' m' ) as 'data_size' , concat(round(index_length/(1024*1024),2), ' m' ) as 'index_size' , concat(round(data_free/(1024*1024),2), ' m' ) as 'data_free' , concat(round(data_free/data_length,2), ' %' ) as 'data_free_pct' , engine as 'engine' from information_schema.tables where table_schema = 'tablename' order by data_free desc ; |
data_free 指表空间碎片的总空间大小,data_free_pct 指这个表的碎片百分比,效果如下:
|
mysql> select concat(table_schema, '.' ,table_name) as 'table_name' , -> table_rows as 'number of rows' , -> concat(round(data_length/(1024*1024),2), ' m' ) as 'data_size' , -> concat(round(index_length/(1024*1024),2), ' m' ) as 'index_size' , -> concat(round(data_free/(1024*1024),2), ' m' ) as 'data_free' , -> concat(round(data_free/data_length,2), ' %' ) as 'data_free_pct' , -> engine as 'engine' -> from information_schema.tables -> where table_schema = 'sbtest' -> order by data_free desc ; + ----------------+----------------+-----------+------------+-----------+---------------+--------+ | table_name | number of rows | data_size | index_size | data_free | data_free_pct | engine | + ----------------+----------------+-----------+------------+-----------+---------------+--------+ | sbtest.sbtest5 | 0 | 0.02 m | 0.00 m | 44.00 m | 2816.00 % | innodb | | sbtest.sbtest4 | 986400 | 214.70 m | 15.52 m | 4.00 m | 0.02 % | innodb | | sbtest.sbtest3 | 986400 | 214.70 m | 15.52 m | 4.00 m | 0.02 % | innodb | | sbtest.sbtest2 | 986400 | 214.70 m | 15.52 m | 4.00 m | 0.02 % | innodb | | sbtest.sbtest1 | 987400 | 199.70 m | 15.52 m | 4.00 m | 0.02 % | innodb | + ----------------+----------------+-----------+------------+-----------+---------------+--------+ 5 rows in set (0.00 sec) |
第一行数据是测试用的数据,表中的所有数据都被删掉了,因此计算出来的 data_free_pct 超过了 100%。
怎么解决表空间碎片问题
目前,能够回收表空间的办法仅有一个,就是重建表,手段包括但不限于 optimize,alter table 等。alter table 的有些操作只能靠 rebuild 表来完成,所以有时候对大表进行一些维护操作之后,也会看到磁盘空间使用率下降,这就是回收了表空间碎片腾出来的那一部分空间。
从一般经验来看,表空间碎片的回收操作不建议经常执行,每个月一次就足够了,因为 rebuild 表对服务器的资源影响会比较大,且会影响这个表的写入操作。碎片率(data_free_pct)低于 20% 的时候也不用特别在意,除非磁盘空间非常紧张,且日志基本被清空。
对于回收空间的问题
对一些日志表,或者是有区域性特征的表,建议使用 mysql 的分区表来管理,需要清理一批数据的时候,可以用 partition truncate 的方式进行清理,磁盘空间也能直接释放掉。
以上就是mysql 表空间碎片的概念及相关问题解决的详细内容,更多关于mysql 表空间碎片的资料请关注开心学习网其它相关文章!
- linuxmysql客户端搭建(一台linux主机启动多个MySQL数据库的方法)
- mysql binlog模式实际使用(实例验证MySQL|update字段为相同的值是否会记录binlog)
- 终于有人将mysql 索引讲清楚了(MySQL 索引的一些细节分享)
- mysql8.0关键字段使用(MySQL 8.0新特性之隐藏字段的深入讲解)
- mysql怎么给查询权限(MySql设置指定用户数据库查看查询权限)
- mysql和utf8哪个好(为什么在MySQL中不建议使用UTF-8)
- binlog怎么恢复mysql数据库(mysql5.7使用binlog 恢复数据的方法)
- mysql的视图和临时表区别(MySQL 内存表和临时表的用法详解)
- 将SQL Server数据迁移到MySQL的常见工具
- 修改mysql默认超时(MySQL sql_mode修改不生效的原因及解决)
- mysql常用存储方案及基本原理(详解分析MySQL8.0的内存消耗)
- mysql5.7.19下载及安装教程(Apache2.2.16+PHP5.3.3+MySQL5.1.49的配置方法)
- mysql数据库死锁原理(MySQL数据库锁机制原理解析)
- mysql8.0详解(MySQL 8.0 的 5 个新特性,太实用了!)
- mysql中的null和空值的区别(解决mysql使用not in 包含null值的问题)
- redhat6.5安装mysql(Redhat7.3安装MySQL8.0.22的详细教程二进制安装)
- CellPress旗下的6 期刊,国人友刊来了解一下吧(CellPress旗下的6期刊国人友刊来了解一下吧)
- ()
- SCI检索 SSCI检索 EI检索 ISTP检索 CSCD检索简介(SCI检索SSCI检索EI检索)
- 参考文献里期刊名称的写法,你知道吗(参考文献里期刊名称的写法)
- 硕博期刊 SCI SSCI CSSCI分不清 一文带你看懂主流期刊分类(硕博期刊SCISSCI)
- 辱华品牌新百伦官宣新代言人IU,个别粉丝希望get爱豆同款(辱华品牌新百伦官宣新代言人IU)
热门推荐
- python将txt数据写入excel(Python将列表数据写入文件txt, csv,excel)
- mysql服务器端安装步骤(windows下jsp+mysql网站环境配置方法)
- 手机网站自适应的方法
- 聊天室python小程序(用Python写一个模拟qq聊天小程序的代码实例)
- 微信开发接口文档地址
- 用python实现atm银行系统(Python实现的银行系统模拟程序完整案例)
- laravel框架保存数据(Laravel 数据库加密及数据库表前缀配置方法)
- 电脑提示8080端口号被占用(80端口被占用怎么办?80端口被占用解决方法)
- ASP.NET将Excel数据导入到数据库
- dedecms命名规则(dedecms utf-8 出现乱码问题的解决方法之一)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9