作者：杨奇龙
网名“北在南方”，资深 DBA，主要负责数据库架构设计和运维平台开发工作，擅长数据库性能调优、故障诊断。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

一、前言
作为 MySQL DBA ，相信大家都经历过在复制模式下，如果没有主键，遇到 load data ，大事务，ddl 等有大量表数据行扫描的行为时，会带来严重的主从延迟，给数据库稳定性和数据一致性带来隐患。
MySQL 8.0.30 已于近日 GA ，新版本为我们提供了一个令人惊喜的特性 -（Generated Invisible Primary Keys）简称 GIPK 。一句概况就是: 当开启GIPK模式后，MySQL 会在没有显示定义主键的InnoDB表上自动生成不可见的主键。

对于已经使用云RDS的朋友，可能很早就享受到云 RDS MySQL 提供的隐式主键特性。但是对于自建数据库的企业，GIPK 依然是一个比较期待特性，（当然有和用起来是两码事！）

长话短说，本文基于实际测试案例来学校 如何使用 GIPK 。
二、实践出真知
2.1 开启 GIPK 由参数sql_generate_invisible_primary_key 控制，默认关闭，表示禁用，如果需要使用该特性，则需显式开启。

master [localhost:22031]> show variables like 'sql_generate_invisible_primary_key'; +------------------------------------+-------+ | Variable_name| Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | OFF| +------------------------------------+-------+ 1 row in set (0.00 sec)master [localhost:22031]> set sql_generate_invisible_primary_key=on; Query OK, 0 rows affected (0.00 sec)master [localhost:22031]> show variables like 'sql_generate_invisible_primary_key'; +------------------------------------+-------+ | Variable_name| Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | ON| +------------------------------------+-------+ 1 row in set (0.00 sec)

2.2 测试 我们分别在关闭和开启该特性下创建两个无主键表:

master [localhost:22031]> create table t1(id int ,c1 int); Query OK, 0 rows affected (0.00 sec) master [localhost:22031] {msandbox} (test) > show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int DEFAULT NULL, `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

开启 GIPK 并创建无主键表 t3 。

master [localhost:22031]> set sql_generate_invisible_primary_key=on; Query OK, 0 rows affected (0.00 sec)master [localhost:22031] {msandbox} (test) > show variables like 'sql_generate_invisible_primary_key'; +------------------------------------+-------+ | Variable_name| Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | ON| +------------------------------------+-------+ 1 row in set (0.00 sec)master [localhost:22031]> create table t3(id int ,c1 int); Query OK, 0 rows affected (0.01 sec)master [localhost:22031]> master [localhost:22031]> show create table t3 \G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `id` int DEFAULT NULL, `c1` int DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

我们可以通过 show create table 发现 t3 的表结构，出现名为 my_row_id 的不可见主键。对两个表插入数据查看差异:

master [localhost:22031]> insert into t1 values(1,1),(2,2),(3,3); Query OK, 3 rows affected (0.00 sec) Records: 3Duplicates: 0Warnings: 0 master [localhost:22031]> select * from t1; +------+------+ | id| c1| +------+------+ |1 |1 | |2 |2 | |3 |3 | +------+------+ 3 rows in set (0.00 sec) master [localhost:22031]> insert into t3 values(1,1),(2,2),(3,3); Query OK, 3 rows affected (0.00 sec) Records: 3Duplicates: 0Warnings: 0 master [localhost:22031]> select * from t3; +------+------+ | id| c1| +------+------+ |1 |1 | |2 |2 | |3 |3 | +------+------+ 3 rows in set (0.00 sec)

【新特性解读 | MySQL 8.0 GIPK 不可见主键】直接通过 select * from table 查询时，t3 和普通表t1无差异。 因为 GIPK 是基于不可见列实现的，如果我们显式指定访问 my_row_id ,则可以查看到隐藏的主键 my_row_id。

master [localhost:22031]> select my_row_id,id,c1 from t3; +-----------+------+------+ | my_row_id | id| c1| +-----------+------+------+ |1 |1 |1 | |2 |2 |2 | |3 |3 |3 | +-----------+------+------+ 3 rows in set (0.00 sec)

总的来说，从业务程序访问数据库的角度来看，开启 GIPK 对业务是透明的。

2.3 关于 DDL 当开启 GIPK 特性时，MySQL 生成的主键不能更改，只能在 VISIBLE 和 INVISIBLE 之间进行切换。比如：
使 GIPK 主键可见: alter table TALBE_NAME alter column my_row_id set visible;

master [localhost:22031]> alter table t3 alter column my_row_id set visible; Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 0 master [localhost:22031]> show create table t3 \G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT, ### 显式可见 `id` int DEFAULT NULL, `c1` int DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)### 而且可以被直接查询到 master [localhost:22031]> select * from t3; +-----------+------+------+ | my_row_id | id| c1| +-----------+------+------+ |1 |1 |1 | |2 |2 |2 | |3 |3 |3 | +-----------+------+------+ 3 rows in set (0.00 sec)

关闭起可见性 : alter table TABLE_NAME alter column my_row_id set invisible;

master [localhost:22031]> alter table t3 alter column my_row_id set invisible; Query OK, 0 rows affected (0.00 sec) Records: 0Duplicates: 0Warnings: 0 master [localhost:22031]> show create table t3 \G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `id` int DEFAULT NULL, `c1` int DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ## 再次通过select * 查询则看不到 my_row_id master [localhost:22031]> select * from t3; +------+------+ | id| c1| +------+------+ |1 |1 | |2 |2 | |3 |3 | +------+------+ 3 rows in set (0.00 sec)

另外就是开启 GIPK 之后 ，my_row_id 是系统关键字，我们创建无主键的表时，不能包含名为 my_row_id 的字段 。

master [localhost:22031]> create table t6(my_row_id int not null,c1 int); ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

当然如果 MySQL 允许创建包含名为 my_row_id 的主键的表 ：

master [localhost:22031]> create table t5(my_row_id int not null auto_increment primary key,c1 int); Query OK, 0 rows affected (0.01 sec)

当开启 GIPK 模式时，如不能直接删除不可见主键。必须显式增加一个新的主键然后再删除 GIPK

master [localhost:22031]> alter table t3 drop PRIMARY KEY; ERROR 1235 (42000): This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'master [localhost:22031]> alter table t3 drop PRIMARY KEY,add primary key(id); ERROR 4111 (HY000): Please drop primary key column to be able to drop generated invisible primary key.master [localhost:22031]> alter table t3 drop column my_row_id,add primary key(id); Query OK, 0 rows affected (0.02 sec) Records: 0Duplicates: 0Warnings: 0

2.4 关于主从复制 需要注意的是 set sql_generate_invisible_primary_key=on|off 并不会被复制到从库，主库上开启该特性的话，从库并不会开启 GIPK 。也就是说从库也不会为任何在源库上没有创建主键的表创建主键。可能会有读者疑问如果主库关闭该特性，但是从库显示开启呢？ 做个测试看看
在 master 上关闭该特性并且创建无主键表t6

master [localhost:22031]> set sql_generate_invisible_primary_key=off; Query OK, 0 rows affected (0.00 sec) master [localhost:22031]> master [localhost:22031]>show variables like 'sql_generate_invisible_primary_key'; +------------------------------------+-------+ | Variable_name| Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | OFF| +------------------------------------+-------+ 1 row in set (0.00 sec)master [localhost:22031]> show tables; +----------------+ | Tables_in_test | +----------------+ | t1| | t2| | t3| | t4| | t5| +----------------+ 5 rows in set (0.00 sec)master [localhost:22031]> create table t6(id int ,c1 int); Query OK, 0 rows affected (0.01 sec)master [localhost:22031]> show create table t6\G *************************** 1. row *************************** Table: t6 Create Table: CREATE TABLE `t6` ( `id` int DEFAULT NULL, `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

在从库上开启该特性

slave1 [localhost:22032]> show tables; +----------------+ | Tables_in_test | +----------------+ | t1| | t2| | t3| | t4| | t5| +----------------+ 5 rows in set (0.00 sec) slave1 [localhost:22032]> set sql_generate_invisible_primary_key=on; Query OK, 0 rows affected (0.00 sec) slave1 [localhost:22032]> show variables like 'sql_generate_invisible_primary_key'; +------------------------------------+-------+ | Variable_name| Value | +------------------------------------+-------+ | sql_generate_invisible_primary_key | ON| +------------------------------------+-------+ 1 row in set (0.00 sec) slave1 [localhost:22032]> show create table t6\G *************************** 1. row *************************** Table: t6 Create Table: CREATE TABLE `t6` ( `id` int DEFAULT NULL, `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

结果: 主库关闭 GIPK ，从库开启 GIPK ，源库上创建无主键表，从库上并不会主动为该表创建主键。

2.5 逻辑备份 大多数实例会进行逻辑备份，如果 开启GIPK 模式时，MySQL 8.0.30 版本的 mysqldump 提供的 --skip-generated-invisible-primary-key 选项会忽略 GIPK 信息。简单来说，mysqldump 时 不带该参数，逻辑导出的数据会包含隐式主键，如果带上该参数，则不带隐式主键。

文章图片




文章图片

2.6 限制

1. 只支持 InnoDB 存储引擎。
2. 支持 row 模式复制，不支持 statement 模式复制。
3. my_row_id 成为系统关键字。

三、小结
总体而言，该特性绝对是强需求。毕竟林子大了，什么样的情况都可能会发生。运(chu)维(li)经(gu)验(zhang)比较丰富 DBA 而言，MySQL 数据库稳定性深受无主键之苦，对于自建场景尤其是没有审核流程的公司而言，该特性能提升数据库系统稳定性和安全性。
参考文档

1. https://dev.mysql.com/doc/ref...
2. https://dev.mysql.com/doc/ref...

推荐阅读

• 

  什么是碱性岩
• 

  洛阳奥斯卡影院6月13号放映什么电影？洛阳奥斯卡影院今日放映表
• 

  京东“京银计划” 是什么 怎么参与？
• 

  共栅极放大电路分析,场效应管共栅极放大电路
• 

  2022年三伏天时间表？
• 

  活珠子不适合哪些人吃
• 

  m当前“最接近完美”的五款手机，别看价格贵，用三年毫无压力
• 

  上海电商有哪些 什么是电商上海，什么是电商上海人
• 

  家用茶具多少钱的合适 – 高档茶盘图片价格表
• 

  冰箱不结冰是怎么回事？原因分析与解决方法
• 

  热水器不烧水怎么办,是因为这些原因
• 

  增加注册资本后股东的股权比例
• 

  白发傅筹第几集知道身世
• 

  CPB水前肌底精华好不好？CPB水前肌底精华怎么用
• 

  兄弟T310如何清零废墨？
• 

  炒杜仲与生杜仲的区别 炒杜仲与生杜仲的不同之处
• 

  《侠客风云传前传》激活方法及特权简单介绍
• 

  取暖器品牌排名？哪个比较好的？
• 

  中风|女性中风有奇特症状？盘点易被女性忽视的11个中风症状！
• 

  减肥可以吃红枣吗