亚洲十八**毛片,国产a久久精品一区二区三区,色综合桃花网,高清精品在线

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

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 (頁號(hào)- 1)* 每頁數(shù)量 limit 每頁數(shù)量

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

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

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

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

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

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

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

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

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

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

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

不會(huì)看咋辦？

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

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

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

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

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

來跟著讀： 層級(jí)越深越優(yōu)先， 同一層級(jí)上到下。

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

重點(diǎn)來了，重點(diǎn)來了，睡覺的玩手機(jī)的停一停。老師要開車了， 啊呸， 開課了。

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

（估計(jì)）啟動(dòng)成本，在開始輸出之前花費(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)槲覀儾恢浪膯挝皇鞘裁?。（所以說這里cost中的成本是具有相對(duì)意義，不具有絕對(duì)意義）

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

上圖中的 Index Scan代表索引掃描， Index Cond代表索引命中，后面是命中的具體的索引； Filter是過濾條件，跟具體的sql有關(guān)， 注意sort， sort中應(yīng)該是有兩行，下面的圖示中能夠看到， 第一行代表對(duì)那個(gè)鍵進(jìn)行排序， 第二行是排序方法（主要有內(nèi)存排序和磁盤排序，應(yīng)該避免磁盤排序）和數(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的信息有助于確定查詢的哪部分是IO密集型的。

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

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

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

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

原因就是：

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

到了這里就開始我們題目中的正文了， 分頁查詢性能優(yōu)化?。。?/p>

怎么優(yōu)化呢？ 經(jīng)過上述一系列的索引和sql優(yōu)化之后，鐵蛋老師發(fā)現(xiàn)雖然sql的執(zhí)行速度比以前快了，但是在單表一千萬的量級(jí)下，這個(gè)查詢的速度還是有點(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ù)量超過了1， 這說明用到了外存， 原來是內(nèi)存不夠了呀！

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

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

w打開pg的配置文件一看， 我靠，窮鬼呀，才分配了512MB的共享緩存總空間， 進(jìn)程單獨(dú)分配了4M空間用于hash，排序等操作，用于維護(hù)的分配了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ù)計(jì)下峰值連接數(shù)和均值連接數(shù)，做一個(gè)權(quán)衡，適當(dāng)提高。

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

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

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

一拍腦袋，怎么把這個(gè)給忘了， 分頁查詢頁面有個(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ù)情況下是不能避免全表掃描的）分頁查詢和統(tǒng)計(jì)的sql并行執(zhí)行怎么實(shí)行？

優(yōu)化全表掃描的速度還得從服務(wù)器下手， 全表掃描慢是因?yàn)榉?wù)器的IO慢，鐵蛋恨不得把這個(gè)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ī)自啟動(dòng)腳本中。

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

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

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

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

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

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

1cluster a using index_name

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

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

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