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.删除临时表