探秘空值位图掩码(NULL bitmap mask)
这篇文章我想谈下空值位图掩码(NULL bitmap mask) ,并揭开它的神秘面纱。空值位图掩码是在存储引擎层为列是否存储NULL值进行编码。它是一个简单的位图掩码,如果值是1的话,表示这列有NULL值,如果是0的话,表示这列有具体的值(非NULL值)。
这样的解释听起来非常直接,但当我们进一步分析时,用这个方法还是有启发性的。首先我们来看看数据行的组合结构,这是存储引擎用来在磁盘上存储记录的结构。结构如下图所示:
这个格式被称为定长变量格式(FixedVar format),因为SQL Server总是先存储定长列(像INT,CHAR),再存储变长列(像VARCHAR)。从图中我们可以看到,SQL Server以存储2 bytes的状态位开始,接着用2 bytes存储由行头到定长列结尾长度(包含所有定长列数据)。然后用2 bytes存储列个数,紧随其他的就是真正的空值位图掩码(NULL bitmap mask)。
所有的一切第一眼看起来都很合理,但我们再仔细看下的话,我们就开始思考,并且你可能会问:为什么SQL Server在每条数据行里存储具体的列数?对于每条数据行,列数都不是一样的么?为什么SQL Server要存储这些冗余的数据?
第1个答案是非常简单和有逻辑性的:SQL Server需要列数来计算用作实际空值位图掩码(NULL bitmap mask)的字节数。小于等于8列的表需要1 byte,9到16列需要 2 bytes,17到24列需要3 bytes,以此类推。明白了么?但在表里每条记录的列数必须是一样的!
我们来看第2个用实例分析的技术性正确答案:首先,你要知道空值位图掩码(NULL bitmap mask)是用在数据引擎级别,即当前记录中的列数。这就是说SQL Server在物理行可以存储不同数量的列。额,好像说的有点含糊不清…………物理数据行列数和表元数据层(sys.dolumns)里列数并不一致。这些也是SQL Server内部的真正不同层级。
因此在什么情况下这些层级间会彼此不相等呢?很简单:当你往表里增加列的时候!如果你加的列是NULL还是NOT NULL,SQL Server会作出完全不同的区别。当你增加一个新的NULL列到表时,SQL Server只更新表元数据层,一点也不接触到存储引擎层。也就是说当你增加一个NULL列时,所有的记录物理存储上不发生任何改变。另一方面,当你增加一个NOT NULL列时,SQL Server会更新表元数据层,同时也会更新存储引擎层,这就是说,SQL Server会接触并重写表里的每一条记录,在那里你增加了一个NOT NULL列。这会带来性能上的巨大区别!因此SQL Server需要在每条数据记录里存储具体的列数,因为这里的列数不能和表元数据层的列数同步。
我们来拿具体的例子来详细分析下。这个例子我们创建了一个简单的含8列的表,SQL Server需要使用1 bytes来作为空值位图掩码(NULL bitmap mask)。
1 CREATE TABLE TestTable 2 ( 3 Column1 INT IDENTITY(1, 1) NOT NULL, 4 Column2 CHAR(600) NOT NULL, 5 Column3 CHAR(600) NOT NULL, 6 Column4 CHAR(600) NOT NULL, 7 Column5 CHAR(600) NOT NULL, 8 Column6 VARCHAR(600) NOT NULL, 9 Column7 VARCHAR(600) NOT NULL, 10 Column8 VARCHAR(600) NOT NULL 11 ) 12 GO
然后,我们往表里插入2条记录:
1 INSERT INTO TestTable VALUES 2 ( 3 REPLICATE('2', 600), 4 REPLICATE('3', 600), 5 REPLICATE('4', 600), 6 REPLICATE('5', 600), 7 REPLICATE('6', 600), 8 REPLICATE('7', 600), 9 REPLICATE('8', 600) 10 ), 11 ( 12 REPLICATE('2', 600), 13 REPLICATE('3', 600), 14 REPLICATE('4', 600), 15 REPLICATE('5', 600), 16 REPLICATE('6', 600), 17 REPLICATE('7', 600), 18 REPLICATE('8', 600) 19 ) 20 GO
我们通过DBCC PAGE命令查看下具体的数据页:
1 DBCC IND(ALLOCATIONDB, TestTable, -1) 2 GO
1 DBCC TRACEON(3604) 2 GO 3 DBCC PAGE(ALLOCATIONDB, 1, 24993, 1) 4 GO 5 6 DBCC TRACEON(3604) 7 GO 8 DBCC PAGE(ALLOCATIONDB, 1, 24995, 1) 9 GO
可以看到,每条记录的长度是 4129 bytes(4204 bytes 数据+ 7 bytes 行开销+ 2 bytes 变长列个数 + 3 * 2 bytes 每个变长列结束位置的偏移量)。
现在我们往表里加一个新的NULL列:
1 ALTER TABLE TestTable ADD Column9 CHAR(600) NULL 2 GO
这是表里的第9列,也就是说SQL Server对于这个列数需要2 bytes。但是 SQL Server并不在存储引擎层改变物理数据行,因为我们只加了一个NULL列。SQL Server不需要在存储引擎层做任何处理。我们可以通过查看数据页来验证下:
记录还是同样4219 bytes的长度,但是我们逻辑上已经在表上加了1列。现在我们来更新表的1条记录,这样的话,新加列就有具体值了:
1 UPDATE TestTable SET Column9 = REPLICATE('9', 600) 2 WHERE Column1 = 1 3 GO
当你查看表里第2条记录的数据页时,记录大小还是原来的4219 bytes。
1 DBCC TRACEON(3604) 2 GO 3 DBCC PAGE(ALLOCATIONDB, 1, 24995, 1) 4 GO
你现在创建了一个场景:SQL Server在数据行内部存储了不同长度的空值位图掩码(NULL bitmap mask)。这就是说你有定长列的表,在存储引擎级别,却有不同长度的行大小!很有趣,是不是?
现在我们删除表并重建,继续往表里插入2条记录:
1 DROP TABLE dbo.TestTable 2 3 CREATE TABLE TestTable 4 ( 5 Column1 INT IDENTITY(1, 1) NOT NULL, 6 Column2 CHAR(600) NOT NULL, 7 Column3 CHAR(600) NOT NULL, 8 Column4 CHAR(600) NOT NULL, 9 Column5 CHAR(600) NOT NULL, 10 Column6 VARCHAR(600) NOT NULL, 11 Column7 VARCHAR(600) NOT NULL, 12 Column8 VARCHAR(600) NOT NULL 13 ) 14 GO 15 16 INSERT INTO TestTable VALUES 17 ( 18 REPLICATE('2', 600), 19 REPLICATE('3', 600), 20 REPLICATE('4', 600), 21 REPLICATE('5', 600), 22 REPLICATE('6', 600), 23 REPLICATE('7', 600), 24 REPLICATE('8', 600) 25 ), 26 ( 27 REPLICATE('2', 600), 28 REPLICATE('3', 600), 29 REPLICATE('4', 600), 30 REPLICATE('5', 600), 31 REPLICATE('6', 600), 32 REPLICATE('7', 600), 33 REPLICATE('8', 600) 34 ) 35 GO
现在我们往表里增加一个NOT NULL列:
1 ALTER TABLE TestTable ADD Column9 CHAR(600) NOT NULL 2 DEFAULT REPLICATE('9', 600) 3 GO
现在,SQL Server需要在存储引擎层改变每条记录,因为新列的默认值必须被增加(当你表里已经有记录存储时,新加列必须要定义一个默认值),而且SQL Server需要扩展空值位图掩码(NULL bitmap mask)。
1 DBCC IND(ALLOCATIONDB, TestTable, -1) 2 GO 3 4 DBCC TRACEON(3604) 5 GO 6 DBCC PAGE(ALLOCATIONDB, 1, 24993, 1) 7 GO 8 9 DBCC TRACEON(3604) 10 GO 11 DBCC PAGE(ALLOCATIONDB, 1, 24995, 1) 12 GO
当你处理大表,给表增加NOT NULL列时,这个现象会导致严重的性能问题。想象下我们往表里插入100万条记录。当我们增加NULL列时,SQL Server只需要几毫秒,因为只进行元数据修改操作。但当我们往表里增加NOT NULL列时,SQL Server待ALTER TABLE操作完成需要花费40秒!在处理大表,往表里增加NOT NULL列,这的确是个非常严重的性能降级!!
希望你现在已经理解了为什么SQL Server在存储引擎层对每条记录存储具体的列数,还有在SQL Server里,当你往大表里增加NOT NULL列,会出现严重的性能问题。
参考文章:
https://www.sqlpassion.at/archive/2011/06/29/the-mystery-of-the-null-bitmap-mask/
注:此文章为WoodyTu学习MS SQL技术,收集整理相关文档撰写,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,有了您的支持才能激发作者更大的写作热情,非常感谢!
【推荐】中国电信天翼云云端翼购节,2核2G云服务器一口价38元/年
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 聊一聊坑人的 C# MySql.Data SDK
· 使用 .NET Core 实现一个自定义日志记录器
· [杂谈]如何选择:Session 还是 JWT?
· 硬盘空间消失之谜:Linux 服务器存储排查与优化全过程
· JavaScript是按顺序执行的吗?聊聊JavaScript中的变量提升
· 好消息,在 Visual Studio 中可以免费使用 GitHub Copilot 了!
· 聊一聊坑人的 C# MySql.Data SDK
· 没事别想不开去创业!
· Winform 使用WebView2 开发现代应用
· Java 项目愚蠢的分层及解决办法