简介MySQL Online DDL

  • 时间:
  • 浏览:1

这里的老事务是发生创建索引操作却说 就却说却说刚开始 英文运行、创建操作却说却说刚开始 英文还没人 提交的事务。考虑以下情况表:

开启session 1,执行:

mysql> create index i_discount on ecs_order_info_bak(discount,tax,agency_id);

inplace:为yes是优选项,说明该操作支持inplace最好的办法 ;

rebuilds table:为no是优选项,大偏离 情况表下是与inplace相反的;

permits DML:为yes是优选项,表示支持读写,能够认为该种DDL支持online最好的办法 ,反之则不支持;

only modifies metadata:参考选项,表示该种DDL是是不是仅仅修改元数据。

首先新建另有一4个空的临时表,表形状是 ALTAR TABLE 新定义的形状;

或者把原表中的数据逐行拷贝到这一 新的临时表中,在插入数据时却说断地更新索引;

在所有数据拷贝却说 删除原表;

最后把临时表rename为却说 的表名;

该索引创建操作绝大偏离 时间消耗在守候MDL锁上,而这却说 却说 ecs_order_info_bak表上的事务未能及时提交引起的。

session 1开启事务后,只访问了t表,并没人 打开ecs_order_info_bak表;

session 2先删除了ecs_order_info_bak表所有pay_fee=34.43 的数据,或者在pay_fee列上创建了索引并成功提交,这时该索引上是没人 34.43这一 值的;

session 1这一 却说 又访问ecs_order_info_bak表,而索引i_pay_fee对session 1 可见,却说 session 1会通过该索引访问表,没人 访问的结果就会返回0 row;

但却说人面,session 1的事务在开启时pay_fee=34.43还并没人 被删除,根据innodb引擎的一致性读形状,此时session 1是能够访问到pay_fee=34.43的数据的。

在online add index期间,也会有锁表难题报告 ,主要在重放row log时,有以下情况表需要锁表:

1、在使用完另有一4个Block,跳转到下另有一4个Block时,需要短暂锁表,判断下另有一4个Block是是不是为Row Log的最后另有一4个Block。若却说 最后另有一4个,跳转完毕后,释放锁;使用Block内的row log不加锁,用户DML操作仍旧能够进行;

2、在使用最后另有一4个Block时,会老是持有锁。此时不允许新的DML操作。保证最后另有一4个Block重放完成却说 ,新索引与聚簇索引记录达到一致情况表。

以上有并是不是锁表情况表的时间都很短,这一 影响是能够接受的。

或者开启session 2 ,执行:

mysql> start transaction;

mysql> select * from ecs_order_info_bak where order_id=142;

online创建索引,遵循的是先创建索引数据字典,后填充数据的最好的办法 。或者最先创建索引数据字典,却说 用户多多线程 运行能够看一遍此索引,但却说 此索引的情况表为ONLINE_INDEX_CREATION,或者索引实际还不让起作用。

很明显,以上有并是不是不同的机制的结果最终产生矛盾,MySQL是却说 解决的:在索引上维护另有一4个trx_id,标识创建此索引的事务ID;若有另有一4个比这一 事务更老的事务,打算使用新建的索引进行快照读,没人 直接报错,错误即如上图所示。上面在介绍创建索引时,在创建索引数据字典却说 标识另有一4个trx_id,也却说这一 意味。

比如为name列(非主键)建立索引时,会遍历聚簇索引,埋点name列的记录并插入到新索引中;此过程原表数据可修改,或者所有涉及到name列的修改记录会保发生Row log中;当遍历完聚簇索引后,再重放Row log中的修改记录,使得新索引与聚簇索引记录达到一致情况表。另外,在重放row log过程中,却说 还有DML操作,没人 会继续追加到row log中,直到重放最后另有一4个row log block却说 锁表,这时不让有追加的DML操作了。

MySQL各版本中,对于DDL的解决最好的办法 是不同的,主要有有并是不是:

1、copy table最好的办法

这是innodb最早支持的最好的办法 。即重建表最好的办法 ,先创建另有一4个目标形状的临时表,或者将原表数据克隆到临时表,再对临时表rename,完成DDL操作。这一 最好的办法 原表可读但不可写,或者消耗一倍的存储空间。

从历史上看,InnoDB表的却说 DDL操作都十分消耗资源。却说 “ALTER TABLE”操作的实现原理却说 :

却说 在mysql的早期版本中,DDL操作却说 锁表会和DML操作发生锁冲突,大大降低并发性。却说 有克隆原表数据,却说 会长时间锁表,没人 读没人 写,DDL操作和DML操作有很严重的冲突。

在MySQL5.5和MySQL5.1版本的innodb表,对create index和drop index操作进行了优化使其解决出显重建表,这一 形状被称之为“fast index creation”。mysql5.6却说却说刚开始 英文增强却说其它类型的alter table操作以解决出显重建表操作,以及允许在DDL期间执行查询和DML操作,却说 兼而有之。在mysql5.7,“alter table rename index”操作也解决了重建表操作。这一 形状现在统称为online DDL。

参考文章

https://www.jb51.net/article/75217.htmhttps://www.cnblogs.com/rayment/p/7762520.html

总体上,onlineDDL具备以下优点:

1、能够提高在繁忙的生产环境中的响应能力和可用性,无论那先 表形状变更操作,长达几小时的不可用是不可接受的;

2、允许您在DDL操作期间调整性能和并发之间的平衡,最好的办法 是选者是是不是完正阻止对表的访问(LOCK=EXCLUSIVE子句),允许查询但不允许DML(LOCK=SHARED子句),或允许对表的完正查询和DML访问(LOCK=NONE 子句)。省略该LOCK子句或指定LOCK=DEFAULT,MySQL将根据操作类型允许尽却说 多的并发;

3、在却说 的情况表下会优先进行就地更改(in-place),而却说 copy table,它解决了磁盘空间使用的临时增加以及克隆表和重建所有二级索引的I / O开销。

参考官方文档:

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-table-operations

当然,仍能够使用多偏离 的ALTER TABLE句子,却说 包括以下十几个 方面:

需要以特定顺序执行的操作,相似于创建索引,后跟使用该索引的外键约束;

所有使用相同特定LOCK 子句的操作,您希望作为另有一4个组成功或失败;

无法进行in-place操作,即仍然克隆和重建表的操作。

这也是为那先 在创建索引的提交阶段会守候打开当前表的所有事务提交,却说 对于那先 事务而言,却说 创建索引一旦成功提交,没人 在那先 事务的活动期内其涉及到的表的形状信息就变了,这与引入MDL机制违背。却说 ,创建索引操作需要守候打开当前表的所有事务提交都可以够提交。

从mysql5.6却说却说刚开始 英文,却说 DDL操作过程都进行了改进,出显了Online DDL。所谓Online DDL却说指相似于于DDL操作和DML基本能够能不发生冲突(却说 绝对不冲突),表在执行DDL操作时同样能够执行DML操作。mysql5.6时却说偏离 DDL操作online化,到现在绝大偏离 DDL却说 Online DDL。

2、inplace最好的办法

这是在mysql5.5版本里却说却说刚开始 英文支持的最好的办法 。却说直接在原表上进行操作,不让出显数据拷贝。原表支持可读或可写。

测试环境为mysql-5.7.18版本,ecs_order_info_bak表数据量30w,65 columns。执行下面操作:

在引入online DDL却说 ,通常的做法是将却说DDL操作组合到另有一4个ALTER TABLE 句子中。却说 每个ALTER TABLE 句子都涉及克隆和重建表操作,却说 一次性对同另有一4个表进行多次更改会更有效,却说 多个更改只需要单个重建表操作。缺点是涉及DDL操作的SQL代码难以维护并在不同的脚本中难以重用。却说 每次特定更改却说 同,则却说 ALTER TABLE为每个略有不同的方案构建新的组合。

对于能够in-place最好的办法 的online DDL操作,现在能够将它们分成单独的ALTER TABLE句子,以便更轻松地编写脚本和维护,而不让牺牲数率。

Row log是有并是不是独占形状,它却说 redo log。它以Block的最好的办法 管理DML记录的存放,另有一4个Block的大小为由参数innodb_sort_buffer_size控制,默认大小为1M,初始化阶段会申请另有一4个Block。

再执行show processlist观察情况表:



观察上图,发现session 1进入了守候MDL锁情况表,这是却说 session 2的事务长时间未提交意味的;当他们歌词 歌词 当他们歌词 歌词 儿在session 2上执行commit操作后,session 1返回如下:

mysql> create index i_discount on ecs_order_info_bak(discount,tax,agency_id);

Query OK, 0 rows affected (10 min 40.82 sec)

Records: 0 Duplicates: 0 Warnings: 0

上述情况表,在session 1执行“select * from ecs_order_info_bak where pay_fee=34.43”操作却说 报出如下错误:

以下是从mysql5.7官方文档中,列出常用的DDL的执行最好的办法

另外开启session 3,观察另有一4个多多线程 运行情况表:

一却说却说刚开始 英文观察到的情况表如下:



这表明session 1正在执行创建索引过程,暂无异常;

能够判断的是,在create index操作的提交阶段有MDL锁的升级,从而意味出显Waiting for table metadata lock情况表。能够从以下另有一4个淬硬层 考虑:

1、在跳转row_log block和重用最后另有一4个block时,却说 升级到EXCLUSIVE-MDL锁;

2、MySQL的MDL锁的引入却说为了保证在事务运行期间元数据信息的一致性,却说 上例的create index操作需要等到 打开当前表的所有事务提交或回滚后 能够提交,或者对于session 2的只读事务来说其活动期间的元数据信息就不一致了;

DDL中,create index操作是最常见的,了解其online最好的办法 原理十分重要。

其简要过程如下:

1、获取目标表的EXCLUSIVE-MDL锁;这需要保证表上没人 任何读写事务,或者操作会进入守候情况表:waiting for table metadata;

2、根据alter操作类型选者执行最好的办法 ;却说 指定了ALGORITHM类型会判断是是不是允许,却说 未指定ALGORITHM则采用默认类型;而create index操作却说 in-place最好的办法 ;

3、创建索引数据字典,并为索引标识trx_id,记录创建此索引的事务;分配row_log对象;

4、降级MDL锁,此却说表允许读写操作;

5、却说却说刚开始 英文正式的创建索引过程:会遍历聚簇索引,埋点对应列的记录插入到新索引中;此外该过程中的DML操作却说 记录到row-log中;

6、聚簇索引遍历完成后,却说却说刚开始 英文重用row_log中的内容,在重用最后一块row_log却说 升级到EXCLUSIVE-MDL锁,不允许DML操作;

7、操作提交阶段:守候打开当前表的所有只读事务提交,更新innodb的数据字典表,提交事务,释放MDL锁等。