最近無意間看到一個(gè)MySQL分頁優(yōu)化的測(cè)試案例，并沒有非常具體地說明測(cè)試場(chǎng)景的情況下，給出了一種經(jīng)典的方案，因?yàn)楝F(xiàn)實(shí)中很多情況都不是固定不變的，能總結(jié)出來通用性的做法或者說是規(guī)律，是要考慮非常多的場(chǎng)景的，同時(shí)，面對(duì)能夠達(dá)到優(yōu)化的方式要追究其原因，同樣的做法，換了個(gè)場(chǎng)景，達(dá)不到優(yōu)化效果的，還要追究其原因。 

個(gè)人對(duì)此場(chǎng)景在不用情況表示懷疑，然后自己測(cè)試了一把，果然發(fā)現(xiàn)一些問題，同時(shí)也證實(shí)了一些預(yù)期的想法。 

本文就MySQL分頁優(yōu)化，從最最簡(jiǎn)單的情況出發(fā)，來做一個(gè)簡(jiǎn)單的分析。 

另：本文測(cè)試環(huán)境是最***配置的云服務(wù)器，相對(duì)來說服務(wù)器硬件環(huán)境有限，不過對(duì)于不同的語句（寫法）應(yīng)該是“平等的” 

20170916補(bǔ)充: 

想想用腳趾頭就能明白， 

1. 如果分頁排序字段是聚集索引，完全沒必要對(duì)索引分頁再查詢數(shù)據(jù)，因?yàn)樗饕褪菙?shù)據(jù)本身。

2. 如果是非聚集索引，先對(duì)索引分頁，然后再利用索引去查詢數(shù)據(jù)，先分頁索引確實(shí)可以減少掃描的范圍

3. 如果經(jīng)常按照2中的方式查詢，也就是按照非聚集索引排序查詢，那么為什么不在該列上建立聚集索引呢。 

MySQL經(jīng)典的分頁“優(yōu)化”做法 

MySQL分頁優(yōu)化中，有一種經(jīng)典的問題，在查詢?cè)?ldquo;靠后”的數(shù)據(jù)越慢（取決于表上的索引類型，對(duì)于B樹結(jié)構(gòu)的索引，SQL Server中也一樣）  

select * from t order by id limit m,n   

也即隨著M的增大，查詢同樣多的數(shù)據(jù)，會(huì)越來越慢

面對(duì)這一問題，于是就產(chǎn)生了一種經(jīng)典的做法，類似于（或者變種）如下的寫法

就是先把分頁范圍內(nèi)的id單獨(dú)找出來，然后再跟基表做關(guān)聯(lián)，***查詢出來所需要的數(shù)據(jù)  

select * from t inner join (select id from t order by id limit m,n)t1 on t1.id = t.id  

這種做法是不是總是生效的，或者說是在什么情況下后者才能到達(dá)到優(yōu)化的目的？有沒有做了改寫之后無效甚至變慢的情況？  

與此同時(shí)，絕大多數(shù)查詢都是有篩選條件的，如果有篩選條件的情況，sql語句就變成了  

select * from t where *** order by id limit m,n   

如果如法炮制，改寫成類似  

select * from t inner join (select id from t where *** order by id limit m,n )t1 on t1.id = t.id   

在這種情況下，改寫后的sql語句還能達(dá)到優(yōu)化的目的嗎？ 

測(cè)試環(huán)境搭建 

測(cè)試數(shù)據(jù)比較簡(jiǎn)單，通過存儲(chǔ)過程循環(huán)寫入測(cè)試數(shù)據(jù)，測(cè)試表的InnoDB引擎表。 

這里要注意的是日志寫入模式一定要修改成innodb_flush_log_at_trx_commit = 2，否則默認(rèn)情況下，500w數(shù)據(jù)，估計(jì)一天都寫不完，這個(gè)與日志寫入模式有關(guān)，就不多說了。 

分頁查詢優(yōu)化的緣由 

首先還是先看一下這個(gè)經(jīng)典的問題，分頁的時(shí)候，越“靠后”查詢相應(yīng)越慢的情況 

測(cè)試一：查詢第1-20行的數(shù)據(jù)，0.01秒 

同樣是查詢20行數(shù)據(jù)，查詢相對(duì)“靠后”的數(shù)據(jù)，比如這里的從4900001-4900020行數(shù)據(jù)的情況，用時(shí)1.97秒。 

從中可以看到，查詢條件不變的情況下，越往后查詢，查詢效率越低，可以簡(jiǎn)單理解成：同樣搜索20行數(shù)據(jù)，越是靠后的數(shù)據(jù)，查詢代價(jià)越大。

至于為什么后一種效率較低，后面會(huì)慢慢分析。 

測(cè)試環(huán)境是centos 7 ，mysql 5.7，測(cè)試表的數(shù)據(jù)是500W 

重現(xiàn)經(jīng)典分頁“優(yōu)化”，當(dāng)沒有篩選條件，排序列為聚集索引的時(shí)候，并不會(huì)有所改善 

這里來對(duì)比以下兩種寫法在聚集索引列作為排序條件時(shí)候的性能  

select * from t order by id limit m,n。 
select * from t inner join (select id from t order by id limit m,n)t1 on t1.id = t.id   

***種寫法： 

select * from test_table1 order by id asc limit 4900000,20;測(cè)試結(jié)果見截圖，執(zhí)行時(shí)間為8.31秒 

第二種改寫后的寫法： 

select t1.* from test_table1 t1

inner join (select id from test_table1 order by id limit 4900000,20)t2 on t1.id = t2.id;執(zhí)行時(shí)間為8.43秒 

這里很清楚，通過經(jīng)典的改寫方法改寫之后，性能能毫無提升,甚至還有一點(diǎn)點(diǎn)變慢了，實(shí)際測(cè)試上表現(xiàn)為兩者在性能上并沒有明顯的線性差異，這兩者樓主是做了多次測(cè)試的。 

我個(gè)人看到類似結(jié)論非要測(cè)一下不可的，這個(gè)東西不能靠蒙，或者靠運(yùn)氣什么的，能提高效率是為什么，不能提高又是為什么。 

那么為什么改寫之后的寫法沒有像傳說中的那種提升性能？

是什么導(dǎo)致當(dāng)前這個(gè)改寫沒有到達(dá)提升性能的目的？

后者能夠提升性能的原理是什么？ 

首先看一下測(cè)試表的表結(jié)構(gòu)，排序列上是有索引，這一點(diǎn)是沒有問題的，關(guān)鍵是這個(gè)排序列上的索引是主鍵（聚集索引）。 

為什么排序列上是聚集索引的時(shí)候，相對(duì)“優(yōu)化”改寫之后的sql并不能達(dá)到“優(yōu)化”的目的? 

在排序列為聚集索引列的情況下，兩者都是順序掃描表來實(shí)現(xiàn)查詢符合條件的數(shù)據(jù)的后者雖然是先驅(qū)動(dòng)一個(gè)子查詢，然后再用子查詢的結(jié)果驅(qū)動(dòng)主表，

但是子查詢并沒有改變“順序掃描表來實(shí)現(xiàn)查詢符合條件的數(shù)據(jù)的”做法，當(dāng)前情況下，甚至改寫后的做法顯得畫蛇添足 

參考如下兩者執(zhí)行計(jì)劃，***個(gè)截圖的執(zhí)行計(jì)劃的一行，與改寫后的sql的執(zhí)行計(jì)劃的第三行（id =2 的那一行），基本上一樣。 

當(dāng)沒有篩選條件，排序列為聚集索引時(shí)候的分頁查詢，所謂的分頁查詢優(yōu)化只不過是畫蛇添足。 

目前來看，查詢上述數(shù)據(jù)，兩種方式都非常慢，那如果要查詢上述的數(shù)據(jù)，該如何做？還是要看為什么慢，首先要理解B數(shù)的平衡性結(jié)構(gòu)，在我自己粗略的理解來看，如下圖，當(dāng)查詢的數(shù)據(jù)“靠后”的時(shí)候，實(shí)際上是偏離在B樹索引的一個(gè)方向，如下兩個(gè)截圖所示的目標(biāo)數(shù)據(jù)其實(shí)平衡樹上的數(shù)據(jù)，沒有所謂的“靠前”與“靠后”，“靠前”與“靠后”都是相對(duì)于對(duì)方來說的，或者說是從掃描的方向上來看的從一個(gè)方向上看“靠后的”數(shù)據(jù)，從一個(gè)方向看就是“靠前的”，前后不是絕對(duì)的。 

如下兩個(gè)截圖是B樹索引結(jié)構(gòu)的粗略表現(xiàn)形式，假如目標(biāo)數(shù)據(jù)的位置固定的情況下，所謂的“靠后”是相對(duì)與從左向右來說的； 

如果從右向左看，之前所謂靠后的數(shù)據(jù)實(shí)際上是“靠前”的。 

只要數(shù)據(jù)是靠前的，要高效低找到這部分?jǐn)?shù)據(jù)，還是可以的。mysql中應(yīng)該也有類似于sqlserver中的正向（forwarded）和反向掃描（backward）的做法。 

如果對(duì)于靠后的數(shù)據(jù)，采用反向掃描，應(yīng)該就可以很快找到這個(gè)部分?jǐn)?shù)據(jù)，然后對(duì)找到的數(shù)據(jù)在再次排序（asc），結(jié)果應(yīng)該是一樣的， 首先來看效果：結(jié)果跟上面的查詢一模一樣，這里僅耗時(shí)0.07秒，之前的兩種寫法均超過了8秒，效率有上百倍的差距。 

至于這個(gè)是為什么，我想根據(jù)上面的闡述，自己應(yīng)該能夠體會(huì)的到，這里附上這個(gè)sql。

如果經(jīng)常查詢所謂的靠后的數(shù)據(jù)，比如說Id較大的數(shù)據(jù)，或者說是時(shí)間維度上較新的數(shù)據(jù)，可以采用倒敘掃描索引的方式來實(shí)現(xiàn)高效分頁查詢 

（這里請(qǐng)計(jì)算好數(shù)據(jù)所在的分頁，同樣的數(shù)據(jù)，正序和倒序其起始“頁碼”是不同的）  

select* from 
( 
    select * from test_table1 order by id desc limit 99980,20 
      
) t order by id;   

當(dāng)沒有篩選條件，排序列為非聚集索引的時(shí)候，會(huì)有所改善 

這里對(duì)測(cè)試表test_table1做出如下改變 

1，增加一個(gè)id_2列，

2，該字段上創(chuàng)建一個(gè)唯一索引，

3，該字段用對(duì)應(yīng)的主鍵Id填充 

上面的測(cè)試是按照主鍵索引（聚集索引）來排序的，現(xiàn)在來按照非聚集索引排序，也即新增的這個(gè)列id_2來排序，測(cè)試一開始提到的兩種分頁方法。 

首先來看***種寫法 

select * from test_table1 order by id_2 asc limit 4900000,20;執(zhí)行時(shí)間為1分鐘多一點(diǎn)，暫且認(rèn)其為60秒 

第二種寫法 

select t1.* from test_table1 t1

inner join (select id from test_table1 order by id_2 limit 4900000,20)t2 on t1.id = t2.id;執(zhí)行時(shí)間1.67秒 

從這種情況來看，也就是說排序列為非聚集索引列的時(shí)候，后一種寫法確實(shí)能大幅度地提升效率。差不多有40倍的提升。 

那么原因在何呢？ 

首先來看***種寫法的執(zhí)行計(jì)劃，可以簡(jiǎn)單理解為這個(gè)sql的執(zhí)行時(shí)做全表掃描之后，然后重新按照id_2排序，***取最前20條數(shù)據(jù)。 

首先全表掃描就是一個(gè)非常耗時(shí)的過程，排序也是一個(gè)非常大的代價(jià)，因此表現(xiàn)為性能非常的低下。 

再來看后者的執(zhí)行計(jì)劃，他是首先子子查詢中，按照id_2上的索引順序掃描，然后用符合條件的主鍵Id去表中查詢數(shù)據(jù)。這樣的話，避免了查詢出來大量的數(shù)據(jù)然后重新排序（Using filesort）。 

如果了解sqlserver執(zhí)行計(jì)劃的情況下，后者與前者相比，應(yīng)該還有避免了頻繁的回表（sqlserver中叫做key lookup或者書簽查找的過程，可以認(rèn)為是子查詢驅(qū)動(dòng)外層表查詢符合條件的20條的數(shù)據(jù)的過程是一個(gè)批量的，一次性的。 

其實(shí)，只有在當(dāng)前情況下，也就是說排序列為非聚集索引列的時(shí)候，改寫后的sql才能提升分頁查詢的效率。 

即便如此，此方式“優(yōu)化”過的分頁語句，還是與如下寫法的分頁效率有比較大的差別的上面也看到了，返回同樣的數(shù)據(jù)，如下的查詢是0.07秒，比這里的1.67秒還是高2個(gè)數(shù)量級(jí)的  

select* from 
( 
    select * from test_table1 order by id desc limit 99980,20 
      
) t order by id;   

另外一個(gè)，想提到的問題就是，如果經(jīng)常性分頁查詢，還要按照某種順序，那么為什么不在這個(gè)列上建立一個(gè)聚集索引。 

比如語句自增Id的，或者時(shí)間+其他字段確保唯一性的，mysql會(huì)在主鍵上自動(dòng)創(chuàng)建聚集索引。 

然后有了聚集索引，“靠前”與“靠后”僅僅是一個(gè)相對(duì)的邏輯上的概念了,如果多數(shù)時(shí)候是想得到“靠后”或者較新的數(shù)據(jù)，就可以采用上述寫法， 

當(dāng)存在篩選條件的情況下，分頁查詢的優(yōu)化 

這一部分想了想，情況太復(fù)雜了，很難概括出來一種非常具有代表性的案例，因此就不過多地做測(cè)試了。  

select * from t where *** order by id limit m,n   

1，比如刷選條件本身就很高效，一過濾出來僅剩下很少一部分?jǐn)?shù)據(jù)，那么改不改寫sql意義也不大，因?yàn)楹Y選條件本身就可以做到很高效的篩選

2，比如刷選條件本身作用不大（過濾后數(shù)據(jù)量依然巨大），這種情況其實(shí)又回到了不存在篩選條件的情況，還有取決于如何排序，正序還是倒序等等

3，比如篩選條件本身作用不大（過濾后數(shù)據(jù)量依然巨大），要考慮的一個(gè)很實(shí)際的問題是數(shù)據(jù)分布，

數(shù)據(jù)的分布也會(huì)影響的sql的執(zhí)行效率（sqlserver中的經(jīng)歷，mysql應(yīng)該差別不大）

4，本身查詢比較復(fù)雜的情況下，很難說用某種方式就可以達(dá)到高效的目的 

情況越復(fù)雜，越是難以總結(jié)出來一種通用性的規(guī)律或者說是方法，一切都要以具體情況來看待，很難下一個(gè)定論。 

這里對(duì)于查詢加上篩選條件的情況，就不做一一分析了，不過可以肯定的是，脫離了實(shí)際場(chǎng)景，肯定沒有一個(gè)固化的方案。 

另外，對(duì)于查詢當(dāng)前頁數(shù)據(jù)時(shí)候，利用上一頁查詢的***值做篩選條件，也可以很快滴找到當(dāng)前頁的數(shù)據(jù)，這樣當(dāng)然沒有問題，但這屬于另外一個(gè)做法，不在本文討論之列。 

補(bǔ)充一個(gè)在SQL Server下的測(cè)試結(jié)果，如果是非聚集索引，如果查詢排序的列是一個(gè)單列索引，分頁方式并不能提升效率。  

create table TestPaging 
( 
    id int identity(1,1), 
    name varchar(50), 
    other varchar(100) 
) 
declare @i int = 0 
while @i<100000 
begin 
    insert into TestPaging values (NEWID(),NEWID()) 
    set @i = @i + 1 
end 
  
create index idx on TestPaging(name)   

從執(zhí)行計(jì)劃可以看出，查詢Id的子查詢是一個(gè)全表掃描 

除非是一個(gè)符合索引，在表中數(shù)據(jù)比較大的情況下，才能提高效率（子查詢進(jìn)行索引掃描的代價(jià)要小于全表掃描的代價(jià)），不過話說回來，如果經(jīng)常按照某個(gè)列排序分頁，為什么該列上不建立成聚集索引呢？ 

總結(jié) 

分頁查詢，越靠后越慢的情況，實(shí)則對(duì)于B樹索引來說，靠前與靠后是一個(gè)邏輯上相對(duì)的概念，性能上的差異，是基于B樹索引結(jié)構(gòu)以及掃描方式有關(guān)的。 

如果加上篩選條件，情況將變得更加復(fù)雜，這個(gè)問題在SQL Server中的原理也是一樣的，本來也在SQL Server中做了測(cè)試的，這里就不重復(fù)了。 

當(dāng)前這種情況，排序列不一定，查詢條件不一定，數(shù)據(jù)分布不一定，就很難用一種特定的方法來實(shí)現(xiàn)“優(yōu)化”，弄不好還起到畫蛇添足的副作用。 

因此在做分頁優(yōu)化的時(shí)候，一定要根據(jù)具體的場(chǎng)景來做分析，方法也不一定只有一種，脫離實(shí)際場(chǎng)景的結(jié)論，都是扯犢子。 

唯有弄清楚這個(gè)問題的來龍去脈，才能游刃有余。 

因此個(gè)人對(duì)于數(shù)據(jù)“優(yōu)化”的結(jié)論，一定是具體問題具體分析，是很忌諱總結(jié)出來一套規(guī)則（規(guī)則1,2,3,4,5）給人“套用”，鑒于本人也很菜，就更不敢總結(jié)出來一些教條了。