1. MySQL 5.5時(shí)代的undo log

我們提供的服務(wù)有：成都網(wǎng)站制作、網(wǎng)站設(shè)計(jì)、外貿(mào)網(wǎng)站建設(shè)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、易縣ssl等。為近千家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的易縣網(wǎng)站制作公司

    在MySQL5.5以及之前，大家會(huì)發(fā)現(xiàn)隨著數(shù)據(jù)庫(kù)上線時(shí)間越來(lái)越長(zhǎng)，ibdata1文件（即InnoDB的共享表空間，或者系統(tǒng)表空間）會(huì)越來(lái)越大，這會(huì)造成2個(gè)比較明顯的問(wèn)題：

（1）磁盤(pán)剩余空間越來(lái)越小，到后期往往要加磁盤(pán)；

（2）物理備份時(shí)間越來(lái)越長(zhǎng)，備份文件也越來(lái)越大。

這是怎么回事呢？

原因除了數(shù)據(jù)量自然增長(zhǎng)之外，在MySQL5.5以及之前，InnoDB的undo log也是存放在ibdata1里面的。一旦出現(xiàn)大事務(wù)，這個(gè)大事務(wù)所使用的undo log占用的空間就會(huì)一直在ibdata1里面存在，即使這個(gè)事務(wù)已經(jīng)關(guān)閉。

那么問(wèn)題來(lái)了，有辦法把上面說(shuō)的空閑的undo log占用的空間從ibdata1里面清理掉嗎？答案是沒(méi)有直接的辦法，只能全庫(kù)導(dǎo)出sql文件，然后重新初始化mysql實(shí)例，再全庫(kù)導(dǎo)入。

2. MySQL 5.6時(shí)代的undo log

MySQL 5.6增加了參數(shù)innodb_undo_directory、innodb_undo_logs和innodb_undo_tablespaces這3個(gè)參數(shù)，可以把undo log從ibdata1移出來(lái)單獨(dú)存放。

下面對(duì)這3個(gè)參數(shù)做一下解釋?zhuān)?/p>

（1）innodb_undo_directory，指定單獨(dú)存放undo表空間的目錄，默認(rèn)為.（即datadir），可以設(shè)置相對(duì)路徑或者絕對(duì)路徑。該參數(shù)實(shí)例初始化之后雖然不可直接改動(dòng)，但是可以通過(guò)先停庫(kù)，修改配置文件，然后移動(dòng)undo表空間文件的方式去修改該參數(shù)；

（2）innodb_undo_tablespaces，指定單獨(dú)存放的undo表空間個(gè)數(shù)，例如如果設(shè)置為3，則undo表空間為undo001、undo002、undo003，每個(gè)文件初始大小默認(rèn)為10M。該參數(shù)我們推薦設(shè)置為大于等于3，原因下文將解釋。該參數(shù)實(shí)例初始化之后不可改動(dòng)；

（3）innodb_undo_logs，指定回滾段的個(gè)數(shù)（早期版本該參數(shù)名字是innodb_rollback_segments），默認(rèn)128個(gè)。每個(gè)回滾段可同時(shí)支持1024個(gè)在線事務(wù)。這些回滾段會(huì)平均分布到各個(gè)undo表空間中。該變量可以動(dòng)態(tài)調(diào)整，但是物理上的回滾段不會(huì)減少，只是會(huì)控制用到的回滾段的個(gè)數(shù)。

實(shí)際使用方面，在初始化實(shí)例之前，我們只需要設(shè)置innodb_undo_tablespaces參數(shù)（建議大于等于3）即可將undo log設(shè)置到單獨(dú)的undo表空間中。如果需要將undo log放到更快的設(shè)備上時(shí)，可以設(shè)置innodb_undo_directory參數(shù)，但是一般我們不這么做，因?yàn)楝F(xiàn)在SSD非常普及。innodb_undo_logs可以默認(rèn)為128不變。

3. MySQL 5.7時(shí)代的undo log

那么問(wèn)題又來(lái)了，undo log單獨(dú)拆出來(lái)后就能縮小了嗎？MySQL 5.7引入了新的參數(shù)，innodb_undo_log_truncate，開(kāi)啟后可在線收縮拆分出來(lái)的undo表空間。在滿足以下2個(gè)條件下，undo表空間文件可在線收縮：

（1）innodb_undo_tablespaces>=2。因?yàn)閠runcate undo表空間時(shí)，該文件處于inactive狀態(tài)，如果只有1個(gè)undo表空間，那么整個(gè)系統(tǒng)在此過(guò)程中將處于不可用狀態(tài)。為了盡可能降低truncate對(duì)系統(tǒng)的影響，建議將該參數(shù)最少設(shè)置為3；

（2）innodb_undo_logs>=35（默認(rèn)128）。因?yàn)樵贛ySQL 5.7中，第一個(gè)undo log永遠(yuǎn)在系統(tǒng)表空間中，另外32個(gè)undo log分配給了臨時(shí)表空間，即ibtmp1，至少還有2個(gè)undo log才能保證2個(gè)undo表空間中每個(gè)里面至少有1個(gè)undo log；

滿足以上2個(gè)條件后，把innodb_undo_log_truncate設(shè)置為ON即可開(kāi)啟undo表空間的自動(dòng)truncate，這還跟如下2個(gè)參數(shù)有關(guān)：

（1）innodb_max_undo_log_size，undo表空間文件超過(guò)此值即標(biāo)記為可收縮，默認(rèn)1G，可在線修改；

（2）innodb_purge_rseg_truncate_frequency,指定purge操作被喚起多少次之后才釋放rollback segments。當(dāng)undo表空間里面的rollback segments被釋放時(shí)，undo表空間才會(huì)被truncate。由此可見(jiàn)，該參數(shù)越小，undo表空間被嘗試truncate的頻率越高。

4. MySQL 5.7的undo表空間的truncate示例

（1） 首先確保如下參數(shù)被正確設(shè)置：

# 為了實(shí)驗(yàn)方便，我們減小該值

innodb_max_undo_log_size = 100M

innodb_undo_log_truncate = ON

innodb_undo_logs = 128  

innodb_undo_tablespaces = 3

# 為了實(shí)驗(yàn)方便，我們減小該值

innodb_purge_rseg_truncate_frequency = 10

（2） 創(chuàng)建表：

mysql> create table t1( id int primary key auto_increment, name varchar(200));

Query OK, 0 rows affected (0.13 sec)

（3）插入測(cè)試數(shù)據(jù)

mysql> insert into t1(name) values(repeat('a',200));

mysql> insert into t1(name) select name from t1;

mysql> insert into t1(name) select name from t1;

mysql> insert into t1(name) select name from t1;

mysql> insert into t1(name) select name from t1;

這時(shí)undo表空間文件大小如下，可以看到有一個(gè)undo文件已經(jīng)超過(guò)了100M：

-rw-r----- 1 mysql mysql  13M Feb 17 17:59 undo001

-rw-r----- 1 mysql mysql 128M Feb 17 17:59 undo002

-rw-r----- 1 mysql mysql  64M Feb 17 17:59 undo003

此時(shí)，為了，讓purge線程運(yùn)行，可以運(yùn)行幾個(gè)delete語(yǔ)句：

mysql> delete from t1 limit 1;

mysql> delete from t1 limit 1;

mysql> delete from t1 limit 1;

mysql> delete from t1 limit 1;

再查看undo文件大?。?/p>

-rw-r----- 1 mysql mysql  13M Feb 17 18:05 undo001

-rw-r----- 1 mysql mysql  10M Feb 17 18:05 undo002

-rw-r----- 1 mysql mysql  64M Feb 17 18:05 undo003

可以看到，超過(guò)100M的undo文件已經(jīng)收縮到10M了。