mysql分頁性能探索

分頁在我們的編程中經(jīng)常會用到,本文帶領(lǐng)大家一起探討mysql分頁性能,希望能幫助到大家。

常見的幾種分頁方式:

1.扶梯方式

mysql分頁性能探索

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

SELECT*FROMLIST_TABLEWHEREid>?offset_id?LIMIT?n;

1.電梯方式

mysql分頁性能探索

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

mysql中,通常提到的b-tree,在存儲引擎實現(xiàn)上,通常都是b+tree。

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

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

首先前端需要傳給你的分頁實體,以及查詢條件

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

然后你需要返回查詢總條數(shù)給前端;

SELECTCOUNT(*)FROMmy_tableWHEREx=?y?ORDERBYid;

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

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

由上面兩條sql語句查詢出來的結(jié)果需要返回給前端的分頁實體,以及單頁結(jié)果集

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

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

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

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

優(yōu)化方法

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

如果沒有必須使用電梯方式的時候,我們可以使用扶梯的方式,來提高性能。

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

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

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

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

orderby優(yōu)化

SELECT*FROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5

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

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

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

下面是對兩條sql的 EXPLAIN

mysql分頁性能探索

mysql分頁性能探索

由圖中我們可以看出,第二個sql可以少掃面很多頁面。

其實這涉及到order by的優(yōu)化問題,第一條sql中并沒有利用到subject_code索引。如果你改為select subject_code …則用到了索引。下面是對order by的優(yōu)化。

order by后的字段,如果要走索引,須與where 條件里的某字段建立復(fù)合索引!!或者說orcerby后的字段如果要走索引排序,它要么與where條件里的字段建立復(fù)合索引【這里建立復(fù)合索引的時候,需要注意復(fù)合索引的列順序為(where字段,order by字段),這樣才能滿足最左列原則,原因可能是order by字段并能算在where 查詢條件中!】,要么它自身要在where條件里被引用到!

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

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

意思是說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ù)據(jù),利用explain語句,他并沒有真正去執(zhí)行sql,而是進行的估算。

相關(guān)推薦:

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

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

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

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點贊5 分享