SQL Server 索引和表体系结构（非聚集索引）

时间：2023-03-08 15:25:36
SQL Server 索引和表体系结构（非聚集索引）

非聚集索引

概述

对于非聚集索引，涉及的信息要比聚集索引更多一些，由于整个篇幅比较大涉及接下来的要写的“包含列的索引”，“索引碎片”等一些知识点，可能要结合起来阅读理解起来要更容易一些。非聚集索引和聚集索引一样都是B-树结构，但是非聚集索引不改变数据的存储方式，所以一个表允许建多个非聚集索引；非聚集索引的叶层是由索引页而不是由数据页组成，索引行包含索引键值和指向表数据存储位置的行定位器，

既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

正文

  • 单个分区中的非聚集索引结构

SQL Server 索引和表体系结构（非聚集索引）

非聚集索引 Index_id>1 可以结合语句查询

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number,p.rows,

x.first_page,x.root_page,x.first_iam_page,x.filegroup_id,x.total_pages,x.used_pages

FROM sys.allocation_units AS au

    JOIN sys.partitions AS p ON au.container_id = p.partition_id

    JOIN sys.objects AS o ON p.object_id = o.object_id

    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id

    join sys.system_internals_allocation_units as x on au.container_id=x.container_id

ORDER BY o.name, p.index_id;

非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述：

  • 如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。

如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server
将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL
Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

  • 非聚集索引与聚集索引相比：

A）叶子结点并非数据结点
B）叶子结点为每一真正的数据行存储一个“键-指针”对
C）叶子结点中还存储了一个指针偏移量，根据页指针及指针偏移量可以定位到具体的数据行。
D）类似的，在除叶结点外的其它索引结点，存储的也是类似的内容，只不过它是指向下一级的索引页的。

聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

SQL Server 索引和表体系结构（非聚集索引）

注意：上图中的数据页是聚集索引或者堆数据行，而不是非聚集索引的数据页，在非聚集索引中不存在数据页，非聚集索引中的叶子层和根节点与中间节点有点不同，它的指针是指向数据行，且如果非聚集索引如果是包含列索引，那么包含列仅仅存储在叶级别，而键值可以存储在所有级别，这块会在接下来的包含列索引中讲述。

对于根与中间级的索引记录，它的结构包括：
A）索引字段值
B）RowId（即对应数据页的页指针+指针偏移量）。在高层的索引页中包含RowId是为了当索引允许重复值时，当更改数据时精确定位数据行。
C）下一级索引页的指针

对于叶子层的索引对象，它的结构包括：
A）索引字段值
B）RowId

由于索引建值存储在索引页中，所以检索单独的索引键值效率是很高的，因为不需要定位到数据页在索引页中就能找到数据，对于当个字段建索引非聚集索引所占的空间要小于聚集索引，因为非聚集索引不需要存储数据行，对于建全覆盖索引除外。

  • 非聚集索引列的选择
  1. 同样非聚集索引避免选择宽列，这点与聚集索引一样。
  2. 包含经常包含在查询的搜索条件（例如返回完全匹配的 WHERE 子句）中的列
  3. 经常作为JOIN 或 GROUP BY 子句
  4. 尽量避免使用组合列建索引，除非组合列在where中有使用，否则可以用包含列索引替代组合索引，选择组合字段做索引，组合字段的第一个字段选择很重要，第一个字段一定要经常被使用的字段，例如AB字段作为组合字段，当WHERE用A字段作为检索条件的时候，查询会使用索引查找；当你使用B作为WHERE的检索条件的时候，查询使用的是索引扫描，虽然我们不能绝对肯定查找的效率就一定比扫描要好，但是这也是告诉我们要合适的选择索引列，甚至的列之间的先后顺序。
  5. 大量非重复值，如姓氏和名字的组合（前提是聚集索引被用于其他列）。不要选择例如性别这种重复值多的列，这种情况表扫描比查找效率会更高，所以有时候当我们用查询计划分析时不一定扫描就一定比查找就要差，我们要根据实际情况去分析问题。
  6. 覆盖查询。
    当索引包含查询中的所有列时，性能可以提升。查询优化器可以找到索引内的所有列值；不会访问表或聚集索引数据，这样就减少了磁盘 I/O
    操作。使用具有包含列的索引来添加覆盖列，而不是创建宽索引键。有关详细信息，请参阅具有包含列的索引。
    如果表有聚集索引，则该聚集索引中定义的列将自动追加到表上每个非聚集索引的末端。这可以生成覆盖查询，而不用在非聚集索引定义中指定聚集索引列。例如，如果一个表在
    C 列上有聚集索引，则 B 和 A 列的非聚集索引将具有其自己的键值列 B、A 和
    C

世界上没有绝对完美的事情，索引也是一样，给我们带来查询效率的同时也会有弊端

  • 对表编制大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的调整

总结

   这篇文章更重要的是讲述索引的存储结构和查找方式，没有讲述索引的一些基本概念和语句的写法，网上有很多写的很好这方面的文章。希望写这篇文章能给大家带来帮助，文章中有一些内容是从别的作者哪里拷贝过来的，因为我觉得原作者（KissKnife）在这方面已经讲述的非常到位，所以借鉴了一下，同样如果文章中有讲述的不合理的地方还望大家提出。

备注：

作者：pursuer.chen

博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》

相关文章