在年夜數據情形下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，以包管每次都是直接數據文件或索引文件中讀取。假如數據經由預熱後，查詢效力會必定水平晉升，但但上述響應的效力晉升比例照樣根本分歧的。
