MySQL各版本，對(duì)于add Index的處理方式是不同的，主要有三種：
（1）Copy Table方式
這是InnoDB最早支持的創(chuàng)建索引的方式。顧名思義，創(chuàng)建索引是通過臨時(shí)表拷貝的方式實(shí)現(xiàn)的。
新建一個(gè)帶有新索引的臨時(shí)表，將原表數(shù)據(jù)全部拷貝到臨時(shí)表，然后Rename，完成創(chuàng)建索引的操作。
這個(gè)方式創(chuàng)建索引，創(chuàng)建過程中，原表是可讀的。但是會(huì)消耗一倍的存儲(chǔ)空間。
（2）Inplace方式
這是原生MySQL 5.5，以及innodb_plugin中提供的創(chuàng)建索引的方式。所謂Inplace，也就是索引創(chuàng)建在原表上直接進(jìn)行，不會(huì)拷貝臨時(shí)表。相對(duì)于Copy Table方式，這是一個(gè)進(jìn)步。
Inplace方式創(chuàng)建索引，創(chuàng)建過程中，原表同樣可讀的，但是不可寫。
（3）Online方式
這是MySQL 5.6.7中提供的創(chuàng)建索引的方式。無論是Copy Table方式，還是Inplace方式，創(chuàng)建索引的過程中，原表只能允許讀取，不可寫。對(duì)應(yīng)用有較大的限制，因此MySQL最新版本中，InnoDB支持了所謂的Online方式創(chuàng)建索引。
InnoDB的Online Add Index，首先是Inplace方式創(chuàng)建索引，無需使用臨時(shí)表。在遍歷聚簇索引，收集記錄并插入到新索引的過程中，原表記錄可修改。而修改的記錄保存在Row Log中。當(dāng)聚簇索引遍歷完畢，并全部插入到新索引之后，重放Row Log中的記錄修改，使得新索引與聚簇索引記錄達(dá)到一致狀態(tài)。
與Copy Table方式相比，Online Add Index采用的是Inplace方式，無需Copy Table，減少了空間開銷；與此同時(shí)，Online Add Index只有在重放Row Log最后一個(gè)Block時(shí)鎖表，減少了鎖表的時(shí)間。
與Inplace方式相比，Online Add Index吸收了Inplace方式的優(yōu)勢(shì)，卻減少了鎖表的時(shí)間。
1.Inplace add Index
測(cè)試表
|
1
2
3
|
create table t1 (a int primary key, b int)engine=innodb;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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
sql_table.cc::mysql_alter_table(); // 判斷當(dāng)前操作是否可以進(jìn)行Inplace實(shí)現(xiàn)，不可進(jìn)行Inplace Alter的包括： // 1. Auto Increment字段修改； // 2. 列重命名； // 3. 行存儲(chǔ)格式修改；等 mysql_compare_tables() -> ha_innobase::check_if_incompatible_data(); // Inplace創(chuàng)建索引第一階段(主要階段) handler0alter.cc::add_index(); … // 創(chuàng)建索引數(shù)據(jù)字典 row0merge.c::row_merge_create_index(); index = dict_mem_index_create(); // 每個(gè)索引數(shù)據(jù)字典上，有一個(gè)trx_id，記錄創(chuàng)建此索引的事務(wù) // 此trx_id有何功能，接著往下看 index->trx_id = trx_id; // 讀取聚簇索引，構(gòu)造新索引的項(xiàng)，排序并插入新索引 row0merge.c::row_merge_build_indexes(); // 讀取聚簇索引，注意：只讀取其中的非刪除項(xiàng) // 跳過所有刪除項(xiàng)，為什么可以這么做？往下看 row_merge_read_clustered_index(); // 文件排序 row_merge_sort(); // 順序讀取排序文件中的索引項(xiàng)，逐個(gè)插入新建索引中 row_merge_insert_index_tuples(); // 等待打開當(dāng)前表的所有只讀事務(wù)提交 sql_base.cc::wait_while_table_is_used(); // 創(chuàng)建索引結(jié)束，做最后的清理工作 handler0alter.cc::final_add_index(); // Inplace add Index完畢 |
Inplace Add Index實(shí)現(xiàn)分析
在索引創(chuàng)建完成之后，MySQL Server立即可以使用新建的索引，做查詢。但是，根據(jù)以上流程，對(duì)我個(gè)人來說，有三個(gè)疑問點(diǎn)：
索引數(shù)據(jù)字典上，為何需要維護(hù)一個(gè)trx_id？
trx_id有何作用？
遍歷聚簇索引讀取所有記錄時(shí)，為何可跳過刪除項(xiàng)？
只讀取非刪除項(xiàng)，那么新建索引上沒有版本信息，無法處理原有事務(wù)的快照讀；
MySQL Server層，為何需要等待打開表的只讀事務(wù)提交？
等待當(dāng)前表上的只讀事務(wù)，可以保證這些事務(wù)不會(huì)使用到新建索引
根據(jù)分析，等待打開表的只讀事務(wù)結(jié)束較好理解。因?yàn)樾滤饕蠜]有版本信息，若這些事務(wù)使用新的索引，將會(huì)讀不到正確的版本記錄。
那么InnoDB是如何處理其他那些在創(chuàng)建索引之前已經(jīng)開始，但卻一直未提交的老事務(wù)呢？這些事務(wù)，由于前期為并未讀取當(dāng)前表，因此不會(huì)被等待結(jié)束。這些事務(wù)在RR隔離級(jí)別下，會(huì)讀取不到正確的版本記錄，因?yàn)槭褂玫乃饕喜]有版本信息。
當(dāng)然，InnoDB同樣考慮到了此問題，并采用了一種比較簡(jiǎn)介的處理方案。在索引上維護(hù)一個(gè)trx_id，標(biāo)識(shí)創(chuàng)建此索引的事務(wù)ID。若有一個(gè)比這個(gè)事務(wù)更老的事務(wù)，打算使用新建的索引進(jìn)行快照讀，那么直接報(bào)錯(cuò)。
考慮如下的并發(fā)處理流程(事務(wù)隔離級(jí)別為RR)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
session 1: session 2:// 此時(shí)創(chuàng)建Global ReadViewselect * from t2; delete from t1 where b = 1; // idx_t1_b索引上，沒有b = 1的項(xiàng) alter table t1 add index idx_t1_b(b);// 由于ReadView在delete之前獲取// 因此b = 1這一項(xiàng)應(yīng)該被讀取到select * from t1 where b = 1; |
當(dāng)session 1執(zhí)行最后一條select時(shí)，MySQL Optimizer會(huì)選擇idx_t1_b索引進(jìn)行查詢，但是索引上并沒有b = 1的項(xiàng)，使用此索引會(huì)導(dǎo)致查詢出錯(cuò)。那么，InnoDB是如何處理這個(gè)情況的呢？
處理流程：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
…ha_innobase::index_init(); change_active_index(); // 判斷session 1事務(wù)的ReadView是否可以看到session 2創(chuàng)建索引的事務(wù) // 此處，session 2事務(wù)當(dāng)然不可見，那么prebuilt->index_usable = false prebuilt->index_usable = row_merge_is_index_usable(readview, index->trx_id);…ha_innobase::index_read(); // 判斷index_usable屬性，此時(shí)為false，返回上層表定義修改，查詢失敗 if (!prebuilt->index_usable) return HA_ERR_TABLE_DEF_CHANGED; |
MySQL Server收到InnoDB返回的錯(cuò)誤之后，會(huì)將錯(cuò)誤報(bào)給用戶，用戶會(huì)收到以下錯(cuò)誤：
|
1
|
mysql> select * from t1 where b = 1; |
|
1
|
ERROR 1412 (HY000): Table definition has changed, please retry transaction |
2.Online add Index
測(cè)試表
|
1
2
3
|
create table t1 (a int primary key, b int)engine=innodb;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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
sql_table.cc::mysql_alter_table(); // 1. 判斷當(dāng)前DDL操作是否可以Inplace進(jìn)行 check_if_supported_inplace_alter(); … // 2. 開始進(jìn)行Online創(chuàng)建的前期準(zhǔn)備工作 prepare_inplace_alter_table(); … // 修改表的數(shù)據(jù)字典信息 prepare_inplace_alter_table_dict(); … // 等待InnoDB所有的后臺(tái)線程，停止操作此表 dict_stats_wait_bg_to_stop_using_tables(); … // Online Add Index區(qū)別與Inplace Add Index的關(guān)鍵 // 在Online操作時(shí)，原表同時(shí)可以讀寫，因此需要 // 將此過程中的修改操作記錄到row log之中 row0log.cc::row_log_allocate(); row_log_t* log = (row_log_t*)&buf[2 * srv_sort_buf_size]; // 標(biāo)識(shí)當(dāng)前索引狀態(tài)為Online創(chuàng)建，那么此索引上的 // DML操作會(huì)被寫入Row Log，而不在索引上進(jìn)行更新 dict_index_set_online_status(index, ONLINE_INDEX_CREATION); … // 3. 開始進(jìn)行真正的Online Add Index的操作(最重要的流程) inplace_alter_table(); // 此函數(shù)的操作，前部分與Inplace Add Index基本一致 // 讀取聚簇索引、排序、并插入到新建索引中 // 最大的不同在于，當(dāng)插入完成之后，Online Add Index // 還需要將row log中的記錄變化，更新到新建索引中 row0merge.cc::row_merge_build_index(); … // 在聚簇索引讀取、排序、插入新建索引的操作結(jié)束之后 // 進(jìn)入Online與Inplace真正的不同之處，也是Online操作 // 的精髓部分——將這個(gè)過程中產(chǎn)生的Row Log重用 row0log.cc::row_log_apply(); // 暫時(shí)將新建索引整個(gè)索引樹完全鎖住 // 注意：只是暫時(shí)性鎖住，并不是在整個(gè)重用Row Log的 // 過程中一直加鎖(防止加鎖時(shí)間過長(zhǎng)的優(yōu)化，如何優(yōu)化？) rw_lock_x_lock(dict_index_get_lock(new_index)); … // InnoDB Online操作最重要的處理流程 // 將Online Copy Table中，記錄的Row Log重放到新建索引上 // 重放Row Log的算法如下： // 1. Row Log中記錄的是Online創(chuàng)建索引期間，原表上的DML操作 // 這些操作包括：ROW_OP_INSERT；ROW_OP_DELETE_MARK; … // 2. Row Log以Block的方式存儲(chǔ)，若DML較多，那么Row Logs可能 // 會(huì)占用多個(gè)Blocks。row_log_t結(jié)構(gòu)中包含兩個(gè)指針：head與tail // head指針用于讀取Row Log，tail指針用于追加寫新的Row Log； // 3.在重用Row Log時(shí)，算法遵循一個(gè)原則：盡量減少索引樹加鎖 // 的時(shí)間(索引樹加X鎖，也意味著表上禁止了新的DML操作) // 索引樹需要加鎖的場(chǎng)景： // (一) 在重用Row Log跨越新的Block時(shí)，需要短暫加鎖； // (二) 若應(yīng)用的Row Log Block是最后一個(gè)Block，那么一直加鎖 // 應(yīng)用最后一個(gè)Block，由于禁止了新的DML操作，因此此 // Block應(yīng)用完畢，新索引記錄與聚簇索引達(dá)到一致狀態(tài)， // 重用階段結(jié)束； // (三) 在應(yīng)用中間Row Log Block上的row log時(shí)，無需加鎖，新的 // DML操作仍舊可以進(jìn)行，產(chǎn)生的row log記錄到最后一個(gè) // Row Log Block之上； // 4. 如果是創(chuàng)建Unique索引，那么在應(yīng)用Row Log時(shí)，可能會(huì)出現(xiàn) // 違反唯一性約束的情況，這些情況會(huì)被記錄到 // row_merge_dup_t結(jié)構(gòu)之中 row_log_apply_ops(trx, index, &dup); row_log_apply_op(); row_log_apply_op_low(); … // 將New Index的Online row log設(shè)置為NULL， // 標(biāo)識(shí)New Index的數(shù)據(jù)已經(jīng)與聚簇索引完全一致 // 在此之后，新的DML操作，無需記錄Row Log dict_index_set_online_status(); index->online_status = ONLINE_INDEX_COMPLETE; index->online_log = NULL; rw_lock_x_unlock(dict_index_get_block(new_index)); row_log_free(); … // 4. Online Add Index的最后步驟，做一些后續(xù)收尾工作 commit_inplace_alter_table(); … |
Online Add Index實(shí)現(xiàn)分析
在看完前面分析的InnoDB 5.6.7-RC版本中實(shí)現(xiàn)的基本處理流程之后，個(gè)人仍舊遺留了幾個(gè)問題，主要的問題有：
Online Add Index是否支持Unique索引？
確切的答案是：支持(不過存在Bug，后面分析)。InnoDB支持Online創(chuàng)建Unique索引。
既然支持，就會(huì)面臨Check Duplicate Key的問題。Row Log中如果存在與索引中相同的鍵值怎么處理？怎么檢測(cè)是否存在相同鍵值？
InnoDB解決此問題的方案也比較簡(jiǎn)介易懂。其維護(hù)了一個(gè)row_merge_dup_t的數(shù)據(jù)結(jié)構(gòu)，存儲(chǔ)了在Row log重放過程中遇到的違反唯一性沖突的Row Log。應(yīng)用完Row Log之后，外部判斷是否存在Unique沖突(有多少Unique沖突，均會(huì)記錄)，Online創(chuàng)建Unique索引失敗。
Row Log是什么樣的結(jié)構(gòu)，如何組織的？
在Online Add Index過程中，并發(fā)DML產(chǎn)生的修改，被記錄在Row Log中。首先，Row Log不是InnoDB的Redo Log，而是每個(gè)正在被Online創(chuàng)建的索引的獨(dú)占結(jié)構(gòu)。
Online創(chuàng)建索引，遵循的是先創(chuàng)建索引數(shù)據(jù)字典，后填充數(shù)據(jù)的方式。因此，當(dāng)索引數(shù)據(jù)字典創(chuàng)建成功之后，新的DML操作就可以讀取此索引，嘗試進(jìn)行更新。但是，由于索引結(jié)構(gòu)上的status狀態(tài)為ONLINE_INDEX_CREATION，因此這些更新不能直接應(yīng)用到新索引上，而是放入Row Log之中，等待被重放到索引之上。
Row Log中，以Block的方式管理DML操作內(nèi)容的存放。一個(gè)Block的大小為由參數(shù)innodb_sort_buffer_size控制，默認(rèn)大小為1M (1048576)。初始化階段，Row Log申請(qǐng)兩個(gè)這樣的Block。
在Row Log重放的過程中，到底需要多久的鎖表時(shí)間？
前面的流程分析中，也提到了鎖表的問題(內(nèi)部為鎖新建索引樹的操作實(shí)現(xiàn))。
在重放Row log時(shí)，有兩個(gè)情況下，需要鎖表：
情況一：在使用完一個(gè)Block，跳轉(zhuǎn)到下一個(gè)Block時(shí)，需要短暫鎖表，判斷下一個(gè)Block是否為Row Log的最后一個(gè)Block。若不是最后一個(gè)，跳轉(zhuǎn)完畢后，釋放鎖；使用Block內(nèi)的row log不加鎖，用戶DML操作仍舊可以進(jìn)行。
情況二：在使用最后一個(gè)Block時(shí)，會(huì)一直持有鎖。此時(shí)不允許新的DML操作。保證最后一個(gè)Block重放完成之后，新索引與聚簇索引記錄達(dá)到一致狀態(tài)。
綜上分析兩個(gè)鎖表情況，情況二會(huì)持續(xù)鎖表，但是由于也只是最后一個(gè)Block，因此鎖表時(shí)間也較短，只會(huì)短暫的影響用戶操作，在低峰期，這個(gè)影響是可以接受的。
3. Online Add Index是否也存在與Inplace方式一樣的限制？
由于Online Add Index同時(shí)也是Inplace方式的，因此Online方式也存在著Inplace方式所存在的問題：新索引上缺乏版本信息，因此無法為老事務(wù)提供快照讀。
不僅如此，相對(duì)于Inplace方式，Online方式的約束更甚一籌，不僅所有小于創(chuàng)建此Index的事務(wù)不可使用新索引，同時(shí)，所有在新索引創(chuàng)建過程中開始的事務(wù)，也不能使用新索引。
這個(gè)增強(qiáng)的限制，在rowmerge.cc::row_merge_read_clustered_index()函數(shù)中調(diào)整，在聚簇索引遍歷完成之后，將新索引的trx_id，賦值為Online Row Log中最大的事務(wù)ID。待索引創(chuàng)建完成之后，所有小于此事務(wù)ID的事務(wù)，均不可使用新索引。
在遍歷聚簇索引讀取數(shù)據(jù)時(shí)，讀取的是記錄的最新版本，那么此記錄是否在Row Log也會(huì)存在？InnoDB如何處理這種情況？
首先，答案是肯定的。遍歷聚簇索引讀取記錄最新版本時(shí)，這些記錄有可能是新事務(wù)修改/插入的。這些記錄在遍歷階段，已經(jīng)被應(yīng)用到新索引上，于此同時(shí)，這些記錄的操作，也被記錄到Row Log之中，出現(xiàn)了一條記錄在新索引上存在，在Row Log中也存在的情況。
當(dāng)然，InnoDB已經(jīng)考慮到了這個(gè)問題。在重放Row Log的過程中，對(duì)于Row Log中的每條記錄，首先會(huì)判斷其在新索引中是否已經(jīng)存在(row0log.c::row_log_apply_op_low())，若存在，則當(dāng)前Row Log可以跳過(或者是將操作類型轉(zhuǎn)換)。
例如：Row Log中記錄的是一個(gè)INSERT操作，若此INSERT記錄在新索引中已經(jīng)存在，那么Row Log中的記錄，可以直接丟棄(若存在項(xiàng)與INSERT項(xiàng)完全一致)；或者是將INSERT轉(zhuǎn)換為UPDATE操作(Row Log記錄與新索引中的記錄，部分索引列有不同)；
Online Add Index是否存在Bug？
答案同樣是肯定的，存在Bug。
其中有一個(gè)Bug，重現(xiàn)方案如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
create table t1 (a int primary key, b int, c char(250))engine=innodb;insert into t1(b,c) values (1,'aaaaaaa');// 保證數(shù)據(jù)量夠多insert into t1(b,c) select b,c from t1;insert into t1(b,c) select b,c from t1;insert into t1(b,c) select b,c from t1;…// max(a) = 196591select max(a) from t1;// b中同樣沒有相同項(xiàng)update t1 set b = a;session 1 session 2alter table t1 add unique index idx_t1_b(b); insert into t1(b,c) values (196592,'b'); // 此update，會(huì)產(chǎn)生b=196589的重復(fù)項(xiàng) update t1 set b=196589 where a=196582; delete from t1 where a = 262127; |
在以上的測(cè)試中，首先為表準(zhǔn)備足夠的數(shù)據(jù)，目的是session 1做Online Add Index的讀取聚簇索引階段，session 2新的記錄也能夠被讀到。
在session 1的Online Add Index完成之后(成功)，執(zhí)行以下兩個(gè)命令，結(jié)果如下：
|
1
|
mysql> show create table t1; |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
+——-+————————————————–| Table | Create Table+——-+————————————————–| t1 | CREATE TABLE `t1` (`a` int(11) NOT NULL AUTO_INCREMENT,`b` int(11) DEFAULT NULL,`c` char(250) DEFAULT NULL,PRIMARY KEY (`a`),UNIQUE KEY `idx_t1_b` (`b`)) ENGINE=InnoDB AUTO_INCREMENT=262129 DEFAULT CHARSET=gbk |+——-+————————————————–mysql> select * from t1 where a in (196582,196589);+——–+——–+———+| a | b | c |+——–+——–+———+| 196582 | 196589| aaaaaaa || 196589 | 196589| aaaaaaa |+——–+——–+———+2 rows in set (0.04 sec) |
可以看到，b上已經(jīng)有了一個(gè)Unique索引，但是表中卻存在兩個(gè)相同的取值為196589的值。
此Bug，是處理Row Log的重放過程，未詳盡考慮所有情況導(dǎo)致的。因此，在MySQL 5.6版本穩(wěn)定之前，慎用！
Online Add Index可借鑒之處
在MySQL 5.6.7中學(xué)習(xí)到兩個(gè)文件操作函數(shù)：一是posix_fadvise()函數(shù)，指定POSIX_FADV_DONTNEED參數(shù)，可做到讀寫不Cache：Improving Linux performance by preserving Buffer Cache State unbuffered I/O in Linux；二是fallocate()函數(shù)，指定FALLOC_FL_PUNCH_HOLE參數(shù)，可做到讀時(shí)清空：Linux Programmer's Manual FALLOCATE(2) 有類似需求的朋友，可試用。
posix_fadvise函數(shù)+POSIX_FADV_DONTNEED參數(shù)，主要功能就是丟棄文件在Cache中的clean blocks。因此，若用戶不希望一個(gè)文件占用過多的文件系統(tǒng)Cache，可以定期的調(diào)用fdatasync()，然后接著posix_fadvise(POSIX_FADV_DONTNEED)，清空文件在Cache中的clean blocks，不錯(cuò)的功能！
