《Mysql實例MYSQL分頁limit速度太慢的優化方法》要點：
本文介紹了Mysql實例MYSQL分頁limit速度太慢的優化方法，希望對您有用。如果有疑問，可以聯系我們。

在mysql中limit可以實現快速分頁,但是如果數據到了幾百萬時我們的limit必須優化才能有效的合理的實現分頁了,否則可能卡死你的服務器哦.MYSQL學習

?? 當一個表數據有幾百萬的數據的時候成了問題!MYSQL學習

?? 如 * from table limit 0,10 這個沒有問題 當 limit 200000,10 的時候數據讀取就很慢,可以按照一下方法解決
??? 第一頁會很快
?? PERCONA PERFORMANCE CONFERENCE 2009上,來自雅虎的幾位工程師帶來了一篇”EfficientPagination Using MySQL”的報告
?? limit10000,20的意思掃描滿足條件的10020行,扔掉前面的10000行,返回最后的20行,問題就在這里.
?? LIMIT 451350 , 30 掃描了45萬多行,怪不得慢的都堵死了.
?? 但是
?? limit 30 這樣的語句僅僅掃描30行.MYSQL學習

?? 那么如果我們之前記錄了最大ID,就可以在這里做文章MYSQL學習

?? 舉個例子MYSQL學習

?? 日常分頁SQL語句
?? select id,name,content from users order by id asc limit 100000,20
?? 掃描100020行
?? 如果記錄了上次的最大ID
?? select id,name,content from users where id>100073 order by id asc limit 20
?? 掃描20行.
?? 總數據有500萬左右
?? 以下例子 當時候 select * from wl_tagindex where byname='f' order by id limit 300000,10 執行時間是 3.21s
?? 優化后：MYSQL學習

 select * from (
    select id from wl_tagindex
    where byname='f' order by id limit 300000,10
 ) a
 left join wl_tagindex b on a.id=b.id

?? 執行時間為 0.11s 速度明顯提升
?? 這里需要說明的是 我這里用到的字段是 byname ,id 需要把這兩個字段做復合索引,否則的話效果提升不明顯MYSQL學習

?? 總結MYSQL學習

?? 當一個數據庫表過于龐大,LIMIT offset, length中的offset值過大,則SQL查詢語句會非常緩慢,你需增加order by,并且order by字段需要建立索引.
?? 如果使用子查詢去優化LIMIT的話,則子查詢必須是連續的,某種意義來講,子查詢不應該有where條件,where會過濾數據,使數據失去連續性.
?? 如果你查詢的記錄比較大,并且數據傳輸量比較大,比如包含了text類型的field,則可以通過建立子查詢.MYSQL學習

?? SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);MYSQL學習

?? 如果limit語句的offset較大,你可以通過傳遞pk鍵值來減小offset = 0,這個主鍵最好是int類型并且auto_incrementMYSQL學習

?? SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;MYSQL學習

?? 這條語句,大意如下:MYSQL學習

?? SELECT * FROM users WHERE uid >=? (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;
?? 如果limit的offset值過大,用戶也會翻頁疲勞,你可以設置一個offset最大的,超過了可以另行處理,一般連續翻頁過大,用戶體驗很差,則應該提供更優的用戶體驗給用戶.MYSQL學習

?? limit 分頁優化方法MYSQL學習

?? 1.子查詢優化法
?? 先找出第一條數據,然后大于等于這條數據的id就是要獲取的數據
?? 缺點：數據必須是連續的,可以說不能有where條件,where條件會篩選數據,導致數據失去連續性
?? 實驗下
??? mysql> set profi=1;
?? Query OK, 0 rows affected (0.00 sec)
?? mysql> select count(*) from Member;
?? +―――-+
?? | count(*) |
?? +―――-+
?? |?? 169566 |
?? +―――-+
?? 1 row in set (0.00 sec)
?? mysql> pager grep !~-
?? PAGER set to ‘grep !~-‘
?? mysql> select * from Member limit 10, 100;
?? 100 rows in set (0.00 sec)
?? mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
?? 100 rows in set (0.00 sec)
?? mysql> select * from Member limit 1000, 100;
?? 100 rows in set (0.01 sec)
?? mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
?? 100 rows in set (0.00 sec)
?? mysql> select * from Member limit 100000, 100;
?? 100 rows in set (0.10 sec)
?? mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
?? 100 rows in set (0.02 sec)
?? mysql> nopager
?? PAGER set to stdout
?? mysql> show profilesG
?? *************************** 1. row ***************************
?? Query_ID: 1
?? Duration: 0.00003300
????? Query: select count(*) from Member
?? *************************** 2. row ***************************
?? Query_ID: 2
?? Duration: 0.00167000
????? Query: select * from Member limit 10, 100
?? *************************** 3. row ***************************
?? Query_ID: 3
?? Duration: 0.00112400
????? Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
?? *************************** 4. row ***************************
?? Query_ID: 4
?? Duration: 0.00263200
????? Query: select * from Member limit 1000, 100
?? *************************** 5. row ***************************
?? Query_ID: 5
?? Duration: 0.00134000
????? Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
?? *************************** 6. row ***************************
?? Query_ID: 6
?? Duration: 0.09956700
????? Query: select * from Member limit 100000, 100
?? *************************** 7. row ***************************
?? Query_ID: 7
?? Duration: 0.02447700
????? Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100
??? 從結果中可以得知,當偏移1000以上使用子查詢法可以有效的提高性能.
?? 2.倒排表優化法
?? 倒排表法類似建立索引,用一張表來維護頁數,然后通過高效的連接得到數據
?? 缺點：只適合數據數固定的情況,數據不能刪除,維護頁表困難
?? 3.反向查找優化法
?? 當偏移超過一半記錄數的時候,先用排序,這樣偏移就反轉了
?? 缺點：order by優化比較麻煩,要增加索引,索引影響數據的修改效率,并且要知道總記錄數
?? ,偏移大于數據的一半
?? 引用
?? limit偏移算法：
?? 正向查找： (當前頁 C 1) * 頁長度
?? 反向查找： 總記錄 C 當前頁 * 頁長度
?? 做下實驗,看看性能如何
?? 總記錄數：1,628,775
?? 每頁記錄數： 40
?? 總頁數：1,628,775 / 40 = 40720
?? 中間頁數：40720 / 2 = 20360
?? 第21000頁
?? 正向查找SQL:
?? Sql代碼
?? SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40
?? 時間：1.8696 秒
?? 反向查找sql:
?? Sql代碼
?? SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40
?? 時間：1.8336 秒
?? 第30000頁
?? 正向查找SQL:
?? Sql代碼
?? 1.SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40
?? SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40
?? 時間：2.6493 秒
?? 反向查找sql:
?? Sql代碼
?? 1.SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40
?? SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 428775, 40
??? 時間：1.0035 秒
?? 注意,反向查找的結果是是降序desc的,并且InputDate是記錄的插入時間,也可以用主鍵聯合索引,但是不方便.
?? 4.limit限制優化法
?? 把limit偏移量限制低于某個數..超過這個數等于沒數據,我記得alibaba的dba說過他們是這樣做的
?? 5.只查索引法MYSQL學習

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/2063.html