程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL優化案例系列-mysql分頁優化

MySQL優化案例系列-mysql分頁優化

編輯：MySQL綜合教程

MySQL優化案例系列-mysql分頁優化。本站提示廣大學習愛好者：（MySQL優化案例系列-mysql分頁優化）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL優化案例系列-mysql分頁優化正文

平日，我們會采取ORDER BY LIMIT start, offset 的方法來停止分頁查詢。例以下面這個SQL：

SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;

或許像上面這個不帶任何前提的分頁SQL：

SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;

普通而言，分頁SQL的耗時跟著 start 值的增長而急劇增長，我們來看上面這2個分歧肇端值的分頁SQL履行耗時：

[email protected]> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
…
10 rows in set (0.05 sec)
[email protected]> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
…
10 rows in set (2.39 sec)

可以看到，跟著分頁數目的增長，SQL查詢耗時也稀有十倍增長，明顯不迷信。明天我們就來剖析下，若何能優化這個分頁計劃。普通滴，想要優化分頁的最終計劃就是：沒有分頁，哈哈哈~~~，不要說我講空話，確切如斯，可以把分頁算法交給Sphinx、Lucence等第三方處理計劃，沒需要讓MySQL來做它不善於的工作。固然了，有小同伴說，用第三方太費事了，我們就想用MySQL來做這個分頁，咋辦呢？莫急，且待我們漸漸剖析，先看下表DDL、數據量、查詢SQL的履行籌劃等信息：

[email protected]> SHOW CREATE TABLE `t1`;
CREATE TABLE `t1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
 `ftype` tinyint(3) unsigned NOT NULL,
...
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[email protected]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 994584 |
+----------+

[email protected]> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 510
 Extra: Using where

[email protected]> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 935510
 Extra: Using where

可以看到，固然經由過程主鍵索引停止掃描了，但第二個SQL須要掃描的記載數太年夜了，並且須要先掃描約935510筆記錄，然後再依據排序成果取10筆記錄，這確定長短常慢了。針對這類情形，我們的優化思緒就比擬清楚了，有兩點：

1、盡量從索引中直接獲得數據，防止或削減直接掃描行數據的頻率
2、盡量削減掃描的記載數，也就是先肯定肇端的規模，再往後取N筆記錄便可

據此，我們有兩種響應的改寫辦法：子查詢、表銜接，即上面如許的：

#采取子查詢的方法優化，在子查詢裡先從索引獲得到最年夜id，然後倒序排，再取10行成果集
#留意這裡采取了2次倒序排，是以在取LIMIT的start值時，比本來的值加了10，即935510，不然成果將和本來的紛歧致

[email protected]> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: <derived2>
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 10
 Extra: Using filesort
*************************** 2. row ***************************
 id: 2
 select_type: DERIVED
 table: t1
 type: ALL
possible_keys: PRIMARY
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 973192
 Extra: Using where
*************************** 3. row ***************************
 id: 3
 select_type: SUBQUERY
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 935511
 Extra: Using where

#采取INNER JOIN優化，JOIN子句裡也優先從索引獲得ID列表，然後直接聯系關系查詢取得終究成果，這裡不須要加10
[email protected]> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: <derived2>
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 935510
 Extra: NULL
*************************** 2. row ***************************
 id: 1
 select_type: PRIMARY
 table: t1
 type: eq_ref
possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: t2.id
 rows: 1
 Extra: NULL
*************************** 3. row ***************************
 id: 2
 select_type: DERIVED
 table: t1
 type: index
possible_keys: NULL
 key: PRIMARY
 key_len: 4
 ref: NULL
 rows: 973192
 Extra: Using where

然後我們來比較下這2個優化後的新SQL履行時光：

[email protected]> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC;
...
rows in set (1.86 sec)
#采取子查詢優化，從profiling的成果來看，比擬本來的誰人SQL快了：28.2%

[email protected]> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
...
10 rows in set (1.83 sec)
#采取INNER JOIN優化，從profiling的成果來看，比擬本來的誰人SQL快了：30.8%

我們再來看一個不帶過濾前提的分頁SQL比較：

#原始SQL
[email protected]> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
   id: 1
 select_type: SIMPLE
  table: t1
   type: index
possible_keys: NULL
   key: PRIMARY
  key_len: 4
   ref: NULL
   rows: 935510
  Extra: NULL

[email protected]> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.22 sec)

#采取子查詢優化
[email protected]> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
*************************** 1. row ***************************
   id: 1
 select_type: PRIMARY
  table: <derived2>
   type: ALL
possible_keys: NULL
   key: NULL
  key_len: NULL
   ref: NULL
   rows: 10
  Extra: Using filesort
*************************** 2. row ***************************
   id: 2
 select_type: DERIVED
  table: t1
   type: ALL
possible_keys: PRIMARY
   key: NULL
  key_len: NULL
   ref: NULL
   rows: 973192
  Extra: Using where
*************************** 3. row ***************************
   id: 3
 select_type: SUBQUERY
  table: t1
   type: index
possible_keys: NULL
   key: PRIMARY
  key_len: 4
   ref: NULL
   rows: 935511
  Extra: Using index

[email protected]> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
…
10 rows in set (2.01 sec)
#采取子查詢優化，從profiling的成果來看，比擬本來的誰人SQL快了：10.6%


#采取INNER JOIN優化
[email protected]> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
   id: 1
 select_type: PRIMARY
  table: 
   type: ALL
possible_keys: NULL
   key: NULL
  key_len: NULL
   ref: NULL
   rows: 935510
  Extra: NULL
*************************** 2. row ***************************
   id: 1
 select_type: PRIMARY
  table: t1
   type: eq_ref
possible_keys: PRIMARY
   key: PRIMARY
  key_len: 4
   ref: t1.id
   rows: 1
  Extra: NULL
*************************** 3. row ***************************
   id: 2
 select_type: DERIVED
  table: t1
   type: index
possible_keys: NULL
   key: PRIMARY
  key_len: 4
   ref: NULL
   rows: 973192
  Extra: Using index

[email protected]> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
…
10 rows in set (1.70 sec)
#采取INNER JOIN優化，從profiling的成果來看，比擬本來的誰人SQL快了：30.2%

至此，我們看到采取子查詢或許INNER JOIN停止優化後，都有年夜幅度的晉升，這個辦法也異樣實用於較小的分頁，固然LIMIT開端的 start 地位小了許多，SQL履行時光也快了許多，但采取這類辦法後，帶WHERE前提的分頁分離能進步查詢效力：24.9%、156.5%，不帶WHERE前提的分頁分離進步查詢效力：554.5%、11.7%，列位可以自行停止測實驗證。單從晉升比例說，照樣挺可不雅的，確保這些優化辦法可以實用於各類分頁形式，便可以從一開端就是用。我們來看下各類場景響應的晉升比例是若干：

年夜分頁，帶WHERE 年夜分頁，不帶WHERE 年夜分頁均勻晉升比例小分頁，帶WHERE 小分頁，不帶WHERE 整體均勻晉升比例子查詢優化 28.20% 10.60% 19.40% 24.90% 554.40% 154.53% INNER JOIN優化 30.80% 30.20% 30.50% 156.50% 11.70% 57.30%

結論：如許看就和顯著了，特別是針對年夜分頁的情形，是以我們優先推舉應用INNER JOIN方法優化分頁算法。

上述每次測試都重啟mysqld實例，而且加了SQL_NO_CACHE，以包管每次都是直接數據文件或索引文件中讀取。假如數據經由預熱後，查詢效力會必定水平晉升，但但上述響應的效力晉升比例照樣根本分歧的。

2014/07/28跋文更新：

其實假如是不帶任何前提的分頁，就沒需要用這麼費事的辦法了，可以采取對主鍵采取規模檢索的辦法，例如參考這篇：Advance for MySQL Pagination