深入了解最佳化SQL查詢-如何寫出高效能SQL語句的具體分析

深入了解最佳化SQL查詢-如何寫出高效能SQL語句的具體分析：

1、 首先要搞清楚什麼叫執行計畫？

執行計劃是資料庫根據SQL語句和相關表格的統計資料所作出的查詢方案，這個方案是由查詢優化器自動分析產生的，例如一條SQL語句如果用來從一個10萬筆記錄的表中查1條記錄，那查詢優化器會選擇「索引查找」方式，如果該表進行了歸檔，目前只剩下5000條記錄了，那查詢優化器就會改變方案，採用「全表掃描」方式。

可見，執行計劃並不是固定的，它是「個人化」的。產生一個正確的「執行計劃」有兩點很重要：

(1)    SQL語句是否清楚告訴查詢最佳化器它想做什麼？

(2)    查詢最佳化器所得到的資料庫統計資料是否是最新的、正確的？

 

2、統一SQL語句的寫法

# 對於以下兩句SQL語句，程式設計師認為是相同的，資料庫查詢最佳化器認為是不同的。

select*from dual 

select*From dual

其實就是大小寫不同，查詢分析器就認為是兩句不同的SQL語句，必須進行兩次解析。產生2個執行計劃。所以身為程式設計師，應該要保證相同的查詢語句在任何地方都一致，多一個空格都不行！

3. 不要把SQL語句寫得太複雜

# 我常看到，從資料庫捕捉到的一條SQL語句印出來有2張A4紙這麼長。一般來說這麼複雜的語句通常都是有問題的。我拿著這2頁長的SQL語句去請教原作者，結果他說時間太長，他一時也看不懂了。可想而知，連原作者都有可能看糊塗的SQL語句，資料庫也一樣會看糊塗。

一般，將一個Select語句的結果當作子集，然後從該子集中再進行查詢，這種一層嵌套語句還是比較常見的，但是根據經驗，超過3層嵌套，查詢優化器就很容易給出錯誤的執行計劃。因為它被繞暈了。像這種類似人工智慧的東西，終究比人的分辨力要差些，如果人都看暈了，我可以保證資料庫也會暈的。

另外，執行計劃是可以重複使用的，越簡單的SQL語句被重複使用的可能性越高。而複雜的SQL語句只要有一個字元改變就必須重新解析，然後再把這一大堆垃圾塞在記憶體裡。可想而知，資料庫的效率會何等低下。

4、 使用「臨時表」暫存中間結果

簡化SQL語句的重要方法就是採用臨時表暫存中間結果，但是，臨時表的好處遠遠不止這些，將臨時結果暫存在臨時表，後面的查詢就在tempdb中了，這可以避免程序中多次掃描主表，也大大減少了程式執行中“共享鎖”阻塞“更新鎖”，減少了阻塞，提高了並發效能。

5. OLTP系統SQL語句必須採用綁定變數

select*from orderheader where changetime >'2010-10-20 00:00:01' 
select*from orderheader where changetime >'2010-09-22 00:00:01'

# 以上兩句語句，查詢最佳化器認為是不同的SQL語句，需要解析兩次。如果採用綁定變數

select*from orderheader where changetime >@chgtime

@chgtime變數可以傳入任何值，這樣大量的類似查詢可以重複使用該執行計劃了，這可以大大降低資料庫解析SQL語句的負擔。一次解析，多次重複使用，是提高資料庫效率的原則。

6、 綁定變數窺測

# 事物都存在兩面性，綁定變數對大多數OLTP處理是適用的，但也有例外。例如where條件中的欄位是「傾斜欄位」的時候。

「傾斜字段」指該列中的絕大多數的值都是相同的，例如一張人口調查表，其中「民族」這列，90%以上都是漢族。那如果一個SQL語句要查詢30歲的漢族人口有多少，那麼「民族」這列必然要放在where條件裡。這時候如果採用綁定變數@nation會存在很大問題。

試想如果@nation傳入的第一個值是“漢族”，那麼整個執行計劃必然會選擇表掃描。然後，第二個值傳入的是“布依族”，按理說“布依族”佔的比例可能只有萬分之一，應該採用索引查找。但是，由於重用了第一次解析的「漢族」的那個執行計劃，那麼第二次也將採用表格掃描方式。這個問題就是著名的“綁定變數窺測”，建議對於“傾斜欄位”不要採用綁定變數。

7. 只在必要的情況下才使用begin tran

SQL Server中一句SQL語句預設就是一個事務，在該語句執行完成後也是預設commit的。其實，這就是begin tran的一個最小化的形式，好比在每句語句開頭隱含了一個begin tran，結束時隱含了一個commit。

有些情況下，我們需要明確聲明begin tran，例如做「插、刪、改」操作需要同時修改幾個表，要求要嘛幾個表都修改成功，要嘛都不成功。 begin tran 可以起到這樣的作用，它可以把若干SQL語句套在一起執行，最後再一起commit。好處是保證了數據的一致性，但任何事情都不是完美無缺的。 Begin tran付出的代價是在提交之前，所有SQL語句鎖住的資源都不能釋放，直到commit掉。

可見，如果Begin tran套住的SQL語句太多，那資料庫的效能就糟了。在該大事務提交之前，必然會阻塞別的語句，造成block很多。

Begin tran所使用的原則是，在確保資料一致性的前提下，begin tran 套住的SQL語句越少越好！有些情況下可以採用觸發器同步數據，不一定要用begin tran。

8. 有些SQL查詢語句要加上nolock

在SQL語句中加nolock是提高SQL Server並發效能的重要手段，在oracle中並不需要這樣做，因為oracle的結構更為合理，有undo表空間保存“資料前影”，如果在修改中還未commit，那麼你讀到的是它修改之前的副本，該副本放在undo表空間中。這樣，oracle的讀、寫可以做到互不影響，這也是oracle 廣受稱讚的地方。 SQL Server 的讀取、寫入是會互相阻塞的，為了提高並發效能，對於一些查詢，可以加上nolock，這樣讀的時候可以允許寫，但缺點是可能讀到未提交的髒資料。使用 nolock有3個原則。

(1)    查詢的結果用於「插、刪、改」的不能加上nolock ！

(2)    查詢的表屬於頻繁發生頁分裂的，慎用nolock ！

(3)    使用臨時表一樣可以保存“資料前影”，並起到類似oracle的undo表空間的功能，

能採用臨時表提高並發效能的，不要用nolock 。

9. 聚集索引沒有建在資料表的順序欄位上，該表容易發生頁分割

例如訂單表，有訂單編號orderid，也有客戶編號contactid，那麼聚集索引該加在哪個欄位上呢？對於該表，訂單編號是順序添加的，如果在orderid上加聚集索引，新增的行都是添加在末尾，這樣不容易經常產生頁分裂。然而，由於大多數查詢都是根據客戶編號來檢查的，因此，將聚集索引加在contactid上才有意義。而contactid對於訂單表而言，並非順序欄位。

例如「張三」的「contactid」是001，那麼「張三」的訂單資訊必須都放在這張表的第一個資料頁上，如果今天「張三」新下了一個訂單，那該訂單資訊不能放在表格的最後一頁，而是第一頁！如果第一頁放滿了呢？很抱歉，表格所有資料都要往後移動為這條記錄騰地方。

SQL Server的索引和Oracle的索引是不同的，SQL Server的聚集索引其實是對資料表依照聚集索引欄位的順序進行了排序，相當於oracle的索引組織表。 SQL Server的聚集索引就是表格本身的一種組織形式，所以它的效率是非常高的。也因為此，插入一筆記錄，它的位置不是隨便放的，而是要按照順序放在該放的資料頁，如果那個資料頁沒有空間了，就引起了頁分裂。所以很顯然，聚集索引沒有建在表的順序欄位上，該表容易發生頁分裂。

曾經碰到過一個情況，一位哥們的某張表重建索引後，插入的效率大幅下降了。估計情況大概是這樣的。該表的聚集索引可能沒有建在表的順序欄位上，該表經常被歸檔，所以該表的資料是以一種稀疏狀態存在的。例如張三下過20張訂單，而最近3個月的訂單只有5張，歸檔策略是保留3個月數據，那麼張三過去的15張訂單已經被歸檔，留下15個空位，可以在insert發生時重新被利用。在這種情況下由於有空位可以利用，就不會發生頁分裂。但是查詢效能會比較低，因為查詢時必須掃描那些沒有資料的空位。

重建聚集索引後情況改變了，因為重建聚集索引就是把表中的資料重新排列一遍，原來的空位沒有了，而頁的填充率又很高，插入資料經常要發生頁分裂，所以效能大幅下降。

對於聚集索引沒有建在順序欄位上的表，是否要給與比較低的頁填充率？是否要避免重建聚集索引？是一個值得考慮的問題！

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

11、使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

12、数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1)    连接字段尽量选择聚集索引所在的字段

(2)    仔细考虑where条件，尽量减小A、B表的结果集

(3)    如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

以上是深入了解最佳化SQL查詢-如何寫出高效能SQL語句的具體分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！