MySQL InnoDB在线调整表结构(online DDL)-新增字段篇

线上服务的表结构调整是业务需求扩展和系统设计拓宽的常见方式，但对线上服务而言，线上库表调整可能对线上服务产生影响。
本文以MySQL 5.6.16-log为例，结合InnoDB引擎特性，给出综合分析，几个核心问题：

  1. 线上库修改表结构操作是否会导致锁表、锁行？
  2. 线上改表结构可用的方案有哪些？各自成本和适用场景？
  3. 5.6+ InnoDB引擎的online DDL特性性能表现如何？

此文仅以新增字段(add column)展开讨论，其他操作(修改字段类型、添加、删除索引等)均与此案例不同，后续逐一展开讨论。

前提说明

  • 线上表的数据2000+万
  • 数据空间13G
  • 索引空间2G
  • 表内数据不时有新增和更新
    由于业务需求扩展, 会遇到需要在线上表增加字段的情况，操作期间避免线上服务产生影响。

问题追溯

  1. 2009年开始对线上表的变更就被展开讨论过
  2. Facebook在2010年也给出解决方法 Online Schema Change for MySQL, 源码在这, 目前已用python进行重写：https://github.com/facebookincubator/OnlineSchemaChange
  3. MySQL 5.6 release note中声明支持InnoDB引擎的“一些alter table操作”的onlie DDL

    Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section 14.13, “InnoDB and Online DDL” for details.

基本方案

一般线上新增字段的两个通用处理方式：

  • 方式1：流量低谷时段进行alter table操作：如凌晨3点

    1
    2
    ALTER TABLE `table_name`
    ADD COLUMN `new_column`  tinyint UNSIGNED NULL DEFAULT 0 COMMENT '字段注释|张晓武|2017-7-17';

  • 方式2：创建临时表、加字段、拷贝数据、增量拷贝、rename表名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- 1. 表结构复制，耗时<10ms
    create table table_new like table_name;
    -- 2. 临时表加字段，耗时<10s
    ALTER TABLE `table_new`
    ADD COLUMN `new_column`  tinyint UNSIGNED NULL DEFAULT 0 COMMENT '字段注释|张晓武|2017-7-17';

    -- 3. 数据拷贝临时表，345w数据耗时86s
    INSERT INTO table_new(id,os,sv,uuid,reg,account,user_id,package_id,created_at,updated_at) (SELECT id,os,sv,uuid,reg,account,user_id,package_id,created_at,updated_at from table_name);

    -- 4. 增量数据再次拷贝，看增量数据量，耗时<100ms
    INSERT INTO table_new(id,os,sv,uuid,reg,account,user_id,package_id,created_at,updated_at) (SELECT id,os,sv,uuid,reg,account,user_id,package_id,created_at,updated_at from table_name
    WHERE `id` > (SELECT MAX(`id`) from `table_new`));

    -- 5. 表名切换，耗时50ms
    RENAME TABLE
    table_name TO table_name_20170717,
    table_new TO table_name;

MySQL 5.6+ InnoDB online DDL支持

MySQL 5.6发布说明中对InnoDB引擎对online DDL的支持进行了说明。
即：支持在线表结构调整，并尽可能不阻塞删除、插入、更新表数据，避免表复制。
但同时描述里也仅仅指出是一些操作，而非所有。
5.6+MySQL中InnoDB引擎对各DDL操作的处理如下图, 原文此处：
InnoDB_online_DDL
从上图看出，针对新增字段(add column)时支持并发数据读写(Permits Concurrent DML), 且算法为in-place方式(不是copy模式，不会复制整表数据)，但需注意该操作会重建表(rebuild table)。
注意：新增字段如果为自增字段(auto-increment)时，不支持并发DML操作。

实际测试情况

参考文件

  1. mysql 5.6 原生Online DDL解析