分頁效能探索-mysql，分頁探索-mysql

分頁效能探索-mysql，分頁探索-mysql

分頁技術在開發中是十分常見的，我剛到公司做的兩個項目都用到了分頁技術，於是就想著對分頁技術進行深入的探索一下。

常見的幾種分頁方式：

扶梯方式在導航上通常只提供上一頁/下一頁這兩種模式，部分產品甚至不提供上一頁功能，只提供一種“更多/more”的方式，也有下拉自動載入更多的方式，在技術上都可以歸納成扶梯方式。
扶梯方式在技術實現上比較簡單及高效，根據當前頁最後一條的位移往後擷取一頁即可。寫成SQL可能類似

SELECT * FROM LIST_TABLE WHERE id > offset_id LIMIT n;

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

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

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

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

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

//分頁實體struct FinanceDcPage{    1: i32 pageSize,                                    //頁容量    2: i32 pageIndex,                                   //當前頁索引}

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

SELECT COUNT(*) FROM my_table WHERE x = y ORDER BY id;

然後再返回指定頁麵條數給前端：

SELECT * FROM my_table WHERE x = y ORDER BY date_col LIMIT (pageIndex - 1) * pageSize, pageSize;

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

//分頁實體struct FinanceDcPage{    1: i32 pageSize,                                    //頁容量    2: i32 pageIndex,                                   //當前頁索引    3: i32 pageTotal,                                   //總頁數    4: i32 totalRecod,                                  //總條數}

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

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

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

最佳化方法

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

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

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

這裡有篇文章介紹了對電梯方式的最佳化，由於我做的項目還沒有上升到要對其做這種最佳化的地步，所以就直接上他的方式吧。

基於傳統方式的最佳化

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

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

order by最佳化

SELECT * FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000,5

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

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

SELECT * FROM pa_dc_flow INNER JOIN (SELECT id FROM pa_dc_flow ORDER BY subject_code DESC LIMIT 100000,5) AS pa_dc_flow_id USING(id);

下面是對兩條sql的 EXPLAIN

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

其實這涉及到order by的最佳化問題，第一條sql中並沒有利用到subject_code索引。如果你改為 select subject_code …則用到了索引。下面是對order by的最佳化。

order by 後的欄位，如果要走索引，須與where 條件裡的某欄位建立複合索引！！或者說orcer by後的欄位如果要走索引排序，它要麼與where 條件裡的欄位建立複合索引【這裡建立複合索引的時候，需要注意複合索引的列順序為（where欄位，order by 欄位），這樣才能滿足最左列原則，原因可能是order by欄位並能算在where 查詢條件中！】，要麼它自身要在where 條件裡被引用到！

表a subject_code為普通欄位,上面建有索引,id是自增主鍵

select * from a order by subject_code   //用不上索引select id from a order by subject_code  //能用上索引select subject_code from a order by subject_code    //能用上索引select * from a where subject_code = XX order by subject_code   //能用上索引

意思是說order by 要避免使用檔案系統排序，要麼把order by的欄位出現在select 後，要麼使用order by欄位出現在where 條件裡，要麼把order by欄位與where 條件欄位建立複合索引！

詳見order by關鍵字最佳化

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

count最佳化

當資料量非常大時，其實可以輸出總數的大概資料，利用explain語句，他並沒有真正去執行sql，而是進行的估算

2015-5-12 19:27:34

Brave,Happy,Thanksgiving !