南昌一中掏脑子挖眼睛完整版_极品太监猎美传奇_太阳是奶酪做的(h)(弓行永夜)_男生宿舍的公共小受_小东西好几天没弄你了最新章节

廢話不多說了，看代碼吧~

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)化方案

故事要這樣說起，w是一個初入職場的程序猿，每天干的活就是實現(xiàn)各種簡單的查詢業(yè)務(wù)，但是鐵蛋有一顆熱愛技術(shù)的心，每天都琢磨著如何寫出花式的增刪改查操作。沒錯平凡的鐵蛋的有著一個偉大的夢想，成為一名高級CRUDER。

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

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

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

雖然有了神圖的指引，但是w還是不知道應(yīng)該優(yōu)化哪個方面？ 不同方面的優(yōu)化方式是什么？一番努力查找，得到了以下信息：

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

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

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

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

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

不會看咋辦？

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

好吧，當(dāng)我沒問?。?！

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

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

總的來說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ù)

上圖中的 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)化?。?！

怎么優(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)存。

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

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

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

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

于是w將共享緩存區(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ù)算是完成個七七八八了，但是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ù)字代表塊號， 第二個代表行號， 就是第幾塊的第幾行， 所以通過此字段就能看出離散程度。