MySQL--Online DDL

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL--Online DDL

powdba 2018-01-07 19:12:15 浏览784
展开阅读全文
最近帮助客户订正SQL时注意到alter的操作可能会锁表(MDL),所以也补一下online ddl的知识,避免不当的操作影响业务的运行。

Online DDL原理:
MySQL数据库操作中,DDL操作(比如CREATE,DROP,ALTER等)代价是非常高的,特别是在单表上千万的情况下,加个索引或改个列类型,就有可能堵塞整个表的读写。
1.1 Online DDL原理
在MySQL支持Online DDL之前,执行DDL主要有两种方式:copy方式和inplace方式,通过在ALTER语句的ALGORITHM参数指定。
ALGORITHM=INPLACE,可以避免重建表带来的IO和CPU消耗,保证DDL期间依然有良好的性能和并发。
ALGORITHM=COPY,需要拷贝原始表,所以不允许并发DML写操作,可读。这种copy方式的效率还是不如inplace ,因为前者需要记录undo和redo log,而且因为临时占用buffer pool引起短时间内性能受影响。
1.2 Online DDL锁定选项
Online DDL方式实质也包含了INPLACE和 COPY两种方式,对于不支持online的ddl操作采用copy方式,比如修改列类型,删除主键,修改字符集等,这些操作都会导致记录格式发生变化,无法通过简单的全量+增量的方式实现Online;对于inplace方式,MySQL内部以”是否修改记录格式”为基准也分为两类,一类需要重建表(重新组织记录),比如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等;另外一类是只需要修改表的元数据,比如删除索引、修改列名不改变数据类型、修改列默认值、修改列自增值等。MySQL将这两类方式分别称为rebuild方式和no-rebuild方式。
此外还有LOCK选项控制是否锁表,根据不同的DDL操作类型有不同的表现:默认MySQL尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。
LOCK=NONE,即DDL期间允许并发读写涉及的表,比如为了保证ALTER TABLE时不影响用户注册或支付,可以明确指定,好处是如果不幸该alter语句不支持对该表的继续写入,则会提示失败,而不会直接发到库上执行。ALGORITHM=COPY默认LOCK级别。
LOCK=SHARED,即DDL期间表上的写操作会被阻塞,但不影响读取。
LOCK=DEFAULT,让MySQL自己去判断LOCK的模式,原则是MySQL尽可能不去锁表。
LOCK=EXCLUSIVE,即DDL期间该表不可用,堵塞任何读写请求。如果你想alter操作在最短的时间内完成,或者表短时间内不可用能接受,可以手动指定。
但是有一点需要说明,无论任何模式下,Online DDL开始之前都需要一个短时间排它锁(exclusive)来准备环境,所以alter命令发出后,会首先等待该表上的其它操作完成,在alter命令之后的请求会出现等待waiting meta data lock。同样在DDL结束之前,也要等待alter期间所有的事务完成,也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务和大的查询操作在执行,否则一样出现连环锁表。

Online DDL状态:
从上面的介绍可以看出,不是MySQL 5.6支持在线DDL就可以随心所欲的ALTER TABLE,锁不锁表要看情况:
提示:下表根据官方Summary of Online Status for DDL Operations整理挑选的常用操作。
In-Place 为Yes是优选项,说明该操作支持INPLACE。
Rebuilds Table?为No是优选项,因为为Yes需要重建表,大部分情况与In-Place是相反的。
Allows Concurrent DML?为Yes是优选项,说明ddl期间表依然可读写,可以指定LOCK=NONE(如果操作允许的话mysql自动就是NONE)。
Only Modifies Metadata?默认所有DDL操作期间都允许查询请求,放在这只是便于参考。
Notes会对前面几列Yes/No带 * 号的限制说明。
Operation
In-Place?
Rebuilds Table?
Allows Concurrent DML?
Only Modifies Metadata?
Notes
添加普通索引
Yes*
No*
Yes
No
对全文索引有限制
删除索引
Yes
No
Yes
Yes
仅修改表的元数据
OPTIMIZE TABLE
Yes*
Yes
Yes
No
从5.6.17开始使用ALGORITHM=INPLACE,当然如果指定了old_alter_table=1或mysqld启动带–skip-new则将还是COPY模式。如果表上有全文索引只支持COPY
设置列默认值
Yes
No
Yes
Yes
仅修改表的元数据
修改auto-increment值
Yes
No
Yes
No*
修改存储在内存中的值,而不是数据文件。
添加外键
Yes*
No
Yes
Yes
当禁用foreign_key_checks时,可以使用in-place算法,否则必须使用copy算法
删除外键
Yes
No
Yes
Yes
foreign_key_checks参数没有任何影响
改变列名
Yes*
No*
Yes*
Yes
为了允许DML并发,仅改变列名,不改变数据类型
添加列
Yes*
Yes*
Yes*
No
尽管允许ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。当添加列是auto-increment,不允许DML并发
删除列
Yes
Yes*
Yes
No
尽管允许ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
修改列数据类型
No*
Yes*
No
No
修改类型或添加长度,都会拷贝表,而且不允许更新操作;对于varchar类型可支持在线修改操作
修改表引擎
Yes*
Yes
Yes
No
尽管允许ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
修改ROW_FORMAT
和KEY_BLOCK_SIZE
Yes
Yes
Yes
No
尽管允许ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
设置列属性NULL
或NOT NULL
Yes
Yes
Yes
No
尽管允许ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作
添加主键
Yes*
Yes
Yes
No
尽管允许ALGORITHM=INPLACE ,但数据大幅重组,所以它仍然是一项昂贵的操作。如果列定义必须转化NOT NULL,则不允许INPLACE
删除并添加主键
Yes
Yes
Yes
No
在同一个 ALTER TABLE 语句删除就主键、添加新主键时,才允许inplace;数据大幅重组,所以它仍然是一项昂贵的操作。
删除主键
No
Yes
No
No
不允许并发DML,要拷贝表,而且如果没有在同一 ATLER TABLE 语句里同时添加主键则会收到限制
转换表字符集
No
Yes*
No
No
如果新的字符集编码不同,则重构表
从表看出,In-Place为No,DML一定是No,说明ALGORITHM=COPY一定会发生拷贝表,只读。但ALGORITHM=INPLACEE也要可能发生拷贝表,但可以并发DML操作。比如:添加、删除列,改变列顺序、添加或删除主键、改变行格式ROW_FORMAT和压缩块大小KEY_BLOCK_SIZE、改变列NULL或NOT NULL、改变列NULL或NOT NULL等。不允许并发DML的情况有:修改列数据类型、删除主键、变更表字符集,即这些类型操作ddl是不能online的。
另外,更改主键索引与普通索引处理方式是不一样的,主键即聚集索引,体现了表数据在物理磁盘上的排列,包含了数据行本身,需要拷贝表;而普通索引通过包含主键列来定位数据,所以普通索引的创建只需要一次扫描主键即可,而且是在已有数据的表上建立二级索引,更紧凑,将来查询效率更高。
修改主键也就意味着要重建所有的普通索引。删除二级索引更简单,修改InnoDB系统表信息和数据字典,标记该所以不存在,标记所占用的表空间可以被新索引或数据行重新利用。

Online DDL限制:
在alter table时,如果涉及到table copy操作,要确保datadir目录有足够的磁盘空间,能够放的下整张表,因为拷贝表的的操作是直接在数据目录下进行的。
添加索引无需table copy,但要确保tmpdir目录足够存下索引一列的数据(如果是组合索引,当前临时排序文件一合并到原表上就会删除)。
在主从环境下,主库执行alter命令在完成之前是不会进入binlog记录事件,如果允许dml操作则不影响记录时间,所以期间不会导致延迟。然而,由于从库是单个SQL Thread按顺序应用relay log,轮到ALTER语句时直到执行完才能下一条,所以从库会在master ddl完成后开始产生延迟。(pt-osc可以控制延迟时间,所以这种场景下它更合适)。
在每个DDL语句执行开始之前,如果期间有其他语句申请了排它锁,则DDL语句需要等待,会产生waiting lock。
在执行一个允许并发DML在线ALTER TABLE时,结束之前这个线程会应用online log记录的增量修改,而这些修改是其它thread里产生的,所以有可能会遇到重复键值错误 (ERROR 1062 (23000): Duplicate entry)。
根据在线DDL操作的内部操作和LOCK语句的子句,在ALTER TABLE DDL操作的初始和最后阶段,在线DDL操作可能需要在短时间内独占访问表。因此,如果有长时间运行的事务执行插入,更新,删除或SELECT … FOR UPDATE在表上,在线DDL操作可能会在完成之前等待; 并且如果在进行中启动了类似的长时间运行的事务,则在线ALTER TABLE操作可能会在完成之前等待。
涉及到table copy时,目前还没有机制限制暂停ddl,或者限制IO阀值,在MySQL 5.7.6开始能够通过performance_schema观察alter table的进度。
一般来说,建议把多个alter语句合并在一起进行,避免多次table rebuild带来的消耗。但是也要注意分组,比如需要copy table和只需inplace就能完成的,应该分两个alter语句。
如果DDL执行时间很长,期间又产生了大量的dml操作,以至于超过了innodb_online_alter_log_max_size变量所指定的大小,会引起DB_ONLINE_LOG_TOO_BIG错误。默认为128M,特别对于需要拷贝大表的alter操作,考虑临时加大该值,以此获得更大的日志缓存空间。
需要注意的是,在MySQL 5.6之前使用ALTER TABLE … ALGORITHM=INPLACE的表不支持包含时间列(DATE,DATETIME,TIMESTAMP),不然会报错。
从MySQL 5.7.11开始,你可以使用innodb_tmpdir配置选项为在线DDL操作定义单独的临时目录。该innodb_tmpdir选项被引入以帮助避免由于在ALTER TABLE重建表的在线操作期间创建的大型临时排序文件而可能发生的临时目录溢出。
执行完ALTER TABLE之后,最好ANALYZE TABLE tb1去更新索引统计信息。
如果操作失败,在线DDL操作的回滚可能是昂贵的。

添加索引实现说明:
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方式的优势,却减少了锁表的时间。

问题:
Online Add Index是否支持Unique索引?
确切的答案是:支持,InnoDB支持Online创建Unique索引。
既然支持,就会面临Check Duplicate Key的问题。Row Log中如果存在与索引中相同的键值怎么处理?怎么检测是否存在相同键值?
InnoDB解决此问题的方案也比较简介易懂。其维护了一个row_merge_dup_t的数据结构,存储了在Row log重放过程中遇到的违反唯一性冲突的Row Log。应用完Row Log之后,外部判断是否存在Unique冲突(有多少Unique冲突,均会记录),Online创建Unique索引失败。
Online与数据一致性如何兼得?
实际上,online ddl并非整个过程都是online,在prepare阶段和commit阶段都会持有MDL-Exclusive锁,禁止读写;而在整个ddl执行阶段,允许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短,因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁止读写,主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存的字典;Commit阶段,禁止读写后,重做最后一部分增量,然后提交,保证数据一致。
如何实现server层和innodb层一致性?
在prepare阶段,server层会生成一个临时的frm文件,里面包含了新表的格式;innodb层生成了临时的ibd文件(rebuild方式);在ddl执行阶段,将数据从原表拷贝到临时ibd文件,并且将row_log增量应用到临时ibd文件;在commit阶段,innodb层修改表的数据字典,然后提交;最后innodb层和mysql层面分别重命名frm和idb文件。
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记录与新索引中的记录,部分索引列有不同);
对Innodb表做ddl过程中异常了,为啥再次做ddl报#sql-xxx already exists
这个错误是什么鬼?这个表#sql-xxx实质是做ddl产生的临时表,ddl异常退出后(比如进程被kill,或者机器异常掉电等),临时文件没有清理。再次执行时,会创建同名的#sql-xxx临时文件,从而导致报错。这里的xxx与table-id强相关,如果是这样,我们把这个讨厌的#sql-xxx临时文件删掉如何呢?再次重做ddl发现还是报同样的错误。这主要原因是,这个临时表信息在innodb的数据字典有残留,通过查询数据字典视图information_schema.innodb_sys_tables,可以发现存在一条#sql-xxx的表记录。
深层次原因:ddl整个过程不是原子的,prepare过程中会新建frm文件,ibd文件,并更新数据字典;然后再进行拷贝全量+重放增量操作;最后再rename frm文件,idb文件,并修改数据字典。由于整个过程涉及到server层和innodb层,并不是一个大事务(每次改数据字典都是单独一个事务),所以执行过程中如果异常终止,就会导致临时表数据字典残留在系统表内。
影响:虽然临时表信息残留在数据字典内,但不影响用户后续操作。
解决方法:由于临时表与table-id强相关,如何改变table-id是我们需要做的,但表又不能被修改,table-id改变不了。这就成了一个悖论,要做ddl,需要改变table-id;要改变table-id,又需要通过ddl操作。查看源码后发现,对于online ddl,临时表名依赖于变更表的table-id(比如#sql-ib79,79就是变更表的table-id),而对于copy类型(非online)的ddl,临时表名则不依赖于table-id(由mysqld进程号+连接会话号产生,比如sql-604d_2,604d是mysqld进程号,2是会话号)。因此,我们通过copy类型的ddl,就可以产生表名不一样的临时表了,也就可以完成ddl任务了。比如:alter table test_log add column c88 int, ALGORITHM=copy;
其它:ddl异常结束,会导致重做ddl失败。如果做ddl过程中,kill query,这个时候ddl也会退出,但退出前会做好善后工作,清理数据字典,因此再次做ddl不会存在问题。
注:借鉴了一篇博客,截取了一部分
主要参考链接http://www.ywnds.com/?p=8893

网友评论

登录后评论
0/500
评论
powdba
+ 关注