MySQL8.0 GIPK隐藏主键可视了

原创 CuiHulong 2022-08-16

2177

MySQL里主键的重要性不言而喻，为了维持索引组织表特性，当没有主键的时候，有会生成一个隐藏列_rowid来标记唯一。在前期的版本中，隐藏的_rowid作用于mvcc，undo，redo等内部机制，无法在其他地方使用。特别是复制环节，为了避免逻辑回放的全表扫描，迫不得已人为设计显式的主键。

GIPK介绍

经过多个版本迭代5.6,5.7，到MySQL 8.0.30现在支持Generated Invisible Primary Keys模式，它会在任何创建的InnoDB表中添加一个不可见的主键(GIPK)。这个增强只适用于InnoDB表。

通过GIPK方式添加到InnoDB表中生成的键列的定义如下:

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

生成的主键的名是my_row_id，BIGINT UNSIGNED（2的64次方）整数自增类型，不用考虑用完问题了。当GIPK模式生效时，不能在CREATE TABLE语句中使用列名来创建一个新的InnoDB表，除非它包含显式的主键。

mysql> CREATE TABLE `pk_table` (
  `id` int NOT NULL,
  `name` varchar(25) NOT NULL
) ;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE `pk_table`;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pk_table | CREATE TABLE `pk_table` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL,
  `name` varchar(25) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


#MySQL的 Information 下 COLUMNS and STATISTICS 表也可以查看到
```sql
mysql> select * from STATISTICS where TABLE_NAME='pk_table' limit 3\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: db9
   TABLE_NAME: pk_table
   NON_UNIQUE: 0
 INDEX_SCHEMA: db9
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: my_row_id
    COLLATION: A
  CARDINALITY: 2
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
   IS_VISIBLE: YES
   EXPRESSION: NULL
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from COLUMNS where TABLE_NAME='pk_table' limit 2\G;
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: db9
              TABLE_NAME: pk_table
             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



# 解析Binlog记录
binlog也会记录相关信息。
```sql
mysql> INSERT INTO `pk_table` (id,name) VALUES(1,'K');
Query OK, 1 row affected (0.01 sec)


mysql> select * from `pk_table` ;
+----+------+
| id | name |
+----+------+
|  1 | K    |
+----+------+
1 row in set (0.00 sec)

mysql> UPDATE `pk_table`  SET  name='W' WHERE  id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

shell # mysqlbinlog --no-defaults  --base64-output=decode-rows -vv  mysql-bin.000032

create 语句

#220815 19:24:05 server id 129  end_log_pos 571 CRC32 0x4aa7c422 	Query	thread_id=23	exec_time=0	error_code=0	Xid = 128
use `db9`/*!*/;
SET TIMESTAMP=1660562645/*!*/;
SET @@session.character_set_client=46,@@session.collation_connection=46,@@session.collation_server=46/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `pk_table` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL,
  `name` varchar(25) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`my_row_id`)
)
/*!*/;

变更语句

#220815 19:27:21 server id 129  end_log_pos 729 CRC32 0x56f99f28 	Query	thread_id=23	exec_time=0	error_code=0
SET TIMESTAMP=1660562841/*!*/;
BEGIN
/*!*/;
# at 729
#220815 19:27:21 server id 129  end_log_pos 909 CRC32 0x71e68a32 	Write_rows: table id 101 flags: STMT_END_F
### INSERT INTO `db9`.`pk_table`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=0 is_null=0 */
###   @3='K'

#220815 19:33:52 server id 129  end_log_pos 1301 CRC32 0xf1f1b86e 	Update_rows: table id 101 flags: STMT_END_F
### UPDATE `db9`.`pk_table`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=0 is_null=0 */
###   @3='K' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=0 is_null=0 */
###   @3='W' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */

控制参数

GIPKs启动
默认情况下，GIPKs不启用。要启用它们，可以将sql_generate_invisible_primary_key服务器系统变量。

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

mysql> SET global sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

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

SHOW CREATE命令是否显示

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

可见

当GIPKs生效时，是否显示生成的不可见的主键，可以使用alter TABLE tbl CHANGE COLUMN my_row_id SET VISIBLE和alter TABLE tbl CHANGE COLUMN my_row_id SET invisible来切换GIPK的可见性。

mysql> ALTER TABLE   `pk_table`  ALTER column my_row_id set visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from pk_table;
+-----------+----+------+
| my_row_id | id | name |
+-----------+----+------+
|         1 |  1 | W    |
+-----------+----+------+
1 row in set (0.00 sec)

mysql> ALTER TABLE   `pk_table`  ALTER column my_row_id set invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from pk_table;
+----+------+
| id | name |
+----+------+
|  1 | W    |
+----+------+
1 row in set (0.00 sec)

复制影响

按照官方说明：此设置对复制应用程序线程没有影响。不会特殊处理按照binlog进行回放。

relay log解析

shell# mysqlbinlog --no-defaults  --base64-output=decode-rows -vv  relay-log.000003

# at 665
#220815 21:38:58 server id 129  end_log_pos 744 CRC32 0xda996ab0 	Query	thread_id=13	exec_time=0	error_code=0	Xid = 110
SET TIMESTAMP=1660570738/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `pk_table` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int NOT NULL,
  `name` varchar(25) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`my_row_id`)
)
/*!*/;
# at 1118
#220815 21:38:58 server id 129  end_log_pos 972 CRC32 0x65a2df19 	Rows_query
# INSERT INTO `pk_table` (id,name) VALUES(1,'K')
# at 1188
#220815 21:38:58 server id 129  end_log_pos 1032 CRC32 0x2def7b20 	Table_map: `db9`.`pk_table` mapped to number 176
# at 1248
#220815 21:38:58 server id 129  end_log_pos 1082 CRC32 0x926e0d48 	Write_rows: table id 176 flags: STMT_END_F
### INSERT INTO `db9`.`pk_table`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=0 is_null=0 */
###   @3='K' /* VARSTRING(100) meta=100 nullable=0 is_null=0 */

逻辑导出

mysqldump&mysqlpump逻辑导出skip-generated-invisible-primary-key选项从输出中排除生成的不可见的主键。mydumper需要跟上节奏了。

shell # mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip-generated-invisible-primary-key -B db9  > pt_table1.sql

shell # mysqlpump -uroot -p123456 --set-gtid-purged=OFF --skip-generated-invisible-primary-key -B db9  > pt_table2.sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db9` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `db9`;
--
-- Table structure for table `pk_table`
--
DROP TABLE IF EXISTS `pk_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pk_table` (
  `id` int NOT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

其他影响

因为my_row_id占据了自增属性，所以无法再创建auto属性字段

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)

mysql> CREATE TABLE `pk_table1` (
          `id` int NOT NULL,
          `iid` bigint unsigned NOT NULL auto_increment ,
           `name`  varchar(25) NOT NULL,
           key(`iid`)
        ) ;
ERROR 4109 (HY000): Failed to generate invisible primary key. Auto-increment column already exists.

my_row_id已经是MySQL关键词范畴了，不能随意使用。

mysql> CREATE TABLE `pk_table2` (
         my_row_id  int NOT NULL,
           `name`  varchar(25) NOT NULL
       ) ;
ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

无法删除my_row_id，重新选主键

mysql> ALTER  TABLE  pk_table   DROP  PRIMARY  KEY ,ADD primary key (id);;
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开发中，对于表设计绝大情况下会要求有主键，

GIPK的出现间接的避免的无显示主键的问题；
并且复制中全表扫描的性能问题；此情况下缓解主从延迟问题；
但同时因为是自增属性，所以无法再创建额外的自增键；
GIPK键列，也无法变删除或更列属性；

隐藏主键改成可视主键，无主键表复制性能提升!

参考

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-30.html#mysqld-8-0-30-gipk

墨力计划 mysql

最后修改时间：2022-12-27 13:28:56

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

文章被以下合辑收录

专家专栏 | 崔虎龙（共162篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

GIPK介绍
控制参数
可见
复制影响
逻辑导出
其他影响
总结
参考