探秘空值位图掩码（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 NULL11 )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 GO3 DBCC PAGE(ALLOCATIONDB, 1, 24993, 1)4 GO5 6 DBCC TRACEON(3604)7 GO8 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) NULL2 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 = 13 GO

当你查看表里第2条记录的数据页时，记录大小还是原来的4219 bytes。

1 DBCC TRACEON(3604)2 GO3 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 NULL13 )14 GO15 16 INSERT INTO TestTable VALUES17 (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 NULL2 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 GO11 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列，会出现严重的性能问题。