8.0 新特性 - Generated Invisible Primary Key

文若3个月前技术文章73

说明

MySQL Innodb 引擎采用的是 IOT(索引组织表)存储方式,主键的重要性就不言而喻。在早期版本用户如果没有显式指定主键,会自动生成隐藏主键 row_id 来组织 B+ 树,隐藏主键 row_id 只会作用于 MVCC、Redo 和 Undo 等内部机制,无法在复制模块中使用。一些大数据组件生成的表结构,往往都没有主键设计,会出现全表扫描回放问题,带来非常大的主备延迟。而且 MySQL 一些周边软件 gh-ost、DTS 服务等,都有依赖主键设计,没有主键会降低数据库的可维护性。

1. GIPK 介绍

Generated Invisible Primary Keys 简称 GIPK,是 2022 年 7 月 16 日 MySQL 发布 8.0.30 版本中的新特性,仅适用于 Innodb 引擎。

1.1 参数设置

GIPK 特性,通过 sql_generate_invisible_primary_key 参数来控制是否开启,默认是关闭状态。

下面的实验,在 GIPK 禁用的状态下,创建一张 auto_0 表,随后开启 GIPK 特性,然后创建 auto_1 表。

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_0 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> SET sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.04 sec)

实验 show create table 对比两张表结构是否有不同:

carbon.png

发现在开启 GIPK 情况下,未指定主键创建表,MySQL 会帮助我们创建名为 my_row_id 主键:

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

2.2 可见性测试

向表 auto_1 表中插入数据:

insert into auto_1 values('a', 1),('b', 2),('c', 3),('d', 4),('e', 5);

执行查询,发现默认是不可见的:

select * from auto_1;
-- 结果:
+------+------+
| c1   | c2   |
+------+------+
| a    |    1 |
| b    |    2 |
| c    |    3 |
| d    |    4 |
| e    |    5 |
+------+------+

除非用户主动指定该字段:

select my_row_id, c1, c2 from auto_1;
-- 结果:
+-----------+------+------+
| my_row_id | c1   | c2   |
+-----------+------+------+
|         1 | a    |    1 |
|         2 | b    |    2 |
|         3 | c    |    3 |
|         4 | d    |    4 |
|         5 | e    |    5 |
+-----------+------+------+

查询元数据库,是可以查到相关信息:

select * from information_schema.COLUMNS where TABLE_NAME='auto_1' and COLUMN_KEY = 'PRI'\G

carbon.png

2.3 修改元数据可见性

开启 GIPK 后,默认会在 show create table 和 information_schema 等元数据库信息中看到,用户可以通过下方参数实现隐藏 my_row_id 的元数据信息,默认不隐藏:

select @@show_gipk_in_create_table_and_information_schema;

2.4 修改查询可见性

可通过 ALTER 语句将 my_row_id 不可见属性进行修改:

 ALTER TABLE `auto_1` ALTER column my_row_id set visible;

carbon-1.png

通过下方语句可修改为不可见:

ALTER TABLE `auto_1` ALTER column my_row_id set invisible;

2. GIPK 测试

2.1 Binlog 分析

看到 GIPK 特性,我的第一反应是 “能否解决无主键表带来的延迟问题?” 下面通过解析 Binlog 进行验证:

SET @@SESSION.GTID_NEXT= '4f4ab2eb-b0fd-11ed-b9e2-fa7581637800:15711'/*!*/;
# at 15439012
#230223 13:45:44 server id 553306  end_log_pos 15439315 CRC32 0x82a51a79 	Query	thread_id=201180	exec_time=0	error_code=0
SET TIMESTAMP=1677131144/*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=45/*!*/;
CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
)
-- 插入
### INSERT INTO `test`.`auto_1`
### SET
###   @1=1
###   @2='a'
###   @3=1
### INSERT INTO `test`.`auto_1`
### SET
###   @1=2
###   @2='b'
###   @3=2
### INSERT INTO `test`.`auto_1`
### SET
###   @1=3
###   @2='c'
###   @3=3
### INSERT INTO `test`.`auto_1`
### SET
###   @1=4
###   @2='d'
###   @3=4

主库在开启 GIPK 后,创建表后 Binlog 中也会有创建 my_row_id 语句,说明可以解决该问题。

需要注意的是,一定要保证主备库的字段 invisible/visible 属性是相同的。

2.2 主从复制

若主库没有开启 GIPK 而从库开启,那么 GIPK 是否会生效?

经测试:主库未开启 GIPK 备库开启 GIPK,主库上创建无主键表,备库不会主动为其创建主键。

2.3 逻辑备份

mysqldump 8.0.30 版本提供 --skip-generated-invisible-primary-key 参数,如果不指定该参数,导出的 SQL 就会包含 my_row_id,如果指定则会忽略 my_row_id 字段。

2.4 其它限制

2.4.1 AUTO_INCREMENT 属性

my_row_id 已经占用表中唯一的 AUTO_INCREMENT 属性,无法再次指定自增属性。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

2.4.2 my_row_id 关键字

my_row_id 已经成为关键字,无法使用。

ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

参考文档

【1】https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-30.html#mysqld-8-0-30-gipk



相关文章

oracle gi安装报错PRCR-1079 : Failed to start resource ora.cvu

安装gi执行root.sh报错:PRCR-1079 : Failed to start resource ora.cvu安装gi执行root.sh报错:PRCC-1014 : LISTENER_SCA...

Kubernetes源码解读(四)--Lister&Watcher源码分析

Kubernetes源码解读(四)--Lister&Watcher源码分析

Lister&&Watcher是Reflector的一个主要能力提供者,我们来看看Lister&&Watcher是如何实现List()和watch()的过程的。List...

GTID 模式 - 通过跳过事务解决主从故障

一、前言很多场景下我们需要跳过一个事务来修复主从关系,例如主从事务不一致,或者对无主键表更新,导致较大延迟,操作过程在此记录。二、操作流程1. 获取最后一个 GTID 操作在 GTID 模式下,如果需...

切换不同的网络访问同一个业务报错"network error"

切换不同的网络访问同一个业务报错"network error"

问题现象:业务:xxx,使用谷歌浏览器登陆之后,访问“商品档案”模块,会提示“network error”报错内容,但是切换到其他浏览器测试是可以正常访问的,同时,在使用谷歌浏览器访问的情况下,如果切...

Atlas集成HBase

Atlas集成HBase

1 集成原理 Atlas HBase hook与HBase master注册为协处理器。在检测到对HBase名称空间/表/列族的更改时, Atlas Hook过Kafka通知更新Atlas中的元数据。...

DBMS_MONITOR包跟踪10046

该包是从Oracle 10g开始提供的,Oracle官方支持。等于10046 level 12。--跟踪当前会话: EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE;...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。