這篇文章主要介紹了MySQL下使用Inplace和Online方式創建索引的教程,針對InnoDB為存儲引擎的情況,需要的朋友可以參考下

MySQL各版本，對于add Index的處理方式是不同的，主要有三種：

(1)Copy Table方式

這是InnoDB最早支持的創建索引的方式。顧名思義，創建索引是通過臨時表拷貝的方式實現的。

新建一個帶有新索引的臨時表，將原表數據全部拷貝到臨時表，然后Rename，完成創建索引的操作。

這個方式創建索引，創建過程中，原表是可讀的。但是會消耗一倍的存儲空間。

(2)Inplace方式

這是原生MySQL 5.5，以及innodb_plugin中提供的創建索引的方式。所謂Inplace，也就是索引創建在原表上直接進行，不會拷貝臨時表。相對于Copy Table方式，這是一個進步。

Inplace方式創建索引，創建過程中，原表同樣可讀的，但是不可寫。

(3)Online方式

這是MySQL 5.6.7中提供的創建索引的方式。無論是Copy Table方式，還是Inplace方式，創建索引的過程中，原表只能允許讀取，不可寫。對應用有較大的限制，因此MySQL最新版本中，InnoDB支持了所謂的Online方式創建索引。

InnoDB的Online Add Index，首先是Inplace方式創建索引，無需使用臨時表。在遍歷聚簇索引，收集記錄并插入到新索引的過程中，原表記錄可修改。而修改的記錄保存在Row Log中。當聚簇索引遍歷完畢，并全部插入到新索引之后，重放Row Log中的記錄修改，使得新索引與聚簇索引記錄達到一致狀態。

與Copy Table方式相比，Online Add Index采用的是Inplace方式，無需Copy Table，減少了空間開銷;與此同時，Online Add Index只有在重放Row Log最后一個Block時鎖表，減少了鎖表的時間。

與Inplace方式相比，Online Add Index吸收了Inplace方式的優勢，卻減少了鎖表的時間。

1.Inplace add Index

測試表

1. create table t1 (a int primary key, b int)engine=innodb; 
2.  
3. insert into t1 values (1,1),(2,2),(3,3),(4,4); 

Inplace Add Index處理流程

SQL

1. alter table t1 add index idx_t1_b(b); 

處理流程

1. sql_table.cc::mysql_alter_table(); 
2.  
3. // 判斷當前操作是否可以進行Inplace實現，不可進行Inplace Alter的包括： 
4.  
5. // 1. Auto Increment字段修改； 
6.  
7. // 2. 列重命名； 
8.  
9. // 3. 行存儲格式修改；等 
10.  
11. mysql_compare_tables() -> ha_innobase::check_if_incompatible_data(); 
12.  
13. // Inplace創建索引第一階段(主要階段) 
14.  
15. handler0alter.cc::add_index(); 
16.  
17. … 
18.  
19. // 創建索引數據字典 
20.  
21. row0merge.c::row_merge_create_index(); 
22.  
23. index = dict_mem_index_create(); 
24.  
25. // 每個索引數據字典上，有一個trx_id，記錄創建此索引的事務 
26.  
27. // 此trx_id有何功能，接著往下看 
28.  
29. index->trx_id = trx_id; 
30.  
31. // 讀取聚簇索引，構造新索引的項，排序并插入新索引 
32.  
33. row0merge.c::row_merge_build_indexes(); 
34.  
35. // 讀取聚簇索引，注意：只讀取其中的非刪除項 
36.  
37. // 跳過所有刪除項，為什么可以這么做？往下看 
38.  
39. row_merge_read_clustered_index(); 
40.  
41. // 文件排序 
42.  
43. row_merge_sort(); 
44.  
45. // 順序讀取排序文件中的索引項，逐個插入新建索引中 
46.  
47. row_merge_insert_index_tuples(); 
48.  
49. // 等待打開當前表的所有只讀事務提交 
50.  
51. sql_base.cc::wait_while_table_is_used(); 
52.  
53. // 創建索引結束，做最后的清理工作 
54.  
55. handler0alter.cc::final_add_index(); 
56.  
57. // Inplace add Index完畢 

Inplace Add Index實現分析

在索引創建完成之后，MySQL Server立即可以使用新建的索引，做查詢。但是，根據以上流程，對我個人來說，有三個疑問點：

索引數據字典上，為何需要維護一個trx_id?

trx_id有何作用?

遍歷聚簇索引讀取所有記錄時，為何可跳過刪除項?

只讀取非刪除項，那么新建索引上沒有版本信息，無法處理原有事務的快照讀;

MySQL Server層，為何需要等待打開表的只讀事務提交?

等待當前表上的只讀事務，可以保證這些事務不會使用到新建索引

根據分析，等待打開表的只讀事務結束較好理解。因為新索引上沒有版本信息，若這些事務使用新的索引，將會讀不到正確的版本記錄。

那么InnoDB是如何處理其他那些在創建索引之前已經開始，但卻一直未提交的老事務呢?這些事務，由于前期為并未讀取當前表，因此不會被等待結束。這些事務在RR隔離級別下，會讀取不到正確的版本記錄，因為使用的索引上并沒有版本信息。

當然，InnoDB同樣考慮到了此問題，并采用了一種比較簡介的處理方案。在索引上維護一個trx_id，標識創建此索引的事務ID。若有一個比這個事務更老的事務，打算使用新建的索引進行快照讀，那么直接報錯。

考慮如下的并發處理流程(事務隔離級別為RR)：

1. session 1: session 2: 
2.  
3. // 此時創建Global ReadView 
4.  
5. select * from t2; 
6.  
7. delete from t1 where b = 1; 
8.  
9. // idx_t1_b索引上，沒有b = 1的項 
10.  
11. alter table t1 add index idx_t1_b(b); 
12.  
13. // 由于ReadView在delete之前獲取 
14.  
15. // 因此b = 1這一項應該被讀取到 
16.  
17. select * from t1 where b = 1; 

當session 1執行最后一條select時，MySQL Optimizer會選擇idx_t1_b索引進行查詢，但是索引上并沒有b = 1的項，使用此索引會導致查詢出錯。那么，InnoDB是如何處理這個情況的呢?

處理流程：

1. … 
2.  
3. ha_innobase::index_init(); 
4.  
5. change_active_index(); 
6.  
7. // 判斷session 1事務的ReadView是否可以看到session 2創建索引的事務 
8.  
9. // 此處，session 2事務當然不可見，那么prebuilt->index_usable = false 
10.  
11. prebuilt->index_usable = row_merge_is_index_usable(readview, index->trx_id); 
12.  
13. … 
14.  
15. ha_innobase::index_read(); 
16.  
17. // 判斷index_usable屬性，此時為false，返回上層表定義修改，查詢失敗 
18.  
19. if (!prebuilt->index_usable) 
20.  
21. return HA_ERR_TABLE_DEF_CHANGED; 

MySQL Server收到InnoDB返回的錯誤之后，會將錯誤報給用戶，用戶會收到以下錯誤：

1. mysql> select * from t1 where b = 1; 

1. ERROR 1412 (HY000): Table definition has changed, please retry transaction 

2.Online add Index

測試表

1. create table t1 (a int primary key, b int)engine=innodb; 
2.  
3. insert into t1 values (1,1),(2,2),(3,3),(4,4); 

Online Add Index處理流程

SQL

1. alter table t1 add index idx_t1_b(b); 

處理流程

1. sql_table.cc::mysql_alter_table(); 
2.  
3. // 1. 判斷當前DDL操作是否可以Inplace進行 
4.  
5. check_if_supported_inplace_alter(); 
6.  
7. … 
8.  
9. // 2. 開始進行Online創建的前期準備工作 
10.  
11. prepare_inplace_alter_table(); 
12.  
13. … 
14.  
15. // 修改表的數據字典信息 
16.  
17. prepare_inplace_alter_table_dict(); 
18.  
19. … 
20.  
21. // 等待InnoDB所有的后臺線程，停止操作此表 
22.  
23. dict_stats_wait_bg_to_stop_using_tables(); 
24.  
25. … 
26.  
27. // Online Add Index區別與Inplace Add Index的關鍵 
28.  
29. // 在Online操作時，原表同時可以讀寫，因此需要 
30.  
31. // 將此過程中的修改操作記錄到row log之中 
32.  
33. row0log.cc::row_log_allocate(); 
34.  
35. row_log_t* log = (row_log_t*)&buf[2 * srv_sort_buf_size]; 
36.  
37. // 標識當前索引狀態為Online創建，那么此索引上的 
38.  
39. // DML操作會被寫入Row Log，而不在索引上進行更新 
40.  
41. dict_index_set_online_status(index, ONLINE_INDEX_CREATION); 
42.  
43. … 
44.  
45. // 3. 開始進行真正的Online Add Index的操作(最重要的流程) 
46.  
47. inplace_alter_table(); 
48.  
49. // 此函數的操作，前部分與Inplace Add Index基本一致 
50.  
51. // 讀取聚簇索引、排序、并插入到新建索引中 
52.  
53. // 最大的不同在于，當插入完成之后，Online Add Index 
54.  
55. // 還需要將row log中的記錄變化，更新到新建索引中 
56.  
57. row0merge.cc::row_merge_build_index(); 
58.  
59. … 
60.  
61. // 在聚簇索引讀取、排序、插入新建索引的操作結束之后 
62.  
63. // 進入Online與Inplace真正的不同之處，也是Online操作 
64.  
65. // 的精髓部分——將這個過程中產生的Row Log重用 
66.  
67. row0log.cc::row_log_apply(); 
68.  
69. // 暫時將新建索引整個索引樹完全鎖住 
70.  
71. // 注意：只是暫時性鎖住，并不是在整個重用Row Log的 
72.  
73. // 過程中一直加鎖(防止加鎖時間過長的優化，如何優化？) 
74.  
75. rw_lock_x_lock(dict_index_get_lock(new_index)); 
76.  
77. … 
78.  
79. // InnoDB Online操作最重要的處理流程 
80.  
81. // 將Online Copy Table中，記錄的Row Log重放到新建索引上 
82.  
83. // 重放Row Log的算法如下： 
84.  
85. // 1. Row Log中記錄的是Online創建索引期間，原表上的DML操作 
86.  
87. // 這些操作包括：ROW_OP_INSERT；ROW_OP_DELETE_MARK; … 
88.  
89.  
90.  
91. // 2. Row Log以Block的方式存儲，若DML較多，那么Row Logs可能 
92.  
93. // 會占用多個Blocks。row_log_t結構中包含兩個指針：head與tail 
94.  
95. // head指針用于讀取Row Log，tail指針用于追加寫新的Row Log； 
96.  
97.  
98.  
99. // 3.在重用Row Log時，算法遵循一個原則：盡量減少索引樹加鎖 
100.  
101. // 的時間(索引樹加X鎖，也意味著表上禁止了新的DML操作) 
102.  
103.  
104.  
105. // 索引樹需要加鎖的場景： 
106.  
107. // (一) 在重用Row Log跨越新的Block時，需要短暫加鎖； 
108.  
109.  
110.  
111. // (二) 若應用的Row Log Block是最后一個Block，那么一直加鎖 
112.  
113. // 應用最后一個Block，由于禁止了新的DML操作，因此此 
114.  
115. // Block應用完畢，新索引記錄與聚簇索引達到一致狀態， 
116.  
117. // 重用階段結束； 
118.  
119.  
120.  
121. // (三) 在應用中間Row Log Block上的row log時，無需加鎖，新的 
122.  
123. // DML操作仍舊可以進行，產生的row log記錄到最后一個 
124.  
125. // Row Log Block之上； 
126.  
127.  
128.  
129. // 4. 如果是創建Unique索引，那么在應用Row Log時，可能會出現 
130.  
131. // 違反唯一性約束的情況，這些情況會被記錄到 
132.  
133. // row_merge_dup_t結構之中 
134.  
135. row_log_apply_ops(trx, index, &dup); 
136.  
137. row_log_apply_op(); 
138.  
139. row_log_apply_op_low(); 
140.  
141. … 
142.  
143. // 將New Index的Online row log設置為NULL， 
144.  
145. // 標識New Index的數據已經與聚簇索引完全一致 
146.  
147. // 在此之后，新的DML操作，無需記錄Row Log 
148.  
149. dict_index_set_online_status(); 
150.  
151. index->online_status = ONLINE_INDEX_COMPLETE; 
152.  
153. index->online_log = NULL; 
154.  
155. rw_lock_x_unlock(dict_index_get_block(new_index)); 
156.  
157. row_log_free(); 
158.  
159. … 
160.  
161. // 4. Online Add Index的最后步驟，做一些后續收尾工作 
162.  
163. commit_inplace_alter_table(); 
164.  
165. … 

Online Add Index實現分析

在看完前面分析的InnoDB 5.6.7-RC版本中實現的基本處理流程之后，個人仍舊遺留了幾個問題，主要的問題有：

Online Add Index是否支持Unique索引?

確切的答案是：支持(不過存在Bug，后面分析)。InnoDB支持Online創建Unique索引。

既然支持，就會面臨Check Duplicate Key的問題。Row Log中如果存在與索引中相同的鍵值怎么處理?怎么檢測是否存在相同鍵值?

InnoDB解決此問題的方案也比較簡介易懂。其維護了一個row_merge_dup_t的數據結構，存儲了在Row log重放過程中遇到的違反唯一性沖突的Row Log。應用完Row Log之后，外部判斷是否存在Unique沖突(有多少Unique沖突，均會記錄)，Online創建Unique索引失敗。

Row Log是什么樣的結構，如何組織的?

在Online Add Index過程中，并發DML產生的修改，被記錄在Row Log中。首先，Row Log不是InnoDB的Redo Log，而是每個正在被Online創建的索引的獨占結構。

Online創建索引，遵循的是先創建索引數據字典，后填充數據的方式。因此，當索引數據字典創建成功之后，新的DML操作就可以讀取此索引，嘗試進行更新。但是，由于索引結構上的status狀態為ONLINE_INDEX_CREATION，因此這些更新不能直接應用到新索引上，而是放入Row Log之中，等待被重放到索引之上。

Row Log中，以Block的方式管理DML操作內容的存放。一個Block的大小為由參數innodb_sort_buffer_size控制，默認大小為1M (1048576)。初始化階段，Row Log申請兩個這樣的Block。

在Row Log重放的過程中，到底需要多久的鎖表時間?

前面的流程分析中，也提到了鎖表的問題(內部為鎖新建索引樹的操作實現)。

在重放Row log時，有兩個情況下，需要鎖表：

情況一：在使用完一個Block，跳轉到下一個Block時，需要短暫鎖表，判斷下一個Block是否為Row Log的最后一個Block。若不是最后一個，跳轉完畢后，釋放鎖;使用Block內的row log不加鎖，用戶DML操作仍舊可以進行。

情況二：在使用最后一個Block時，會一直持有鎖。此時不允許新的DML操作。保證最后一個Block重放完成之后，新索引與聚簇索引記錄達到一致狀態。

綜上分析兩個鎖表情況，情況二會持續鎖表，但是由于也只是最后一個Block，因此鎖表時間也較短，只會短暫的影響用戶操作，在低峰期，這個影響是可以接受的。

3. Online Add Index是否也存在與Inplace方式一樣的限制？

由于Online Add Index同時也是Inplace方式的，因此Online方式也存在著Inplace方式所存在的問題：新索引上缺乏版本信息，因此無法為老事務提供快照讀。

不僅如此，相對于Inplace方式，Online方式的約束更甚一籌，不僅所有小于創建此Index的事務不可使用新索引，同時，所有在新索引創建過程中開始的事務，也不能使用新索引。

這個增強的限制，在rowmerge.cc::row_merge_read_clustered_index()函數中調整，在聚簇索引遍歷完成之后，將新索引的trx_id，賦值為Online Row Log中最大的事務ID。待索引創建完成之后，所有小于此事務ID的事務，均不可使用新索引。

在遍歷聚簇索引讀取數據時，讀取的是記錄的最新版本，那么此記錄是否在Row Log也會存在?InnoDB如何處理這種情況?

首先，答案是肯定的。遍歷聚簇索引讀取記錄最新版本時，這些記錄有可能是新事務修改/插入的。這些記錄在遍歷階段，已經被應用到新索引上，于此同時，這些記錄的操作，也被記錄到Row Log之中，出現了一條記錄在新索引上存在，在Row Log中也存在的情況。

當然，InnoDB已經考慮到了這個問題。在重放Row Log的過程中，對于Row Log中的每條記錄，首先會判斷其在新索引中是否已經存在(row0log.c::row_log_apply_op_low())，若存在，則當前Row Log可以跳過(或者是將操作類型轉換)。

例如：Row Log中記錄的是一個INSERT操作，若此INSERT記錄在新索引中已經存在，那么Row Log中的記錄，可以直接丟棄(若存在項與INSERT項完全一致);或者是將INSERT轉換為UPDATE操作(Row Log記錄與新索引中的記錄，部分索引列有不同);

Online Add Index是否存在Bug?

答案同樣是肯定的，存在Bug。

其中有一個Bug，重現方案如下：

1. create table t1 (a int primary key, b int, c char(250))engine=innodb; 
2.  
3. insert into t1(b,c) values (1,'aaaaaaa'); 
4.  
5. // 保證數據量夠多 
6.  
7. insert into t1(b,c) select b,c from t1; 
8.  
9. insert into t1(b,c) select b,c from t1; 
10.  
11. insert into t1(b,c) select b,c from t1; 
12.  
13. … 
14.  
15. // max(a) = 196591 
16.  
17. select max(a) from t1; 
18.  
19. // b中同樣沒有相同項 
20.  
21. update t1 set b = a; 
22.  
23. session 1 session 2 
24.  
25. alter table t1 add unique index idx_t1_b(b); 
26.  
27. insert into t1(b,c) values (196592,'b'); 
28.  
29. // 此update，會產生b=196589的重復項 
30.  
31. update t1 set b=196589 where a=196582; 
32.  
33. delete from t1 where a = 262127; 

在以上的測試中，首先為表準備足夠的數據，目的是session 1做Online Add Index的讀取聚簇索引階段，session 2新的記錄也能夠被讀到。

在session 1的Online Add Index完成之后(成功)，執行以下兩個命令，結果如下：

1. mysql> show create table t1; 

1. +——-+————————————————– 
2.  
3. | Table | Create Table 
4.  
5. +——-+————————————————– 
6.  
7. | t1 | CREATE TABLE `t1` ( 
8.  
9. `a` int(11) NOT NULL AUTO_INCREMENT, 
10.  
11. `b` int(11) DEFAULT NULL, 
12.  
13. `c` char(250) DEFAULT NULL, 
14.  
15. PRIMARY KEY (`a`), 
16.  
17. UNIQUE KEY `idx_t1_b` (`b`) 
18.  
19. ) ENGINE=InnoDB AUTO_INCREMENT=262129 DEFAULT CHARSET=gbk | 
20.  
21. +——-+————————————————– 
22.  
23. mysql> select * from t1 where a in (196582,196589); 
24.  
25. +——–+——–+———+ 
26.  
27. | a | b | c | 
28.  
29. +——–+——–+———+ 
30.  
31. | 196582 | 196589 
32. | aaaaaaa | 
33.  
34. | 196589 | 196589 
35. | aaaaaaa | 
36.  
37. +——–+——–+———+ 
38.  
39. 2 rows in set (0.04 sec) 

可以看到，b上已經有了一個Unique索引，但是表中卻存在兩個相同的取值為196589的值。

此Bug，是處理Row Log的重放過程，未詳盡考慮所有情況導致的。因此，在MySQL 5.6版本穩定之前，慎用!

Online Add Index可借鑒之處

在MySQL 5.6.7中學習到兩個文件操作函數：一是posix_fadvise()函數，指定POSIX_FADV_DONTNEED參數，可做到讀寫不Cache：Improving Linux performance by preserving Buffer Cache State unbuffered I/O in Linux;二是fallocate()函數，指定FALLOC_FL_PUNCH_HOLE參數，可做到讀時清空：Linux Programmer's Manual FALLOCATE(2) 有類似需求的朋友，可試用。

posix_fadvise函數+POSIX_FADV_DONTNEED參數，主要功能就是丟棄文件在Cache中的clean blocks。因此，若用戶不希望一個文件占用過多的文件系統Cache，可以定期的調用fdatasync()，然后接著posix_fadvise(POSIX_FADV_DONTNEED)，清空文件在Cache中的clean blocks，不錯的功能!