云服务器内容精选

  • DDL工具简介 MySQL 5.6之前数据库中对大表的表结构修改的DDL操作通常会引发DML语句阻塞,复制延迟升高等问题,导致数据库对外呈现出一种“异常”的状态。本文介绍了MySQL原生的数据库DDL方式Copy和Inplace算法、开源工具gh-ost以及MySQL 8.0新增的Instant秒级加列的算法的原理,使用限制,适用场景等。 MySQL原生的Copy算法由于在拷贝数据的过程中对源表加MDL写锁,导致DML语句被长时间阻塞,已经不推荐使用。 Inplace算法相比Copy算法有很大的改进,采用在原表上进行更改的方法,不需要生成临时表,占用的额外空间小。同时Inplace操作只需要短暂的持有MDL写锁,不会造成DML操作被长时间阻塞。但是对大表的表结构修改,依然要消耗大量的时间,导致备机在回放DDL语句时产生较大的复制延迟。 开源gh-ost将一个DDL操作拆分成多个小操作,减少单次操作的时间来降低复制延迟。同时只有在最后rename镜像表和原表的过程中才会短暂阻塞读写操作。gh-ost基于Binlog回放增量数据,同时额外维护了额外的心跳表来记录DDL执行过程,支持临时暂停DDL过程。这些机制导致gh-ost的执行时间比原生的DDL算法略长。 MySQL 8.0之后提出的instant秒级加列算法,不再需要rebuild整个表,只需要在表的metadata中记录新增列的基本信息即可。这种方式将大表的加列操作降低到了秒级。但是目前这种方式的应用场景只局限在添加列,设置列默认值,删除列默认值,修改ENUM/SET列的定义等少量DDL场景。 根据每种算法和工具的特点,建议在可以使用instant算法的DDL场景和版本下,尽可能使用instant算法来减少DDL对整个业务的影响。此外的其他情况,如果客户是主备或含有只读实例的场景,且对复制延迟带来的影响容忍较低的情况下,使用gh-ost工具来进行DDL操作。如果客户需要快速变更表结构,可以容忍短时间的主备不一致的问题,用Inplace算法可以满足需求。Copy算法由于会长时间阻塞DML操作,占用大量磁盘空间,且执行时间较长,目前在可以应用其他算法和工具的场景下不推荐使用。 表1 DDL工具说明 方法 MySQL Copy MySQL Inplace gh-ost instant DDL过程中读取数据 允许 允许 允许 允许 DDL过程中写入数据 不允许 允许(短暂时间不允许) 允许(短暂时间不允许) 允许 额外空间占用 大 小(需要rebuild会略高) 大 小 执行时间 非常长 长 非常长 短 复制延迟 大 大 小 小 父主题: MySQL Online DDL工具使用
  • 背景 通常情况下大表的DDL操作都会对业务产生很大的影响,需要在业务低峰期做。MySQL 5.7支持原生DDL工具Copy和Inplace算法、以及开源DDL工具gh-ost,减少了DDL期间DML操作被阻塞的情况。但是大表DDL仍然需要花费很长时间。 instant秒级加列算法,让添加列的时候不在需要rebuild整个表,只需要在表的metadata中记录新增列的基本信息即可,可以很快执行完成。但是目前支持的DDL操作有限。
  • 使用限制 使用场景的限制: 部分场景下增加、删除、重命名(MySQL 8.0.28之后)列。 设置或删除列的默认值。 修改ENUM或SET列的定义。 更改索引的类型(BTREE | HASH)。 增加或删除虚拟列。 表名重命名。 添加或删除列的限制: 不支持有其他INSTANT语句在同一行的操作在同一条语句的情况。 新增列将会放到最后,不支持改变列的顺序(MySQL 8.0.29后支持任意位置加列)。 不支持在行格式为COMPRESSED的表上快速加列或删除。 不支持在已经有全文索引的表上快速加列或删除。 不支持在临时表上快速加列或删除。 重命名列的限制: 不支持重命名被其他表引用的列。 不支持重命名列的操作与生成或者删除虚拟列在同一个语句中。 修改ENUM或SET列的限制: 不支持ENUM或者SET列数据类型占用的存储空间发生变化。 增加或删除虚拟列的限制: 不支持对分区表的增加或删除操作。
  • 载入数据字典 MySQL从系统表读取表定义时,会将instant column相关的信息载入到InnoDB的表对象“dict_table_t”和索引对象“dict_index_t”中。 dict_table_t::n_instant_cols:第一次instant add column之前的非虚拟字段个数(包含系统列)。 dict_index_t::instant_cols:用于标示是否存在Instant column。 dict_index_t::n_instant_nullable:第一次instant add column之前的可为NULL的字段个数。 dict_col_t::instant_default:存储instant列默认值及其长度。
  • 记录格式 为了支持instant add column,针对COMPACT和DYNAMIC类型引入了新的记录格式,主要为了记录字段的个数信息。 如果没有执行过instant add column操作,则表的行记录格式保持不变。 如果执行过instant add column操作,则所有新的记录都会设置一个特殊的标记,同时在记录内存储字段的个数。 INSTANT_FLAG使用了info bits中的一个bit位,如果记录是第一次instant add column之后插入的,该flag被设置为1。 图1 记录格式
  • 新的数据字典信息 在执行instant add column的过程中,MySQL会将第一次intant add column之前的字段个数以及每次加的列的默认值保存在tables系统表的“se_private_data”字段中。 dd::Table::se_private_data::instant_col:第一次instant add column之前表上的列的个数。 dd::Column::se_private_data::default_null:标识instant column的默认值是否为NULL。 dd::Column::se_private_data::default:当instant column的默认值不是NULL时存储具体的默认值,column default value需要从InnoDB类型byte转换成“se_private_data”中的char类型。