MySQL的表碎片处理

  • 时间:
  • 浏览:1

mysql5.6的官方文档在13.7.2.4小节对optimize操作有完整的介绍。optimize table命令的作用是重新组织表数据和关联索引数据的物理存储,以减小存储空间并提高访问表时的I/O速率;命令主要作用于innodb、myisam和archive引擎表,而命令对表所做的实际更改取决于该表使用的存储引擎。

·innodb引擎下的optimize操作 对于innodb表,optimize table操作实际映射为alter table ... force操作,当对innodb表执行optimize操作时因为会老是出現下面的提示信息:

mysql5.5默认是共享表空间,从5.6以后开始默认是独立表空间,每张表有当时人的文件空间。查看土辦法 所以我看数据文件大小和表数据量大小的差异:可可不可以 能 先在数据库中通过系统表information_schema.tables因为“show table status like ‘tb’ ”语句计算出data_length+index_length的值,再到操作系统上查看对应表的ibd文件(因为myd、myi文件)的物理大小。因为ibd文件比data_length+index_length值大所以,说明表存在碎片。

类式于查看test库下student表的碎片空间情况报告:

对于innodb表的优化,可可不可以 能 用alter table tb_name engine=innodb的形式优化,对于myisam表的优化可可不可以 能 直接使用optimize。

说明:

1、对于写比较频繁的表,容易产生碎片问提,但所以我用老是进行清理,一般每周因为每月一次就可可不可以 能 了;

2、OPTIMIZE TABLE只对MyISAM,BDB和InnoDB表起作用,尤其是MyISAM表的作用最为明显。此外,并都有所有表都需用进行碎片采集,一般只需用对涵盖可变长度的文本数据类型的表进行采集即可。

对于mysql表数据,当你delete掉所以数据时,你你是什么 数据占用的磁盘空间因为并不不立刻被回收;比如一张表有10G的数据,delete掉1G数据后,再查看表ibd文件会发现文件大小因为还是10G;因为什么会儿 表有insert操作语句,越来越mysql就会优先考虑可可不可以 能 将新数据存储到空白空间上,容易老是出現越来越 的情况报告:某个空白空间的大小是2MB,新插入一根数据大小是1.5MB并存储到该空白空间上,这时就会产生更小的空白空间,而你你是什么 更小的空白空间更难被利用,因为像你你是什么 碎片非常多,就会比较浪费资源怎么让降低表磁盘I/O性能。

对于频繁地update操作,也很容易产生碎片问提。比如对于可变长字段,如varchar、text、blob等字段,因为update操作将数据大小改小,越来越也会产生碎片问提。

mysql目前比较常用的引擎是innodb和myisam,这两种引擎下都有因为产生碎片,碎片的产生和消除都有随机的,而碎片太久会给查询扫描工作带来越大的影响。

data_free值可可不可以 能 反映出表的碎片空间大小。里边student表data_free显示4M,与上另俩个土辦法 计算出的碎片大小近似吻合。

根据系统表计算出student表数据为72MB,查看ibd文件大小为76MB,碎片空间离米 有4MB左右,不算太久。

mysql的系统表information_schema.tables中记录着每张表的数据、索引大小,行数等重要信息,主要字段信息如下:

table_schema:表所在数据库名

table_name:表名

engine:表的存储引擎

tables_rows:表数据行数

data_length:数据长度,即表数据大小,单位字节

index_length:索引长度,即表索引大小,单位字节

data_free:已分配但未使用的空间大小,单位字节,可可不可以 能 认为是碎片空间

通过data_free字段可可不可以 能 查出数据库涵盖你你是什么 表产生了碎片,data_length+index_length值所以我表数据量总大小(拿你你是什么 求和值与表数据文件大小比较,得到的差值往往与data_free值不一样,问你为什么会儿 )。

可可不可以 能 用下面的SQL来统计数据库涵盖你你是什么 表产生了碎片空间:

这实际上因为对表做了优化,第一步是提示optimize操作不适用该类型表,第二步是映射为alter table操作执行并成功。

在mysql5.6.17前一天 ,optimize操作越来越使用online DDL,怎么让整个操作期间会锁表,表上不允许有DML操作;

从mysql5.6.17以后开始,对于常规的和分区的innodb表,optimize操作使用online DDL,越来越 只会在操作的准备阶段和提交阶段锁住DML操作,大大提高了并发性。

一般通过optimize命令清理碎片,不过optimize命令对共享表空间不起作用。

对于mysql5.6,因为执行optimize table tb_name优化innodb表因为会报如下信息: