前言
上一篇我們分析了資料庫中的統計資訊的作用，我們已經了解了資料庫如何通過統計資訊來掌控資料庫中各個表的内容分布。不清楚的童鞋可以點選參考。
作為調優系列的文章，資料庫的索引肯定是不能少的了，是以本篇我們就開始分析這塊内容，關于索引的基礎知識就不打算深入分析了，網上一搜一片片的，本篇更側重的是一些實戰項内容展示，希望通過本篇文章各位看官能在真正的場景中找到合适的解決方法足以。
對于索引的使用，我希望的是遇到問題找到合适的解決方法就可以，切勿亂用！！！
本篇在分析出索引的優越性的同時也将負面影響展現出來。
技術準備
資料庫版本為SQL Server2012，前幾篇文章用的是SQL Server2008RT，内容差別不大，利用微軟的以前的案例庫（Northwind）進行分析，部分内容也會應用微軟的另一個案例庫AdventureWorks
相信了解SQL Server的朋友，對這兩個庫都不會太陌生。
概念了解
所謂的索引同SQL Server中的其它類型的資料頁一樣，也是固定的8KB（8192位元組）,存儲方式同為B-Tree結構，索引B樹中的每一頁稱為一個索引節點。B樹頂端節點為根節點。索引中的底層節點稱為葉節點。根節點與葉節點之間的任何索引統稱為中間級。
算了，描述起來太麻煩，聯機叢書上截個圖直覺的展示結構：
上面的圖直覺的展示出B-Tree結構的方式，基本和資料頁的結構類似，這裡有一點需要提醒下，就是聚集索引的最底層的葉子節點存儲的為實際的資料頁。就這一點為資料的快速擷取可謂提供了一個超快方式，也是我們調優中必須要使用的，後續文章中分析。
再來看一下非聚集索引。
非聚集索引和聚集索引相比，同樣以B-Tree的結構存儲，但是在存儲的内容上有着顯著的差別：
- 基礎表的資料行不按非聚集索引鍵的順序排序和存儲
- 非聚集索引的葉層是由索引頁而不是由資料組成
由于上面的幾種特性中，很明顯的擷取資料最快的方式是通過聚集索引，因為它葉子節點就是資料頁，同樣葉子節點的資料頁實體順序也是按照聚集索引的結構順序進行存儲，這也就造成了一個資料表隻能存在一個聚集索引，并且聚集索引所占據的磁盤空間要遠遠小于非聚集索引。
而對于非聚集索引的葉子節點存儲的是索引行，擷取資料的話必須通過索引行所記錄的資料頁的位址（聚集索引鍵或者堆表的RID）,這一特性也就是造就了，一張資料表可以有多個非聚集聚集索引，并且需要自己獨立的存儲空間。
兩種索引設計的初衷都是為了便于快速的擷取到資料頁，提高查詢性能。這就好比一本書需要加上目錄一個道理。
關于索引的知識很多，基礎的内容不作太多介紹，不了解的可以自行查閱資料，網上N多。
下面主要介紹一下使用技巧和注意事項，我相信這也是朋友們最關注的。
一、聚集索引的選擇
所有的利用索引提升查詢性能方式中，首當其中的就是聚集索引，它速度快是因為B-Tree這種優越的存儲算法，B-Tree作為一個平衡分叉樹的資料結構，是市面上所有的關系型資料庫所采用的方式，有興趣的同學可以深入研究一下此種算法。
來看一下聚集索引，因為在一張表中隻能存在一個，并且主要經過聚集索引查找在葉節點就可以擷取到資料内容，是以SQL Server資料庫系統也在盡力的為聚集索引的存在提供便利。
舉個例子：
USE [TestDB]
GO
CREATE TABLE [dbo].[TestTable](
[A] [int] PRIMARY KEY NOT NULL,
[B] [varchar](20) NULL
)
GO 我們建立一張測試表，一般采取的最佳設計是在這張表上添加一個主鍵。 主鍵的概念，我相信幾乎了解點資料庫的童鞋就不陌生，兩大基本特性：不重複、非空。
好了，僅僅這兩點就被利用，不重複所帶來的含義就是選擇性高，非空更能帶來資料的稠密度高，是以，SQL Server就痛快的将聚集索引選在了主鍵列上，并且這種方式在資料庫中起了一個高雅的名字：主鍵索引。
是以當我們建立完這張表的時候，SQL Server預設就将該表的聚集索引建立好了。
為了避免名稱的重複，SQL Server預設給名稱加了一個GUID的字段。真可謂用心了。
當然，正規的方式使我們自己指定這個名稱，腳本如下：
CREATE TABLE [TestTable3]
(
[A] [int] NOT NULL,
[B] [varchar](20) NULL
CONSTRAINT PK_Index PRIMARY KEY([A])
);
GO 看上去優雅多了。
其實，SQL Server這種預設的方式最主要的目的就是為了最大限度的利用好聚集索引，因為我們知道聚集索引所帶來的好處，并且它還為非聚集索引的形成創造了基礎條件：非聚集索引的葉子節點就是聚集索引的鍵值碼。
是以基于此，我們以後設計表的時候，也不要辜負了SQL Server的用心，将每張表都應該有一個聚集索引。
我見過很多人設計出來的表就是赤裸裸的堆表。而這不是嚴重的，嚴重的是很多不明是以的在堆表上加上了非聚集索引，這在大并發的場景中就是一個典型的死鎖環境，文章後面會複現該場景。
當然，這種方式不是一個最優的一種方式，因為我們知道我們在設計表的時候，主鍵大部分情況下為無意義的鍵，也就說很多的情況在查詢的時候是不會作為篩選條件的，并且它所覆寫的範圍也僅限于主鍵列。是以最優的設計是采用聯合主鍵或者自定義聚集索引列。當然了，SQL Server上面這種設計的初衷大部分是考慮了小白的建表方式，權衡了利弊選出的一種折中方式，如無特别需求，預設的這種建立聚集索引的方式基本能滿足業務場景。
接着我們分析下非聚集索引
二、非聚集索引的選擇
經過文章前面的分析，我們可以了解到聚集索引所帶來的好處，但是它也有着最大的自身限制性：一張表隻能存在一個聚集索引。
為了更多的使用索引，SQL Server又引入了非聚集索引，并且單張表的非聚集索引項可以存在好多個，是以足以讓我們領略索引帶來的性能提升。
上面，我們知道在一張表指定主鍵的時候，SQL Server預設就将聚集索引給建立好了，但是對于非聚集索引的建立，SQL Server預設是不會幫助建立的，需要我們手動建立，因為它也不知道你的非聚集索引建立到那一列上更合适。
但是，通常有一個最佳實踐就是，作為關系性資料為了應當複雜的業務實體，采用的設計結構一般都是采用一對一、一對多、多對多的設計思路，而這種設計結構就形成了主外鍵的關系，我們知道主鍵SQL Server會自動的建立聚集索引，索引在外鍵中推薦的方式是手動建立非聚集索引，目的是為了加快表之間的映射關系。
但是，非聚集索引因為其存儲結構的特别性（葉節點存儲的非資料頁），影響了它讀取資料的效率，并且更多時候我們要擷取的是一部分資料而非一條資料。
在擷取的一部分資料為非聚集索引所覆寫那麼利用非聚集索引是高效的，如果擷取的資料非索引所覆寫，也就是通過聚集索引查找的時候還需要引入額外的書簽查找，這種狀态效率是非常低的，因為我們知道對于B-Tree結構下的書簽查找是：随機IO,随機IO所帶來的性能消耗是非常大的，為此SQL Server會放棄這種方式，直接通過表掃描（Table seek）或者聚集索引掃描（Index Seek）擷取的資料更直接。
上面的這部分内容，我在前面的第一篇文章就有介紹，可以點選檢視。
描述起來太麻煩，來個例子解釋下：
SELECT OrderID,CustomerID,OrderDate
FROM Orders
ORDER BY OrderDate 很簡單的查詢，來看一下執行計劃
因為該表上存在一個主鍵，是以這裡采用了聚集索引掃描（Index Scan），如果沒有聚集索引，這裡肯定就是表掃描了。
下面我們利用一個Hint提示來檢視一下SQL Server利用非聚集索引的過程。
這裡我們用Fast N Hint提示，這個提示很簡單就是告訴SQL Server快速的先擷取出前N行資料，别的資料都靠後...把前N行的資料擷取效率提至最高（記住：這個提示最佳的應用場景就是分頁查詢，很多業務系統都有分頁顯示，加上此Hint會讓資料庫最快的擷取出前多少條資料）
我們後續的文章會詳細分析各種Hint的用處。
繼續分析，我想快速擷取到前1行資料，腳本如下：
SELECT OrderID,CustomerID,OrderDate
FROM Orders
ORDER BY OrderDate
OPTION(FAST 1) 為了快速擷取到一行資料，SQL Server更改了執行計劃，采用了非聚集索引來掃描，并且為了擷取出其它列的資料不得不引進一個書簽查找（Key Lookup），從上面我們可以看到書簽查找的消耗高達66%。
我們接着分析，我想擷取前十行的資料，腳本如下：
SELECT OrderID,CustomerID,OrderDate
FROM Orders
ORDER BY OrderDate
OPTION(FAST 10) 當我們要擷取十行的時候，書簽查找的消耗已經開始飙升，上面已經飙升到了90%....原因很簡單，就是我文章前面分析的這裡是随機IO...
雖然書簽查找影響效率，但是我們查找的資料隻是很少的一部分，是以這裡SQL Server認為利用非聚集索引+書簽查找擷取資料還是一種最優方式。
我們接着分析，我想快速擷取二十行資料，腳本如下
SELECT OrderID,CustomerID,OrderDate
FROM Orders
ORDER BY OrderDate
OPTION(FAST 20) 到此，SQL Server已經果斷的放棄了非聚集索引+書簽查找這種方式。采用了聚集索引掃描這種更低廉的方式。
經過我的測試，我找到了SQL Server認為這個聚集索引有效的數值範圍：
SELECT OrderID,CustomerID,OrderDate
FROM Orders
ORDER BY OrderDate
OPTION(FAST 15)
SELECT OrderID,CustomerID,OrderDate
FROM Orders
ORDER BY OrderDate
OPTION(FAST 16) 這個判别的閥值是15行，一旦超過了15行資料，SQL Server就會放棄非聚集索引了。
我們從這個過程中可以分析出非聚集索引的有效範圍：15（有效行數）/1660(總行數)=0.009638，也就是9%的這麼一個量，當然，這個值非固定值，取決于多種因素，比如行類型、内容分布、硬體環境等吧。
但是，通過這個值我想告訴你的是：非聚集索引的有效性其實範圍很窄，因為其覆寫範圍小，這就導緻了很多童鞋建立好了非聚集索引了，但是在真正執行的時候基本是沒有用。
這裡再多談點，還有很多人誤認為神馬非聚集索引選INT類型比選Varchar類型好，更有甚者上次看到群裡有人為了把電話号碼也存儲成INT....目的就是為了查找快雲雲...
關于這些觀點，其實都是很淺層的了解...索引列的選擇最好是整型不錯，但是也好區分好列内容分布，選擇的标準隻有一個：最大限度的提升SQL Server的可選擇性。
舉個極端點的例子：将性别列加上非聚集索引：選擇性隻有50%.......本來非聚集索引覆寫範圍就小，這種索引基本上就是無用...
另外，還要注意索引的順序問題，比如：兩列值：姓、名字，設計索引的時候請将姓放在前面，然後是名字...這就好比你查找通訊錄一般最先區分姓，然後在找名字一樣....
好吧...一談就談多了，回歸咱們的内容。
上面的非聚集索引帶來的随機IO問題，SQL Server從2005版本也給出了解決方法：包含性的列索引
其實很簡單，就是在存儲非聚集索引的時候将要擷取的資料頁包含進葉子節點。
就是為了模仿聚集索引的方式，将非聚集索引的葉子節點也存放進資料頁資訊，當然，因為實體資料頁隻有一份，是以非聚集索引隻能再拷貝一份自己存儲了，這樣在查找非聚集索引的時候就可以直接擷取資料了。
代碼如下：
USE [Northwind]
GO
CREATE NONCLUSTERED INDEX [OrderDateINDEX] ON [dbo].[Orders]
(
[OrderDate] ASC
)
INCLUDE
(
[OrderID],
[CustomerID]
) WITH (ONLINE = ON)
GO 這樣的話，在查找這列的時候就都會采用此非聚集索引了。并且避免了随機IO(書簽查找)的存在，降低了IO值，提升了性能。
當然，在大部分的業務系統中，利用非聚集索引擷取的資料量還是比較少的，大部分是一條展示明細頁面，這樣的話非聚集索引的有利面就充分顯現了。
是以針對OLTP業務系統而言，要學會利用好非聚集索引。
當然，凡事有利有弊，也不能過多的建立非聚集索引，如果利用過多的索引這就好比将一張表的各個列資料拷貝了N份重新存儲，占用空間不說，最主要的是SQL Server在新添加資料的時候需要維護各個非聚集索引，這會導緻資料的插入速度減慢，還會造成更多的索引碎片，增加讀取IO。
下面，我們來重制下文章前面提到的死鎖現象，這些問題純粹是設計不到位導緻。
關于此問題高兄在以前的文章中就有介紹，這裡我借用以下它的腳本來重制下，點選此可以連接配接到高兄的那篇文章。
腳本如下：
create table testklup
(
clskey int not null,
nlskey int not null, cont1 int not null, cont2 char(3000) ) create unique clustered index inx_cls on testklup(clskey) create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1) insert into testklup select 1,1,100,'aaa' insert into testklup select 2,2,200,'bbb' insert into testklup select 3,3,300,'ccc' 開啟一個線程進項查詢修改
----模拟高頻update操作
declare @i int
set @i=100
while 1=1 begin update testklup set cont1=@i where clskey=1 set @[email protected]+1 end 另外同樣一個線程進行查詢操作
----模拟高頻select操作
declare @cont2 char(3000)
while 1=1 begin select @cont2=cont2 from testklup where nlskey=1 end 本來兩個操作，一個要修改，一個要查詢，SQL Server會自動很好的維護好兩者秩序，不會發生死鎖的情況，但是...但是我們在上面建立了一個包含性的非聚集索引，将Cont1列拷貝進入了非聚集索引，這樣修改操作就需要維護非聚集索引列，而這時候我們有利用非聚集索引進行查詢，兩者恰巧發生在同一張表的兩個不同的鍵值上，這就造成了一次死鎖的發生。
我們開啟Profile來捕捉此死鎖的發生。
其實，對于這種問題好幾種解決方式，因為我們這知道這個問題的罪魁禍首就是我們建立的非聚集索引不恰當，使得查詢和修改發生在兩個同一張表的不同鍵值上。
是以一種解決方式就是，直接将這個聚集索引去掉。這樣就不會産生額外的鍵鎖的存在。
另一種方式就是講我們的非聚集索引把cont2列也包含進去，腳本如下
CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup]
([nlskey] ASC) INCLUDE ( [cont2]) 當然，也可以提高隔離級别或者降低隔離級别，但這不是推薦的方法，原因很簡單：降低隔離級别會髒讀，提高隔離級别會影響并發量。
希望各位看官在設計資料庫的時候不要發生此類悲劇。尤其高并發的情況下，一定要謹慎，再謹慎的進行。
當然，這裡也要捎帶提醒一下：不要手裡拿着錘子，眼裡看什麼都是釘子！！切勿過度設計。
還是那句話，合适的場景采取合适的方案，一切不能武斷，更不能輕易聽信于别人，要以實踐方能出真理。
索引的知識實在是太廣泛....稍寫點東西就夠篇幅了....先到此吧...後續我再補充一部分關于索引的内容。
我們要及時的維護好索引，及時的重建、碎片整理、删除無用索引等操作，包括建立索引的一系列注意項等。
關于此塊内容下一篇文章介紹吧。
關于調優内容太廣泛，我們放在以後的篇幅中介紹，有興趣的可以提前關注
三、考察問題
在文章的最後，曬一個前幾天在書中看到的一個比較有意思的邏輯，這裡共享下供院友們玩味，也考察下對T-SQL語句的邏輯能力，這道題可以作為一道面試題，不算太難，但是完全能測試出對T-SQL程式設計能力的高低。
問題内容如下：
--建立一個回話資訊記錄表
CREATE TABLE dbo.Sessions
(
keycol INT NOT NULL IDENTITY,
app VARCHAR(10) NOT NULL,
usr VARCHAR(10) NOT NULL,
host VARCHAR(10) not null, starttime DATETIME not null, endtime DATETIME not null, CONSTRAINT PK_Sessions PRIMARY KEY(keycol), CHECK(endtime>starttime) ); GO --插入部分測試資料 INSERT INTO DBO.Sessions VALUES('app1','user1','host1','20030212 08:30','20030212 10:30'); INSERT INTO DBO.Sessions VALUES('app1','user2','host1','20030212 09:30','20030212 11:30'); INSERT INTO DBO.Sessions VALUES('app1','user3','host2','20030212 09:31','20030212 11:20'); INSERT INTO DBO.Sessions VALUES('app1','user4','host2','20030212 11:30','20030212 12:30'); INSERT INTO DBO.Sessions VALUES('app1','user5','host3','20030212 11:35','20030212 12:35'); INSERT INTO DBO.Sessions VALUES('app2','user6','host3','20030212 08:30','20030212 10:30'); INSERT INTO DBO.Sessions VALUES('app2','user7','host3','20030212 08:30','20030212 10:30'); INSERT INTO DBO.Sessions VALUES('app2','user8','host3','20030212 08:30','20030212 10:30'); 就一張表，要求擷取出：查詢出每個應用程式的最大并發數.... 問題不是很難，想測試下能力的可以試試.....再重申下，一定好審好題再做，可以将答案給我留言。
結語
有問題可以留言或者私信，随時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。
轉載于:https://www.cnblogs.com/MuNet/p/5729413.html