每日一博 | 一起看看 MySQL 中的隐藏列-低调大师

每日一博 | 一起看看 MySQL 中的隐藏列

2022-04-21 537

摘要：在mysql的多版本并发控制 mvcc中，我们知道mysql中存在一些隐藏列，例如行标识、事务ID、回滚指针等，不知道大家是否和我一样好奇过，要怎样才能实际地看到这些隐藏列的值呢？

在mysql的多版本并发控制mvcc中，我们知道mysql中存在一些隐藏列，例如行标识、事务ID、回滚指针等，不知道大家是否和我一样好奇过，要怎样才能实际地看到这些隐藏列的值呢？

本文我们就来重点讨论一下诸多隐藏列中的行标识DB_ROW_ID，实际上，将行标识称为隐藏列并不准确，因为它并不是一个真实存在的列，DB_ROW_ID实际上是一个非空唯一列的别名。在拨开它的神秘面纱之前，我们看一下官方文档的说明：

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements

简单翻译一下，如果在表中存在主键或非空唯一索引，并且仅由一个整数类型的列构成，那么就可以使用SELECT语句直接查询_rowid，并且这个_rowid的值会引用该索引列的值。

着重看一下文档中提到的几个关键字，主键、唯一索引、非空、单独一列、数值类型，接下来我们就要从这些角度入手，探究一下神秘的隐藏字段_rowid。

1、存在主键

先看设置了主键且是数值类型的情况，使用下面的语句建表：

 CREATE TABLE `table1` (
  `id` bigint(20) NOT NULL PRIMARY KEY ,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB;

插入三条测试数据后，执行下面的查询语句，在select查询语句中直接查询_rowid：

 select *,_rowid from table1

查看执行结果，_rowid可以被正常查询：

可以看到在设置了主键，并且主键字段是数值类型的情况下，_rowid直接引用了主键字段的值。对于这种可以被select语句查询到的的情况，可以将其称为显式的rowid。

回顾一下前面提到的文档中的几个关键字，分别对其进行分析。由于主键必定是非空字段，下面来看一下主键是非数值类型字段的情况，建表如下：

CREATE TABLE `table2` (
  `id` varchar(20) NOT NULL PRIMARY KEY ,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB;

在table2执行上面相同的查询，结果报错无法查询_rowid，也就证明了如果主键字段是非数值类型，那么将无法直接查询_rowid。

2、无主键，存在唯一索引

上面对两种类型的主键进行了测试后，接下来我们看一下当表中没有主键、但存在唯一索引的情况。首先测试非空唯一索引加在数值类型字段的情况，建表如下：

 CREATE TABLE `table3` (
  `id` bigint(20) NOT NULL UNIQUE KEY,
  `name` varchar(32)
) ENGINE=InnoDB;

查询可以正常执行，并且_rowid引用了唯一索引所在列的值：

唯一索引与主键不同的是，唯一索引所在的字段可以为NULL。在上面的table3中，在唯一索引所在的列上添加了NOT NULL非空约束，如果我们把这个非空约束删除掉，还能显式地查询到_rowid吗？下面再创建一个表，不同是在唯一索引所在的列上，不添加非空约束：

 CREATE TABLE `table4` (
  `id` bigint(20) UNIQUE KEY,
  `name` varchar(32)
) ENGINE=InnoDB;

执行查询语句，在这种情况下，无法显式地查询到_rowid：

和主键类似的，我们再对唯一索引被加在非数值类型的字段的情况进行测试。下面在建表时将唯一索引添加在字符类型的字段上，并添加非空约束：

 CREATE TABLE `table5` (
  `id` bigint(20),
  `name` varchar(32) NOT NULL UNIQUE KEY
) ENGINE=InnoDB;

同样无法显示的查询到_rowid：

针对上面三种情况的测试结果，可以得出结论，当没有主键、但存在唯一索引的情况下，只有该唯一索引被添加在数值类型的字段上，且该字段添加了非空约束时，才能够显式地查询到_rowid，并且_rowid引用了这个唯一索引字段的值。

3、存在联合主键或联合唯一索引

在上面的测试中，我们都是将主键或唯一索引作用在单独的一列上，那么如果使用了联合主键或联合唯一索引时，结果会如何呢？还是先看一下官方文档中的说明：

_rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.

简单来说就是，如果主键存在、且仅由数值类型的一列构成，那么_rowid的值会引用主键。如果主键是由多列构成，那么_rowid将不可用。

根据这一描述，我们测试一下联合主键的情况，下面将两列数值类型字段作为联合主键建表：

CREATE TABLE `table6` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `name` varchar(32),
  PRIMARY KEY(`id`,`no`)
) ENGINE=InnoDB;

执行结果无法显示的查询到_rowid：

同样，这一理论也可以作用于唯一索引，如果非空唯一索引不是由单独一列构成，那么也无法直接查询得到_rowid。这一测试过程省略，有兴趣的小伙伴可以自己动手试试。

4、存在多个唯一索引

在mysql中，每张表只能存在一个主键，但是可以存在多个唯一索引。那么如果同时存在多个符合规则的唯一索引，会引用哪个作为_rowid的值呢？老规矩，还是看官方文档的解答：

Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

简单翻译一下，如果表中的第一个非空唯一索引仅由一个整数类型字段构成，那么_rowid会引用这个字段的值。否则，如果第一个非空唯一索引不满足这种情况，那么_rowid将不可用。

在下面的表中，创建两个都符合规则的唯一索引：

CREATE TABLE `table8_2` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `name` varchar(32),
  UNIQUE KEY(no),
  UNIQUE KEY(id)
) ENGINE=InnoDB;

看一下执行查询语句的结果：

可以看到_rowid的值与no这一列的值相同，证明了_rowid会严格地选取第一个创建的唯一索引作为它的引用。

那么，如果表中创建的第一个唯一索引不符合_rowid的引用规则，第二个唯一索引满足规则，这种情况下，_rowid可以被显示地查询吗？针对这种情况我们建表如下，表中的第一个索引是联合唯一索引，第二个索引才是单列的唯一索引情况，再来进行一下测试：

CREATE TABLE `table9` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `name` varchar(32),
  UNIQUE KEY `index1`(`id`,`no`),
  UNIQUE KEY `index2`(`id`)
) ENGINE=InnoDB;

进行查询，可以看到虽然存在一个单列的非空唯一索引，但是因为顺序选取的第一个不满足要求，因此仍然不能直接查询_rowid：

如果将上面创建唯一索引的语句顺序调换，那么将可以正常显式的查询到_rowid。

5、同时存在主键与唯一索引

从上面的例子中，可以看到唯一索引的定义顺序会决定将哪一个索引应用_rowid，那么当同时存在主键和唯一索引时，定义顺序会对其引用造成影响吗？

按照下面的语句创建两个表，只有创建主键和唯一索引的顺序不同：

 CREATE TABLE `table11` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY(no)
) ENGINE=InnoDB;

CREATE TABLE `table12` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  UNIQUE KEY(id),
  PRIMARY KEY(no)
) ENGINE=InnoDB;

查看运行结果：

可以得出结论，当同时存在符合条件的主键和唯一索引时，无论创建顺序如何，_rowid都会优先引用主键字段的值。

6、无符合条件的主键与唯一索引

上面，我们把能够直接通过select语句查询到的称为显式的_rowid，在其他情况下虽然_rowid不能被显式查询，但是它也是一直存在的，这种情况我们可以将其称为隐式的_rowid。

实际上，innoDB在没有默认主键的情况下会生成一个6字节长度的无符号数作为自动增长的_rowid，因此最大为2^48-1，到达最大值后会从0开始计算。下面，我们创建一个没有主键与唯一索引的表，在这张表的基础上，探究一下隐式的_rowid。

 CREATE TABLE `table10` (
  `id` bigint(20),
  `name` varchar(32)
) ENGINE=InnoDB;

首先，我们需要先查找到mysql的进程pid：

ps -ef | grep mysqld

可以看到，mysql的进程pid是2068：

在开始动手前，还需要做一点铺垫，在innoDB中其实维护了一个全局变量dictsys.row_id，没有定义主键的表都会共享使用这个row_id，在插入数据时会把这个全局row_id当作自己的主键，然后再将这个全局变量加 1。

接下来我们需要用到gdb调试的相关技术，gdb是一个在Linux下的调试工具，可以用来调试可执行文件。在服务器上，先通过yum install gdb安装，安装完成后，通过下面的gdb命令把 row_id 修改为 1：

gdb -p 2068 -ex 'p dict_sys->row_id=1' -batch

命令执行结果：

在空表中插入3行数据：

 INSERT INTO table10 VALUES (100000001, 'Hydra');
INSERT INTO table10 VALUES (100000002, 'Trunks');
INSERT INTO table10 VALUES (100000003, 'Susan');

查看表中的数据，此时对应的_rowid理论上是1~3：

然后通过gdb命令把row_id改为最大值2^48，此时已超过dictsys.row_id最大值：

gdb -p 2068 -ex 'p dict_sys->row_id=281474976710656' -batch

命令执行结果：

再向表中插入三条数据：

 INSERT INTO table10 VALUES (100000004, 'King');
INSERT INTO table10 VALUES (100000005, 'Queen');
INSERT INTO table10 VALUES (100000006, 'Jack');

查看表中的全部数据，可以看到第一次插入的三条数据中，有两条数据被覆盖了：

为什么会出现数据覆盖的情况呢，我们对这一结果进行分析。首先，在第一次插入数据前_rowid为1，插入的三条数据对应的_rowid为1、2、3。如下图所示：

当手动设置_rowid为最大值后，下一次插入数据时，插入的_rowid重新从0开始，因此第二次插入的三条数据的_rowid应该为0、1、2。这时准备被插入的数据如下所示：

当出现相同_rowid的情况下，新插入的数据会根据_rowid覆盖掉原有的数据，过程如图所示：

所以当表中的主键或唯一索引不满足我们前面提到的要求时，innoDB使用的隐式的_rowid是存在一定风险的，虽然说2^48这个值很大，但还是有可能被用尽的，当_rowid用尽后，之前的记录就会被覆盖。从这一角度也可以提醒大家，在建表时一定要创建主键，否则就有可能发生数据的覆盖。

本文基于mysql 5.7.31 进行测试
官方文档：https://dev.mysql.com/doc/refman/5.7/en/create-index.html

点击关注，第一时间了解华为云新鲜技术~

微信关注我们

原文链接：https://my.oschina.net/u/4526289/blog/5407325

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

QEMU 7.0 发布，正式支持 Intel AMX、大幅改进 RISC-V

QEMU 是一个免费开源的模拟器，它通过动态二进制翻译来模拟机器的处理器，并为机器提供一套不同的硬件和设备模型，使其能够运行各种客户操作系统。它可以与基于内核的虚拟机（KVM）互操作，以接近原生速度运行虚拟机。QEMU 还可以对用户级进程进行模拟，使为一种架构编译的应用程序能够在另一种架构上运行。 QEMU 7.0 版本正式推出，这个版本包含了来自 225 位贡献者共 2500 多次提交。更新内容包括： ACPI：支持通过 ACPI ERST 接口记录访客事件 virtiofs：改进的安全标签支持 ARM：'virt' board 支持 virtio-mem-pci、指定访客 CPU 拓扑结构，以及在使用 KVM/hvf 时启用 PAuth ARM："xlnx-versal-virt" board 支持 PMC SLCR 和模拟 OSPI 闪存控制器 HPPA：支持多达 16 个 vCPU、为 HP-UX VDE/CDE 环境改进图形驱动、设置 SCSI 启动顺序，以及其他一些新功能 OpenRISC：'sim' board 支持多达 4 个核心，加载外部 initrd 映像，并为启...

2022-04-22

811

wimlib 是一个开源的、跨平台的库，用于创建、提取和修改 Windows Imaging（WIM）档案。WIM 是一种文件归档格式，有点类似于 ZIP；但与 ZIP 不同的是，它允许存储各种 Windows 特有的元数据，允许在一个归档文件中存储多个 "镜像"，自动删除所有文件内容的重复数据，并支持可选的固态压缩以获得更好的压缩率。wimlib 及其命令行前端wimlib-imagex为 Microsoft 的WIMGAPI、ImageX和DISM提供了一个免费的跨平台替代方案。在 Windows 和类 UNIX 的系统（如 macOS 和 Linux）上提供快速和可靠的文件归档允许非 Windows 操作系统的用户读取和写入 Windows Imaging（WIM）文件支持在 Windows 文件系统（如 NTFS）上正确归档文件允许从非 Windows 操作系统部署 Windows 操作系统为微软使用的几种压缩格式提供独立的、高质量的开源压缩器和解压缩器 wimlib 以源码 tarball 或二进制文件的形式发布。该软件由一个 C 语言库和 wimlib-image...

2022-04-22

3133

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。