[搜片神器]伺服器SQL2005查詢分頁語句你了解了麼

在網站準備采用Lucence.net來進行索引處理搜尋慢問題的時候，突然發現常用的分頁擷取資料的row_number也支援不住了，後期查到200多萬的時候非常慢(總資料有500萬)，經過網上查詢分析一些資料後，基本上搞明白是什麼原因導緻的，順便紀錄一下解決方案。

在sosobt.com網站準備采用Lucence.net來進行索引處理搜尋慢問題的時候，突然發現常用的分頁擷取資料的row_number也支援不住了，後期查到200多萬的時候非常慢(總資料有500萬)，經過網上查詢分析一些資料後，基本上搞明白是什麼原因導緻的，順便紀錄一下解決方案。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

網上找的幾種sqlserver2005高效分頁sql查詢語句

top方案：

sql codeselect top 10 *

from table1

where id not in(select top 開始的位置 id from table1)

max:

sql codeselect top 10 * from

table1

where id>(select max(id)

from (select top 開始位置 id from table1order by id)tt)

row:

sql codeselect *

from (

select

row_number()over(order by tempcolumn)temprownumber,*

from

(select top 開始位置+10 tempcolumn=0,* from table1)t

)tt

where

temprownumber>開始位置

3種分頁方式，分别是max方案，top方案，row方案

效率：

第1：row

第2：max

第3：top

缺點：

max：必須使用者編寫複雜sql，不支援非唯一列排序

top：必須使用者編寫複雜sql,不支援複合主鍵

row：不支援sqlserver2000

測試資料：

共320萬條資料，每頁顯示10條資料，分别測試了2萬頁、15萬頁和32萬頁。

頁碼，top方案，max方案，row方案

2萬，60ms，46ms，33ms

15萬，453ms，343ms，310ms

32萬，953ms，720ms，686ms

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

個人也是經常采用row_number來解決問題的，但是主鍵是以ID為主的，這次資料庫表不是ID主鍵，

如果使用ID來進行擷取200萬後面的200條資料，發現伺服器上需要幾分鐘都不一定出來得結果

select * from (select *,row_number() over(order by ID asc) as RowNum from H31_DHT_TYPE_101_1) as a where RowNum between 2000000  and  2000200

但是使用HashKey來排序，則很快，ms表示毫秒

select * from (select *,row_number() over(order by hashkey asc) as RowNum from H31_DHT_TYPE_101_1) as a where RowNum between 2000000  and  2000200

[2013-10-13 7:32:02]: Addindex[1011]:1500601 DBTime:219ms IndexTime:47ms
[2013-10-13 7:32:02]: Addindex[1011]:1500801 DBTime:219ms IndexTime:31ms
[2013-10-13 7:32:03]: Addindex[1011]:1501001 DBTime:547ms IndexTime:687ms
[2013-10-13 7:32:04]: Addindex[1011]:1501201 DBTime:219ms IndexTime:94ms
[2013-10-13 7:32:04]: Addindex[1011]:1501401 DBTime:219ms IndexTime:31ms
[2013-10-13 7:32:04]: Addindex[1011]:1501601 DBTime:219ms IndexTime:62ms
[2013-10-13 7:32:04]: Addindex[1011]:1501801 DBTime:219ms IndexTime:31ms
[2013-10-13 7:32:05]: Addindex[1011]:1502001 DBTime:219ms IndexTime:78ms
[2013-10-13 7:32:05]: Addindex[1011]:1502201 DBTime:219ms IndexTime:47ms
[2013-10-13 7:32:05]: Addindex[1011]:1502401 DBTime:218ms IndexTime:32ms

由于hashkey是唯一，但不是遞增的問題，必須以ID遞增來擷取紀錄增加到lucence索引裡面，

在此查詢了何時使用聚集索引或非聚集索引

下面的表總結了何時使用聚集索引或非聚集索引（很重要）：

動作描述	使用聚集索引	使用非聚集索引
列經常被分組排序	應
傳回某範圍内的資料		不應
一個或極少不同值
小數目的不同值
大數目的不同值
頻繁更新的列
外鍵列
主鍵列
頻繁修改索引列

主鍵	聚集索引
用途	強制表的實體完整性	對資料行的排序，友善查詢用
一個表多少個	一個表最多一個主鍵	一個表最多一個聚集索引
是否允許多個字段來定義	一個主鍵可以多個字段來定義	一個索引可以多個字段來定義
是否允許 null 資料行出現	如果要建立的資料列中資料存在null，無法建立主鍵。建立表時指定的 PRIMARY KEY 限制列隐式轉換為 NOT NULL。	沒有限制建立聚集索引的列一定必須 not null . 也就是可以列的資料是 null 參看最後一項比較
是否要求資料必須唯一	要求資料必須唯一	資料即可以唯一，也可以不唯一。看你定義這個索引的 UNIQUE 設定。（這一點需要看後面的一個比較，雖然你的資料列可能不唯一，但是系統會替你産生一個你看不到的唯一列）
建立的邏輯	資料庫在建立主鍵同時，會自動建立一個唯一索引。如果這個表之前沒有聚集索引，同時建立主鍵時候沒有強制指定使用非聚集索引，則建立主鍵時候，同時建立一個唯一的聚集索引	如果未使用 UNIQUE 屬性建立聚集索引，資料庫引擎将向表自動添加一個四位元組 uniqueifier 列。必要時，資料庫引擎将向行自動添加一個 uniqueifier 值，使每個鍵唯一。此列和列值供内部使用，使用者不能檢視或通路。

通過此處分析聚焦索引一個表隻能有一個，如何解決目前的問題呢？

首先我們分析下

select * from (select *,row_number() over(order by ID asc) as RowNum from H31_DHT_TYPE_101_1) as a where RowNum between 2000000  and  2000200

這條語句的對與錯的問題，

1.select *,row_number() over(order by ID asc) as RowNum from H31_DHT_TYPE_101_1表明了需要查詢所有字段和列全部搜尋出來了，資料小的時候你不會發現，但資料量大的時候就知道了，因為他表明了擷取所有字段的資料進行按ID排序，雖然ID也索引了。

2.上面需要很長時間，然後再去定位查詢感覺時間很慢。

是以個人的想法是先不用查詢出所有字段的資料，

WITH temp AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS 'RowNumber' FROM H31_DHT_TYPE_101_1)

這條語句查詢出來的結果需要1到2秒，比起上面的基本上需要很長時間算是好的了，

然後再從這裡面取ID号，這樣就節約了不少時間，

WITH temp AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS 'RowNumber' FROM H31_DHT_TYPE_101_1)
select ID,hashKey,recvTime,updateTime,keyContent,keyType,recvTimes,fileCnt,filetotalSize,Detail,viewTimes,viewLevel from H31_DHT_TYPE_101_1 where id in (select id from temp where RowNumber between  2000000  and  2000200)

分析使用的時間在2秒左右

[2013-10-14 2:43:17]: Addindex[1011]:2577092 DBTime:1453ms IndexTime:32ms
[2013-10-14 2:43:19]: Addindex[1011]:2577292 DBTime:1515ms IndexTime:32ms
[2013-10-14 2:43:20]: Addindex[1011]:2577492 DBTime:1578ms IndexTime:31ms
[2013-10-14 2:43:22]: Addindex[1011]:2577692 DBTime:1687ms IndexTime:32ms
[2013-10-14 2:43:23]: Addindex[1011]:2577892 DBTime:1422ms IndexTime:31ms
[2013-10-14 2:43:25]: Addindex[1011]:2578092 DBTime:1547ms IndexTime:31ms
[2013-10-14 2:43:27]: Addindex[1011]:2578292 DBTime:1469ms IndexTime:31ms
[2013-10-14 2:43:28]: Addindex[1011]:2578492 DBTime:1516ms IndexTime:31ms
[2013-10-14 2:43:30]: Addindex[1011]:2578692 DBTime:1422ms IndexTime:31ms
[2013-10-14 2:43:36]: Addindex[1011]:2578892 DBTime:6422ms IndexTime:31ms
[2013-10-14 2:43:40]: Addindex[1011]:2579092 DBTime:3938ms IndexTime:31ms
[2013-10-14 2:43:43]: Addindex[1011]:2579292 DBTime:3484ms IndexTime:32ms
[2013-10-14 2:43:48]: Addindex[1011]:2579492 DBTime:4265ms IndexTime:32ms
[2013-10-14 2:43:50]: Addindex[1011]:2579692 DBTime:1750ms IndexTime:31ms

總結:

1.對SQL2005的分頁進一步的了解了聚集索引的問題，有聚集索引的使用ROW_NUMBER則很快；

2.對目前大家所采用的分頁語句是否合理的問題，優化的問題得了學習，特此記錄一下。

3.希望寫得不對的地方請大家在此留言指教下.

下一篇準備對網站采用lucence.net3.03如何來進行索引搞定搜尋的問題進行記錄。

大家看累了,就移步到娛樂區sosobt.com 去看看,休息下...

希望大家多多推薦哦...大家的推薦才是下一篇介紹的動力...

[搜片神器]伺服器SQL2005查詢分頁語句你了解了麼

繼續閱讀

用SQL寫出分頁查詢

PHP+MySQL 分頁那點事

寫分頁用的SQL(備忘)

sql 存儲過程分頁

SQL Server2012 T-SQL對分頁的增強嘗試

關于用戶端接口分頁sql語句

SQL Relay

[SQL]學習一下sql資料分頁

V$SQL與V$SQLAREA

SQL Reverse函數

【譯】使用Spark SQL 運作大規模基因組工作流原文連結：編譯:使用Spark SQL 運作大規模基因組工作流資料讀取基因組資料的使用資料導出下一步是什麼

如何利用工具，迅猛定位低效SQL？ | 1分鐘系列

【譯】使用Spark SQL 運作大規模基因組工作流

資料庫分頁

分頁功能存儲過程，Delphi 和 .NET應用