SQL Server調優系列進階篇（如何索引調優）

標籤：upd   alt   有意思   意思   tab   邏輯   針對   好題   利用   

前言

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

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

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

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

技術準備

資料庫版本為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]GOCREATE 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 OrdersORDER BY OrderDate

很簡單的查詢，來看一下執行計畫

因為該表上存在一個主鍵，所以這裡採用了叢集索引掃描（Index Scan），如果沒有叢集索引，這裡肯定就是表掃描了。

下面我們利用一個Hint提示來查看一下SQL Server利用非叢集索引的過程。

這裡我們用Fast N Hint提示，這個提示很簡單就是告訴SQL Server快速的先擷取出前N行資料，別的資料都靠後...把前N行的資料擷取效率提至最高（記住：這個提示最佳的應用情境就是分頁查詢，很多業務系統都有分頁顯示，加上此Hint會讓資料庫最快的擷取出前多少條資料）

我們後續的文章會詳細分析各種Hint的用處。

繼續分析，我想快速擷取到前1行資料，指令碼如下：

SELECT OrderID,CustomerID,OrderDate FROM OrdersORDER BY OrderDateOPTION(FAST 1)

為了快速擷取到一行資料，SQL Server更改了執行計畫，採用了非叢集索引來掃描，並且為了擷取出其它列的資料不得不引進一個書籤尋找（Key Lookup），從上面我們可以看到書籤尋找的消耗高達66%。

我們接著分析，我想擷取前十行的資料，指令碼如下：

SELECT OrderID,CustomerID,OrderDate FROM OrdersORDER BY OrderDateOPTION(FAST 10)

當我們要擷取十行的時候，書籤尋找的消耗已經開始飆升，上面已經飆升到了90%....原因很簡單，就是我文章前面分析的這裡是隨機IO...

雖然書籤尋找影響效率，但是我們尋找的資料只是很少的一部分，所以這裡SQL Server認為利用非叢集索引+書籤尋找擷取資料還是一種最優方式。

我們接著分析，我想快速擷取二十行資料，指令碼如下

SELECT OrderID,CustomerID,OrderDate FROM OrdersORDER BY OrderDateOPTION(FAST 20)

到此，SQL Server已經果斷的放棄了非叢集索引+書籤尋找這種方式。採用了叢集索引掃描這種更低廉的方式。

經過我的測試，我找到了SQL Server認為這個叢集索引有效數值範圍：

SELECT OrderID,CustomerID,OrderDate FROM OrdersORDER BY OrderDateOPTION(FAST 15)SELECT OrderID,CustomerID,OrderDate FROM OrdersORDER BY OrderDateOPTION(FAST 16)

這個判別的閥值是15行，一旦超過了15行資料，SQL Server就會放棄非叢集索引了。

我們從這個過程中可以分析出非叢集索引的有效範圍：15（有效行數）/1660(總行數)=0.009638，也就是9%的這麼一個量，當然，這個值非固定值，取決於多種因素，比如行類型、內容分布、硬體環境等吧。

但是，通過這個值我想告訴你的是：非叢集索引的有效性其實範圍很窄，因為其覆蓋範圍小，這就導致了很多童鞋建立好了非叢集索引了，但是在真正執行的時候基本是沒有用。

這裡再多談點，還有很多人誤認為神馬非叢集索引選INT類型比選Varchar類型好，更有甚者上次看到群裡有人為了把電話號碼也儲存成INT....目的就是為了尋找快云云...

關於這些觀點，其實都是很淺層的理解...索引列的選擇最好是整型不錯，但是也好區分好列內容分布，選擇的標準只有一個：最大限度的提升SQL Server的可選擇性。

舉個極端點的例子：將性別列加上非叢集索引：選擇性只有50%.......本來非叢集索引覆蓋範圍就小，這種索引基本上就是無用...

另外，還要注意索引的順序問題，比如：兩列值：姓、名字，設計索引的時候請將姓放在前面，然後是名字...這就好比你尋找通訊錄一般最先區分姓，然後在找名字一樣....

好吧...一談就談多了，迴歸咱們的內容。

上面的非叢集索引帶來的隨機IO問題，SQL Server從2005版本也給出瞭解決方法：包含性的列索引

其實很簡單，就是在儲存非叢集索引的時候將要擷取的資料頁包含進葉子節點。

就是為了模仿叢集索引的方式，將非叢集索引的葉子節點也存放進資料頁資訊，當然，因為物理資料頁只有一份，所以非叢集索引只能再拷貝一份自己儲存了，這樣在尋找非叢集索引的時候就可以直接擷取資料了。

代碼如下：

USE [Northwind]GOCREATE 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 intset @i=100while 1=1 begin   update testklup set cont1=@i   where clskey=1  set @[email protected]+1 end

另外同樣一個線程進行查詢操作

----類比高頻select操作declare @cont2 char(3000)while 1=1begin    select @cont2=cont2 from testklup where nlskey=1end

本來兩個操作，一個要修改，一個要查詢，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.SessionsVALUES(‘app1‘,‘user1‘,‘host1‘,‘20030212 08:30‘,‘20030212 10:30‘);INSERT INTO DBO.SessionsVALUES(‘app1‘,‘user2‘,‘host1‘,‘20030212 09:30‘,‘20030212 11:30‘);INSERT INTO DBO.SessionsVALUES(‘app1‘,‘user3‘,‘host2‘,‘20030212 09:31‘,‘20030212 11:20‘);INSERT INTO DBO.SessionsVALUES(‘app1‘,‘user4‘,‘host2‘,‘20030212 11:30‘,‘20030212 12:30‘);INSERT INTO DBO.SessionsVALUES(‘app1‘,‘user5‘,‘host3‘,‘20030212 11:35‘,‘20030212 12:35‘);INSERT INTO DBO.SessionsVALUES(‘app2‘,‘user6‘,‘host3‘,‘20030212 08:30‘,‘20030212 10:30‘);INSERT INTO DBO.SessionsVALUES(‘app2‘,‘user7‘,‘host3‘,‘20030212 08:30‘,‘20030212 10:30‘);INSERT INTO DBO.SessionsVALUES(‘app2‘,‘user8‘,‘host3‘,‘20030212 08:30‘,‘20030212 10:30‘);

就一張表，要求擷取出：查詢出每個應用程式的最大並發數.... 問題不是很難，想測試下能力的可以試試.....再重申下，一定好審好題再做，可以將答案給我留言。

 

結語 

有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。

 

文章最後給出前面幾篇的串連，以下內容基本涵蓋我們日常中所寫的查詢運算的分解，看來有必要整理一篇目錄了.....

SQL Server調優系列基礎篇

SQL Server調優系列基礎篇（常用運算子總結）

SQL Server調優系列基礎篇（聯合運算子總結）

SQL Server調優系列基礎篇（並行運算總結）

SQL Server調優系列基礎篇（並行運算總結篇二）

SQL Server調優系列基礎篇（索引運算總結）

SQL Server調優系列基礎篇（子查詢運算總結）

-----------------以下進階篇-------------------

SQL Server調優系列進階篇（查詢最佳化工具的運行方式）

SQL Server調優系列進階篇（查詢語句運行幾個指標值監測）

SQL Server調優系列進階篇（深入剖析統計資訊）

 

SQL Server調優系列進階篇（如何索引調優）