• MySQL Online DDL

    背景

    众所周知,数据库的DDL操作(比如CREATE,DROP,ALTER等)代价是非常高的,特别是在表数据特别大或者刚好对应的表并发读写高的情况下,日常的加索引或者新列,就有可能堵塞整个表的读写。

    然后 mysql 5.6 开始,大家期待的Online DDL出现了,可以实现修改表结构的同时,依然允许DML操作(select,insert,update,delete)。在这个特性出现以前,用的比较多的工具是percona官方提供的pt-online-schema-change。

    其实,早在 MySQL 5.1 (带InnoDB Plugin)和5.5中,有个特性叫 Fast Index Creation(下称 FIC),就是在添加或者删除二级索引的时候,可以不用复制原表。我们对比一下FIC和FIC之前的有关索引的DDL操作:

    FIC之前

    1. 首先新建Temp table临时表,表结构是 ALTAR TABLE 新定义的结构
    2. 然后把原表中数据导入到这个Temp table
    3. 删除原表
    4. 最后把临时表rename为原来的表名

    为了保持数据的一致性,中间复制数据(Copy Table)全程锁表只读,如果有写请求进来将无法提供服务,连接数爆涨。

    FIC

    1. 新建索引的数据字典
    2. 锁表,禁止DML,允许查询
    3. 读取聚集索引,构造新的索引项,排序并插入新索引(在聚集索引的基础上
    4. 等待打开当前表的所有只读事务提交(元数据锁的作用不就是此吗?
    5. 创建索引结束

    如上,引入FIC之后,创建二级索引时会对原表加上一个S锁,创建过程不需要重建表(no-rebuild);而删除InnoDB二级索引只需要更新内部视图,并标记这个索引的空间可用,去掉数据库元数据上该索引的定义即可。这个过程也只允许读操作,不能写入,但大大加快了修改索引的速度(不含主键索引,InnoDB IOT的特性决定了修改主键依然需要 Copy Table )。

    但是, FIC只对索引的创建删除有效,mysql 5.6版本支持了 online DDL, 在有些修改表结构的操作同时,依然允许DML操作(select,insert,update,delete)。

    Online DDL

    1. 对比之前的FIC,Online DDL对创建索引操作有什么改进呢?

    首先,它借用之前FIC的方式创建索引,无需使用临时表。然后,在它遍历聚簇索引,收集记录并插入到新索引的过程中,原表记录可修改。因为它将修改的记录临时保存在Undo Log中。当聚簇索引遍历完毕,并全部插入到新索引之后,重放Undo Log中的记录修改,使得新索引与聚簇索引记录达到一致状态。

    与FIC方式相比,Online Add Index吸收了FIC方式的优势,用全量+增量的方式实现了DDL期间DML支持,并且极大减少了锁表的时间。

    2. 是不是所有的DDL都可以支持这种高效的Online方式?

    答案是否定的,Online DDL并不是万能的,有些DDL操作操作会导致表的记录格式发生变化,无法通过简单的全量+增量的方式实现online。 比如修改列类型,删除主键,修改字符集等。而这些操作可能依旧还是会回到最原始的copy table的方式。

    并且也有另外一些操作它虽然不需要copy table,但是由于它本身也修改了表的记录格式。所以需要重新组织纪录,比如optimize table、添加/删除列、修改列NULL/NOT NULL属性等; 这种方式我们称之为rebuild方式。

    而最后剩下的一些操作,就能较好的支持我们的Online DDL操作,比如删除索引、修改列名、修改列默认值、修改列自增值等。

    我们可以借助官方文档里的说明查看具体哪些DDL操作属于上面的哪一类:

    https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

    这里简单的罗列出一些常见的:

    • In-Place 为Yes是优选项,说明该操作支持INPLACE
    • Copies Table 为No是优选项,因为为Yes需要重建表。大部分情况与In-Place是相反的
    • Allows Concurrent DML? 为Yes是优选项,说明ddl期间表依然可读写,可以指定 LOCK=NONE(如果操作允许的话mysql自动就是NONE)
    • Allows Concurrent Query? 默认所有DDL操作期间都允许查询请求,放在这只是便于参考
    • Notes 会对前面几列Yes/No带 * 号的限制说明
    Operation In-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes
    添加索引 Yes* No* Yes Yes 对全文索引的一些限制
    删除索引 Yes No Yes Yes 仅修改表的元数据
    OPTIMIZE TABLE Yes Yes Yes Yes 从 5.6.17开始使用ALGORITHM=INPLACE,当然如果指定了old_alter_table=1或mysqld启动带--skip-new则将还是COPY模式。如果表上有全文索引只支持COPY
    对一列设置默认值 Yes No Yes Yes 仅修改表的元数据
    对一列修改auto-increment 的值 Yes No Yes Yes 仅修改表的元数据
    添加 foreign key constraint Yes* No* Yes Yes 为了避免拷贝表,在约束创建时会禁用foreign_key_checks
    删除 foreign key constraint Yes No Yes Yes foreign_key_checks 不影响
    改变列名 Yes* No* Yes* Yes 为了允许DML并发, 如果保持相同数据类型,仅改变列名
    添加列 Yes* Yes* Yes* Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。当添加列是auto-increment,不允许DML并发
    删除列 Yes Yes* Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
    修改列数据类型 No Yes* No Yes 修改类型或添加长度,都会拷贝表,而且不允许更新操作
    更改列顺序 Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
    修改ROW_FORMAT
    和KEY_BLOCK_SIZE
    Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
    设置列属性NULL
    或NOT NULL
    Yes Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
    添加主键 Yes* Yes Yes Yes 尽管允许 ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。
    如果列定义必须转化NOT NULL,则不允许INPLACE
    删除并添加主键 Yes Yes Yes Yes 在同一个 ALTER TABLE 语句删除就主键、添加新主键时,才允许inplace;数据大幅重组,所以它仍然是一项昂贵的操作。
    删除主键 No Yes No Yes 不允许并发DML,要拷贝表,而且如果没有在同一 ATLER TABLE 语句里同时添加主键则会收到限制
    变更表字符集 No Yes No Yes 如果新的字符集编码不同,重建表

    由上图中的Notes信息中我们可以看到,我们可以通过 ALGORITHM 参数来作用DDL语句,选择使用什么样的方式来进行Online DDL。那么ALGORITHM又代表什么含义呢?我们应该怎么去应用查看它呢?

    官方文档里MySQL 5.6的 alter 语法结构里比5.5多了两个参数:ALGORITHM 和 LOCK,如下:

    yy

    ALGORITHM指定了DDL操作的算法,MySQL的ONLINE DDL方式分为 INPLAVE 和 COPY 以及 DEFAULT 三种方式:

    • COPY 表示按照MySQL 5.1版本之前的方法,需要拷贝原始表,所以不允许并发DML写操作,可读。 这种copy方式的效率不如 inplace 。
    • INPLACE 表示采用操作不需要创建临时表。可以避免重建表带来的IO和CPU消耗,保证ddl期间依然有良好的性能和并发。
    • DEFAULT 表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法,该参数默认为OFF,表示采用INLACE的方式。

    ALGORITHM 指定了Online DDL 内部的实现方式,此外我们还可以用 LOCK 选项控制是否锁表,根据不同的DDL操作类型有不同的表现:默认mysql尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。

    yy

    但是有一点需要说明,无论任何模式下,online ddl开始之前都需要一个短时间排它锁(exclusive)来准备环境,所以alter命令发出后,会首先等待该表上的其它操作完成,在alter命令之后的请求会出现等待  waiting meta data lock这就是传说中的元数据锁,这下有些知识点就串起来了。同样在ddl结束之前,也要等待alter期间所有的事务完成,也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行,否则一样出现连环锁表。

    Online DDL的限制和应该注意的地方

    那么到此为止,我们是否觉得有了 online DDL以后就可以任性的进行线上变更了? 当然不是,对于DBA来说,这或许是个永远也无法彻底解决的难题。但我们可以无休止的进行优化。 但前提是对此有较为深刻的理解,接下来就看一下Online DDL的限制和应该注意的地方:

    1. 在alter table时,如果涉及到table copy操作,要确保datadir目录有足够的磁盘空间,能够放的下整张表,因为拷贝表的的操作是直接在数据目录下进行的。

    2. 添加索引无需table copy,但要确保tmpdir目录足够存下索引一列的数据(如果是组合索引,当前临时排序文件一合并到原表上就会删除)

    3. 在主从环境下,主库执行alter命令在完成之前是不会进入binlog记录事件,如果允许dml操作则不影响记录时间,所以期间不会导致延迟。然而,由于从库是单个SQL Thread按顺序应用relay log,轮到ALTER语句时直到执行完才能下一条,所以从库会在master ddl完成后开始产生延迟。(pt-osc可以控制延迟时间,所以这种场景下它更合适)

    4. 在执行一个允许并发DML在线 ALTER TABLE时,结束之前这个线程会应用 online log 记录的增量修改,而这些修改是其它thread里产生的,所以有可能会遇到重复键值错误(ERROR 1062 (23000): Duplicate entry)

    5. 涉及到table copy时,目前还没有机制限制暂停ddl,或者限制IO阀值在MySQL 5.7.6开始能够通过 performance_schema 观察alter table的进度。

    6. 一般来说,建议把多个alter语句合并在一起进行,避免多次table rebuild带来的消耗。但是也要注意分组,比如需要copy table和只需inplace就能完成的,应该分两个alter语句。

    7.如果DDL执行过程中,mysql会将这时候发生的DML操作全部都先记录在online log中,如果这时候DML的并发很大,以至于超过了innodb_online_alter_log_max_size变量所指定的大小,会引起DB_ONLINE_LOG_TOO_BIG 错误。默认为 128M,特别对于需要拷贝大表的alter操作,考虑临时加大该值,以此获得更大的日志缓存空间。

    8. 执行完 ALTER TABLE 之后,最好 ANALYZE TABLE tb1 去更新索引统计信息
    以后,我们在进行线上DDL操作的时候就可以参照上面的内容,针对不同的情况选择不同的方式。心里就会踏实很多,也不用提心吊胆,担心对业务造成冲击。但我想我们心里还有一些疑问,比如: 说了那么多,那么Online DDL在数据库内部究竟是怎么做到的呢?

    其实这个问题最好能通过调试去看源代码,但本人水平有限,试了一下发现理清思路太耗精力就搁浅了。官方文档里面也并没有找到详细的赘述,所以直接参照网上前辈的总结:

    Online DDL 主要包括3个阶段,prepare阶段,ddl执行阶段,commit阶段,rebuild方式比no-rebuild方式实质多了一个ddl执行阶段,prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

    Prepare阶段

    • 创建新的临时frm文件 (与InnoDB无关)
    • 持有EXCLUSIVE-MDL锁,禁止读写
    • 根据alter类型,确定执行方式(copy,rebuild,no-rebuild)假如是Add Index,则选择no-rebuild方式
    • 更新数据字典的内存对象
    • 分配row_log对象记录增量(仅rebuild类型需要)
    • 生成新的临时ibd文件(仅rebuild类型需要)

    ddl执行阶段

    • 降级EXCLUSIVE-MDL锁,允许读写
    • 扫描old_table的聚集索引每一条记录rec
    • 遍历新表的聚集索引和二级索引,逐一处理
    • 根据rec构造对应的索引项
    • 将构造索引项插入sort_buffer块排序
    • 将sort_buffer块更新到新的索引上
    • 记录ddl执行过程中产生的增量(仅rebuild类型需要)
    • 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
    • 重放row_log间产生dml操作append到row_log最后一个Block

    commit阶段

    • 当前Block为row_log最后一个时,禁止读写,升级到EXCLUSIVE-MDL锁
    • 重做row_log中最后一部分增量
    • 更新innodb的数据字典表
    • 提交事务(刷事务的redo日志)
    • 修改统计信息
    • rename临时idb文件,frm文件
    • 变更完成

    由上可知,online ddl并非整个过程都是online,在prepare阶段和commit阶段都会持有MDL-Exclusive锁,禁止读写;而在整个ddl执行阶段,允许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短,因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁止读写,主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存的字典;Commit阶段,禁止读写后,重做最后一部分增量,然后提交,保证数据一致。

    关于Online DDL的概括总图(摘自网上)

    yy

     

     

    参考:

    http://seanlook.com/2016/05/24/mysql-online-ddl-concept/