MySQL · 专家投稿 · InnoDB物理行中null值的存储的推断与验证

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 前言想写这边文章,是因为之前想写一个解析innodb ibd文件的工具,在写这个工具的过程中,发现逻辑记录转物理记录的转换中,最难的有两部分,一是每行每字段null值占用的字节和存储,二是变长字段占用的字节和存储的格式。本文中重点针对第一种情况。之前看有关介绍compact行记录格式: 变长字段之后的第二个部分是NULL标志位,该位指示了该行数据中是否有NULL值,有则用1表示。该部分

前言

想写这边文章,是因为之前想写一个解析innodb ibd文件的工具,在写这个工具的过程中,发现逻辑记录转物理记录的转换中,最难的有两部分,一是每行每字段null值占用的字节和存储,二是变长字段占用的字节和存储的格式。本文中重点针对第一种情况。
之前看有关介绍compact行记录格式:

变长字段之后的第二个部分是NULL标志位,该位指示了该行数据中是否有NULL值,有则用1表示。该部分所占字节为1字节
—–《InnoDB存储引擎》

之后便思考是否不管有多少个列都是NULL,该部分都只占1个字节呢?
便有了如下测试

本文约定

逻辑记录:record (元组)
物理记录:row(行)
只讨论compact行格式

所用工具

自己python写的工具innodb_extract

测试数据

表结构

localhost.test>desc null_test;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| name             | varchar(20)  | YES  |     | NULL    |                | 
| legalname        | varchar(25)  | YES  |     | NULL    |                | 
| industry         | varchar(10)  | YES  |     | NULL    |                | 
| province         | varchar(10)  | YES  |     | NULL    |                | 
| city             | varchar(15)  | YES  |     | NULL    |                | 
| size             | varchar(15)  | YES  |     | NULL    |                | 
| admin_department | varchar(128) | YES  |     | NULL    |                | 
+------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)


表内数据

+----+------+-----------+----------+----------+------+------+------------------+
| id | name | legalname | industry | province | city | size | admin_department |
+----+------+-----------+----------+----------+------+------+------------------+
|  1 | NULL | NULL      | NULL     | NULL     | NULL | NULL | NULL             | 
|  2 | TOM  | NULL      | NULL     | NULL     | NULL | NULL | NULL             | 
|  3 | ALEX | NULL      | NULL     | NULL     | NULL | NULL | HR               | 
+----+------+-----------+----------+----------+------+------+------------------+
3 rows in set (0.00 sec)

分析数据

通过工具看三行数据

#  python innodb_extract.py null_test.ibd
infimum
7f 000010001c 8000000000000001 0000f1e27b17 b5000001680084
1          
7e 0000180020 8000000000000002 0000f1e27b17 b5000001680094 544f4d

2   TOM       
3e 000020ffb6 8000000000000003 0000f1e27b17 b50000016800a4 414c4558 4852

3   ALEX      HR 

第一行:
null标志位:0x7f (01111111)
说明:从右向左方向写,一共7个null值
record header:000010001c
Transaction Id:0000f1e27b17
Roll Pointer:b5000001680084
数据:

第二行:
null标志位:0x7e (01111110)
说明:除第二列,其余均是null值
record header:0000180020
Transaction Id:0000f1e27b17
Roll Pointer:b5000001680084
数据:
第二列:544f4d => TOM

第三行:
null标志位:0x3e (00111110)
说明:除了第2列和第8列,其余均是null值
record header:000020ffb6
Transaction Id:0000f1e27b17
Roll Pointer:b5000001680084
数据:
第二列:414c4558 => ALEX
第八列:4852 => HR

假设

继续上面,如果包含Null值的字段是8个,或者9个会是怎样?

深度剖析

代码片段,该函数将物理记录转化为逻辑记录,版本5.5.31,源文件rem0rec.c,

rec_convert_dtuple_to_rec_comp(
/*===========================*/
	rec_t*			rec,	/*!< in: origin of record */
	const dict_index_t*	index,	/*!< in: record descriptor */
	const dfield_t*		fields,	/*!< in: array of data fields */
	ulint			n_fields,/*!< in: number of data fields */
	ulint			status,	/*!< in: status bits of the record */
	ibool			temp)	/*!< in: whether to use the
					format for temporary files in
					index creation */
{
	const dfield_t*	field;
	const dtype_t*	type;
	byte*		end;
	byte*		nulls;
	byte*		lens;
	ulint		len;
	ulint		i;
	ulint		n_node_ptr_field;
	ulint		fixed_len;
	ulint		null_mask	= 1;
	ut_ad(temp || dict_table_is_comp(index->table));
	ut_ad(n_fields > 0);

	if (temp) {
		ut_ad(status == REC_STATUS_ORDINARY);
		ut_ad(n_fields <= dict_index_get_n_fields(index));
		n_node_ptr_field = ULINT_UNDEFINED;
		nulls = rec - 1;
		if (dict_table_is_comp(index->table)) {
			/* No need to do adjust fixed_len=0. We only
			need to adjust it for ROW_FORMAT=REDUNDANT. */
			temp = FALSE;
		}
	} else {
		nulls = rec - (REC_N_NEW_EXTRA_BYTES + 1);

		switch (UNIV_EXPECT(status, REC_STATUS_ORDINARY)) {
		case REC_STATUS_ORDINARY:
			ut_ad(n_fields <= dict_index_get_n_fields(index));
			n_node_ptr_field = ULINT_UNDEFINED;
			break;
		case REC_STATUS_NODE_PTR:
			ut_ad(n_fields
			      == dict_index_get_n_unique_in_tree(index) + 1);
			n_node_ptr_field = n_fields - 1;
			break;
		case REC_STATUS_INFIMUM:
		case REC_STATUS_SUPREMUM:
			ut_ad(n_fields == 1);
			n_node_ptr_field = ULINT_UNDEFINED;
			break;
		default:
			ut_error;
			return;
		}
	}

	end = rec;
	lens = nulls - UT_BITS_IN_BYTES(index->n_nullable);
	/* clear the SQL-null flags */
	memset(lens + 1, 0, nulls - lens);
	
	

结合COMPACT row格式来看:

row记录格式如下:

|---------------------extra_size-----------------------------------------|---------fields_data------------|
|--columns_lens---|---null lens----|------fixed_extrasize(5)-------------|--col1---|---col2---|---col2----|
|end<--------begin|end<-------beign|-------------------------------------|orgin---------------------------|

  • 先看nulls = rec - (REC_N_NEW_EXTRA_BYTES + 1)
    rec为记录开始的offset,也就是,extrasize也就是固定长度的record header的长度。注意null标志位和变长字段长度列表是从右->左的方向写的(原因可参见下部分代码)。所以nulls指向的是null lens后一字节开始的位置。
  • 再看lens = nulls - UT_BITS_IN_BYTES(index->n_nullable)
    index->n_nullable指的是表结构中定义can be null的字段的个数,一个字段用一个bit来标记,UT_BITS_IN_BYTES将占用bit数转为占用的字节数。所以lens指向的是column_lens后面一个字节的位置,即跳过了Null标志的占用的空间,同样在写入值的时候也是从后面向前面写。
  • memset(lens + 1, 0, nulls - lens) 将nulls空间清零。

之后就是遍历每一个字段,先对定义了can be null字段进行处理

/* Store the data and the offsets */

	for (i = 0, field = fields; i < n_fields; i++, field++) {
		const dict_field_t*	ifield;

		type = dfield_get_type(field);
		len = dfield_get_len(field);

		if (UNIV_UNLIKELY(i == n_node_ptr_field)) {
			ut_ad(dtype_get_prtype(type) & DATA_NOT_NULL);
			ut_ad(len == REC_NODE_PTR_SIZE);
			memcpy(end, dfield_get_data(field), len);
			end += REC_NODE_PTR_SIZE;
			break;
		}

		if (!(dtype_get_prtype(type) & DATA_NOT_NULL)) {
			/* nullable field */
			ut_ad(index->n_nullable > 0);

			if (UNIV_UNLIKELY(!(byte) null_mask)) {
				nulls--;
				null_mask = 1;
			}
			
			

因为方向是从右向左写,也就是从后往前写,如果该字段为null,则将null标志位设为1并向前移1位,如果满了8个,也就是有8个字段都为null则offset向左移1位,并将null_mask置为1

从这段代码看出之前的猜想,也就是并不是Null标志位只固定占用1个字节==,而是以8为单位,满8个null字段就多1个字节,不满8个也占用1个字节,高位用0补齐

			ut_ad(*nulls < null_mask);

			/* set the null flag if necessary */
			if (dfield_is_null(field)) {
				*nulls |= null_mask;
				null_mask <<= 1;
				continue;
			}

			null_mask <<= 1;
		}
		

这段代码是就是设置null字段与null标志位的映射关系,如果字段为null,则设置标志位为1。

栗子验证

翻过来再看之前的例子,我们逐步的添加字段并设置default null看下null标志位的变化

  • step 1,添加两个并设置default null
localhost.test>alter table null_test add column `kind` varchar(15) DEFAULT NULL after `size`;
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

localhost.test>alter table null_test add column licenseno varchar(15) DEFAULT NULL after `kind`;
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0.11

那么理论来讲,第一行数据有9个null列了。满8个null列之后,继续向左写移,写1个bit之后开始占据两个字节。我们通过工具解析之后看下

#  python innodb_extract.py null_test.ibd
01ff 000010001d 8000000000000001 0000f1e27c81 980000028c0084
1            
01fe 0000180021 8000000000000002 0000f1e27c81 980000028c0094 544f4d
2   TOM         
00fe 000020ffb3 8000000000000003 0000f1e27c81 980000028c00a4 414c455848
3   ALEX        HR 

第一行null标志位变为0x01ff,即00000001 11111111一共有9个null字段,满了8位之后,继续向前占1个字节从右往左继续写
同理,第二行0x01fe,即00000001 11111110
第三行0x00fe,00000000 11111110

再继续添加8个字段并设置default null

localhost.test>desc null_test;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| name             | varchar(20)  | YES  |     | NULL    |                | 
| legalname        | varchar(25)  | YES  |     | NULL    |                | 
| industry         | varchar(10)  | YES  |     | NULL    |                | 
| province         | varchar(10)  | YES  |     | NULL    |                | 
| city             | varchar(15)  | YES  |     | NULL    |                | 
| size             | varchar(15)  | YES  |     | NULL    |                | 
| kind             | varchar(15)  | YES  |     | NULL    |                | 
| licenseno        | varchar(15)  | YES  |     | NULL    |                | 
| admin_department | varchar(128) | YES  |     | NULL    |                | 
| null_col1        | varchar(15)  | YES  |     | NULL    |                | 
| null_col2        | varchar(15)  | YES  |     | NULL    |                | 
| null_col3        | varchar(15)  | YES  |     | NULL    |                | 
| null_col4        | varchar(15)  | YES  |     | NULL    |                | 
| null_col5        | varchar(15)  | YES  |     | NULL    |                | 
| null_col6        | varchar(15)  | YES  |     | NULL    |                | 
| null_col7        | varchar(15)  | YES  |     | NULL    |                | 
| null_col8        | varchar(15)  | YES  |     | NULL    |                | 
+------------------+--------------+------+-----+---------+----------------+
18 rows in set (0.00 sec)


最多Null字段的第一行目前有个17个null字段,对应17个Null bit

root@hebe211 ibd]#  python innodb_extract.py null_test.ibd

01ffff 000010001e 8000000000000001 0000f1e27cce c60000017600840301fffe0000
1                    
01fffe 0000180022 8000000000000002 0000f1e27cce c6000001760094 544f4d
2   TOM                 
01fefe 000020ffb0 8000000000000003 0000f1e27cce c60000017600a4 414c45 5848
3   ALEX        HR         

第一行null标志位变为0x01ff,即00000001 11111111 11111111 一共有17个null字段,满了两个8位之后,继续向前占1个字节从右往左继续写
同理,第二行0x01fe,即00000001 11111111 11111110
第三行0x00fe,00000001 11111110 11111110

结论

允许null的字段需要额外的空间来保存字段Null到null标志位映射的对应关系,所以保存这个映射关系的null标志位长度并不是固定的。也就是null字段越多并不是越省空间。实际生产环境中应尽量减少can be null的字段。

作者介绍
赵晨@微博研发中心, 微博:@fiona514

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
13天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
120 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
13天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
13天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的段、区和页
MySQL的InnoDB存储引擎逻辑存储结构与Oracle相似,包括表空间、段、区和页。表空间由段和页组成,段包括数据段、索引段等。区是1MB的连续空间,页是16KB的最小物理存储单位。InnoDB是面向行的存储引擎,每个页最多可存放7992行记录。
|
13天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
【赵渝强老师】MySQL的InnoDB存储引擎
|
1月前
|
关系型数据库 MySQL 数据库
vertx 的http服务表单提交与mysql验证
本文介绍了如何使用Vert.x处理HTTP服务中的表单提交,并通过集成MySQL数据库进行验证,包括项目依赖配置、表单HTML代码和完整的Vert.x服务代码。
19 2
|
1月前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
125 0
|
3月前
|
监控 关系型数据库 MySQL
在Linux中,mysql的innodb如何定位锁问题?
在Linux中,mysql的innodb如何定位锁问题?
|
3月前
|
SQL 存储 关系型数据库
"MySQL增列必锁表?揭秘InnoDB在线DDL,让你的数据库操作飞一般,性能无忧!"
【8月更文挑战第11天】在数据库领域,MySQL凭借其稳定高效的表现深受开发者喜爱。对于是否会在给数据表添加列时锁表的问题,MySQL的行为受版本、存储引擎等因素影响。从5.6版起,InnoDB支持在线DDL,可在改动表结构时保持表的可访问性,避免长时间锁表。而MyISAM等则需锁表完成操作。例如,在使用InnoDB的表上运行`ALTER TABLE users ADD COLUMN email VARCHAR(255);`时,通常不会完全锁表。虽然在线DDL提高了灵活性,但复杂操作或大表变更仍可能暂时影响性能。因此,进行结构变更前应评估其影响并择机执行。
73 6
|
3月前
|
SQL 关系型数据库 MySQL
在 MySQL 中使用 IS NULL
【8月更文挑战第12天】
646 0
在 MySQL 中使用 IS NULL
|
3月前
|
SQL 关系型数据库 MySQL
mysql不等于<>取特定值反向条件的时候字段有null值或空值读取不到数据
对于数据库开发的专业人士来说,理解NULL的特性并知道如何正确地在查询中处理它们是非常重要的。以上所介绍的技巧和实例可以帮助你更精准地执行数据库查询,并确保数据的完整性和准确性。在编写代码和设计数据库结构时,牢记这些细节将有助于你避免许多常见的错误,提高数据库应用的质量与性能。
125 0

相关产品

  • 云数据库 RDS MySQL 版
  • 下一篇
    无影云桌面