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