首页 > 数据库 >【昌哥IT课堂】MySQL8.0新特性之不可见主键

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

时间：2024-09-10 14:04:41浏览次数：12  
标签：昌哥 primary 主键 sec key MySQL8.0 id row

 


一、概述
作为 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


文章看完了，如果觉得本文对您的工作或生活有用，希望分享给你身边的朋友，一起学习，共同进步哈~~~

欢迎关注我的公众号【数库信息技术】，你的关注是我写作的动力源泉

各大平台都可以找到我：
————————————————————————————
公众号：数库信息技术
墨天轮：https://www.modb.pro/u/427810
百家号：https://author.baidu.com/home/1780697309880431
CSDN ：https://blog.csdn.net/rscpass
51CTO: https://blog.51cto.com/u_16068254
博客园：https://www.cnblogs.com/shukuinfo
知乎:https://www.zhihu.com/people/shukuinfo
————————————————————————————

 

标签：昌哥,primary,主键,sec,key,MySQL8.0,id,row
From： https://www.cnblogs.com/shukuinfo/p/18406257

相关文章

  • [MySQL]为什么主键最好是有序递增的
    为什么主键索引最好是有序递增的我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？InnoDB创建主键索引默认为聚簇索引，数据被存放在了B+Tree的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时......
  • centos7.9安装mysql8.0.39
    1.添加MySQLYum仓库首先，需要下载并安装MySQLYum仓库RPM包：sudorpm-Uvhhttps://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm2.更新Yum缓存安装完Yum仓库后，更新Yum缓存：sudoyumcleanallsudoyummakecache3.安装MySQL 现在可以......
  • docker 安装mysql8.0
    1、window安装dockerdesktop查询镜像：dockerimages删除镜像：dockerrmi[镜像id]查询容器：dockerps-a删除容器：dockerrm[容器id]停止容器：dockerstop[容器id]重启容器：dockerrestart[容器id]启动命令：dockerrun-it-vD:/docker/mysql/data:/var/lib/mysql-vD:/doc......
  • MySQL零基础入门教程-8.1 表的连接\增删数据、表结构的增删改、字段约束(非空、唯一
    教程来源：B站视频BV1Vy4y1z7EX001-数据库概述_哔哩哔哩_bilibili我听课收集整理的课程的完整笔记，供大家学习交流下载：夸克网盘分享本文内容为完整笔记的第三篇目录1、表怎么进行连接的2、insert语句可以一次插入多条记录吗？可以的！3、快速创建表？【了解内容】4、将查询结果......
  • MySQL8.0Windows安装说明
    目录MySQL8.0Windows安装说明下载并安装压缩包初始化数据安装服务操作服务设置密码windows界面服务管理dll库缺失问题将MySQL添加全局路径MySQL8.0Windows安装说明下载并安装压缩包下载地址：https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.16-winx64.zip安装：解压......
  • mysql为什么不推荐uuid做主键?
    在MySQL中，不推荐使用UUID作为主键的主要原因还是性能问题，其次是可读性差和浪费存储空间。性能问题：UUID是128位的字符串，通常被表示为32个字符的十六进制数。相比自增的整数（如AUTO_INCREMENT），UUID更大，占用的存储空间也更多，这会增加索引大小，导致查询变慢，尤其是在大表中。无序......
  • 自增主键去哪了?---一次开发过程中的思考
    前情提要：最近新接了一个需求，需要去创建两张表，其中有一张表需要根据业务id和业务类型建立唯一索引，对数据唯一性进行约束。因为涉及到业务嘛，表结构就进行缩略了表结构示例如下：CREATETABLE`example_table`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'......
  • MybatisPlus 主键策略之type=IdType.ASSIGN_ID等详解
    雪花算法（雪花）是微博开源的分布式ID生成算法其核心思想就是：使用一个64位的长型的数字作为全局唯一ID,主要介绍了MybatisPlus 主键策略（type=IdType.ASSIGN_ID等详解）,需要的朋友可以参考下: 我们可以通过@TableId注解的 类型属性来设置主键id的增长策略，一共有几个多个主键策略，......
  • UUIDV7: 我就是要用UUID做主键
    一直以来的互联网谣言:UUID是不适合用作数据库主键的.为什么?因为UUID是全随机的,对于数据库的索引不友好,插入时可能导致大量的索引树的分支合并.UUID根据RFC4122的描述,UUID被设计用于去中心化的ID生成格式128bit,16个字节,示例:f81d4fae-7dec-11d0-a765-00a0c91e6......
  • UUIDV7: 我就是要用UUID做主键
    一直以来的互联网谣言:UUID是不适合用作数据库主键的.为什么?因为UUID是全随机的,对于数据库的索引不友好,插入时可能导致大量的索引树的分支合并.UUID根据RFC4122的描述,UUID被设计用于去中心化的ID生成格式128bit,16个字节,示例:f81d4fae-7dec-11d0-a765-00a0c91e6......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99