續上一篇SQLServer查詢性能優化之創建合理的索引（上）
數據庫索引分為聚集索引和非聚集索引，聚集索引就是物理索引，也就是數據的物理的存儲順序，聚集索引的葉子節點就是數據行本身；非聚集索引是邏輯索引，也可以簡單的認為是對聚集索引建立的索引，一般來說聚集索引的鍵就是非聚集索引的葉子節點（在不使用include時）。
關于索引的選擇
對于索引類型來說沒什么好選的，一般來說聚集索引是必須的（有特殊需要的另說），非聚集索引看實際需要靈活建立。因此對于索引來說主要是決定在那些列上建立索引，尤其是對于聚集索引這點非常重要。
聚集索引
聚集索引作為最重要的索引往往被我們所忽略，而其最大的優勢就是大范圍數據查詢有著較高的效率，因此聚集索引列的選擇往往對數據庫性能有著灰常大的影響。為了盡量發揮聚集索引在大范圍數據查找上的優勢，推薦按以下順序選擇聚集索引列。
聚集索引字段選擇優先級：時間字段>>會進行大范圍查詢的列>>具有唯一值的有實際意義的字段>>自增列ID
1、時間字段：若表里面有時間列，并且時間是按照數據插入順序增長時（時間無需唯一即可有重復值，哪怕是大范圍重復），建議采用時間列作為聚集索引的第一選擇。理由：聚集索引有一個巨大的優勢就是進行大范圍數據查找，而且這個優勢會隨著數據量的增加而越來越明顯，一般來說我們需要進行大數據量范圍查詢時都會用時間列圍作為篩選條件，由于聚集索引不存在書簽查找而且可以進行連續掃描，因此查詢速度會非常快。時間列數據最好是順序插入的這樣可以盡量減少磁盤碎片，是數據存儲相對集中，便于連續數據讀取。
2、會進行大范圍查詢的列：若表里面沒有時間字段或者時間字段不適合做聚集索引，可以選擇那些在建表時就明確知道會經常進行大范圍數據篩選的列，而且最好是選擇性較低的列（即有較多重復值的列，性別這種列不算啦），如有必要可以使用組合索引。理由：聚集索引在數據查詢的優勢主要在于范圍數據查找，把聚集索引弄成唯一的把這個大好優勢給白白浪費了。
3、具有唯一值的有實際意義的字段：若找不到適合條件1、2的列，那還是乖乖的把聚集索引列建立在唯一列上吧，最好找那種有實際意義的具有唯一性的列，比如訂單表可以用訂單號作聚集索引，訂單明細表使用訂單號和產品編號做聯合聚集索引。理由：找不到合適的時間字段和較低選擇性字段的話，把主鍵建成聚集索引是我們大多情況下的選擇。
這里建議把唯一性的聚集索引順便建成主鍵，和編碼時方法、變量命名一樣，推薦列名自解釋，即看到列名就知道它就是主鍵，省得你再去猜，比如訂單表你來個自增ID列做主鍵，再建一個OrderCode列做訂單號，用這個表時你得懷疑這個OrderCode是不是唯一滴呢，有木有建立唯一約束呢，同理在訂單明細表來個自增列ID也會產生如此疑問，產生疑問還是小事，若是你忘記了在應該唯一的列上建立約束，沒準哪天程序控制不好給你個巨大的驚喜。
4、自增列ID：前面3中條件都找不到合適的列了還是使用我們的神器自增列ID吧，自增列ID也是我們使用最多的主鍵（順便也就成聚集索引了），而且能較好滿足我們大多數需求。自增ID列堪稱無所不能，int類型只占用4個字節完全滿足窄索引要求，絕對的順序存儲可以有效降低索引碎片，完全符合我們的見表習慣，有用沒用來個自增ID列做主鍵總是沒錯滴。
這里考慮聚集索引的鍵列主要為查詢考慮，有些觀點認為應該總是把聚集索引建立唯一列上，這里不敢茍同，誠然有些特殊情況下確實需要這么做，但大說情況下還是建立在選擇性較低的列、時間列上比較好，這樣才能發揮聚集索引在范圍數據查找方面的巨大優勢。關于聚集索引在列上重復數據SQL Server需要額外的建立唯一標示用以定位造成查詢時的額外開銷非常小，小到與其帶來范圍查找的優勢而言完全可以忽略。
當然了在選擇列時要盡量使用窄索引，也只是盡量而已，主要還是看付出的代價還獲得的收益，若有足夠的收益啥玩意都可以滴。記住我們滴目標是利用聚集索引提高大范圍查詢效率。
非聚集索引
與聚集索引不同，非聚集索引可以建立多個，這也給我們帶來了很大的靈活，畢竟聚集索引就那么一個不可能靠它滿足所有需求，更多的我們得依賴非聚集索引。記住非聚集索引不是大白菜，你想鍵多少就建多少，建立索引是有代價的，任何涉及到索引列的數據修改都會導致索引的修改，索引越多數據的曾、刪、改的額外代價也就越大。對于非聚集索引來說，我們的目標是用盡可能少的索引覆蓋盡可能多的查詢。
非聚集索引的列選擇順序（組合索引）：經常被使用為查詢條件列>>具有較高選擇性的列（選擇性越高越好，唯一最好）>>經常排序的列
1、經常被使用為查詢條件列：我們的查詢千變萬化，建立索引時要首先考慮有哪些列被經常性的用于各種查詢，把使用頻率較高的列作為組合索引的第一列（先導列），若一個查詢中沒有用到組合索引中的先導列，多數情況下這個索引就不會被使用，因此為了盡可能多的復用組合索引把使用較多的查詢列作為組合索引的第一列吧。（關于這點對于聚集索引的組合索引同樣適用）
2、具有較高選擇性的列：這點很簡單盡量使用高選擇性列作為先導列，如果可以通過第一個條件過濾（隨便什么判定邏輯=、>、<、like），只要能大幅減少數據范圍，就把它作為先導列。
3、條件1、2、3都確定不了時那就用經常被排序的列吧，我們的很多操作都需要先進行排序才可以進行進一步查詢，比如group by，like等操作都是要先進行排序操作才可以完成下一步查詢。
補充一點：可以把經常被返回的列放到索引的include里面去，在不增加索引鍵大小的情況下盡可能覆蓋盡可能多的列，這樣當遇到某些查詢，沒有用到組合索引的先導列，但又感覺不值得為其建立索引時，若此查詢用到得字段被組合索引實現了索引覆蓋，可以進行非聚集索引掃描完成查詢（當非聚集索引實現了索引覆蓋時，進行非聚集索引掃描有著比聚集索引掃描更好的效率）。
下面我們用一些示例來簡單說明下
CREATE TABLE Orders
(
ID INT IDENTITY PRIMARY KEY,--自增列ID做主鍵，這樣創建默認就成了聚集索引
OrderCode BIGINT NOT NULL,--訂單號
Price DECIMAL(18,2) NOT NULL,--訂單金額
UserID INT NOT NULL,--用戶ID
[Status] INT NOT NULL,--訂單狀態
PostTime DATETIME NOT NULL,--下單時間
Moblie CHAR(11) NOT NULL,--用戶手機號
[Address] NVARCHAR(200) NOT NULL--收獲地址
) |
創建一個訂單表，按照我們的習慣有用沒用來個自增列ID做主鍵，隨隨便便也就建立了聚集索引，現在我們來看一下，對于訂單表來我們一般都會一組規則生成訂單號，而不是簡單的使用自增ID，因此我們創建了OrderCode用作訂單號，當然了訂單號必須是唯一的，因此需要創建唯一約束，過了些日子有其它人用到訂單表或你自己用這個表，難免就會有些疑惑，OrderCode需要唯一，在這個表里到底是不是唯一的呢，于是乎你首先查看OrderCode上面是否建立了唯一約束，然后知道OrderCode就是唯一的，這也沒啥，但是來個人都要查一遍，過段時間忘了還得再確認一次，很是麻煩，再看看我們那個主鍵ID，他神馬都沒干，就在那里呆著，現在拿掉它，于是表變為
CREATE TABLE Orders
(
OrderCode BIGINT NOT NULL PRIMARY KEY,--訂單號
Price DECIMAL(18,2) NOT NULL,--訂單金額
UserID INT NOT NULL,--用戶ID
[Status] INT NOT NULL,--訂單狀態
PostTime DATETIME NOT NULL,--下單時間
Moblie CHAR(11) NOT NULL,--用戶手機號
[Address] NVARCHAR(200) NOT NULL--收獲地址
) |
現在不管誰、什么時候看到這個表基本上不會懷疑OrderCode是否唯一了，一個不起眼的小改進，帶來了很大的便利，所以主鍵自解釋很有必要的
我們看一下以下幾個可能經常用到的查詢
--查詢1：指定用戶特定時間內的所有訂單
SELECT * FROM dbo.Orders WHERE UserID=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查詢2：指定用戶的單個訂單
SELECT * FROM dbo.Orders WHERE UserID=1 AND OrderCode=22222222222
--查詢3：指定用戶特定時間內特定狀態的訂單
SELECT * FROM dbo.Orders WHERE UserID=1 AND Status=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查詢4：指定時間內所有的訂單
SELECT * FROM dbo.Orders WHERE PostTime BETWEEN '2012-6-1' AND '2012-6-30' AND Status=1 |
為了最優查詢速度有可能會創建以下索引
索引1：
CREATE INDEX IX_UserIDPostTime ON dbo.Orders(UserID,PostTime)
索引2：
CREATE INDEX IX_UserIDOrderCode ON dbo.Orders(UserID,OrderCode)
索引3：
CREATE INDEX IX_UserIDStatusPostTime ON dbo.Orders(UserID, Status,PostTime)
索引4：
CREATE INDEX IX_PostTimeStatus ON dbo.Orders(PostTime,Status) |
最悲觀的情況下上面4個索引可能同時存在，為每一個查詢建立對應的索引固然可行，但代價未免太大，別忘了索引不是大白菜。因此我們應盡可能的用少的索引覆蓋多的查詢。來看下上面的索引，如果只創建了索引1，那么只有查詢1、3能從索引1受益，查詢4沒用用到索引1的先導列故不會用到索引1，查詢2由于聚集索引存在根本不需要額外的非聚集索引。而索引2由于聚集索引的存在更是完全沒必要存在，因此首先干掉索引2。再看索引3，索引3可以覆蓋查詢1和查詢3、查詢4，但由于索引列順序問題使其在應對查詢4時基本無效，對查詢1雖然有效但效果不盡如人意，我們對索引3做下簡單調整，把PostTime列和Status列順序互換，修改后索引3對原查詢3基本沒影響，而且對查詢1的效率提升也達到最大化
修改后的索引3：
CREATE INDEX IX_UserIDPostTimeStatus ON dbo.Orders(UserID,PostTime,Status) |
現在索引3可以很好的完成查詢1和查詢3，因此索引1現在可以刪除掉，現在只剩索引3和索引4了，我們可以看到修改后的索引3由于先導列問題依然無法用于查詢4，為了使索引3用于查詢4我們再次修改索引3，把PostTime放到索引的第一列，其它列保持順序不變
再次修改后的索引3：
CREATE INDEX IX_PostTimeUserIDStatus ON dbo.Orders(PostTime,UserID,Status) |
可以看到現在索引3也可以有效的用于查詢4了，但是由于先導列原因若將Status列和UserID列換貨，固然可以提高查詢4效率但是會影響查詢1，我們考慮到Status列一般也就幾種狀態，多了也就幾十種，相對于UserID來說選擇性低高，因此還是把選擇性較高的UserID列放在前面，最大化查詢1和查詢3查詢效率。再來看最后一個索引4，索引4和對查詢1和查詢4起效果，由于查詢1已有索引3可用，故忽略對查詢1的作用，現在只剩下對查詢4的起作用，我們看查詢4，索引3和索引4都對其產生效果，毫無疑問索引4對查詢4效果更大一些，但考慮到Status列的低選擇性和多維護一個索引的代價，索引3已能較好的完成查詢4，所以刪除索引4。
這樣一來針對Orders表的4個查詢，我們經過對原來4個索引的優化調整后只保留了修改后的索引3，索引從4個變成一個，而查詢效率方面卻沒有受較大的影響，達到了用盡可能少的索引完成盡可能多的查詢的目的。
在上面的演示中我們使用了OrderCode做為聚集索引，通過對非聚集索引的調整較好的完成了查詢，在大說數情況下這樣就可以ok了，現在我們考慮下若Orders表數據量較大，執行我們的查詢4若返回結果達到幾萬、幾十萬甚至更多的時候，很可能會導致索引失效從而發生表掃描，這時除非我們隊查詢4使用的索引實現索引覆蓋，不過這基本上不大現實。那如何解決這個問題呢？這時時間列上的聚集索引就開始顯示威力了，修改我們的Orders表將聚集索引建立到PostTime列上
--刪除原來的聚集索引主鍵 PK_Orders
ALTER TABLE dbo.Orders DROP CONSTRAINT PK_Orders --創建非聚集索引主鍵 PK_Orders
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(OrderCode) --在時間列PostTime上創建聚集索引
CREATE CLUSTERED INDEX IX_PostTime ON dbo.Orders(PostTime) |
修改后我們擔心的書簽查找問題徹底消失了，因為通常導致索引失效的原因都是過多的書簽查找，發生大量書簽查找時基本上和范圍查詢脫不開關系，大范圍數據查詢又肯定會用到時間列，所以推薦時間列做聚集索引。至于說修改了聚集索引后更新，訂單查詢效率會不會下降，對于這點基本上可以忽略，進行訂單查詢時一般數據量都會較小，那點書簽查找開銷完全可以忽略掉，比如我們的查詢2。
這時我們可能發現我們上面修改的索引3有個尷尬的現狀，索引3的先導列PostTime現在成了聚集索引，那么索引3對查詢1、查詢3、查詢4還有木有用，索引3還有木有存在的必要，關于這點一般來說呢非聚集索引最好不要用聚集索引做先導列，很可能建了等于白建這里只是個建議，那么若刪除了索引3，查詢1、查詢3、查詢4效率會如何，查詢4效率肯定是會提升的、查詢1、查詢3需要實際測試，當然若可以的話把聚集索引建成聯合的，把UserID放進去就不會有此問題了
--在時間列PostTime和UserID列上創建聚集索引
CREATE CLUSTERED INDEX IX_PostTimeUserID ON dbo.Orders(PostTime,UserID) |
當然了這種修改還得小心進行，根據實際需求靈活修改，理想的情況下是建立專門的只讀數據庫復制，在只讀庫上建立最適合范圍查找的聚集索引，在主庫上建立最有利于增、刪、改的聚集索引，對實時性要求不高的查詢全部轉移到只讀庫上執行，相對而言需要大范圍數據篩選的查詢都不需要多好的實時性，盡可到只讀庫執行，而主庫呢肯定是主要執行實時性要求高的小數據量查詢。
總結：
聚集索引的優勢在于大范圍數據查詢效率，因此需要將聚集索引建立在時間列、選擇性相對較低并且經常會用于范圍查詢的列(選擇性過低的如性別列肯定不行，過低的選擇性列索引建了等于白建，比如你在性別列上集索引以為通過性別列起碼一下過濾掉一半數據，范圍大大減小你就大錯特錯了，這點選擇性通常查詢優化器會直接忽略掉，還不如個表掃描來的快），充分發揮聚集索引大范圍數據查詢優勢。
非聚集索引要盡量使用選擇性較高的列以盡可能減少返回的數據量，利用組合索引提高索引的復用率，不要建過多的無用索引，如果發現某個表建了很多的非聚集索引，不妨把那些索引、查詢摘出來分析合并下，減少沒用索引的數量，以提高整體性能。
索引建立還需根據實際需要進行選擇，本文所述觀點在能夠適用于大多數情況，但建立好的索引不是一朝一夕能夠做到的，理論上成立的事實際應用中往往會事與愿違，索引的有效性還要依靠數據庫統計信息等綜合考慮，故每當建立索引后一定要查看下查詢計劃，查看下IO開銷，看看查詢優化器是否按照我們預期的方式使用了索引。