MySQL 8.0.30 新新特性 不可见主键_mysql8.0隐式主键

MySQL 8.0.30引入了“生成的不可见主键”（GIPK）功能。本博客介绍了此功能，讨论了其局限性和限制，并描述了备份和恢复操作如何使用此功能。让我们开始吧！

背景

InnoDB存储引擎包含一个称为隐式主键的内部功能。此功能会在生成的名为DB_ROW_ID的列上自动生成名为GEN_CLUST_index的隐藏聚集索引。隐式主键是在创建没有Primary Key或UNIQUE Key的表时生成的，其中所有键列都定义为NOT NULL。DB_ROW_ID列本身是一个6字节字段，随着新行插入表中，该字段单调增加。隐式主键是一种确保解决与表锁定主键相关的限制的机制。然而，在处理隐式主键时，除了它的优点之外，还有一些缺点需要考虑：
 

可伸缩性问题：所有具有隐式主键的表都使用所有表通用的序列计数器为DB_ROW_ID列生成唯一的6字节值。但是，此计数器受到互斥锁的保护，这可能会引入可伸缩性问题。使用隐式主键并发插入表可能会由于互斥锁的争用而导致性能问题。

不适用于复制设置（经典和高可用性）：隐式主键的使用在复制环境中带来了挑战，因为它依赖于公共序列计数器来生成唯一值。对于表的同一行，不可能在多个复制节点之间为DB_row_ID生成相同的值。因此，隐式主键不能在复制设置中使用。这一限制意味着，在高可用性设置中不支持具有隐式主键的表，并且经典复制无法有效地执行此类表。

InnoDB隐式主键的缺点促使人们在MySQL中实现新的GIPK功能。GIPK功能为开发人员和数据库管理员提供了极大的便利，因为它可以自动在不可见列上生成主键。这消除了在某些场景中显式定义主键的需要，同时保留了使用主键确保数据一致性和优化查询的优势。此外，GIPK功能有助于解决与缺少主键的表相关的限制。例如，MySQL中的高可用性解决方案Group Replication不支持没有主键的表。此外，对于缺少主键的表，经典复制可能无法以最佳或高效的方式执行。因此，GIPK功能使开发人员能够克服这些限制，并确保MySQL数据库的顺利运行。

GIPK简介

当用户尝试创建一个没有主键的InnoDB表时，MySQL会在启用该功能时自动为该表生成主键。默认情况下，此功能处于禁用状态。要启用它，请将“sql_generate_invisible_primary_key”变量设置为“ON”。

以下是一个示例，演示在无GIPK模式（禁用GIPK功能）和GIPK模式中创建表的差异：

在无GIPK模式下：

-- Disable GIPK mode

SET GLOBAL sql_generate_invisible_primary_key = OFF;

-- Create InnoDB table without primary key

CREATE TABLE users(id INT, name VARCHAR(50), age INT) ENGINE = InnoDB;

-- Verify the table structure

SHOW CREATE TABLE users

*************************** 1. row ***************************

Table: users

Create Table: CREATE TABLE `users` (

`id` int DEFAULT NULL,

`name` varchar(50) DEFAULT NULL,

`age` int DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

在这种模式下，使用现有的InnoDB机制创建隐藏的隐式主键来生成表的主键

GIPK模式下：

-- Enable GIPK mode

SET GLOBAL sql_generate_invisible_primary_key = ON;

-- Create InnoDB table without primary key

CREATE TABLE users(id INT, name VARCHAR(50), age INT) ENGINE = InnoDB;

-- Verify the table structure

SHOW CREATE TABLE users

*************************** 1. row ***************************

       Table: users

Create Table: CREATE TABLE `users` (

  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,

  `id` int DEFAULT NULL,

  `name` varchar(50) DEFAULT NULL,

  `age` int DEFAULT NULL,

  PRIMARY KEY (`my_row_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.02 sec)

在GIPK模式下，MySQL会自动添加一个名为“my_row_id”的不可见列，其类型为“bigint unsigned NOT NULL auto_increment”，作为表的列列表中的第一列。此外，在“my_row_id”列上添加了一个主键。

当MySQL中的InnoDB表没有显式定义主键时，数据库会将所有键列都定义为not NULL的UNIQUE key视为隐式主键。启用GIPK后，即使对于使用隐式主键创建的表，也会生成主键。

以下是一个示例：

-- Enable GIPK mode

SET GLOBAL sql_generate_invisible_primary_key = ON;

-- Create InnoDB table without primary key

CREATE TABLE users(id INT NOT NULL UNIQUE, name VARCHAR(50), age INT) ENGINE = InnoDB;

-- Verify the table structure

SHOW CREATE TABLE users

*************************** 1. row ***************************

       Table: users

Create Table: CREATE TABLE `users` (

  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,

  `id` int NOT NULL,

  `name` varchar(50) DEFAULT NULL,

  `age` int DEFAULT NULL,

  PRIMARY KEY (`my_row_id`),

  UNIQUE KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.02 sec)

将GIPK添加到已存在的表：

在GIPK模式中，只有在创建表时，主键才会自动添加到表中。更改表不会自动为没有主键的表添加主键。不可见主键可以添加到现有表中，如下所示，

在独立的DB系统中，用户可以将GIPK添加到如下表中：

ALTER TABLE users ADD COLUMN my_row_id BIGINT UNSIGNED NOT NULL INVISIBLE AUTO_INCREMENT PRIMARY KEY FIRST;

在复制设置中，用户可以遵循此多阶段过程将GIPK添加到源表中：

ALTER TABLE users ADD COLUMN my_row_id BIGINT UNSIGNED DEFAULT NULL INVISIBLE FIRST;

SET @x = 0;                                        

UPDATE users SET my_row_id = (@x := @x+1);

LOCK TABLES users WRITE;

SET @x := (SELECT MAX(my_row_id) FROM users);

UPDATE users SET my_row_id = (@x := @x+1) WHERE my_row_id IS NULL;

ALTER TABLE users ADD PRIMARY KEY(my_row_id), MODIFY COLUMN my_row_id BIGINT UNSIGNED AUTO_INCREMENT INVISIBLE;

UNLOCK TABLES;

将生成的不可见主键（GIPK）添加到现有表后，InnoDB在隐藏列DB_ROW_ID上的隐式主键和列DB_ROW_ID本身将被放弃。

在SHOW和information_SCHEMA表中查找GIPK信息：

用户可以在SHOW CREATE TABLE、SHOW COLUMNS和SHOW INDEX语句的输出中查看生成的不可见主键（GIPK）信息。此外，GIPK信息在information Schema COLUMNS和STATISTICS表中可见。

-- Enable GIPK mode

SET GLOBAL sql_generate_invisible_primary_key = ON;

-- Create InnoDB table without primary key

CREATE TABLE users(name VARCHAR(50)) ENGINE = InnoDB;

-- Verify the table structure

SHOW CREATE TABLE users

*************************** 1. row ***************************

       Table: users

Create Table: CREATE TABLE `users` (

  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,

  `name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`my_row_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.02 sec)

-- Verify INFORMATION_SCHEMA.COLUMNS output

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' and table_name='users'\G

*************************** 1. row ***************************

           TABLE_CATALOG: def

            TABLE_SCHEMA: test

              TABLE_NAME: users

             COLUMN_NAME: my_row_id

        ORDINAL_POSITION: 1

          COLUMN_DEFAULT: NULL

             IS_NULLABLE: NO

               DATA_TYPE: bigint

CHARACTER_MAXIMUM_LENGTH: NULL

  CHARACTER_OCTET_LENGTH: NULL

       NUMERIC_PRECISION: 20

           NUMERIC_SCALE: 0

      DATETIME_PRECISION: NULL

      CHARACTER_SET_NAME: NULL

          COLLATION_NAME: NULL

             COLUMN_TYPE: bigint unsigned

              COLUMN_KEY: PRI

                   EXTRA: auto_increment INVISIBLE

              PRIVILEGES: select,insert,update,references

          COLUMN_COMMENT:

   GENERATION_EXPRESSION:

                  SRS_ID: NULL

*************************** 2. row ***************************

           TABLE_CATALOG: def

            TABLE_SCHEMA: test

              TABLE_NAME: users

             COLUMN_NAME: name

        ORDINAL_POSITION: 2

          COLUMN_DEFAULT: NULL

             IS_NULLABLE: YES

               DATA_TYPE: varchar

CHARACTER_MAXIMUM_LENGTH: 50

  CHARACTER_OCTET_LENGTH: 200

       NUMERIC_PRECISION: NULL

           NUMERIC_SCALE: NULL

      DATETIME_PRECISION: NULL

      CHARACTER_SET_NAME: utf8mb4

          COLLATION_NAME: utf8mb4_0900_ai_ci

             COLUMN_TYPE: varchar(50)

              COLUMN_KEY:

                   EXTRA:

              PRIVILEGES: select,insert,update,references

          COLUMN_COMMENT:

   GENERATION_EXPRESSION:

                  SRS_ID: NULL

2 rows in set (0.01 sec)

-- Verify SHOW COLUMNS output

SHOW COLUMNS FROM users\G

*************************** 1. row ***************************

Field: my_row_id

Type: bigint unsigned

Null: NO

Key: PRI

Default: NULL

Extra: auto_increment INVISIBLE

*************************** 2. row ***************************

Field: name

Type: varchar(50)

Null: YES

Key:

Default: NULL

Extra:

2 rows in set (0.01 sec)

-- Verify INFORMATION_SCHEMA.STATISTICS output

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='test' and table_name='users'\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: users

NON_UNIQUE: 0

INDEX_SCHEMA: test

INDEX_NAME: PRIMARY

SEQ_IN_INDEX: 1

COLUMN_NAME: my_row_id

COLLATION: A

CARDINALITY: 0

SUB_PART: NULL

PACKED: NULL

NULLABLE:

INDEX_TYPE: BTREE

COMMENT:

INDEX_COMMENT:

IS_VISIBLE: YES

EXPRESSION: NULL

1 row in set (0.02 sec)

-- Verify SHOW INDEX output

SHOW INDEX FROM users\G

*************************** 1. row ***************************

Table: users

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: my_row_id

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

1 row in set (0.01 sec)

要在SHOW和information schema COLUMNS和STATISTICS结果集中隐藏生成的不可见主键（GIPK）信息，用户可以将系统变量“SHOW_GIPK_in_create_table_and_information_schema”设置为“OFF”。默认情况下，此变量设置为“ON”。以下是一个示例：

-- Enable GIPK mode

SET GLOBAL sql_generate_invisible_primary_key = ON;

-- Create InnoDB table without primary key

CREATE TABLE users(name VARCHAR(50)) ENGINE = InnoDB;

-- Verify the table structure with sql_gipk_in_create_table_and_information_schema=ON

SHOW CREATE TABLE users

*************************** 1. row ***************************

       Table: users

Create Table: CREATE TABLE `users` (

  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,

  `name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`my_row_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.02 sec)

-- Hide GIPK information

SET SESSION sql_gipk_in_create_table_and_information_schema=OFF;

-- Verify the table structure

SHOW CREATE TABLE users

*************************** 1. row ***************************

       Table: users

Create Table: CREATE TABLE `users` (

  `name` varchar(50) DEFAULT NULL,

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.02 sec)

-- Verify INFORMATION_SCHEMA.COLUMNS output

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='test' and table_name='users'\G

*************************** 1. row ***************************

           TABLE_CATALOG: def

            TABLE_SCHEMA: test

              TABLE_NAME: users

             COLUMN_NAME: name

        ORDINAL_POSITION: 2

          COLUMN_DEFAULT: NULL

             IS_NULLABLE: YES

               DATA_TYPE: varchar

CHARACTER_MAXIMUM_LENGTH: 50

  CHARACTER_OCTET_LENGTH: 200

       NUMERIC_PRECISION: NULL

           NUMERIC_SCALE: NULL

      DATETIME_PRECISION: NULL

      CHARACTER_SET_NAME: utf8mb4

          COLLATION_NAME: utf8mb4_0900_ai_ci

             COLUMN_TYPE: varchar(50)

              COLUMN_KEY:

                   EXTRA:

              PRIVILEGES: select,insert,update,references

          COLUMN_COMMENT:

   GENERATION_EXPRESSION:

                  SRS_ID: NULL

1 rows in set (0.01 sec)

-- Verify SHOW COLUMNS output

SHOW COLUMNS FROM users\G

*************************** 1. row ***************************

Field: name

Type: varchar(50)

Null: YES

Key:

Default: NULL

Extra:

1 rows in set (0.01 sec)

-- Verify INFORMATION_SCHEMA.STATISTICS output

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema='test' and table_name='users'\G

Empty set (0.00 sec)

-- Verify SHOW INDEX output

SHOW INDEX FROM users\G

Empty set (0.01 sec)

备份和恢复：

用户可以使用mysqldump和mysqlpump工具创建数据库的逻辑备份。默认情况下，这些工具包括生成的不可见主键（GIPK）以及表定义中的列定义，以及逻辑转储中生成列的值。这意味着，从逻辑转储恢复数据库时，将使用GIPK和生成列的相应值创建表。

为了提供更大的灵活性，mysqldump和mysqlpump都提供了一个名为“--skip-generated-invisibleprimarykey”的新选项。通过使用此选项，用户可以在不包含GIPK信息的情况下创建逻辑转储。如果用户希望从转储中排除生成的主键，这可能很有用。但是，需要注意的是，如果在GIPK模式下恢复逻辑转储，那么将为任何未定义主键的表生成主键。

另一方面，物理备份始终包括表的GIPK信息。从物理备份恢复时，GIPK模式不适用，因为备份包含实际的表数据以及生成的主键。

总体而言，mysqldump和mysqlpump工具为用户提供了在逻辑备份中包括或排除GIPK信息的选项，而物理备份始终保留表的GIPK。

GIPK的限制：

GIPK功能仅适用于InnoDB表。对于在其他存储引擎中创建的表，不会生成主键。

如果正在创建的表已经有一个名为“my_row_id”的列，则不会生成主键。

分区表目前不支持此功能。如果创建的分区表没有主键，则会报告错误。

解决方法是在此类表上显式创建主键。如果没有合适的自然主键，可以使用由分区列和以UUID为默认值的不可见列组成的主键。但是，在启用二进制日志的情况下向现有表添加这样的主键时，ALTER TABLE语句添加具有基于UUID的默认值的不可见列可能会出现问题。为了解决这个问题，需要遵循一个多阶段的过程。

ALTER TABLE t1 ADD COLUMN my_row_id VARBINARY(16)

ALTER TABLE t1 CHANGE COLUMN my_row_id SET DEFAULT (UUID_TO_BIN(UUID())

LOCK TABLES t1 WRITE;

-- This statement will be binlogged using ROW format!

UPDATE TABLE t1 SET row_id = UUID_TO_BIN(UUID()) WHERE my_row_id IS NULL;

ALTER TABLE t1 ADD PRIMARY KEY (part_col1, ... part_colN, my_row_id);

UNLOCK TABLES;

如果正在创建的表已经有AUTO_INCREMENT列，则不会添加主键。

一种可能的解决方法是使用为分区表描述的多阶段过程手动创建基于UUID的不可见主键。

如果在CREATE TABLE中没有指定主键。。。SELECT语句，则生成主键。但是，如果SELECT语句包含名称为“my_row_id”的列，则不会生成主键，并且会报告错误。

这种情况的解决方法是在同一CREATE TABLE中的“my_row_id”列上显式添加主键。。。SELECT语句，如下例所示：

CREATE TABLE users_copy (my_row_id BIGINT UNSIGNED

                         INVISIBLE AUTO_INCREMENT PRIMARY KEY)

                         AS SELECT my_row_id, users.* FROM users;

使用基于语句的二进制日志记录时，正在复制CREATE TABLE。。。生成不可见主键的SELECT是不安全的，因此在这种情况下会报告错误。

如果CREATE table的源表。。。LIKE没有主键，则不会为目标表生成主键。创建表。。。LIKE通常用于目标表定义必须与源表定义类似的情况。因此，为了保持相同的行为，主键不会添加到目标表中。

不允许对CHANGE/MODIFY/ALTER/RENAME“my_row_id”列执行ALTER TABLE操作。在这种情况下会生成一个错误。

更改“my_row_id”的可见性是一个例外。允许对“my_row_id”执行此操作。请注意，更改“my_row_id”列的可见性也会影响生成的不可见主键的属性。

更改表。。。DROP PRIMARY KEY删除生成的不可见主键，但不允许在同一语句中删除“my_row_id”列。

在GIPK模式下，不允许ALTER TABLE操作导致没有主键的表。在这种情况下会报告一个错误。
 

References

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

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_generate_invisible_primary_key

https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_show_gipk_in_create_table_and_information_schema

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_skip-generated-invisible-primary-key

https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html#option_mysqlpump_skip-generated-invisible-primary-key

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

Thanks for using MySQL!

文章来源于mysql博客：https://blogs.oracle.com/mysql/post/generated-invisible-primary-key