SQLServer索引的四個進階特性

SQLServer索引的四個進階特性

 

一、Index Building Filter(索引建立時過濾)
    

        有一些索引非常低效的，比如經常查詢狀態為進行中的訂單，訂單有99%的狀態是完成，1%是進行中 ，因此我們在訂單狀態欄位上建了一個索引，效能是提高了，但是感覺索引中儲存了99%的完成狀態資料是永遠不會查詢到的，很浪費空間。如果我們的索引在建立的時間就不儲存完成狀態的資料，那不是更好。 Index Building Filter就是用來解決這個問題。

SQLServer
支援，文法樣本：

create index idx_3 on order(status) where status=’running’

MySQL:不支援

Oracle:不支援，可以考慮用分區解決

二、Index Include Column(索引包含列)

我們經常需要建一些複合式索引，一般有兩種原因：
1.通過複合式索引可以提高索引過濾度
    比如訂單表有會員ID和訂單日期2個欄位，如果我們經常要按會員和訂單時間查詢，

Select * from order where member_id=? and order_date between ? and ?

那建立會員ID+訂單日期的索引很合適。

create index idx_1 on order(member_id,order_date);

2.索引覆蓋讀取
    比如我們需要讀取一個會員訂單的訂單ID+狀態列表，SQL如下：

select order_id,status from order where member_id=?

如果我們的索引中只有member_id欄位，那麼還需要回表查詢order_id和status資料才能返回結果，如果建一個member_id+order_id+status的複合式索引:

create index idx_2 on order(member_id,order_id,status);

 

那隻要訪問索引就可以返回資料了，這種雖然效能提高了，但是由於索引多了欄位，因此增加了索引建立成本和索引空間。

SQLServer

        SQLServer除了支援複合式索引外，還支援Index Include Column特性，Index Include Column是複合式索引的一種變種，它的特點是可以指定複合式索引中哪些列是排序列，哪些列只是把內容儲存在索引中，這個特性不僅可以滿足索引覆蓋讀取，而且可以減少索引對DML的效能影響。文法如：

create index idx_2 on order(member_id) include(order_id,status);

其中member_id欄位是普通索引列，order_id和status列是內容include列。

普通複合式索引資料存放區結構樣本:
 
Include Column複合式索引資料存放區結構樣本:
 

SQLServer管理器的SQL最佳化自動索引推薦就經常看到推薦Include Column方式。

MySQL:不支援，只能用複合式索引代替
Oracle:不支援，只能用複合式索引代替

三、叢集索引(Cluster Index)

資料庫通常用兩種儲存方式，一種是堆表，即表中的資料是基本無序的，像往一個房間(資料區塊)堆箱子(記錄)一樣，只要有空間就往裡面放，放滿了就準備一個新房間再放。

另外一種就是聚集儲存，資料按表中一個或幾個欄位排序儲存，如所示。

 

由於要排序，需要索引來保證效率，所以聚集儲存和叢集索引儲存通常指一個意思。

SQLServer
        如果表沒有主鍵預設為堆表，如果有主鍵預設為按主鍵聚集儲存。SQLServer支援非主鍵索引聚集儲存，這個特性非常有意義，比如訂單表有訂單ID(主鍵)和會員ID，如果按訂單ID聚集儲存，由於訂單ID一般都是隨機訪問，返回單條記錄，所以對按訂單ID查詢沒有什麼效能提高。假設需按會員ID查詢，一個會員有許多訂單，分頁一次返回20條，那就需要20次離散資料訪問。
如果可以按會員ID聚集儲存，那用會員ID查詢可能只需要1次離散資料訪問就可以，效能可以提升很多，這種方式對訂單插入有一些效能影響，如果訂單插入不多，按會員查詢頻繁，那按會員ID建叢集索引給用訂單ID聚集效果很好。
MySQL
       MySQL MYISAM儲存引擎只支援堆儲存，不支援叢集索引。
       MySQL INNODB儲存引擎只能按主鍵聚集，如果沒有主鍵就用一個內部隱藏主鍵代替。

Oracle
       Oracle預設是堆儲存，如果建成索引組織表則按主鍵聚集儲存。Oracle還有一個種更進階的聚集儲存，概念叫簇(Cluster)，可以定義一個簇對象，然後將一個或多個表按欄位順序聚集的儲存在這個簇中，從而實現多個表聚集儲存，適用於一些主從表，如訂單與訂單明細，它們的資料是按關聯欄位聚集的儲存在一個資料區塊中，訂單與訂單明細經常一起查詢，所以這種邏輯只要讀取一次資料區塊即可，如果用非Cluster，那需要讀取多個資料區塊才OK。

 

四、VIEW INDEX(視圖索引)

在視圖上建索引，感覺沒有意義，因為視圖本身就是一個邏輯的概念，並不儲存物理資料，何來索引之說。

SQLServer
支援。視圖上建索引首先視圖需要綁定架構。視圖上需要先建一個唯的叢集索引，把資料持久化，持久化後還可以建其它新的索引，像普通表一樣處理了。

視圖上建索引可以讓資料持久化，一般有兩種用途
1. 統計類資料查詢效能最佳化
如經常要做select sum(amount) from t2這樣的操作，效能不好最佳化，並且t表資料變化不多，那麼可以建一個視圖(注意：需要加上with schemabinding選項):

CREATE VIEW V2with schemabindingASSELECT     SUM(amount) as sum_amount,COUNT_BIG (*) as cntFROM         t2

然後在這個視圖上建一個唯一叢集索引，資料就持久化了。

CREATE UNIQUE CLUSTERED INDEX idx_4 ON V2 (sum_amount)

然後我們用noexpand方式查詢v2索引檢視表，如下：

SELECT  sum_amount FROM  v2 WITH(NOEXPAND)

效能會非常好，因為視圖裡只有一行資料，直接讀取即可，不需要再從t2全表掃描匯總。

2.自動實現多維度聚集儲存
        資料庫的表一般只能設計為按一種方式聚集儲存(只允許有一個叢集索引)，但在實際業務中存在一些多個維度查詢，比如交易表，需要按買家維度查詢，也需要按賣家維度查詢。普通表只能選擇一種，如果要兩種維度效能都很好很難，有時只能人工的分為兩張表，一張表按買家聚集，一張表按賣家聚集，用程式或觸發器維護兩張表資料的一致性，這樣看起來很彆扭。採用視圖索引後可以在主表(買家維度資料表)上建個視圖，然後在視圖上用賣家維度建叢集索引，以後如果要按買家查詢則查詢主表，如果按賣家查詢才查詢索引檢視表。
這種效能最佳化方式只是一個方案設計，實踐中沒有經過驗證。

MySQL:不支援
Oracle:不支援，物化視圖可以起到類似的作用，並提供了更多的資料同步控制特性。

2013/3/25

我的新浪微博(http://weibo.com/yzsind)