久久久精品一区ed2k-女人被男人叉到高潮的视频-中文字幕乱码一区久久麻豆樱花-俄罗斯熟妇真实视频

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

0、導(dǎo)讀

在分頁(yè)功能開(kāi)發(fā)時(shí)，我們很習(xí)慣用LIMIT O,N的方法來(lái)取數(shù)據(jù)。這種方法在遇到超大分頁(yè)偏移量時(shí)是會(huì)把MySQL搞死的，請(qǐng)別再這么寫(xiě)SQL了

成都創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),寶雞企業(yè)網(wǎng)站建設(shè),寶雞品牌網(wǎng)站建設(shè),網(wǎng)站定制,寶雞網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷(xiāo),網(wǎng)絡(luò)優(yōu)化,寶雞網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M(mǎn)足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專(zhuān)業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶(hù)成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

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

通常，我們會(huì)采用ORDER BY LIMIT start, offset 的方式來(lái)進(jìn)行分頁(yè)查詢(xún)。例如下面這個(gè)SQL：

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

或者像下面這個(gè)不帶任何條件的分頁(yè)SQL：

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

一般而言，分頁(yè)SQL的耗時(shí)隨著 start 值的增加而急劇增加，我們來(lái)看下面這2個(gè)不同起始值的分頁(yè)SQL執(zhí)行耗時(shí)：

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)

可以看到，隨著分頁(yè)數(shù)量的增加，SQL查詢(xún)耗時(shí)也有數(shù)十倍增加，顯然不科學(xué)。

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

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

當(dāng)然了，有小伙伴說(shuō)，用第三方太麻煩了，我們就想用MySQL來(lái)做這個(gè)分頁(yè)，咋辦呢？莫急，且待我們慢慢分析。

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

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

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

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

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

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

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

#方法一

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

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


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)查詢(xú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

然后來(lái)對(duì)比下這2個(gè)優(yōu)化后的執(zhí)行時(shí)間/代價(jià)：

#1、子查詢(xún)優(yōu)化：從profiling的結(jié)果來(lái)看，相比原來(lái)耗時(shí)減少 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é)果來(lái)看，相比原來(lái)耗時(shí)減少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)

再來(lái)看一個(gè)不帶過(guò)濾條件的分頁(yè)SQL對(duì)比：

#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、采用子查詢(xún)優(yōu)化，相比原來(lái)耗時(shí)減少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)化，相比原來(lái)耗時(shí)減少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)

至此，我們看到采用子查詢(xún)或者INNER JOIN進(jìn)行優(yōu)化后，都有大幅度的提升，這個(gè)方法也同樣適用于較小的分頁(yè)。

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

  • 帶WHERE條件的分頁(yè)分別能提高查詢(xún)效率：24.9%、156.5%；

  • 不帶WHERE條件的分頁(yè)分別提高查詢(xún)效率：554.5%、11.7%。

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

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


 大分頁(yè)，帶WHERE 大分頁(yè)，不帶WHERE 大分頁(yè)平均提升比例 小分頁(yè)，帶WHERE 小分頁(yè)，不帶WHERE 總體平均提升比例
子查詢(xún)優(yōu)化 28.20% 10.60% 19.40% 24.90% 554.40% 154.53%
INNER JOIN優(yōu)化 30.80% 30.20% 30.50% 156.50% 11.70% 57.30%

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

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

相關(guān)閱讀：

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

老葉茶館鎮(zhèn)店之寶，掃碼識(shí)別或訪(fǎng)問(wèn) http://yejinrong.com 直達(dá)
優(yōu)化 | 再用傳統(tǒng)分頁(yè)SQL你就死定了

本文標(biāo)題：優(yōu)化|再用傳統(tǒng)分頁(yè)SQL你就死定了
文章源于：http://sd-ha.com/article4/pchjoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、外貿(mào)建站、軟件開(kāi)發(fā)、網(wǎng)站策劃、靜態(tài)網(wǎng)站、Google

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都seo排名網(wǎng)站優(yōu)化