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