程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 分頁性能探索-mysql

分頁性能探索-mysql

編輯：MySQL綜合教程

分頁性能探索-mysql

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

常見的幾種分頁方式：

扶梯方式

vcnPzaizo9a7zOG5qcnP0rvSsy/PwtK70rPV4sG91tbEo8q9o6yyv7fWsvrGt8n11sGyu8zhuanJz9K70rO5psTco6zWu8zhuanSu9bWJmxkcXVvO7j8tuAvbW9yZSZyZHF1bzu1xLe9yr2jrNKy09DPwsCt19S2r7zT1Ni4/LbgtcS3vcq9o6zU2ry8yvXJz7a8v8nS1LnpxMmzybf2zN23vcq9oaM8YnIgLz4NCrf2zN23vcq91Nq8vMr1yrXP1snPsci9z7zytaW8sLjf0KejrLj5vt21scew0rPX7rrz0rvM9bXExqvSxs35uvO78cih0rvSs7y0v8mho9C0s8lTUUy/ycTcwODLxjwvcD4NCjxwcmUgY2xhc3M9"brush: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，這種方式