① sqlserver2000 如何提高分頁查詢大數據量的效率
sqlserver2005及以上的版本有row_number()函數可以高效分頁，sqlserver2000的話只能看演算法了
② 大數據量實時統計排序分頁查詢 優化總結
大數據量實時統計排序分頁查詢 (並發數較小時) 的瓶頸不是函數（count，sum等）執行，
不是having, 也不是order by，甚至不是表join, 導致慢的原因就在於「數據量太大本身」
就是將表劃分為M份相互獨立的部分,可以是分表，也可以是不分表但冗餘一個取模結果欄位
實際結果是不分表比分表更加靈活，只需稍加配置，就可以動態切分大表，隨意更改M的大小。
將1條慢sql（大於30秒）拆分成為N條查詢速度巨快的sql(單條sql執行時間控制在20毫秒以內)
然後再web應用中以適當的線程數去並發查詢這些執行時間快的N條小sql再匯總結果
第一步查詢中去並發執行這N條小sql, 只取排序欄位和標識欄位，其他欄位一律丟棄
匯總結果後定位出當前頁面要顯示的pageNum條數據，再進行第二步查詢，取出頁面上需要展示的所有欄位
PS：這一點是至關重要的，其他幾點都可以不看，這點是最關鍵的。慢慢解釋一下：
a) 第一種方式是把資料庫中所有記錄（只取排序欄位和標識欄位並且不做任何sum，count having order by等操作）
全部拉到web應用中，在web應用中完成所有的計算
b) 第二種方式是把資料庫中所有記錄做sum count having等操作之後的所有行數拉到web應用中，在web應用中完成剩餘計算
c) 第三種方式是把資料庫中所有記錄做sum count having order by等操作之後把limit後的數據拉到web應用中，
在web應用中對limit後的數據再計算
顯然，第一種方式 資料庫什麼活都不做只取數據 是不可行的。以lg_order_count_seller為例，1500萬行，
如果只算id, seller_id和order_count 這三個bigint類型，至少需要拉8*3*1500 0000 = 360000000=340M,
拉到內存中之後存儲需要8*4*15000000= 460M,這還不算List是的2的n次方這個特點和計算排序等的內存開銷，
不僅資料庫與web應用機器IO扛不住，就是應用自身恐怕也要OOM了。
第二種方式，所有記錄做sum count having等操作之後,由於是group by seller_id的，總得數據量變為100萬（就是賣家總數），
這樣子一來，共需要拉8*3*100 0000 = 23M,拉到內存之後，需要8*4*100 0000 = 30M, 再算上List是的2的n次方這個特點和
計算排序等的內存開銷也不會超過100M, IO的時間和內存開銷勉強可以考慮接受。
第三種方式，所有記錄做sum count having order by等操作之後把limit後的數據拉到web應用中，因為做了limit，所以，
數據量很小了，無論是IO還是內存開銷都已經很小了。可以忽略。
綜合以上三種，第三種方式適用於頁面的前n頁和後n頁，因為這個limit的數據量隨著頁數的增大而增大，
當大到每個切分後的小表的數據量時就轉為第二種方式了。
第二種方式適用於頁面的第[n+1, totaoPageNum-n]頁。
切分成N條小sql後並行執行時排序不穩定性的解決辦法
① 問題描述：
優化之前，還是是一條大慢sql查詢時，由於資料庫排序是穩定排序，
所以當兩條記錄排序欄位值相同時他們在頁面上的頁碼位置是固定的。
優化之後，當並行執行這N條小sql時，由於無法控制這些小sql的先後執行順序，
導致在web應用中當兩條記錄的排序欄位值相同時在頁面上的頁碼位置是隨機的。
② 解決辦法：
除了拉標識欄位(seller_id)和排序欄位(order_count_sum)之外，再取一個unique（id）的欄位，當兩條記錄的排序欄位值相同時，再用這個unique的欄位（在賣家監控中這個欄位是id）進行第二次排序.這樣就解決了排序不穩定的問題。
③ 也許，看到這里會有疑問，為什麼不用seller_id？seller_id也是唯一， 這樣子不是少取id這個欄位，減少IO了？
seller_id雖然也是唯一，可以輔助排序，但是不要忘記資料庫的排序規則是：
如果兩列的值相等，那麼序號在前的排在前面，這里的序號就是主鍵(自動生成，autoincrement),
如果用seller_id的話還是不能保證排序的穩定性，只能用主鍵id.
優先載入頁面上的主要元素，然後再去非同步載入次要元素，
反應在賣家監控頁面中，查數據和查頁頁碼的sql語句基本相同，是在競爭同一資源，
所以，需要做一個策略，優先把資源讓給查數，數據查完之後再去查頁碼。
限流
由於多線程取數據並沒有從本質上提高資料庫性能，所以必須針對大數據量實時統計排序分頁查詢做限流
我這里打個比方：食堂有6個窗口，物流團隊吃飯要買6個菜，平均每買1個菜需要1分鍾的時間，
如果派我一個人去一個窗口買的話需要6分鍾的時間
假如派6個人分別去6個窗口買這6個菜，只需要1分鍾的時間
但是，如果除了物流團隊，再來其他5個團隊呢，也就是說6個團隊每個團隊買6個菜共買36個菜，
這樣子有的團隊先買完，有的團隊後買完，但平均時間還是6分鍾。本質上沒有變化。
所以，對於特定的查詢條件，必須進行限流。讓每分鍾至多有6個團隊買菜，這樣子能使得情況變得不至於太糟糕。
從根本上改變現狀
這一點從目前來看只能是展望了，比如mysql資料庫換更為強大的oracle資料庫，
或更換InnoDb引擎為其他，或更換SATA硬碟為SSD 。。。。。。
從實踐效果來看，優化後的效果是很明顯的。
相同的查詢條件，原來一個頁面查詢時間由於超過60秒超時了，根據1-6點建議優化之後，查詢時間變為2秒至3.5秒之間。
③ 如何用sql語句 實現分頁查詢
分頁：一般會把當前頁通過get方式傳遞，PHP通過$_GET['page']接收。
查詢：可以從當前乘以每頁顯示數通過limit來實現分頁效果。
//每頁顯示條數
$pageSize=10;
//當前頁
$_GET['page']?$page=1:$page=$_GET['page'];
//開始查詢位置
$seat=$page*$pageSize;
//sql語句
$sql="select*fromtablelimit$seat,$pageSize";
//輸出數據到view即可
④ 如何用sql語句 實現分頁查詢
方法1：
適用於 SQL Server 2000/2005
SELECT TOP 頁大小 *
FROM table1
WHERE id NOT IN
(
SELECT TOP 頁大小*(頁數-1) id FROM table1 ORDER BY id
)
ORDER BY id
方法2：
適用於 SQL Server 2000/2005
SELECT TOP 頁大小 *
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP 頁大小*(頁數-1) id FROM table1 ORDER BY id
) A
)
ORDER BY id
方法3：
適用於 SQL Server 2005
SELECT TOP 頁大小 *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
) A
WHERE RowNumber > 頁大小*(頁數-1)
⑤ MySQL大數據量分頁查詢方法及其優化
使用子查詢優化大數據量分頁查詢
這種方式的做法是先定位偏移位置的id，然後再往後查詢，適用於id遞增的情況。
使用id限定優化大數據量分頁查詢
使用這種方式需要先假設數據表的id是連續遞增的，我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的范圍，可以使用 id between and 來查詢：
當然了，也可以使用in的方式來進行查詢，這種方式經常用在多表關聯的情況下，使用其他表查詢的id集合來進行查詢：
但是使用這種in查詢方式的時候要注意的是，某些MySQL版本並不支持在in子句中使用limit子句。
參考 sql優化之大數據量分頁查詢（mysql） - yanggb - 博客園 (cnblogs.com)
⑥ mysql百萬數據分頁查詢4秒，求教怎麼優化
很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過MySQL更好的實現分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解資料庫的底層或多或少有助於優化分頁查詢。
我們先從一個常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個查詢耗時0.00sec。So，這個查詢有什麼問題呢？實際上，這個查詢語句和參數都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的問題在於offset(分頁偏移量)很大的時候，像下面這樣：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執行計劃可以發現該SQL檢索了100015行，但最後只需要15行。大的分頁偏移量會增加使用的數據，MySQL會將大量最終不會使用的數據載入到內存中。就算我們假設大部分網站的用戶只訪問前幾頁數據，但少量的大的分頁偏移量的請求也會對整個系統造成危害。Facebook意識到了這一點，但Facebook並沒有為了每秒可以處理更多的請求而去優化資料庫，而是將重心放在將請求響應時間的方差變小。
對於分頁請求，還有一個信息也很重要，就是總共的記錄數。我們可以通過下面的查詢很容易的獲取總的記錄數。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在採用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優化是採用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先准備好符合條件的記錄數，隨後只要執行一句 select FOUND_ROWS(); 就能獲得總記錄數。但是在大多數情況下，查詢語句簡短並不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優化。文章分為兩部分，第一部分是如何獲取記錄的總數目，第二部分是獲取真正的記錄。
高效的計算行數
如果採用的引擎是MyISAM，可以直接執行COUNT(*)去獲取行數即可。相似的，在堆表中也會將行數存儲到表的元信息中。但如果引擎是InnoDB情況就會復雜一些，因為InnoDB不保存表的具體行數。
我們可以將行數緩存起來，然後可以通過一個守護進程定期更新或者用戶的某些操作導致緩存失效時，執行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創建一個新的表「news」，按照時事性排序(最新發布的在最前面)，實現一個高性能的分頁。為了簡單，我們就假設最新發布的新聞的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一個比較高效的方式是基於用戶展示的最後一個新聞Id。查詢下一頁的語句如下，需要傳入當前頁面展示的最後一個Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查詢上一頁的語句類似，只不過需要傳入當前頁的第一個Id，並且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查詢方式適合實現簡易的分頁，即不顯示具體的頁數導航，只顯示「上一頁」和「下一頁」，例如博客中頁腳顯示「上一頁」，「下一頁」的按鈕。但如果要實現真正的頁面導航還是很難的,下面看看另一種方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應的id。這種方法還有一個好處。假設，網站上正在發布一片新的文章，那麼所有文章的位置都會往後移一位，所以如果用戶在發布文章時換頁，那麼他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發表過一篇類似的博客，利用了組合索引和兩個位置變數，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對應的頁碼存儲到表中，並在該列上創建合適的索引。採用這種方式，當新增一個記錄的時候，需要執行下面的查詢重新生成對應的頁號。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當然，也可以新增一個專用於分頁的表，可以用個後台程序來維護。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
現在想獲取任意一頁的元素就很簡單了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用於數據集相對小，並且沒有可用的索引的情況下—比如處理搜索結果時。在一個普通的伺服器上執行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下來就可以向下面一樣執行分頁查詢了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;
簡單來說，對於分頁的優化就是。。。避免數據量大時掃描過多的記錄。
⑦ （問題解決再追加100分）sql server存儲過程實現查詢數據條數過大，分頁查詢怎麼實現
按說5-8w這樣數量級的數據沒有問題，寫入Excel是布比較耗性能，主要還是要通過優化寫入Excel的代碼效率上去考慮。你可以考慮利用分批查詢寫入的方式來避免一次寫太多的數據到Excel：將你的查詢結果分段，比方你的語句中能不能用時間來認為分段，每次返回部分結果。
回到你的問題，對大數據量查詢的解決方案有以下兩種：
（1）、將全部數據先查詢到內存中，然後在內存中進行分頁，這種方式對內存佔用較大，必須限制一次查詢的數據量。
（2）、採用存儲過程在資料庫中進行分頁，這種方式對資料庫的依賴較大，不同的資料庫實現機制不通，並且查詢效率不夠理想。以上兩種方式對用戶來說都不夠友好。
2．解決思路
通過在待查詢的資料庫表上增加一個用於查詢的自增長欄位，然後採用該欄位進行分頁查詢，可以很好地解決這個問題。下面舉例說明這種分頁查詢方案。
（1）、在待查詢的表格上增加一個long型的自增長列，取名為「queryId」,mssql、sybase直接支持自增長欄位，oracle可以用sequence和trigger來實現。然後在該列上加上一個索引。
添加queryId列的語句如下：
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)
Sybase: QUERYID numeric(19) identity
Oracle:
CREATE SEQUENCE queryId_S
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999 MINVALUE 1
CYCLE
CACHE 20
ORDER;
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT
ON "test_table"
FOR EACH ROW
BEGIN
select queryId_S.nextval into :new.queryId from al;
END;
（2）、在查詢第一頁時，先按照大小順序的倒序查出所有的queryId，
語句如下：select queryId from test_table where + 查詢條件 +order by queryId desc 。
因為只是查詢queryId欄位，即使表格中的數據量很大，該查詢也會很快得到結果。然後將得到的queryId保存在應用伺服器的一個數組中。
（3）、用戶在客戶端進行翻頁操作時，客戶端將待查詢的頁號作為參數傳遞給應用伺服器，伺服器通過頁號和queyId數組算出待查詢的queyId最大和最小值，然後進行查詢。
算出queyId最大和最小值的演算法如下,其中page為待查詢的頁號，pageSize為每頁的大小，queryIds為第二步生成的queryId數組：
int startRow = (page - 1) * pageSize
int endRow = page * pageSize - 1;
if (endRow >=queryIds.length)
{
endRow = this.queryIds.length - 1;
}
long startId =queryIds[startRow];
long endId =queryIds[endRow];
查詢語句如下：
String sql = "select * from test_table" + 查詢條件 + "(queryId <= " + startId + " and queryId >= " + endId + ")";
3．效果評價
該分頁查詢方法對所有資料庫都適用，對應用伺服器、資料庫伺服器、查詢客戶端的cpu和內存佔用都較低，查詢速度較快，是一個較為理想的分頁查詢實現方案。經過測試，查詢4百萬條數據，可以在3分鍾內顯示出首頁數據，以後每一次翻頁操作基本在2秒以內。內存和cpu佔用無明顯增長。
以上也僅僅是分頁查詢結果查看的問題，你需要寫入到Excel的話還需要考慮Excel寫入代碼的執行效率，這部分是很值得研究的。
⑧ 用SQL寫出分頁查詢
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A --不排序
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
SELECT *
FROM (SELECT a.*, row_number() over(ORDER BY 1) rn--假排序，速度同上
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;
SELECT *
FROM (SELECT a.*, row_number() over(ORDER BY c1) rn --真實排序，無法比較速度
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;
Oracle的分頁查詢語句基本上可以按照本文給出的格式來進行套用。
分頁查詢格式：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的范圍。
上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果盡快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 40這句上。
選擇第21 到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。
這是由於CBO 優化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第一個查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。
而第二個查詢語句，由於查詢條件BETWEEN 21 AND 40是存在於查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什麼）。因此，對於第二個查詢語句，Oracle最內層返回給中間層的是所有滿足條件的數據，而中間層返回給最外層的也是所有數據。數據的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。
上面分析的查詢不僅僅是針對單表的簡單查詢，對於最內層查詢是復雜的多表聯合查詢或最內層查詢包含排序的情況一樣有效。
這里就不對包含排序的查詢進行說明了，下一篇文章會通過例子來詳細說明。下面簡單討論一下多表聯合的情況。對於最常見的等值表連接查詢，CBO 一般可能會採用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會考慮）。在這里，由於使用了分頁，因此指定了一個返回的最大記錄數，NESTED LOOP在返回記錄數超過最大值時可以馬上停止並將結果返回給中間層，而HASH JOIN必須處理完所有結果集（MERGE JOIN也是）。那麼在大部分的情況下，對於分頁查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的數據，越靠後面的頁數訪問幾率越小）。
因此，如果不介意在系統中使用HINT的話，可以將分頁的查詢語句改寫為：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
⑨ sql多表關聯，數據量比較大的分頁查詢，怎麼做，有沒有較好的方法，
可以做一個存儲過程，傳入參數的方式，參數可以有顯示第幾頁，分頁大小，可以返回總行數和查詢結果