百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 编程字典 > 正文

MySQL InnoDB 修改表列Online DDL

toyiye 2024-07-11 00:30 12 浏览 0 评论

概述

一般来说数据库结构一经设计,不能轻易更改,因为更改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操作,执行的具体细节大不相同,详见下表:

OperationIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies MetadataAdding a columnYesYesYes*NoDropping a columnYesYesYesNoRenaming a columnYesNoYes*YesReordering columnsYesYesYesNoSetting a column default valueYesNoYesYesChanging the column data typeNoYesNoNoExtending VARCHAR column sizeYesNoYesYesDropping the column default valueYesNoYesYesChanging the auto-increment valueYesNoYesNo*Making a column NULLYesYes*YesNoMaking a column NOT NULLYes*Yes*YesNoModifying 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 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使用空闲
  • 执行修改期间是否允许停止服务
  • 是否有其他关联的数据库,保证数据一致性

相关推荐

为何越来越多的编程语言使用JSON(为什么编程)

JSON是JavascriptObjectNotation的缩写,意思是Javascript对象表示法,是一种易于人类阅读和对编程友好的文本数据传递方法,是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON(数据库用json格式存储)

在本文中,您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?,Devops,敏捷?,测试?以及?项目?管理?最新?,最热门?的?文章?,每天?花?...

MySQL 从零开始:05 数据类型(mysql数据类型有哪些,并举例)

前面的讲解中已经接触到了表的创建,表的创建是对字段的声明,比如:上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶(json格式对象)

一、引言在现代Web开发中,JSON(JavaScriptObjectNotation)已经成为数据交换的标准格式。无论是从前端向后端发送数据,还是从后端接收数据,JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data(json和formdata提交区别)

在讨论现代网络开发与API设计的语境下,理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里,特别值得关注的是两种主流数据格式:...

JSON 语法(json 语法 priority)

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解(json的语法规则)

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意:json的key是字符串,且必须是双引号,不能是单引号...

MySQL JSON数据类型操作(mysql的json)

概述mysql自5.7.8版本开始,就支持了json结构的数据存储和查询,这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库,在处理json这种非结构化的数据...

JSON的数据模式(json数据格式示例)

像XML模式一样,JSON数据格式也有Schema,这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解(后端json格式)

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势(什么叫json)

现在程序员还有谁不知道JSON吗?无论对于前端还是后端,JSON都是一种常见的数据格式。那么JSON到底是什么呢?JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型,以存储结构化数据。JSON是一种开放的数据格式,可用于存储各种类型的值。什么是JSON类型?JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类(javascript 包)

JOSN:我们希望可以将一个对象在不同的语言中进行传递,以达到通信的目的,最佳方式就是将一个对象转换为字符串的形式JSON(JavaScriptObjectNotation)-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介:Json,全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?(json和xml哪个好)

JSON(JavaScriptObjectNotation)和XML(eXtensibleMarkupLanguage)是在日常开发中比较常用的两种数据格式,它们主要的作用就是用来进行数据的传...

取消回复欢迎 发表评论:

请填写验证码