MySQL InnoDB下關於MVCC的一個問題的分析

這個是網友++C++在群里問的一個關於MySQL的問題，本篇文章實驗測試環境為MySQL 5.6.20，事務隔離級別為REPEATABLE-READ ，在演示問題前，我們先準備測試環境。準備一個測試表test以及一個存儲過程迴圈往test表裡面插入記錄。

CREATE TABLE test

(

  `id` int(11) primary key not null,

  `name` char(255) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

delimiter &&

drop procedure if exists prc_insert;

create procedure prc_insert(in  cnt int)

begin

declare i int;

set i=1;

while i < cnt do

    insert into test(id, name) select i,  CONCAT('name',i) from dual;

    set i = i+1;

end while;

end &&

delimiter ;

線上程ID為14的會話中，開啟事務，然後執行查詢test的SQL語句

mysql> select connection_id() from dual;

+-----------------+

| connection_id() |

+-----------------+

|              14 |

+-----------------+

1 row in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;

然後線上程ID為12的會話中，迴圈往表test裡面插入1000000記錄

mysql> select connection_id() from dual;

+-----------------+

| connection_id() |

+-----------------+

|              12 |

+-----------------+

1 row in set (0.00 sec)

mysql> call prc_insert(1000000);

Query OK, 1 row affected (8 min 32.11 sec)

在執行迴圈插入的這段時間里（SQL執行需要幾分鐘時間），我們線上程ID 為14的會話中反覆執行select * from test這個SQL語句，你會發現該SQL的執行時間變長。那麼引起SQL語句執行時間變長的原因是什麼呢？ 如何解釋得通呢？

剛開始討論的時候，以為MySQL會像ORACLE那樣會在UNDO的回滾段中產生大量UNDO記錄，最後導致SQL語句會像ORACLE那樣產生額外的一致性讀，產生額外的IO，從而導致執行時間變長。 後面測試發現，其實對於MySQL而言，INSERT操作在事務提交前只對當前事務可見，因此產生的Undo日誌可以在事務提交後直接刪除，而此處使用是自動提交模式。用“MySQL技術內幕：InnoDB存儲引擎”裡面提供的腳本py_innodb_page_info.py測試驗證。也是確實如此（UNDO日誌的大小變化很小，時而增長，時而變小）。其實MySQL裡面多版本併發控制（MVCC）的實現機制跟Oracle還是不同的。不能生搬硬套Oracle下的那套理論。

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3224

File Segment inode: 6

B-tree Node: 142

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3223

File Segment inode: 6

B-tree Node: 143

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3213

File Segment inode: 5

B-tree Node: 154

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 1

Freshly Allocated Page: 1326

Undo Log Page: 3205

File Segment inode: 5

B-tree Node: 162

File Space Header: 2

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1

Total number of page: 4864:

Insert Buffer Free List: 32

Insert Buffer Bitmap: 1

System Page: 130

Transaction system Page: 2

Freshly Allocated Page: 1326

Undo Log Page: 3240

File Segment inode: 5

B-tree Node: 127

File Space Header: 1

其實InnoDB的多版本併發控制（MVCC），“高性能MySQL”這本書中有這麼一段描述：

InnoDB的MVCC，是通過每行記錄後面保存的兩個隱藏的列來實現的