MySQL下使用Inplace和Online方式创建索引的教程
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
测试表
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
alter table t1 add index idx_t1_b(b);
处理流程
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)：
session 1: session 2:
// 此时创建global readview
select * 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是如何处理这个情况的呢？
处理流程：
…
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返回的错误之后，会将错误报给用户，用户会收到以下错误：
mysql> select * from t1 where b = 1;
error 1412 (hy000): table definition has changed, please retry transaction
2.online add index
测试表
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
alter table t1 add index idx_t1_b(b);
处理流程
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，重现方案如下：
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) = 196591
select max(a) from t1;
// b中同样没有相同项
update t1 set b = a;
session 1 session 2
alter 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完成之后(成功)，执行以下两个命令，结果如下：
mysql> show create table t1;
+——-+————————————————– | 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，不错的功能！