SQL Server調優系列進階篇（如何索引調優） - zhijianliutang－IT工程師數位筆記本

文章出處

前言

上一篇我們分析了數據庫中的統計信息的作用，我們已經了解了數據庫如何通過統計信息來掌控數據庫中各個表的內容分布。不清楚的童鞋可以點擊參考。

作為調優系列的文章，數據庫的索引肯定是不能少的了，所以本篇我們就開始分析這塊內容，關于索引的基礎知識就不打算深入分析了，網上一搜一片片的，本篇更側重的是一些實戰項內容展示，希望通過本篇文章各位看官能在真正的場景中找到合適的解決方法足以。

對于索引的使用，我希望的是遇到問題找到合適的解決方法就可以，切勿亂用！！！

本篇在分析出索引的優越性的同時也將負面影響展現出來。

技術準備

數據庫版本為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 @i=@i+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');