優(yōu)化 | 再用傳統(tǒng)分頁SQL你就死定了

0、導(dǎo)讀

在分頁功能開發(fā)時，我們很習慣用LIMIT O,N的方法來取數(shù)據(jù)。這種方法在遇到超大分頁偏移量時是會把MySQL搞死的，請別再這么寫SQL了

通常，我們會采用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執(zhí)行耗時：

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ù)十倍增加，顯然不科學。

今天我們就來分析下，如何能優(yōu)化這個分頁方案。

一般滴，想要優(yōu)化分頁的終極方案就是：沒有分頁，哈哈哈~~~，不要說我講廢話，確實如此，可以把分頁算法交給Solr、Lucene、Sphinx等第三方解決方案，尤其是遇到有模糊搜索的需求時，沒必要讓MySQL來做它不擅長的事情。

當然了，有小伙伴說，用第三方太麻煩了，我們就想用MySQL來做這個分頁，咋辦呢？莫急，且待我們慢慢分析。

先看下表DDL、數(shù)據(jù)量、查詢SQL的執(zhí)行計劃等信息：

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

可以看到，雖然是通過主鍵索引掃描數(shù)據(jù)的，但第二個SQL需要掃描的記錄數(shù)太大了，而且需要先掃描約935510條記錄，然后再根據(jù)排序結(jié)果取10條記錄，這肯定是非常慢了。

針對這種情況，我們的優(yōu)化思路就比較清晰了，有兩點：

1. 盡可能從索引中直接獲取數(shù)據(jù)，避免或減少再次掃描行數(shù)據(jù)的次數(shù)（也就是我們通常所說的避免回表）；

2. 盡可能減少掃描的記錄數(shù)，也就是先確定起始的范圍，再往后取N條記錄。

根據(jù)上面這兩種優(yōu)化思路，有相應(yīng)的SQL改寫方法：子查詢、表連接，像下面這樣的：

#方法一

#采用子查詢的方式優(yōu)化，在子查詢里先從索引獲取到最大id，然后倒序排，再取10行結(jié)果集

#注意這里采用了兩次倒序排，因此在取LIMIT的start值時，比原來的值加了10，即935510，否則結(jié)果將和原來的不一致

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)化后的執(zhí)行時間/代價：

#1、子查詢優(yōu)化：從profiling的結(jié)果來看，相比原來耗時減少 28.2%

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)

#2、INNER JOIN優(yōu)化：從profiling的結(jié)果來看，相比原來耗時減少30.8%

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)

再來看一個不帶過濾條件的分頁SQL對比：

#1、原始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)

#2、采用子查詢優(yōu)化，相比原來耗時減少10.6%

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)

#3、采用INNER JOIN優(yōu)化，相比原來耗時減少30.2%

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)化后，都有大幅度的提升，這個方法也同樣適用于較小的分頁。

說下結(jié)論，子查詢和INNER JOIN分頁優(yōu)化方法的提升效率是：

• 帶WHERE條件的分頁分別能提高查詢效率：24.9%、156.5%；

• 不帶WHERE條件的分頁分別提高查詢效率：554.5%、11.7%。

單從提升比例說，還是挺可觀的。而且這兩種優(yōu)化方法基本上可適用于各種分頁模式，強烈建議一開始就改成這種SQL寫法習慣。

我們來看下各種場景相應(yīng)的提升比例是多少：

這樣看就和明顯了，尤其是針對大分頁的情況，因此我們優(yōu)先推薦使用INNER JOIN方式優(yōu)化分頁算法。

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

相關(guān)閱讀：

[MySQL優(yōu)化案例]系列 — discuz!熱帖翻頁優(yōu)化