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 表空间碎片的资料请关注开心学习网其它相关文章!
- mysql程序中判断select返回空值(解决MySQL读写分离导致insert后select不到数据的问题)
- mysql中innodb四种事务级别(MySQL InnoDB 锁的相关总结)
- 最全面的mysql索引详解(MySQL 全文索引使用指南)
- python对mysql数据分析(python使用adbapi实现MySQL数据库的异步存储)
- mysql插入数据错误代码(教你解决往mysql数据库中存入汉字报错的方法)
- mysql 慢查询日志
- mysql索引建立及应用(MYSQL创建索引,这些知识应该了解)
- mysql 临时表
- mysql存储过程遍历数据(Mysql 存储过程中使用游标循环读取临时表)
- mysql锁机制图解(详解mysql 中的锁结构)
- laravel数据表配置(laravel实现按月或天或小时统计mysql数据的方法)
- mysql将字符串转换为日期的函数(Mysql中时间戳转为Date的方法示例)
- mysql索引为什么是b+树(mysql 使用B+树索引有哪些优势)
- mysql 排序源码(MySQL排序原理和案例详析)
- mysql substr函数的用法(MySQL切分函数substring的具体使用)
- mysql数据库数据分析(详解MySQL数据库千万级数据查询和存储)
- 写信告诉我今天海是什么颜色(写信告诉我今天海是什么颜色回答)
- 英语难学吗(法语比英语难学吗)
- 今天要吃什么(今天要吃什么菜好)
- 网红直播可以赚很多钱吗(网红直播可以赚很多钱吗)
- 今天是什么日子(今天是什么日子有什么特殊意义吗)
- 这里输入关键词(怎么输入关键词搜索)
热门推荐
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9