《Mysql實(shí)例MYSQL分頁limit速度太慢的優(yōu)化方法》要點(diǎn)：
本文介紹了Mysql實(shí)例MYSQL分頁limit速度太慢的優(yōu)化方法，希望對您有用。如果有疑問，可以聯(lián)系我們。

在mysql中l(wèi)imit可以實(shí)現(xiàn)快速分頁,但是如果數(shù)據(jù)到了幾百萬時(shí)我們的limit必須優(yōu)化才能有效的合理的實(shí)現(xiàn)分頁了,否則可能卡死你的服務(wù)器哦.MYSQL學(xué)習(xí)

?? 當(dāng)一個(gè)表數(shù)據(jù)有幾百萬的數(shù)據(jù)的時(shí)候成了問題!MYSQL學(xué)習(xí)

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

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

?? 舉個(gè)例子MYSQL學(xué)習(xí)

?? 日常分頁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行.
?? 總數(shù)據(jù)有500萬左右
?? 以下例子 當(dāng)時(shí)候 select * from wl_tagindex where byname='f' order by id limit 300000,10 執(zhí)行時(shí)間是 3.21s
?? 優(yōu)化后：MYSQL學(xué)習(xí)

 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

?? 執(zhí)行時(shí)間為 0.11s 速度明顯提升
?? 這里需要說明的是 我這里用到的字段是 byname ,id 需要把這兩個(gè)字段做復(fù)合索引,否則的話效果提升不明顯MYSQL學(xué)習(xí)

?? 總結(jié)MYSQL學(xué)習(xí)

?? 當(dāng)一個(gè)數(shù)據(jù)庫表過于龐大,LIMIT offset, length中的offset值過大,則SQL查詢語句會(huì)非常緩慢,你需增加order by,并且order by字段需要建立索引.
?? 如果使用子查詢?nèi)?yōu)化LIMIT的話,則子查詢必須是連續(xù)的,某種意義來講,子查詢不應(yīng)該有where條件,where會(huì)過濾數(shù)據(jù),使數(shù)據(jù)失去連續(xù)性.
?? 如果你查詢的記錄比較大,并且數(shù)據(jù)傳輸量比較大,比如包含了text類型的field,則可以通過建立子查詢.MYSQL學(xué)習(xí)

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

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

?? SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;MYSQL學(xué)習(xí)

?? 這條語句,大意如下:MYSQL學(xué)習(xí)

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

?? limit 分頁優(yōu)化方法MYSQL學(xué)習(xí)

?? 1.子查詢優(yōu)化法
?? 先找出第一條數(shù)據(jù),然后大于等于這條數(shù)據(jù)的id就是要獲取的數(shù)據(jù)
?? 缺點(diǎn)：數(shù)據(jù)必須是連續(xù)的,可以說不能有where條件,where條件會(huì)篩選數(shù)據(jù),導(dǎo)致數(shù)據(jù)失去連續(xù)性
?? 實(shí)驗(yàn)下
??? 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
??? 從結(jié)果中可以得知,當(dāng)偏移1000以上使用子查詢法可以有效的提高性能.
?? 2.倒排表優(yōu)化法
?? 倒排表法類似建立索引,用一張表來維護(hù)頁數(shù),然后通過高效的連接得到數(shù)據(jù)
?? 缺點(diǎn)：只適合數(shù)據(jù)數(shù)固定的情況,數(shù)據(jù)不能刪除,維護(hù)頁表困難
?? 3.反向查找優(yōu)化法
?? 當(dāng)偏移超過一半記錄數(shù)的時(shí)候,先用排序,這樣偏移就反轉(zhuǎn)了
?? 缺點(diǎn)：order by優(yōu)化比較麻煩,要增加索引,索引影響數(shù)據(jù)的修改效率,并且要知道總記錄數(shù)
?? ,偏移大于數(shù)據(jù)的一半
?? 引用
?? limit偏移算法：
?? 正向查找： (當(dāng)前頁 C 1) * 頁長度
?? 反向查找： 總記錄 C 當(dāng)前頁 * 頁長度
?? 做下實(shí)驗(yàn),看看性能如何
?? 總記錄數(shù)：1,628,775
?? 每頁記錄數(shù)： 40
?? 總頁數(shù)：1,628,775 / 40 = 40720
?? 中間頁數(shù)：40720 / 2 = 20360
?? 第21000頁
?? 正向查找SQL:
?? Sql代碼
?? SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 839960, 40
?? 時(shí)間：1.8696 秒
?? 反向查找sql:
?? Sql代碼
?? SELECT * FROM `abc` WHERE `BatchID` = 123 ORDER BY InputDate DESC LIMIT 788775, 40
?? 時(shí)間：1.8336 秒
?? 第30000頁
?? 正向查找SQL:
?? Sql代碼
?? 1.SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40
?? SELECT * FROM `abc` WHERE `BatchID` = 123 LIMIT 1199960, 40
?? 時(shí)間：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
??? 時(shí)間：1.0035 秒
?? 注意,反向查找的結(jié)果是是降序desc的,并且InputDate是記錄的插入時(shí)間,也可以用主鍵聯(lián)合索引,但是不方便.
?? 4.limit限制優(yōu)化法
?? 把limit偏移量限制低于某個(gè)數(shù)..超過這個(gè)數(shù)等于沒數(shù)據(jù),我記得alibaba的dba說過他們是這樣做的
?? 5.只查索引法MYSQL學(xué)習(xí)

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/2063.html