SQL Server 非聚集索引(如何预先估算其大小空间).pdfVIP

SQL Server 非聚集索引（如何预先估算其大小空间） 假如有一张大表，现在需要增加一个非聚集索引，对于DBA来说，要有预估其大小以及执 行时间的估算能力，尤其对一些企业使用SSD硬盘，其硬盘空间很是宝贵，增加索引如果 错误预估其大小，很有可能导致硬盘资源超出预期使用量，造成没必要的麻烦，这里只针对 其预估硬盘占用空间展开讨论，行为标准 8060 in_row_data,不涉及行溢出，大对象等情 况。 举个例子给大家 create table Index_test (id int,a char(10)) go insert into Index_test select 100,aaaaa go 4000 create nonclustered index ix_id_a on Index_test (id,a) go 如果认为索引行的单行大小为 4 + 10 = 14 字节，那么最后的计算结果应该是 索引占用了（4000/ （8096/14））*8192/1024 = 48KB。实际呢？ 索引占用了（14+1）*8192/1024 = 120KB 与预期的值相差了 2 倍以上，设想一下，如果你的某张大表的索引错误的预测为 50G，实 际则会占用 100G 以上。 网络上很多资料给出了非聚集索引的存储格式，这里我简单明了的说明下非聚集索引的内部 结构和预测其大小的方法。： （非聚集索引分为根叶，中间级页面，叶级页面，实际在物理存储上，我们可以将根叶和 中间级页面合并看成中间级页面，因为两者的存储格式是相同的。我后面的讨论也都是分为 叶级页面和中间级页面） 非聚集索引叶级页面单行：存储格式 1. 状态位（1 字节），标识此行是否有变长，空值，以及此行是否为索引行等等 2. 非聚集索引（定长列）键值大小 3. 如果是堆表，则是 Rowid （8 字节），如果是聚集索引，则是聚集索引键值（定长 部分）大小。 4. 包行列（定长列）长度 5. 索引列数（2 字节） 6. Null 位（1 字节）用来标识哪列值为 null 7. 变长列数量（2 字节） 8. 变长列 1 偏移长度（2 字节）+变长列 2 偏移长度（2 字节）.. 变长列 n 偏移长度 （2 字节） 9. 包行列（变长列）长度 2 字节 10. 非聚集索引（变长列）键值大小 + 聚集索引（变长列）键值大小 +包行列（变长 列）键值大小 11. 行偏移量（2 字节） 非聚集索引中间级页面单行：存储格式 1. 状态位（1 字节），标识此行是否有变长，空值，以及此行是否为索引行等等 2. 非聚集索引（定长列）键值大小 3. 如果是堆表，则是 Rowid （8 字节），如果是聚集索引，则是聚集索引键值（定长 部分）大小。 4. 非聚集索引键值所在的 pageid （4 字节） + 非聚集索引键值所在的页面的文件 i d(2 字节) 5. 索引列数（2 字节） 6. Null 位（1 字节）用来标识哪列值为 null 7. 变长列数量（2 字节） 8. 变长列 1 偏移长度（2 字节）+变长列 2 偏移长度（2 字节）.. 变长列 n 偏移长度 （2 字节） 9. 包行列（变长列）长度 10. 非聚集索引（变长列）键值大小 + 聚集索引（变长列）键值大小 +包行列（变长 列）键值大小 11. 行偏移量（2 字节） 下面总结了几条规律，方便理解上面的结构： 1. 如果索引涉及的列有一个允许 null，则索引行会包含索引列数（2 字节）和 Null 位 （1 字节） 2. 如果索引包含聚集索引，则需将 rowid 替换为聚集索引键值 3. 如果索引包含唯一约束，则中间层页面不会包含 rowid 或者聚集索引键值 4. 如果聚集索引不是唯一