探秘空值位图掩码(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/

posted @   Woodytu  阅读(1675)  评论(1编辑  收藏  举报
编辑推荐:
· 聊一聊坑人的 C# MySql.Data SDK
· 使用 .NET Core 实现一个自定义日志记录器
· [杂谈]如何选择:Session 还是 JWT?
· 硬盘空间消失之谜:Linux 服务器存储排查与优化全过程
· JavaScript是按顺序执行的吗?聊聊JavaScript中的变量提升
阅读排行:
· 好消息,在 Visual Studio 中可以免费使用 GitHub Copilot 了!
· 聊一聊坑人的 C# MySql.Data SDK
· 没事别想不开去创业!
· Winform 使用WebView2 开发现代应用
· Java 项目愚蠢的分层及解决办法
点击右上角即可分享
微信分享提示