我就廢話不多說了，看代碼吧~
select
row_number() over(order by 業(yè)務(wù)號,主鍵,排序號) rn -- 行號
,count(0) over() cnt -- 總條數(shù)
,id
from 表
order by 排序號,主鍵,業(yè)務(wù)號
offset (頁號- 1)* 每頁數(shù)量 limit 每頁數(shù)量
補充：postgreSQL單表數(shù)據(jù)量上千萬分頁查詢緩慢的優(yōu)化方案
故事要這樣說起，王鐵蛋是一個初入職場的程序猿，每天干的活就是實現(xiàn)各種簡單的查詢業(yè)務(wù)，但是鐵蛋有一顆熱愛技術(shù)的心，每天都琢磨著如何寫出花式的增刪改查操作。沒錯平凡的鐵蛋的有著一個偉大的夢想，成為一名高級CRUDER。（一不小心激動了，開水倒進了我的花瓶）。
時間就這樣一天天的流逝，鐵蛋感覺不管自己的crud寫的再花騷也不能達到高級cruder的級別，于是乎鐵蛋心一橫，接下了一個艱巨的任務(wù)，對單表數(shù)據(jù)量到百萬千萬級別的查詢頁面進行優(yōu)化，這是鐵蛋工作任務(wù)上的一小步，卻是鐵蛋實現(xiàn)夢想的一大步。
接任務(wù)簡單，做任務(wù)難呀！ 這是鐵蛋第一天的感受，接了這個任務(wù)之后鐵蛋沒有一點頭緒，從哪下手呢？鐵蛋仔細一想既然要優(yōu)化，那么總得知道 哪里需要優(yōu)化吧？ 可以從哪些方面優(yōu)化吧？ 需要知道最如何分析瓶頸在哪吧？ 不料天降神圖，給了鐵蛋一個指引， 沒錯就是數(shù)據(jù)庫可以優(yōu)化的方向圖。
注：圖中效果的漸變其實不太準(zhǔn)確， 但是總的來說如果不是SQL寫的特別爛的話大體上優(yōu)化這些不同的方面對性能的影響是以圖中的示意變化的。
雖然有了神圖的指引，但是鐵蛋還是不知道應(yīng)該優(yōu)化哪個方面？ 不同方面的優(yōu)化方式是什么？經(jīng)過鐵蛋的一番努力查找（哈哈，這次不是上天相助了，總要努力下的， 不然這黑幕太明顯了），得到了以下信息：
從成本方面考慮，土豪的優(yōu)化方式向來簡單粗暴，硬件不行就換硬件嘛, 不差錢?。。?但是鐵蛋不行呀，草根一枚，要錢沒錢， 要人沒人，只能選擇便宜的來下手了。柿子嘛還是得挑軟的捏，于是乎，鐵蛋躊躇滿志的找產(chǎn)品商量改需求。
咳咳 ！?。?！怎么說呢？ 鐵蛋是為了降低成本，為公司控本降費，初心是好的，但是呀這個做法嗯嗯啊啊。。。， 大家以此為戒哦！??！
既然改需求不行，那就只能往下走了， 先來一波SQL優(yōu)化看看，要優(yōu)化SQL總得知道SQL慢在哪里了吧？
咋辦咋辦！ 不知道哪里慢咋辦？
還能咋辦，看SQL的執(zhí)行計劃唄！
不會看咋辦？
啥！ 不會看， 不會看學(xué)??！
好吧，當(dāng)我沒問?。。?/p>
怎么看執(zhí)行計劃呢，首先你得會一個SQL的命令，叫EXPLAIN, 此命令用于查看SQL的執(zhí)行計劃。得此命令，鐵蛋如獲至寶， 拿起來就是一頓操作，看到命令輸出的結(jié)果后，鐵蛋傻眼了，這什么鬼？ 這怎么看？
怎么看？？？ 用眼睛看唄，還能怎么看。
總的來說sql的執(zhí)行計劃是一個樹形層次結(jié)構(gòu)， 一般來說閱讀上遵從層級越深越優(yōu)先， 同一層級由上到下的原則。
來跟著鐵蛋老師讀： 層級越深越優(yōu)先， 同一層級上到下。
順序知道了，得知道里面的意思了吧， 是的沒錯， 但是這個里面比較具體的一些細節(jié)這里就不再展開了，只介紹比較常關(guān)注的幾個關(guān)鍵字：
重點來了，重點來了，睡覺的玩手機的停一停。王老師要開車了， 啊呸， 開課了。
第一行的括號中從左到右依次代表的是：
（估計）啟動成本，在開始輸出之前花費的時間，例如排序時間。
（估計）總成本， 這里有一個前提是計劃節(jié)點會完整運行，即所有可用行都會被檢索。實際上一些節(jié)點的父節(jié)點不會檢索所有可用行（如LIMIT）。
（估計）輸出的總行數(shù)，同樣的是基于節(jié)點會完整運行的假設(shè)。
（估計）輸出行的平均寬度（以字節(jié)為單位）
注意：
cost中描述的是啟動成本和總成本，但是到目前為止我們還不知道這個數(shù)字代表的具體含義，因為我們不知道它的單位是什么。（所以說這里cost中的成本是具有相對意義，不具有絕對意義）
rows代表的是輸出的總行數(shù)，他不是計劃節(jié)點處理或掃描的行數(shù)，而是節(jié)點發(fā)出的行數(shù)。由于使用where子句過濾，這個值通常小于掃描的數(shù)目。理想情況下，頂級的rows近似于實際的查詢返回，更新或刪除的行數(shù)
欲知詳情，且待鐵蛋老師的執(zhí)行計劃章節(jié)詳解，本課就不做衍生。
上圖中的 Index Scan代表索引掃描， Index Cond代表索引命中，后面是命中的具體的索引； Filter是過濾條件，跟具體的sql有關(guān)， 注意sort， sort中應(yīng)該是有兩行，下面的圖示中能夠看到， 第一行代表對那個鍵進行排序， 第二行是排序方法（主要有內(nèi)存排序和磁盤排序，應(yīng)該避免磁盤排序）和數(shù)據(jù)大小。
explain還有兩個比較有用的參數(shù)一個是analyze, 一個是buffers。 加上第一個參數(shù)可以讓sql真正的執(zhí)行并且預(yù)估執(zhí)行時間， 第二參數(shù)可以查看緩存命中情況。
actual time對應(yīng)的意義和cost相似，但是不同于cost， actual time具有絕對意義，因為它的單位是ms。loops代表循環(huán)的次數(shù)。
緩存命中情況主要看Buffers這一行， hit就是命中情況，buffers的信息有助于確定查詢的哪部分是IO密集型的。
Hash節(jié)點主要看 Buckes， 哈希桶的數(shù)量， Batches：批處理的數(shù)量，批處理的數(shù)量如果超過1，則還會使用磁盤空間，但不會顯示。 Memory Usage代表內(nèi)存的使用峰值。
有了以上信息我們基本上就可以尋醫(yī)問藥， 對癥下藥了， 該建索引的建索引， 查詢語句沒有命中索引的調(diào)整下sql，聯(lián)合索引條件過濾包含驅(qū)動列，且驅(qū)動列在前效率最高。
索引優(yōu)化小技巧：
索引盡量建在數(shù)據(jù)比較分散的列上， 不要在變化很小的字段上加索引，比如性別之類的。
原因就是：
索引本質(zhì)上是一種空間換時間的操作，通過B Tree這種數(shù)據(jù)結(jié)構(gòu)減少io的操作次數(shù)以此來提升速度。如果在變化很小的字段上建立索引，那么可能單個葉子節(jié)點上的數(shù)據(jù)量也是龐大的，反而增加了io的次數(shù)（如果查詢字段有包含非索引列，索引命中之后還需要回表）
到了這里就開始我們題目中的正文了， 分頁查詢性能優(yōu)化?。。?/p>
怎么優(yōu)化呢？ 經(jīng)過上述一系列的索引和sql優(yōu)化之后，鐵蛋老師發(fā)現(xiàn)雖然sql的執(zhí)行速度比以前快了，但是在單表一千萬的量級下，這個查詢的速度還是有點龜速呀。
仔細看了上圖中的執(zhí)行計劃發(fā)現(xiàn)有三個個地方有嫌疑，一個是Hash節(jié)點， 一個是Sort， 還有一個是Buffers。
在Hash節(jié)點中Batches批處理的數(shù)量超過了1， 這說明用到了外存， 原來是內(nèi)存不夠了呀！
Sort節(jié)點中，排序方法是歸并， 而且是磁盤排序， 原來也是內(nèi)存不夠了。
Buffers 節(jié)點中，同一個sql執(zhí)行兩次每次都有新的io，說明緩存空間也不夠，最終這三個現(xiàn)象都指向了內(nèi)存。
鐵蛋打開pg的配置文件一看， 我靠，窮鬼呀，才分配了512MB的共享緩存總空間， 進程單獨分配了4M空間用于hash，排序等操作，用于維護的分配了512MB。
這哪行，再窮不能窮內(nèi)存呀！ 內(nèi)從都沒有怎么快，怎么快！
鐵蛋一看，服務(wù)器有64GB的內(nèi)存，恨不得都分過去，還好旁邊的二狗阻止了他。
二狗說不是這么玩的， 共享緩存區(qū)的內(nèi)存一般分配是內(nèi)存的1/4，不超過總內(nèi)存的1/2。 線程內(nèi)存就看著給了，預(yù)計下峰值連接數(shù)和均值連接數(shù)，做一個權(quán)衡，適當(dāng)提高。
于是鐵蛋將共享緩存區(qū)的內(nèi)存分配為20GB， 單個線程用于hash和排序的分配了200MB。 重啟數(shù)據(jù)庫， 跑了下執(zhí)行計劃。 sql里面從以前的一分鐘，四五十秒變成了三四秒左右。
仔細看了下執(zhí)行計劃， sort中的磁盤排序變成了內(nèi)存排序，排序方法從歸并變成了快排。 Hash節(jié)點中批處理的數(shù)量也變成了1， Buffers中緩存全部命中。
到了這里優(yōu)化看似就完成了，但是還有些不太圓滿。 哪里不圓滿呢？ 明明sql的分頁查詢語句很快，為什么頁面上的分頁查詢還是要四五秒呢？
鐵蛋一拍腦袋，怎么把這個給忘了， 分頁查詢頁面有個總數(shù)統(tǒng)計， 總數(shù)統(tǒng)計的sql也需要占時間的呀？ 怎么辦？
有辦法， 不要慌？ 我們的原則就是兩條腿走路，兩個方針政策。
優(yōu)化全表掃描的速度 （為什么要優(yōu)化全表掃描的速度，因為統(tǒng)計總數(shù)的時候大多數(shù)情況下是不能避免全表掃描的）分頁查詢和統(tǒng)計的sql并行執(zhí)行怎么實行？
優(yōu)化全表掃描的速度還得從服務(wù)器下手， 全表掃描慢是因為服務(wù)器的IO慢，鐵蛋恨不得把這個82年的機械硬盤換成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 開機自啟動腳本中。
sql并行化的實現(xiàn)也比較容易，在一開始就向線程池提交一個統(tǒng)計sql'的任務(wù)， 等到分頁查詢的數(shù)據(jù)處理完成最后要返回給前端之前找線程池要總數(shù)就行了，如果沒有執(zhí)行完，會阻塞等待執(zhí)行完，所以響應(yīng)時間就可以控制在sql執(zhí)行時間最長的那段時間之內(nèi)了。
至此優(yōu)化任務(wù)算是完成個七七八八了，但是鐵蛋突然手一抖點了最后一頁，哎發(fā)現(xiàn)怎么最后一頁查詢的速度要比第一頁慢上一些，怎么回事？
因為如果sql涉及到針對某個字段的排序，那么往后翻頁的時候如果采用的是limit offset 的方式會變得很慢，因為數(shù)據(jù)庫需要先把前面的數(shù)據(jù)都讀出來然后扔掉前面不需要的。這個時候一般情況下沒有太多sql上的技巧可以優(yōu)化了，只有在某些個特殊情況下可以采用一些小技巧。
方法是錨點定位法或者叫點位過濾，差不多就這個叫法，知道意思就行。
這個定位是怎么做的呢，如果當(dāng)你的查詢不帶過濾條件， （比如你的個人訂單記錄，只是比較下，不要細糾）。且你的數(shù)據(jù)中有一個遞增且連續(xù)的字段（注意一定要連續(xù)），那么就可以通過翻頁前的最后一條數(shù)據(jù)的id來定位下一頁的位置， 或者直接根據(jù)分頁大小和要跳轉(zhuǎn)的頁碼直接定位到你要翻頁的地方，一般情況下這個字段是主鍵。
示例：
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
寫在最后的鐵蛋老師的忠告， 如果在某些情況下通過某個索引去查詢的時候因為數(shù)據(jù)離散存儲導(dǎo)致的索引命中之后回表IO放大導(dǎo)致查詢緩慢的問題，可以通過CLUSTER 命令強制數(shù)據(jù)按照某個索引的順序密集存儲。
1cluster a using index_name
如何查看數(shù)據(jù)是不是離散存儲，很簡單??！ 在selec語句中加上ctid字段。
ctid | id
-------+----
(0,1) | 10
(0,2) | 11
ctid的第一個數(shù)字代表塊號， 第二個代表行號， 就是第幾塊的第幾行， 所以通過此字段就能看出離散程度。
至此優(yōu)化任務(wù)結(jié)束了， 鐵蛋老師感覺舉例自己的CRUDER 的夢想又近了一步。
文章來源：腳本之家
來源地址：https://www.jb51.net/article/204310.htm
申請創(chuàng)業(yè)報道，分享創(chuàng)業(yè)好點子。點擊此處，共同探討創(chuàng)業(yè)新機遇！
