pgsql 實(shí)現(xiàn)分頁(yè)查詢(xún)方式

我就廢話(huà)不多說(shuō)了，看代碼吧~

select
  row_number() over(order by 業(yè)務(wù)號(hào),主鍵,排序號(hào)) rn -- 行號(hào)
  ,count(0) over() cnt -- 總條數(shù)
  ,id

from 表

order by 排序號(hào),主鍵,業(yè)務(wù)號(hào)

offset (頁(yè)號(hào)- 1)* 每頁(yè)數(shù)量 limit 每頁(yè)數(shù)量

補(bǔ)充：postgreSQL單表數(shù)據(jù)量上千萬(wàn)分頁(yè)查詢(xún)緩慢的優(yōu)化方案

故事要這樣說(shuō)起，王鐵蛋是一個(gè)初入職場(chǎng)的程序猿，每天干的活就是實(shí)現(xiàn)各種簡(jiǎn)單的查詢(xún)業(yè)務(wù)，但是鐵蛋有一顆熱愛(ài)技術(shù)的心，每天都琢磨著如何寫(xiě)出花式的增刪改查操作。沒(méi)錯(cuò)平凡的鐵蛋的有著一個(gè)偉大的夢(mèng)想，成為一名高級(jí)CRUDER。（一不小心激動(dòng)了，開(kāi)水倒進(jìn)了我的花瓶）。

時(shí)間就這樣一天天的流逝，鐵蛋感覺(jué)不管自己的crud寫(xiě)的再花騷也不能達(dá)到高級(jí)cruder的級(jí)別，于是乎鐵蛋心一橫，接下了一個(gè)艱巨的任務(wù)，對(duì)單表數(shù)據(jù)量到百萬(wàn)千萬(wàn)級(jí)別的查詢(xún)頁(yè)面進(jìn)行優(yōu)化，這是鐵蛋工作任務(wù)上的一小步，卻是鐵蛋實(shí)現(xiàn)夢(mèng)想的一大步。

接任務(wù)簡(jiǎn)單，做任務(wù)難呀！ 這是鐵蛋第一天的感受，接了這個(gè)任務(wù)之后鐵蛋沒(méi)有一點(diǎn)頭緒，從哪下手呢？鐵蛋仔細(xì)一想既然要優(yōu)化，那么總得知道 哪里需要優(yōu)化吧？ 可以從哪些方面優(yōu)化吧？ 需要知道最如何分析瓶頸在哪吧？ 不料天降神圖，給了鐵蛋一個(gè)指引， 沒(méi)錯(cuò)就是數(shù)據(jù)庫(kù)可以?xún)?yōu)化的方向圖。

注：圖中效果的漸變其實(shí)不太準(zhǔn)確， 但是總的來(lái)說(shuō)如果不是SQL寫(xiě)的特別爛的話(huà)大體上優(yōu)化這些不同的方面對(duì)性能的影響是以圖中的示意變化的。

雖然有了神圖的指引，但是鐵蛋還是不知道應(yīng)該優(yōu)化哪個(gè)方面？ 不同方面的優(yōu)化方式是什么？經(jīng)過(guò)鐵蛋的一番努力查找（哈哈，這次不是上天相助了，總要努力下的， 不然這黑幕太明顯了），得到了以下信息：

從成本方面考慮，土豪的優(yōu)化方式向來(lái)簡(jiǎn)單粗暴，硬件不行就換硬件嘛, 不差錢(qián)?。。?但是鐵蛋不行呀，草根一枚，要錢(qián)沒(méi)錢(qián)， 要人沒(méi)人，只能選擇便宜的來(lái)下手了。柿子嘛還是得挑軟的捏，于是乎，鐵蛋躊躇滿(mǎn)志的找產(chǎn)品商量改需求。

咳咳 ?。。。≡趺凑f(shuō)呢？ 鐵蛋是為了降低成本，為公司控本降費(fèi)，初心是好的，但是呀這個(gè)做法嗯嗯啊啊。。。， 大家以此為戒哦?。?！

既然改需求不行，那就只能往下走了， 先來(lái)一波SQL優(yōu)化看看，要優(yōu)化SQL總得知道SQL慢在哪里了吧？

咋辦咋辦！ 不知道哪里慢咋辦？

還能咋辦，看SQL的執(zhí)行計(jì)劃唄！

不會(huì)看咋辦？

啥！ 不會(huì)看， 不會(huì)看學(xué)啊！

好吧，當(dāng)我沒(méi)問(wèn)?。?！

怎么看執(zhí)行計(jì)劃呢，首先你得會(huì)一個(gè)SQL的命令，叫EXPLAIN, 此命令用于查看SQL的執(zhí)行計(jì)劃。得此命令，鐵蛋如獲至寶， 拿起來(lái)就是一頓操作，看到命令輸出的結(jié)果后，鐵蛋傻眼了，這什么鬼？ 這怎么看？

怎么看？？？ 用眼睛看唄，還能怎么看。

總的來(lái)說(shuō)sql的執(zhí)行計(jì)劃是一個(gè)樹(shù)形層次結(jié)構(gòu)， 一般來(lái)說(shuō)閱讀上遵從層級(jí)越深越優(yōu)先， 同一層級(jí)由上到下的原則。

來(lái)跟著鐵蛋老師讀： 層級(jí)越深越優(yōu)先， 同一層級(jí)上到下。

順序知道了，得知道里面的意思了吧， 是的沒(méi)錯(cuò)， 但是這個(gè)里面比較具體的一些細(xì)節(jié)這里就不再展開(kāi)了，只介紹比較常關(guān)注的幾個(gè)關(guān)鍵字：

重點(diǎn)來(lái)了，重點(diǎn)來(lái)了，睡覺(jué)的玩手機(jī)的停一停。王老師要開(kāi)車(chē)了， 啊呸， 開(kāi)課了。

第一行的括號(hào)中從左到右依次代表的是：

（估計(jì)）啟動(dòng)成本，在開(kāi)始輸出之前花費(fèi)的時(shí)間，例如排序時(shí)間。

（估計(jì)）總成本， 這里有一個(gè)前提是計(jì)劃節(jié)點(diǎn)會(huì)完整運(yùn)行，即所有可用行都會(huì)被檢索。實(shí)際上一些節(jié)點(diǎn)的父節(jié)點(diǎn)不會(huì)檢索所有可用行（如LIMIT）。

（估計(jì)）輸出的總行數(shù)，同樣的是基于節(jié)點(diǎn)會(huì)完整運(yùn)行的假設(shè)。

（估計(jì)）輸出行的平均寬度（以字節(jié)為單位）

注意：

cost中描述的是啟動(dòng)成本和總成本，但是到目前為止我們還不知道這個(gè)數(shù)字代表的具體含義，因?yàn)槲覀儾恢浪膯挝皇鞘裁?。（所以說(shuō)這里cost中的成本是具有相對(duì)意義，不具有絕對(duì)意義）

rows代表的是輸出的總行數(shù)，他不是計(jì)劃節(jié)點(diǎn)處理或掃描的行數(shù)，而是節(jié)點(diǎn)發(fā)出的行數(shù)。由于使用where子句過(guò)濾，這個(gè)值通常小于掃描的數(shù)目。理想情況下，頂級(jí)的rows近似于實(shí)際的查詢(xún)返回，更新或刪除的行數(shù)

欲知詳情，且待鐵蛋老師的執(zhí)行計(jì)劃章節(jié)詳解，本課就不做衍生。

上圖中的 Index Scan代表索引掃描， Index Cond代表索引命中，后面是命中的具體的索引； Filter是過(guò)濾條件，跟具體的sql有關(guān)， 注意sort， sort中應(yīng)該是有兩行，下面的圖示中能夠看到， 第一行代表對(duì)那個(gè)鍵進(jìn)行排序， 第二行是排序方法（主要有內(nèi)存排序和磁盤(pán)排序，應(yīng)該避免磁盤(pán)排序）和數(shù)據(jù)大小。

explain還有兩個(gè)比較有用的參數(shù)一個(gè)是analyze, 一個(gè)是buffers。 加上第一個(gè)參數(shù)可以讓sql真正的執(zhí)行并且預(yù)估執(zhí)行時(shí)間， 第二參數(shù)可以查看緩存命中情況。

actual time對(duì)應(yīng)的意義和cost相似，但是不同于cost， actual time具有絕對(duì)意義，因?yàn)樗膯挝皇莔s。loops代表循環(huán)的次數(shù)。

緩存命中情況主要看Buffers這一行， hit就是命中情況，buffers的信息有助于確定查詢(xún)的哪部分是IO密集型的。

Hash節(jié)點(diǎn)主要看 Buckes， 哈希桶的數(shù)量， Batches：批處理的數(shù)量，批處理的數(shù)量如果超過(guò)1，則還會(huì)使用磁盤(pán)空間，但不會(huì)顯示。 Memory Usage代表內(nèi)存的使用峰值。

有了以上信息我們基本上就可以尋醫(yī)問(wèn)藥， 對(duì)癥下藥了， 該建索引的建索引， 查詢(xún)語(yǔ)句沒(méi)有命中索引的調(diào)整下sql，聯(lián)合索引條件過(guò)濾包含驅(qū)動(dòng)列，且驅(qū)動(dòng)列在前效率最高。

索引優(yōu)化小技巧：

索引盡量建在數(shù)據(jù)比較分散的列上， 不要在變化很小的字段上加索引，比如性別之類(lèi)的。

原因就是：

索引本質(zhì)上是一種空間換時(shí)間的操作，通過(guò)B Tree這種數(shù)據(jù)結(jié)構(gòu)減少io的操作次數(shù)以此來(lái)提升速度。如果在變化很小的字段上建立索引，那么可能單個(gè)葉子節(jié)點(diǎn)上的數(shù)據(jù)量也是龐大的，反而增加了io的次數(shù)（如果查詢(xún)字段有包含非索引列，索引命中之后還需要回表）

到了這里就開(kāi)始我們題目中的正文了， 分頁(yè)查詢(xún)性能優(yōu)化！??！

怎么優(yōu)化呢？ 經(jīng)過(guò)上述一系列的索引和sql優(yōu)化之后，鐵蛋老師發(fā)現(xiàn)雖然sql的執(zhí)行速度比以前快了，但是在單表一千萬(wàn)的量級(jí)下，這個(gè)查詢(xún)的速度還是有點(diǎn)龜速呀。

仔細(xì)看了上圖中的執(zhí)行計(jì)劃發(fā)現(xiàn)有三個(gè)個(gè)地方有嫌疑，一個(gè)是Hash節(jié)點(diǎn)， 一個(gè)是Sort， 還有一個(gè)是Buffers。

在Hash節(jié)點(diǎn)中Batches批處理的數(shù)量超過(guò)了1， 這說(shuō)明用到了外存， 原來(lái)是內(nèi)存不夠了呀！

Sort節(jié)點(diǎn)中，排序方法是歸并， 而且是磁盤(pán)排序， 原來(lái)也是內(nèi)存不夠了。

Buffers 節(jié)點(diǎn)中，同一個(gè)sql執(zhí)行兩次每次都有新的io，說(shuō)明緩存空間也不夠，最終這三個(gè)現(xiàn)象都指向了內(nèi)存。

鐵蛋打開(kāi)pg的配置文件一看， 我靠，窮鬼呀，才分配了512MB的共享緩存總空間， 進(jìn)程單獨(dú)分配了4M空間用于hash，排序等操作，用于維護(hù)的分配了512MB。

這哪行，再窮不能窮內(nèi)存呀！ 內(nèi)從都沒(méi)有怎么快，怎么快！

鐵蛋一看，服務(wù)器有64GB的內(nèi)存，恨不得都分過(guò)去，還好旁邊的二狗阻止了他。

二狗說(shuō)不是這么玩的， 共享緩存區(qū)的內(nèi)存一般分配是內(nèi)存的1/4，不超過(guò)總內(nèi)存的1/2。 線(xiàn)程內(nèi)存就看著給了，預(yù)計(jì)下峰值連接數(shù)和均值連接數(shù)，做一個(gè)權(quán)衡，適當(dāng)提高。

于是鐵蛋將共享緩存區(qū)的內(nèi)存分配為20GB， 單個(gè)線(xiàn)程用于hash和排序的分配了200MB。 重啟數(shù)據(jù)庫(kù)， 跑了下執(zhí)行計(jì)劃。 sql里面從以前的一分鐘，四五十秒變成了三四秒左右。

仔細(xì)看了下執(zhí)行計(jì)劃， sort中的磁盤(pán)排序變成了內(nèi)存排序，排序方法從歸并變成了快排。 Hash節(jié)點(diǎn)中批處理的數(shù)量也變成了1， Buffers中緩存全部命中。

到了這里優(yōu)化看似就完成了，但是還有些不太圓滿(mǎn)。 哪里不圓滿(mǎn)呢？ 明明sql的分頁(yè)查詢(xún)語(yǔ)句很快，為什么頁(yè)面上的分頁(yè)查詢(xún)還是要四五秒呢？

鐵蛋一拍腦袋，怎么把這個(gè)給忘了， 分頁(yè)查詢(xún)頁(yè)面有個(gè)總數(shù)統(tǒng)計(jì)， 總數(shù)統(tǒng)計(jì)的sql也需要占時(shí)間的呀？ 怎么辦？

有辦法， 不要慌？ 我們的原則就是兩條腿走路，兩個(gè)方針政策。

優(yōu)化全表掃描的速度 （為什么要優(yōu)化全表掃描的速度，因?yàn)榻y(tǒng)計(jì)總數(shù)的時(shí)候大多數(shù)情況下是不能避免全表掃描的）分頁(yè)查詢(xún)和統(tǒng)計(jì)的sql并行執(zhí)行怎么實(shí)行？

優(yōu)化全表掃描的速度還得從服務(wù)器下手， 全表掃描慢是因?yàn)榉?wù)器的IO慢，鐵蛋恨不得把這個(gè)82年的機(jī)械硬盤(pán)換成SSD，但是人微言輕，只能從其他方面下手： 調(diào)大IO預(yù)讀的大小

#查看當(dāng)前預(yù)讀大小
blockdev --getra /dev/vda
#設(shè)置預(yù)讀大小 , 4096的單位是扇區(qū)，即512bytes
blockdev --setra 4096 /dev/vda

注意：上面的命令在服務(wù)器重啟之后失效，所以想永久生效需要將此命令放到 /etc/rc.local 開(kāi)機(jī)自啟動(dòng)腳本中。

sql并行化的實(shí)現(xiàn)也比較容易，在一開(kāi)始就向線(xiàn)程池提交一個(gè)統(tǒng)計(jì)sql'的任務(wù)， 等到分頁(yè)查詢(xún)的數(shù)據(jù)處理完成最后要返回給前端之前找線(xiàn)程池要總數(shù)就行了，如果沒(méi)有執(zhí)行完，會(huì)阻塞等待執(zhí)行完，所以響應(yīng)時(shí)間就可以控制在sql執(zhí)行時(shí)間最長(zhǎng)的那段時(shí)間之內(nèi)了。

至此優(yōu)化任務(wù)算是完成個(gè)七七八八了，但是鐵蛋突然手一抖點(diǎn)了最后一頁(yè)，哎發(fā)現(xiàn)怎么最后一頁(yè)查詢(xún)的速度要比第一頁(yè)慢上一些，怎么回事？

因?yàn)槿绻鹲ql涉及到針對(duì)某個(gè)字段的排序，那么往后翻頁(yè)的時(shí)候如果采用的是limit offset 的方式會(huì)變得很慢，因?yàn)閿?shù)據(jù)庫(kù)需要先把前面的數(shù)據(jù)都讀出來(lái)然后扔掉前面不需要的。這個(gè)時(shí)候一般情況下沒(méi)有太多sql上的技巧可以?xún)?yōu)化了，只有在某些個(gè)特殊情況下可以采用一些小技巧。

方法是錨點(diǎn)定位法或者叫點(diǎn)位過(guò)濾，差不多就這個(gè)叫法，知道意思就行。

這個(gè)定位是怎么做的呢，如果當(dāng)你的查詢(xún)不帶過(guò)濾條件， （比如你的個(gè)人訂單記錄，只是比較下，不要細(xì)糾）。且你的數(shù)據(jù)中有一個(gè)遞增且連續(xù)的字段（注意一定要連續(xù)），那么就可以通過(guò)翻頁(yè)前的最后一條數(shù)據(jù)的id來(lái)定位下一頁(yè)的位置， 或者直接根據(jù)分頁(yè)大小和要跳轉(zhuǎn)的頁(yè)碼直接定位到你要翻頁(yè)的地方，一般情況下這個(gè)字段是主鍵。

示例：

select id, time from a order by time limit 10 offset 1000;
//錨點(diǎn)定位就是
select id, time from a where id in (select id from a where id > 1000 limit 10) 
order by time
//或者直接
select id, time from a where id > 1000 order by time limit 10 

寫(xiě)在最后的鐵蛋老師的忠告， 如果在某些情況下通過(guò)某個(gè)索引去查詢(xún)的時(shí)候因?yàn)閿?shù)據(jù)離散存儲(chǔ)導(dǎo)致的索引命中之后回表IO放大導(dǎo)致查詢(xún)緩慢的問(wèn)題，可以通過(guò)CLUSTER 命令強(qiáng)制數(shù)據(jù)按照某個(gè)索引的順序密集存儲(chǔ)。

cluster a using index_name

如何查看數(shù)據(jù)是不是離散存儲(chǔ)，很簡(jiǎn)單??！ 在selec語(yǔ)句中加上ctid字段。

ctid | id
-------+----
 (0,1) | 10
 (0,2) | 11

ctid的第一個(gè)數(shù)字代表塊號(hào)， 第二個(gè)代表行號(hào)， 就是第幾塊的第幾行， 所以通過(guò)此字段就能看出離散程度。

至此優(yōu)化任務(wù)結(jié)束了， 鐵蛋老師感覺(jué)舉例自己的CRUDER 的夢(mèng)想又近了一步。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。