数据管理DMS的无锁变更功能支持DML无锁变更与DDL无锁变更。

背景信息

  • 大表变更

    当您进行大表更新时,可能会因单条SQL影响多条行数而出现日志超过参数阀值而执行失败,或因SQL不走索引导致锁表等。

  • 结构变更

    在MySQL5.5以及之前的版本中,MySQL的DDL仅提供Table-Copy和In-Place(MySQL5.5开始提供)两种执行算法。MySQL5.6及后续版本提供了Innodb-OnlineDDL能力,覆盖了大范围的DDL类型(添加列、删除列、列名修改、添加索引、修改索引等),但仍然有些常见的DDL类型无法覆盖(修改列的类型、修改列的长度、修改字符集等)。

为解决上述问题,DMS提供无锁变更功能,支持DML无锁变更与DDL无锁变更,您可以通过该功能执行变更操作,规避大量风险。具体操作,请参见DML无锁变更DDL无锁变更

详情的功能介绍与原理说明,请参见下文。

DML无锁变更

  • 背景说明
    当业务累积了大量数据时,需要对在线数据与历史数据进行分隔管理,或定期清除表中数据。常见的现象有:
    • 单条SQL影响行数非常多(会出现日志超过参数阀值而执行失败)
    • SQL不使用索引(会导致锁表,增加数据库负载甚至导致业务故障)
    此类问题通常可通过分批执行的方法解决,但是仍存在风险,例如:
    • 研发写程序分批处理,如果分批的方式不当,例如直接用LIMIT进行分批,可能仍会锁表;若分批执行的频率没有很好控制,会造成主备延迟过大。这些都会对数据库造成影响,进而影响业务。
    • DBA提取目标数据手动拆分:人工拆分非常繁琐,工作量大,且易出错,并且很难进行动态调整。
  • 功能特点

    数据管理DMS提供的DML无锁变更功能,基于强大的DMS引擎将单个SQL拆分成多个批次执行,且每个批次执行后有sleep(暂停)进行缓冲。能更好地满足业务方对大量数据变更的需求(例如历史数据清理、全表更新字段等),同时保证执行效率,以及减小对数据库性能、数据库空间等的影响,做到不影响业务。

  • 主要原理
    • 分批执行单个SQL。
    • 每个批次执行后有sleep(暂停)进行缓冲。
  • 适用场景
    • 历史数据清理。
    • 全表更新字段。

DDL无锁变更

  • 背景说明

    诸多业务在研发之初都会采用MySQL作为数据库的技术选型。随着业务的发展,数据量不断增加,MySQL的维护难度越来越高。在大数据量的情况下去做结构变更(增加字段、修改字段属性、增加索引、修改索引等)需要考虑锁表的风险,锁表将导致业务无法正常写入。

    MySQL原生能力:
    • 在MySQL5.5以及之前的版本中,MySQL的DDL仅提供Table-Copy和In-Place(MySQL5.5开始提供)两种执行算法。
      • Table-Copy:通过拷贝临时表完成变更,变更期间表被锁定不可写入。
      • In-Place:该算法执行期间可以对数据库进行读写,但仅支持索引数据。
    • MySQL5.6及后续版本提供了Innodb-OnlineDDL能力,覆盖了大范围的DDL类型(添加列、删除列、列名修改、添加索引、修改索引等),但仍然有些常见的DDL类型无法覆盖(修改列的类型、修改列的长度、修改字符集等)。
  • 功能特点
    • 相比较数据库原生,DMS无锁结构变更支持控制变更的执行速率,可避免数据库主备链路延迟,对数据库性能影响更小,并支持多种原生OnlineDDL执行时会锁表的场景。
    • 相比较PT-Online(PT-Online-Schema-Change)、OSC(OnlineSchemaChange)等其它工具,DMS无锁结构变更不依赖于触发器,且异步执行时对数据库的影响非常小,可随时安全中断。
    • DMS无锁结构变更与DTS同步工具兼容性好,变更的表中如有DTS表级别的复制链路,使用DMS无锁结构变更不会导致DTS复制中断。
      说明 必须与2020年2月14日后重启过DTS复制链路。
    • MySQL原生OnlineDDL与DMS无锁结构变更能力对比如下。
      支持项 MySQL5.5及以前版本 MySQL5.6及后续版本 DMS无锁结构变更
      添加列 X
      删除列 X
      列名修改 X
      添加索引 X
      修改索引 X
      碎片整理操作 X
      修改列的类型 X X
      修改列的长度 X X
      修改字符集 X X
      转换字符 X X
      分区表操作 X X
      时区修正操作 X X
      缓解或消除备库延迟 X X
    • 更多信息,请参见DDL无锁变更附录
  • 主要原理
    1. 创建临时表:CREATE TABLE tmp_table_table LIKE table_name
    2. 变更临时表结构: ALTER TABLE tmp_table_table XXXX
    3. 全量拷贝数据:INSERT IGNORE INTO tmp_table_table (SELECT %s FROM table_name FORCE INDEX (%s) WHERE xxx
    4. 增量数据Binlog同步: UPDATE/INSERT/DELETE tmp_table_name
    5. 切换新旧表: RENAME TABLE table_name to old_tmp_table_table, tmp_table_name to table_name
  • 适用场景
    • 变更数据库表结构。
    • 变更表的字符集、校验集,调整时区等。
    • 规避Optimize Table操作回收表空间、降低碎片率时引起的锁表,具体操作,请参见无锁结构变更回收碎片空间