©2016-2024 Matools All rights reserved,
粤ICP备17059708号
一、表碎片的产生
对于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表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含可变长度的文本数据类型的表进行整理即可。
热门工具 换一换