轉自：http://www.cnblogs.com/wayfarer/archive/2005/04/19/140609.html

數據庫的查詢功能，其性能終究是有限的。即使我們對數據庫進行了最優配置，對數據表設計再三斟酌，然而一旦面臨海量數據，且返回結果集較大的時候，常規的查詢語句就無能為力了。一般說來，當返回的結果集超過總數量的40%時，數據庫層面上的優化就顯得束手無策了。此時，我們應該考慮從sql語句和程序業務上著手。
在我參與開發的業務里，主要是在通訊行業，如移動、電信或網通，其中數據表數量最多的就是話單記錄。通常都會在每個月達到百萬級的數量，一年合計就達到千萬級了。在這種情況下，除了進行定期備份和清除無效數據等措施，以減少話單總量。在進行話單的查詢設計時，仍然需要進行設計上的改進，以滿足客戶的需求。

1． 總體思路

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

2． 實現方案

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

在微軟的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

2.2 方案二：直接根據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值條件的同時，將排序改為降序的方式。

2.3 兩種方案實現方式的比較

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

2.4 兩種方案性能的比較

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

2.4.1 測試環境

操作系統：Windows
數據庫：Sql Server 2000
訪問方式：本機直接訪問數據庫（非客戶端訪問方式）
總記錄數：9,001,789條
每頁記錄數：2,000條

2.4.2 測試結果

	方案一（耗時：秒）	方案二（耗時：秒）
第1頁	0.1~0.2	0.1~0.2
第3頁（4,000條記錄后）	11	0.1~0.2
第10頁（20,000條記錄后）	12	0.1~0.2
第50頁（100,000條記錄后）	14	0.1~0.2
第100頁（200,000條記錄后）	15	0.1~0.2
第1000頁（2,000,000條記錄后）	47	0.1~0.2

 從測試結果看，方案二在性能上有非常大的優勢。由于IdCdr建立了索引，且該值為int類型，因此，查詢條件中，IdCdr具體的值對查詢沒有影響。而方案一由于是通過臨時表方式，且臨時表的記錄數會根據頁數的增加而增加，這在一定程度上影響了查詢性能。（注：如果是在Sql Server中，且數據量不太大，選擇方案一并采用top的方法還是比較優秀的。一般的網頁設計時，分頁查詢均采用這種方式）不過，如果我們不僅是實現上、下頁翻頁，還要實現指定頁查詢，則第二種方案由于需要獲得所有頁首、末記錄的IdCdr值，故在查詢之前的初始化過程需要耗費較長的時間。

    兩種方案，各有優勢。另外，對于分頁查詢時，我們還可以使用游標來實現。但是如果是多種數據庫，使用游標的方式不便于數據庫腳本的移植，應該慎用

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 17:13 none

請問：

數據查詢的時候數據會更新嗎？

如果更新的話，更新數據的入口點，你可以控制嗎？也即使你是否能讓所有更新數據的入口都在某個程序中？

最重要的一點，需要對所有數據進行動態排序嗎？（就是用戶會更換排序條件）如果會，那么提供排序的列有幾個？  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 17:33 聽棠.NET

樓主的思考是有一定的道理的。
我一般是使用GUID做主鍵的。那怎么辦呢？？  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 17:47 lay

方案2確實效率高，但對表設計有要求，不光是程序上的處理  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 18:10 Austin leng

方案2的確可以達到較高的效率，也是可行的。
但是，使用頻率應該是不高，道理其實只有一個，因為大多數的排序都不是按主鍵來排序的。
所以，分頁存儲過程，我認為，使用得最多的倒是SQL語句拼湊的方法來分頁，和使用表變量（或臨時表）的方法來分頁的最多。
  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 20:44 黃金獅子旗下

插臨時表的辦法分頁效率不好。還是SQL語句嵌套方式好。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 21:22 dudu

博客園采用的是方案一, 看來需要改進一下。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 22:23 wayfarer

None問得很有道理。在查詢的時候，如果有最新的更新，是否能顯示，要看這條新記錄插入的時機。然而由于每頁查詢耗時比較少，且每次查詢都會根據條件和排序進行select，因此影響不會太大。

然而方案二的排序方式，必然要受到排序的影響。至少在進行排序時，IdCdr必須是排序的主字段。

另外，GUID使用這種方式是可以的。雖然GUID是隨機產生的，但它仍然有順序。只是比起Identity字段，要慢一些。而且如果有實時插入的新紀錄的話，可能會在查詢的時候會漏掉。

@dudu
如果記錄不時太多，比如達到百萬級。且使用Sql Server的話，我覺采用方案一結合top就夠了。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-19 23:39 湘南和也

樓主的第二個方案和我用的完全一樣。這個寫起來很方便，我就一直這么用了。

但如果真從數據庫性能來講它并不是很好的，特別是用到了not in的時候，最好連in都不要用。

我目前正在思考怎樣優化這種sql語句。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 08:45 none

樓主是否考慮過在程序中實現緩存呢？

比如說：把IdCdr索引完全的讀到程序中，緩存起來（比如ArrayList，當然有泛型更好），下次要查，在這里得到ID號，然后根據ID號直接到數據庫查出數據，數據總記錄數量也可以統計緩存中的索引數量得出，并且做任何更新的時候，都需要同步這個索引，如果有多個排序，那就給每個排序維護一個索引，樓主是否也考慮到內存的問題了，是的，這樣相當消耗內存，我做過，大約1000W的ID存于ArrayList中會使用大約160M的內存，如果有泛型就好很多了！不過就便是160M，對于1000W數據的應用來說又算得了什么呢？這樣做的話，在1600+512內存的情況下，任何一頁數據的獲取都只需要15-60ms就夠了，而對用戶來說，完全察覺不到。。。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 09:27 wayfarer

@None

正如你說的，還是內存的問題。消耗這么多內存，以換取幾毫秒，或者幾秒鐘的性能，不劃算。

因為程序是作為客戶端安裝在客戶機上，機器的配置也不會太高。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 09:52 none

恩那，我覺得如果數據庫在服務器上呢，就比較適合，如果數據庫在客戶端確實就沒有必要了，如果客戶端只負責顯示，而服務器上負責業務邏輯，可能也比較適合實現。  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 09:53 none

對了，關鍵在于，這樣實現之后，數據庫服務器的開銷將會大大降低，我記得我測試的時候CPU基本沒超過2%過，如果不用，則一直在40%以上  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 10:11 cmoremore

這個對數據庫表又要求吧？我的表沒有唯一標示的字段怎么辦？  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 12:00 none

呵呵，這個是有假設基礎的，假設基礎就是肯定有唯一標識  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-04-20 16:14 Da.Feng

哎，偶看來已經固步自封了  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-06-09 18:01 BOBO

我的表也沒有唯一的標識符，我準備在方法二的基礎上＋一個identity的臨時表來實現，不知道可不可以？  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-06-30 21:23 er

er  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-06-30 21:32 iww

各位大俠：
我有一個問題，請問在sql server中百萬數據量的表上

針對一個或兩個字段進行LIKE條件檢索的時間大概是多少亞？

謝謝！

例如：
select title,author,abstract
from books
where ((books.abstract like '%你好%')
and (books.titile like '%題目%'))


我這里沒有這種規模的數據庫，望各位大俠幫忙一下。
能把結果發到我的信箱嗎？ happyiww◎126.com  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-08-19 11:41 愛天

我是初學者,請作者幫幫忙,我現在做了一個簡單的客戶資料檔案系統,請問怎樣才能讓他有外部連接呀,謝謝指教.  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2005-08-19 11:42 愛天

我的QQ:42575475 郵箱:wang7261712@126.com  回復  更多評論   

# re: 怎樣改進數據庫的查詢性能? 2006-12-15 23:26 yunhuasheng

寫的好