简析Innodb Online DDL

源于最近有几次对于一些大表进行DDL操作,但可能因为对InnoDB引擎的Online DDL的原理所知甚少,以至于有一些同学一开口就是,"执行DDL时会锁表,如果数据量太大锁表时间太长,会阻塞到线上业务",为确保以后进行DDL操作时能够做到心中有数,对Online DDL做一些总结。

前身

MySQL于5.6版本推出Online DDL,那么在没有Online DDL前,它是如何做DDL?主要分为两种形式。

copy table

  • create temp table,创建临时表结构,保持与原表结构一致
  • lock original table,不允许对原表进行DML操作,仅允许Queries
  • ddl on temp table,在临时表上执行DDL (lock)
  • copy original table data into temp table,将原表数据复制到临时表 (lock)
  • rename temp tablename to oringal tablename,对原表加锁并进行rename操作,不允许DML & Queries,直至整个过程完成。

从整个执行过程来分析,为了保证执行期间表数据的一致性,必须通过加锁来阻止DML操作,只允许Queries。当表数据量过大时,在复制阶段所需要的时间过长导致整个语句在执行期间加锁阻塞其他事务的DML语句,故而导致应用连接数撑爆,大量事务超时等问题。

inplace

inplace又称为fast index creation,仅支持索引的创建。

  • create frm, 创建数据字典
  • lock original table,不允许对原表进行DML操作,仅允许Queries
  • copy table,按照聚簇索引顺序读取数据,构造新的索引项,顺序插入新的索引页
  • lock original table,锁表,不允许DML & Queries
  • rename,替换frm文件,完成DDL过程

inplace方式比起需要copy table自然是更优的,因为只需要从聚簇索引读取新索引的列项出来构造新索引页,其他索引不受影响。但是,无论是copy table 还是 inplace在执行操作的过程中都需要长时间锁表,阻塞DML语句,毫无疑问对于服务运行而言简直是毁灭式的行为,因此online ddl迫在眉睫。

在讨论online ddl前,有必要先了解几个点,其中包括 Innodb索引组织形式,LOCK CAUSEMETADATA LOCK

索引组织形式

关于索引的官方文档

开发同学在构建数据库表时,都会根据业务特点选择合适的索引,因为索引的目的是加速搜索。从官网文档中可以看到Innodb存储引擎将索引分为Clustered Index以及Secondary Index,称为聚集索引与二级索引(也称聚簇索引,非聚簇索引)。

二者如何区分?Innodb只会有一个聚簇索引,聚簇索引的叶子节点上存储的是Row Data,一般情况下是表定义中的PRIMARY KEY,如果没有则选择表定义中第一个非空UNIQUE KEY,如果表定义中即没有PK,也没有UK,则会通过Innodb隐藏的一个ROW ID作为索引。而处理聚簇索引之外的其他索引,皆称为二级索引。二级索引的叶子节点上存储的是聚簇索引的值。

正因为这个特征,很多企业定义的SQL规范中都会有类似:

  • PK尽量选择长整型,且趋势递增,推荐使用AUTO INCREMENT
  • PK不适合较长的字符串
  • ...

LOCK CAUSE

关于LOCK CAUSE官方文档

默认情况下,MySQL会选择尽可能的轻量的锁来完成DDL操作,在某些特定情况下可以更加严格的锁来完成操作。比如前文提到,DDL过程中完成不允许DML,仅允许Queries,也可以不允许DML & Queries等。

  • LOCK=NONE
    Permits concurrent queries and DML.
  • LOCK=SHARED
    Permits concurrent queries but blocks DML.
  • LOCK=DEFAULT
    Permits as much concurrency as possible (concurrent queries, DML, or both). Omitting the LOCK clause is the same as specifying LOCK=DEFAULT.
  • LOCK=EXCLUSIVE
    Blocks concurrent queries and DML

METADATA LOCK

关于METADATA LOCK官方文档
关于METADATA LOCK补充文档

元数据锁,是MySQL的表锁之一,属于隐式锁(另一种是显式锁,通过lock table ... with read/write指定)。在补充文档中可以了解到元数据锁的由来,也对其进行了解释。补充文档中提及元数据锁有11种类型,常用的有MDL_SHARED_READ,MDL_SHARED_WRITEMDL_EXCLUSIVE。元数据锁是一个跟随事务结束而释放的锁,由MySQL控制,保护处于事务中的表元数据的一致性。

  • MDL_SHARED_READQueries时加锁,属于共享锁
  • MDL_SHARED_WRITEDML时加锁,属于共享锁
  • MDL_EXCLUSIVEDDL时加锁,属于独占锁

Online DDL

简述

MySQL在5.6.7版本推了Online DDL能力,主要是在基于原有的fast index creation上增强实现。其次,假定前提,衡量DDL是否足够Online在于是否长时间允许DML

目前Online DDL支持两种形式

  • COPY
  • INPLACE

MySQL如何选择该两种形式呢?

  • 支持指定,即在执行语句上指定ALGORITHM=INPLACE / COPY
  • 默认对于不支持Online DDLsql语句则采用COPY,相反则采用INPLACE

INPLACE会根据是否涉及到修改行记录格式分为三种情形

  • Rebuilds Table,修改了行记录格式,比如修改列类类型、增减列等
  • Not Rebuilds Table & Not Only Modifies Metadata,不需要重建表但是也不仅仅只是修改元数据,比如增加索引。
  • Only Modifies Metadata,仅修改元数据,比如删除索引、设置列默认值,重命名列名等

Online DDL支持选项

  • ALGORITHM={COPY|INPLACE}
  • LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE},参照前文LOCK CAUSE

可以从官方文档查看Online DDL支持情况。

有几个点需要特意说明

  • INPLACE并不表示绝对支持并行DML,但是COPY绝对不支持并行DML
  • 简单区分 INPLACECOPY,在于是否需要创建临时表
  • 简单区分 INPLACECOPYCOPY主要由Server支持,INPLACE主要由Innodb支持
  • INPLACE不是不需要额外的数据空间,取决是否为Only Modifies Metadata
  • Only Modifies Metadata不需要Rebuilds Table,不需要Rebuilds Table的不一定是Only Modifies Metadata
image.png

实现原理

Online DDL主要分为3个阶段,PREPAREEXECUTECOMMIT

  • PREPARE
    • 创建新的临时frm文件
    • 持有MDL_EXCLUSIVE锁,禁止读写
    • 根据alter类型,确定执行方式(copyrebuildnot-rebuild
    • 更新数据字典的内存对象
    • 若是需要rebuild,分配row_log对象用于记录增量
    • 若是需要rebuild,生成新的临时ibd文件
  • EXECUTE
    • 如果是仅修改元数据:
      • 这部分无操作
    • 其他,则是:
      • 降低MDL_EXCLUSIVE锁,允许DML & Queries(copy 不允许写)
      • 记录DDL执行过程中产生的增量row-log(非only modify metadata类型需要)
      • 扫描old_table的聚集索引每一条记录record
      • 遍历新表的聚集索引和二级索引,逐一处理
      • 根据record构造对应的索引项
      • 将构造索引项插入sort_buffer
      • sort_buffer块插入新的索引
      • row_log中的操作应用到新临时表中,应用到最后一个Block
  • COMMIT
    • 升级到MDL_EXECLUSIVE锁,禁止读写
    • 重做最后一部分的row_log增量
    • 更新innodb的数据字典表
    • 提交事务,写redo日志
    • 修改统计信息
    • rename临时的ibd文件、frm文件
    • DDL完成

在整个Online DDL的过程中,并非是完全的Permits Concurrent DML,但是由于整个过程中持有MDL_EXCLUSIVE锁的时间较短,所以近似的认为整个过程是Permits Concurrent DML

Online DDL带来的优势

  • Online DDL期间,QueriesDML操作在多数情况下可以正常执行,锁表时间大大减少。
  • 允许INPLACE操作的DDL,避免COPY方式的磁盘IO及CPU资源,减少对数据库的整体负荷,使得在DDL期间,能够维持数据库的高性能及高吞吐量;
  • 允许INPLACE操作的 DDL,比需要COPY到临时表的操作要更少占用buffer pool,避免以往DDL过程中性能的临时下降,因为需要拷贝数据到临时表,这个过程会占用到buffer pool,导致内存中的部分频繁访问的数据会被清理出去。

资料

https://www.cnblogs.com/cchust/p/4639397.html
https://www.cnblogs.com/xinysu/p/6732646.html
https://www.cnblogs.com/dbabd/p/10381942.html
http://mysql.taobao.org/monthly/2021/03/06/

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,163评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,301评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 162,089评论 0 352
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,093评论 1 292
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,110评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,079评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,005评论 3 417
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,840评论 0 273
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,278评论 1 310
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,497评论 2 332
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,667评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,394评论 5 343
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,980评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,628评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,796评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,649评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,548评论 2 352

推荐阅读更多精彩内容