導(dǎo)讀：數(shù)據(jù)庫(kù)的查詢功能，其性能終究是有限的。即使我們對(duì)數(shù)據(jù)庫(kù)進(jìn)行了***配置，對(duì)數(shù)據(jù)表設(shè)計(jì)再三斟酌，然而一旦面臨海量數(shù)據(jù)，且返回結(jié)果集較大的時(shí)候，常規(guī)的查詢語(yǔ)句就無(wú)能為力了。一般說(shuō)來(lái)，當(dāng)返回的結(jié)果集超過(guò)總數(shù)量的40%時(shí)，數(shù)據(jù)庫(kù)層面上的優(yōu)化就顯得束手無(wú)策了。此時(shí)，我們應(yīng)該考慮從sql語(yǔ)句和程序業(yè)務(wù)上著手。下文中就改進(jìn)數(shù)據(jù)庫(kù)查詢性能為大家作出了詳細(xì)的分析方案。
一、 總體思路

通過(guò)SQL語(yǔ)句“set rowcount 每頁(yè)記錄數(shù)”，并指定每頁(yè)記錄數(shù)，每次只查詢符合條件記錄集中指定的記錄數(shù)，以達(dá)到分頁(yè)的目的。由于查詢功能一般應(yīng)用在平臺(tái)界面中，如果通過(guò)分頁(yè)的方式，可以使得單位查詢的速度顯著提高。同時(shí)，返回的結(jié)果集也顯著減少，這降低了一次查詢消耗內(nèi)存的容量，對(duì)于界面的刷新速度也有明顯的提高。由于分頁(yè)查詢將原來(lái)一次查詢的總時(shí)間，通過(guò)分頁(yè)的方式，分割為每個(gè)小段，因此對(duì)于用戶而言，每次獲得結(jié)果的時(shí)間就很短了，這在界面與交互設(shè)計(jì)中，從考慮用戶體驗(yàn)的角度出發(fā)，也是非常合理的。
由于該方法需要指定每頁(yè)記錄數(shù)，因此需要被查詢的目的表必須具備一個(gè)標(biāo)識(shí)唯一值的字段，并將該字段建立索引，以作為查詢和排序的條件。在數(shù)據(jù)庫(kù)設(shè)計(jì)中，有很多種創(chuàng)建標(biāo)識(shí)字段的方法。最簡(jiǎn)單地莫過(guò)于創(chuàng)建Identity字段。當(dāng)然這種方式的問(wèn)題也多多，這里不再贅述。也可以寫一個(gè)存儲(chǔ)過(guò)程，負(fù)責(zé)生成唯一標(biāo)識(shí)的ID。

二、 實(shí)現(xiàn)方案

要進(jìn)行分頁(yè)查詢，首先需要確定每頁(yè)的記錄數(shù)。根據(jù)各種業(yè)務(wù)和局方的不同需求，同時(shí)各個(gè)局方話單量也各有不同，所以，每頁(yè)記錄數(shù)值應(yīng)放到AAA.ini配置文件中，便于靈活配置。
在分頁(yè)查詢之前，我們需要知道每個(gè)月的話單應(yīng)該的總頁(yè)數(shù)，可以先獲得查詢目的表的總記錄數(shù)（以Ctsi業(yè)務(wù) (固網(wǎng)點(diǎn)對(duì)點(diǎn)短信)為例，下同），SQL語(yǔ)句如下：
select count(1) from CtsiInfoRecord where 條件
注：后面的查詢語(yǔ)句中均應(yīng)包括查詢條件，為清楚表現(xiàn)sql語(yǔ)句，本文一律省略該條件。
然后通過(guò)總記錄數(shù)和每頁(yè)記錄數(shù)，獲得每個(gè)月分頁(yè)查詢的總頁(yè)數(shù)。
由于我們的業(yè)務(wù)主要使用微軟的Sql Server2000和sybase。因此，實(shí)現(xiàn)分頁(yè)查詢有兩種方式。具體實(shí)現(xiàn)方案如下：
 方案一：通過(guò)建立臨時(shí)表結(jié)合分頁(yè)查詢

在微軟的Sql Server中，在其T-SQL中引入了top語(yǔ)法，通過(guò)該語(yǔ)法可以非常方便的實(shí)現(xiàn)分頁(yè)查詢，sql語(yǔ)句為（以Ctsi業(yè)務(wù)為例）：
select top 每頁(yè)記錄數(shù) * from CtsiInfoRecord01 where IdCdr not in
(select top 頁(yè)數(shù)*每頁(yè)記錄數(shù) IdCdr from CtsiInfoRecord01 order by IdCdr)
order by IdCdr
在實(shí)際查詢時(shí)，只需要修改子查詢的top記錄數(shù)即可。
遺憾的是，該top語(yǔ)法在sybase中并不支持。相對(duì)應(yīng)的語(yǔ)法為set rowcount 記錄數(shù)。但該語(yǔ)法不能放在子查詢語(yǔ)句中，因此，上述的方法無(wú)法實(shí)現(xiàn)。
根據(jù)該方法的實(shí)現(xiàn)思路，引入臨時(shí)表，并結(jié)合分頁(yè)查詢來(lái)實(shí)現(xiàn)，sql語(yǔ)句如下：
set rowcount頁(yè)數(shù)*每頁(yè)記錄數(shù)
select IdCdr into #ctsitable from CtsiInfoRecord01 order by IdCdr
set rowcount 每頁(yè)記錄數(shù)
select * from CtsiInfoRecord01 where IdCdr not in
(select IdCdr from #ctsitable ) order by IdCdr
drop table #ctsitable
注：#ctsitable為臨時(shí)庫(kù)tempdb中的臨時(shí)表；
在sybase中，不支持在子查詢中引入order by；
如果查詢***頁(yè)，則不需要建立臨時(shí)表，直接查詢即可：
set rowcount 每頁(yè)記錄數(shù) select * from CtsiInfoRecord01 order by IdCdr

 方案二：直接根據(jù)IdCdr條件分頁(yè)查詢

假定話單表的唯一標(biāo)識(shí)字段為IdCdr。如果通過(guò)order by進(jìn)行排序（默認(rèn)升序），在每頁(yè)記錄數(shù)固定以及查詢條件相同的前提下，下一頁(yè)查詢的所有記錄，其IdCdr值必然大于上一頁(yè)末記錄的IdCdr。如果我們每次查詢后，獲得了末記錄的IdCdr值，然后在下一次查詢時(shí)，引入該條件，得到的結(jié)果必然是根據(jù)條件查詢出來(lái)的下一頁(yè)結(jié)果。方法如下：
set rowcount 每頁(yè)記錄數(shù)
select * from CtsiInfoRecord where IdCdr > 上一頁(yè)末記錄IdCdr值 order by IdCdr
如果是上一頁(yè)查詢，則剛好相反，需要獲得下一頁(yè)首記錄的IdCdr值：
set rowcount 每頁(yè)記錄數(shù)
select * from CtsiInfoRecord where IdCdr < 下一頁(yè)首記錄IdCdr值
注：如果查詢首頁(yè)，則將IdCdr值條件刪掉。
如果查詢末頁(yè)，在刪掉IdCdr值條件的同時(shí)，將排序改為降序的方式。

兩種方案實(shí)現(xiàn)方式的比較

從Sql語(yǔ)句的角度來(lái)看，方案二更簡(jiǎn)單，也更容易理解。不過(guò)相對(duì)麻煩的就是需要每次去獲得上一頁(yè)末記錄的IdCdr值（或下一頁(yè)首記錄IdCdr值）。前一次查詢時(shí)，還需要記錄首記錄和末記錄值。另外，方案二是根據(jù)上頁(yè)首記錄（或末記錄）IdCdr值作為查詢條件，它與具體的頁(yè)數(shù)無(wú)關(guān)，因此，無(wú)法直接定位顯示某頁(yè)的結(jié)果，除非在之前將各頁(yè)的首、末記錄放到數(shù)組中保存下來(lái)，但這就要耗費(fèi)一定的時(shí)間。一旦改變了查詢條件，數(shù)組中保存的值，還需要更新。
方案一，Sql語(yǔ)句較復(fù)雜，但并不影響查詢的程序。同時(shí)，由于其引入了臨時(shí)表機(jī)制，該臨時(shí)表是放到tempdb數(shù)據(jù)庫(kù)中。如果多次查詢，則必然會(huì)多次刪除和創(chuàng)建臨時(shí)表，帶來(lái)的結(jié)果是tempdb數(shù)據(jù)庫(kù)的日志會(huì)不段增長(zhǎng)。同時(shí)由于日志的增長(zhǎng)，也會(huì)影響使用臨時(shí)表的性能。如果要具體實(shí)現(xiàn)，必須在上述的sql語(yǔ)句中，實(shí)時(shí)地清除tempdb庫(kù)中的日志。
總體說(shuō)來(lái)，方案一，Sql語(yǔ)句復(fù)雜，但程序設(shè)計(jì)簡(jiǎn)單；而方案二則剛剛相反。

兩種方案性能的比較

由于上述兩種方案都是對(duì)sql語(yǔ)句進(jìn)行改進(jìn)，因此我在測(cè)試時(shí)，直接運(yùn)行sql語(yǔ)句來(lái)計(jì)算其查詢所消耗的時(shí)間。如果是在具體的業(yè)務(wù)界面中，還應(yīng)加上一些前置、后置操作的耗時(shí)，尤其是界面顯示結(jié)果集的時(shí)間。但由于每頁(yè)記錄數(shù)相對(duì)較小，返回的結(jié)果集也較小，因此這些耗時(shí)可以忽略不計(jì)。
另外，測(cè)試記錄的時(shí)間只包括了查詢語(yǔ)句的時(shí)間（方案一還包括了建立臨時(shí)表，并插入記錄的時(shí)間），沒(méi)有包含計(jì)算符合條件的總記錄數(shù)時(shí)間。

從上文中的分析可以總結(jié)出兩種方案，各有優(yōu)勢(shì)。另外，對(duì)于分頁(yè)查詢時(shí)，我們還可以使用游標(biāo)來(lái)實(shí)現(xiàn)。但是如果是多種數(shù)據(jù)庫(kù)，使用游標(biāo)的方式不便于數(shù)據(jù)庫(kù)腳本的移植，應(yīng)該慎用。大家在選用改進(jìn)數(shù)據(jù)庫(kù)查詢性能的方案時(shí)要根據(jù)自己的實(shí)際情況作出***的選擇，希望上文中涉及到的內(nèi)容對(duì)大家能夠有所幫助。

【編輯推薦】

1. 復(fù)制SqlServer數(shù)據(jù)庫(kù)
2. SQL Server XML查詢工具
3. 數(shù)據(jù)庫(kù)設(shè)計(jì)中的五個(gè)范式
4. Oracle導(dǎo)入導(dǎo)出數(shù)據(jù)庫(kù)的語(yǔ)法