MySQL中的undo截斷(r11筆記第89天)

MySQL中的undo截斷還是一個很不錯的特性。這讓我想起了很久以前看到一個諾大的ibdata,但是卻拿它無能為力，想把它收縮唯一的辦法就是重建或者重構資料。

   Oracle用得久了，總會有一些想法，看起來很平常的技術怎麼在MySQL中卻無能為力。當然這個念頭也有些日子了。

   MySQL 5.6中把undo做了剝離，可以指定單獨的undo表空間，但是要收縮階段還是無能為力，這個也算是一個過渡的特性吧，到了MySQL 5.7中，這個功能就可以說是上了正道了，我們可以截斷，化被動為主動，這種方式就很好。

    而如果要展望這個特性，我覺得還可以持續改進，就是可以線上修改，切換undo等。

    要實現這個階段功能，其實還需要花點功夫，那就是在初始化的時候就完成這些基礎配置，否則會收到下面這樣有些模糊的提示資訊。

2017-02-28 22:39:48 7fedca8127e0 InnoDB: Expected to open 1 undo tablespaces but was able
2017-02-28 22:39:48 7fedca8127e0 InnoDB: to find only 0 undo tablespaces.
2017-02-28 22:39:48 7fedca8127e0 InnoDB: Set the innodb_undo_tablespaces parameter to the
2017-02-28 22:39:48 7fedca8127e0 InnoDB: correct value and retry. Suggested value is 0所以我們打算初始化一個全新的庫來做一個簡單的測試。

my.cnf的內容如下，你可以自己根據需要指定也可以。

my.cnf
[client]
socket = /home/mysql/mysql.sock

[mysql]
socket = /home/mysql/mysql.sock
default-character-set = utf8

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /home/mysql
port = 3306
socket = /home/mysql/mysql.sock
pid-file = /home/mysql/mysql.pid
max_allowed_packet = 32M
ft_min_word_len = 4
event_scheduler = 1
explicit_defaults_for_timestamp=true
tmpdir = /dev/shm
character-set-server = utf8

#innodb_undo_directory=/data/undolog
innodb_undo_tablespaces=4
innodb_undo_logs=128
innodb_max_undo_log_size=200M
innodb_purge_rseg_truncate_frequency
innodb_undo_log_truncate=1
重點就是最後的幾個引數了。

先初始化一下資料字典，

mysqld  --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql然後配置使得MySQL服務啟動

service mysql start開啟檔案目錄，就會赫然看到下面的幾個undo檔案，因為引數

innodb_undo_tablespaces為4，所以會有4個檔案。innodb_undo_logs預設是128個，至少是35個，官網也有詳細的解釋。而innodb_undo_directory的目錄則預設按照資料目錄來取得，所以我索性去掉了。4個Undo檔案都是10M，也就是初始大小。

drwxr-x--- 2 mysql mysql     8192 Feb 28 23:09 performance_schema
drwxr-x--- 2 mysql mysql     8192 Feb 28 23:09 sys
-rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo001
-rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo002
-rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo003
-rw-r----- 1 mysql mysql 10485760 Feb 28 23:09 undo004我們初始化資料，然後插入一些資料。

mysql> create table test_undo(id int,name varchar(30));
mysql> insert into test_undo values(1,'a');
mysql> insert into test_undo values(2,'b');
insert可以反覆執行，資料就是指數級的增長，事務也會逐漸變大，大概在200萬資料量的時候，undo的分佈如下：

-rw-r----- 1 mysql mysql 13631488 Feb 28 23:16 undo001
-rw-r----- 1 mysql mysql 22020096 Feb 28 23:16 undo002
-rw-r----- 1 mysql mysql 10485760 Feb 28 23:16 undo003
-rw-r----- 1 mysql mysql 10485760 Feb 28 23:16 undo004資料在1600萬左右的時候，undo檔案的大小如下：

-rw-r----- 1 mysql mysql  13631488 Feb 28 23:20 undo001
-rw-r----- 1 mysql mysql  22020096 Feb 28 23:20 undo002
-rw-r----- 1 mysql mysql 150994944 Feb 28 23:20 undo003
-rw-r----- 1 mysql mysql  75497472 Feb 28 23:20 undo004這個時候我們開始測試一下截斷的部分，和一個引數密切相關，那就是innodb_purge_rseg_truncate_frequency，我們可以為了測試，適當設定小一些，能夠馬上看到效果，比如我設定為20

mysql> set global innodb_purge_rseg_truncate_frequency=20;然後我繼續開啟一個很大的事務，插入千萬資料，undo的檔案就會暴增，當然因為最大的事務佔用了一個undo檔案，那個檔案還是會持續增大，儘管超過了設定的閾值。

-rw-r----- 1 mysql mysql  13631488 Feb 28 23:33 undo001
-rw-r----- 1 mysql mysql  22020096 Feb 28 23:33 undo002
-rw-r----- 1 mysql mysql 150994944 Feb 28 23:33 undo003
-rw-r----- 1 mysql mysql 293601280 Feb 28 23:33 undo004然後我們使點小技巧，delete幾條資料，觸發截斷的臨界點。

mysql> delete from test_undo limit 10;再次檢視，undo的檔案就會收縮，當然你可以清晰的看到，不是所有的，因為和事務大小也有關係。

-rw-r----- 1 mysql mysql  13631488 Feb 28 23:34 undo001
-rw-r----- 1 mysql mysql  22020096 Feb 28 23:34 undo002
-rw-r----- 1 mysql mysql 150994944 Feb 28 23:34 undo003
-rw-r----- 1 mysql mysql  10485760 Feb 28 23:34 undo004