mysql分頁(yè)性能探索

常見(jiàn)的幾種分頁(yè)方式：

1.扶梯方式

扶梯方式在導(dǎo)航上通常只提供上一頁(yè)/下一頁(yè)這兩種模式，部分產(chǎn)品甚至不提供上一頁(yè)功能，只提供一種“更多/more”的方式，也有下拉自動(dòng)加載更多的方式，在技術(shù)上都可以歸納成扶梯方式。
扶梯方式在技術(shù)實(shí)現(xiàn)上比較簡(jiǎn)單及高效，根據(jù)當(dāng)前頁(yè)最后一條的偏移往后獲取一頁(yè)即可。寫(xiě)成SQL可能類(lèi)似

SELECT*FROMLIST_TABLEWHEREid> offset_id LIMIT n;

1.電梯方式

另外一種數(shù)據(jù)獲取方式在產(chǎn)品上體現(xiàn)成精確的翻頁(yè)方式，如1,2,3……n，同時(shí)在導(dǎo)航上也可以由用戶(hù)輸入直達(dá)n頁(yè)。國(guó)內(nèi)大部分場(chǎng)景采用電梯方式，但電梯方式在技術(shù)實(shí)現(xiàn)上相對(duì)成本較高。

在MySQL中，通常提到的b-tree，在存儲(chǔ)引擎實(shí)現(xiàn)上，通常都是b+tree。

使用電梯方式時(shí)候，當(dāng)用戶(hù)指定翻到第n頁(yè)時(shí)候，并沒(méi)有直接方法尋址到該位置，而是需要從第一樓逐個(gè)count，scan到count*page時(shí)候，獲取數(shù)據(jù)才真正開(kāi)始，所以導(dǎo)致效率不高。

傳統(tǒng)分頁(yè)技術(shù)(電梯方式)

首先前端需要傳給你的分頁(yè)實(shí)體，以及查詢(xún)條件

//分頁(yè)實(shí)體
structFinanceDcPage{
1:i32 pageSize,//頁(yè)容量
2:i32 pageIndex,//當(dāng)前頁(yè)索引
}

然后你需要返回查詢(xún)總條數(shù)給前端；

SELECTCOUNT(*)FROMmy_tableWHEREx= y ORDERBYid;

然后再返回指定頁(yè)面條數(shù)給前端：

SELECT*FROMmy_tableWHEREx= y ORDERBYdate_colLIMIT (pageIndex - 1)* pageSize, pageSize;

由上面兩條sql語(yǔ)句查詢(xún)出來(lái)的結(jié)果需要返回給前端的分頁(yè)實(shí)體，以及單頁(yè)結(jié)果集

//分頁(yè)實(shí)體
structFinanceDcPage{
1:i32 pageSize,//頁(yè)容量
2:i32 pageIndex,//當(dāng)前頁(yè)索引
3:i32 pageTotal,//總頁(yè)數(shù)
4:i32 totalRecod,//總條數(shù)
}

傳統(tǒng)查詢(xún)方法，每次請(qǐng)求變化的只有pageIndex值，也就是limit offset,num的offset

如limit 0,10; limit 10,10; …. limit10000,10;

上面的變化會(huì)導(dǎo)致每次查詢(xún)所執(zhí)行的時(shí)間會(huì)有偏差，offset值越大需要的時(shí)間越長(zhǎng)，如limit10000,10 需要讀取10010個(gè)數(shù)據(jù)才能得到想要的10條數(shù)據(jù)。

優(yōu)化方法

傳統(tǒng)方法中我們了解到，影響效率的關(guān)鍵是程序遍歷了許多不需要的數(shù)據(jù)，找到了關(guān)鍵點(diǎn)那么就從這里著手。

如果沒(méi)有必須使用電梯方式的時(shí)候，我們可以使用扶梯的方式，來(lái)提高性能。

但是大多數(shù)情況，電梯形式更能滿(mǎn)足用戶(hù)的需求，所以我們就需要另找方法來(lái)優(yōu)化電梯形式。

基于傳統(tǒng)方式的優(yōu)化

上面提到的優(yōu)化方式，要么難以滿(mǎn)足用戶(hù)的需求，要么實(shí)現(xiàn)起來(lái)過(guò)于復(fù)雜，所以如果數(shù)據(jù)量不是特別大的時(shí)候，像百來(lái)萬(wàn)條數(shù)據(jù)，其實(shí)根本沒(méi)有必要使用上面的優(yōu)化方法。

傳統(tǒng)方法已經(jīng)足夠用了，只不過(guò)傳統(tǒng)方法也可能需要優(yōu)化的地方。例如：

orderby優(yōu)化

SELECT*FROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5

這條語(yǔ)句中使用了ORDERBY關(guān)鍵字，那么對(duì)什么進(jìn)行排序又非常重要了，如果你是對(duì)自增id進(jìn)行排序的話，那么這條語(yǔ)句就不需要優(yōu)化了，如果是索引甚至非索引的話，那就需要優(yōu)化了。

首先你要保證它是索引，不然真的會(huì)很慢。然后如果他是索引，但是本身不像自增id那樣有序的話，那么就要改寫(xiě)成下面的語(yǔ)句。

SELECT*FROMpa_dc_flowINNERJOIN(SELECTidFROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5)ASpa_dc_flow_idUSING(id);

下面是對(duì)兩條sql的 EXPLAIN

由圖中我們可以看出，第二個(gè)sql可以少掃面很多頁(yè)面。

其實(shí)這涉及到order by的優(yōu)化問(wèn)題，第一條sql中并沒(méi)有利用到subject_code索引。如果你改為select subject_code …則用到了索引。下面是對(duì)order by的優(yōu)化。

order by后的字段，如果要走索引，須與where 條件里的某字段建立復(fù)合索引��！或者說(shuō)orcerby后的字段如果要走索引排序，它要么與where條件里的字段建立復(fù)合索引【這里建立復(fù)合索引的時(shí)候，需要注意復(fù)合索引的列順序?yàn)椋╳here字段，order by字段），這樣才能滿(mǎn)足最左列原則，原因可能是order by字段并能算在where 查詢(xún)條件中！】，要么它自身要在where條件里被引用到！

表asubject_code為普通字段,上面建有索引,id是自增主鍵

select*fromaorderbysubject_code//用不上索引
selectidfromaorderbysubject_code//能用上索引
selectsubject_codefromaorderbysubject_code//能用上索引
select*fromawheresubject_code= XX orderbysubject_code//能用上索引

意思是說(shuō)order by 要避免使用文件系統(tǒng)排序，要么把order by的字段出現(xiàn)在select后，要么使用order by字段出現(xiàn)在where 條件里，要么把order by字段與where條件字段建立復(fù)合索引！

第二條sql就是巧妙的利用第二種方式利用上了索引。 select id from a order bysubject_code，這種方式

count優(yōu)化

當(dāng)數(shù)據(jù)量非常大時(shí)，其實(shí)可以輸出總數(shù)的大概數(shù)據(jù)，利用explain語(yǔ)句，他并沒(méi)有真正去執(zhí)行sql，而是進(jìn)行的估算。

相關(guān)推薦：

MySQL分頁(yè)性能優(yōu)化指南

php mysql分頁(yè)類(lèi)(php新手入門(mén))

php+mysql分頁(yè)代碼詳解_PHP教程

以上就是mysql分頁(yè)性能探索的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！