眼见为实,来瞧瞧MySQL中的隐藏列
在介绍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 用尽后,之前的记录就会被覆盖。从这一角度也可以提醒大家,在建表时一定要创建主键,否则就有可能发生数据的覆盖。
芯片大缺货,何时是尽头?大家好,我是宫迅伟。上期,我和西部数据全球采购副总裁施若芾先生(以下统称施总)一起就大数据在采购方面的应用安全性问题进行了分析。这一期,我们将和大家聊聊大数据是否会取代采购岗位,以
小米MIX4明日亮相,优点缺点都存在小米数字系列的一次次崛起真正让用户体验到了普通的价格用到了顶尖旗舰手机的快感,也让用户了解到小米真正的实力。虽然今年一直宣传要冲击高端手机市场,但总是被用户嘲笑没有性价比了,和小米
比特币以后会涨到多少?价格与价值是有区别的。所以很多时候你要搞清楚决定一个物体价格背后的机制。货币是一个标准,他是一种衡量价格的体系,是一个以物易物的交易中间商。很多人喜欢说比特币的稀缺性,从而认定他一
拜登发布最激进产业新政,却不带马斯克一起玩我们没有回头路了。这句话,美国总统拜登(JoeBiden)入主白宫后一共在公开场合说了两遍,而有意思的是,这两次都和汽车产业的振兴有关。第一次,是在今年5月。拜登在福特汽车罗格电动
两家芯片巨头为何花200亿美元买两家自动驾驶公司?在8月5日,全球芯片巨头之一的高通,正式公告称已提出45。5亿美元的现金,并购瑞典自动驾驶公司Veoneer(维宁尔),这是继2017年英特尔153亿美元并购以色列自动驾驶公司Mo
realme可以啊!Q2可以用大丰收来形容看似第二季度已经结束,但在智能手机领域第二季度可以总结的事非常多。因为在这个季度全球智能手机的格局发生了巨大的变化,看似三星还排在第一位,但相对于第二名的小米优势并不大,苹果直接被
有句话说科学无国界,但其实在大国之间,科学是绝对有国界的有句话说科学无国界,但其实在大国之间,科学是绝对有国界的。因为科学是一个很强大的壁垒,若是科学技术不强,那么很多东西只能买过来,价格便会十分地高昂,甚至使用还要付高额的专利费。而华
TikTok将为青少年用户增加更多隐私保护同时限制推送通知在监管审查加强后,TikTok在其平台上推出了对未成年人的保护措施。该公司表示,它将为13至17岁的青少年用户推出一系列产品变化,旨在使他们的TikTok体验更私密更安全更少成瘾。
5G时代谁将为王?OPPO系全球榜单硕果累累,这几个战略布局很重要不得不说,国产5G手机在全球市场上的受欢迎程度要比我们想象中强得多,从知名市场调研机构StrategyAnalytics发布的最新安卓5G智能手机市场份额研究报告中,我们可以看到2
饭小圈开始内测觊觎已久的美团,或加速挺进外卖社交炣燃科技8月13日讯(杨洋)一直念念不忘社交的美团,终于准备杀进来了天眼查APP显示,北京三快科技有限公司于7月30日申请注册饭小圈商标,现国际分类为教育娱乐,商标状态为申请中。据
遇到顺丰丢件问题?看顺丰如何保障用户贴心体验如今,收发快递已成为人们日常生活中不可或缺的一部分,可与日俱增的,还有快递运输途中随时可能发生的丢件损件等问题。据国家邮政局的数据显示,2021年6月,用户对快递服务问题申诉189