MySQL下使用Inplace和Online方式创建索引的教程

2024-03-25 12:37:03 36

MySQL各版本，对于addIndex的处理方式是不同的，主要有三种：

（1）CopyTable方式
这是InnoDB最早支持的创建索引的方式。顾名思义，创建索引是通过临时表拷贝的方式实现的。

新建一个带有新索引的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建索引的操作。

这个方式创建索引，创建过程中，原表是可读的。但是会消耗一倍的存储空间。

（2）Inplace方式
这是原生MySQL5.5，以及innodb_plugin中提供的创建索引的方式。所谓Inplace，也就是索引创建在原表上直接进行，不会拷贝临时表。相对于CopyTable方式，这是一个进步。

Inplace方式创建索引，创建过程中，原表同样可读的，但是不可写。

（3）Online方式
这是MySQL5.6.7中提供的创建索引的方式。无论是CopyTable方式，还是Inplace方式，创建索引的过程中，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式创建索引。

InnoDB的OnlineAddIndex，首先是Inplace方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在RowLog中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放RowLog中的记录修改，使得新索引与聚簇索引记录达到一致状态。

与CopyTable方式相比，OnlineAddIndex采用的是Inplace方式，无需CopyTable，减少了空间开销；与此同时，OnlineAddIndex只有在重放RowLog最后一个Block时锁表，减少了锁表的时间。

与Inplace方式相比，OnlineAddIndex吸收了Inplace方式的优势，却减少了锁表的时间。

1.InplaceaddIndex

测试表

createtablet1(aintprimarykey,bint)engine=innodb;

insertintot1values(1,1),(2,2),(3,3),(4,4);

InplaceAddIndex处理流程
SQL

altertablet1addindexidx_t1_b(b);

处理流程

sql_table.cc::mysql_alter_table();

//判断当前操作是否可以进行Inplace实现，不可进行InplaceAlter的包括：

//1.AutoIncrement字段修改；

//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();

//InplaceaddIndex完毕

InplaceAddIndex实现分析
在索引创建完成之后，MySQLServer立即可以使用新建的索引，做查询。但是，根据以上流程，对我个人来说，有三个疑问点：

索引数据字典上，为何需要维护一个trx_id？
trx_id有何作用？

遍历聚簇索引读取所有记录时，为何可跳过删除项？
只读取非删除项，那么新建索引上没有版本信息，无法处理原有事务的快照读；

MySQLServer层，为何需要等待打开表的只读事务提交？
等待当前表上的只读事务，可以保证这些事务不会使用到新建索引

根据分析，等待打开表的只读事务结束较好理解。因为新索引上没有版本信息，若这些事务使用新的索引，将会读不到正确的版本记录。

那么InnoDB是如何处理其他那些在创建索引之前已经开始，但却一直未提交的老事务呢？这些事务，由于前期为并未读取当前表，因此不会被等待结束。这些事务在RR隔离级别下，会读取不到正确的版本记录，因为使用的索引上并没有版本信息。

当然，InnoDB同样考虑到了此问题，并采用了一种比较简介的处理方案。在索引上维护一个trx_id，标识创建此索引的事务ID。若有一个比这个事务更老的事务，打算使用新建的索引进行快照读，那么直接报错。

考虑如下的并发处理流程(事务隔离级别为RR)：

session1:session2:

//此时创建GlobalReadView

select*fromt2;

deletefromt1whereb=1;

//idx_t1_b索引上，没有b=1的项

altertablet1addindexidx_t1_b(b);

//由于ReadView在delete之前获取

//因此b=1这一项应该被读取到

select*fromt1whereb=1;

当session1执行最后一条select时，MySQLOptimizer会选择idx_t1_b索引进行查询，但是索引上并没有b=1的项，使用此索引会导致查询出错。那么，InnoDB是如何处理这个情况的呢？

处理流程：

…

ha_innobase::index_init();

change_active_index();

//判断session1事务的ReadView是否可以看到session2创建索引的事务

//此处，session2事务当然不可见，那么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)

returnHA_ERR_TABLE_DEF_CHANGED;

MySQLServer收到InnoDB返回的错误之后，会将错误报给用户，用户会收到以下错误：

mysql>select*fromt1whereb=1;

ERROR1412(HY000):Tabledefinitionhaschanged,pleaseretrytransaction

2.OnlineaddIndex

测试表

createtablet1(aintprimarykey,bint)engine=innodb;

insertintot1values(1,1),(2,2),(3,3),(4,4);

OnlineAddIndex处理流程
SQL

altertablet1addindexidx_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();

…

//OnlineAddIndex区别与InplaceAddIndex的关键

//在Online操作时，原表同时可以读写，因此需要

//将此过程中的修改操作记录到rowlog之中

row0log.cc::row_log_allocate();

row_log_t*log=(row_log_t*)&buf[2*srv_sort_buf_size];

//标识当前索引状态为Online创建，那么此索引上的

//DML操作会被写入RowLog，而不在索引上进行更新

dict_index_set_online_status(index,ONLINE_INDEX_CREATION);

…

//3.开始进行真正的OnlineAddIndex的操作(最重要的流程)

inplace_alter_table();

//此函数的操作，前部分与InplaceAddIndex基本一致

//读取聚簇索引、排序、并插入到新建索引中

//最大的不同在于，当插入完成之后，OnlineAddIndex

//还需要将rowlog中的记录变化，更新到新建索引中

row0merge.cc::row_merge_build_index();

…

//在聚簇索引读取、排序、插入新建索引的操作结束之后

//进入Online与Inplace真正的不同之处，也是Online操作

//的精髓部分——将这个过程中产生的RowLog重用

row0log.cc::row_log_apply();

//暂时将新建索引整个索引树完全锁住

//注意：只是暂时性锁住，并不是在整个重用RowLog的

//过程中一直加锁(防止加锁时间过长的优化，如何优化？)

rw_lock_x_lock(dict_index_get_lock(new_index));

…

//InnoDBOnline操作最重要的处理流程

//将OnlineCopyTable中，记录的RowLog重放到新建索引上

//重放RowLog的算法如下：

//1.RowLog中记录的是Online创建索引期间，原表上的DML操作

//这些操作包括：ROW_OP_INSERT；ROW_OP_DELETE_MARK;…



//2.RowLog以Block的方式存储，若DML较多，那么RowLogs可能

//会占用多个Blocks。row_log_t结构中包含两个指针：head与tail

//head指针用于读取RowLog，tail指针用于追加写新的RowLog；



//3.在重用RowLog时，算法遵循一个原则：尽量减少索引树加锁

//的时间(索引树加X锁，也意味着表上禁止了新的DML操作)



//索引树需要加锁的场景：

//(一)在重用RowLog跨越新的Block时，需要短暂加锁；



//(二)若应用的RowLogBlock是最后一个Block，那么一直加锁

//应用最后一个Block，由于禁止了新的DML操作，因此此

//Block应用完毕，新索引记录与聚簇索引达到一致状态，

//重用阶段结束；



//(三)在应用中间RowLogBlock上的rowlog时，无需加锁，新的

//DML操作仍旧可以进行，产生的rowlog记录到最后一个

//RowLogBlock之上；



//4.如果是创建Unique索引，那么在应用RowLog时，可能会出现

//违反唯一性约束的情况，这些情况会被记录到

//row_merge_dup_t结构之中

row_log_apply_ops(trx,index,&dup);

row_log_apply_op();

row_log_apply_op_low();

…

//将NewIndex的Onlinerowlog设置为NULL，

//标识NewIndex的数据已经与聚簇索引完全一致

//在此之后，新的DML操作，无需记录RowLog

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.OnlineAddIndex的最后步骤，做一些后续收尾工作

commit_inplace_alter_table();

…

OnlineAddIndex实现分析
在看完前面分析的InnoDB5.6.7-RC版本中实现的基本处理流程之后，个人仍旧遗留了几个问题，主要的问题有：

OnlineAddIndex是否支持Unique索引？

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

既然支持，就会面临CheckDuplicateKey的问题。RowLog中如果存在与索引中相同的键值怎么处理？怎么检测是否存在相同键值？

InnoDB解决此问题的方案也比较简介易懂。其维护了一个row_merge_dup_t的数据结构，存储了在Rowlog重放过程中遇到的违反唯一性冲突的RowLog。应用完RowLog之后，外部判断是否存在Unique冲突(有多少Unique冲突，均会记录)，Online创建Unique索引失败。

RowLog是什么样的结构，如何组织的？

在OnlineAddIndex过程中，并发DML产生的修改，被记录在RowLog中。首先，RowLog不是InnoDB的RedoLog，而是每个正在被Online创建的索引的独占结构。

Online创建索引，遵循的是先创建索引数据字典，后填充数据的方式。因此，当索引数据字典创建成功之后，新的DML操作就可以读取此索引，尝试进行更新。但是，由于索引结构上的status状态为ONLINE_INDEX_CREATION，因此这些更新不能直接应用到新索引上，而是放入RowLog之中，等待被重放到索引之上。

RowLog中，以Block的方式管理DML操作内容的存放。一个Block的大小为由参数innodb_sort_buffer_size控制，默认大小为1M(1048576)。初始化阶段，RowLog申请两个这样的Block。

在RowLog重放的过程中，到底需要多久的锁表时间？

前面的流程分析中，也提到了锁表的问题(内部为锁新建索引树的操作实现)。

在重放Rowlog时，有两个情况下，需要锁表：

情况一：在使用完一个Block，跳转到下一个Block时，需要短暂锁表，判断下一个Block是否为RowLog的最后一个Block。若不是最后一个，跳转完毕后，释放锁；使用Block内的rowlog不加锁，用户DML操作仍旧可以进行。

情况二：在使用最后一个Block时，会一直持有锁。此时不允许新的DML操作。保证最后一个Block重放完成之后，新索引与聚簇索引记录达到一致状态。

综上分析两个锁表情况，情况二会持续锁表，但是由于也只是最后一个Block，因此锁表时间也较短，只会短暂的影响用户操作，在低峰期，这个影响是可以接受的。

3.OnlineAddIndex是否也存在与Inplace方式一样的限制？

由于OnlineAddIndex同时也是Inplace方式的，因此Online方式也存在着Inplace方式所存在的问题：新索引上缺乏版本信息，因此无法为老事务提供快照读。

不仅如此，相对于Inplace方式，Online方式的约束更甚一筹，不仅所有小于创建此Index的事务不可使用新索引，同时，所有在新索引创建过程中开始的事务，也不能使用新索引。

这个增强的限制，在rowmerge.cc::row_merge_read_clustered_index()函数中调整，在聚簇索引遍历完成之后，将新索引的trx_id，赋值为OnlineRowLog中最大的事务ID。待索引创建完成之后，所有小于此事务ID的事务，均不可使用新索引。

在遍历聚簇索引读取数据时，读取的是记录的最新版本，那么此记录是否在RowLog也会存在？InnoDB如何处理这种情况？

首先，答案是肯定的。遍历聚簇索引读取记录最新版本时，这些记录有可能是新事务修改/插入的。这些记录在遍历阶段，已经被应用到新索引上，于此同时，这些记录的操作，也被记录到RowLog之中，出现了一条记录在新索引上存在，在RowLog中也存在的情况。

当然，InnoDB已经考虑到了这个问题。在重放RowLog的过程中，对于RowLog中的每条记录，首先会判断其在新索引中是否已经存在(row0log.c::row_log_apply_op_low())，若存在，则当前RowLog可以跳过(或者是将操作类型转换)。

例如：RowLog中记录的是一个INSERT操作，若此INSERT记录在新索引中已经存在，那么RowLog中的记录，可以直接丢弃(若存在项与INSERT项完全一致)；或者是将INSERT转换为UPDATE操作(RowLog记录与新索引中的记录，部分索引列有不同)；

OnlineAddIndex是否存在Bug？

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

其中有一个Bug，重现方案如下：

createtablet1(aintprimarykey,bint,cchar(250))engine=innodb;

insertintot1(b,c)values(1,'aaaaaaa');

//保证数据量够多

insertintot1(b,c)selectb,cfromt1;

insertintot1(b,c)selectb,cfromt1;

insertintot1(b,c)selectb,cfromt1;

…

//max(a)=196591

selectmax(a)fromt1;

//b中同样没有相同项

updatet1setb=a;

session1session2

altertablet1adduniqueindexidx_t1_b(b);

insertintot1(b,c)values(196592,'b');

//此update，会产生b=196589的重复项

updatet1setb=196589wherea=196582;

deletefromt1wherea=262127;

在以上的测试中，首先为表准备足够的数据，目的是session1做OnlineAddIndex的读取聚簇索引阶段，session2新的记录也能够被读到。

在session1的OnlineAddIndex完成之后(成功)，执行以下两个命令，结果如下：

mysql>showcreatetablet1;

+——-+————————————————–

|Table|CreateTable

+——-+————————————————–

|t1|CREATETABLE`t1`(

`a`int(11)NOTNULLAUTO_INCREMENT,

`b`int(11)DEFAULTNULL,

`c`char(250)DEFAULTNULL,

PRIMARYKEY(`a`),

UNIQUEKEY`idx_t1_b`(`b`)

)ENGINE=InnoDBAUTO_INCREMENT=262129DEFAULTCHARSET=gbk|

+——-+————————————————–

mysql>select*fromt1whereain(196582,196589);

+——–+——–+———+

|a|b|c|

+——–+——–+———+

|196582|196589
|aaaaaaa|

|196589|196589
|aaaaaaa|

+——–+——–+———+

2rowsinset(0.04sec)

可以看到，b上已经有了一个Unique索引，但是表中却存在两个相同的取值为196589的值。

此Bug，是处理RowLog的重放过程，未详尽考虑所有情况导致的。因此，在MySQL5.6版本稳定之前，慎用！

OnlineAddIndex可借鉴之处
在MySQL5.6.7中学习到两个文件操作函数：一是posix_fadvise()函数，指定POSIX_FADV_DONTNEED参数，可做到读写不Cache：ImprovingLinuxperformancebypreservingBufferCacheState unbufferedI/OinLinux；二是fallocate()函数，指定FALLOC_FL_PUNCH_HOLE参数，可做到读时清空：LinuxProgrammer'sManualFALLOCATE(2)有类似需求的朋友，可试用。

posix_fadvise函数+POSIX_FADV_DONTNEED参数，主要功能就是丢弃文件在Cache中的cleanblocks。因此，若用户不希望一个文件占用过多的文件系统Cache，可以定期的调用fdatasync()，然后接着posix_fadvise(POSIX_FADV_DONTNEED)，清空文件在Cache中的cleanblocks，不错的功能！

MySQL下使用Inplace和Online方式创建索引的教程

热门推荐

随机推荐