在mysql中l(wèi)imit可以實(shí)現(xiàn)快速分頁(yè)耕腾，但是如果數(shù)據(jù)到了幾百萬(wàn)時(shí)我們的limit必須優(yōu)化才能有效的合理的實(shí)現(xiàn)分頁(yè)了，否則可能卡死你的服務(wù)器哦墩剖。
當(dāng)一個(gè)表數(shù)據(jù)有幾百萬(wàn)的數(shù)據(jù)時(shí)咐低，分頁(yè)的時(shí)候成了問(wèn)題如 select * from table limit 0,10 這個(gè)沒(méi)有問(wèn)題 當(dāng) limit 200000,10 的時(shí)候數(shù)據(jù)讀取就很慢杖们，可以按照一下方法解決
引發(fā)的SQL語(yǔ)句
代碼如下
復(fù)制代碼
SELECT a.uid, a.veil, a.content, a.datetimes, a.audit, b.user_name, b.uc_idFROM news_talkabout aLEFT JOIN users_info b ON a.uid = b.idWHERE infoid =11087ORDER BY a.id DESCLIMIT 451350 , 30
丟在phpmyadmin里執(zhí)行一下，是很慢肩狂。讓人思考是什么因素
代碼如下
復(fù)制代碼
SELECT a.uid, a.veil, a.content, a.datetimes, a.audit, b.user_name, b.uc_idFROM news_talkabout aLEFT JOIN users_info b ON a.uid = b.idWHERE infoid =11087ORDER BY a.id DESCLIMIT 0 , 30
第一頁(yè)會(huì)很快
PERCONA PERFORMANCE CONFERENCE 2009上摘完，來(lái)自雅虎的幾位工程師帶來(lái)了一篇"EfficientPagination Using MySQL"的報(bào)告
limit10000,20的意思掃描滿(mǎn)足條件的10020行，扔掉前面的10000行傻谁，返回最后的20行孝治，問(wèn)題就在這里。
LIMIT 451350 , 30 掃描了45萬(wàn)多行审磁，怪不得慢的數(shù)據(jù)庫(kù)都堵死了谈飒。
但是
limit 30 這樣的語(yǔ)句僅僅掃描30行。
那么如果我們之前記錄了最大ID态蒂，就可以在這里做文章
舉個(gè)例子
日常分頁(yè)SQL語(yǔ)句
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萬(wàn)左右
以下例子 當(dāng)時(shí)候 select * from wl_tagindex where byname='f' order by id limit 300000,10 執(zhí)行時(shí)間是 3.21s
優(yōu)化后：
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 速度明顯提升
這里需要說(shuō)明的是 我這里用到的字段是 byname ,id 需要把這兩個(gè)字段做復(fù)合索引，否則的話(huà)效果提升不明顯
總結(jié)
當(dāng)一個(gè)數(shù)據(jù)庫(kù)表過(guò)于龐大钾恢，LIMIT offset, length中的offset值過(guò)大手素，則SQL查詢(xún)語(yǔ)句會(huì)非常緩慢，你需增加order by瘩蚪，并且order by字段需要建立索引泉懦。
如果使用子查詢(xún)?nèi)?yōu)化LIMIT的話(huà)，則子查詢(xún)必須是連續(xù)的疹瘦，某種意義來(lái)講崩哩，子查詢(xún)不應(yīng)該有where條件，where會(huì)過(guò)濾數(shù)據(jù)拱礁，使數(shù)據(jù)失去連續(xù)性琢锋。
如果你查詢(xún)的記錄比較大，并且數(shù)據(jù)傳輸量比較大呢灶，比如包含了text類(lèi)型的field吴超，則可以通過(guò)建立子查詢(xún)。
SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);
如果limit語(yǔ)句的offset較大鸯乃，你可以通過(guò)傳遞pk鍵值來(lái)減小offset = 0鲸阻，這個(gè)主鍵最好是int類(lèi)型并且auto_increment
SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;
這條語(yǔ)句，大意如下:
SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;
如果limit的offset值過(guò)大缨睡，用戶(hù)也會(huì)翻頁(yè)疲勞鸟悴，你可以設(shè)置一個(gè)offset最大的，超過(guò)了可以另行處理奖年，一般連續(xù)翻頁(yè)過(guò)大细诸，用戶(hù)體驗(yàn)很差，則應(yīng)該提供更優(yōu)的用戶(hù)體驗(yàn)給用戶(hù)陋守。
limit 分頁(yè)優(yōu)化方法
1.子查詢(xún)優(yōu)化法
先找出第一條數(shù)據(jù)震贵，然后大于等于這條數(shù)據(jù)的id就是要獲取的數(shù)據(jù)
缺點(diǎn)：數(shù)據(jù)必須是連續(xù)的利赋，可以說(shuō)不能有where條件，where條件會(huì)篩選數(shù)據(jù)猩系，導(dǎo)致數(shù)據(jù)失去連續(xù)性
實(shí)驗(yàn)下
mysql> set profi[ling](http://www.111cn.net/list-198/)=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> nopagerPAGER set to stdout mysql> show profilesG*************************** 1. row ***************************Query_ID: 1Duration: 0.00003300 Query: select count(*) from Member *************************** 2. row ***************************Query_ID: 2Duration: 0.00167000 Query: select * from Member limit 10, 100*************************** 3. row ***************************Query_ID: 3Duration: 0.00112400 Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100 *************************** 4. row ***************************Query_ID: 4Duration: 0.00263200 Query: select * from Member limit 1000, 100*************************** 5. row ***************************Query_ID: 5Duration: 0.00134000 Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100 *************************** 6. row ***************************Query_ID: 6Duration: 0.09956700 Query: select * from Member limit 100000, 100*************************** 7. row ***************************Query_ID: 7Duration: 0.02447700 Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100
從結(jié)果中可以得知媚送，當(dāng)偏移1000以上使用子查詢(xún)法可以有效的提高性能
2.倒排表優(yōu)化法
倒排表法類(lèi)似建立索引，用一張表來(lái)維護(hù)頁(yè)數(shù)寇甸，然后通過(guò)高效的連接得到數(shù)據(jù)
缺點(diǎn)：只適合數(shù)據(jù)數(shù)固定的情況塘偎，數(shù)據(jù)不能刪除，維護(hù)頁(yè)表困難
3.反向查找優(yōu)化法
當(dāng)偏移超過(guò)一半記錄數(shù)的時(shí)候拿霉，先用排序吟秩，這樣偏移就反轉(zhuǎn)了
缺點(diǎn)：order by優(yōu)化比較麻煩，要增加索引友浸，索引影響數(shù)據(jù)的修改效率峰尝，并且要知道總記錄數(shù)
，偏移大于數(shù)據(jù)的一半
引用
limit偏移算法：
正向查找： (當(dāng)前頁(yè) - 1) * 頁(yè)長(zhǎng)度
反向查找： 總記錄 - 當(dāng)前頁(yè) * 頁(yè)長(zhǎng)度
做下實(shí)驗(yàn)收恢，看看性能如何
總記錄數(shù)：1,628,775
每頁(yè)記錄數(shù)： 40
總頁(yè)數(shù)：1,628,775 / 40 = 40720
中間頁(yè)數(shù)：40720 / 2 = 20360
第21000頁(yè)
正向查找SQL:
Sql代碼
SELECT * FROMabcWHEREBatchID= 123 LIMIT 839960, 40
時(shí)間：1.8696 秒
反向查找sql:
Sql代碼
SELECT * FROMabcWHEREBatchID= 123 ORDER BY InputDate DESC LIMIT 788775, 40
時(shí)間：1.8336 秒
第30000頁(yè)
正向查找SQL:
Sql代碼
SELECT * FROMabcWHEREBatchID= 123 LIMIT 1199960, 40
時(shí)間：2.6493 秒
反向查找sql:
Sql代碼
SELECT * FROMabcWHEREBatchID= 123 ORDER BY InputDate DESC LIMIT 428775, 40
時(shí)間：1.0035 秒
注意武学，反向查找的結(jié)果是是降序desc的，并且InputDate是記錄的插入時(shí)間伦意，也可以用主鍵聯(lián)合索引火窒，但是不方便。
4.limit限制優(yōu)化法
把limit偏移量限制低于某個(gè)數(shù)驮肉。熏矿。超過(guò)這個(gè)數(shù)等于沒(méi)數(shù)據(jù)，我記得alibaba的dba說(shuō)過(guò)他們是這樣做的
5.只查索引法
