理解 MySQL 隐式主键

很早，在 MySQL 运维的过程中就发现了有一类复制延迟问题，非常难缠。当主库的表没有主键/唯一键时，在主库使用一条 UPDATE 或 DELETE操作了大量记录，在使用ROW模式的备库中，则会收到对应的、大量的变更记录，而这些变更记录在备库上应用（apply）时，因为没有主键或者唯一索引，每一条变更的回放都需要很长时间，最终导致主备之间无法追上的延迟。

所以，在很早的时候，MySQL 规范中就有一条，表必须要有主键。对于企业，也许可以通过规范，或者调整表结构去绕开这个问题，但是，对于提供数据库托管服务的云厂商来说，却没法去要求上面使用数据库的用户去做任何适配。但是，云厂商有需要为这些数据库服务提供基于主备的高可用能力。这就陷入了一个困境，这也是为什么云厂商可能是最早需要解决这个问题的。

早在 2016 年，阿里云的 RDS 就已经通过引入隐式主键解决类似的问题：MySQL · 最佳实践 · RDS 只读实例延迟分析。

MySQL的实现方案

相比于社区的实现，MySQL 官方的实现考虑的更加全面，首先引入不可见列、不可见索引等特性，然后再在此基础上实现隐式主键，也全面的考虑对历史版本的兼容性、对复制的影响、对备份的影响、对各类操作命令的影响等。

在 MySQL 8.0.30 版本（2022年07月）中，官方MySQL正式引入了隐式主键的功能。对于所有没有显式主键的 InnoDB 表，都会新增一个如下的隐式主键：

my_row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT INVISIBLE PRIMARY KEY

所以，甚至在你新建一个 InnoDB 表时，如果你没有显式的主键，那么字段名 my_row_id就不能再使用了。也因为该版本是通过 INVISIBLE COLUMN 实现的，所以可以通过ALTER TABLE t1 CHANGE COLUMN...命令将隐式主键转换为普通列。

打开隐式主键功能

在 MySQL 中可以通过配置参数 sql_generate_invisible_primary_key 是否开启隐式主键功能。

参数 show_gipk_in_create_table_and_information_schema 则可以控制在SHOW以及 information_schema中是否展示隐式主键信息，该参数可以帮助使用SHOW以及 information_schema的应用程序，依旧保持很好的兼容性。

其他相关的参数包括：

sql_require_primary_key ：该参数可以强制要求数据库中的表尽量有主键。例如，创建表、ALTER表时都需要表有主键；删除表的主键失败等，总之，尽可能的要求表均有主键。
REQUIRE_TABLE_PRIMARY_KEY_CHECK 这是复制配置时的选项，该参数控制的是复制时的应用线程（apply）如何检查表是否有主键，该选项的取值为：{STREAM | ON | OFF | GENERATE}。该参数可以很好的控制，从主库复制过来的表，对主键配置的要求。

DDL 、复制与Binlog

如果MySQL开启了隐式主键，那么就像invisible column一样，CREATE TABLE、ALTER TABLE的创建的隐式主键也会存储在 Binlog 中，所以备库如果在复制时，也可以活动对应的信息。

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

mysql> create table t1_no_pk(n char(10),age int);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1_no_pk\G
*************************** 1. row ***************************
       Table: t1_no_pk
Create Table: CREATE TABLE `t1_no_pk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `n` char(10) 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.00 sec)

再来使用mysqlbinlog命令看看对应 binlog格式：

# at 653
#250316 15:02:05 server id 1  end_log_pos 928 CRC32 0x9ca72462 	Query	thread_id=9	exec_time=0	error_code=0	Xid = 31
SET TIMESTAMP=1742108525/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t1_no_pk` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `n` char(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

orczhou.com

理解 MySQL 隐式主键

目录

为什么需要隐式主键

MySQL的实现方案

打开隐式主键功能

DDL 、复制与Binlog

参考阅读

Leave a Reply