當(dāng)前位置：維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql學(xué)習(xí)MySQL中分頁優(yōu)化的實(shí)例詳解

作者：VEPHP 時間 2017-09-21

《Mysql學(xué)習(xí)MySQL中分頁優(yōu)化的實(shí)例詳解》要點(diǎn)：
本文介紹了Mysql學(xué)習(xí)MySQL中分頁優(yōu)化的實(shí)例詳解，希望對您有用。如果有疑問，可以聯(lián)系我們。

通常,我們會采用ORDER BY LIMIT start, offset 的方式來進(jìn)行分頁查詢.例如下面這個SQL：MYSQL教程

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

或者像下面這個不帶任何條件的分頁SQL：MYSQL教程

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

一般而言,分頁SQL的耗時隨著 start 值的增加而急劇增加,我們來看下面這2個不同起始值的分頁SQL執(zhí)行耗時：MYSQL教程

yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
…
10 rows in set (0.05 sec)
yejr@imysql.com> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
…
10 rows in set (2.39 sec)

可以看到,隨著分頁數(shù)量的增加,SQL查詢耗時也有數(shù)十倍增加,顯然不科學(xué).今天我們就來分析下,如何能優(yōu)化這個分頁方案. 一般滴,想要優(yōu)化分頁的終極方案就是：沒有分頁,哈哈哈~~~,不要說我講廢話,確實(shí)如此,可以把分頁算法交給Sphinx、Lucence等第三方解決方案,沒必要讓MySQL來做它不擅長的事情. 當(dāng)然了,有小伙伴說,用第三方太麻煩了,我們就想用MySQL來做這個分頁,咋辦呢?莫急,且待我們慢慢分析,先看下表DDL、數(shù)據(jù)量、查詢SQL的執(zhí)行計劃等信息：MYSQL教程

yejr@imysql.com> 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;
yejr@imysql.com> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 994584 |
+----------+
yejr@imysql.com> 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
yejr@imysql.com> 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

可以看到,雖然通過主鍵索引進(jìn)行掃描了,但第二個SQL需要掃描的記錄數(shù)太大了,而且需要先掃描約935510條記錄,然后再根據(jù)排序結(jié)果取10條記錄,這肯定是非常慢了. 針對這種情況,我們的優(yōu)化思路就比較清晰了,有兩點(diǎn)：MYSQL教程

1、盡可能從索引中直接獲取數(shù)據(jù),避免或減少直接掃描行數(shù)據(jù)的頻率
2、盡可能減少掃描的記錄數(shù),也就是先確定起始的范圍,再往后取N條記錄即可MYSQL教程

據(jù)此,我們有兩種相應(yīng)的改寫方法：子查詢、表連接,即下面這樣的：MYSQL教程

#采用子查詢的方式優(yōu)化,在子查詢里先從索引獲取到最大id,然后倒序排,再取10行結(jié)果集
#注意這里采用了2次倒序排,因此在取LIMIT的start值時,比原來的值加了10,即935510,否則結(jié)果將和原來的不一致
MYSQL教程

yejr@imysql.com> 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優(yōu)化,JOIN子句里也優(yōu)先從索引獲取ID列表,然后直接關(guān)聯(lián)查詢獲得最終結(jié)果,這里不需要加10
yejr@imysql.com> 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個優(yōu)化后的新SQL執(zhí)行時間：MYSQL教程

yejr@imysql.com> 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)
#采用子查詢優(yōu)化,從profiling的結(jié)果來看,相比原來的那個SQL快了：28.2%
yejr@imysql.com> 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優(yōu)化,從profiling的結(jié)果來看,相比原來的那個SQL快了：30.8%

我們再來看一個不帶過濾條件的分頁SQL對比：MYSQL教程

#原始SQL
yejr@imysql.com> 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
yejr@imysql.com> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.22 sec)
#采用子查詢優(yōu)化
yejr@imysql.com> 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
yejr@imysql.com> 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)
#采用子查詢優(yōu)化,從profiling的結(jié)果來看,相比原來的那個SQL快了：10.6%
#采用INNER JOIN優(yōu)化
yejr@imysql.com> 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
yejr@imysql.com> 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優(yōu)化,從profiling的結(jié)果來看,相比原來的那個SQL快了：30.2%

至此,我們看到采用子查詢或者INNER JOIN進(jìn)行優(yōu)化后,都有大幅度的提升,這個方法也同樣適用于較小的分頁,雖然LIMIT開始的 start 位置小了很多,SQL執(zhí)行時間也快了很多,但采用這種方法后,帶WHERE條件的分頁分別能提高查詢效率：24.9%、156.5%,不帶WHERE條件的分頁分別提高查詢效率：554.5%、11.7%,各位可以自行進(jìn)行測試驗(yàn)證.單從提升比例說,還是挺可觀的,確保這些優(yōu)化方法可以適用于各種分頁模式,就可以從一開始就是用. 我們來看下各種場景相應(yīng)的提升比例是多少：
MYSQL教程

201558115710029.jpg (649×215) MYSQL教程

結(jié)論：這樣看就和明顯了,尤其是針對大分頁的情況,因此我們優(yōu)先推薦使用INNER JOIN方式優(yōu)化分頁算法.MYSQL教程

上述每次測試都重啟mysqld實(shí)例,并且加了SQL_NO_CACHE,以保證每次都是直接數(shù)據(jù)文件或索引文件中讀取.如果數(shù)據(jù)經(jīng)過預(yù)熱后,查詢效率會一定程度提升,但但上述相應(yīng)的效率提升比例還是基本一致的.
MYSQL教程

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/2337.html

標(biāo)簽：

欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

PHP教程

WEB前端開發(fā)

數(shù)據(jù)庫

WEB服務(wù)器

APP開發(fā)

LINUX學(xué)習(xí)

后端開發(fā)課程

前端開發(fā)課程

數(shù)據(jù)庫課程

Mysql學(xué)習(xí)MySQL中分頁優(yōu)化的實(shí)例詳解

同類教程排行

特輯教程