日期:2014-05-18 浏览次数:20571 次
写写如果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 --------