如何pgsql中實現一個分頁查詢功能

本篇文章為大家展示了如何pgsql中實現一個分頁查詢功能，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

select
  row_number() over(order by 業務號,主鍵,排序號) rn -- 行號
  ,count(0) over() cnt -- 總條數
  ,id

from 表

order by 排序號,主鍵,業務號

offset (頁號- 1)* 每頁數量 limit 每頁數量

補充：postgreSQL單表數據量上千萬分頁查詢緩慢的優化方案

故事要這樣說起，王鐵蛋是一個初入職場的程序猿，每天干的活就是實現各種簡單的查詢業務，但是鐵蛋有一顆熱愛技術的心，每天都琢磨著如何寫出花式的增刪改查操作。沒錯平凡的鐵蛋的有著一個偉大的夢想，成為一名高級CRUDER。（一不小心激動了，開水倒進了我的花瓶）。

時間就這樣一天天的流逝，鐵蛋感覺不管自己的crud寫的再花騷也不能達到高級cruder的級別，于是乎鐵蛋心一橫，接下了一個艱巨的任務，對單表數據量到百萬千萬級別的查詢頁面進行優化，這是鐵蛋工作任務上的一小步，卻是鐵蛋實現夢想的一大步。

接任務簡單，做任務難呀！ 這是鐵蛋第一天的感受，接了這個任務之后鐵蛋沒有一點頭緒，從哪下手呢？鐵蛋仔細一想既然要優化，那么總得知道 哪里需要優化吧？ 可以從哪些方面優化吧？ 需要知道最如何分析瓶頸在哪吧？ 不料天降神圖，給了鐵蛋一個指引， 沒錯就是數據庫可以優化的方向圖。

注：圖中效果的漸變其實不太準確， 但是總的來說如果不是SQL寫的特別爛的話大體上優化這些不同的方面對性能的影響是以圖中的示意變化的。

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

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

咳咳 ！！！！怎么說呢？ 鐵蛋是為了降低成本，為公司控本降費，初心是好的，但是呀這個做法嗯嗯啊啊。。。， 大家以此為戒哦！！！

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

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

還能咋辦，看SQL的執行計劃唄！

不會看咋辦？

啥！ 不會看， 不會看學啊！

好吧，當我沒問！！！

怎么看執行計劃呢，首先你得會一個SQL的命令，叫EXPLAIN, 此命令用于查看SQL的執行計劃。得此命令，鐵蛋如獲至寶， 拿起來就是一頓操作，看到命令輸出的結果后，鐵蛋傻眼了，這什么鬼？ 這怎么看？

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

總的來說sql的執行計劃是一個樹形層次結構， 一般來說閱讀上遵從層級越深越優先， 同一層級由上到下的原則。

來跟著鐵蛋老師讀： 層級越深越優先， 同一層級上到下。

順序知道了，得知道里面的意思了吧， 是的沒錯， 但是這個里面比較具體的一些細節這里就不再展開了，只介紹比較常關注的幾個關鍵字：

重點來了，重點來了，睡覺的玩手機的停一停。王老師要開車了， 啊呸， 開課了。

第一行的括號中從左到右依次代表的是：

（估計）啟動成本，在開始輸出之前花費的時間，例如排序時間。

（估計）總成本， 這里有一個前提是計劃節點會完整運行，即所有可用行都會被檢索。實際上一些節點的父節點不會檢索所有可用行（如LIMIT）。

（估計）輸出的總行數，同樣的是基于節點會完整運行的假設。

（估計）輸出行的平均寬度（以字節為單位）

注意：

cost中描述的是啟動成本和總成本，但是到目前為止我們還不知道這個數字代表的具體含義，因為我們不知道它的單位是什么。（所以說這里cost中的成本是具有相對意義，不具有絕對意義）

rows代表的是輸出的總行數，他不是計劃節點處理或掃描的行數，而是節點發出的行數。由于使用where子句過濾，這個值通常小于掃描的數目。理想情況下，頂級的rows近似于實際的查詢返回，更新或刪除的行數

欲知詳情，且待鐵蛋老師的執行計劃章節詳解，本課就不做衍生。

上圖中的 Index Scan代表索引掃描， Index Cond代表索引命中，后面是命中的具體的索引； Filter是過濾條件，跟具體的sql有關， 注意sort， sort中應該是有兩行，下面的圖示中能夠看到， 第一行代表對那個鍵進行排序， 第二行是排序方法（主要有內存排序和磁盤排序，應該避免磁盤排序）和數據大小。

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

actual time對應的意義和cost相似，但是不同于cost， actual time具有絕對意義，因為它的單位是ms。loops代表循環的次數。

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

Hash節點主要看 Buckes， 哈希桶的數量， Batches：批處理的數量，批處理的數量如果超過1，則還會使用磁盤空間，但不會顯示。 Memory Usage代表內存的使用峰值。

有了以上信息我們基本上就可以尋醫問藥， 對癥下藥了， 該建索引的建索引， 查詢語句沒有命中索引的調整下sql，聯合索引條件過濾包含驅動列，且驅動列在前效率最高。

索引優化小技巧：

索引盡量建在數據比較分散的列上， 不要在變化很小的字段上加索引，比如性別之類的。

原因就是：

索引本質上是一種空間換時間的操作，通過B Tree這種數據結構減少io的操作次數以此來提升速度。如果在變化很小的字段上建立索引，那么可能單個葉子節點上的數據量也是龐大的，反而增加了io的次數（如果查詢字段有包含非索引列，索引命中之后還需要回表）

到了這里就開始我們題目中的正文了， 分頁查詢性能優化！！！

怎么優化呢？ 經過上述一系列的索引和sql優化之后，鐵蛋老師發現雖然sql的執行速度比以前快了，但是在單表一千萬的量級下，這個查詢的速度還是有點龜速呀。

仔細看了上圖中的執行計劃發現有三個個地方有嫌疑，一個是Hash節點， 一個是Sort， 還有一個是Buffers。

在Hash節點中Batches批處理的數量超過了1， 這說明用到了外存， 原來是內存不夠了呀！

Sort節點中，排序方法是歸并， 而且是磁盤排序， 原來也是內存不夠了。

Buffers 節點中，同一個sql執行兩次每次都有新的io，說明緩存空間也不夠，最終這三個現象都指向了內存。

鐵蛋打開pg的配置文件一看， 我靠，窮鬼呀，才分配了512MB的共享緩存總空間， 進程單獨分配了4M空間用于hash，排序等操作，用于維護的分配了512MB。

這哪行，再窮不能窮內存呀！ 內從都沒有怎么快，怎么快！

鐵蛋一看，服務器有64GB的內存，恨不得都分過去，還好旁邊的二狗阻止了他。

二狗說不是這么玩的， 共享緩存區的內存一般分配是內存的1/4，不超過總內存的1/2。 線程內存就看著給了，預計下峰值連接數和均值連接數，做一個權衡，適當提高。

于是鐵蛋將共享緩存區的內存分配為20GB， 單個線程用于hash和排序的分配了200MB。 重啟數據庫， 跑了下執行計劃。 sql里面從以前的一分鐘，四五十秒變成了三四秒左右。

仔細看了下執行計劃， sort中的磁盤排序變成了內存排序，排序方法從歸并變成了快排。 Hash節點中批處理的數量也變成了1， Buffers中緩存全部命中。

到了這里優化看似就完成了，但是還有些不太圓滿。 哪里不圓滿呢？ 明明sql的分頁查詢語句很快，為什么頁面上的分頁查詢還是要四五秒呢？

鐵蛋一拍腦袋，怎么把這個給忘了， 分頁查詢頁面有個總數統計， 總數統計的sql也需要占時間的呀？ 怎么辦？

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

優化全表掃描的速度 （為什么要優化全表掃描的速度，因為統計總數的時候大多數情況下是不能避免全表掃描的）分頁查詢和統計的sql并行執行怎么實行？

優化全表掃描的速度還得從服務器下手， 全表掃描慢是因為服務器的IO慢，鐵蛋恨不得把這個82年的機械硬盤換成SSD，但是人微言輕，只能從其他方面下手： 調大IO預讀的大小

#查看當前預讀大小
blockdev --getra /dev/vda
#設置預讀大小 , 4096的單位是扇區，即512bytes
blockdev --setra 4096 /dev/vda

注意：上面的命令在服務器重啟之后失效，所以想永久生效需要將此命令放到 /etc/rc.local 開機自啟動腳本中。

sql并行化的實現也比較容易，在一開始就向線程池提交一個統計sql'的任務， 等到分頁查詢的數據處理完成最后要返回給前端之前找線程池要總數就行了，如果沒有執行完，會阻塞等待執行完，所以響應時間就可以控制在sql執行時間最長的那段時間之內了。

至此優化任務算是完成個七七八八了，但是鐵蛋突然手一抖點了最后一頁，哎發現怎么最后一頁查詢的速度要比第一頁慢上一些，怎么回事？

因為如果sql涉及到針對某個字段的排序，那么往后翻頁的時候如果采用的是limit offset 的方式會變得很慢，因為數據庫需要先把前面的數據都讀出來然后扔掉前面不需要的。這個時候一般情況下沒有太多sql上的技巧可以優化了，只有在某些個特殊情況下可以采用一些小技巧。

方法是錨點定位法或者叫點位過濾，差不多就這個叫法，知道意思就行。

這個定位是怎么做的呢，如果當你的查詢不帶過濾條件， （比如你的個人訂單記錄，只是比較下，不要細糾）。且你的數據中有一個遞增且連續的字段（注意一定要連續），那么就可以通過翻頁前的最后一條數據的id來定位下一頁的位置， 或者直接根據分頁大小和要跳轉的頁碼直接定位到你要翻頁的地方，一般情況下這個字段是主鍵。

示例：

select id, time from a order by time limit 10 offset 1000;
//錨點定位就是
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

寫在最后的鐵蛋老師的忠告， 如果在某些情況下通過某個索引去查詢的時候因為數據離散存儲導致的索引命中之后回表IO放大導致查詢緩慢的問題，可以通過CLUSTER 命令強制數據按照某個索引的順序密集存儲。

cluster a using index_name

如何查看數據是不是離散存儲，很簡單！！ 在selec語句中加上ctid字段。

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

ctid的第一個數字代表塊號， 第二個代表行號， 就是第幾塊的第幾行， 所以通過此字段就能看出離散程度。

上述內容就是如何pgsql中實現一個分頁查詢功能，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。