mysql分頁效能探索

小云云原創: 2017-12-08 11:52:261694瀏覽

分頁在我們的程式設計中常會用到，本文帶領大家一起探討mysql分頁效能，希望能幫助大家。

常見的幾個分頁方式：

1.扶梯方式

##扶梯方式在導航上通常只提供上一頁/下一頁這兩種模式，部分產品甚至不提供上一頁功能，只提供一種“更多/more”的方式，也有下拉自動加載更多的方式，在技術上都可以歸納為扶梯方式。

扶梯方式在技術實作上比較簡單、高效，根據目前頁最後一條的偏移往後取得一頁即可。寫成SQL可能類似

SELECT*FROMLIST_TABLEWHEREid> offset_id LIMIT n;

1.電梯方式

##另一種資料取得方式在產品上體現成精確的翻頁方式，如1,2,3…n，同時在導覽上也可以由使用者輸入直達n頁。國內大部分場景採用電梯方式，但電梯方式在技術實現上相對成本較高。

在MySQL中，通常提到的b-tree，在儲存引擎實作上，通常都是b+tree。

使用電梯方式時候，當使用者指定翻到第n頁時候，並沒有直接方法尋址到該位置，而是需要從第一樓逐個count，scan到count*page時候，取得數據才真正開始，所以導致效率不高。

傳統分頁技術(電梯方式)

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

//分页实体
structFinanceDcPage{
1:i32 pageSize,//页容量
2:i32 pageIndex,//当前页索引
}

然後你需要傳回查詢總條數給前端；

SELECTCOUNT(*)FROMmy_tableWHEREx= y ORDERBYid;

##然後再傳回指定頁面條數給前端：

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

由上面兩個sql語句查詢出來的結果需要傳回給前端的分頁實體，以及單頁結果集

//分页实体
structFinanceDcPage{
1:i32 pageSize,//页容量
2:i32 pageIndex,//当前页索引
3:i32 pageTotal,//总页数
4:i32 totalRecod,//总条数
}

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

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

上面的變化會導致每次查詢所執行的時間會有偏差，offset值越大需要的時間越長，如limit10000,10 需要讀取10010個數據才能得到想要的10個數據。

優化方法

傳統方法中我們了解到，影響效率的關鍵是程式遍歷了許多不需要的數據，找到了關鍵點那麼就從這裡著手。

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

但是大多數情況，電梯形式更能滿足使用者的需求，所以我們就需要另找方法來最佳化電梯形式。

基於傳統方式的最佳化

上面提到的最佳化方式，要嘛難以滿足使用者的需求，要嘛實作起來過於複雜，所以如果資料量不是特別大的時候，像百來萬個數據，其實根本沒有必要使用上面的最佳化方法。

傳統方法已經夠用了，只不過傳統方法也可能需要最佳化的地方。例如：

orderby最佳化

SELECT*FROMpa_dc_flowORDERBYsubject_codeDESCLIMIT100000,5

這條語句中使用了ORDERBY關鍵字，那麼對什麼進行排序又非常重要了，如果你是對自增id進行排序的話，那麼這條語句就不需要優化了，如果是索引甚至非索引的話，那就需要優化了。

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

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

下面是兩個sql的EXPLAIN

################### ####由圖中我們可以看出，第二個sql可以少掃面很多頁。 ######其實這牽涉到order by的最佳化問題，第一條sql中並沒有利用到subject_code索引。如果你改為select subject_code …則用到了索引。下面是對order by的最佳化。 ###

order by后的字段，如果要走索引，须与where 条件里的某字段建立复合索引！！或者说orcerby后的字段如果要走索引排序，它要么与where条件里的字段建立复合索引【这里建立复合索引的时候，需要注意复合索引的列顺序为（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 要避免使用文件系统排序，要么把order by的字段出现在select后，要么使用order by字段出现在where 条件里，要么把order by字段与where条件字段建立复合索引！

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

count优化

当数据量非常大时，其实可以输出总数的大概数据，利用explain语句，他并没有真正去执行sql，而是进行的估算。

mysql分頁效能探索

相關文章