MySQL表空间碎片

您所在的位置:网站首页 磁盘碎片整理目的是什么意思 MySQL表空间碎片

MySQL表空间碎片

2024-07-10 04:47| 来源: 网络整理| 查看: 265

1 碎片是如何产生的

MySQL碎片指的是MySQL数据文件中一些不连续的空白空间,这些空间无法再被全部利用,久而久之越来越多越来越零碎。 MySQL中碎片的产生主要由频繁的删除、更新和插入操作导致:

在InnoDB中,删除记录只是将这些行标记为“已删除”,不是真的从索引中物理删除了,磁盘的文件大小不会收缩。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。 当执行插入操作时,MySQL会尝试使用空白空间,如果插入的数据刚好按照索引排序落在这个区间,可能会复用这个位置,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;

对于大量的UPDATE,也会产生文件碎片化。Innodb的最小物理存储分配单位是页(page),而UPDATE也可能导致页分裂,频繁的页分裂,页会变得稀疏,并且被不规则的填充,所以最终数据会有碎片。例如原始数据长度varchar(100),大规模更新数据长度为50,这样的话,有50的空间被空白了,新入库的数据不能完全利用剩余的50,这就会产生碎片。

【MySQL的几种删除情况】

drop、truncate不管是InnoDB还是MyISAM都立刻释放磁盘空间delete from table_name删除全部表数据,MyISAM立刻释放磁盘空间,InnoDB不会立刻释放磁盘空间delete from table_name where xxx;带条件的删除不管是InnoDB还是MyISAM都不会立刻释放delete from表后虽然未立刻释放磁盘空间,但下次插入的时候仍然可以使用这部分空间 2 碎片会带来什么问题

浪费磁盘空间:由于碎片空间是不连续的,导致这些空间不能充分被利用;

查询性能下降:由于碎片的存在,导致数据库的磁盘 I/O 操作变成离散随机读写,加重了磁盘 I/O 的负担。查询需要扫描的磁盘空间也更大了导致查询速度下降。

3 如何清理碎片 3.1 查看表是否存在碎片

一种方式是查看表的status,如果data_free字段不为0则表示有碎片存在

show table status like '表名'

在这里插入图片描述

第二种方式是从information_schema.tables表中查询表的元数据信息:

SELECT CONCAT( TRUNCATE ( SUM( data_length ) / 1024 / 1024, 2 ), 'MB' ) AS data_size, CONCAT( TRUNCATE ( SUM( data_free ) / 1024 / 1024, 2 ), 'MB' ) AS data_free, CONCAT( TRUNCATE ( SUM( index_length ) / 1024 / 1024, 2 ), 'MB' ) AS index_size FROM information_schema.TABLES WHERE TABLE_NAME = 'tableName'; 3.1碎片清理方式 optimize table table_name Optimize语句可以重新组织表、索引的物理存储,减少存储空间,提高访问的IO效率

【注意】 1.使用这个语句需要对目标表具有select、insert权限 2.检查磁盘空间:剩余空间必须 > 被optimize的表大小 3.Optimize只对独立表空间(innodb_file_per_table=1)才有作用 4.数据量越大的表,优化耗时越长,百万条数据大约耗时30s(约25000-30000行/秒,此数据根据机器磁盘性能会有差异)。所以,在磁盘优化时,所有的增删操作将受限,请选择一个业务空档期执行。

alter table table_name engine=InnoDB 本质上是recreate,Alter期间支持DML查询和更新操作

执行过程 1.获取MDL(meta data lock)写锁,innodb内部创建与原表结构相同的临时文件 2.拷贝数据前,MDL写锁退化成读锁,支持DML更新操作 3.根据主键递增顺序,将一行一行的数据读出并写到临时文件,直到全部写入完成,并且拷贝期间的DML更新操作会记录到Row log中 4.上锁,再将Row log中的数据应用到临时文件 5.互换原表和临时表的名字 6.删除临时表



【本文地址】


今日新闻


推荐新闻


CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3