程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 在年夜數據情形下MySQL的一種簡略分頁優化辦法

在年夜數據情形下MySQL的一種簡略分頁優化辦法

編輯:MySQL綜合教程

在年夜數據情形下MySQL的一種簡略分頁優化辦法。本站提示廣大學習愛好者:(在年夜數據情形下MySQL的一種簡略分頁優化辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是在年夜數據情形下MySQL的一種簡略分頁優化辦法正文


平日運用須要對表中的數據停止翻頁,假如數據量很年夜,常常會帶來機能上的成績:

root@sns 07:16:25>select count(*) from reply_0004 where thread_id = 5616385 and deleted = 0;
+———-+
| count(*) |
+———-+
| 1236795 |
+———-+
1 row in set (0.44 sec)
root@sns 07:16:30>select id
from reply_0004 where thread_id = 5616385 and deleted = 0
order by id asc limit 1236785, 10 ;
+———–+
| id    |
+———–+
| 162436798 |
| 162438180 |
| 162440102 |
| 162442044 |
| 162479222 |
| 162479598 |
| 162514705 |
| 162832588 |
| 162863394 |
| 162899685 |
+———–+
10 rows in set (1.32 sec)

索引:threa_id+deleted+id(gmt_Create)
10 rows in set (1.32 sec)
這兩條sql是為查詢最初一頁的翻頁sql查詢用的。因為一次翻頁常常只須要查詢較小的數據,如10條,但須要向後掃描年夜量的數據,也就是越往後的翻頁查詢,掃描的數據量會越多,查詢的速度也就愈來愈慢。
因為查詢的數據量年夜小是固定的,假如查詢速度不受翻頁的頁數影響,或許影響最低,那末如許是最好的後果了(查詢最初最幾頁的速度和開端幾頁的速度分歧)。
在翻頁的時刻,常常須要對個中的某個字段做排序(這個字段在索引中),升序排序。那末可弗成以應用索引的有序性來處理下面碰到的成績喃,謎底是確定的。好比有10000條數據須要做分頁,那末前5000條做asc排序,後5000條desc排序,在limit startnum,pagesize參數中作出響應的調劑。
然則這無疑給運用法式帶來龐雜,這條sql是用於服裝論壇t.vhao.net答復帖子的sql,常常用戶在看帖子的時刻,普通都是檢查前幾頁和最初幾頁,那末在翻頁的時刻最初幾頁的翻頁查詢采取desc的方法來完成翻頁,如許便可以較好的進步機能:

root@snsgroup 07:16:49>select * from (select id

->      from group_thread_reply_0004 where thread_id = 5616385 and deleted = 0

->    order by id desc limit 0, 10)t order by t.id asc;

+———–+

| id    |

+———–+

| 162436798 |

| 162438180 |

| 162440102 |

| 162442044 |

| 162479222 |

| 162479598 |

| 162514705 |

| 162832588 |

| 162863394 |

| 162899685 |

+———–+

10 rows in set (0.87 sec)

可以看到機能晉升了50%以上。

數太年夜了,並且須要先掃描約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%,列位可以自行停止測實驗證。單從晉升比例說,照樣挺可不雅的,確保這些優化辦法可以實用於各類分頁形式,便可以從一開端就是用。 我們來看下各類場景響應的晉升比例是若干:

201558115710029.jpg (649×215)

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

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved