MySQL 8.0.30新特性之GIPK不可见主键sql_generate_invisible_primary_key

0    34    2

👉 本文共约1909个字,系统预计阅读时间或需8分钟。

简介

作为 MySQL DBA ,相信大家都经历过在复制模式下,如果没有主键,遇到 load data ,大事务,ddl 等有大量表数据行扫描的行为时,会带来严重的主从延迟,给数据库稳定性和数据一致性带来隐患。例如:https://www.xmmup.com/dbbao34ceshimysqlzhucongfuzhizhongzhukubiaoqueshizhujianhuidaozhizhu.html

从MySQL 8.0.30 开始,提出了一个令人惊喜的特性 -(Generated Invisible Primary Keys)简称 GIPK 。一句概况就是: 当开启GIPK模式后,MySQL 会在没有显示定义主键的InnoDB表上自动生成不可见的主键

对于已经使用云RDS的朋友,可能很早就享受到云 RDS MySQL 提供的隐式主键特性。但是对于自建数据库的企业,GIPK 依然是一个比较期待特性,(当然有和用起来是两码事!)

示例

环境

开启新特性

GIPK 由参数sql_generate_invisible_primary_key 控制,默认关闭,表示禁用,如果需要使用该特性,则需显式开启。

测试

我们分别在关闭和开启该特性下创建两个无主键表:

开启 GIPK 并创建无主键表 t3 。

我们可以通过 show create table 发现 t3 的表结构,出现名为 my_row_id 的不可见主键。对两个表插入数据查看差异:

直接通过 select * from table 查询时,t3 和普通表t1无差异。因为 GIPK 是基于不可见列实现的,如果我们显式指定访问 my_row_id ,则可以查看到隐藏的主键 my_row_id

总的来说,从业务程序访问数据库的角度来看,开启 GIPK 对业务是透明的。

关于 DDL

当开启 GIPK 特性时,MySQL 生成的主键不能更改,只能在 VISIBLE 和 INVISIBLE 之间进行切换。比如:

使 GIPK 主键可见: alter table TALBE_NAME alter column my_row_id set visible;

关闭起可见性 : alter table TABLE_NAME alter column my_row_id set invisible;

另外就是开启 GIPK 之后 ,my_row_id 是系统关键字,我们创建无主键的表时,不能包含名为 my_row_id 的字段 。

当然如果 MySQL 允许创建包含名为 my_row_id 的主键的表 :

当开启 GIPK 模式时,则不能直接删除不可见主键,必须显式增加一个新的主键然后再删除 GIPK,如下:

关于主从复制

需要注意的是 set sql_generate_invisible_primary_key=on|off 并不会被复制到从库,主库上开启该特性的话,从库并不会开启 GIPK 。也就是说从库也不会为任何在源库上没有创建主键的表创建主键。可能会有读者疑问如果主库关闭该特性,但是从库显示开启呢? 做个测试看看:

在 master 上关闭该特性并且创建无主键表t6

在从库上开启该特性

结果: 主库关闭 GIPK ,从库开启 GIPK ,源库上创建无主键表,从库上并不会主动为该表创建主键。

逻辑备份

大多数实例会进行逻辑备份,如果 开启GIPK 模式时,MySQL 8.0.30 版本的 mysqldump 提供的 --skip-generated-invisible-primary-key 选项会忽略 GIPK 信息。简单来说,mysqldump 时 不带该参数,逻辑导出的数据会包含隐式主键,如果带上该参数,则不带隐式主键。

图片

图片

限制

  1. 只支持 InnoDB 存储引擎。
  2. 支持 row 模式复制,不支持 statement 模式复制。
  3. my_row_id 成为系统关键字。

参考文档

https://mp.weixin.qq.com/s/72MmKRIxdz3zkwjdu4_lEw

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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

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

    头像

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    18 − 6 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部