sqlserver高效分頁

Ⅰ sqlserver 聚合 如何分頁

我看沒人回答我再上。
-----------------------------
這個用子查詢就可以了！如果嵌套很多，證明你的資料庫設計很蹩腳。
給你個例子：
----------
SELECT TOP 10
convert(varchar(10),K.ID) as ID,
convert(varchar(20),convert(decimal(18,2),K.SumRealSaleCost)) as SumRealSaleCost,
K.CountRes
from
(
select
ROW_NUMBER() OVER (ORDER BY GetDate()) AS ID,
(sum(M.Sale) over()-sum(M.ReturnSaleCost) over()) as SumRealSaleCost,
count(*) over() as CountRes
from
(
---這里你愛用什麼聚合函數就用什麼聚合函數，我只用了一個簡單的子查詢而已！
select
A.Sale,
A.ReturnSaleCost
from ProctStore A
) M
) K
where K.ID >？
--這個排序最好和分頁函數一致。
ORDER BY K.Sale DESC
--------------------
最外層為類型轉化層
中間是數據邏輯層
最內層就是集合構建層
我這種寫法很多人不理解，本人原創。你網路不到！
---------------------
如果LZ資料庫有功底的話，加入我的團隊吧！資料庫聚賢庄

Ⅱ 幾種常見SQL分頁方式效率比較，一帖子

sqlserver分頁 第一種分頁方法 需用到的參數: pageSize 每頁顯示多少條數據 pageNumber 頁數 從客戶端傳來 totalRecouds 表中的總記錄數 select count (*) from 表名 totalPages 總頁數 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1 pages 計算前pages 條數據 pages= pageSize*(pageNumber-1) SQL語句: select top pageSize * from 表名 where id not in (select top pages id from 表名 order by id) order by id 第二種分頁方法 pageSize 每頁顯示多少條數據 pageNumber 頁數 從客戶端傳來 pages=pageSize*(pageNumber-1)+1 select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t ) mysql分頁 需用到的參數: pageSize 每頁顯示多少條數據 pageNumber 頁數 從客戶端傳來 totalRecouds 表中的總記錄數 select count (*) from 表名 totalPages 總頁數 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1 pages 起始位置 pages= pageSize*(pageNumber-1) SQL語句: select * from 表名 limit pages, pageSize; mysql 分頁依賴於關鍵字 limit 它需兩個參數:起始位置和pageSize 起始位置=頁大小*(頁數-1) 起始位置=pageSize*(pageNumber -1) oracle分頁 pageSize 每頁顯示多少條數據 pageNumber 頁數 從客戶端傳來 totalRecouds 表中的總記錄數 select count (*) from 表名 totalPages 總頁數 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1 startPage 起始位置 startPage= pageSize*(pageNumber-1)+1 endPage=startPage+pageSize SQL語句 select a.* from ( select rownum num ,t.* from 表名 t where 某列=某值 order by id asc )a where a.num>=startPage and a.num<endPage db2分頁 int startPage=1 //起始頁 int endPage; //終止頁 int pageSize=5; //頁大小 int pageNumber=1 //請求頁 startPage=(pageNumber-1)*pageSize+1 endPage=(startPage+pageSize); SQL語句 select * from (select 欄位1,欄位2,欄位3,欄位4,欄位5,rownumber() over(order by 排序欄位 asc ) as rowid from 表名 )as a where a.rowid >= startPage AND a.rowid <endPage access分頁 pageSize 每頁顯示多少條數據 pageNumber 頁數 從客戶端傳來 pages=pageSize*(pageNumber-1)+1 SQL語句 select top pageSize * from 表名 where id>=(select max(id) from (select top pages id from 表名 order by id asc ) t )

Ⅲ sqlserver2000 如何提高分頁查詢大數據量的效率

sqlserver2005及以上的版本有row_number()函數可以高效分頁，sqlserver2000的話只能看演算法了

Ⅳ sqlserver查詢分頁問題，條件比較復雜，怎樣實現較好求指點

盡量不要使用嵌套子查詢，優先使用連接子查詢；
In 關鍵字盡量少使用，效率低，除非 in 包含的信息量非常少，並且叫固定；
如果查詢條件和查詢結果表無直接關聯，優先根據查詢條件查詢出符合條件的臨時結果值，然後和最終結果表關聯；
除 top n 方式分頁之外，使用 row_number() over 方式分頁也可以；
能明確指定結果欄位的，盡量不要用 * ；
不相關的欄位可以省略；
如果最終結果表數據比較大，考慮分表，分區，分庫；
查詢結果表欄位盡量不要包含大文本之類的欄位；
where 條件中對於varchar 欄位 的 like 查詢要適度，不要用太多，會影響效率；

Ⅳ 如何優化Mysql千萬級快速分頁

很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過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;

簡單來說，對於分頁的優化就是。。。避免數據量大時掃描過多的記錄。

Ⅵ 如果在資料庫中有大數據量，而我們用分頁存儲過程，怎麼樣才能效率高

--------------------------------
--關於分頁儲存的效率問題
--5個存儲過程都是採用不同的方式
--------------------------------
------------------------------------------
--利用select top 和select not in進行分頁--
------------------------------------------
create procere proc_paged_with_notin --利用select top and select not in
(
@pageIndex int, --頁索引
@pageSize int --每頁記錄數
)
as
begin
set nocount on;
declare @timediff datetime --耗時
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
execute(@sql) --因select top後不支技直接接參數，所以寫成了字元串@sql
select datediff(ms,@timediff,GetDate()) as 耗時
set nocount off;
endexec proc_paged_with_notin 10000,10
--------------------------------------
--利用select top 和 select max(列鍵)--
--------------------------------------
create procere proc_paged_with_selectMax --利用select top and select max(列)
(
@pageIndex int, --頁索引
@pageSize int --頁記錄數
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'
execute(@sql)
select datediff(ms,@timediff,GetDate()) as 耗時
set nocount off;
end--------------------------------------------------------
--利用select top和中間變數--此方法因網上有人說效果最佳--
--------------------------------------------------------
create procere proc_paged_with_Midvar --利用ID>最大ID值和中間變數
(
@pageIndex int,
@pageSize int
)
as
declare @count int
declare @ID int
declare @timediff datetime
declare @sql nvarchar(500)
begin
set nocount on;
select @count=0,@ID=0,@timediff=getdate()
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id
set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)
execute(@sql)
select datediff(ms,@timediff,getdate()) as 耗時
set nocount off;
end
---------------------------------------------------------------------------------------
--利用Row_number() 此方法為SQL server 2005中新的方法,利用Row_number()給數據行加上索引--
---------------------------------------------------------------------------------------
create procere proc_paged_with_Rownumber --利用SQL 2005中的Row_number()
(
@pageIndex int,
@pageSize int
)
as
declare @timediff datetime
begin
set nocount on;
select @timediff=getdate()
select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1)
select datediff(ms,@timediff,getdate()) as 耗時
set nocount off;
end
--------------------------
--利用臨時表及Row_number--
--------------------------
create procere proc_CTE --利用臨時表及Row_number
(
@pageIndex int, --頁索引
@pageSize int --頁記錄數
)
as
set nocount on;
declare @ctestr nvarchar(400)
declare @strSql nvarchar(400)
declare @datediff datetime
begin
select @datediff=GetDate()
set @ctestr='with Table_CTE as
(select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';
set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)
end
begin
execute sp_executesql @strSql
select datediff(ms,@datediff,GetDate())
set nocount off;
end
我們分別在每頁10條數據的情況下在第2頁，第1000頁，第10000頁，第100000頁，第199999頁進行測試，耗時單位：ms 每頁測試5次取其平均值 存過第2頁耗時第1000頁耗時第10000頁耗時第100000頁耗時第199999頁耗時效率排行1用not in0ms16ms47ms475ms953ms32用select max5ms16ms35ms325ms623ms13中間變數_number0ms0ms34ms365ms710ms24臨時表780ms796ms798ms780ms805ms4正好我正在研究這個問題 給大家分享

Ⅶ sqlserver分頁查詢，誰給我解釋解釋啊為什麼這個效率就高，看不懂是怎麼查詢的

簡單點說 就好比 現在有 100斤的東西 不分頁就是直接拿100斤 sqlserver分頁 就是一次拿20斤 你說哪個拿得快一點

Ⅷ 資料庫sqlserver如何用存儲過程做分頁

存儲過程：create Procere pname
( @pageIndex int，@pageSize)
as
select * from tableName order by id
offset @pageIndex * pageSize fetch next pageSize rows only
分頁：
sqlserver 在2008之前 使用 top 和 not int top 的方式來做分頁
2008以後使用 row_number() 函數作為分頁關鍵函數
2012使用 offset 1 fetch next 10 rows only
你問了2個問題，你可以優先把視圖，存儲過程，觸發器等弄明白，分頁是查詢，在存儲過程里可以寫復雜的sql文，只是在運行時是預編譯和參數化查詢防止sql注入