Mysql Online DDL的使用详解

正文

Online DDL在MySQL 5.6才开始支持的，在5.5及之前版本，使用alter table/create index等命令进行表结构修改操作均会锁表，这在生产环境上明显是不可接受的。

在MySQL 5.7，Online DDL在性能和稳定性上不断得到优化，性能有显著优势，且对业务负载影响小，停写时间可控，相对pt-osc/gh-ost来说，无需安装第三方依赖包，同时支持Inplace算法的Online DDL，由于无需拷表，所需磁盘空间也更小。

先来看一个常见的DDL语句：

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

其中，LOCK描述了DDL期间运行的并发程度，ALGORITHM描述了DDL的实现方式

LOCK参数

  1. LOCK=NONE:允许并发的查询和DML操作
  2. LOCK=SHARED:允许并发的查询，但阻塞DML操作
  3. LOCK=DEFAULT: 由系统决定，允许尽可能多的并发性(并发查询、DML或两者)。如果省略LOCK子句相当于指定LOCK=DEFAULT
  4. LOCK=EXCLUSIVE:阻塞并发查询和DML操作。

ALGORITHM参数

  1. ALGORITHM=COPY：采用拷表方式进行表变更，与pt-osc/gh-ost类似；
  2. ALGORITHM=INPLACE：仅需要进行引擎层数据改动，不涉及Server层；

COPY TABLE流程

  1. 首先建立临时表，表结构为ALTAR TABLE更改后的结构
  2. 将原表中数据导入到临时表（server层创建临时表，会有显示的IBD文件）
  3. 删除原表
  4. 将临时表rename为原来的表名

同时这一过程中，为了保持数据的一致性，中间复制数据时（Copy Table）全程锁表只读，如果有写请求进来将无法提供服务，将导致连接数爆张。

IN-PLACE流程

  1. 建立一个临时文件，扫描原表主键的所有数据页
  2. 用数据页中原表记录生成B+树，存储到临时文件中（innodb_temp_data_file_path临时表空间下创建临时文件）
  3. 生成临时文件的过程中，将所有对原表的操作记在一个日志文件(rowlog)中
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个辑数据上与原表相同
  5. 数据文件（日志文件记录和重放操作）
  6. 用临时文件替换原表数据文件

这一过程中，alter 语句在启动的时候获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁，也就是说在最耗时的copy数据到临时文件的过程中，原表是可以进行dml操作的，仅仅会在最后的新旧表切换阶段加锁，这个rename的时间就非常快了。

允许并发DML的DDL操作

  • 创建/新增二级索引
  • 重命名二级索引
  • 删除二级索引
  • 改变索引类型(USING {BTREE | HASH})
  • 添加主键（expensive cost）
  • 删除主键并增加另一个（expensive cost）(ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;)
  • 新增列 （expensive cost）
  • 删除列 （expensive cost）
  • 重命名列
  • 列重新排序 （expensive cost）
  • 改变列默认值
  • 删除列默认值
  • 改变列自增值
  • 设置列属性null/not null （expensive cost）
  • 修改枚举或集合列的定义
  • Change ROW_FORMAT
  • Change key block size

标记为expensive cost的操作虽然允许OnlineDDL,但本身对服务器IO,CPU都会造成较高负担，同时会导致复制阻塞，造成另一种形式的从库复制延迟，所以如果是大表，建议业务低峰期执行

不允许并发DML的DDL操作

  • 添加全文索引
  • 添加空间索引
  • 删除主键
  • 改变列数据类型
  • 添加自增列（新增列->变为自增列）
  • 变更表字符集
  • 修改数据类型长度
    • 特例：varchar字符长度从10变更到小于255 采用inplace方式不会锁表；从255变更到10会锁表；

以上就是Mysql Online DDL的使用详解的详细内容，更多关于Mysql Online DDL的使用的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL在线DDL gh-ost使用总结

    MySQL在线DDL gh-ost使用总结

    背景: 作为一个DBA,大表的DDL的变更大部分都是使用Percona的pt-online-schema-change,本文说明下另一种工具gh-ost的使用:不依赖于触发器,是因为他是通过模拟从库,在row binlog中获取增量变更,再异步应用到ghost表的.在使用gh-ost之前,可以先看GitHub 开源的 MySQL 在线更改 Schema 工具[转]文章或则官网了解其特性和原理.本文只对使用进行说明. 说明: 1)下载安装:https://github.com/github/gh-

  • MySQL ddl语句的使用

    前言 SQL的语言分类主要包含如下几种: DDL 数据定义语言 create.drop.alter 数据定义语言 create.drop.alter 语句 . DML 数据操纵语言 insert.delete.update 定义对数据库记录的增.删.改操作. DQL 数据库查询语言 select 定义对数据库记录的查询操作. DCL 数据库控制语言 grant.remove 定义对数据库.表.字段.用户的访问权限和安全级别. (授权grant,收回权限revoke等). TCL 事务控制语言 s

  • MySQL数据定义语言DDL的基础语句

    MySQL DDL 语句 什么是DDL,DML. DDL 是数据定义语言,就是对数据库,表层面的操作,如 CREATE,ALTER,DROP.DML 是数据操作语言,也就是对表中数据的增删改查,如 SELECT,UPDATE,INSERT,DELETE. 假设现在有数据库 lian_xi 里面有表 user 和 orders; 1.1登陆MySQL环境 在cmd窗口中输入mysql -u root -p登录MySQL环境 1.2查看数据库 Show databases; 1.3使用数据库 Use

  • MySQL8.0新特性之支持原子DDL语句

    MySQL 8.0开始支持原子数据定义语言(DDL)语句.此功能称为原子DDL.原子DDL语句将与DDL操作关联的数据字典更新,存储引擎操作和二进制日志写入组合到单个原子事务中.即使服务器在操作期间暂停,也会提交事务,并将适用的更改保留到数据字典,存储引擎和二进制日志,或者回滚事务. 通过在MySQL 8.0中引入MySQL数据字典,可以实现Atomic DDL.在早期的MySQL版本中,元数据存储在元数据文件,非事务性表和存储引擎特定的字典中,这需要中间提交.MySQL数据字典提供的集中式事务

  • 解决MySQL 5.7中定位DDL被阻塞的问题

    在上篇文章<MySQL表结构变更,不可不知的Metadata Lock>中,我们介绍了MDL引入的背景,及基本概念,从"道"的层面知道了什么是MDL.下面就从"术"的层面看看如何定位MDL的相关问题. 在MySQL 5.7中,针对MDL,引入了一张新表performance_schema.metadata_locks,该表可对外展示MDL的相关信息,包括其作用对象,类型及持有等待情况. 开启MDL的instrument 但是相关instrument并没有

  • MySQL曝中间人攻击Riddle漏洞可致用户名密码泄露的处理方法

    针对MySQL 5.5和5.6版本的Riddle漏洞会经由中间人攻击泄露用户名密码信息.请尽快更新到5.7版本. Riddle漏洞存在于DBMS Oracle MySQL中,攻击者可以利用漏洞和中间人身份窃取用户名和密码. "Riddle是一个在Oracle MySQL 5.5和5.6客户端数据库中发现的高危安全漏洞.允许攻击者在中间人位置使用Riddle漏洞破坏MySQL客户端和服务器之间的SSL配置连接."漏洞描述写道."此漏洞是一个非常危险的漏洞,因为首先它会影响MyS

  • 详解MySQL8.0原子DDL语法

    详解MySQL8.0原子DDL语法

    01 原子DDL介绍 原子DDL语句将数据字典更新.存储引擎操作和与DDL操作相关联的二进制日志写入合并到单个原子操作中.该操作要么提交,对数据字典.存储引擎和二进制日志保留适用的更改,要么回滚. 在MySQL8.0中,原子DDL操作这一特性,支持表相关操作,例如create table.drop table等,也支持非表相关操作,例如create routine.drop trigger等. 其中: 支持的表操作包含: drop.create.alter(操作对象是databases, tab

  • MySQL DDL 引发的同步延迟该如何解决

    MySQL DDL 引发的同步延迟该如何解决

    前言 写作案例分析,主要是工具介绍&推荐.MySQL 的同步机制比较单纯,主库上执行过的 DML 和 DDL 会在从库上再执行一次,那么主库上需要 10min 才能执行完的 DDL 理论上在从库至少也要花费 10min 才能执行完,这意味着从库的同步会延迟 10min 以上,等 DDL 执行完之后才会继续追同步. 解决方案 从 MySQL 的同步原理来看,主要是 DDL 这个单独的操作会花费太久的时间,导致从库也会被卡主.那么解决这个问题的办法就很容易想到:"拆解" DDL 的

  • MySQL8.0 DDL原子性特性及实现原理

    1. DDL原子性概述 8.0之前并没有统一的数据字典dd,server层和引擎层各有一套元数据,sever层的元数据包括(.frm,.opt,.par,.trg等),用于存储表定义,分区表定义,触发器定义等信息:innodb层也有自己一套元数据,包括表信息,索引信息等,这两套元数据并没有机制保证一致性,这就导致了在异常情况下可能存在元数据不一致问题,一种典型场景下,删表操作,sever层的frm已经成功删除了,但引擎层数据字典并没有更新,导致再建重名表失败的问题.同样的,比如drop tabl

  • MySQL在线DDL工具 gh-ost的原理解析

    MySQL在线DDL工具 gh-ost的原理解析

    一.简介 gh-ost基于 golang 语言,是 github 开源的一个 DDL 工具,是 GitHub's Online Schema Transmogrifier/Transfigurator/Transformer/Thingy 的缩写,意思是 GitHub 的在线表定义转换器. 1.1 原理 主要实现原理,首先建两张表,一张_gho的影子表,gh-ost会将原表数据以及增量数据都应用到这个表,最后会将这个表和原表做次表名切换,另一张是_ghc表,这个表是存放changelog的数据,

  • Mysql DDL常见操作汇总

    库的管理 创建库 create database [if not exists] 库名; 删除库 drop databases [if exists] 库名; 建库通用的写法 drop database if exists 旧库名; create database 新库名; 示例 mysql> show databases like 'javacode2018'; +-------------------------+ | Database (javacode2018) | +---------

  • 解析MySQL8.0新特性——事务性数据字典与原子DDL

    前言 事务性数据字典与原子DDL,是MySQL 8.0推出的两个非常重要的新特性,之所以将这两个新特性放在一起,是因为两者密切相关,事务性数据字典是前提,原子DDL是一个重要应用场景. MySQL 8.0之前的数据字典 MySQL 8.0之前的数据字典,主要由以下三部分组成: (1)操作系统文件 db.opt:数据库元数据信息 frm:表元数据信息 par:表分区元数据信息 TRN/TRG:触发器元数据信息 ddl_log.log:DDL过程中产生的元数据信息 (2)mysql库下的非InnoD

随机推荐