應用索引技術優化SQL 語句(Part 1)

一、前言

 

很多資料庫系統效能不理想是因為系統沒有經過整體優化，存在大量效能低下的SQL 語句。這類SQL語句效能不好的首要原因是缺乏高效的索引。沒有索引除了導致語句本身執行速度慢外，更是導致大量的磁碟讀寫操作，使得整個系統效能都受之影響而變差。解決這類系統的首要辦法是優化這些沒有索引或索引不夠好的SQL語句。

 

本文討論和索引相關的有關內容，以及通過分析語句的執行計劃來講述如何應用索引技術來優化SQL 語句。通過分析執行計劃，讀者可以檢查索引是否有用，以及如何建立高效的索引。本文對資料庫管理人員以及資料庫系統開發人員都有一定參考意義。

 

如果讀者不知道應該優化資料庫系統的哪些SQL語句，那麼建議讀者參考筆者的另外一篇文章，《應用Profiler優化SQL Server資料庫系統》。那篇文章介紹如何利用Profiler和Read80trace工具找出資料庫系統中的關鍵的和頻繁執行的SQL語句，你可以把精力花在這些最值得優化的SQL語句上面。

 

二、建立索引的關鍵

 

優化SQL語句的關鍵是儘可能減少語句的logical reads。這裡說的logical reads是指語句執行時需要訪問的單位為8K的資料頁總數。logical reads 越少，其需要的記憶體和CPU時間也就越少，語句執行速度就越快。不言而喻，索引的最大好處是它可以極大減少SQL語句的logical reads數目，從而極大減少語句的執行時間。建立索引的關鍵是索引要能夠大大減少語句的logical reads。一個索引好不好，主要看它減少的logical reads多不多。

 

執行set statistics io命令可以得到SQL語句的logical reads資訊。舉例如下：

 

在Query Analyzer 中執行如下的命令：

 

/***** Script. 1 *****************************/

set statistics io on

select au_id,au_lname ,au_fname 

from pubs..authors where au_lname ='Green'

set statistics io on

/********************************************/

 

輸出結果如下：

 

au_id       au_lname                au_fname            

----------- ---------------------------------------- --------------------

213-46-8915 Green                    Marjorie

(1 row(s) affected)

Table 'authors'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

 

上面的logical reads 1就是指該Select語句的邏輯讀總數是1。Logical reads 越少越好。如果Logical reads很大，而返回的行數很少，也即兩者相差較大，那麼往往意味者語句需要優化。比如語句沒有索引，或索引不夠好等。注意Logical reads和後面的physical reads的區別。Logical reads中包含該語句從記憶體資料緩衝區中訪問的頁數和從物理磁碟讀取的頁數。而physical reads表示那些沒有駐留在記憶體緩衝區中需要從磁碟讀取的資料頁。Read-ahead reads是SQL Server為了提高效能而產生的預讀。預讀可能會多讀取一些資料。 優化的時候我們主要關注Logical Reads就可以了。注意如果physical Reads或Read-ahead reads很大，那麼往往意味著語句的執行時間（duration）裡面會有一部分耗費在等待物理磁碟IO上。

 

二、單欄位索引，組合索引和覆蓋索引

 

顧名思義，單欄位索引是指只有一個欄位的索引，而組合索引指有多個欄位構成的索引。

下面的例子講述建立這些索引的一些技巧，以及如何結合執行計劃判斷SQL語句是否利用了索引。

 

1． 對出現在where子句中的欄位加索引

 

先執行如下的語句建立示例所需要的表：

/**************Script. 2************************************/

use tempdb

go

if exists (select * from dbo.sysobjects where id = object_id(N'[tbl1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [tbl1]

GO

create table tbl1

(學生號 int,學生姓名 varchar(20),性別 char(2), 年齡 int,入學時間 datetime,備註 char(500))

go

declare @i int

set @i=0

declare  @j int

set @j=0

while @i<5000

begin

if (rand()*10>3) set @j=1 else set @j=0

insert into tbl1 values(@i,

 char( rand()*10+100)+char( rand()*5+50)+char( rand()*3+100)+char( rand()*6+80),

@j, 20+rand()*10,convert(varchar(20), getdate()-rand()*3000,112),

char( rand()*9+100)+char( rand()*4+50)+char( rand()*2+130)+char( rand()*5+70))

set @i=@i+1

end

/**************************************************/

 

然後我們看如下的語句應該如何建立索引：

 

/********Script. 3**********************************/

set statistics profile on

set statistics io on

go

select 學生姓名, 入學時間 from tbl1 where 學生號=972

go

set statistics profile off

set statistics io off

go

/****************************************************/

 

注意上面的set statistics profile命令將輸出語句的執行計劃。也許你會問，為什麼不用SET SHOWPLAN_ALL呢？使用SET SHOWPLAN_ALL也是可以的。不過set statistics profile輸出的是SQL 語句的執行時候真正使用的執行計劃，而SET SHOWPLAN_ALL輸出的是預計（Estimate）的執行計劃。使用SET SHOWPLAN_ALL是後面的語句並不會真正執行。

 

上面script輸出結果（部分）如下：

 

學生姓名                 入學時間                                                    

-------------------- ------------------------------------------------------          

g4eQ                 2005-05-29 00:00:00.000                                         

                                                                                      

(1 row(s) affected)                                                                  

                                                                                      

Table 'tbl1'.Scan count 1,logical reads 385, physical reads 0,read-ahead reads 0. 

Rows  Executes  StmtText                                                     

------------------------------------------------------------------------------

1     1         SELECT [學生姓名]=[學生姓名],[入學時間]=[入學時間] FROM [tbl1]

1     1           |--Table Scan(OBJECT:([tempdb].[dbo].[tbl1]), WHERE:([tbl1].

                                                                                     

(2 row(s) affected)                                                                   

 

從上面輸出結果可以看到，這條語句執行時候使用了Table Scan，也就是對整個表進行了全表掃描。全表掃描的效能通常是很差的，要儘量避免。如果上面的select語句是資料庫系統經常執行的關鍵語句， 那麼應該對它建立相應的索引。建立索引的技巧之一是對經常出現在where條件中的欄位建立索引。所以對上面的select語句，應該在學生號欄位上建立單欄位索引idx_學生號：

 

create nonclustered index idx_學生號 on tbl1(學生號)

 

然後再執行Script. 3，部分結果如下：

                                                                       

Table 'tbl1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.          

Rows Executes StmtText                                                                       

---------------------------------------------------------------------------------------------

1    1        SELECT [學生姓名]=[學生姓名],[入學時間]=[入學時間] FROM [tbl1] WHERE [學生號]=@

1    1          |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[tbl1]))    

1    1               |--Index Seek(OBJECT:([tempdb].[dbo].[tbl1].[idx_學生號]), SEEK:([tbl1].

 

上面的結果顯示我們剛剛建立的idx_學生號這個索引確實被使用到了。語句的logical reads極大減少，從沒有索引前的385減少到3，Table Scan也變成了Index Seek，效能極大提高。從上面的例子可以知道，如果你在執行計劃中看到Table Scan或聚集索引的Index Scan（聚集索引的Index Scan相當於Table Scan）， 而且對應的logical reads相當大，那麼就要設法使之變成Index seek。設法避免Table scan或Index scan是優化SQL 語句使用的常用技巧。通常Index Seek需要的logical reads比前兩者要少得多。

 

2．組合索引

 

如果where語句中有多個欄位，那麼可以考慮建立組合索引。例子如下：

 

/*****Script. 4******************************************/

set statistics profile on

set statistics io on

go

select學生姓名, 入學時間 from tbl1

where  入學時間>='20050301' and 入學時間年齡>24

go

set statistics profile off

set statistics io off

go

/*******************************************************/

 

為了提高該語句的效能，可以在入學時間和年齡上建立一個組合索引如下：

 

create nonclustered index idx_入學時間年齡 on tbl1(入學時間,年齡)

 

你也許會問，如果把入學時間和年齡欄位換個位置建立如下的組合索引如何？

 

create nonclustered index idx_年齡入學時間 on tbl1(年齡,入學時間)

 

這個索引沒有前面的好。分析這兩個欄位的唯一性：

 

select count(*) from tbl1 group by 入學時間

select count(*) from tbl1 group by 年齡

 

部分輸出結果如下：

 

distinct_value_of 入學時間 

(2426 row(s) affected)

 

distinct_value_of 年齡

(10 row(s) affected)

 

結果顯示入學時間欄位有2426個唯一值，而年齡欄位只有10個。也就是說入學時間欄位的唯一性比年齡欄位高得多。對於上面的兩個索引分別執行Script. 4，你會發現對第一個索引語句的logical reads是8 而第二個索引導致的logical reads為16，相差了一倍。如果表很大那麼效能的差異可想而知。所以，組合索引中欄位的順序是非常重要的，越是唯一的欄位越是要靠前。另外，無論是組合索引還是單個列的索引，儘量不要選擇那些唯一性很低的欄位。比如說，在只有兩個值0和1的欄位上建立索引沒有多大意義。

 

有時候你要決定為每個相關欄位單獨建立索引還是建立一個組合索引。比如說如果下面的語句經常執行：

 

Select c1, c2,c3 from tblname where c1='abc' and c2=3

Select c1, c3 from tblname where c1='b'

Select c1, c2 from tblname where c2=10

 

應該如何建立索引呢？這取決於各語句的比例。如果大部分語句總是根據c1和c2查詢，那麼一個組合索引（c1＋c2）或者一個覆蓋索引是非常有用的，然後多加一個單獨對c3建立的索引。反之，如果第一個語句執行次數非常少，大部分語句是後面兩種，那麼當然要對c1和c2分別建立索引。你也許會問，對第一種語句，分別對c1和c2建立索引可以嗎？可以。對某些語句SQL Server 可能會分別使用兩個索引（即索引交叉技術）查詢資料然後取其交集得到結果。但��時候SQL Server 未必會使用你建立的全部的單欄位索引。所以如果對單欄位進行索引，建議使用set statistics profile來驗證索引確實被充分使用。logical reads越少的索引越好。

 

 

3．覆蓋索引

 

對於script. 4中的select語句，有沒有更好的索引呢？有的。那就是使用覆蓋索引（covered index）。覆蓋索引能夠使得語句不需要訪問表僅僅訪問索引就能夠得到所有需要的資料。因為聚集索引葉子節點就是資料所以無所謂覆蓋與否，所以覆蓋索引主要是針對非聚集索引而言。不知大家注意到沒有，我們前面討論的執行計劃中除了index seek外，還有一個Bookmark Lookup關鍵字。Bookmark Lookup表示語句在訪問索引後還需要對錶進行額外的Bookmark Lookup操作才能得到資料。也就是說為得到一行資料起碼有兩次IO，一次訪問索引，一次訪問基本表。如果語句返回的行數很多，那麼Bookmark Lookup操作的開銷是很大的。覆蓋索引能夠避免昂貴的Bookmark Lookup操作，減少IO的次數，提高語句的效能。

 

覆蓋索引需要包含select子句和WHERE子句中出現的所有欄位。Where語句中的欄位在前面，select中的在後面。就script. 5中的select語句而言，覆蓋索引如下：

 

create nonclustered index idx_covered on tbl1(入學時間,年齡,學生姓名)

 

然後再執行script. 4，輸出結果如下：

 

Table 'tbl1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.                                 

Rows Executes StmtText                                                                                             

------------------------------------------------------------------------------------------------------

6  1  SELECT [學生姓名]=[學生姓名],[入學時間]=[入學時間] FROM [tbl1] WHERE [入學時間]>=@1 AND [入學時間]

6  1   |--Index Seek(OBJECT:([tempdb].[dbo].[tbl1].[idx_covered]), SEEK:(([tbl1].[入學時間], [tbl1].[年齡])

 

比較一下上面的logical reads，是大大減少了。Bookmark Lookup操作也消失了。所以建立覆蓋索引是減少logical reads提升語句效能的非常有用的優化技巧。

 

實際上索引的建立原則是比較複雜的。有時候你無法在索引中包含了Where子句中所有的欄位。在考慮索引是否應該包含一個欄位時，應考慮該欄位在語句中的作用。比如說如果經常以某個欄位作為where條件作精確匹配返回很少的行，那麼就絕對值得為這個欄位建立索引。再比如說，對那些非常唯一的欄位如主鍵和外來鍵，經常出現在group by，order by中的欄位等等都值得建立索引。因篇幅有限，這裡不再進行展開了。SQL Server的聯機手冊中有很好的相關內容，請讀者自行參考。