SQLServer索引調優實踐

索引的重要性

資料庫效能最佳化中索引絕對是一個重量級的因素，可以說，索引使用不當，其它最佳化措施將毫無意義。

聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)

最通俗的解釋是:聚簇索引的順序就是資料的實體儲存體順序，而對非聚簇索引的索引順序與資料物理排列順序無關。舉例來說，你翻到新華字典的漢字“爬”那一頁就是P開頭的部分，這就是實體儲存體順序（聚簇索引）；而不用你到目錄，找到漢字“爬”所在的頁碼，然後根據頁碼找到這個字（非聚簇索引）。

下表給出了何時使用聚簇索引與非聚簇索引:

動作	使用聚簇索引	使用非聚簇索引
列經常被分組排序	應	應
返回某範圍內的資料	應	不應
一個或極少不同值	不應	不應
小數目的不同值	應	不應
大數目的不同值	不應	應
頻繁更新的列	不應	應
外鍵列	應	應
主鍵列	應	應
頻繁修改索引列	不應	應

聚簇索引的唯一性

正式聚簇索引的順序就是資料的實體儲存體順序，所以一個表最多隻能有一個聚簇索引，因為實體儲存體只能有一個順序。正因為一個表最多隻能有一個聚簇索引，所以它顯得更為珍貴，一個表設定什麼為聚簇索引對效能很關鍵。

初學者最大的誤區：把主鍵自動設為聚簇索引

因為這是SQLServer的預設主鍵行為，你設定了主鍵，它就把主鍵設為聚簇索引，而一個表最多隻能有一個聚簇索引，所以很多人就把其他索引設定為非聚簇索引。這個是最大的誤區。甚至有的主鍵又是無意義的自動增量欄位，那樣的話Clustered index對效率的協助，完全被浪費了。

剛才說到了，聚簇索引效能最好而且具有唯一性，所以非常珍貴，必須謹慎設定。一般要根據這個表最常用的SQL查詢方式來進行選擇，某個欄位作為聚簇索引，或組合聚簇索引，這個要看實際情況。

事實上，建表的時候，先需要設定主鍵，然後添加我們想要的聚簇索引，最後設定主鍵，SQLServer就會自動把主鍵設定為非聚簇索引（會自動根據情況選擇）。如果你已經設定了主鍵為聚簇索引，必須先刪除主鍵，然後添加我們想要的聚簇索引，最後恢複設定主鍵即可。

記住我們的最終目的就是在相同結果集情況下，儘可能減少邏輯IO。

我們先從一個實際使用的簡單例子開始。

一個簡單的表：

CREATE TABLE [dbo].[Table1](   [ID] [int] IDENTITY(1,1) NOT NULL,   [Data1] [int] NOT NULL DEFAULT ((0)),   [Data2] [int] NOT NULL DEFAULT ((0)),   [Data3] [int] NOT NULL DEFAULT ((0)),   [Name1] [nvarchar](50) NOT NULL DEFAULT (''),   [Name2] [nvarchar](50) NOT NULL DEFAULT (''),   [Name3] [nvarchar](50) DEFAULT (''),   [DTAt] [datetime] NOT NULL DEFAULT (getdate())

來點測試資料（10w條）：

declare @i int set @i = 1 while @i < 100000 begin insert into Table1 ([Data1] ,[Data2] ,[Data3] ,[Name1],[Name2] ,[Name3]) values(@i, 2* @i,3*@i, CAST(@i AS NVARCHAR(50)), CAST(2*@i AS NVARCHAR(50)), CAST(3*@i AS NVARCHAR(50))) set @i = @i + 1 end update table1 set dtat= DateAdd (s, data1, dtat)

開啟查詢分析器的IO統計和時間統計：

SET STATISTICS IO ON; SET STATISTICS TIME ON;

顯示實際的“執行計畫”：

我們最常用的SQL查詢是這樣的：

SELECT * FROM Table1 WHERE Data1 = 2 ORDER BY DTAt DESC;

先在Table1設主鍵ID，系統自動為該主鍵建立了聚簇索引。

然後執行該語句，結果是：

Table 'Table1'. Scan count 1, logical reads 911, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 7 ms.

 

然後我們在Data1和DTat欄位分別建立非聚簇索引：

CREATE NONCLUSTERED INDEX [N_Data1] ON [dbo].[Table1] ( [Data1] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [N_DTat] ON [dbo].[Table1] ( [DTAt] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

再次執行該語句，結果是：

Table 'Table1'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 39 ms.

 

可以看到設立了索引反而沒有任何效能的提升而且消耗的時間更多了，繼續調整。

然後我們刪除所有非聚簇索引，並刪除主鍵，這樣所有索引都刪除了。建立複合式索引Data1和DTAt，最後加上主鍵：

CREATE CLUSTERED INDEX [C_Data1_DTat] ON [dbo].[Table1] ( [Data1] ASC, [DTAt] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

再次執行語句：

Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

 

可以看到只有聚簇索引seek了，消除了index scan和nested loop，而且執行時間也只有1ms，達到了最初最佳化的目的。

複合式索引小結

小結以上的調優實踐，要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢，像本例就是那句類似的組合條件查詢的情況，這種情況最好使用組合聚簇索引，而且最多用到的欄位要放在組合聚簇索引的前面，否則的話就索引就不會有好的效果，看下例：

查詢條件落在複合式索引的第二個欄位上，引起了index scan，效果很不好，執行時間是：

Table 'Table1'. Scan count 1, logical reads 238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 22 ms.

而如果僅查詢條件是第一個欄位也沒有問題，因為複合式索引最左首碼原則，實踐如下：

Table 'Table1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

從中可以看出，最多用到的欄位要放在組合聚簇索引的前面。

Index seek 為什麼比 Index scan好？

索引掃描也就是遍曆B樹，而seek是B樹尋找直接定位。

Index scan多半是出現在索引列在運算式中。資料庫引擎無法直接確定你要的列的值，所以只能掃描整個整個索引進行計算。index seek就要好很多.資料庫引擎只需要掃描幾個分支節點就可以定位到你要的記錄。回過來,如果叢集索引的葉子節點就是記錄，那麼Clustered Index Scan就基本等同於full table scan。

一些最佳化原則

1. 1、預設情況下建立的索引是非聚簇索引，但有時它並不是最佳的。在非群集索引下，資料在物理上隨機存放在資料頁上。合理的索引設計要建立在對各種查詢的分析和預測上。一般來說：
   a.有大量重複值、且經常有範圍查詢（ > ,< ，> =,< =）和order by、group by發生的列，可考
   慮建立群集索引；
   b.經常同時存取多列，且每列都含有重複值可考慮建立複合式索引；
   c.複合式索引要盡量使關鍵查詢形成索引覆蓋，其前置列一定是使用最頻繁的列。索引雖有助於提高效能但不是索引越多越好，恰好相反過多的索引會導致系統低效。使用者在表中每加進一個索引，維護索引集合就要做相應的更新工作。
   2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語，任何一種索引都有助於SELECT的效能提高。

3、多表操作在被實際執行前，查詢最佳化工具會根據串連條件，列出幾組可能的串連方案並從中找出系統開銷最小的最佳方案。串連條件要充份考慮帶有索引的表、行數多的表；內外表的選擇可由公式：外層表中的匹配行數*內層表中每一次尋找的次數確定，乘積最小為最佳方案。
4、任何對列的操作都將導致表掃描，它包括資料庫函數、計算運算式等等，查詢時要儘可能將操作移至等號右邊。
5、IN、OR子句常會使用工作表，使索引失效。如果不產生大量重複值，可以考慮把子句拆開。拆開的子句中應該包含索引。

Sql的最佳化原則2：
1、只要能滿足你的需求，應儘可能使用更小的資料類型：例如使用MEDIUMINT代替INT
2、盡量把所有的列設定為NOT NULL，如果你要儲存NULL，手動去設定它，而不是把它設為預設值。
3、盡量少用VARCHAR、TEXT、BLOB類型
4、如果你的資料只有你所知的少量的幾個。最好使用ENUM類型 
 

有關Join的一些原則

SQL Server 有三種類型的JOIN操作：

• Nested loops joins
• Merge joins
• Hash joins
•  

如果Join的輸入很小，例如小於10行，然後其他的Join輸入很大並且索引在其列上，則Nested loops joins是最快的。（原因參考Understanding Nested Loops Joins）

如果兩個Join輸入都不小，但在索引列上排序（例如是在掃描排序的索引後獲得的 scanning sorted indexes），則Merge joins是最快的。（原因參考Understanding Merge Joins）

Hash joins可以有效處理大量的、沒有排序的、沒有索引的輸入。尤其對複雜查詢的中間結果處理很有效。（更多參考Understanding Hash Joins）

 

如何分析SQL語句

微軟MSDN給出了答案：http://msdn.microsoft.com/en-us/library/ms191227.aspx

 

找出資料庫中效能最差的SQL

最佳化哪個表？從何入手？首先需要定位效能瓶頸，找到運行最慢的SQL。可以採用如下步驟：

1. 運行 dbcc  freeProcCache  清除緩衝

2. 運行你的程式，或者你的SQL或預存程序，操作資料庫

3. 完了以後運行以下SQL找到運行最慢的SQL：

SELECT  DB_ID(DB.dbid) '資料庫名'
      , OBJECT_ID(db.objectid) '對象'
      , QS.creation_time '編譯計劃的時間'
      , QS.last_execution_time '上次執行計畫的時間'
      , QS.execution_count '執行的次數'
      , QS.total_elapsed_time / 1000 '佔用的總時間（秒）'
      , QS.total_physical_reads '物理讀取總次數'
      , QS.total_worker_time / 1000 'CPU 時間總量（秒）'
      , QS.total_logical_writes '邏輯寫入總次數'
      , QS.total_logical_reads N'邏輯讀取總次數'
      , QS.total_elapsed_time / 1000 N'總花費時間（秒）'
      , SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE QS.statement_end_offset
                      END - QS.statement_start_offset ) / 2 ) + 1) AS '執行語句'
FROM    sys.dm_exec_query_stats AS QS CROSS APPLY
        sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN
        ( SELECT    *
          FROM      sys.dm_exec_cached_plans cp CROSS APPLY
                    sys.dm_exec_query_plan(cp.plan_handle)
        ) DB
            ON QS.plan_handle = DB.plan_handle
where   SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,
                  ( ( CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.text)
                        ELSE qs.statement_end_offset
                      END - qs.statement_start_offset ) / 2 ) + 1) not like '%fetch%'
                      ORDER BY QS.total_elapsed_time / 1000 DESC

 

使用SQLServer Profiler找出資料庫中效能最差的SQL

首先開啟SQLServer Profiler:

然後點擊工具列“New Trace”，使用預設的模板，點擊RUN。

也許會有報錯："only TrueType fonts are supported. There id not a TrueType font"。不用怕，點擊Tools菜單->Options，重新選擇一個字型例如Vendana 即可。（這個是微軟的一個bug）

運行起來以後，SQLServer Profiler會監控資料庫的活動，所以最好在你需要監控的資料庫上多做些操作。等覺得差不多了，點擊停止。然後儲存trace結果到檔案或者table。

這裡儲存到Table：在菜單“File”-“Save as ”-“Trace table”，例如輸入一個master資料庫的新的table名：profileTrace，儲存即可。

找到最耗時的SQL：

use master select * from profiletrace order by duration desc;

找到了效能瓶頸，接下來就可以有針對性的一個個進行調優了。

對使用SQLServer Profiler的更多資訊可以參考：

http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx

 

使用SQLServer Database Engine Tuning AdvisorDatabase Engine Tuning Advisor

使用上述的SQLServer Profiler得到了trace還有一個好處就是可以用到這個最佳化顧問。用它可以偷點懶，得到SQLServer給您的最佳化顧問，例如這個表需要加個索引什麼的…

首先開啟Database Engine Tuning Advisor：

然後開啟剛才profiler的結果（我們存到了master資料庫的profileTrace表）：

點擊“start analysis”，運行完成後查看最佳化建議（圖中最後是建議建立的索引，效能提升72%）

這個方法可以偷點懶，得到SQLServer給您的最佳化顧問。

繼續閱讀：

• SQLServer索引調優實踐(2) - 索引覆蓋

或許您對以下文章有興趣：

• 程式員辦網站創業，幾個問題你想好了嗎？
• CTO談豆瓣網和校內網技術架構變遷
• AJAX延遲非同步載入側邊欄+伺服器端緩衝AJAX輸出
• 二級下拉式功能表被遮住，css設定z-index在ie下沒作用的問題解決辦法
• 簡單JS實現走馬燈效果的文字(無需jQuery)
• jQuery和ExtJS的timeOut逾時設定和event事件處理