写个东西

liangCK 2009-08-28 05:25:59

加精

写写如果SELECT列表中,使用*和不使用*的索引使用情况，如果错了，希望各位改正。



例子以Northwind.dbo.Orders表为例，因为对这个表比较熟悉。

先创建出示例数据库：

CREATE DATABASE Test;

GO

USE Test

GO

--将Northwind.dbo.Orders表的数据导到我们的测试数据库当中.

SELECT * INTO dbo.Orders FROM Northwind.dbo.Orders;

GO



--现在为Test.dbo.Orders表添加几个索引

--建立OrderID为键值的聚集索引

CREATE UNIQUE CLUSTERED INDEX cidx_OrderID ON dbo.Orders(OrderID);

--建立CustomerID,EmployeeID复合的非聚集索引

CREATE INDEX idx_CustomerID_EmployeeID ON dbo.Orders(CustomerID,EmployeeID);

--建立OrderDate为键值的非聚集索引,并包含ShipVia,Freight列

CREATE INDEX idx_OrderDate ON dbo.Orders(OrderDate) INCLUDE(ShipVia,Freight);

--建立ShippedDate为键值的非聚集索引

CREATE INDEX idx_ShippedDate ON dbo.Orders(ShippedDate);



/*

table_name           index_name           index_id    type_desc

-------------------- -------------------- ----------- ------------------------------------------------------------

Orders               cidx_OrderID         1           CLUSTERED

Orders               idx_CustomerID_Emplo 2           NONCLUSTERED

Orders               idx_OrderDate        3           NONCLUSTERED

Orders               idx_ShippedDate      4           NONCLUSTERED



(4 row(s) affected)

*/

--聚集索引的index_id固定为1的，而非聚集索引的index_id从2到249之间，

--而没有聚集索引时，有一个index_id为0，index_name为null的记录。



/*

我们说，聚集索引和非聚集索引的主要区别是叶级别存放些什么。聚集索引在存放键值，

还会存放所有的数据。而非聚集索引除了存放键值，还会存一个bookmark，

而bookmark是rid还是聚集索引键看表是否是堆表。



因为聚集索引在叶级别中存放所有的数据，所以它会覆盖表中所有的列。

而非聚集索引则不能覆盖表中所有的列。所以要清楚非聚集索引覆盖哪些列，这个很重要。

*/



CREATE INDEX idx_CustomerID_EmployeeID ON dbo.Orders(CustomerID,EmployeeID);

/*

idx_CustomerID_EmployeeID索引覆盖了CustomerID、EmployeeID和OrderID。

我们说，非聚集索引除了存放键值外，还会存一个bookmark，

因为OrderID是聚集索引的键值，所以非聚集索引会以OrderID作为bookmark存放。

*/



CREATE INDEX idx_OrderDate ON dbo.Orders(OrderDate) INCLUDE(ShipVia,Freight);

/*

Idx_OrderDate索引覆盖了OrderDate,ShipVia,Freight，OrderID四个列的数据，

而ShipVia,Freight仅存放在叶级别中，不影响非聚集索引键在索引当中的位置。

*/



CREATE INDEX idx_ShippedDate ON dbo.Orders(ShippedDate);

--Idx_ShippedDate索引则覆盖了ShippedDate和OrderID





--示例一:

SELECT * FROM dbo.Orders;

/*

在这个查询中,SELECT 使用了*,也就是要返回所有列的数据,我们知道,要返回全部的所有数据,

我们只要在聚集索引中逐页去扫描,就能得到所有的数据.

所以它的执行计划是:

StmtText

-------------------------------------------------------------------------

  |--Clustered Index Scan(OBJECT:([Test].[dbo].[Orders].[cidx_OrderID])) 

*/



--示例二:

SELECT * FROM dbo.Orders ORDER BY ShippedDate;

/*

在这个示例当中.加上ORDER BY ShippedDate,我们知道ORDER BY 会得益于索引,

而ShippedDate列上刚好有一个索引,而这时间,会不会在idx_ShippedDate上作Index Scan呢?

答案是不会的.在默认情况下,ORDER BY不会使用该列的非聚集索引.

除非是ORDER BY中列的索引覆盖了SELECT列表中的列

所以示例二的执行计划是:

StmtText

------------------------------------------------------------------------------

  |--Sort(ORDER BY:([Test].[dbo].[Orders].[ShippedDate] ASC))

       |--Clustered Index Scan(OBJECT:([Test].[dbo].[Orders].[cidx_OrderID]))

*/



示例三:

SELECT OrderID,ShippedDate FROM dbo.Orders ORDER BY ShippedDate;

/*

这个示例中,ORDER BY 中的ShippedDate列中的idx_ShippedDate索引,

正好覆盖了SELECT列表中的OrderID和ShippedDate,

所以可以在idx_ShippedDate中作一个Index Scan就能得到以ShippedDate排序的数据

所以示例三的执行计划是:

StmtText

-----------------------------------------------------------------------------------

  |--Index Scan(OBJECT:([Test].[dbo].[Orders].[idx_ShippedDate]), ORDERED FORWARD)

*/



--示例四:

SELECT OrderID,OrderDate,ShipVia,Freight

FROM dbo.Orders 

ORDER BY OrderDate,ShipVia;



/*

OrderDate列中有一个索引,它覆盖了OrderDate,ShipVia,Freight,OrderID

上面的SELECT当中,正好是这个索引覆盖的列

那这个查询会不会在idx_OrderDate上作一个Index Scan呢?

答案是不会的.因为在ORDER BY 当中,没有OrderDate和ShipVia复合的索引,

所以无法确定OrderDate和ShipVia组合的顺序.

但是它的列表当中.在idx_OrderDate索引中已经它们的数据了.

所以会在idx_OrderDate索引上作一个Index Scan,再加一个Sort排序

执行计划为:

StmtText

-------------------------------------------------------------------------------------------------

  |--Sort(ORDER BY:([Test].[dbo].[Orders].[OrderDate] ASC, [Test].[dbo].[Orders].[ShipVia] ASC))

       |--Index Scan(OBJECT:([Test].[dbo].[Orders].[idx_OrderDate]))

*/



--示例五

SELECT OrderID,CustomerID,EmployeeID 

FROM dbo.Orders ORDER BY CustomerID,EmployeeID

/*

这个查询中.ORDER BY中的两个列正好有一个idx_CustomerID_EmployeeID的覆合索引,

而SELECT列表中的列,索引idx_CustomerID_EmployeeID也能覆盖掉它.

所以这个查询只需要在idx_CustomerID_EmployeeID索引上作一个Index Scan即可

执行计划为:

StmtText

---------------------------------------------------------------------------------------------

  |--Index Scan(OBJECT:([Test].[dbo].[Orders].[idx_CustomerID_EmployeeID]), ORDERED FORWARD)

*/