【昌哥IT课堂】MySQL8.0新特性之不可见主键

一、概述
作为 MySQL DBA ,相信大家都经历过在复制模式下,如果没有主键,遇到 load data ,大事务,ddl 等有大量表数据行扫描的行为时,会带来严重的主从延迟,给数据库稳定性和数据一致性带来隐患。

MySQL 8.0.30 新版本为我们提供了一个新特性 -(Generated Invisible Primary Keys)简称 GIPK 。当开启GIPK模式后,MySQL 会在没有显示定义主键的InnoDB表上自动生成不可见的主键。

二、参数配置
GIPK 由参数 sql_generate_invisible_primary_key 控制,默认关闭,表示禁用,如果需要使用该特性,则需显式开启。
mysql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.02 sec)

mysql> set sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+
1 row in set (0.01 sec)

三、功能验证:
现在分别在关闭和开启该特性下创建两个无主键表:
创建一张无主键的表t1:
mysql> create table t1(id int ,c1 int);
Query OK, 0 rows affected (0.00 sec)
mysql> 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 并创建无主键表 t2 。
mysql> set sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+
1 row in set (0.00 sec)

mysql> create table t2(id int ,c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `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 发现 t2 的表结构,出现一个名为 my_row_id 的不可见主键。

分别在两个表插入数据:
mysql> insert into t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)
mysql> insert into t2 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from t2;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)


直接通过 select * table_name 查询表时,t2 和普通表t1没有差异。 因为 GIPK 是基于不可见列实现的,如果我们显式指定访问 my_row_id ,则可以查看到隐藏的主键 my_row_id。
mysql> select my_row_id,id,c1 from t2;
+-----------+------+------+
| my_row_id | id   | c1   |
+-----------+------+------+
|         1 |    1 |    1 |
|         2 |    2 |    2 |
|         3 |    3 |    3 |
+-----------+------+------+
3 rows in set (0.00 sec)
所以,从业务程序访问数据库的来看,开启 GIPK 对业务是透明的。

四、表结构变更注意事项:
当开启 GIPK 特性时,MySQL 生成的主键不能更改,只能在 VISIBLE 和 INVISIBLE 之间进行切换。比如:
使 GIPK 主键可见: alter table TALBE_NAME alter column my_row_id set visible;
mysql> alter table t2 alter column my_row_id set visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `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)

### 而且可以被直接查询到
mysql> select * from t2;
+-----------+------+------+
| 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;
mysql> alter table t2 alter column my_row_id set invisible;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `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
mysql> select * from t2;
+------+------+
| id   | c1   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

当开启 GIPK 之后 ,my_row_id 是系统关键字,我们创建无主键的表时,不能包含名为 my_row_id 的字段 。
mysql> create table t3(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 的主键的表 :
mysql> 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
mysql> alter table t2 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.'


mysql> alter table t2 drop PRIMARY KEY,add primary key(id);
ERROR 4111 (HY000): Please drop primary key column to be able to drop generated invisible primary key.


mysql> alter table t2 drop column my_row_id,add primary key(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


五、主从复制注意事项:
需要注意的是 set sql_generate_invisible_primary_key=on|off 并不会被复制到从库,主库上开启该特性的话,从库并不会开启 GIPK 。也就是说从库也不会为任何在源库上没有创建主键的表创建主键。 
下面测试一下,当主库关闭该特性,从库开启这个特性时的情况:
在 master 上关闭该特性并且创建无主键表t3 
mysql> set sql_generate_invisible_primary_key=off;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>show variables like 'sql_generate_invisible_primary_key';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
| t4             |
| t5             |
+----------------+
5 rows in set (0.00 sec)

mysql> create table t3(id int ,c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `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 t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `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 ,源库上创建无主键表,从库上并不会主动为该表创建主键。


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


七、限制
只支持 InnoDB 存储引擎。
支持 row 模式复制,不支持 statement 模式复制。
my_row_id 成为系统关键字。


八、总结
总体而言,该特性绝对是强需求。像把MySQL只当做一个存数据的容器时,很多开发并不会创建主键,像BI开发人员,所以现实情况是,什么样的情况都可能会发生。 MySQL 数据库稳定性也因为很多无主键的表,导致很多故障发生,对于自建场景尤其是没有严格审核流程的开发团队 ,该特性更能提升数据库系统稳定性和安全性。


【昌哥IT课堂】MySQL8.0新特性之不可见主键
官方文档中文翻译版本下载地址:
https://cloud.189.cn/t/eUBJJzaeqUJ3 (访问码:qg7d)


微信公众号视频播放地址:
https://mp.weixin.qq.com/s/9v-e6mZHAHB8Q4uoWQlonw

分割线
感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS