MySQL InnoDB 修改表列Online DDL

时间：2021-05-23 04:23:36

概述

一般来说数据库结构一经设计，不能轻易更改，因为更改DDL（Data Definition Language）操作代价很高，所以在进行数据库结构设计时需要谨慎。

但是业务发展是未知的，特别是那些变化很大的业务，所以不可避免的需要修改数据库结构，本文主要对MySQL5.6+ InnoDB存储引擎字段的修改进行探讨。

对于不同的场景，所使用的方式也会大不相同，尤其是修改百万级，千万级的表字段时，要特别注意。

DDL操作类型

数据库结构的DDL操作总体来说有如下几种：

  • 索引操作（Index Operations）
  • 键操作（Primary Key Operations）
  • 列操作（Column Operations）
  • 外键操作（Foreign Key Operations）
  • 表操作（Table Operations）
  • 分区操作（Partitioning Operations）

本文主要对列操作（Column Operations）进行探讨，其他更详细的信息参考MySQL官方英文文档

Online DDL操作

简述

本文探讨的是Online DDL操作，MySQL5.6以上支持，相较于一般DDL，它在实现修改表结构的同时，依然允许DML操作（SELECT,INSERT,UPDATE,DELETE）。

Online DDL主要有两种方式：IN PLACE和COPY。

  • IN PLACE：直接在原表上进行修改，相比于COPY方式可以避免重建表带来的IO和CPU消耗，有更好的性能并支持并发DML操作
  • COPY：创建修改后的临时表，然后将原表的数据复制到临时表，执行期间不允许并发DML写操作，否则会导致脏数据。

在MySQL之前，我们一般使用COPY的方式，借助临时表，手动修改。

需要注意的是：并不是所有的Online DDL操作都支持IN PLACE方式。

MySQL InnoDB数据存储方式

在MySQL中，一张表的数据分为两种，一种是结构数据，记录者站表包含哪些字段，哪些数据类型，另一种是记录数据，保存每天记录的原始数据。它们是用不同的文件进行存储的。

在mysql指定的data_dir数据存储目录可以看到每张表对应一个frm文件，这个文件就是存放着表的结构数据。

INPLACE方式详细介绍

对于添加索引，添加/删除列、修改列NULL/NOT NULL属性等操作，需要修改MySQL内部的数据记录，对这类操作进行Online DDL操作时，需要重建表（rebuild）。

相反，对于删除索引，修改列默认值，修改列名等操作不需要修改MySQL内部的数据记录，只需要修改结构数据frm文件，而不需要重建表（no-rebuild）。

另外，在进行Online DDL操作期间，不同的操作可以选择不同的锁机制。主要有以下几种锁机制：

  • LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
  • LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操作不支持对表的继续写入，则DDL操作失败，对表修改无效
  • LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
  • LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

无论任何模式下，Online DDL操作开始都需要一小段时间的排它锁来准备环境，用于等待该表上的其他操作执行完毕，此时Online DDL操作会提示：waiting meta data lock。

同样在Online DDL操作结束之前，也会等待Online DDL操作期间的事务完成，此时也会出现排它锁。

所以需要确保在执行Online DDL之前和执行期间没有大型DML事务占用该表，否则会出现长时间锁表甚至死锁。

Online DDL各种列操作情况

从上面的介绍可以看出，不同的DDL操作，执行的具体细节大不相同，详见下表：

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a column Yes Yes Yes* No
Dropping a column Yes Yes Yes No
Renaming a column Yes No Yes* Yes
Reordering columns Yes Yes Yes No
Setting a column default value Yes No Yes Yes
Changing the column data type No Yes No No
Extending VARCHAR column size Yes No Yes Yes
Dropping the column default value Yes No Yes Yes
Changing the auto-increment value Yes No Yes No*
Making a column NULL Yes Yes* Yes No
Making a column NOT NULL Yes* Yes* Yes No
Modifying the definition of an ENUM or SET column Yes No Yes Yes

其中各列指标解释如下：

  • In Place：是否支持In Place方式，Yes为优选方案
  • Re Builds Table：是否需要重建表，不重建（No）为优选方案
  • Permits Concurrent DML：是否允许并发DML操作，允许（Yes）为优选方案
  • Only Modifies Metadata：是否值修改表结构数据，即只修改frm文件

列操作方式

下面列举常用的列操作的执行方法以及注意事项。

添加列（Adding a column）

为表添加一列的方法如下：

ALTER TABLE tbl_name

ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

添加列时如果附加auto increment选项，则不允许并发DML操作，此操作会重建表，开销巨大。最优化选项是指定：ALGORITHM=INPLACE, LOCK=SHARED。

删除列（Dropping a column）

ALTER TABLE tbl_name

DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

重命名列名（Renaming a column）

ALTER TABLE tbl

CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

如果你的目的只是修改列名，一定要保证修改后的列的数据类型，NULL/NOT NULL等属性和原来的列一致。

该操作建议指定INPLACE方式，这样只会更新frm文件，即使修改的列名是外键。

重新排列列顺序（Reordering columns）

ALTER TABLE tbl_name

MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

该操作费力不讨好，不建议对数据量超过百万级的大表进行操作，它会对表重建。

修改列数据类型（Changing the column data type）

ALTER TABLE tbl_name

CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

修改数据类型只支持COPY方式。

修改列的默认值（Setting a column default value）

ALTER TABLE tbl

ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;

修改列的自增熟悉（Changing the auto-increment value）

ALTER TABLE table

AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

该操作用于修改下一条记录的自增值，只会修改内存中的值，而不会修改数据文件。

对于分布式系统，经常需要手动制定开始自增的值，可以使用该方法。

修改NULL/NOT NULL属性（Making a column NULL and Making a column NOT NULL）

-- Making a column NULL

ALTER TABLE tbl_name

MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

-- Making a column NOT NULL

ALTER TABLE tbl_name

MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

因为设置列为NULL时，该列在原有数据类型空间的基础上增加一个直接来存储是否为NULL，所以需要重建表。

当把NULL的列设为NOT NULL时，如果有记录为NULL，则该操作会失败。

修改ENUM或SET的定义（Modifying the definition of an ENUM or SET column）

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));

ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;

该方式用于修改一个枚举或者集合的值，对于在尾部增加枚举或者集合值的情况，如果增加之后存储空间没有变化，就可以使用IN PLACE方式。

反之如果存储空间发生变化，如从2个字节便到三个字节，或者在中间添加值，那么就需要COPY的方式。

对于那种值的个数不确定或者枚举名称变化的场景，建议使用tinyint代替ENUM或者SET来进行存储。

实际中如何执行DDL修改

综合上述，可以得出常用的三种方法。

Online DDL

通过执行ALTER等命令直接修改。适用的情况如下：

  • 表中数据量较小，低于百万级别
  • 需要MySQL5.6+以上
  • 能够忍受长时间不提供服务的百万级表，需要一小时以内

手动修改frm文件

该方式适用于不支持Online DDL的场景，只能执行Only Modifies Metadata部分的DDL修改。修改方法如下：

首先找到MySQL数据存储路径，可从进程信息中查看：

# 查找mysql进程信息

ps aux|grep mysql

查到当前数据库的数据存储目录，然后cd到所看到的frm表结构文件目录，备份需要处理的frm文件。

在数据库创建一个类似的数据表，然后修改该表，再把该表的frm文件和原来的表的frm文件替换。

-- mysql中创建临时表

create table tbl_temp like tbl;

-- 修改临时表

ALTER TABLE tbl

ADD COLUMN `count`  bigint(20) NOT NULL DEFAULT 0 COMMENT '';

-- 锁表

flush tables with write lock;

-- 备份源文件

cp tbl.frm tbl.frm.bak

# 替换数据结构文件frm

cp tbl_temp.frm tbl.frm

-- mysql移除读锁

unlock tables;

-- 测试修改是否成功

select * from tbl limit 1;

-- 如果出现错误，导致连接丢失等，可以回滚

flush tables with write lock;

cp tbl.frm.bak tbl.frm

unlock tables;

手动执行COPY方式

通过复制临时表，然后修改临时表，再把原表中的数据复制到临时表中，并切换临时表和原表。

当需要对原表中数据进行额外的处理时，只能选择此方式，该方式会造成大量的磁盘IO，并且执行期间不允许写入。

对于千万级别的表，可以分批进行复制，使用一些策略来允许迁移过程中的写入。

执行修改时需要考虑的因素

首先需要对执行的表数据量进行确认，如果数据量超过百万级甚至千万级，需要检查下面的事项：

  • 当前系统内存容量充足
  • 当前系统内存使用情况良好
  • 当前系统CPU使用空闲
  • 执行修改期间是否允许停止服务
  • 是否有其他关联的数据库，保证数据一致性

如果有一些遗漏的点，欢迎大家补充，这样也能够在以后遇到类似问题的时候提供参考。

文章已经同步到个人博客：http://uusama.com/866.html，过两天把其他的Online DDL操作测试之后再加上。（个把月没写文章了，已然懒得像一坨咸鱼）

相关文章