?2016-2026 Matools All rights reserved,
粵ICP備17059708號
一、表碎片的產(chǎn)生
對于mysql表數(shù)據(jù)，當(dāng)你delete掉很多數(shù)據(jù)時(shí)，這些數(shù)據(jù)占用的磁盤空間可能并不會立刻被回收；比如一張表有10G的數(shù)據(jù)，delete掉1G數(shù)據(jù)后，再查看表ibd文件會發(fā)現(xiàn)文件大小可能還是10G；如果這個表有insert操作的話，那么mysql就會優(yōu)先考慮能不能將新數(shù)據(jù)存儲到空白空間上，容易出現(xiàn)這樣的情況：某個空白空間的大小是2MB，新插入一條數(shù)據(jù)大小是1.5MB并存儲到該空白空間上，這時(shí)就會產(chǎn)生更小的空白空間，而這種更小的空白空間更難被利用，如果像這種碎片非常多，就會比較浪費(fèi)資源而且降低表磁盤I/O性能。
對于頻繁地update操作，也很容易產(chǎn)生碎片問題。比如對于可變長字段，如varchar、text、blob等字段，如果update操作將數(shù)據(jù)大小改小，那么也會產(chǎn)生碎片問題。
mysql目前比較常用的引擎是innodb和myisam，這兩種引擎下都有可能產(chǎn)生碎片，碎片的產(chǎn)生和消除都是隨機(jī)的，而碎片越多會給查詢掃描工作帶來越大的影響。
二、查看表碎片的方式
1、data_length+index_length與ibd文件大小的比較
mysql5.5默認(rèn)是共享表空間，從5.6開始默認(rèn)是獨(dú)立表空間，每張表有自己的文件空間。查看方式就是看數(shù)據(jù)文件大小和表數(shù)據(jù)量大小的差異：可以先在數(shù)據(jù)庫中通過系統(tǒng)表information_schema.tables或者“show
table status like ‘tb’
”語句計(jì)算出data_length+index_length的值，再到操作系統(tǒng)上查看對應(yīng)表的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
根據(jù)系統(tǒng)表計(jì)算出student表數(shù)據(jù)為72MB，查看ibd文件大小為76MB，碎片空間大概有4MB左右，不算太多。
2、通過系統(tǒng)表tables的data_free字段看表碎片
mysql的系統(tǒng)表information_schema.tables中記錄著每張表的數(shù)據(jù)、索引大小，行數(shù)等重要信息，主要字段信息如下：
table_schema：表所在數(shù)據(jù)庫名
table_name：表名
engine：表的存儲引擎
tables_rows：表數(shù)據(jù)行數(shù)
data_length：數(shù)據(jù)長度，即表數(shù)據(jù)大小，單位字節(jié)
index_length：索引長度，即表索引大小，單位字節(jié)
data_free：已分配但未使用的空間大小，單位字節(jié)，可以認(rèn)為是碎片空間
通過data_free字段可以查出數(shù)據(jù)庫中有哪些表產(chǎn)生了碎片，data_length+index_length值就是表數(shù)據(jù)量總大小(拿這個求和值與表數(shù)據(jù)文件大小比較，得到的差值往往與data_free值不一樣，不知道為什么)。
可以用下面的SQL來統(tǒng)計(jì)數(shù)據(jù)庫中有哪些表產(chǎn)生了碎片空間：
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，與上一個方式計(jì)算出的碎片大小近似吻合。
三、清理表碎片
一般通過optimize命令清理碎片，不過optimize命令對共享表空間不起作用。
對于mysql5.6，如果執(zhí)行optimize table tb_name優(yōu)化innodb表可能會報(bào)如下信息：
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表的優(yōu)化，可以用alter table tb_name
engine=innodb的形式優(yōu)化，對于myisam表的優(yōu)化可以直接使用optimize。
四、optimize操作介紹
mysql5.6的官方文檔在13.7.2.4小節(jié)對optimize操作有詳細(xì)的介紹。optimize
table命令的作用是重新組織表數(shù)據(jù)和關(guān)聯(lián)索引數(shù)據(jù)的物理存儲，以減小存儲空間并提高訪問表時(shí)的I/O效率；命令主要作用于innodb、myisam和archive引擎表，而命令對表所做的實(shí)際更改取決于該表使用的存儲引擎。
·innodb引擎下的optimize操作
對于innodb表，optimize table操作實(shí)際映射為alter table ...
force操作，當(dāng)對innodb表執(zhí)行optimize操作時(shí)可能會出現(xiàn)下面的提示信息：
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 |
+------------+----------+----------+-------------------------------------------------------------------+
這實(shí)際上已經(jīng)對表做了優(yōu)化，第一步是提示optimize操作不適用該類型表，第二步是映射為alter table操作執(zhí)行并成功。
在mysql5.6.17之前，optimize操作沒有使用online DDL，因此整個操作期間會鎖表，表上不允許有DML操作；
從mysql5.6.17開始，對于常規(guī)的和分區(qū)的innodb表，optimize操作使用online
DDL，這樣只會在操作的準(zhǔn)備階段和提交階段鎖住DML操作，大大提高了并發(fā)性。
說明：
1、對于寫比較頻繁的表，容易產(chǎn)生碎片問題，但也不用經(jīng)常進(jìn)行清理，一般每周或者每月一次就可以了；
2、OPTIMIZE
TABLE只對MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，并不是所有表都需要進(jìn)行碎片整理，一般只需要對包含可變長度的文本數(shù)據(jù)類型的表進(jìn)行整理即可。
熱門工具 換一換