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%以上。
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%,列位可以自行停止測實驗證。單從晉升比例說,照樣挺可不雅的,確保這些優化辦法可以實用於各類分頁形式,便可以從一開端就是用。 我們來看下各類場景響應的晉升比例是若干:
結論:如許看就和顯著了,特別是針對年夜分頁的情形,是以我們優先推舉應用INNER JOIN方法優化分頁算法。
上述每次測試都重啟mysqld實例,而且加了SQL_NO_CACHE,以包管每次都是直接數據文件或索引文件中讀取。假如數據經由預熱後,查詢效力會必定水平晉升,但但上述響應的效力晉升比例照樣根本分歧的。