SQL Server 查詢效能最佳化——索引與SARG（二）

 

接 SQL Server 查詢效能最佳化——索引與SARG（一）

對於非SARG語句，SQL SERVER 必須評估每一條記錄以決定它是否符合WHERE子句的條件。所以索引對於採用非SARG條件的查詢通常沒什麼用處。而通過非SARG語句通常包含以下操作： NOT、!=、<>、!>、!< 、NOT EXISTS 、NOT IN 和NOT LIKE 等，以及上述提及的“%IS%”，其中LIKE使用方法會造成全表掃描（TARLE SCAN）或是聚集掃描而降低效能．

建立SQL Server 查詢效能最佳化——索引與SARG（一）中開頭部分建立索引中的索引1，3

--例一、SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY] where COP_G_NO like '%79'

 

表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數1，邏輯讀取1306 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 

 

 

--例二、  SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY] with(index(idx_wbk_pde_list_cop_g_no))where COP_G_NO like '0016%' 

 

 

表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數1，邏輯讀取925 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 

 

 

-----例三、SELECT *  FROM [WBK_PDE_LIST_ORG_HISTROY]  where COP_G_NO like '0016%'

 

表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數1，邏輯讀取1306 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 

 

使用索引	查詢語句	查詢記錄數量	執行成本
全表掃描	例一	916	1.03687
指定使用非叢集索引	例二	919	2.24756
使用叢集索引	例三	919	1.03687

從上表中的例一、例三中可以看出全表掃描與叢集索引掃描的執行成本是一樣的，或相差不大。而例二中，使用了指定的非叢集索引，由於使用非叢集索引尋找 資料，會有一個RID索引值尋找（或稱Bookup Lookup）隨機操作，所以當查詢的記錄數量越多，則執行成本就越大。

 

 

下面來講講一些常見的WHERE子句用法使用不當的情景，並探討較正確的用法：

。對WHERE子句做運算。
。負向查詢。
。對WHERE子句使用函數。
。使用OR 操作。

一、 不要對WHERE子句做運算
先以簡單的範例來比較SARG 和非SARG ，你可以通過Managemenet studio來查看查詢最佳化程式是否可以有效分析該語句。

  1 、沒有任何索引

 

---例一：在WHERE中做運算SELECT  *  FROM [WBK_PDE_LIST_ORG_HISTROY] where wbook_no+G_NO='BE40494245002011844'---例二：在WHERE中不做運算
SELECT * FROM [WBK_PDE_LIST_ORG_HISTROY] where wbook_no='BE404942450020' and g_no='11844'

 

   1) 例一與例二查詢的IO讀寫數

  表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數1，邏輯讀取1306 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

   2）例一與例二的查詢執行計畫成本

 

 

 

 2、只建立叢集索引。

ALTER TABLE [dbo].[WBK_PDE_LIST_ORG_HISTROY] ADD  CONSTRAINT [PK_WBK_PDE_LIST_ORG_HISTROY] PRIMARY KEY CLUSTERED ([WBOOK_NO] ASC,[G_NO] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]GO

 

   1) 例一與例二查詢的IO讀寫數

    表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數1，邏輯讀取1314 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 

   2）例一與例二的查詢執行計畫成本

 

 

 3  建立SQL Server 查詢效能最佳化——索引與SARG（一）中開頭部分建立索引中的索引1（叢集索引）和索引5（非叢集索引）

 

 

 

 

   1) 例一的情況同上面2.

   2) 例二查詢的IO讀寫數

表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數0，邏輯讀取3 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

   3）例二的查詢執行計畫成本

 

從三個執行計畫中，可以看到明顯的差異。

因為SARG 的寫法讓查詢最佳化程式可以直接對比條件常量，可以有效地利用在WHERE子句中的列名欄位上建立的叢集索引或非叢集索引。而非SARG 則因為需要運算才知道資料是否符合，導致無法直接使用索引，因而採用叢集索引掃描方式、全表掃描方式、索引掃描方式。

對於第一個查詢語句，由於不符合SARG格式，所以你可以看到查詢最佳化查詢並沒有有效利用索引。

 

第一種情況，查詢最佳化查詢對於例一與例二都使用了全表掃描方式，從中可以看出整個資料表有共有1306頁，

而此查詢語句的邏輯讀是1306次，與總資料頁差不多，對整個表進行了掃描。與查詢計劃中所示一樣。

第二種情況，由於沒有了idx_WBK_PDE_LIST_COP_G_NO索引，只有叢集索引PK_WBK_PDE_LIST_ORG_HISTROY。從中可以看出PK_WBK_PDE_LIST_ORG_HISTROY的索引頁共有1306頁，而此查詢語句的邏輯讀是1314次，與總索引頁差不多，也是叢集索引掃描，與查詢計劃中所示一樣。

對於第三種情況，由於符合SARG 格式，而且WHERE子句後面的條件中的欄位中都建立有索引，所以你可以看到查詢最佳化程式會有效地利用先前建立的叢集索引（索引1）進行叢集索引尋找。速度非常快。