問答專欄Q & A COLUMN
“我是喲喲吼說科技，專注于數(shù)據(jù)網(wǎng)絡(luò)的回答，歡迎大家與我交流數(shù)據(jù)網(wǎng)絡(luò)的問題”
如題，如何將數(shù)據(jù)庫查詢出來的數(shù)據(jù)進(jìn)行分頁操作？
喲喲簡(jiǎn)單說一下分析及操作步驟：
a、創(chuàng)建所需要的屬性，如當(dāng)前的頁碼CurrentPage；
b、通過數(shù)據(jù)庫查出得到數(shù)據(jù)的總和TotalConunt；
c、分析一共需要多少頁TotalPage（所有數(shù)據(jù)的總和/CurrentCount，采用math.ceil()方法）；
d、生成setter和getter方法；
a、創(chuàng)建一個(gè)service層的對(duì)象；
b、在service層創(chuàng)建findPageCategory方法，目的是為了獲取跟分頁有關(guān)的所有屬性，如TotalPage，TotalCount等；
a、查詢數(shù)據(jù)庫中所有數(shù)據(jù)的總和；
b、分頁查詢數(shù)據(jù)，即使用SQL聚合語句limit查詢數(shù)據(jù)后第一個(gè)數(shù)據(jù)的起始位置，第二個(gè)參數(shù)表示每個(gè)分頁查詢的數(shù)據(jù)條數(shù)；
歡迎大家多多關(guān)注我，在下方評(píng)論區(qū)說出自己的見解。
評(píng)論0
加載中...
我之前寫過《如何在SQLServer查詢中實(shí)現(xiàn)高效分頁》專門講述分頁的，現(xiàn)摘錄部分，希望可以解決問題。
從MSSQL2005開始，SQL Server提供了一個(gè)內(nèi)置函數(shù)ROW_NUMBER，這是一個(gè)非常神奇的函數(shù)。從MSSQL2012開始，SQL Server提供了offset方法進(jìn)行分頁。
今天我們就談?wù)凴OW_NUMBER和offset的語法和在分頁中的應(yīng)用。
ROW_NUMBER實(shí)現(xiàn)對(duì)結(jié)果集的輸出進(jìn)行編號(hào)。 具體來說，返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從 1 開始。
ROW_NUMBER ( ) OVER ( order_by_clause )
offset是order by的子句，主要用來限定返回的行數(shù)，用來做分頁也是很合適的。只是從MSSQL2012才開始支持。語法結(jié)構(gòu)如下：
FETCH { NEXT } { integer_constant | fetch_row_count_expression } { ROWS } ONLY
fetch_row_count_expression 可以是變量、參數(shù)或常量標(biāo)量子查詢。 在使用子查詢時(shí)，它無法引用在外部查詢范圍中定義的任何列。也就是說，它無法與外部查詢相關(guān)聯(lián)。
結(jié)合到分頁，語法語法：
offset startPage rows fetch next pageSize rows only
其中起始頁面：startPage=(@page-1)*@rows，頁面大小：pageSize=@rows
演示數(shù)據(jù)準(zhǔn)備的代碼我就不粘貼了，直接看下圖：
比如我們希望按照業(yè)務(wù)員+銷售區(qū)域排序后，每4條記錄一頁顯示，語法如下：
declare @pagesize int =4;--每頁記錄數(shù)
declare @pagenum int =1;--第幾頁
with saledata as
(
select row_number() over(order by FName,FDistrict) as FRowIndex,* from @sale
)
select * from saledata
where FRowIndex between @pagesize*(@pagenum-1)+1
and @pagenum*@pagesize;
重復(fù)的代碼部分不再贅述，在查詢時(shí)要注意，offset是Order By的子句，不能獨(dú)立存在。語法結(jié)構(gòu)如下：
select * from @sale
order by FName,FDistrict
offset (@pagenum-1)*@pagesize rows fetch next @pagesize rows only
返回的結(jié)果與使用row_number是一致的。完整的測(cè)試腳本參看下圖：
希望能解決題主所提的問題！
評(píng)論0
加載中...
很多應(yīng)用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個(gè)分頁的導(dǎo)航欄。然而，如何通過MySQL更好的實(shí)現(xiàn)分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數(shù)據(jù)庫的底層或多或少有助于優(yōu)化分頁查詢。
我們先從一個(gè)常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個(gè)查詢耗時(shí)0.00sec。So，這個(gè)查詢有什么問題呢？實(shí)際上，這個(gè)查詢語句和參數(shù)都沒有問題，因?yàn)樗玫搅讼旅姹淼闹麈I，而且只讀取15條記錄。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的問題在于offset(分頁偏移量)很大的時(shí)候，像下面這樣：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查詢?cè)谟?M行記錄時(shí)需要0.22sec，通過EXPLAIN查看SQL的執(zhí)行計(jì)劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會(huì)增加使用的數(shù)據(jù)，MySQL會(huì)將大量最終不會(huì)使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設(shè)大部分網(wǎng)站的用戶只訪問前幾頁數(shù)據(jù)，但少量的大的分頁偏移量的請(qǐng)求也會(huì)對(duì)整個(gè)系統(tǒng)造成危害。Facebook意識(shí)到了這一點(diǎn)，但Facebook并沒有為了每秒可以處理更多的請(qǐng)求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請(qǐng)求響應(yīng)時(shí)間的方差變小。
對(duì)于分頁請(qǐng)求，還有一個(gè)信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB為存儲(chǔ)引擎時(shí)需要耗費(fèi)9.28sec。一個(gè)不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時(shí)事先準(zhǔn)備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句 select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢語句簡(jiǎn)短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個(gè)語句的查詢性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
這個(gè)語句耗時(shí)20.02sec，是上一個(gè)的兩倍。事實(shí)證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。
高效的計(jì)算行數(shù)
如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會(huì)將行數(shù)存儲(chǔ)到表的元信息中。但如果引擎是InnoDB情況就會(huì)復(fù)雜一些，因?yàn)镮nnoDB不保存表的具體行數(shù)。
我們可以將行數(shù)緩存起來，然后可以通過一個(gè)守護(hù)進(jìn)程定期更新或者用戶的某些操作導(dǎo)致緩存失效時(shí)，執(zhí)行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進(jìn)入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經(jīng)說過了，大的偏移量會(huì)影響性能，所以我們要重寫查詢語句。為了演示，我們創(chuàng)建一個(gè)新的表“news”，按照時(shí)事性排序(最新發(fā)布的在最前面)，實(shí)現(xiàn)一個(gè)高性能的分頁。為了簡(jiǎn)單，我們就假設(shè)最新發(fā)布的新聞的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一個(gè)比較高效的方式是基于用戶展示的最后一個(gè)新聞Id。查詢下一頁的語句如下，需要傳入當(dāng)前頁面展示的最后一個(gè)Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查詢上一頁的語句類似，只不過需要傳入當(dāng)前頁的第一個(gè)Id，并且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查詢方式適合實(shí)現(xiàn)簡(jiǎn)易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實(shí)現(xiàn)真正的頁面導(dǎo)航還是很難的,下面看看另一種方式。
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;
通過上面的語句可以為每一個(gè)分頁的按鈕計(jì)算出一個(gè)offset對(duì)應(yīng)的id。這種方法還有一個(gè)好處。假設(shè)，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會(huì)往后移一位，所以如果用戶在發(fā)布文章時(shí)換頁，那么他會(huì)看見一篇文章兩次。如果固定了每個(gè)按鈕的offset Id，這個(gè)問題就迎刃而解了。Mark Callaghan發(fā)表過一篇類似的博客，利用了組合索引和兩個(gè)位置變量，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對(duì)應(yīng)的頁碼存儲(chǔ)到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當(dāng)新增一個(gè)記錄的時(shí)候，需要執(zhí)行下面的查詢重新生成對(duì)應(yīng)的頁號(hào)。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當(dāng)然，也可以新增一個(gè)專用于分頁的表，可以用個(gè)后臺(tái)程序來維護(hù)。
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;
現(xiàn)在想獲取任意一頁的元素就很簡(jiǎn)單了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數(shù)據(jù)集相對(duì)小，并且沒有可用的索引的情況下—比如處理搜索結(jié)果時(shí)。在一個(gè)普通的服務(wù)器上執(zhí)行下面的查詢，當(dāng)有2M條記錄時(shí)，要耗費(fèi)2sec左右。這種方式比較簡(jiǎn)單，創(chuàng)建一個(gè)用來存儲(chǔ)所有Id的臨時(shí)表即可(這也是最耗費(fèi)性能的地方)。
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;
接下來就可以向下面一樣執(zhí)行分頁查詢了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;
簡(jiǎn)單來說，對(duì)于分頁的優(yōu)化就是。。。避免數(shù)據(jù)量大時(shí)掃描過多的記錄。
評(píng)論0
加載中...
limit (page-1)*pagesize，pagesie＃＃mysql --oracle rownum三重查詢排序分頁
評(píng)論0
加載中...0
回答0
回答0
回答0
回答0
回答0
回答0
回答0
回答0
回答0
回答
