注册 登录
  • 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL InnoDB Online DDL

MySQL InnoDB 彭东稳 9510次浏览 已收录 1个评论

一、概述

MySQL DBA 应该都知道,数据库操作里面,DDL 操作(比如 ALTER 等)代价是非常高的,特别是在单表上千万的情况下,加个索引或改个列类型,就有可能堵塞整个表的读写。一个是有些 DDL 操作本身有锁,一个是 MDL 锁。但 DDL 又是一个不可避免的需求,无论是业务迭代,还是整理表碎片都需要。

我们知道如果要收缩一个表,只是 delete 掉表里面不用的数据的话,表文件的大小是不会变的,你还要通过 alter table 命令重建表,才能达到表文件变小的目的。试想一下,如果你现在有一个表 A,需要做空间收缩,为了把表中存在的空洞去掉,你可以怎么做呢?

你可以新建一个与表 A 结构相同的表 B,然后按照主键 ID 递增的顺序,把数据一行一行地从表 A 里读出来再插入到表 B 中。

由于表 B 是新建的表,所以表 A 主键索引上的空洞,在表 B 中就都不存在了。显然地,表 B 的主键索引更紧凑,数据页的利用率也更高。如果我们把表 B 作为临时表,数据从表 A 导入表 B 的操作完成后,用表 B 替换 A,从效果上看,就起到了收缩表 A 空间的作用。

这里,你可以使用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前,这个命令的执行流程跟我们前面描述的差不多,区别只是这个临时表 B 不需要你自己创建,MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

基本上整个过程如下“图1”:

MySQL InnoDB Online DDL

显然,花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表 A 的话,就会造成数据丢失。因此,在整个 DDL 过程中,表 A 中不能有更新。也就是说,这个 DDL 不是 Online 的。

从 MySQL 5.6 开始,大家期待的 Online DDL 出现了,可以实现修改表结构的同时,依然允许 DML 操作。在这个特性出现以前,用的比较多的 DDL 操作工具是 pt-online-schema-change & gh-ost,当然现在用的也比较多。

二、Online DDL原理

MySQL 5.6 版本开始引入的 Online DDL,对“图1”描述的操作流程做了优化。

我给你简单描述一下引入了 Online DDL 之后,重建表的流程如下“图2”:

MySQL InnoDB Online DDL

简单描述一下,如下:

1. 建立一个临时文件,扫描表 A 主键的所有数据页;

2. 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;

3. 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中,对应的是下图中 state2 的状态;

4. 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是下图中 state3 的状态;

5. 用临时文件替换表 A 的数据文件。

可以看到,与“图1”过程的不同之处在于,由于日志文件记录和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。

上面只是重建表过程的优化,那么一个 ALTER 语句真正执行时,其实 Online DDL 的过程是下面这样的:

1. DDL 启动时拿 MDL 写锁

2. 然后降级成 MDL 读锁

3. 真正做 DDL

4. 升级成 MDL 写锁

5. 释放 MDL 锁

其中 MDL 元数据锁在整个过程的 1、2、4、5 步骤如果没有锁冲突,执行时间非常短,如果说有大事务或者大的查询已经持有 MDL 锁,那么在 DDL 的第一步就会进行阻塞,需要获取锁,通过 show processlist 查看进行状态为 waiting meta data lock。而在第3步占用了 DDL 绝大部分时间,因为要重建表,这期间这个表可以正常读写数据,是因此称为“online”。而非“online”模式,也就是 Copy 表的方式与整个“Online DDL”操作步骤是一样的,也需要拿 MDL 锁,然后释放;区别主要在第 3 步。

可以看到 DDL 语句在启动的时候需要获取 MDL 写锁,但是这个写锁在真正拷贝数据之前就退化成读锁了。首先获取 MDL 写锁是为了确保没有其他线程在用这个表。为什么要退化呢?就是为了实现 Online DDL,MDL 读锁不会阻塞增删改操作。

那为什么不干脆直接解锁呢?其实为了保护自己,禁止其他线程对这个表同时做 DDL。

而对于一个大表来说,Online DDL 最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。所以,相对于整个 DDL 过程来说,锁的时间非常短。对业务来说,就可以认为是 Online 的。

需要补充说明的是,上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,还是推荐使用 GitHub 开源的 gh-ost 来做。

你可能注意到了,在“图1”中,我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表,是在 server 层创建的。而在“图2”中,根据表 A 重建出来的数据是放在“tmp_file”里的,这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说,没有把数据挪动到临时表,是一个“原地”操作,这就是“inplace”名称的来源。

所以,如果你有一个 1TB 的表,现在磁盘间是 1.2TB,能不能做一个 inplace 的 DDL 呢?

答案是不能。因为,tmp_file 也是要占用临时空间的。我们重建表的这个语句 alter table t engine=InnoDB,其实隐含的意思是:

跟 inplace 对应的就是拷贝表的方式了,用法是:

当你使用 ALGORITHM=copy 的时候,表示的是强制拷贝表,对应的流程就是“图1”的操作过程。

这样说你可能会觉得,inplace 跟 Online 是不是就是一个意思? 其实不是的,只是在重建表这个逻辑中刚好是这样而已。

比如,如果我要给 InnoDB 表的一个字段加全文索引,写法是:

这个过程是 inplace 的,但会阻塞增删改操作,是非 Online 的。 如果说这两个逻辑之间的关系是什么的话,可以概括为:

1. DDL 过程如果是 Online 的,就一定是 inplace 的;

2. 反过来未必,也就是说 inplace 的 DDL,有可能不是 Online 的。截止到 MySQL 8.0,添加全文索引(FULLTEXT index)和空间索引 (SPATIAL index) 就属于这种情况。

对于不支持 Inplace 的 DDL 操作采用 Copy 方式,比如修改列类型,删除主键,修改字符集等,这些操作都会导致记录格式发生变化,目前无法使用 Inplace 方式。对于 Inplace 方式,MySQL 内部以”是否修改记录格式”为基准也分为两类,一类需要重建表(重新组织记录),比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等;另外一类是只需要修改表的元数据,比如删除索引、修改列名不改变数据类型、修改列默认值、修改列自增值等。MySQL 将这两类方式分别称为 rebuild 和 no-rebuild 方式。

此外 ALTER 语句还有 LOCK 选项控制是否锁表,根据不同的 DDL 操作类型有不同的表现。默认 MySQL 尽可能不去锁表,但是像修改主键这样的昂贵操作不得不选择锁表。

LOCK=NONE,即 DDL 期间允许并发读写涉及的表,比如为了保证 ALTER TABLE 时不影响用户注册或支付,可以明确指定,好处是如果不幸该 ALTER 语句不支持对该表的继续写入操作,则会提示失败,而不会直接发到库上执行。ALGORITHM=COPY 的默认 LOCK 级别。

LOCK=SHARED,即 DDL 期间表上的写操作会被阻塞,但不影响读取。

LOCK=DEFAULT,即 DDL 开始时,让 MySQL 自己去判断 LOCK 的模式,原则是 MySQL 尽可能不去锁表。

LOCK=EXCLUSIVE,即 DDL 期间该表不可用,堵塞任何读写请求。如果你想 ALTER 操作在最短的时间内完成,或者表短时间内不可用能接受,可以手动指定。

最后,你可能会碰到这种情况,对一个表重建后,发现表空间不仅没变小,还稍微大了一点儿,比如 1TB 变成了 1.01TB。

这主要是因为原表是紧凑的,而在重建表的时候,InnoDB 不会把整张表占满,每个页留了 1/16 给后续的更新使用。也就是说,其实重建表之后不是“最”紧凑的。假如是这么一个过程:

1. 将表 t 重建一次;

2. 插入一部分数据,但是插入的这些数据,用掉了一部分的预留空间;

3. 这种情况下,再重建一次表 t,就可能会出现问题中的现象。

我们怎么判断 DDL 执行是 Inplace 还是 Copy 的方式呢?一个最直观的判断方法是看命令执行后影响的行数,没有新建临时表的话影响的行数是0。也就是说如果是 Inplace 方式则影响行数是0,如果是 Copy 方式就会显示影响行数为整表记录数。

还有如果将 ALTER 操作显式的放到事务里,MySQL 默认会提交 ALTER 语句前面的事务,然后自己独立执行 ALTER 语句。即无法满足在“可重复读”隔离级别下,在一个事务更改表结构后不提交,然后在另一个事务查询的时候不会看见新的表结构。以后当元数据信息都下沉到了 InnoDB 层后,可能会满足这个表结构修改也能实现可重复问题问题。

有同学问到使用 optimize table、analyze table 和 alter table 这三种方式重建表的区别。这里,我顺便再简单和你解释一下。 从 MySQL 5.6 版本开始,alter table t engine = InnoDB(也就是 recreate)默认的就是上面图 4 的流程了; analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁; optimize table t 等于 recreate+analyze。

三、Online DDL状态

从上面的介绍可以看出,不是 MySQL 5.6 支持 Online DDL 就可以随心所欲的 ALTER TABLE,锁不锁表要看情况。

提示:下表根据官方 Summary of Online Status for DDL Operations 整理挑选的常用操作。

  • In-Place 为 Yes 是优选项,说明该操作支持 In-Place。
  • Rebuilds Table?为 No 是优选项,因为为 Yes 需要重建表,就是 Copy 数据,大部分情况与 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 系统表信息和数据字典,标记该所以不存在,标记所占用的表空间可以被新索引或数据行重新利用。

下“图3”是一个包含 1078880 行的表,进行的 Online DDL 和 PT ONLINE 模式一些修改操作之间的比较结果(ONLINE DDL VS PT-ONLINE-SCHEMA-CHANGE):

MySQL InnoDB Online DDL

四、Online DDL测试

MySQL 5.6 支持的 ONLINE DDL 其各种算法测试。

可以看出来更改数据类型不支持 INPLACE 算法,只支持 COPY。并且使用 COPY 算法时,不支持 LOCK=NONE。

但是可以支持 LOCK=SHARED,就是允许读操作。

对于 tinyint、int、smallint、bigint 等数值类型的数据类型,自身位大小的增大或减小是支持 ONLINE 的(注意:不支持从 tinyint 变更为 int),如下:

另外,如果是更改字段属性(类型不变)根据类型不同支持的 Online DDL 也是有限的,如把允许为 NULL 变更为不允许为NULL就不支持。

但是在线支持变更 comment 属性。

需要注意的是,在 MySQL 5.6 之前使用 ALTER TABLE … ALGORITHM=INPLACE 的表不支持包含时间列(DATE,DATETIME,TIMESTAMP),不然会报错。

MySQL 5.7 版本支持重命名索引和修改 VARCHAR 的大小(增大不能减小),且无需 table-copy(秒级增加 varchar 大小)。这两项操作在之前的版本中,都需要重建索引或表,适用于各引擎。

但存在限制,即只支持 0~255 字节内的或者 255 以上字节间的增加,也就是说若从 254 增到 256 时不能使用 INPLACE 算法(增加到 255 可以),必须使用 COPY 算法,否侧报错。这个原理就是 VARCHAR 会在头部存储一个长度,如果小于 255 就是一个 BYTES 字节,8 位;如果大于 255 当然就需要两个字节了。头部都变了,自然要重新 copy table 了。另外使用 INPLACE 算法缩小 VARCHAR 的 ALTER TABLE 也是不支持的,必须用 COPY 算法。

最后说一下 Online DDL 操作根据数据量的不同的耗时时间,我在生产环境做过两千五百万数据的 ALTER 操作,耗时 12 分钟 31 秒,我的硬件配置是 32 核 32G。按照这个时间可以根据硬件不同大概估算出数据量不同时的耗时时间。比如 100 万数据估计耗时:1000000 / (25000000 / (12*60+30)) = 30秒。

五、Online DDL限制

  • 在 alter table 时,如果涉及到 table copy 操作,要确保 datadir 目录有足够的磁盘空间,能够放的下整张表,因为拷贝表的的操作是直接在数据目录下进行的。
  • 添加索引无需 table copy,但要确保 tmpdir 目录足够存下索引一列的数据(如果是组合索引,当前临时排序文件一合并到原表上就会删除)。
  • 在主从环境下,主库执行 alter 命令在完成之前是不会进入 binlog 记录事件,如果允许 dml 操作则不影响记录时间,所以期间不会导致延迟。然而,由于从库是单个 SQL Thread 按顺序应用 relay log,轮到 ALTER 语句时直到执行完才能下一条,所以从库会在主库 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 操作的回滚可能是昂贵的。

六、Online DDL实施细节

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

  • Prepare 阶段

1. 创建新的临时frm文件(与InnoDB无关)。

2. 持有EXCLUSIVE-MDL锁,禁止读写。

3. 根据alter类型,确定执行方式(copy,online-rebuild,online-norebuild)。假如是Add Index,则选择online no-rebuild即Inplace方式。

4. 更新数据字典的内存对象。

5. 分配row_log对象记录增量(仅rebuild类型需要)。

6. 生成新的临时ibd文件(仅rebuild类型需要)。

  • DDL 执行阶段

1. 降级EXCLUSIVE-MDL锁,允许读写。

2. 扫描old_table的聚集索引每一条记录rec。

3. 遍历新表的聚集索引和二级索引,逐一处理。

4. 根据rec构造对应的索引项。

5. 将构造索引项插入sort_buffer块排序。

6. 将sort_buffer块更新到新的索引上。

7. 记录DDL执行过程中产生的增量(仅rebuild类型需要)。

8. 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)。

9. 重放row_log间产生DML操作append到row_log最后一个Block。

  • Commit阶段

1. 当前Block为row_log最后一个时,禁止读写,升级到EXCLUSIVE-MDL锁。

2. 重做row_log中最后一部分增量。

3. 更新innodb的数据字典表。

4. 提交事务(刷事务的redo日志)。

5. 修改统计信息。

6. rename临时idb文件,frm文件。

7. 变更完成。

这有一个导图挺直观的:http://blog.itpub.net/22664653/viewspace-2056953 添加列时由于需要 copy table,row_log 会重放到新表上(临时 ibd 文件),直到最后一个 block,锁住原表禁止更新。row_log 记录了 DDL 变更过程中新产生的 DML 操作,并在 DDL 执行的最后将其应用到新的表中,保证数据完整性。

七、添加删除索引实现

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

  • Copy方式

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

1. 新建一个带有新索引的临时表。

2. 然后锁原表,进制DML操作,允许读操作。

3. 将原表数据全部拷贝到临时表(无排序,一行行拷贝)。

4. 然后Rename,升级字典锁,禁止读写。

5. 完成创建索引的操作。

  • Inplace方式

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

1. 新建一个带有新索引的临时表。

2. 然后锁原表,禁止 DML 操作,允许读操作。

3. 读取聚集索引,构造新的索引项,排序并插入新索引。

4. 然后 Rename,升级字典锁,禁止读写。

5. 完成创建索引的操作。

  • Online方式

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

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

与 Copy 方式相比,Online Add Index 采用的是 Inplace 方式,无需 Copy Table,减少了空间开销;与此同时,Online Add Index 只有在重放 Row Log 最后一个 Block 时锁表,减少了锁表的时间。

与 Inplace 方式相比,Online Add Index 吸收了 Inplace 方式的优势,却减少了锁表的时间。

八、Online DDL空间要求

上面在说 Online DDL 使用限制时也说了 innodb_online_alter_log_max_size 参数,这个参数是 MySQL 5.6.6 引入的,因为在 Online DDL 过程中需要保持 delete、update、insert 这些数据,所以需要一个日志去保持,这个参数就是限制这个日志的最大大小,当 DDL 过程中需要的这个日志的大小比这个限制还大的时候就会报错。

具体错误大概如下:

该参数为动态参数且全局的,可通过如下命令加大(不一定是我下面设置的大小,这个根据你的情况)

官方文档关于这个参数的解释:

innodb_online_alter_log_max_size Type integer
Default 134217728
Min Value 65536
Max Value 2**64-1

该参数如果太小有可能导致 DDL 失败,这期间所有的未提交的 DML 操作都会回滚;但是如果太大会可能会导致 DDL 操作后最后锁定表的时间更长(锁定表,应用日志到表上)。

八、若干问题

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不会存在问题。

十、ONLINE DDL BUG

这个问题在 Online DDL 使用限制也提到了,就是在执行一个允许并发 DML 操作的 ALTER TABLE 时,ALTER TABLE 结束之前这个线程会应用 Online log 记录的增量修改,而这些修改是其它线程里产生的,所以有可能会遇到重复键值错误 (ERROR 1062 (23000): Duplicate entry)。

当你 Alter Add Column 时使用 ALGORITHM=INPLACE,LOCK=NONE 时,虽然属于 in-place,但也还是会有copy表的操作,但可以并发执行dml。在DDL结束之前会应用online log记录的这段时间的增量修改,此时如果表有大量数据写入(有主键或唯一索引),你可能会碰到如下bug#76895bug # 77572

解决办法也是有的,多尝试几次,可能会成功;另外使用OSC工具或者使用ALGORITHM=COPY,LOCK=SHARED算法加读锁。据说此BUG修复在5.6.28版本,不好重现,所以没有验证。

<参考>

http://hedengcheng.com/?p=421

https://mp.weixin.qq.com/s/HsHn79zQrQXGJS4sQgsNUw

https://cloud.tencent.com/developer/article/1006513

https://mp.weixin.qq.com/s/iNFB6f5piIUq0eX8rwZGqQ

http://blog.itpub.net/attachment/201603/14/22664653_14579330164s0X.jpg


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (12)or分享 (0)
关于作者:

您必须 登录 才能发表评论!

(1)个小伙伴在吐槽