libo集团官方APPlibo集团官方APP

立博在线官方APP
libosport官方网址

MySQL InnoDB 修改表列Online DDL

概述

一般来说数据库结构一经设计,不能轻易更改,因为更改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 PLACECOPY

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操作,执行的具体细节大不相同,详见下表:

OperationIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Adding a columnYesYesYes*No
Dropping a columnYesYesYesNo
Renaming a columnYesNoYes*Yes
Reordering columnsYesYesYesNo
Setting a column default valueYesNoYesYes
Changing the column data typeNoYesNoNo
Extending VARCHAR column sizeYesNoYesYes
Dropping the column default valueYesNoYesYes
Changing the auto-increment valueYesNoYesNo*
Making a column NULLYesYes*YesNo
Making a column NOT NULLYes*Yes*YesNo
Modifying the definition of an ENUM or SET columnYesNoYesYes

其中各列指标解释如下:

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 NULLALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;-- Making a column NOT NULLALTER 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 tblADD COLUMN `count` bigint(20) NOT NULL DEFAULT 0 COMMENT "";-- 锁表flush tables with write lock; -- 备份源文件cp tbl.frm tbl.frm.bak# 替换数据结构文件frmcp tbl_temp.frm tbl.frm -- mysql移除读锁unlock tables; -- 测试修改是否成功select * from tbl limit 1; -- 如果出现错误,导致连接丢失等,可以回滚flush tables with write lock;cp tbl.frm.bak tbl.frmunlock tables;

手动执行COPY方式

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

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

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

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

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

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

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

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

, 1, 0, 9);

欢迎阅读本文章: 李倩琳

立博体育下载

立博在线官方APP