MySQL8.0 - 新特性 - Instant Add Column

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: MySQL8.0开始对一些DDL操作做了大量的优化,例如原子DDL, 快速DDL(只修改元数据),前者解决了长期以来mysql的一大诟病,后者则提升了dba同学的生活品质 官方文档列出了一些可以快速ddl的操作,大体包括: 修改索引类型 Add column (limited) 当一条alt.

MySQL8.0开始对一些DDL操作做了大量的优化,例如原子DDL, 快速DDL(只修改元数据),前者解决了长期以来mysql的一大诟病,后者则提升了dba同学的生活品质

官方文档列出了一些可以快速ddl的操作,大体包括:

  • 修改索引类型
  • Add column (limited)

    • 当一条alter语句中同时存在不支持instant的ddl时,则无法使用
    • 只能顺序加列
    • 不支持压缩表
    • 不支持包含全文索引的表
    • 不支持临时表,临时表只能使用copy的方式执行DDL
    • 不支持那些在数据词典表空间中创建的表
  • 修改/删除列的默认值
  • 修改索引类型
  • 修改ENUM/SET类型的定义

    • 存储的大小不变时
    • 向后追加成员
  • 增加或删除类型为virtual的generated column
  • RENAME TABLE操

本文主要介绍下在MySQL8.0.12引入的快速加列特性。虽然这个特性不能覆盖所有加列场景,但已经能解决很大部分加列带来的问题:

  • 对超级大表的加列操作通常可能耗时几个小时甚至数天的时间
  • 在ddl的过程中产生的临时表会占用磁盘空间
  • ddl带来的复制延迟问题

具体的worklog为: WL#11250 - Support Instant Add Column,描述的非常详细,本文简单描述下其思路

使用

ALTER语句增加了新的语法INSTANT,你可以显式地指定,但MySQL自身也会自动选择合适的算法。所以这个特性通常对用户是透明的。

增加了一些新的information_schema表来展示相关信息:

I_S.innodb_tables.instant_cols
I_S.innodb_columns.has_default/default_value

举个简单的例子:

root@test 03:54:47>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT '11',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


c,d 是instant added...

root@test 03:55:40>select table_id, name, pos, len, has_default,default_value from information_schema.innodb_columns where has_default = 1\G
*************************** 1. row ***************************
     table_id: 1143
         name: c
          pos: 2
          len: 4
  has_default: 1
default_value: NULL
*************************** 2. row ***************************
     table_id: 1143
         name: d
          pos: 3
          len: 4
  has_default: 1
default_value: 8000000b
2 rows in set (0.00 sec)


root@test 03:56:35>select instant_cols from information_schema.innodb_tables where name = 'test/t1';
+--------------+
| instant_cols |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

实现

记录格式修改

快速加列特性,在增加列时,实际上只是修改了元数据,原来存储在文件中的行记录并没有被修改。当行格式为redundent类型时,记录解析是不依赖元数据的,可以自解析,
但如果行格式是dynamic或者compact类型,由于行内不存储元数据,尤其是列的个数信息,其记录的解析需要依赖元数据的辅助。因此为了支持动态加列功能,需要对行格式做一定的修改

其大体思路为:

  • 如果表上从未发生过instant add column, 则行格式维持不变。
  • 如果发生过instant ddl, 那么所有新的记录上都被特殊标记了一个flag, 同时在行内存储了列的个数
  • 由于只支持往后顺序加列,通过列的个数就可以知道这个行记录中包含了哪些列的信息

我们先来看看典型compact行类型的记录组织结构:

+--------------------------------+---------------------+---------------+
| Non-null variable-length array | SQL-null flags/bitmap | Extra 5 bytes |
+--------------------------------+----------------------+---------------+

其中extra 5 bytes包含如下信息:

+-----------+---------------+----------+-------------+-----------------+
| Info bits | Records owned | Heap No. | Record type | Next record ptr |
+-----------+---------------+----------+-------------+-----------------+

extra info中包含的信息如下:
a) Info bits: 4 bits

0x10: REC_INFO_MIN_REC_FLAG
0x20: REC_INFO_DELETED_FLAG
其中还有两个bit是未使用的

b) Record owned: 4 bits
REC_NEW_N_OWNED

c) Heap No. : 13 bits

d) Record type: 3 bits

REC_STATUS_ORDINARY:叶子节点记录
REC_STATUS_NODE_PTR:非叶子节点记录
REC_STATUS_INFIMUM/REC_STATUS_SUPREMUM 系统记录

e) Next record ptr: 2 bytes

为了支持instant add column, 使用了info bits中的一个bit位,如果被设置,表示这条记录是第一次instant add column后插入的, flag为:
Ox80: REC_INFO_INSTANT_FLAG

当flag被设置时,在记录中就会使用1或2个字节来存储列的个数

+--------------------------+----------------+---------------+---------------+
| Non-null variable-length |                |               |               |
| array                    | SQL-null flags | fields number | Extra 5 bytes |
+--------------------------+----------------+---------------+---------------+

对于redundent类型,由于已经有了列个数信息,无需进行修改

数据词典信息

对数据词典进行了扩展并记录:

  • 在第一次instant add column之前的列个数
  • 每次加的列的默认值

通过这些信息加上记录上的额外信息,可以正确解析出记录上的数据

数据词典:
a) dd::Table::se_private_data::instant_col:在第一次instant ADD COLUMN之前表上面的列的个数
b) dd::Partition::se_private_data::instant_col, 和a类似,存储分区表上instant col的个数,但有所不同的是,分区表上的分区之间
可能存在不同列的个数。因为我们单独truncate一个分区,而truncate操作会清空instant标记,因此b)中存储的instant_col不应该比a)中每个分区上的instant_col要小
c) dd::Column::se_private_data::default_null, 表示默认值为NULL
d) dd::Column::se_private_data::default, 当默认值不为null时,这里存储默认值
DD_instant_col_val_coder
--- column default value需要从innodb类型byte转换成se_private_data中的text类型(char), 使用一个类型DD_instant_col_val_coder来辅助转换
example: 0XFF => 0x0F, 0x0F

在将表load到内存建立表对象dict_table_t和索引对象dict_index_t时,有几个关键成员要载入进来,因为会用于辅助解析记录

dict_table_t::n_instant_cols 第一次instant add column之前的非虚拟列个数,(包含系统列
dict_index_t::instant_cols flag用于标示是否存在Instant column
dict_index_t::n_instant_nullable: 第一次instant add column之前的可为null的列个数
dict_col_t::instant_default: 存储默认值及其长度, 当解析数据时看到Instant column, 会直接引用到这里的数据指针

载入逻辑:

ha_innobase::open
|-->dd_open_table
    |--> dd_open_table_one
上述提到的几个变量会被设置.

DDL

检查表是否支持instant ddl

ha_innodb::check_if_supported_inplace_alter()
    innobase_support_instant
    innopart_support_instant
            dict_table_t::support_instant_add()

condition:

  • 不是压缩表
  • 不是data dictionary tablespace
  • 不是全文索引表
  • 不是临时表

除此之外, 新增列还要确保不改变列的顺序

当判定可以立刻加列时,仅仅需要修改数据词典信息即可

ha_innobase::commit_inplace_alter_table
|--> dd_commit_inplace_instant
    |--> dd_commit_instant_part
    |--> dd_commit_instant_table
     1. dd::TABLE中记录instant column的个数
     2. 存储新的列的默认值

Note:

  1. truncate操作会重置instant标记
ha_innobase::truncate_impl
    dd_clear_instant_table

2.重建表的话,新不的表将不包含instant列

select

查询的关键在于如何正确的解析出记录中的每一行(对于不在其中的instant column,填默认值即可), 关键的函数是:

rec_init_offsets
|-->rec_init_offsets_comp_ordinary
    |-->rec_init_null_and_len_comp

何时填写instant add的default值:
default值存储在dict_col_t::dict_col_default_t

将默认值填到返回的记录中:

row_sel_store_mysql_field_func
    rec_get_nth_field_instant
    rec_get_nth_field_instant: 封装了列值: 如果是记录中的,则从记录中读取,否则返回其默认值

insert

记录在插入之前从tuple转换成physical record:

rec_convert_dtuple_to_rec
    rec_convert_dtuple_to_rec_new
           rec_convert_dtuple_to_rec_comp
当表上有instant column时
   1. 会占用1(如果列个数小于REC_N_FIELDS_ONE_BYTE_MAX)或者2个字节来存储列个数
   2. 在记录的Info bits字段设置REC_INFO_INSTANT_FLAG,表示这个记录是instant add column之后创建的

update

对于update,不会把default的值转换成inline的,除非去更新包含default值的列(row_upd_changes_field_size_or_external

对于update的回滚做了特殊处理:

  • 如果回滚的值从non-default到default值,那么这个是不会存储到列里面去的。(dtuple_t::ignore_trailing_default())
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
18天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
|
3月前
|
JSON 关系型数据库 MySQL
MySQL 8.0 新特性
MySQL 8.0 新特性
150 10
MySQL 8.0 新特性
|
3月前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
565 18
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
2月前
|
SQL 安全 关系型数据库
MySQL8.2有哪些新特性?
【10月更文挑战第3天】MySQL8.2有哪些新特性?
40 2
|
4月前
|
算法 关系型数据库 MySQL
一天五道Java面试题----第七天(mysql索引结构,各自的优劣--------->事务的基本特性和隔离级别)
这篇文章是关于MySQL的面试题总结,包括索引结构的优劣、索引设计原则、MySQL锁的类型、执行计划的解读以及事务的基本特性和隔离级别。
|
4月前
|
关系型数据库 MySQL
解决MySQL insert出现Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘xxx‘ at row 1
解决MySQL insert出现Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘xxx‘ at row 1
305 2
|
4月前
|
SQL 算法 关系型数据库
(二十)MySQL特性篇:2022年的我们,必须要懂的那些数据库新技术!
 MySQL数据库从1995年诞生至今,已经过去了二十多个年头了,到2022.04.26日为止,MySQL8.0.29正式发行了GA版本,在此之前版本也发生了多次迭代,发行了大大小小N多个版本,其中每个版本中都有各自的新特性,所有版本的特性加起来,用一本书的篇幅也无法完全阐述清楚,因此本章主要会挑重点特性来讲,具体各版本的特性可参考MySQL官网的开发手册。
160 1
|
5月前
|
关系型数据库 MySQL 测试技术
MySQL 报错 ERROR 1709: Index column size too large
MySQL 报错 ERROR 1709: Index column size too large
223 4
|
6月前
|
关系型数据库 MySQL 测试技术
深入探索MySQL 8:隐藏索引与降序索引的新特性
深入探索MySQL 8:隐藏索引与降序索引的新特性
|
6月前
|
SQL 关系型数据库 MySQL

相关产品

  • 云数据库 RDS MySQL 版