MySQL的表碎片处理

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

MySQL的表碎片处理

未央& 2018-12-12 14:21:12 浏览1495
展开阅读全文

一、表碎片的产生

对于mysql表数据,当你delete掉很多数据时,这些数据占用的磁盘空间可能并不会立刻被回收;比如一张表有10G的数据,delete掉1G数据后,再查看表ibd文件会发现文件大小可能还是10G;如果这个表有insert操作的话,那么mysql就会优先考虑能不能将新数据存储到空白空间上,容易出现这样的情况:某个空白空间的大小是2MB,新插入一条数据大小是1.5MB并存储到该空白空间上,这时就会产生更小的空白空间,而这种更小的空白空间更难被利用,如果像这种碎片非常多,就会比较浪费资源而且降低表磁盘I/O性能。
对于频繁地update操作,也很容易产生碎片问题。比如对于可变长字段,如varchar、text、blob等字段,如果update操作将数据大小改小,那么也会产生碎片问题。
mysql目前比较常用的引擎是innodb和myisam,这两种引擎下都有可能产生碎片,碎片的产生和消除都是随机的,而碎片越多会给查询扫描工作带来越大的影响。

二、查看表碎片的方式

1、data_length+index_length与ibd文件大小的比较

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表的碎片空间情况:

mysql> select table_name,(data_length+index_length)/1024/1024 length,engine,data_free
    -> from information_schema.tables
    -> where table_name='student';
+------------+-------------+--------+-----------+
| table_name | length      | engine | data_free |
+------------+-------------+--------+-----------+
| student    | 72.14062500 | InnoDB |   4194304 |
+------------+-------------+--------+-----------+
1 row in set (0.01 sec)

[root@cos7-jiang test]# ll -h student.ibd
-rw-rw----. 1 mysql mysql 76M Dec 12 13:53 student.ibd

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

2、通过系统表tables的data_free字段看表碎片

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来统计数据库中有哪些表产生了碎片空间:

mysql> select table_name,table_schema,engine,table_rows,data_length+index_length length,data_free
    -> from information_schema.tables
    -> where data_free !=0
    -> and table_schema not in('information_schema','mysql','performance_schema');
+------------+--------------+--------+------------+----------+-----------+
| table_name | table_schema | engine | table_rows | length   | data_free |
+------------+--------------+--------+------------+----------+-----------+
| student    | test         | InnoDB |    1075752 | 75644928 |   4194304 |
+------------+--------------+--------+------------+----------+-----------+

data_free值可以反映出表的碎片空间大小。上面student表data_free显示4M,与上一个方式计算出的碎片大小近似吻合。

三、清理表碎片

一般通过optimize命令清理碎片,不过optimize命令对共享表空间不起作用。
对于mysql5.6,如果执行optimize table tb_name优化innodb表可能会报如下信息:

mysql> optimize table jiang;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| test.jiang | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.jiang | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+

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

四、optimize操作介绍

mysql5.6的官方文档在13.7.2.4小节对optimize操作有详细的介绍。optimize table命令的作用是重新组织表数据和关联索引数据的物理存储,以减小存储空间并提高访问表时的I/O效率;命令主要作用于innodb、myisam和archive引擎表,而命令对表所做的实际更改取决于该表使用的存储引擎。
·innodb引擎下的optimize操作
对于innodb表,optimize table操作实际映射为alter table ... force操作,当对innodb表执行optimize操作时可能会出现下面的提示信息:

mysql> optimize table jiang;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| test.jiang | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.jiang | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+

这实际上已经对表做了优化,第一步是提示optimize操作不适用该类型表,第二步是映射为alter table操作执行并成功。
在mysql5.6.17之前,optimize操作没有使用online DDL,因此整个操作期间会锁表,表上不允许有DML操作;
从mysql5.6.17开始,对于常规的和分区的innodb表,optimize操作使用online DDL,这样只会在操作的准备阶段和提交阶段锁住DML操作,大大提高了并发性。

说明:
1、对于写比较频繁的表,容易产生碎片问题,但也不用经常进行清理,一般每周或者每月一次就可以了;
2、OPTIMIZE TABLE只对MyISAM,BDB和InnoDB表起作用,尤其是MyISAM表的作用最为明显。此外,并不是所有表都需要进行碎片整理,一般只需要对包含可变长度的文本数据类型的表进行整理即可。

网友评论

登录后评论
0/500
评论
未央&
+ 关注
所属云栖号: 袋鼠云技术团队