全部产品
云市场

不锁表结构变更

更新时间:2020-03-18 15:09:39

背景

在MySQL数据库类型使用愈发频繁的今天,随着业务的发展需要对已在线上运行的大表进行结构修改(增加字段、修改字段属性、增加索引、修改索引等)是一个非常普遍的需求。但对于OLTP系统,变更期间业务尤其是高并发业务是非常不愿意看到因结构变更造成锁表而对业务造成影响的情况发生的。

在MySQL5.5以及之前的版本中,MySQL的DDL仅提供Table-Copy和In-Place(MySQL5.5开始提供)两种执行算法。

  • Table-Copy:通过拷贝临时表完成变更,变更期间表被锁定不可写入。
  • In-Place:在添加、修改索引时,In-Place会避免源表数据的拷贝操作只对索引数据进行,从而达到变更数据的目的。该算法执行期间可以读写数据,但仅支持索引操作。

由此可见,DDL操作在MySQL5.5以及之前的版本中风险极高!

MySQL5.6及后续版本提供了innodb-online-ddl能力(官方文档:innodb-online-ddl),覆盖了大范围的DDL类型。比如:添加列、删除列、列名修改、添加索引、修改索引等,但仍然有些常见的DDL类型无法覆盖,比如修改列的类型、修改列的长度、修改字符集,同样存在DDL变更高风险的问题。

为了解决这个问题,DMS企业版提供了不锁表结构变更功能。

产品优点

  • 在使用MySQL原生Online DDL过程中,有概率会导致主备延迟。DMS不锁表结构变更功能会控制速率不产生延迟。
  • 变更的目标表中如有DTS表级别复制链路,DMS的不锁表结构变更功能可以确保DTS复制不被中断。(必须于2020年2月24日之后重启过DTS复制链路)

支持能力

  • 版本:MySQL所有版本、POLARDB
  • 引擎:InnoDB、Rocksdb
  • 实例来源:经典网络的RDS、VPC网络的RDS、经典网络的ECS自建数据库、VPC网络的ECS自建数据库、公网自建数据库

典型应用场景

  • 不锁表结构变更,规避数据库变更锁表阻塞业务的现象发生。
  • 不锁表结构变更,规避原生OnlineDDL带来的主备延迟现象发生。
  • 不锁表结构变更,取代原有optimize table锁表变更回收表空间、降低碎片率的方案为不锁表回收。最佳实践-不锁表结构变更回收碎片空间
  • 不锁表结构变更,修改表的字符集、校验集。
  • 不锁表结构变更,调整时区。

开启方法

  1. 登录DMS控制台
  2. 单击页面顶部的系统管理>实例管理,进入实例管理页面。开启不锁表结构变更1
  3. 单击目标实例右侧操作列下的更多>编辑实例,打开编辑实例对话框。开启不锁表结构变更2
  4. 单击高级信息页签。
  5. 不锁表结构变更的下拉菜单中根据实际需求选择开启(DMS无锁表结构变更优先)开启(MySQL原生OnlineDDL优先)

    开启不锁表结构变更3

    • 关闭:原生语句下发给MySQL执行,不进行任何处理。
    • 开启(DMS无锁表结构变更优先):直接使用DMS自研的无锁表结构变更执行,以保障不锁表。(执行时间相对原生行为会慢一些,但不影响复制行为,不会产生延迟现象。)
    • 开启(MySQL原生OnlineDDL优先):优先使用MySQL原生的OnlineDDL执行,如果源生会锁表的话会转用DMS自研的不锁表结构变更执行,以保障不锁表。(执行时间相对比较快,但有可能导致实例的并行复制降为串行复制,从而可能产生主备延迟现象。)
  6. 单击提交

主要原理

  • 创建临时表:CREATE TABLE tmp_table_table LIKE table_name
  • 变更临时表结构: ALTER TABLE tmp_table_table XXXX
  • 全量拷贝数据:INSERT IGNORE INTO tmp_table_table (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx
  • 增量数据binlog同步: UPDATA/INSRT/DELETE tmp_table_name
  • 切换新旧表: RENAME TABLE table_name to old_tmp_table_table, tmp_table_name to table_name

变更依赖表上必须有主键、唯一键,用于全量拷表分段操作以及后续的增量更新依据。

被依赖的主键、唯一键不支持进行数据更新,若有更新则任务会失败退出。

全程无触发器等逻辑,且变更期间不影响并行复制为串行,不会产生主备延迟。

MySQL自带onlineddl会造成备库并行复制变成串行复制,导致主备延迟的可能。

使用限制

  • 账号权限:必须是以下三种类型账号的其中之一。

    • 高权限账号。
    • 具有日志权限的账号。
    • 读写账号。
  • 磁盘空间:由于需要在目标数据库新建表拷贝数据,大表变更时需要保证磁盘有足够的剩余空间,避免空间不足导致RDS实例被锁定。

  • 日志格式:POLARDB默认未开启binlog,需要开启row模式binlog复制,详情请参见 POLARDB-如何开启Binlog
  • 不支持主键、唯一键存在更新的场景。
    • 如果表上有主键,主键不能存在update更新。
    • 如果表上没有主键,只有唯一键,唯一键不能存在update更新。
    • 如果表上没有主键、没有唯一键,当前尚不支持使用不锁表变更逻辑。需要在实例上关闭此开关或先添加主键、唯一键后重新提交任务。