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
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(); // 判断当前操作是否可以进行Inplace实现，不可进行Inplace Alter的包括： // 1. Auto Increment字段修改； // 2. 列重命名； // 3. 行存储格式修改；等 mysql_compare_tables() -> ha_innobase::check_if_incompatible_data(); // Inplace创建索引第一阶段(主要阶段) handler0alter.cc::add_index(); … // 创建索引数据字典 row0merge.c::row_merge_create_index(); index = dict_mem_index_create(); // 每个索引数据字典上，有一个trx_id，记录创建此索引的事务 // 此trx_id有何功能，接着往下看 index->trx_id = trx_id; // 读取聚簇索引，构造新索引的项，排序并插入新索引 row0merge.c::row_merge_build_indexes(); // 读取聚簇索引，注意：只读取其中的非删除项 // 跳过所有删除项，为什么可以这么做？往下看 row_merge_read_clustered_index(); // 文件排序 row_merge_sort(); // 顺序读取排序文件中的索引项，逐个插入新建索引中 row_merge_insert_index_tuples(); // 等待打开当前表的所有只读事务提交 sql_base.cc::wait_while_table_is_used(); // 创建索引结束，做最后的清理工作 handler0alter.cc::final_add_index(); // Inplace add Index完毕 |
Inplace Add Index实现分析
在索引创建完成之后，MySQL Server立即可以使用新建的索引，做查询。但是，根据以上流程，对我个人来说，有三个疑问点：
索引数据字典上，为何需要维护一个trx_id？
trx_id有何作用？
遍历聚簇索引读取所有记录时，为何可跳过删除项？
只读取非删除项，那么新建索引上没有版本信息，无法处理原有事务的快照读；
MySQL Server层，为何需要等待打开表的只读事务提交？
等待当前表上的只读事务，可以保证这些事务不会使用到新建索引
根据分析，等待打开表的只读事务结束较好理解。因为新索引上没有版本信息，若这些事务使用新的索引，将会读不到正确的版本记录。
那么InnoDB是如何处理其他那些在创建索引之前已经开始，但却一直未提交的老事务呢？这些事务，由于前期为并未读取当前表，因此不会被等待结束。这些事务在RR隔离级别下，会读取不到正确的版本记录，因为使用的索引上并没有版本信息。
当然，InnoDB同样考虑到了此问题，并采用了一种比较简介的处理方案。在索引上维护一个trx_id，标识创建此索引的事务ID。若有一个比这个事务更老的事务，打算使用新建的索引进行快照读，那么直接报错。
考虑如下的并发处理流程(事务隔离级别为RR)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
session 1: session 2:// 此时创建Global ReadViewselect * from t2; delete from t1 where b = 1; // idx_t1_b索引上，没有b = 1的项 alter table t1 add index idx_t1_b(b);// 由于ReadView在delete之前获取// 因此b = 1这一项应该被读取到select * from t1 where b = 1; |
当session 1执行最后一条select时，MySQL Optimizer会选择idx_t1_b索引进行查询，但是索引上并没有b = 1的项，使用此索引会导致查询出错。那么，InnoDB是如何处理这个情况的呢？
处理流程：
|
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事务的ReadView是否可以看到session 2创建索引的事务 // 此处，session 2事务当然不可见，那么prebuilt->index_usable = false prebuilt->index_usable = row_merge_is_index_usable(readview, index->trx_id);…ha_innobase::index_read(); // 判断index_usable属性，此时为false，返回上层表定义修改，查询失败 if (!prebuilt->index_usable) 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
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. 判断当前DDL操作是否可以Inplace进行 check_if_supported_inplace_alter(); … // 2. 开始进行Online创建的前期准备工作 prepare_inplace_alter_table(); … // 修改表的数据字典信息 prepare_inplace_alter_table_dict(); … // 等待InnoDB所有的后台线程，停止操作此表 dict_stats_wait_bg_to_stop_using_tables(); … // Online Add Index区别与Inplace Add Index的关键 // 在Online操作时，原表同时可以读写，因此需要 // 将此过程中的修改操作记录到row log之中 row0log.cc::row_log_allocate(); row_log_t* log = (row_log_t*)&buf[2 * srv_sort_buf_size]; // 标识当前索引状态为Online创建，那么此索引上的 // DML操作会被写入Row Log，而不在索引上进行更新 dict_index_set_online_status(index, ONLINE_INDEX_CREATION); … // 3. 开始进行真正的Online Add Index的操作(最重要的流程) inplace_alter_table(); // 此函数的操作，前部分与Inplace Add Index基本一致 // 读取聚簇索引、排序、并插入到新建索引中 // 最大的不同在于，当插入完成之后，Online Add Index // 还需要将row log中的记录变化，更新到新建索引中 row0merge.cc::row_merge_build_index(); … // 在聚簇索引读取、排序、插入新建索引的操作结束之后 // 进入Online与Inplace真正的不同之处，也是Online操作 // 的精髓部分——将这个过程中产生的Row Log重用 row0log.cc::row_log_apply(); // 暂时将新建索引整个索引树完全锁住 // 注意：只是暂时性锁住，并不是在整个重用Row Log的 // 过程中一直加锁(防止加锁时间过长的优化，如何优化？) rw_lock_x_lock(dict_index_get_lock(new_index)); … // InnoDB Online操作最重要的处理流程 // 将Online Copy Table中，记录的Row Log重放到新建索引上 // 重放Row Log的算法如下： // 1. Row Log中记录的是Online创建索引期间，原表上的DML操作 // 这些操作包括：ROW_OP_INSERT；ROW_OP_DELETE_MARK; … // 2. Row Log以Block的方式存储，若DML较多，那么Row Logs可能 // 会占用多个Blocks。row_log_t结构中包含两个指针：head与tail // head指针用于读取Row Log，tail指针用于追加写新的Row Log； // 3.在重用Row Log时，算法遵循一个原则：尽量减少索引树加锁 // 的时间(索引树加X锁，也意味着表上禁止了新的DML操作) // 索引树需要加锁的场景： // (一) 在重用Row Log跨越新的Block时，需要短暂加锁； // (二) 若应用的Row Log Block是最后一个Block，那么一直加锁 // 应用最后一个Block，由于禁止了新的DML操作，因此此 // Block应用完毕，新索引记录与聚簇索引达到一致状态， // 重用阶段结束； // (三) 在应用中间Row Log Block上的row log时，无需加锁，新的 // DML操作仍旧可以进行，产生的row log记录到最后一个 // Row Log Block之上； // 4. 如果是创建Unique索引，那么在应用Row Log时，可能会出现 // 违反唯一性约束的情况，这些情况会被记录到 // row_merge_dup_t结构之中 row_log_apply_ops(trx, index, &dup); row_log_apply_op(); row_log_apply_op_low(); … // 将New Index的Online row log设置为NULL， // 标识New Index的数据已经与聚簇索引完全一致 // 在此之后，新的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的最后步骤，做一些后续收尾工作 commit_inplace_alter_table(); … |
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
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');// 保证数据量够多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中同样没有相同项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，会产生b=196589的重复项 update t1 set b=196589 where a=196582; 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
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上已经有了一个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，不错的功能！
