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

四、分析執行計劃建立索引

 

根據語句的執行計劃來判斷應該對什麼表建立什麼索引，是常用優化技巧。其實文章前面的例子已經告訴讀者如何結合statistics profile 和statistics IO語句的輸出來建立索引。這裡分析一個稍微複雜一些的例子。

 

SQL語句如下：

SELECT CurrentseNo FROM v_ptdata_edss WHERE MRN = @P1 

 

Statistics IO的輸出如下：

 

Table 'ptseoutpat'. Scan count 2, logical reads 8, physical reads 0, read-ahead reads 0.

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

 

部分執行計劃如下：

 

Rows    Executes StmtText                                                                                      

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

0       1        SELECT CurrentseNo FROM v_ptdata_edss WHERE MRN = @P1                                         

0       1          |--Nested Loops(Inner Join, OUTER REFERENCES:([ptdata].[CurrentseNo]))                      

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

1       1               |    |--Filter(WHERE:(Convert([ptdata].[PatExtID])=[@P1]))                             

571955  1               |         |--Index Scan(OBJECT:([TTSH_Neon_ADT].[dbo].[ptdata].[PK_ptdata]))           

0       1               |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009], [Expr1010], [Expr1011]))     

2       1                    |--Merge Interval                                                                 

2       1                    |    |--Sort(TOP 2, ORDER BY:([Expr1012] DESC, [Expr1013] ASC, [Expr1009] ASC, [Exp

2       1                    |         |--Compute Scalar(DEFINE:([Expr1012]=4&[Expr1011]=4 AND NULL=[Expr1009],

2       1                    |              |--Concatenation                                                   

1       1                    |                   |--Compute Scalar(DEFINE:([Expr1006]=NULL, [Expr1007]=NULL, [Ex

1       1                    |                   |    |--Constant Scan                                         

1       1                    |                   |--Compute Scalar(DEFINE:([Expr1009]='Jan  1 1900 12:00AM', [Ex

1       1                    |                        |--Constant Scan                                         

0       2                    |--Index Seek(OBJECT:([TTSH_Neon_ADT].[dbo].[ptseoutpat].[ptseoutpat1]), SEEK:([pts

                                                                                                                

分析的關鍵是：

 

步驟1）找出最昂貴的表（也就是logical reads最多的表），是'ptdata' 表。

 

步驟2）從執行計劃中找出對ptdata表的相應的操作，通常是左邊行數最多的那一行如上圖中的標誌行。對錶的操作是index scan操作。

 

步驟3）根據操作判斷如何建立index或如何改寫語句。從執行計劃中我們看到index scan之後的操作也就是下面的filter操作把資料大大減少了：

 

Filter(WHERE:(Convert([ptdata].[PatExtID])=[@P1])) 

 

一般情況下，對這個欄位建立索引問題就解決了。但對我們的例子語句而言還不夠。實際上PatExtID欄位已經有索引了。那麼為什麼用index scan而不用index seek呢？ 後來發現原因是傳遞的引數@P1和表欄位PatExtID的型別是不一致的。@P1是nvarchar型別，而PatExtID是varchar型別。這導致了SQL Server 產生了對索引欄位進行index scan的Convert操作。解決方法很簡單，把傳遞的引數改成varchar或把表欄位型別改成nvarchar，使得它們型別一致就可以了。

 

五．語句的寫法影響SQL Server 能否利用索引

 

僅僅有索引是不夠的。語句的寫法會影響SQL Server 對索引的選擇。比如下面的語句：

 

select  學生姓名, 入學時間 from tbl1 where DATEDIFF(mm,'20050301',入學時間)=1

 

理所當然，需要在入學時間欄位上建立索引：

 

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

 

然後執行如下script. 5看看該索引是否有用：

/******Script. 5***********************************/

set statistics profile on

set statistics io on

go

select  學生姓名, 入學時間 from tbl1 where DATEDIFF(mm,'20050301',入學時間)=1

go

set statistics profile off

set statistics io off

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

 

語句的部分輸出如下：

 

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

Rows  Executes    StmtText                                                             

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

56    1          select  學生姓名, 入學時間 from tbl1 where DATEDIFF(mm,'20050301',入學

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

 

不幸的是，是Table Scan，剛建立的索引並沒有被使用。這是因為WHERE語句中的DATEDIFF函式引起的。因為函式作用在索引欄位上， SQL Server 無法直接利用索引定位資料，必須對該欄位所有的值運算該函式才能得知函式結果是否滿足where條件。在這種情況下，Table Scan是最好的選擇。為了使用索引，可以把語句改成如下的樣子：

 

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

  where 入學時間>='20050401' and 入學時間

 

把該語句替換script. 5中select語句然後執行該script，結果如下：

 

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

Rows Executes StmtText                                                                  

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

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

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

56   1         |--Index Seek(OBJECT:([tempdb].[dbo].[tbl1].[idx_入學時間]), SEEK:([tbl1].

 

可以看到Table Scan變成了Index seek， Logical Reads 也減少到58。從上面的例子可以知道，為了利用索引，不要對where語句中的欄位直接使用各種函式或表示式。要儘量把函式或表示式放在操作符的右邊。

 

再多舉一些例子，下面的where語句寫法是不好的：

 

Where substring(colum1,1,4)>'ddd'

Where convert(varchar(200),column1)>'aaa'

 

如果你實在無法避免上面的情況，而相關的語句又是資料庫系統的關鍵語句，那麼建議你從系統設計的高度來考慮問題。比方說，改變表的結構等，使得不再需要在where子句中的欄位上直接使用函式或表示式等。

 

使用前置百分號或不等號也是不好的Where寫法：

 

Where column1 like ‘%abc%’

Where column1 <> 'bb'

 

第一個where語句中因為第一個百分號會導致SQL Server 進行索引掃描（index scan）或Table Scan。要儘量不使用前置百分號。比方說改成如下的語句就會好得多：

 

Where column1 like ‘abc%’

 

再多看一個例子：

 

Where column1 ＝2 OR column2＝30

 

這個where語句中如果column1 和column2中任何一個欄位沒有索引，那麼整條語句就會導致全表掃描。（想一想為什麼？）所以在有OR的where語句要特別注意OR兩邊的欄位都要有必要的索引。