sqlserver 索引对查找数据的影响

• 建立索引的主要目的，是按照预期的顺序排列数据，并且存储一部分冗余数据，用来加快数据检索速度
• 一张表 如果没有 clustered index，不管有没有非聚集索引，数据都会以随机的顺序存放。以 dbo.OrderDetail 为例：

IF OBJECT_ID('dbo.OrderDetail') IS NOT NULL
DROP TABLE dbo.OrderDetail
GO

CREATE TABLE dbo.OrderDetail (
    OrderID bigint,
    OrderDetailID bigint,
    UnitPrice numeric(18,2),
    OrderQty bigint,
    ProductID bigint,
    ModifiedDate datetime )
ON [PRIMARY]
GO

INSERT INTO dbo.OrderDetail(OrderID,OrderDetailID,UnitPrice) VALUES(75124,121318,1229.45)
INSERT INTO dbo.OrderDetail(OrderID,OrderDetailID,UnitPrice) VALUES(67315,96488,32.39)
INSERT INTO dbo.OrderDetail(OrderID,OrderDetailID,UnitPrice) VALUES(67217,101763,7.95)
INSERT INTO dbo.OrderDetail(OrderID,OrderDetailID,UnitPrice) VALUES(47011,14426,469.74)
GO

Table Scan

• 若要查找单价大于200的销售记录，运行语句为：

set statistics profile on
go

select OrderDetailID, UnitPrice 
from dbo.OrderDetail
where UnitPrice > 200

• 由于 UnitPrice 上没有索引，所以 SQL Server 不得不对该表从头到尾扫描一遍，把所有 UnitPrice > 200 的记录一个一个挑出来，执行计划为 Table Scan

解读：如果看到 Table Scan 就说明该表没有 clustered index

Clustered Index Scan

• 现在，在取值唯一的字段 OrderDetailID 上建立一个 Clustered Index，这样所有的数据都会按照聚集索引的顺序存储。

create clustered index OrderDetail_CL on dbo.OrderDetail (OrderDetailID)

• 再次查找单价大于200的销售记录，由于查询条件 UnitPrice 上没有索引，所以 SQL Server 还是要把所有记录都扫描一遍，但执行计划从 Table Scan 变成了 Clustered Index Scan

解读：因为在有 clustered index 的表格上，数据是直接存放在索引的最底层，所以要扫描整个表格里的数据，就需要把整个 clustered index 扫描一遍。在这里，Clustered Index Scan 就相当于一个 Table Scan，所要用的时间和资源和 Table Scan 没有什么差别，并不是说这里有了“Index” 就比 Table Scan 效率更高。

Index Seek

• 现在，在 UnitPrice 上建立一个 nonclustered index

解读：在 Nonclustered Index 里，会为每条记录存储一份 nonclustered index 索引键的值和一份 clustered index 索引键的值（在没有clustered index的表格里，是RID值）。所以在这里，每条记录都会有一份 UnitPrice 和 OrderDetailID 记录，按照 UnitPrice 的顺序存放。

create nonclustered index OrderDetail_NCL_UnitPrice on dbo.OrderDetail (UnitPrice)

• 再次查找单价大于200的销售记录，SQL Server 不用扫描整张表，直接找到了符合记录的值，执行计划为 Index Seek

KEY Lookup 书签查找

• 现在，如果增加了几个字段返回，SQL Server 就需要先在 nonclustered index 上找到所有 UnitPrice > 200 的记录，再根据 OrderDetailID 找到存储在 clustered index 上的详细数据，这个过程可以称为“KEY Lookup”即书签查找，这也是为什么不推荐 select * 的原因。

select OrderID, OrderDetailID, UnitPrice 
from dbo.OrderDetail with (index=OrderDetail_NCL_UnitPrice)
where UnitPrice > 200

• 在执行计划里，可以看到 SQL Server 先用 Index Seek 了 OrderDetail_NCL_UnitPrice，然后用 Clustered Index Seek 把需要的行找出来。这里的 Nested Loops 其实就是 KEY Lookup（堆表 叫 RID Lookup）

如何避免书签查找

• 为了避免书签查找，可以创建 include 索引，包含返回的所有字段即可（聚集索引字段不用加）

create nonclustered index OrderDetail_NCL_include
on dbo.OrderDetail (UnitPrice) include (OrderID)

• 再次运行上面的语句，执行计划为 Index Seek，使用了新的 include 索引，便没有了书签查找

select OrderID, OrderDetailID, UnitPrice 
from dbo.OrderDetail 
where UnitPrice > 200

总结

• 总结一下，在SQL Server里根据数据找寻目标的不同和方法不同，有下面几种情况。

结构	Scan	Seek
Heap堆 (没有聚集索引的表)	Table Scan	无
聚集索引	Clustered Index Scan	Clustered Index Seek
非聚集索引	Index Scan	Index Seek

注1：Table Scan 说明该表没有 clustered index，SQL正在扫描整张表。而 Clustered Index Scan 说明 SQL Server 正在扫描一张有 clustered index 的表格，但也是整表扫描，效率并高不到哪去。

注2：Clustered Index Seek 和 Index Seek 说明 SQL Server 正在利用索引结果检索目标数据。如果结果集只占表格总数据量的一小部分，Seek 会比 Scan 的效率提高很多。