optimize 回收表空間的一些說明

optimize指令回收表空間的說明

線上伺服器，有張大表需要用pt-archiver根據時間劃分歸檔大量資料到另一個新表中。原先200G的表，在歸檔完成後，du -hs 顯示依然是200G的大小，删除了大量的行記錄但是實際上空間是不會釋放的。

這種情況下，我們就要使用optimize指令重建表以達到釋放表空間的目的。

（好像是從5.6.6之後，optimize不鎖表了，但是optimize操作會進行rebuild表操作，要確定磁盤剩餘空間足夠存放新表的大小，不然操作會失敗）

另外，如果在主庫執行optimize table會造成從庫延遲，這種情況下，可以使用 optiminze no_write_to_binlog table xxxx ; 這樣就不會把optimize操作寫入binlog。主庫執行完後，再到從庫執行optimize table操作。

模拟過程如下：

use test;

CREATE TABLE `t` (

  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `b` char(10) DEFAULT NULL,

  PRIMARY KEY (`a`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

insert into t (b) select 'aaaaaaa';

insert into t(b) select b from t;    多執行幾次這個指令，造出大量的行資料

然後使用py_innodb_page_info分析page，如下，可以看到存了資料的page很多【下圖紅色部分】

[root@master /root/py_innodb_page_type ]#./py_innodb_page_info.py -v /data/mysql/test/t.ibd

page offset 00000000, page type <FileSpace Header>

page offset 00000001, page type <Insert BufferBitmap>

page offset 00000002, page type <FileSegment inode>

page offset 00000003, page type<B-tree Node>, page level <0000>

page offset 00000004, page type<B-tree Node>, page level <0000>

page offset 00000005, page type<B-tree Node>, page level <0000>

page offset 00000006, page type<B-tree Node>, page level <0000>

page offset 00000007, page type<B-tree Node>, page level <0000>

page offset 00000008, page type<B-tree Node>, page level <0000>

page offset 00000009, page type<B-tree Node>, page level <0000>

page offset 0000000a, page type<B-tree Node>, page level <0000>

page offset 0000000b, page type<B-tree Node>, page level <0000>

page offset 0000000c, page type<B-tree Node>, page level <0000>

page offset 0000000d, page type<B-tree Node>, page level <0000>

page offset 0000000e, page type<B-tree Node>, page level <0000>

page offset 0000000f, page type<B-tree Node>, page level <0000>

page offset 00000010, page type<B-tree Node>, page level <0000>

page offset 00000000, page type <Freshly AllocatedPage>

page offset 00000000, page type <FreshlyAllocated Page>

Total number of page: 19:

Freshly Allocated Page: 2

Insert Buffer Bitmap: 1

File Space Header: 1

B-tree Node: 14

File Segment inode: 1

然後大量删除資料

delete from test.t where a>100;  開始删除大量的資料(隻保留100條記錄，確定資料應該在第一個資料頁存的下）

然後用hexdump去看下innodb的第二個page資訊

hexdump -C -s 65536 -n 16384 /data/mysql/test/t.ibd   發現這個page的資料還是很多，它們并沒有被真正的删除 (實際上當一條記錄被删除後，該空間隻是标記為空閑了，它會被加入到空間連結清單裡面)

### hexdump指令說明：

## -s 從啥位置開始取資料，-n 取出多少bytes的資料。 

## 因為每個page 16k。InnoDB前3個page是存放其它資料的。第一個data page是從16*1024*3=49152位置開始的。第二個data page是從16*1024*4=65536開始的。

重建下test.t表試試效果：

root@localhost [test]> optimize table t;

再次使用py_innodb_page_info分析page，如下，可以看到page少了很多【下圖紅色部分】，基本上都被回收了。

page offset 00000001, page type <InsertBuffer Bitmap>

Total number of page: 6:

B-tree Node: 1

然後再用hexdump去看下innodb的第二個page資訊，發現這個page的資料已經全部是0了，是一個空白的page

[root@master /tmp ]# hexdump -C -s 65536 -n16384 /data/mysql/test/t.ibd    

00010000 00 00 00 00 00 00 00 00  00 00 0000 00 00 00 00  |................|

*

00014000

本文轉自 lirulei90 51CTO部落格，原文連結：http://blog.51cto.com/lee90/1954049，如需轉載請自行聯系原作者