T-SQL查詢優化亂彈

  T-SQL查詢優化亂彈 收藏

以下是我整理的一些在SQL Server上實作高效查詢的技巧和提示。經驗之談，不妥之處請大家指正。

1. 關于索引

談資料庫查詢優化必然要講索引。索引是個大話題。想要盡可能提高資料庫系統的性能，設計良好的索引，必然要深入了解索引的結構，這個可以專門開一個話題來 讨論（在《SQL Server 2005技術内幕：存儲引擎》一書中這個話題是用專門的一章來講的）。這次我們主要談查詢技巧，對索引隻要有一個基本的了解即可。

舉個例子來了解索引：

以一本漢語字典為例，字典好比一個表，表中的每個字及其拼音、解釋可以了解為字段，每個字即一行記錄；字典中的字是按照拼音和筆劃順序排列的，是以拼音和 筆劃即字典表的聚集索引鍵（複合鍵）。字典前面的目錄是非聚集索引，包括拼音索引、部首索引、筆劃索引、四角号碼索引等等。

由此：

一、聚集索引隻能有一個 。因為字典表中的字（記錄）不可能既按拼音和筆劃排序又按部首排序。但聚集索引并非隻能建在拼音和筆劃上，比如四号号碼字典或是古時的辭典。

二、非聚集索引可以有多個 。非聚集索引查到的隻是一個指向記錄存放位置的頁碼（這隻是舉例，SQLServer的具體實作略有不同）。

三、在已經被索引覆寫的字段上建索引是一種不必要的浪費 。比如拼音已經被聚集索引覆寫了，前面目錄中的非聚集拼音索引則是多餘。（這一點後面詳述）

（以下兩條涉及索引的結構和關系模型的概念，可以不深究）

四、如果索引鍵不是唯一的，則索引結構中會引入一個附加屬性來保證唯一排序 。比如在部首索引中，相同部首之下的字會按筆劃排序。

五、主鍵與聚集索引鍵并不等價 。前者是一個資料模型的抽象概念，後者是一個資料庫的實體結構。字典表的主鍵是字和拼音的組合鍵，它唯一地決定一個确定讀音的漢字，這也表示字典表中的一條記錄。拼音和筆劃的組合聚集索引鍵隻是字典表的實體存儲方式。很多時候聚集索引鍵與主鍵是相同的，但這并不必然。

了解了索引的結構，我們就很容易明白索引對于高效查詢的重要性。一個沒有索引的表在實體上是一個堆（heap），即一堆無序的資料，比如一個國小生的練字 本。對于小表，堆掃描也足夠快，比如在國小生的練字本上找一個字，我們隻能将整個表周遊一遍，這不困難；但不能想象每次查字典都要将整本字典周遊一遍。所 以，在大表上通常要根據查詢需要建合适的索引。

如果要查一個知道讀音的字，這時可以使用聚集索引：比如“牛”字的拼音是niu2（2表示二聲），我可以直接翻字典的中部（N在字母表的中部），然後根據 翻到的字母去逼近目标值niu2，如果niu2這個拼音的字有多個，我可以再根據聚集索引鍵的第二個字段筆劃順序去找“牛”字，當然這時候通常已經定位到 了一個資料頁（真的是一頁），掃描也是很快的，以至我們通常不會意識到使用聚集索引鍵的第二個字段。這便是Clustered Index Seek。

然而很多人根據讀音查一個字時通常習慣于先在拼音目錄找到這個字的頁碼再去翻相應的頁，因為國小國文教字典使用方法時是這麼教的。這種方法和根據部首查一 個字一樣，是No-clustered Index Seek + Table Lookup。因為拼音目錄的頁數少，查找起來會比聚集索引查找快一些，但這種方法後面要多加一步表查找，總耗時可能比較大。這樣看來，學校教的這種查字 典方法就好比加了一個強制使用拼音索引的查詢提示（hint），讓資料庫引擎放棄聚集索引不用而優先使用拼音字段上的非聚集索引。

在實際的資料庫中，索引發揮作用的地方并不僅限于查找。事實上，WHERE/JOIN/GROUP BY/PARTITION BY/ORDER BY/DISTINCT這些語句都會受益于索引。WHERE略過不說；JOIN基本上可以看作是一種特殊的查找；根據索引字段進行GROUP BY/PARTITION BY/ORDER BY一般效率比較高，尤其是單表查詢時根據聚集索引ORDER BY；用DISTINCT去重也是一種變相查找（找重複值），因而索引也可以發揮作用。

2. SARG

在查詢時為了使用索引，查詢條件也需要滿足一些條件，官方定義為SARG（Search-ARGument，查詢參數）。SARG 運算符包括 =、>、<、>=、<=、IN、BETWEEN，有時還包括 LIKE（在進行字首比對時，如 LIKE 'John%'）。SARG 可以包括由 AND 聯接的多個條件。

舉幾個SARG查詢條件的例子：

拼音 = 'niu2'：前面介紹了，這是Clustered Index Seek。

拼音 > 'niu2'：方法類似，找到niu2這個值，然後把其後的字全部列出，因為這個查詢的選擇性小（即要查的目标資料在整個表中比例大），其實際查詢方法可能是Clustered Index Scan。

<、>=、<=同理。

IN好比是要查一組值，拼音 IN ('niu2', 'fei1', 'long2')的查詢方法跟 = 差不多。而且，如果IN後面是一個子查詢，資料庫的查詢計劃可能會類似于JOIN，還可能使用子查詢的表的索引。

col BETWEEN a AND b邏輯上等價于col >= a AND col <= b，不必多說。

拼音 LIKE 'ni%'的查詢也不難，因為索引是前序排列的。然而如果是 拼音 LIKE '%iu2' 就沒那麼簡單了（這種情況SQL Server 2005另有妙計）。

再舉幾個非SARG的反面例子：

拼音 <> 'niu2'

拼音 NOT IN (niu2, fei1, long2)

這兩種情況，索引就不太容易發揮作用了。

LEN(拼音) >= 3

轉繁體(漢字) = '龍'

漢字 + '寺' = '特' --（假定 + 操作是把兩個漢字左右結合組成一個字）

這樣的查詢更恐怖。查詢引擎隻能老老實實地根據每條記錄計算等式左邊的值再跟右邊的值配置，沒有辦法使用索引。除非查詢引擎能夠明白 轉繁體() 的逆函數是 轉簡體()、+ 運算符的逆操作是 -。在查詢引擎達到這樣的智能之前，我們不如換一種寫法：

拼音 LIKE '___%' --（剛剛說了對于LIKE運算符SQL Server 2005另有妙計，通常LIKE是優于在表字段上使用函數的）

漢字 = 轉簡體('龍') 或 漢字 = '龍'

漢字 = '特' - '寺' 或 漢字 = '牛'

關于AND和OR的差别，想象這兩個查詢條件：

拼音 = 'peng2' AND 部首 = '鳥'：可以先用索引找到peng2再查找部首是鳥字旁，這樣就知道結果是“鵬”而不是“棚”。因為AND兩邊是交集的關系。

拼音 = 'peng2' OR 部首 = '鳥'：這就比較麻煩，查詢引擎可能會先通過拼音索引找peng2再根據部首索引找鳥字旁然後把兩部分并起來，也可能幹脆表掃描。是以OR不是SARG。

綜上，我們應該在查詢條件中盡量使用SARG以便使用索引：

一、避免在表字段上使用函數或運算符 。盡可能找到其反向運算在确定的條件上操作。

二、避免使用<>和NOT。當然這個很多時候無法避免，隻能說盡量避免。

三、将OR表達式轉化為等價的AND表達式或UNION操作。

第三條情況不多見，舉個例子：

(1) SELECT * FROM Employees WHERE HireDate >= '20090101' OR (HireDate >= '20080101' AND Salary < 10000.00)

可以替換為：

(2) SELECT * FROM Employees WHERE HireDate >= '20080101' AND (HireDate >= '20090101' OR Salary < 10000.00)

或：

(3)

SELECT * FROM Employees WHERE HireDate >= '20090101'

UNION

SELECT * FROM Employees WHERE HireDate >= '20080101' AND Salary < 10000.00

其中，(2)可以先用HireDate上的索引進行過濾，(3)可以在兩個查詢上分别使用索引（但UNION操作的代價也比較大）。有些時候，這種改寫可以提高效率。

以上讨論索引時舉了漢語字典的例子。給定一個查詢條件，資料庫查詢引擎查找表的過程，可以近似類比為一個人查字典的過程。分析一個查詢條件，可以想象一下自己查字典，這樣便可以對能否和如何使用索引有一個直覺的了解。

（嚴格地說，一個人查字典的方式是多分查找，而不是資料庫索引查詢采取的B-樹查找。但兩種查找方式很類似，可以近似類比。）

3. 用面向集合的思維方式使用SQL

現在比較流行說程式設計思想。一般的程式設計語言都是采用面向過程的思維方式；當然特殊的還有面向對象、面向事件、面向服務等等，不過淘去其中概念性的說法，本質 上依然是過程化的。因為程式作為一種搞定事情（Problem-Solving）的工具，自然是需要一步一步處理的，這跟人類思維的過程是一緻的。程式設計即 是思維的擴充。

在資料庫（無非一種特殊的應用程式）領域也不例外。各種實用的資料庫系統都對SQL進行了過程化的擴充。這是現實應用開發的需要。

但SQL本身處理的是資料，這是資料庫不同于其它應用程式的地方。程式的整體流程是過程化的，但在局部，處理資料需要一種面向集合的思維方式。很多從應用 程式開發轉到SQL程式設計的程式員往往沒有進行這種思維轉變，因而常常寫出散發着“壞氣味”（Bad Smell）的SQL代碼。

比如某公司現在要為員工加薪，方案是薪資在10000.00以下的加10%，10000.00及以上的加5%。過程式思維的代碼可能是這樣：

UPDATE Employees SET Salary = Salary * 1.10 WHERE Salary < 10000.00

UPDATE Employees SET Salary = Salary * 1.05 WHERE Salary >= 10000.00

（還好沒有用遊标或寫循環-_-|）

細想一下，這會有問題：那些薪資在10000.00以下的但是加5%之後便在10000.00以上的員工将會在加5%之後再加10%。是以正确的寫法是把兩個語句換一下位置。

然而SQL采用面向集合的思維自然會有所努力，它提供了CASE WHEN語句（準确說是表達式）。集合式思維的代碼則是這樣：

UPDATE Employees SET Salary = Salary * CASE WHEN Salary < 10000.00 THEN 1.10 ELSE 1.05 END

（可以看到CASE WHEN ... END這一整體就是一個表達式，它實作的是類似于IF ... ELSE ...語句的作用。）

有些時候需要實作一些複雜的邏輯。比如在HRMS（人力資源管理系統）中需要根據員工的入職時間、工作地點、職位、績效、市場平均薪資等資訊綜合計算員工 的薪資調整金額，甚至可能還要結合企業設定的計劃或政策；或者是在ERP中需要對客戶的姓名、住址、郵箱、電話号碼等敏感資訊進行一個複雜的編碼加密過 程；等等。這種情況下的思維邏輯可能是這樣：

Foreach row In table:

blah...

blah...

blah...

（一段很長的處理）

很多時候程式員會直接把這段僞碼翻譯成這樣的SQL：

WHILE ... --用遊标循環表中每一行

BEGIN

（一段很長的處理，可能包含一些IF ... ELSE ...或者SELECT ... FROM ...甚至是另一個WHILE！！）

END

處理循環并非SQL所擅長的操作。可以想象這樣代碼的執行效率。

用批量操作代替循環操作，不光意味着更好的性能，而且邏輯更為直覺（關系模型中的資料本身即是以集合形式存在的），有時甚至能避免邏輯上的錯誤。

是以，盡量把一條一條記錄循環處理的邏輯寫成批量處理。如果邏輯比較複雜，不妨分成多個步驟的操作，或是封裝在自定義函數裡。除非萬不得已，否則一定不要使用循環。

有本書中講，SQL代碼中出現一次IF便要減一些分（比如-1），出現一次WHILE便要減很多分（比如-10或-50，不誇張）。可以嘗試用這樣的方法為自己的存儲過程打分。

4. 避免在SELECT子句中使用子查詢或包含查詢的标量UDF

想象一個銷售資訊系統，很時候我們都需要根據客戶編号UserID翻譯出客戶姓名UserName，類似如下檢視訂單的情況：

SELECT

  o.OrderID, ... --關于訂單的資訊

  CustomerName = (SELECT u.UserName FROM Users u WHERE u.UserID = o.UserID)

FROM Orders o

WHERE some_condition

或許我們還會把上面那段子查詢封裝在自定義函數中，進而隻需：

  CustomerName = dbo.UserName(o.UserID)

這種情況下，查詢引擎會采用嵌套循環的方式，根據每一條訂單去查詢Users表找到UserName。

相比如下代碼：

SELECT

  o.OrderID, ... --關于訂單的資訊

  CustomerName = u.UserName

FROM Orders o

INNER JOIN Users u

ON o.UserID = u.UserID

WHERE some_condition

最壞的情況下，JOIN會采用嵌套循環的方式執行。如果Orders表和Users表上的UserID字段都建有索引，JOIN可能會以一種更高效的合并聯接方式執行。對于資料量大的表，這種優勢會非常明顯。

是以，除非邏輯上必須，或是為了代碼可讀性值得付出一定的代價，否則，盡量避免在SELECT子句中使用子查詢或包含查詢的标量UDF。

5. 避免臨時資料集過大

SQL Server處理查詢語句的過程中會産生一些臨時資料集，比如子查詢（也包括派生表和CTE），或是每一步JOIN操作。這些臨時資料集需要從磁盤中讀 出，會占用記憶體，在資料量大的時候還會部分寫入tempdb，而磁盤讀寫是主要的性能瓶頸，占用記憶體也不是好事情。是以：

一、隻選擇需要的字段。如果隻需要使用表中的幾個字段，就不要SELECT * FROM ...。

二、盡早過濾。比如：

SELECT ...

FROM A

INNER JOIN B ON A.col1 = B.col1

AND A.col0 = some_value

INNER JOIN C ON B.col2 = C.col2

INNER JOIN ...

WHERE A.col0 = some_value

位置一和位置二的兩個條件隻要寫一個就可以了。而且邏輯上二者是等價的（隻要A和B之間是内聯接）。但是由于臨時資料集的問題，有些情況下寫在位置一的性 能會好于寫在位置二。大概是SQL Server的查詢引擎還不夠聰明。因為從理論上講，查詢引擎的實體處理過程并不等同于SQL代碼的邏輯解釋過程。好的查詢引擎應該能夠明白位置一和位置 二的邏輯等價性，進而采用同樣的執行計劃。

這一條僅供參考。SQL Server的未來版本或許會解決此類問題。在實際開發中，如果需要由此引發的性能問題，就需要在代碼可讀化和執行性能之間做一個平衡了。

三、如子查詢的資料集過大，考慮插入臨時表，分步驟查詢。但要考慮到臨時表的索引問題。

6. 避免使用動态SQL

動态SQL功能很強大，但是難以調試和維護（字元串拼接、無文法高亮）、效率低（難以重用執行計劃）、安全性差（SQL注入）。除非功能上必須，否則盡量避免使用動态SQL。

假如真的需要使用動态SQL，使用sp_executesql的方式優于EXEC()的方式。因為前者有些時候可以重用執行計劃而改善性能，而且允許傳參，資料類型上更安全。

7. 鎖定和并發

對于多使用者的資料庫系統，并發問題也是影響性能的一部分。如果因為事務的鎖定導緻大量的等待和阻塞，性能上的損失可能會遠大于局部語句級的改進。這也是個大話題（而且又是《存儲引擎》一書的專門一章）。

簡單來說，在設計資料庫時便需要根據系統業務的需要設定合理的并發模型和事務隔離機制。并發模型是資料庫級的設定，包括資料庫選項 ALLOW_SNAPSHOT_ISOLATION / READ_COMMITTED_SNAPSHOT，這是需要資料庫設計師和DBA考慮的部分；事務隔離機制可以在會話級指定，使用者要執行查詢時可以用 SET TRANSACTION ISOLATION LEVEL語句修改設定。

比如某些資料統計工作，對資料庫以查詢為主，不希望查詢導緻表鎖定影響業務操作，那最好在查詢的每個表上加提示WITH (NOLOCK)，或是在查詢最初執行SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED（可以在SSMS設定：工具->選項->查詢執行/SQL Server/進階）。這樣避免了查詢表時申請共享鎖。

而對于SQL程式開發來說，需要保證事務盡可能短。在相同的隔離機制下，至少盡量縮短了鎖定時間。

8. 磁盤讀寫（補充内容）

學計算機的人自然都了解階梯式的存貯器性能/成本平衡：CPU的寄存器和Cache、記憶體、磁盤、錄音帶，在這個清單中，越往後的存貯裝置越是價格便宜、容量更大，而讀寫性能則越差。下面這個清單可以對它們的性能差異有一個直覺的認識：

各種操作的計時，2001年夏天在一台典型的1GHz PC上完成：

　　執行單條指令　　　　　　　　　　　　1 納秒 = (1/1,000,000,000) 秒

　　從L1緩存中取一個word　　　　　　　　2 納秒

　　從主記憶體中取一個word　　　　　　　　10 納秒

　　從連續的磁盤位置中取一個word　　　　200 納秒

　　從新的磁盤位置中取一個word（尋址）　8,000,000納秒 = 8毫秒

也就是說，從平均執行時間上說，讓CPU跑上上百條指令，或是讓資料在記憶體裡折騰若幹個來回，也比不上讀寫磁盤上的資料。

對資料庫查詢來說，掃描資料、寫資料、寫日志，這些都是恐怖的性能瓶頸。可以參看上面“避免臨時資料集過大”一節所說。

通過以下兩個會話級選項，可以檢視SQL語句執行時的時間和IO統計。

SET STATISTICS IO ON

SET STATISTICS TIME ON

在搭建資料庫伺服器時，一般的業務資料庫的資料和日志檔案通常會選擇存放在RAID1或RAID5上，但tempdb最好另外放在RAID0上，以減少頻繁的磁盤讀寫開銷。

（完）