實戰：上億資料如何秒查？

最近在忙着優化集團公司的一個報表。優化完成後，報表查詢速度有從半小時以上(甚至查不出)到秒查的質變。從修改sql查詢語句邏輯到決定建立存儲過程實作，花了我3天多的時間，在此總結一下，希望對朋友們有幫助。

資料背景

首先，項目是西門子中國在我司實施部署的mes項目，由于項目是在産線上運作(3 years+)，資料累積很大。在項目的資料庫中，大概上億條資料的表有5個以上，千萬級資料的表10個以上，百萬級資料的表，很多...

(曆史問題，當初實施無人監管，無人監控資料庫這塊的性能問題。ps:我剛入職不久...)

不多說，直接貼西門子中國的開發人員在我司開發的ssrs報表中的sql語句：

這個查詢語句，實際上通過我的檢測和調查，在b/s系統前端已無法查出結果，半小時，一小時 ... 。因為我直接在sql查詢分析器查，半小時都沒有結果。

(原因是裡面對一張上億級資料表和3張千萬級資料表做全表掃描查詢)

不由感慨，西門子中國的素質(或者說責任感)就這樣？

下面說說我的分析和走的彎路(思維誤區)，希望對你也有警醒。

探索和誤區

首先相關表的索引，沒有建全的，把索引給建上。

索引這步完成後，發現情況還是一樣，查詢速度幾乎沒有改善。後來想起相關千萬級資料以上的表，都還沒有建立表分區。于是考慮建立表分區以及資料複制的方案。

這裡有必要說明下：我司報表用的是一個專門的資料庫伺服器，資料從産線訂閱而來。就是常說的“讀寫分離”。

如果直接在原表上建立表分區，你會發現執行表分區的事物會直接死鎖。原因是：表分區操作本身會鎖表，産線還在推資料過來，這樣很容易“阻塞”，“死鎖”。

我想好的方案是：建立一個新表(空表)，在新表上建好表分區，然後複制資料過來。

正打算這麼幹。等等！我好像進入了一個嚴重的誤區！

分析： 原sql語句和業務需求，是對産線的資料做産品以及序列号的追溯，關鍵是查詢條件裡沒有有規律的"條件"(如日期、編号)，貿然做了表分區，在這裡幾乎沒有意義！反而會降低查詢性能！

好險！還是一步一步來，先做sql語句分析。

一、對原sql語句的分析

1、查詢語句的where條件，有大量@var in ... or (@var ='') 的片段

2、where條件有like '%'+@var+'%'

3、where條件有 case ... end 函數

4、多次連接配接同一表查詢，另外使用本身已嵌套的視圖表，是不是必須，是否可替代？

5、sql語句有号，視圖中也有号出現

二、優化設計

首先是用存儲過程改寫，好處是設計靈活。

核心思想是：用一個或多個查詢條件(查詢條件要求至少輸入一個)得到臨時表，每個查詢條件如果查到集合，就更新這張臨時表，最後彙總的時候，隻需判斷這個臨時表是否有值。以此類推，可以建立多個臨時表，将查詢條件彙總。

這樣做目前來看至少兩點好處：

1、省去了對變量進行 =@var or (@var='')的判斷；

2、抛棄sql拼接，提高代碼可讀性。

再有就是在書寫存儲過程，這個過程中要注意：

1、盡量想辦法使用臨時表掃描替代全表掃描；

2、抛棄in和not in語句，使用exists和not exists替代；

3、和客戶确認，模糊查詢是否有必要，如沒有必要，去掉like語句；

4、注意建立适當的，符合場景的索引；

5、踩死 "*" 号；

6、避免在where條件中對字段進行函數操作；

7、對實時性要求不高的報表，允許髒讀(with(nolock))。

三、存儲過程

如果想參考優化設計片段的詳細内容，請參閱sql代碼：

雖然犧牲了代碼的可讀性，但創造了性能價值。本人水準有限，還請各位不吝賜教！

最後，将ssrs報表替換成此存儲過程後，sql查詢分析器是秒查的。b/s前端用時1~2秒！

四、總結

平常的你是否偶爾會因急于完成任務而書寫一堆性能極低的sql語句呢？寫出可靠性能的sql語句不難，難的是習慣。

本文的優化思想很簡單，關鍵點是避免全表掃描 & 注重sql語句寫法 & 索引，另外，如果你查詢的表有可能會在查詢時段更新，而實際業務需求允許髒讀，可加with(nolock)預防查詢被更新事物阻塞。