WHERE條件中or與union引起的全表掃描的問題

標籤：des   http   使用   資料   io   問題   

 說起資料庫的SQL語句執行效率的問題，就不得不提where條件陳述式中的or（邏輯或）引起的全表掃描問題，從而導致效率下降。

 

    在以往絕大多數的資料中，大多數人的建議是使用 union 代替 or ，以解決由於使用了 OR 導致的全表掃描。然而，實際是不是如此呢？flymorn就拿5萬多條資料的MSSQL資料庫來測試。

 

    在SQL Server查詢分析器中鍵入如下代碼：

 

SET STATISTICS profile ON
SET STATISTICS io ON
SET STATISTICS time ON 
go
select * from chuzu where c_id>1000 or c_qu=‘沙坪壩區‘ order by c_time desc

 

select * from chuzu where c_id>1000 
union
select * from chuzu where c_qu=‘沙坪壩區‘ order by c_time desc
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF

 

    資料庫設計中，id為主鍵，同時也是叢集索引，qu是普通欄位列，兩個條件中的欄位是不一樣的。

 

    執行計畫如下：

 

    從執行計畫中可以看出，採用了 union 的SQL語句的查詢成本為50.22%，比採用 or 的成本 49.78%稍多，當然這隻是計劃。我們再來看看執行效率：

 

（所影響的行數為 52713 行）
表 ‘chuzu‘。掃描計數 1，邏輯讀 2412 次，物理讀 0 次，預讀 0 次。
SQL Server 執行時間: CPU 時間 = 938 毫秒，耗費時間 = 3222 毫秒。

 

（所影響的行數為 52713 行）
表 ‘chuzu‘。掃描計數 2，邏輯讀 4774 次，物理讀 0 次，預讀 0 次。
SQL Server 執行時間:  CPU 時間 = 1484 毫秒，耗費時間 = 4323 毫秒。

 

    從這樣的資料可以看出，採用了 union 的SQL語句的效率（4323 毫秒）實際上並沒有比採用 or （3222 毫秒） 的高，耗費的時間也要多，採用了 or 的效率反而高出了25%。

 

    如果where條件中的是同一個欄位的話，執行效率也大體如上。

 

SET STATISTICS profile ON
SET STATISTICS io ON
SET STATISTICS time ON 
go
select * from chuzu where c_qu=‘九龍坡區‘ or c_qu=‘沙坪壩區‘ order by c_time desc

 

select * from chuzu where c_qu=‘九龍坡區‘
union
select * from chuzu where c_qu=‘沙坪壩區‘ order by c_time desc
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF

 

   在這樣的執行計畫中，union 成本為 60.75% ，採用 or 的成本為 39.25%。依然是or的效率高。

 

   再來看執行結果：

 

（所影響的行數為 6131 行）
表 ‘chuzu‘。掃描計數 1，邏輯讀 2412 次，物理讀 0 次，預讀 0 次。
SQL Server 執行時間:  CPU 時間 = 203 毫秒，耗費時間 = 635 毫秒。

 

（所影響的行數為 6131 行）
表 ‘chuzu‘。掃描計數 2，邏輯讀 4824 次，物理讀 0 次，預讀 0 次。
SQL Server 執行時間:  CPU 時間 = 360 毫秒，耗費時間 = 798 毫秒。

 

    採用union的執行時間 798 ms ，or的時間是 635ms，效率上來說，依然是 or 的效率高。

 

    總結：我的測試結果正和網上說的相反，也許是因為我的資料量還不夠大，才5萬多的資料；或許當資料量到了百萬千萬級的時候，union 的效率 就會比 or 的高了。

 

     所以，我的理解是在資料量還沒有足夠大，sql語句中還是盡量用 or 條件查詢，因為資料量不大的情況下，即使全表掃描也要比邏輯讀兩次，掃描兩次的時間要少，效率要高；當然，如果你的資料達到百萬層級以上了，那就不要用 or 了，可以用 union 或 union all 代替 or ，以避免因為 or 引起的全表掃描。