關於InnoDB中mvcc和覆蓋索引查詢的困惑？

01-13

InnoDB中如果設置資料庫隔離級別是RR的話，會用到InnoDB的mvcc機制，我的理解mvcc是在每行記錄的後面加了兩列，分別表示更新版本和刪除版本，這樣的話普通的快照讀就不需要加鎖，直接判斷版本號，只讀取更新版本小於當前事務版本以及刪除版本為空或小於當前事務版本的，我的理解這兩列版本是在聚簇索引裡面存放，辣么問題來了，覆蓋索引是因為只走索引而不需要查詢聚簇索引所以速度很快，但是只走索引怎麼檢查版本號，不檢查版本號怎麼保證幻行的發生，亦或是索引本身也有版本號？

來天坑了，坑人呀，報錯之後表格沒了

首先我們來了解幾個結構

view的結構

下面解析一下每個欄位的含義：

low_limit_no：

low_limit_id：trx-&>id &>=low_limit_id的事務，當前view不可見

up_limit_id：trx-&>id&

n_trx_ids：

trx_ids：系統中所有活躍事務id列表

view_list：view列表

聚集索引行結構

二級索引記錄結構

在二級索引頁面中，有DB_MAX_ID：標識修改索引頁面的最大事務ID

2）插入和更新時候的操作

初始情況

–create table and index

create table test (id int , comment char(50), primary key(id), key test(comment)) engine=InnoDB;

–Insert

insert into test values(1, 『aaa』);

insert into test values(2, 『bbb』);

1.
執行如下兩個sql

update test set id = 9 where id = 1;

update test set comment =
『ccc』 where id = 9;

索引結構變為如下：

1.更新主鍵，聚簇索引/二級索引均無法進行in place update，均會產生兩個版本；

2.更新非主鍵索引，聚簇索引可以in place
update；二級索引產生兩個版本；

3.聚簇索引記錄undo，二級索引不記錄undo；

4.更新聚簇索引，記錄舊版本會進入rollback
segment undo page

5.更新二級索引，同時需要判斷是否修改索引頁面的MAX_TRX_ID

6.屬於同一事務的undo記錄，在undo page中保存逆向指針

3）查詢（查詢主鍵索引、二級索引）

3.1 查詢主鍵索引

select
* from test where id = 1;

查詢的結果和當前查詢所屬的read view 的值有關係，read view值不同，產生的結果也不同

對於read view0，db_trx_id(1811) &< view.up_limit_id，則當前記錄對view可見，即該記錄刪除可見，查詢結果為空

對於read view1， db_trx_id(1811) &>
view.low_limit_id，則當前記錄對view不可見，根據db_roll_ptr回溯歷史記錄，trx_id=1809，對1809在view中判斷可見性，1809通過up_limit)id和low_limit_id不能夠判斷可見性，則在trx_ids[]中查詢，發現不在trx_ids中，則該記錄可見，返回（1，aaa）

對於read
view2， db_trx_id(1811)通過up_limit)id和low_limit_id不能夠判斷可見性，則在trx_ids[]中查詢，發現在trx_ids中，則該記錄不可見，返回結果為空

總結：

通過主鍵查找記錄，需要配合read_view，記錄DATA_TRX_ID，記錄DATA_ROLL_PTR指針共同判斷。
read_view用於判斷當前記錄是否可見(判斷DATA_TRX_ID)。DATA_ROLL_PTR用於將當前記錄回滾到前一版本。

3.2 查詢二級索引

select
* from test where comment = "aaa";

查詢的結果和當前查詢所屬的read view 的值有關係，read view值不同，產生的結果也不同，此時二級索引中的MAX_TRX_ID派上了用場

對於read view0，
MAX_TRX_ID(1816)&

對於read view2， MAX_TRX_ID(1816)根據up_limit_id和low_limit_id不能判斷

其可見性，則依次遍歷每一條記錄來判斷可見性，對於第一條記錄

，反查到聚簇索引記錄

，對該調記錄判斷其可見性，1811通過low_limit_id和up_limit_id判斷不了，且不在trx_ids中，則該條記錄可見，即aaa已經刪除，

對於第二條記錄

，反查到聚簇索引記錄

，對該調記錄判斷其可見性，1816通過low_limit_id和up_limit_id判斷不了，且在trx_ids中，則該條記錄不可見，通過db_roll_ptr進行判斷，1811通過low_limit_id和up_limit_id判斷不了，且不在trx_ids中，則該條記錄可見，返回（9，aaa），

對於第三條記錄

，反查到聚簇索引記錄

，對該調記錄判斷其可見性，1816通過low_limit_id和up_limit_id判斷不了，且在trx_ids中，則該條記錄不可見，通過db_roll_ptr進行判斷，1811通過low_limit_id和up_limit_id判斷不了，且不在trx_ids中，則該條記錄可見，值為aaa，同時發現該值aaa和二級索引中的值ccc不相同，則該條記錄不返回

綜上所述最後返回（9，aaa）

總結：

1.二級索引的可見性判斷需要通過聚簇索引來完成；

騷年你的理解沒錯二級索引得去找聚簇索引實現RR級別的事務（覆蓋也沒用）所以很耽誤時間。如果由於這點使得效率很低的話，設計表，選擇主鍵的時候，可以多想想事務能不能與主鍵相關

只有聚簇索引葉子節點上的記錄，才有最近修改的事務id和回滾指針(非葉子節點和二級索引的所有節點都沒有)，所以二級索引的可見性只能通過聚簇索引。