Sql Server之旅——第九站 看公司這些DBA們設計的這些複合索引

一線碼農發表於2015-02-03
SQLServer索引

　　這一篇再說下索引的最後一個主題，索引覆蓋，當然學習比較好的捷徑是看看那些大師們設計的索引，看從中能提取些什麼營養的東西，下面我們看

看資料庫中一個核心的Orders表。

　　

一：檢視錶的架構

<1> 先檢視這個表的大概架構資訊

1 --檢視錶的架構資訊
2 SELECT c.column_id,c.name,t.name FROM sys.columns AS c 
3 JOIN sys.types t
4 ON c.system_type_id=t.system_type_id
5 WHERE c.object_id=object_id('O_Orders') 
6 ORDER BY c.column_id

 

從這個訂單表來看大概有89個欄位。。。還是蠻多的，可能有太多的歷史原因吧，下面就有一個疑問來了，針對這麼多的欄位加上五花八門的型別，如何規劃

好單列索引和複合索引。。。下面我們來看看這些專家們怎麼設計的。

 

<2> 複合索引

　　首先宣告一下，由於我的許可權有限，不能進行DBCC IND，PAGE等命令，所以我沒有能力判斷下面的索引是include索引還是複合索引，所以這裡統一叫成

複合索引吧。

1 SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders')

從上面可以看到，有9個非聚集索引，1個聚集索引，然後可以通過 SHOW_STATISTICS 抽查幾個索引看看到底關聯了哪些欄位，找到其中的二個索引，

覆蓋多達6列，如索引"idx_order_status_2","IX_O_OrdersUID"。

DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2)
DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID)

 

從這兩個索引中關聯的欄位大概可以看出兩點資訊：

①：這些欄位都比較小，為char(1)，smallint，bit這樣的，自然表示的狀態會比較少。

②：將表中多個狀態少的欄位挑選幾個按照訪問頻率組合在一起做一個索引。

 

但是仔細想想，雖然原則上說狀態少的欄位不合適建索引，但是類似“訂單狀態(OrderStatus”這種欄位，肯定是一個被頻繁查詢的列。。。既然是頻繁的列，

肯定就要想辦法優化，方法就是建複合索引，這樣在複雜的sql中更加容易被撞上索引覆蓋。

比如下面這樣：

1 SET STATISTICS IO ON 
2 SELECT OrderStatus, ProcessStatus, SendTicketCity, FlightAgency, Eticket, OrderID
3 FROM dbo.Orders WHERE OrderStatus='P' AND ProcessStatus='1' AND SendTicketCity=1

然後繼續挑選幾個索引瞄一瞄。。。一般來說，覆蓋1到2個列的索引都叫小索引。

1 DBCC SHOW_STATISTICS(O_Orders,idx_eid_orderdate)
2 DBCC SHOW_STATISTICS(O_Orders,IX_O_Order_FinishDate)

通過上面的索引大概可以看到，Eid和FinishDate這兩列，一眼掃過就知道應該是一個唯一性比較高的列了，至於為什麼要覆蓋2列，那這個就是根據業務

和生產的滾動資料來決定了，那這樣的索引有什麼好處呢？同樣更容易會撞到索引連結，也就是多條件中會走到多個索引，每個索引中貢獻一些列剛好可以

滿足select中的所有列。。。比如下面這樣。

1 -- 可以看到，select中的所有列都是有idx_eid_orderdate 和 IX_O_Order_FinishDate 貢獻
2 SELECT OrderID,FinishDate,PrepayType,Eid,OrderDate
3 FROM  dbo.O_Orders WHERE Eid='cctv1' AND FinishDate>2015-1-1

 

好了，就像園友說的，索引就是拆東牆補西牆，每建一個索引都需要評估它的利弊。

 

相關文章