8.0 新特性 - Generated Invisible Primary Key_sql_generate_invisible_primary_key

说明

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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

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

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY
1

2.2 可见性测试

向表 auto_1 表中插入数据：

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

执行查询，发现默认是不可见的：

select * from auto_1;
-- 结果：
+------+------+
| c1   | c2   |
+------+------+
| a    |    1 |
| b    |    2 |
| c    |    3 |
| d    |    4 |
| e    |    5 |
+------+------+
1
2
3
4
5
6
7
8
9
10
11

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

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 |
+-----------+------+------+
1
2
3
4
5
6
7
8
9
10
11

查询元数据库，是可以查到相关信息：

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

2.3 修改元数据可见性

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

select @@show_gipk_in_create_table_and_information_schema;
1

2.4 修改查询可见性

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

 ALTER TABLE `auto_1` ALTER column my_row_id set visible;
1

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

ALTER TABLE `auto_1` ALTER column my_row_id set invisible;
1

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

主库在开启 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