概述
一般来说数据库结构一经设计, 不能轻易更改, 因为更改 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 官方英文文档 https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
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 使用空闲
执行修改期间是否允许停止服务
是否有其他关联的数据库, 保证数据一致性
来源: https://www.cnblogs.com/youyoui/p/9545621.html