主页 / dba / 问题 / 287535
Accepted
Caius Jard
Asked: 2021-03-24 06:18:02 +0800 CST

对于使用 WHERE 命中 NCI 和使用 ORDERBY 命中 CI 的查询，SQLServer 是否可以隐式组合聚集索引和非聚集索引？

  • 772

我最近问了一个关于必须按 GUID 顺序记录事件的表的最佳索引策略的问题。GUID 令人讨厌，但它是不可避免的，因为它是其他地方的 FK。

就我想做的查询而言，它们本质上是以下形式SELECT TOP N SomeColumn FROM t WHERE GuidColumn = 'someguid' ORDER BY AutoIncInt DESC

我做了一些实验，一个很好的权衡方法似乎是：

  • 在表上创建AutoIncInt列作为唯一的集群键
  • 在 上创建一个非唯一的非聚集索引GuidColumn，并且INCLUDE SomeColumn

起初我有非聚集索引SomeGuid ASC, AutoIncInt DESC，认为这将有助于ORDER BY AutoIncInt DESC查询的一部分，但当我删除它时计划保持不变 - 不需要排序操作，查询只是命中 NC 索引，做了一个顶部和一个选择；似乎 SQLServer 在某种程度上足够聪明，即使在非聚集索引中根本没有引用 AutoIncInt 时，也有一种方法可以轻松按顺序获取数据/轻松计算 TOP 需要哪些最后 N 行

这种机制在实践中如何运作？由于引用了聚簇索引，非聚簇索引是否具有隐式顺序，以便 SQLS 可以快速轻松地推断出我想要 SomeColumn 来自哪些 N 个索引节点？

sql-server azure-sql-database

1 个回答

  1. Best Answer

    SQL Server b-tree 索引中的每个叶节点行通过唯一键唯一标识表中的单个行。在非聚集索引中，此唯一键由声明的键列和行定位符组成。行定位器是聚集索引键（包括 CI 键值不唯一时的唯一符）。对于堆（表上没有聚集索引），行定位器是行的物理位置（文件、页、槽）。

    即使未指定DBCC PAGE，此演示脚本的输出也会显示GuidColumn和作为索引键：。AutoIncIntAutoIncInt

    DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (
      AutoIncInt int IDENTITY NOT NULL CONSTRAINT pk_t PRIMARY KEY CLUSTERED
    , GuidColumn uniqueidentifier NOT NULL
    , SomeColumn char(5000) NOT NULL
)
CREATE INDEX idx ON t(GuidColumn) INCLUDE (SomeColumn);
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','3')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','1')
INSERT INTO t (GuidColumn, SomeColumn) VALUES('00000000-0000-0000-0000-000000000001','2')

DECLARE @dbid int, @fileid int, @pageid int;
SELECT TOP (1) 
      @dbid = DB_ID()
    , @fileid = allocated_page_file_id
    , @pageid = allocated_page_page_id
FROM sys.dm_db_database_page_allocations( DB_ID(), OBJECT_ID(N'dbo.t','U'), 2, 1, 'DETAILED')
WHERE 
    page_type_DESC = 'INDEX_PAGE'
ORDER BY 
      allocated_page_file_id
    , allocated_page_page_id;

DBCC PAGE(@dbid,@fileid,@pageid,3);
GO

    结果：

    +--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
| FileId | PageId | Row | Level |           GuidColumn (key)           | AutoIncInt (key) | SomeColumn |  KeyHashValue  | Row Size |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+
|      1 |    424 |   0 |     0 | 00000000-0000-0000-0000-000000000001 |                1 |          3 | (63f9557c63d5) |     5038 |
+--------+--------+-----+-------+--------------------------------------+------------------+------------+----------------+----------+

    这种索引架构对您的查询具有积极的性能影响，因为该AutoIncInt列不仅作为行定位器隐式包含在非聚集索引中，而且还是唯一键的一部分，因此是有序的。这允许您的查询计划使用GuidColumn WHERE谓词的索引查找以及子句的向后扫描ORDER BY...DESC。

    下面是实际查询计划的摘录，显示了索引的向后扫描。AutoIncInt无论是否明确指定为键列，生成的非聚集索引都是相同的。

    <IndexScan Ordered="true" ScanDirection="BACKWARD"
    • 1

相关问题