SQL SELECT列表中,使用*和不使用*的索引使用方式

 寫寫如果SELECT列表中,使用*和不使用*的索引使用方式，如果錯了，希望各位改正。</p><p>例子以Northwind.dbo.Orders表為例，因為對這個表比較熟悉。<br />先建立出樣本資料庫：<br />CREATE DATABASE Test;<br />GO<br />USE Test<br />GO<br />--將Northwind.dbo.Orders表的資料導到我們的測試資料庫當中.<br />SELECT * INTO dbo.Orders FROM Northwind.dbo.Orders;<br />GO</p><p>--現在為Test.dbo.Orders表添加幾個索引<br />--建立OrderID為索引值的叢集索引<br />CREATE UNIQUE CLUSTERED INDEX cidx_OrderID ON dbo.Orders(OrderID);<br />--建立CustomerID,EmployeeID複合的非叢集索引<br />CREATE INDEX idx_CustomerID_EmployeeID ON dbo.Orders(CustomerID,EmployeeID);<br />--建立OrderDate為索引值的非叢集索引,並包含ShipVia,Freight列<br />CREATE INDEX idx_OrderDate ON dbo.Orders(OrderDate) INCLUDE(ShipVia,Freight);<br />--建立ShippedDate為索引值的非叢集索引<br />CREATE INDEX idx_ShippedDate ON dbo.Orders(ShippedDate);</p><p>/*<br />table_name index_name index_id type_desc<br />-------------------- -------------------- ----------- ------------------------------------------------------------<br />Orders cidx_OrderID 1 CLUSTERED<br />Orders idx_CustomerID_Emplo 2 NONCLUSTERED<br />Orders idx_OrderDate 3 NONCLUSTERED<br />Orders idx_ShippedDate 4 NONCLUSTERED</p><p>(4 row(s) affected)<br />*/<br />--叢集索引的index_id固定為1的，而非叢集索引的index_id從2到249之間，<br />--而沒有叢集索引時，有一個index_id為0，index_name為null的記錄。</p><p>/*<br />我們說，叢集索引和非叢集索引的主要區別是分葉層級存放些什麼。叢集索引在存放索引值，<br />還會存放所有的資料。而非叢集索引除了存放索引值，還會存一個bookmark，<br />而bookmark是rid還是叢集索引鍵看錶是否是堆表。</p><p>因為叢集索引在分葉層級中存放所有的資料，所以它會覆蓋表中所有的列。<br />而非叢集索引則不能覆蓋表中所有的列。所以要清楚非叢集索引覆蓋哪些列，這個很重要。<br />*/</p><p>CREATE INDEX idx_CustomerID_EmployeeID ON dbo.Orders(CustomerID,EmployeeID);<br />/*<br />idx_CustomerID_EmployeeID索引覆蓋了CustomerID、EmployeeID和OrderID。<br />我們說，非叢集索引除了存放索引值外，還會存一個bookmark，<br />因為OrderID是叢集索引的索引值，所以非叢集索引會以OrderID作為bookmark存放。<br />*/</p><p>CREATE INDEX idx_OrderDate ON dbo.Orders(OrderDate) INCLUDE(ShipVia,Freight);<br />/*<br />Idx_OrderDate索引覆蓋了OrderDate,ShipVia,Freight，OrderID四個列的資料，<br />而ShipVia,Freight僅存放在分葉層級中，不影響非叢集索引鍵在索引當中的位置。<br />*/</p><p>CREATE INDEX idx_ShippedDate ON dbo.Orders(ShippedDate);<br />--Idx_ShippedDate索引則覆蓋了ShippedDate和OrderID</p><p>--樣本一:<br />SELECT * FROM dbo.Orders;<br />/*<br />在這個查詢中,SELECT 使用了*,也就是要返回所有列的資料,我們知道,要返回全部的所有資料,<br />我們只要在叢集索引中逐頁去掃描,就能得到所有的資料.<br />所以它的執行計畫是:<br />StmtText<br />-------------------------------------------------------------------------<br /> |--Clustered Index Scan(OBJECT:([Test].[dbo].[Orders].[cidx_OrderID]))<br />*/</p><p>--樣本二:<br />SELECT * FROM dbo.Orders ORDER BY ShippedDate;<br />/*<br />在這個樣本當中.加上ORDER BY ShippedDate,我們知道ORDER BY 會得益於索引,<br />而ShippedDate列上剛好有一個索引,而這時間,會不會在idx_ShippedDate上作Index Scan呢?<br />答案是不會的.在預設情況下,ORDER BY會使用叢集索引掃描Clustered Index Scan,<br />然後再加一個Sort運算子去排序.<br />除非是ORDER BY中列的索引覆蓋了SELECT列表中的列<br />所以樣本二的執行計畫是:<br />StmtText<br />------------------------------------------------------------------------------<br /> |--Sort(ORDER BY:([Test].[dbo].[Orders].[ShippedDate] ASC))<br /> |--Clustered Index Scan(OBJECT:([Test].[dbo].[Orders].[cidx_OrderID]))<br />*/</p><p>樣本三:<br />SELECT OrderID,ShippedDate FROM dbo.Orders ORDER BY ShippedDate;<br />/*<br />這個樣本中,ORDER BY 中的ShippedDate列中的idx_ShippedDate索引,<br />正好覆蓋了SELECT列表中的OrderID和ShippedDate,<br />所以可以在idx_ShippedDate中作一個Index Scan就能得到以ShippedDate排序的資料<br />所以樣本三的執行計畫是:<br />StmtText<br />-----------------------------------------------------------------------------------<br /> |--Index Scan(OBJECT:([Test].[dbo].[Orders].[idx_ShippedDate]), ORDERED FORWARD)<br />*/</p><p>--樣本四:<br />SELECT OrderID,OrderDate,ShipVia,Freight<br />FROM dbo.Orders<br />ORDER BY OrderDate,ShipVia;</p><p>/*<br />OrderDate列中有一個索引,它覆蓋了OrderDate,ShipVia,Freight,OrderID<br />上面的SELECT當中,正好是這個索引覆蓋的列<br />那這個查詢會不會在idx_OrderDate上作一個Index Scan呢?<br />答案是不會的.因為在ORDER BY 當中,沒有OrderDate和ShipVia複合的索引,<br />所以無法確定OrderDate和ShipVia組合的順序.<br />但是它的列表當中.在idx_OrderDate索引中已經它們的資料了.<br />所以會在idx_OrderDate索引上作一個Index Scan,再加一個Sort排序<br />執行計畫為:<br />StmtText<br />-------------------------------------------------------------------------------------------------<br /> |--Sort(ORDER BY:([Test].[dbo].[Orders].[OrderDate] ASC, [Test].[dbo].[Orders].[ShipVia] ASC))<br /> |--Index Scan(OBJECT:([Test].[dbo].[Orders].[idx_OrderDate]))<br />*/</p><p>--樣本五<br />SELECT OrderID,CustomerID,EmployeeID<br />FROM dbo.Orders ORDER BY CustomerID,EmployeeID<br />/*<br />這個查詢中.ORDER BY中的兩個列正好有一個idx_CustomerID_EmployeeID的覆合索引,<br />而SELECT列表中的列,索引idx_CustomerID_EmployeeID也能覆蓋掉它.<br />所以這個查詢只需要在idx_CustomerID_EmployeeID索引上作一個Index Scan即可<br />執行計畫為:<br />StmtText<br />---------------------------------------------------------------------------------------------<br /> |--Index Scan(OBJECT:([Test].[dbo].[Orders].[idx_CustomerID_EmployeeID]), ORDERED FORWARD)<br />*/</p><p> 來自梁哥的文章