MySQL InnoDB Add Index实现调研(二:Online Add Index)

11月 7th, 2012
  1. MySQL Add Index实现

MySQL各版本,对于add Index的处理方式是不同的,主要有三种:

 

  • Copy Table方式

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


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


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


  • Inplace方式

    这是原生MySQL 5.5,以及innodb_plugin中提供的创建索引的方式。所谓Inplace,也就是索引创建在原表上直接进行,不会拷贝临时表。相对于Copy Table方式,这是一个进步。


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


  • 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. Online add Index

本章节,主要通过测试/源码跟踪的方式,调研InnoDB Online Add Index的实现方式(MySQL 5.6.7-RC版本提供的新功能)。分析Online add Index有哪些需要注意的地方,最后展示一个InnoDB Online Add Index存在的Bug。

  1. 测试准备

测试版本

MySQL 5.6.7-RC

测试表

    create table t1 (a int primary key, b int)engine=innodb;

    insert into t1 values (1,1),(2,2),(3,3),(4,4);

 

  1. 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();

            …

  1. 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,因此锁表时间也较短,只会短暂的影响用户操作,在低峰期,这个影响是可以接受的。


  • 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版本稳定之前,慎用!

 

  1. 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,不错的功能!

  1. 李大玉
    1月 16th, 201314:08

    不错,很详细

  2. 郭伟明
    2月 8th, 201313:12

    在5.6.7版本里确实少了个row log应用时针对UNIQUE的检测问题,其实就是缺少应用ROW LOG时,ROWLOG里的数据未检测是否符合操作UNIQUE DDL索引的约束,直接将数据应用进去了,但主键没此问题。在5.6.10版本中,我已测试,没有该BUG了,应该是ONLINE 索引创建完,去应用ROW LOG后,如果成功,再抛出 DDL成功信息,如有约束制约,那么抛出DDL失败,回滚?

  3. rudyLi
    5月 26th, 201417:48

    你好,查看innodb索引创建的时候找到您的blog,我想问一下在导入千万级别的数时,创建聚簇索引的时间大概在什么数量级,创建二级索引呢?版本是mysql5.6,存储引擎 InnoDB

  4. Rhett
    11月 11th, 201422:10

    mysql 会话session没有事务ID吗,如果存在的话,session1应该读取不到session2的数据,因为其事务开始较早,session 2无论插入还是更新操作都查看不到,只是需要把新的DML操作log存在到log日志中,在索引创建完之后再重新对log里的DML操作再次在该索引中执行一遍即可。

  5. dawn
    5月 22nd, 201515:33

    该bug在percona5.6.19中已经修复了。