我就廢話不多說了,看代碼吧~
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ù)量
補(bǔ)充:postgreSQL單表數(shù)據(jù)量上千萬分頁查詢緩慢的優(yōu)化方案
故事要這樣說起,王鐵蛋是一個初入職場的程序猿,每天干的活就是實現(xiàn)各種簡單的查詢業(yè)務(wù),但是鐵蛋有一顆熱愛技術(shù)的心,每天都琢磨著如何寫出花式的增刪改查操作。沒錯平凡的鐵蛋的有著一個偉大的夢想,成為一名高級CRUDER。(一不小心激動了,開水倒進(jìn)了我的花瓶)。
時間就這樣一天天的流逝,鐵蛋感覺不管自己的crud寫的再花騷也不能達(dá)到高級cruder的級別,于是乎鐵蛋心一橫,接下了一個艱巨的任務(wù),對單表數(shù)據(jù)量到百萬千萬級別的查詢頁面進(jìn)行優(yōu)化,這是鐵蛋工作任務(wù)上的一小步,卻是鐵蛋實現(xiàn)夢想的一大步。
接任務(wù)簡單,做任務(wù)難呀! 這是鐵蛋第一天的感受,接了這個任務(wù)之后鐵蛋沒有一點(diǎn)頭緒,從哪下手呢?鐵蛋仔細(xì)一想既然要優(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)品商量改需求。
咳咳 ?。。?!怎么說呢? 鐵蛋是為了降低成本,為公司控本降費(fèi),初心是好的,但是呀這個做法嗯嗯啊啊。。。, 大家以此為戒哦!??!
既然改需求不行,那就只能往下走了, 先來一波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)先, 同一層級上到下。
順序知道了,得知道里面的意思了吧, 是的沒錯, 但是這個里面比較具體的一些細(xì)節(jié)這里就不再展開了,只介紹比較常關(guān)注的幾個關(guān)鍵字:
重點(diǎn)來了,重點(diǎn)來了,睡覺的玩手機(jī)的停一停。王老師要開車了, 啊呸, 開課了。
第一行的括號中從左到右依次代表的是:
(估計)啟動成本,在開始輸出之前花費(fèi)的時間,例如排序時間。
(估計)總成本, 這里有一個前提是計劃節(jié)點(diǎn)會完整運(yùn)行,即所有可用行都會被檢索。實際上一些節(jié)點(diǎn)的父節(jié)點(diǎn)不會檢索所有可用行(如LIMIT)。
(估計)輸出的總行數(shù),同樣的是基于節(jié)點(diǎn)會完整運(yùn)行的假設(shè)。
(估計)輸出行的平均寬度(以字節(jié)為單位)
注意:
cost中描述的是啟動成本和總成本,但是到目前為止我們還不知道這個數(shù)字代表的具體含義,因為我們不知道它的單位是什么。(所以說這里cost中的成本是具有相對意義,不具有絕對意義)
rows代表的是輸出的總行數(shù),他不是計劃節(jié)點(diǎn)處理或掃描的行數(shù),而是節(jié)點(diǎn)發(fā)出的行數(shù)。由于使用where子句過濾,這個值通常小于掃描的數(shù)目。理想情況下,頂級的rows近似于實際的查詢返回,更新或刪除的行數(shù)
欲知詳情,且待鐵蛋老師的執(zhí)行計劃章節(jié)詳解,本課就不做衍生。
上圖中的 Index Scan代表索引掃描, Index Cond代表索引命中,后面是命中的具體的索引; Filter是過濾條件,跟具體的sql有關(guān), 注意sort, sort中應(yīng)該是有兩行,下面的圖示中能夠看到, 第一行代表對那個鍵進(jìn)行排序, 第二行是排序方法(主要有內(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é)點(diǎn)主要看 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é)點(diǎn)上的數(shù)據(jù)量也是龐大的,反而增加了io的次數(shù)(如果查詢字段有包含非索引列,索引命中之后還需要回表)
到了這里就開始我們題目中的正文了, 分頁查詢性能優(yōu)化!??!
怎么優(yōu)化呢? 經(jīng)過上述一系列的索引和sql優(yōu)化之后,鐵蛋老師發(fā)現(xiàn)雖然sql的執(zhí)行速度比以前快了,但是在單表一千萬的量級下,這個查詢的速度還是有點(diǎn)龜速呀。
仔細(xì)看了上圖中的執(zhí)行計劃發(fā)現(xiàn)有三個個地方有嫌疑,一個是Hash節(jié)點(diǎn), 一個是Sort, 還有一個是Buffers。
在Hash節(jié)點(diǎn)中Batches批處理的數(shù)量超過了1, 這說明用到了外存, 原來是內(nèi)存不夠了呀!
Sort節(jié)點(diǎn)中,排序方法是歸并, 而且是磁盤排序, 原來也是內(nèi)存不夠了。
Buffers 節(jié)點(diǎn)中,同一個sql執(zhí)行兩次每次都有新的io,說明緩存空間也不夠,最終這三個現(xiàn)象都指向了內(nèi)存。
鐵蛋打開pg的配置文件一看, 我靠,窮鬼呀,才分配了512MB的共享緩存總空間, 進(jìn)程單獨(dú)分配了4M空間用于hash,排序等操作,用于維護(hù)的分配了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里面從以前的一分鐘,四五十秒變成了三四秒左右。
仔細(xì)看了下執(zhí)行計劃, sort中的磁盤排序變成了內(nèi)存排序,排序方法從歸并變成了快排。 Hash節(jié)點(diǎn)中批處理的數(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年的機(jī)械硬盤換成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 開機(jī)自啟動腳本中。
sql并行化的實現(xiàn)也比較容易,在一開始就向線程池提交一個統(tǒng)計sql'的任務(wù), 等到分頁查詢的數(shù)據(jù)處理完成最后要返回給前端之前找線程池要總數(shù)就行了,如果沒有執(zhí)行完,會阻塞等待執(zhí)行完,所以響應(yīng)時間就可以控制在sql執(zhí)行時間最長的那段時間之內(nèi)了。
至此優(yōu)化任務(wù)算是完成個七七八八了,但是鐵蛋突然手一抖點(diǎn)了最后一頁,哎發(fā)現(xiàn)怎么最后一頁查詢的速度要比第一頁慢上一些,怎么回事?
因為如果sql涉及到針對某個字段的排序,那么往后翻頁的時候如果采用的是limit offset 的方式會變得很慢,因為數(shù)據(jù)庫需要先把前面的數(shù)據(jù)都讀出來然后扔掉前面不需要的。這個時候一般情況下沒有太多sql上的技巧可以優(yōu)化了,只有在某些個特殊情況下可以采用一些小技巧。
方法是錨點(diǎn)定位法或者叫點(diǎn)位過濾,差不多就這個叫法,知道意思就行。
這個定位是怎么做的呢,如果當(dāng)你的查詢不帶過濾條件, (比如你的個人訂單記錄,只是比較下,不要細(xì)糾)。且你的數(shù)據(jù)中有一個遞增且連續(xù)的字段(注意一定要連續(xù)),那么就可以通過翻頁前的最后一條數(shù)據(jù)的id來定位下一頁的位置, 或者直接根據(jù)分頁大小和要跳轉(zhuǎn)的頁碼直接定位到你要翻頁的地方,一般情況下這個字段是主鍵。
示例:
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
寫在最后的鐵蛋老師的忠告, 如果在某些情況下通過某個索引去查詢的時候因為數(shù)據(jù)離散存儲導(dǎo)致的索引命中之后回表IO放大導(dǎo)致查詢緩慢的問題,可以通過CLUSTER 命令強(qiáng)制數(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è)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!