关于mysql:新特性解读-MySQL-80-GIPK-不可见主键

作者：杨奇龙
网名“北在北方”，资深 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: t1Create Table: CREATE TABLE `t1` (  `id` int DEFAULT NULL,  `c1` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 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: t3Create 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_ci1 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: 3  Duplicates: 0  Warnings: 0master [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: 3  Duplicates: 0  Warnings: 0master [localhost:22031]> select * from t3;+------+------+| id   | c1   |+------+------+|    1 |    1 ||    2 |    2 ||    3 |    3 |+------+------+3 rows in set (0.00 sec)

间接通过 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: 0  Duplicates: 0  Warnings: 0master [localhost:22031]> show create table t3 \G*************************** 1. row ***************************       Table: t3Create 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_ci1 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: 0  Duplicates: 0  Warnings: 0master [localhost:22031]> show create table t3 \G*************************** 1. row ***************************       Table: t3Create 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_ci1 row in set (0.00 sec)## 再次通过select * 查问则看不到 my_row_idmaster [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: 0  Duplicates: 0  Warnings: 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: t6Create Table: CREATE TABLE `t6` (  `id` int DEFAULT NULL,  `c1` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 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: t6Create Table: CREATE TABLE `t6` (  `id` int DEFAULT NULL,  `c1` int DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 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 限度

只反对 InnoDB 存储引擎。
反对 row 模式复制，不反对 statement 模式复制。
my_row_id 成为零碎关键字。

三、小结

总体而言，该个性相对是强需要。毕竟林子大了，什么样的状况都可能会产生。运(chu)维(li)经(gu)验(zhang)比拟丰盛 DBA 而言，MySQL 数据库稳定性深受无主键之苦，对于自建场景尤其是没有审核流程的公司而言，该个性能晋升数据库系统稳定性和安全性。

参考文档

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