SQLServer索引調(diào)優(yōu)實踐

賈朋亮博客 2014-03-23

展開全文

索引的重要性

數(shù)據(jù)庫性能優(yōu)化中索引絕對是一個重量級的因素，可以說，索引使用不當，其它優(yōu)化措施將毫無意義。

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

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

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

動作	使用聚簇索引	使用非聚簇索引
列經(jīng)常被分組排序	應(yīng)	應(yīng)
返回某范圍內(nèi)的數(shù)據(jù)	應(yīng)	不應(yīng)
一個或極少不同值	不應(yīng)	不應(yīng)
小數(shù)目的不同值	應(yīng)	不應(yīng)
大數(shù)目的不同值	不應(yīng)	應(yīng)
頻繁更新的列	不應(yīng)	應(yīng)
外鍵列	應(yīng)	應(yīng)
主鍵列	應(yīng)	應(yīng)
頻繁修改索引列	不應(yīng)	應(yīng)

聚簇索引的唯一性

正式聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，所以一個表最多只能有一個聚簇索引，因為物理存儲只能有一個順序。正因為一個表最多只能有一個聚簇索引，所以它顯得更為珍貴，一個表設(shè)置什么為聚簇索引對性能很關(guān)鍵。

初學(xué)者最大的誤區(qū)：把主鍵自動設(shè)為聚簇索引

因為這是SQLServer的默認主鍵行為，你設(shè)置了主鍵，它就把主鍵設(shè)為聚簇索引，而一個表最多只能有一個聚簇索引，所以很多人就把其他索引設(shè)置為非聚簇索引。這個是最大的誤區(qū)。甚至有的主鍵又是無意義的自動增量字段，那樣的話Clustered index對效率的幫助，完全被浪費了。

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

事實上，建表的時候，先需要設(shè)置主鍵，然后添加我們想要的聚簇索引，最后設(shè)置主鍵，SQLServer就會自動把主鍵設(shè)置為非聚簇索引（會自動根據(jù)情況選擇）。如果你已經(jīng)設(shè)置了主鍵為聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復(fù)設(shè)置主鍵即可。

記住我們的最終目的就是在相同結(jié)果集情況下，盡可能減少邏輯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())

來點測試數(shù)據(jù)（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統(tǒng)計和時間統(tǒng)計：

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

顯示實際的“執(zhí)行計劃”：

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

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

先在Table1設(shè)主鍵ID，系統(tǒng)自動為該主鍵建立了聚簇索引。

然后執(zhí)行該語句，結(jié)果是：

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]

再次執(zhí)行該語句，結(jié)果是：

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.

可以看到設(shè)立了索引反而沒有任何性能的提升而且消耗的時間更多了，繼續(xù)調(diào)整。

然后我們刪除所有非聚簇索引，并刪除主鍵，這樣所有索引都刪除了。建立組合索引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]

再次執(zhí)行語句：

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，而且執(zhí)行時間也只有1ms，達到了最初優(yōu)化的目的。

組合索引小結(jié)

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

查詢條件落在組合索引的第二個字段上，引起了index scan，效果很不好，執(zhí)行時間是：

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多半是出現(xiàn)在索引列在表達式中。數(shù)據(jù)庫引擎無法直接確定你要的列的值，所以只能掃描整個整個索引進行計算。index seek就要好很多.數(shù)據(jù)庫引擎只需要掃描幾個分支節(jié)點就可以定位到你要的記錄?；剡^來,如果聚集索引的葉子節(jié)點就是記錄，那么Clustered Index Scan就基本等同于full table scan。

一些優(yōu)化原則

1、缺省情況下建立的索引是非聚簇索引，但有時它并不是最佳的。在非群集索引下，數(shù)據(jù)在物理上隨機存放在數(shù)據(jù)頁上。合理的索引設(shè)計要建立在對各種查詢的分析和預(yù)測上。一般來說：
a.有大量重復(fù)值、且經(jīng)常有范圍查詢（ > ,< ，> =,< =）和order by、group by發(fā)生的列，可考
慮建立群集索引；
b.經(jīng)常同時存取多列，且每列都含有重復(fù)值可考慮建立組合索引；
c.組合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列。索引雖有助于提高性能但不是索引越多越好，恰好相反過多的索引會導(dǎo)致系統(tǒng)低效。用戶在表中每加進一個索引，維護索引集合就要做相應(yīng)的更新工作。
2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語，任何一種索引都有助于SELECT的性能提高。

3、多表操作在被實際執(zhí)行前，查詢優(yōu)化器會根據(jù)連接條件，列出幾組可能的連接方案并從中找出系統(tǒng)開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表；內(nèi)外表的選擇可由公式：外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定，乘積最小為最佳方案。
4、任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊。
5、IN、OR子句常會使用工作表，使索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開。拆開的子句中應(yīng)該包含索引。

Sql的優(yōu)化原則2：
1、只要能滿足你的需求，應(yīng)盡可能使用更小的數(shù)據(jù)類型：例如使用MEDIUMINT代替INT
2、盡量把所有的列設(shè)置為NOT NULL，如果你要保存NULL，手動去設(shè)置它，而不是把它設(shè)為默認值。
3、盡量少用VARCHAR、TEXT、BLOB類型
4、如果你的數(shù)據(jù)只有你所知的少量的幾個。最好使用ENUM類型

有關(guān)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可以有效的處理大量的、沒有排序的、沒有索引的輸入。尤其對復(fù)雜查詢的中間結(jié)果處理很有效。（更多參考Understanding Hash Joins）

如何分析SQL語句

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

找出數(shù)據(jù)庫中性能最差的SQL

優(yōu)化哪個表？從何入手？首先需要定位性能瓶頸，找到運行最慢的SQL。可以采用如下步驟：

1. 運行 dbcc freeProcCache 清除緩存

2. 運行你的程序，或者你的SQL或存儲過程，操作數(shù)據(jù)庫

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

SELECT DB_ID(DB.dbid) '數(shù)據(jù)庫名'
      , OBJECT_ID(db.objectid) '對象'
      , QS.creation_time '編譯計劃的時間'
      , QS.last_execution_time '上次執(zhí)行計劃的時間'
      , QS.execution_count '執(zhí)行的次數(shù)'
      , QS.total_elapsed_time / 1000 '占用的總時間（秒）'
      , QS.total_physical_reads '物理讀取總次數(shù)'
      , QS.total_worker_time / 1000 'CPU 時間總量（秒）'
      , QS.total_logical_writes '邏輯寫入總次數(shù)'
      , QS.total_logical_reads N'邏輯讀取總次數(shù)'
      , 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 '執(zhí)行語句'
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