索引 - 索引键的最大大小

小咪咪 2021-06-26 16:06 372阅读 0赞

在设计包含许多键列或大型列的索引时, 计算索引键的大小以确保不超过最大索引键的大小. `SQL Server` 为所有索引键列的最大总大小保留 900 字节. 这排除了在非聚集索引的定义中包括的非键列.

## 计算索引键的大小 ##

若要计算索引键的大小, 请按下列步骤执行操作。

1.  显示索引将依据的表列的属性, 可以使用 sys.columns 目录视图执行此操作.
2.  求索引键中将定义的每一列的长度之和.  
    例如, 以下语句聚合 `Person.Address` 表中指定列的 `sys.columns` 视图索引的 `max_length` 列.

USE AdventureWorks2008R2;
    GO
    
    SELECT SUM(max_length) AS TotalIndexKeySize
    FROM sys.columns
    WHERE name IN(N'AddressLine1', N'AddressLine2', N'City', N'StateProvinceID', N'PostalCode')
    AND object_id = OBJECT_ID(N'Person.Address');

> 如果表列为 `Unicode` 数据类型（例如, `nchar` 或 `nvarchar`）, 则所显示的列长度是该列的存储长度. 这是 `CREATE TABLE`语句中指定的字符数的两倍. 在前面的示例中, `City` 定义为 `nvarchar(30)` 数据类型. 因此, 该列的存储长度为60.

1.  如果总长度小于 `900` 字节, 则该列可以作为索引键列. 如果总长度超过 900 字节, 则查看有关选项的以下信息和其他注意事项.

CREATE INDEX 语句使用下列算法来计算索引键大小 :

*  如果所有固定键列的大小加上 `CREATE INDEX` 语句中指定的所有可变键列的最大大小小于 `900` 字节, 则 `CREATE INDEX`语句将成功完成, 而不出现警告或错误.
 *  如果所有固定键列的大小加上所有可变键列的最大大小超过 `900` 字节, 而所有固定键列的大小加上所有可变键列的最小大小小于 `900` 字节,则 `CREATE INDEX` 语句将成功执行, 并显示一条警告, 指示如果后续 `INSERT` 或 `UPDATE`语句指定的值所生成的键值大于 `900` 字节, 则该语句可能会失败. 若表中现有数据行的值所生成的键值大于 `900` 字节, `CREATE INDEX` 语句将失败. 若后续 `INSERT` 或 `UPDATE` 语句指定的数据值所生成的键值大于 `900` 字节,该语句将失败.
 *  如果所有固定键列的大小加上 `CREATE INDEX` 语句中指定的所有可变列的最小大小超过 `900` 字节, `CREATE INDEX`语句将失败.

下表总结了创建达到或超过最大索引键大小限制的索引的结果.

<table> 
 <thead> 
  <tr> 
   <th align="left">可变长度列的最小大小 + 固定数据列的大小</th> 
   <th align="left">可变长度列的最大大小 + 固定数据列的大小</th> 
   <th align="left">现有行的索引键列长度总和的最大值*</th> 
   <th align="left">是否创建索引</th> 
   <th align="left">消息类型</th> 
   <th align="left">由于索引键值过大导致 INSERT 或 UPDATE 运行时错误</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">&gt; 900 字节</td> 
   <td align="left">不相关</td> 
   <td align="left">不相关</td> 
   <td align="left">否</td> 
   <td align="left">错误</td> 
   <td align="left">索引没有生成错误。</td> 
  </tr> 
  <tr> 
   <td align="left">&lt;= 900 字节</td> 
   <td align="left">&lt;= 900 字节</td> 
   <td align="left">不相关</td> 
   <td align="left">是</td> 
   <td align="left">无</td> 
   <td align="left">否。</td> 
  </tr> 
  <tr> 
   <td align="left">&lt;= 900 字节</td> 
   <td align="left">&gt; 900 字节</td> 
   <td align="left">&lt;= 900 字节</td> 
   <td align="left">是</td> 
   <td align="left">警告</td> 
   <td align="left">仅当所有索引列的当前长度之和大于 900 字节时。</td> 
  </tr> 
  <tr> 
   <td align="left">&lt;= 900 字节</td> 
   <td align="left">&gt; 900 字节</td> 
   <td align="left">&gt; 900 字节</td> 
   <td align="left">否</td> 
   <td align="left">错误</td> 
   <td align="left">索引没有生成错误。</td> 
  </tr> 
 </tbody> 
</table>

在执行 `CREATE INDEX` 语句时, 表中的任何行都不能有总长度超过 `900` 字节的索引键值.

## 使用包含列以避免大小限制 ##

可以将非键列包含在非聚集索引中来避免 `16` 键列的最大当前索引大小限制和 `900` 字节的最大索引键大小. `SQL Server` 数据库引擎在计算索引键列数或索引键列的总大小时不考虑非键列. 在具有包含列的非聚集索引中, 索引键列的总大小限制在 900 字节. 所有非键列的总大小只受 `INCLUDE` 子句中所指定列的大小限制. 例如, `varchar(max)` 列限制为 `2 GB`. `INCLUDE` 子句中的列可以为除 `text`, `ntext` 和 `image` 以外的所有数据类型.

> 注意: 在对表进行分区时, 如果分区键列尚未出现在非唯一聚集索引中, 则它们将会由数据库引擎添加到索引中. 索引列合并后的大小（不将包含列计算在内）加上任何添加的分区列在非唯一聚集索引中不能超过 1800 字节.