SQL Server 索引結構及其使用（二）[轉]

SQL Server 索引結構及其使用（二）

改善SQL語句

　　很多人不知道SQL語句在SQL SERVER中是如何執行的，他們擔心自己所寫的SQL語句會被SQL SERVER誤解。比如：

和執行:

　　一些人不知道以上兩條語句的執行效率是否一樣，因為如果簡單的從語句先後上看，這兩個語句的確是不一樣，如果tID是一個聚合索引，那麼後一句僅僅從表的10000條以後的記錄中查詢就行了；而前一句則要先從全表中查詢看有幾個name=''zhangsan''的，而後再根據限制條件條件tID>10000來提出查詢結果。
　　事實上，這樣的擔心是不必要的。SQL SERVER中有一個“查詢分析優化器”，它可以計算出where子句中的搜尋條件並確定哪個索引能縮小表掃描的搜尋空間，也就是說，它能實現自動優化。
　　雖然查詢優化器可以根據where子句自動的進行查詢優化，但大家仍然有必要了解一下“查詢優化器”的工作原理，如非這樣，有時查詢優化器就會不按照您的本意進行快速查詢。
　　在查詢分析階段，查詢優化器檢視查詢的每個階段並決定限制需要掃描的資料量是否有用。如果一個階段可以被用作一個掃描引數（SARG），那麼就稱之為可優化的，並且可以利用索引快速獲得所需資料。
　　SARG的定義：用於限制搜尋的一個操作，因為它通常是指一個特定的匹配，一個值得範圍內的匹配或者兩個以上條件的AND連線。形式如下：

列名 操作符 <常數 或 變數>

或

<常數 或 變數> 操作符列名

列名可以出現在操作符的一邊，而常數或變數出現在操作符的另一邊。如：

Name=’張三’

價格>5000

5000<價格

Name=’張三’ and 價格>5000

　　如果一個表示式不能滿足SARG的形式，那它就無法限制搜尋的範圍了，也就是SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對於不滿足SARG形式的表示式來說是無用的。
　　介紹完SARG後，我們來總結一下使用SARG以及在實踐中遇到的和某些資料上結論不同的經驗：

1、Like語句是否屬於SARG取決於所使用的萬用字元的型別

如：name like ‘張%’ ，這就屬於SARG

而：name like ‘%張’ ,就不屬於SARG。

原因是萬用字元%在字串的開通使得索引無法使用。

2、or 會引起全表掃描
　　Name=’張三’ and 價格>5000 符號SARG，而：Name=’張三’ or 價格>5000 則不符合SARG。使用or會引起全表掃描。

3、非操作符、函式引起的不滿足SARG形式的語句
　　不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外還有函式。下面就是幾個不滿足SARG形式的例子：

ABS(價格)<5000

Name like ‘%三’

有些表示式，如：

WHERE 價格*2>5000

SQL SERVER也會認為是SARG，SQL SERVER會將此式轉化為：
WHERE 價格>2500/2

但我們不推薦這樣使用，因為有時SQL SERVER不能保證這種轉化與原始表示式是完全等價的。

4、IN 的作用相當與OR

語句：

和

是一樣的，都會引起全表掃描，如果tid上有索引，其索引也會失效。

5、儘量少用NOT

6、exists 和 in 的執行效率是一樣的
　　很多資料上都顯示說，exists要比in的執行效率要高，同時應儘可能的用not exists來代替not in。但事實上，我試驗了一下，發現二者無論是前面帶不帶not，二者之間的執行效率都是一樣的。因為涉及子查詢，我們試驗這次用SQL SERVER自帶的pubs資料庫。執行前我們可以把SQL SERVER的statistics I/O狀態開啟：

（1）

該句的執行結果為：

表 ''sales''。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。
表 ''titles''。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

（2）

第二句的執行結果為：

表 ''sales''。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。
表 ''titles''。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

我們從此可以看到用exists和用in的執行效率是一樣的。

7、用函式charindex()和前面加萬用字元%的LIKE執行效率一樣
　　前面，我們談到，如果在LIKE前面加上萬用字元%，那麼將會引起全表掃描，所以其執行效率是低下的。但有的資料介紹說，用函式charindex()來代替LIKE速度會有大的提升，經我試驗，發現這種說明也是錯誤的：

select gid,title,fariqi,reader from tgongwen
　　　　　　　　　where charindex(''刑偵支隊'',reader)>0 and fariqi>''2004-5-5''

用時：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

用時：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

8、union並不絕對比or的執行效率高
　　我們前面已經談到了在where子句中使用or會引起全表掃描，一般的，我所見過的資料都是推薦這裡用union來代替or。事實證明，這種說法對於大部分都是適用的。

用時：68秒。掃描計數 1，邏輯讀 404008 次，物理讀 283 次，預讀 392163 次。

用時：9秒。掃描計數 8，邏輯讀 67489 次，物理讀 216 次，預讀 7499 次。

看來，用union在通常情況下比用or的效率要高的多。

　　但經過試驗，筆者發現如果or兩邊的查詢列是一樣的話，那麼用union則反倒和用or的執行速度差很多，雖然這裡union掃描的是索引，而or掃描的是全表。
　

用時：6423毫秒。掃描計數 2，邏輯讀 14726 次，物理讀 1 次，預讀 7176 次。

用時：11640毫秒。掃描計數 8，邏輯讀 14806 次，物理讀 108 次，預讀 1144 次。

9、欄位提取要按照“需多少、提多少”的原則，避免“select *”
　　我們來做一個試驗：

用時：4673毫秒

用時：1376毫秒

用時：80毫秒

　　由此看來，我們每少提取一個欄位，資料的提取速度就會有相應的提升。提升的速度還要看您捨棄的欄位的大小來判斷。

10、count(*)不比count(欄位)慢
　　某些資料上說：用*會統計所有列，顯然要比一個世界的列名效率低。這種說法其實是沒有根據的。我們來看：

用時：1500毫秒

用時：1483毫秒

用時：3140毫秒

用時：52050毫秒

　　從以上可以看出，如果用count(*)和用count(主鍵)的速度是相當的，而count(*)卻比其他任何除主鍵以外的欄位彙總速度要快，而且欄位越長，彙總的速度就越慢。我想，如果用count(*)， SQL SERVER可能會自動查詢最小欄位來彙總的。當然，如果您直接寫count(主鍵)將會來的更直接些。

11、order by按聚集索引列排序效率最高
　　我們來看：（gid是主鍵，fariqi是聚合索引列）：

用時：196 毫秒。 掃描計數 1，邏輯讀 289 次，物理讀 1 次，預讀 1527 次。

用時：4720毫秒。 掃描計數 1，邏輯讀 41956 次，物理讀 0 次，預讀 1287 次。

用時：4736毫秒。 掃描計數 1，邏輯讀 55350 次，物理讀 10 次，預讀 775 次。

用時：173毫秒。 掃描計數 1，邏輯讀 290 次，物理讀 0 次，預讀 0 次。

用時：156毫秒。 掃描計數 1，邏輯讀 289 次，物理讀 0 次，預讀 0 次。

　　從以上我們可以看出，不排序的速度以及邏輯讀次數都是和“order by 聚集索引列” 的速度是相當的，但這些都比“order by 非聚集索引列”的查詢速度是快得多的。
　　同時，按照某個欄位進行排序的時候，無論是正序還是倒序，速度是基本相當的。

12、高效的TOP
　　事實上，在查詢和提取超大容量的資料集時，影響資料庫響應時間的最大因素不是資料查詢，而是物理的I/0操作。如：

　　這條語句，從理論上講，整條語句的執行時間應該比子句的執行時間長，但事實相反。因為，子句執行後返回的是10000條記錄，而整條語句僅返回10條語句，所以影響資料庫響應時間最大的因素是物理I/O操作。而限制物理I/O操作此處的最有效方法之一就是使用TOP關鍵詞了。TOP關鍵詞是SQL SERVER中經過系統優化過的一個用來提取前幾條或前幾個百分比資料的詞。經筆者在實踐中的應用，發現TOP確實很好用，效率也很高。但這個詞在另外一個大型資料庫ORACLE中卻沒有，這不能說不是一個遺憾，雖然在ORACLE中可以用其他方法（如：rownumber）來解決。在以後的關於“實現千萬級資料的分頁顯示儲存過程”的討論中，我們就將用到TOP這個關鍵詞。
　　到此為止，我們上面討論瞭如何實現從大容量的資料庫中快速地查詢出您所需要的資料方法。當然，我們介紹的這些方法都是“軟”方法，在實踐中，我們還要考慮各種“硬”因素，如：網路效能、伺服器的效能、作業系統的效能，甚至網路卡、交換機等。

（待續...）