在秦張良椎尔店，在漢蘇武節(jié)
LIMIT 分頁性能問題
上圖出自《高性能MYSQL》
問題sql： 普通limit
EXPLAIN SELECT * FROM emp ORDER BY id LIMIT 5000000,10
問題sql執(zhí)行時(shí)間 8.231秒，存在性能問題。
業(yè)務(wù)上的優(yōu)化
1、限制用戶分頁专执，不允許進(jìn)行過大的分頁。比如說百度的分頁郁油，它最多只能跳轉(zhuǎn)到一定頁數(shù)本股，用戶再往下翻頁也只顯示最后一頁攀痊。
2、禁用count(*)痊末，不去查詢總條數(shù)蚕苇，不計(jì)算總頁數(shù)，不設(shè)計(jì)跳頁的功能
sql層面優(yōu)化方式
1凿叠、增加冗余的列 “頁碼” 來定位記錄
對條件值可以進(jìn)行估算，對于幾百上千頁的檢索嚼吞，往往不需要很精確盒件。也可以專門增加冗余的列來定位記錄，比如如下的 查詢舱禽，有一個(gè)page列炒刁，指定記錄所在的頁，代價(jià)是在修改數(shù)據(jù)的時(shí)候需要維護(hù)這個(gè)列的數(shù)據(jù)誊稚，如下面的查詢翔始。
SELECT * FROM emp WHERE page = 100 ORDER BY name;
2、索引覆蓋里伯，延時(shí)關(guān)聯(lián)
使用 索引覆蓋城瞎，延時(shí)關(guān)聯(lián) 的方式，將大大提高查詢效率疾瓮，它讓mysql掃描盡可能少的頁面脖镀，獲取需要訪問的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢需要所有的列。這個(gè)技術(shù)可以用于優(yōu)化關(guān)聯(lián)查詢中的LIMIT字句狼电。
1蜒灰、我們可以使用
SELECT id FROM emp ORDER BY id LIMIT 5000000,10充當(dāng)子查詢，這樣就只在索引中查詢id肩碟，而不用回表查强窖；然后根據(jù)子查詢得出來的id集合再去查對應(yīng)的select * 數(shù)據(jù)即可;
EXPLAIN SELECT * FROM emp INNER JOIN (SELECT id FROM emp ORDER BY id LIMIT 5000000,10 )AS tb USING(id)
最后查詢時(shí)間是 5.4 秒，效果在這里并不理想削祈。但是
索引覆蓋翅溺，延時(shí)關(guān)聯(lián)的優(yōu)化思想是非常好的！它不僅是可以用在這里岩瘦。
3未巫、還有注意一點(diǎn)： 能用
INNER JOIN就不要使用IN。比如在這個(gè)limit優(yōu)化中启昧，我使用的是INNER JOIN自關(guān)聯(lián)查詢叙凡。如果我使用IN呢？情況會(huì)如何密末？
EXPLAIN SELECT * FROM emp WHERE id IN ( SELECT id from ( SELECT id FROM emp ORDER BY id LIMIT 5000010, 10)tb )
首先握爷，使用IN 的話最終的查詢時(shí)間是15.7秒跛璧，是使用JOIN的三倍！再來看它的查詢計(jì)劃： 直接多了一個(gè)查詢新啼！
4追城、計(jì)算出邊界值，直接 BETWEEN AND
可以將limit查詢轉(zhuǎn)換為已知位置的查詢燥撞，讓mysql通過范圍掃描獲得到對應(yīng)的結(jié)果座柱。
EXPLAIN SELECT * FROM emp WHERE id BETWEEN 5000001 AND 5000010 ORDER BY id
使用BETWEEN .. AND 最終的時(shí)間是 0.063秒，少去了大量的掃描操作物舒；使用這種方式的前提是必須要知道id的邊界值色洞！同樣id必須是連續(xù)單調(diào)遞增的；如果之間有數(shù)據(jù)被刪除掉冠胯，或者因?yàn)閣here條件過濾掉火诸，那么最終得到的頁數(shù)不會(huì)是10了吧；所以這種方式局限性太大了
5荠察、得到上頁最后一條記錄的id置蜀，然后limit
假設(shè)查詢返回的是 5000001 到 5000011 的記錄，那么下一頁查詢就可以從這個(gè)5000011 開始拿10條數(shù)據(jù)悉盆。這樣做無論翻多少也性能都會(huì)很好盯荤！
EXPLAIN SELECT * FROM emp WHERE id >= 5000001 ORDER BY id LIMIT 10
這種方式查詢時(shí)間為 0.067。是limit優(yōu)化的首選舀瓢。不過它不能實(shí)現(xiàn)當(dāng)前頁（無法實(shí)現(xiàn)點(diǎn)擊頁碼跳轉(zhuǎn)）廷雅，只能上一頁，下一頁的翻頁京髓。適合app端分頁優(yōu)化航缀；它要求id是遞增的（不能使用uuid等字符串充當(dāng)id），但id可以不連續(xù)（可以使用where過濾堰怨、刪除數(shù)據(jù)芥玉，都不會(huì)影響分頁）；我們來看看它的查詢計(jì)劃如下：
結(jié)合索引優(yōu)化
最終是推薦使用第3個(gè)方法：得到上頁最后一條記錄的id备图，然后limit灿巧；但是若需要同時(shí)進(jìn)行where條件檢索、 order by排序的話就必須參加合適的聯(lián)合索引了揽涮！
比如下面的語句抠藕，使用ename 進(jìn)行l(wèi)ike的模糊查詢， 排序的時(shí)候指定id DESC,ename DESC
EXPLAIN SELECT
*
FROM
emp
WHERE
id >= 5000001
AND ename LIKE '%a%'
ORDER BY
id DESC,
ename DESC
LIMIT 10
那么先看看查詢計(jì)劃：出現(xiàn)了 Using filesort
然后執(zhí)行下蒋困，執(zhí)行時(shí)間為 4.6秒盾似。需要優(yōu)化！
所以雪标，需要?jiǎng)?chuàng)建聯(lián)合索引如下 index(id,ename)
SHOW INDEX FROM emp
最終查詢時(shí)間為：0.089 秒零院，優(yōu)化成功溉跃。再看看它的查詢計(jì)劃：
很好，已經(jīng)沒有 Using filesort 了
其他方法
1告抄、在網(wǎng)上看到有人這樣做
EXPLAIN SELECT
*
FROM
emp
WHERE
id >= ( SELECT id FROM emp ORDER BY id LIMIT 5000001, 1 )
LIMIT 10;
這個(gè)方法和上面講的索引覆蓋 是一樣的撰茎，先從索引中找到id，然后通過id查數(shù)據(jù)打洼。只不過上面是使用JOIN這里使用 >=龄糊。這樣就必須讓id是連續(xù)的了。最終查詢時(shí)間是 4-5 秒募疮。和索引覆蓋差不多
2绎签、使用Sphinx
