改進數據庫的查詢性能
導讀：數據庫的查詢功能，其性能終究是有限的。即使我們對數據庫進行了***配置，對數據表設計再三斟酌，然而一旦面臨海量數據，且返回結果集較大的時候，常規的查詢語句就無能為力了。一般說來，當返回的結果集超過總數量的40%時，數據庫層面上的優化就顯得束手無策了。此時，我們應該考慮從sql語句和程序業務上著手。下文中就改進數據庫查詢性能為大家作出了詳細的分析方案。
一、 總體思路
通過SQL語句“set rowcount 每頁記錄數”，并指定每頁記錄數，每次只查詢符合條件記錄集中指定的記錄數，以達到分頁的目的。由于查詢功能一般應用在平臺界面中，如果通過分頁的方式，可以使得單位查詢的速度顯著提高。同時，返回的結果集也顯著減少，這降低了一次查詢消耗內存的容量，對于界面的刷新速度也有明顯的提高。由于分頁查詢將原來一次查詢的總時間，通過分頁的方式，分割為每個小段，因此對于用戶而言，每次獲得結果的時間就很短了，這在界面與交互設計中，從考慮用戶體驗的角度出發，也是非常合理的。
由于該方法需要指定每頁記錄數，因此需要被查詢的目的表必須具備一個標識唯一值的字段，并將該字段建立索引，以作為查詢和排序的條件。在數據庫設計中，有很多種創建標識字段的方法。最簡單地莫過于創建Identity字段。當然這種方式的問題也多多，這里不再贅述。也可以寫一個存儲過程，負責生成唯一標識的ID。
二、 實現方案
要進行分頁查詢，首先需要確定每頁的記錄數。根據各種業務和局方的不同需求，同時各個局方話單量也各有不同，所以，每頁記錄數值應放到AAA.ini配置文件中，便于靈活配置。
在分頁查詢之前，我們需要知道每個月的話單應該的總頁數，可以先獲得查詢目的表的總記錄數（以Ctsi業務 (固網點對點短信)為例，下同），SQL語句如下：
select count(1) from CtsiInfoRecord where 條件
注：后面的查詢語句中均應包括查詢條件，為清楚表現sql語句，本文一律省略該條件。
然后通過總記錄數和每頁記錄數，獲得每個月分頁查詢的總頁數。
由于我們的業務主要使用微軟的Sql Server2000和sybase。因此，實現分頁查詢有兩種方式。具體實現方案如下：
方案一：通過建立臨時表結合分頁查詢
在微軟的Sql Server中，在其T-SQL中引入了top語法，通過該語法可以非常方便的實現分頁查詢，sql語句為（以Ctsi業務為例）：
select top 每頁記錄數 * from CtsiInfoRecord01 where IdCdr not in
(select top 頁數*每頁記錄數 IdCdr from CtsiInfoRecord01 order by IdCdr)
order by IdCdr
在實際查詢時，只需要修改子查詢的top記錄數即可。
遺憾的是，該top語法在sybase中并不支持。相對應的語法為set rowcount 記錄數。但該語法不能放在子查詢語句中，因此，上述的方法無法實現。
根據該方法的實現思路，引入臨時表，并結合分頁查詢來實現，sql語句如下：
set rowcount頁數*每頁記錄數
select IdCdr into #ctsitable from CtsiInfoRecord01 order by IdCdr
set rowcount 每頁記錄數
select * from CtsiInfoRecord01 where IdCdr not in
(select IdCdr from #ctsitable ) order by IdCdr
drop table #ctsitable
注：#ctsitable為臨時庫tempdb中的臨時表；
在sybase中，不支持在子查詢中引入order by；
如果查詢***頁，則不需要建立臨時表，直接查詢即可：
set rowcount 每頁記錄數 select * from CtsiInfoRecord01 order by IdCdr
方案二：直接根據IdCdr條件分頁查詢
假定話單表的唯一標識字段為IdCdr。如果通過order by進行排序（默認升序），在每頁記錄數固定以及查詢條件相同的前提下，下一頁查詢的所有記錄，其IdCdr值必然大于上一頁末記錄的IdCdr。如果我們每次查詢后，獲得了末記錄的IdCdr值，然后在下一次查詢時，引入該條件，得到的結果必然是根據條件查詢出來的下一頁結果。方法如下：
set rowcount 每頁記錄數
select * from CtsiInfoRecord where IdCdr > 上一頁末記錄IdCdr值 order by IdCdr
如果是上一頁查詢，則剛好相反，需要獲得下一頁首記錄的IdCdr值：
set rowcount 每頁記錄數
select * from CtsiInfoRecord where IdCdr < 下一頁首記錄IdCdr值
注：如果查詢首頁，則將IdCdr值條件刪掉。
如果查詢末頁，在刪掉IdCdr值條件的同時，將排序改為降序的方式。
兩種方案實現方式的比較
從Sql語句的角度來看，方案二更簡單，也更容易理解。不過相對麻煩的就是需要每次去獲得上一頁末記錄的IdCdr值（或下一頁首記錄IdCdr值）。前一次查詢時，還需要記錄首記錄和末記錄值。另外，方案二是根據上頁首記錄（或末記錄）IdCdr值作為查詢條件，它與具體的頁數無關，因此，無法直接定位顯示某頁的結果，除非在之前將各頁的首、末記錄放到數組中保存下來，但這就要耗費一定的時間。一旦改變了查詢條件，數組中保存的值，還需要更新。
方案一，Sql語句較復雜，但并不影響查詢的程序。同時，由于其引入了臨時表機制，該臨時表是放到tempdb數據庫中。如果多次查詢，則必然會多次刪除和創建臨時表，帶來的結果是tempdb數據庫的日志會不段增長。同時由于日志的增長，也會影響使用臨時表的性能。如果要具體實現，必須在上述的sql語句中，實時地清除tempdb庫中的日志。
總體說來，方案一，Sql語句復雜，但程序設計簡單；而方案二則剛剛相反。
兩種方案性能的比較
由于上述兩種方案都是對sql語句進行改進，因此我在測試時，直接運行sql語句來計算其查詢所消耗的時間。如果是在具體的業務界面中，還應加上一些前置、后置操作的耗時，尤其是界面顯示結果集的時間。但由于每頁記錄數相對較小，返回的結果集也較小，因此這些耗時可以忽略不計。
另外，測試記錄的時間只包括了查詢語句的時間（方案一還包括了建立臨時表，并插入記錄的時間），沒有包含計算符合條件的總記錄數時間。
從上文中的分析可以總結出兩種方案，各有優勢。另外，對于分頁查詢時，我們還可以使用游標來實現。但是如果是多種數據庫，使用游標的方式不便于數據庫腳本的移植，應該慎用。大家在選用改進數據庫查詢性能的方案時要根據自己的實際情況作出***的選擇，希望上文中涉及到的內容對大家能夠有所幫助。
【編輯推薦】
