通过

估计非聚集索引的大小

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

按照下列步骤估计存储非聚集索引所需的空间大小：

  1. 计算步骤 2 和步骤 3 中所用变量的值。

  2. 计算用于存储非聚集索引的叶级中的索引信息的空间。

  3. 计算用于存储非聚集索引的非叶级中的索引信息的空间。

  4. 对计算出的值求和。

步骤 1. 计算用于步骤 2 和 3 的变量

可使用下列步骤计算所用变量的值以估计存储索引的较高级别所需的空间大小。

  1. 指定表中显示的行数：

    • Num_Rows = 表中的行数

  2. 指定索引键中固定长度和可变长度列的数量，并计算存储所需的空间：

    索引键列可以包括固定长度和可变长度列。 若要估计内部级别索引行的大小，请计算每组列在索引行中所占据的空间。 列的大小取决于数据类型和长度规定。

    • Num_Key_Cols = 总键列数（固定长度和可变长度）
    • Fixed_Key_Size = 所有固定长度键列的总字节大小
    • Num_Variable_Key_Cols = 可变长度键列数
    • Max_Var_Key_Size = 所有可变长度键列的最大字节大小

  3. 如果索引不是唯一的，对所需的数据行定位符说明如下：

    如果非聚集索引不是唯一的，数据行定位符将与非聚集索引键组合使用，以便为每一行生成唯一的键值。

    如果非聚集索引在堆上，则数据行定位符是堆 RID。 其大小是 8 个字节。

    • Num_Key_ColsNum_Key_Cols + 1 =
    • Num_Variable_Key_ColsNum_Variable_Key_Cols + 1 =
    • Max_Var_Key_SizeMax_Var_Key_Size + 8 =

    如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。 必须与非聚集索引键组合的列是那些在聚集键中但尚未包含在当前非聚集索引键列集合中的列。

    • Num_Key_ColsNum_Key_Cols + 不在非群集索引键列集中的群集键列数（如果群集索引不唯一，则 + 1） =

    • Fixed_Key_SizeFixed_Key_Size + 不在非群集索引键列集中的固定长度群集键列的总字节大小 =

    • Num_Variable_Key_ColsNum_Variable_Key_Cols + 不在非群集索引键列集中的可变长度群集键列数（如果群集索引不唯一，则 + 1） =

    • Max_Var_Key_SizeMax_Var_Key_Size + 不在非群集索引键列集中的可变长度群集键列的最大字节大小（如果群集索引不唯一，则 + 4） =

  4. 行的一部分可能会被保留以管理列的可空性，该部分称为 null 位图。 计算其大小：

    如果索引键中有可为 Null 的列（包括步骤 1.3 中所述的所有必要的聚集键列），则保留索引行的一部分，以用于 Null 位图。

    • Index_Null_Bitmap = 2 + ((索引行中的列数 + 7) / 8)

    仅应使用上述表达式中的整数部分， 而放弃所有余数。

    如果没有可为 Null 的键列，请将 Index_Null_Bitmap 设置为 0。

  5. 计算可变长度数据大小：

    如果索引键中有可变长度的列（包括所有必要的聚集索引键列），请确定存储索引行中的这些列需使用的空间：

    • Variable_Key_Size = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size

    添加到 Max_Var_Key_Size 的字节用于跟踪每个变量列。 此公式假设所有可变长度列均百分之百充满。 如果预计可变长度列占用的存储空间比例较低，则可以按照该比例调整 Max_Var_Key_Size 值，从而对整个表大小得出一个更准确的估计。

    如果没有可变长度列，请将 Variable_Key_Size 设置为 0。

  6. 计算索引行大小：

    • Index_Row_SizeFixed_Key_SizeVariable_Key_Size = Index_Null_Bitmap + 1（对应于索引行的行标题开销）+ 6（对应于子页 ID 指针） + +

  7. 计算每页索引行数（每页 8,096 个可用字节）：

    • Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

    由于索引行不跨页，因此每个页的索引行数应向下舍入到最接近的整行。 公式中的 2 是计算行数时引入的行大小余量。

步骤 2. 计算用于在叶级别存储索引信息的空间

可使用下列步骤估计存储叶级索引所需的空间大小。 需要步骤 1 中保留的值才能完成此步骤。

  1. 指定叶级的固定长度列和可变长度列的数量，并计算存储这些列所需的空间：

    您可以通过包含索引键列和非键列扩展非聚集索引。 这些额外的列只存储在叶级非聚集索引。 有关详细信息，请参阅 创建包含列的索引。

    Note

    你可以组合 varchar、 nvarchar、 varbinary或 sql_variant 列，使定义的表的总宽度超过 8,060 字节。 对于 varchar、 varbinary或 sql_variant 中的每一列，其长度不能超过 8,000 字节，对于 nvarchar 列，不能超过 4,000 字节。 但是，它们的组合宽度可能超过表中的 8,060 字节限制。 这也适用于具有包含列的非聚集索引叶行。

    如果非聚集索引没有任何包含的列，请使用步骤 1 中的值，包括步骤 1.3 中确定的任何修改：

    • Num_Leaf_Cols = Num_Key_Cols
    • 固定叶大小 = 固定键大小
    • Num_Variable_Leaf_Cols = Num_Variable_Key_Cols
    • Max_Var_Leaf_Size = Max_Var_Key_Size

    如果非聚集索引确实具有包含列，则对步骤 1 中的值加上适当的值（包括在步骤 1.3 中进行的任何修改）。 列的大小取决于数据类型和长度规定。 有关详细信息，请参阅数据类型。

    • Num_Leaf_ColsNum_Key_Cols + 包含的列数 =
    • Fixed_Leaf_SizeFixed_Key_Size + 固定长度包含列的总字节大小 =
    • Num_Variable_Leaf_ColsNum_Variable_Key_Cols + 可变长度包含列数 =
    • Max_Var_Leaf_SizeMax_Var_Key_Size + 可变长度包含列的最大字节大小 =

  2. 数据行定位符说明：

    如果非聚集索引不统一，则数据行定位符的开销已在步骤 1.3 中考虑，无需进一步修改。 转到下一步。

    如果非聚集索引是唯一的，则必须在叶级的所有行中说明数据行定位符。

    如果非聚集索引在堆上，则数据行定位符是堆 RID（大小为 8 字节）。

    • Num_Leaf_ColsNum_Leaf_Cols + 1 =
    • Num_Variable_Leaf_ColsNum_Variable_Leaf_Cols + 1 =
    • Max_Var_Leaf_SizeMax_Var_Leaf_Size + 8 =

    如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。 必须与非聚集索引键组合的列是那些在聚集键中但尚未包含在当前非聚集索引键列集合中的列。

    • Num_Leaf_ColsNum_Leaf_Cols + 不在非群集索引键列集中的群集键列数（如果群集索引不唯一，则 + 1） =

    • Fixed_Leaf_SizeFixed_Leaf_Size + 不在非群集索引键列集中的固定长度群集键列数 =

    • Num_Variable_Leaf_ColsNum_Variable_Leaf_Cols + 不在非群集索引键列集中的可变长度群集键列数（如果群集索引不唯一，则 + 1） =

    • Max_Var_Leaf_SizeMax_Var_Leaf_Size + 不在非群集索引键列集中的可变长度群集键列的字节大小（如果群集索引不唯一，则 + 4） =

  3. 计算 Null 位图大小：

    • Leaf_Null_Bitmap = 2 + ((Num_Leaf_Cols + 7) / 8)

    仅应使用上述表达式中的整数部分， 而放弃所有余数。

  4. 计算可变长度数据大小：

    如果存在可变长度的列（键列或包含列），包括在之前的步骤 2.2 中所述的所有必要的聚集索引键列，请确定存储索引行中的这些列所需的空间：

    • Variable_Leaf_Size = 2 + (Num_Variable_Leaf_Cols x 2) + Max_Var_Leaf_Size

    添加到 Max_Var_Key_Size 的字节用于跟踪每个变量列。 此公式假设所有可变长度列均百分之百充满。 如果预计可变长度列占用的存储空间比例较低，则可以按照该比例调整 Max_Var_Leaf_Size 值，从而对整个表大小得出一个更准确的估计。

    如果没有可变长度的列（键列或包含列），则将 Variable_Leaf_Size 设置为 0。

  5. 计算索引行大小：

    • Leaf_Row_SizeFixed_Leaf_SizeVariable_Leaf_Size = Leaf_Null_Bitmap + 1（用于索引行的行标题开销） + +

  6. 计算每页索引行数（每页 8,096 个可用字节）：

    • Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)

    由于索引行不跨页，因此每个页的索引行数应向下舍入到最接近的整行。 公式中的 2 是计算行数时引入的行大小余量。

  7. 根据指定的 填充因子 计算每页保留的空行数：

    • Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Leaf_Row_Size + 2)

    计算中使用的填充因子为整数值，而不是百分比。 由于行不跨页，因此每页的行数应向下取整，以达到最接近的完整行。 随着填充因子的增长，每个页面上存储的数据越多，页数更少。 公式中的 2 是计算行数时引入的行大小余量。

  8. 计算存储所有行所需的页数：

    • Num_Leaf_PagesNum_Rows / (Leaf_Rows_Per_PageFree_Rows_Per_Page) = -

    估计的页数应向上舍入到最接近的整数。

  9. 计算索引的大小（每页 8,192 个总字节）：

    • Leaf_Space_Used = 8192 x Num_Leaf_Pages

步骤 3. 计算用于在非叶级别中存储索引信息的空间

按照下列步骤估计存储索引的中间级和根级所需的空间大小。 需要步骤 2 和步骤 3 中保留的值才能完成此步骤。

  1. 计算索引中的非叶级数：

    • 非叶级别 = 1 + 日志（Index_Rows_Per_Page） （Num_Leaf_Pages / Index_Rows_Per_Page）

    将此值向上舍入到最接近的整数。 此值不包括非聚集索引的叶子节点级别。

  2. 计算索引中的非叶页数：

    • Num_Index_Pages = ∑Level （Num_Leaf_Pages/Index_Rows_Per_Page^级别），其中 1 <= 级别 = <

    将每个被加数向上舍入到最接近的整数。 由于是个简单示例，请考虑使用 Num_Leaf_Pages = 1000 和 Index_Rows_Per_Page = 25 的索引。 叶级别上方的第一个索引级别存储 1,000 个索引行，每个叶页有一个索引行，每个页可以容纳 25 个索引行。 这意味着需要 40 页来存储这 1,000 个索引行。 下一级索引必须存储 40 行。 这意味着需要 2 页。 最后一级索引必须存储 2 行。 这意味着需要 1 页。 这就产生了 43 个非叶索引页。 如果将这些数用到前面的公式中，结果如下：

    • Non-leaf_Levels = 1 + log(25) (1000 / 25) = 3

    • Num_Index_Pages = 1000/(25^3)+ 1000/(25^2) + 1000/(25^1) = 1 + 2 + 40 = 43，这是上面的示例中所述的页数。

  3. 计算索引的大小（每页 8,192 个总字节）：

    • Index_Space_Used = 8192 x Num_Index_Pages

步骤 4. 计算值总数

对从前面两个步骤中得到的值求和：

Nonclustered index size (bytes) = Leaf_Space_UsedIndex_Space_used +

此计算不考虑以下条件：

  • 分区：分区的空间开销最小，但计算起来很复杂。 包括这一点并不重要。

  • 分配页：至少有一个 IAM 页用于跟踪分配给堆的页面，但空间开销很小，没有算法可以确定地计算要使用的 IAM 页数。

  • 大型对象（LOB）值：用于确定用于存储 LOB 数据类型 varchar（max）、 varbinary（max）、 nvarchar（max）、 文本、 ntext、 xml 和 图像 值的确切空间的算法。 只需添加所需的 LOB 值的平均大小、乘 以Num_Rows，并将其添加到非聚集索引总大小即可。

  • 压缩：无法预计算压缩索引的大小。

  • 稀疏列：有关稀疏列的空间要求的信息，请参阅 “使用稀疏列”。

相关内容

  • Last updated on