MySQL8.0新特性之：生成不可见主键GIPK

概念描述

  从MySQL 8.0.30 版本开始，MySQL支持为任何InnoDB引擎下，没有显式创建主键的表生成不可见主键。当 sql_generate_invisible_primary_key 服务器系统变量设置为 ON 时，MySQL会自动将生成的不可见主键（GIPK）添加到这类表中，表现形式为：

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

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

说明：当sql_generate_invisible_primary_key值为ON，创建无主键表时，MySQL会自动为其创建一个不可见主键 my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */ PRIMARY KEY

 

GIPK的意义

  当前MySQL数据库在运维工程中我们强烈建议表必须有主键，默认MySQL并没有进行限制，无需主键也能创建innodb表，但这基本上不是最佳实践。
  GIPK出现后可以解决相当一部分潜在的问题：避免表因为没有主键后数据量增多而造成的查询性能下降；避免MGR架构中表必须有主键的限制；解决了主从复制架构中MySQL表没有主键造成的主从同步延迟问题。。。

 

测试验证

#### 设置参数sql_generate_invisible_primary_key启用：
mysql > set global sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.01 sec)

#### 显示创建无主键表：gipk
mysql >create table gipk(name varchar(50),remark varchar(100));
Query OK, 0 rows affected (0.01 sec)

#### 查询建表语句：
mysql >show create table gipkG
*************************** 1. row ***************************
Table: gipk
Create Table: CREATE TABLE `gipk` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`remark` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

#### 查看主键列信息：
mysql > show columns from gipk;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO | PRI | NULL | auto_increment INVISIBLE |
| name | varchar(50) | YES | | NULL | |
| remark | varchar(100) | YES | | NULL | |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.00 sec)

#### 插入数据验证：
root@localhost:rockdb 05:17:55 >insert into gipk values ('tom','aaa');
Query OK, 1 row affected (0.00 sec)

root@localhost:rockdb 05:18:18 >insert into gipk values ('nick','abcd');
Query OK, 1 row affected (0.00 sec)

root@localhost:rockdb 05:18:30 >insert into gipk values ('rock','xyz');
Query OK, 1 row affected (0.00 sec)

root@localhost:rockdb 05:18:37 >select * from gipk;
+------+--------+
| name | remark |
+------+--------+
| tom | aaa |
| nick | abcd |
| rock | xyz |
+------+--------+
3 rows in set (0.00 sec)

#### 显示查询不可见主键my_row_id：
root@localhost:rockdb 05:18:45 >select my_row_id,name,remark from gipk;
+-----------+------+--------+
| my_row_id | name | remark |
+-----------+------+--------+
| 1 | tom | aaa |
| 2 | nick | abcd |
| 3 | rock | xyz |
+-----------+------+--------+
3 rows in set (0.00 sec)

#### 查询建表语句，发现auto_increment值已经变为4
root@localhost:rockdb 05:19:02 >show create table gipkG
*************************** 1. row ***************************
Table: gipk
Create Table: CREATE TABLE `gipk` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`remark` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

在binlog中的表现

GIPK启用后binlog日志中会记录完整的建表语句，包含相关不可见主键的信息：

-- 解析相关binlog日志内容：
/data/mysql/mysql8034/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -vv binlog.000009 > 9.sql


-- 查看binlog日志中关于建表和插入数据的相关语句：
-- [root@c1 binlogs]# vi 9.sql

# at 7894
#231025 17:01:24 server id 1288034 end_log_pos 8241 CRC32 0xfd09942c Query thread_id=12 exec_time=0 error_code=0 Xid = 150
SET TIMESTAMP=1698224484/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `gipk` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`remark` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
)
/*!*/;
# at 8241
#231025 17:18:18 server id 1288034 end_log_pos 8320 CRC32 0xd08ced7b GTID last_committed=29 sequence_number=30 rbr_only=yes original_committed_timestamp=1698225498209989 immedi
ate_commit_timestamp=1698225498209989 transaction_length=301
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1698225498209989 (2023-10-25 17:18:18.209989 CST)
# immediate_commit_timestamp=1698225498209989 (2023-10-25 17:18:18.209989 CST)
/*!80001 SET @@session.original_commit_timestamp=1698225498209989*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= '7ce9d1d6-60d8-11ee-9656-000c29ff0201:3839'/*!*/;
# at 8320
#231025 17:18:18 server id 1288034 end_log_pos 8397 CRC32 0x343e280c Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1698225498/*!*/;
BEGIN
/*!*/;
# at 8397
#231025 17:18:18 server id 1288034 end_log_pos 8458 CRC32 0xca249a37 Table_map: `rockdb`.`gipk` mapped to number 118
# has_generated_invisible_primary_key=1
# at 8458
#231025 17:18:18 server id 1288034 end_log_pos 8511 CRC32 0x209ea15c Write_rows: table id 118 flags: STMT_END_F
### INSERT INTO `rockdb`.`gipk`
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='tom' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### @3='aaa' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
# at 8511
#231025 17:18:18 server id 1288034 end_log_pos 8542 CRC32 0x654a2d47 Xid = 156
COMMIT/*!*/;


在复制中的表现

  GIPK参数 sql_generate_invisible_primary_key（默认未开启OFF）的设置只会在当前实例中生效，但这并不影响主从复制或者MGR架构同步的正常运行，但是需要保证的是基于ROW模式的复制。

在导出数据中的表现

  默认情况下mysqldump会将含有GIPK信息的不可见主键信息和相关my_row_id数据一起导出：

-- 默认导出：
/data/mysql/mysql8034/bin/mysqldump -uroot -pmysql -S /data/mysql/8034/run/mysql.sock rockdb gipk > gipk.sql

[root@c1 8034]# more gipk.sql
-- MySQL dump 10.13 Distrib 8.0.34, for Linux (x86_64)
--
-- Host: localhost Database: rockdb
-- ------------------------------------------------------
-- Server version 8.0.34

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '7ce9d1d6-60d8-11ee-9656-000c29ff0201:1-3856';

--
-- Table structure for table `gipk`
--

DROP TABLE IF EXISTS `gipk`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `gipk` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`remark` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `gipk`
--

LOCK TABLES `gipk` WRITE;
/*!40000 ALTER TABLE `gipk` DISABLE KEYS */;
INSERT INTO `gipk` (`my_row_id`, `name`, `remark`) VALUES (1,'tom','aaa'),(2,'nick','abcd'),(3,'rock','xyz'),(4,'enmo','best');
/*!40000 ALTER TABLE `gipk` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-10-26 15:24:15


 
  可以通过参数 --skip-generated-invisible-primary-key 将GIPK信息排除掉：

[root@c1]# mysqldump -uroot -pmysql -S /data/mysql/8034/run/mysql.sock --set-gtid-purged=OFF --skip-generated-invisible-primary-key rockdb gipk > gipk3.sql


[root@c1 8034]# more gipk3.sql
-- MySQL dump 10.13 Distrib 8.0.34, for Linux (x86_64)
--
-- Host: localhost Database: rockdb
-- ------------------------------------------------------
-- Server version 8.0.34

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `gipk`
--

DROP TABLE IF EXISTS `gipk`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `gipk` (
`name` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
`remark` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `gipk`
--

LOCK TABLES `gipk` WRITE;
/*!40000 ALTER TABLE `gipk` DISABLE KEYS */;
INSERT INTO `gipk` VALUES ('tom','aaa'),('nick','abcd'),('rock','xyz'),('enmo','best');
/*!40000 ALTER TABLE `gipk` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-10-26 15:33:20


   说明： 使用参数 --skip-generated-invisible-primary-key 将会对原有数据产生完整性影响（会去掉表中的不可见主键和相关的数据），注意结合实际情况应用。

 

限制和约束

 1. GIPK只支持innodb引擎表

 2. GIPK如果在复制中应用，只支持基于row模式的复制，不支持基于statement模式复制。

 3. GIPK功能启用时，创建无主键表时，不能含有my_row_id名字的字段

mysql >create table t_g (id int ,name varchar(50),my_row_id int);
ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.


 4. GIPK功能启用时，my_row_id主键不能被修改，只能调整VISIBLE/INVISIBLE属性

#### 可以设置对my_row_id主键的属性调整（visible/invisible）
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 > alter table t2 alter column my_row_id set invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

#### 不能进行对my_row_id列除visible/invisible之外的其他调整
mysql > alter table t2 modify column my_row_id varchar(100);
ERROR 4110 (HY000): Altering generated invisible primary key column 'my_row_id' is not allowed.


 

参考文档

https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html