問(wèn)題
問(wèn)題起源于一個(gè)涉及到數(shù)據(jù)遍歷的腳本。
該腳本會(huì)對(duì)一個(gè)MySQL表中的數(shù)據(jù)進(jìn)行有條件的全表遍歷。SQL如下：
select * from table where update_time < CURDATE() order by update_time desc limit 100 offset 10000;
這樣寫看起來(lái)很正常系吭，但實(shí)際在數(shù)據(jù)量大了之后，使用起來(lái)開始出現(xiàn)問(wèn)題颗品，越來(lái)越慢肯尺，慢到不可接受，甚至影響其他的讀寫操作躯枢。
分析
原因就是limit offset這個(gè)語(yǔ)句则吟，并不如人們望文生義想的那樣，直接定位到第10000位然后取后面的100條記錄锄蹂。
而是令人發(fā)指的先一直一條一條讀取到10100條氓仲，然后再根據(jù)offset的設(shè)置，舍棄前10000條記錄，返回后面的100條記錄敬扛。
其實(shí)原因也好理解晰洒，MySQL的數(shù)據(jù)存儲(chǔ)并不是一個(gè)數(shù)組，可以直接根據(jù)下標(biāo)獲取第X位啥箭。即使給你搜索的字段加了索引谍珊，也只是使用該字段的值去建立一個(gè)新的二叉樹（索引二叉樹），來(lái)方便你快速找到數(shù)據(jù)位置急侥。
但是試想一下砌滞，當(dāng)你要在二叉樹中找到第n大的數(shù)時(shí)，你并不能像找一個(gè)具體的值一樣利用二叉樹的能力快速找到坏怪，因?yàn)槟阋膊恢烂總€(gè)節(jié)點(diǎn)的左子樹和右子樹分別有多少記錄贝润。
因此只能借用索引二叉樹是個(gè)B+樹這一特點(diǎn)，去利用葉子節(jié)點(diǎn)上的鏈表铝宵，去遍歷你要數(shù)的所有節(jié)點(diǎn)打掘。
這還不止。
MySQL不僅僅會(huì)讓你遍歷一遍索引值鹏秋，我們知道MySQL默認(rèn)的InnoDB引擎分為主鍵索引二叉樹和輔助索引二叉樹尊蚁，你使用其他自己定義的索引時(shí)，只是得到主鍵拼岳，真正取數(shù)據(jù)還得根據(jù)索引得到的主鍵枝誊，去主鍵索引二叉樹獲取到具體的數(shù)據(jù)况芒。
那此時(shí)惜纸，實(shí)際上你不僅在無(wú)效遍歷前10000個(gè)索引節(jié)點(diǎn)，MySQL還會(huì)讓你去根據(jù)遍歷到的這10000個(gè)無(wú)效索引節(jié)點(diǎn)去真正地查10000次數(shù)據(jù)绝骚，這就是10000次無(wú)效的數(shù)據(jù)查詢耐版。
為什么MySQL一定要讓你去查這些無(wú)效數(shù)據(jù)呢？因?yàn)镸ySQL的實(shí)現(xiàn)分為引擎層和數(shù)據(jù)層压汪，limit offset只能作用于引擎層返回的結(jié)果集粪牲，因此對(duì)引擎層來(lái)說(shuō)，他也不知道前10000個(gè)是會(huì)扔掉的數(shù)據(jù)止剖，只能先一股腦地往上傳腺阳。
更進(jìn)一步的，為什么MySQL不把limit offset直接傳給引擎層呢穿香？是因?yàn)椴樵冋Z(yǔ)句實(shí)際是由一個(gè)個(gè)算子組合起來(lái)的亭引，比如有選擇算子（where條件）、連接算子（join）皮获、投影算則（select的字段）焙蚓、數(shù)據(jù)源等，不同的算子有計(jì)算順序，導(dǎo)致底層的算子是不知道上層計(jì)算條件的购公。
總得來(lái)說(shuō)萌京，這種實(shí)現(xiàn)就導(dǎo)致，數(shù)據(jù)量越大宏浩，offset得越多知残，速度就會(huì)越慢，對(duì)MySQL的壓力就會(huì)越大绘闷。
解法
知道了問(wèn)題根源之后橡庞，就可以對(duì)應(yīng)地找解法。
解法1
比如我這里是要遍歷數(shù)據(jù)印蔗，既然用offset遍歷有性能問(wèn)題扒最，那就直接用主鍵id的范圍條件來(lái)縮小范圍。
select * from table where id > 10000 limit 100;
根據(jù)上面的分析我們可以指導(dǎo)华嘹，這樣做吧趣，一方面直接省去了一次查詢索引二叉樹后再查主鍵二叉樹的過(guò)程，而是直接就查主鍵二叉樹并獲取其節(jié)點(diǎn)上的數(shù)據(jù)耙厚。
另一方面强挫，用大于的條件，從而利用好二叉樹的特性薛躬，快速查找到數(shù)據(jù)的起始節(jié)點(diǎn)俯渤，然后獲取其后的100條記錄數(shù)據(jù)即可。
理解清楚型宝，這和offset找第100001條節(jié)點(diǎn)的實(shí)現(xiàn)機(jī)制有本質(zhì)區(qū)別八匠。
不過(guò)如果此時(shí)使用explain對(duì)SQL性能進(jìn)行檢查，會(huì)發(fā)現(xiàn)rows的數(shù)量等于id > 10000后剩余的總記錄數(shù)趴酣，而不是我們limit的100梨树，比如總共如果有15000條記錄，那此時(shí)的rows會(huì)是5000岖寞。
那這是否說(shuō)明sql需要遍歷id > 10000的所有記錄呢抡四？
不是的。explain得出的rows只是一個(gè)估算值仗谆。
實(shí)際上根據(jù)《MySQL EXPLAIN limits and errors》 一文所說(shuō)指巡，explain時(shí)，是不會(huì)考慮“LIMIT”的隶垮。
LIMIT is not taken into account while estimating number of rows
因此explain出的rows是id > 10000后剩余的總記錄數(shù)藻雪，是符合預(yù)期的，而實(shí)際執(zhí)行時(shí)岁疼，只會(huì)遍歷到limit的數(shù)量就會(huì)結(jié)束了阔涉。
這種做法在20W的數(shù)據(jù)量級(jí)下缆娃，經(jīng)過(guò)測(cè)試查詢性能可以提升43倍。
解法2
上面的做法基本只適用于遍歷的簡(jiǎn)單場(chǎng)景瑰排，從而可以直接使用主鍵去查詢贯要。
但大部分場(chǎng)景下，業(yè)務(wù)的查詢都是附帶條件的椭住，也就是說(shuō)必須要用到輔助的索引二叉樹崇渗。
前面說(shuō)了，如果用非主鍵的索引去遍歷京郑，會(huì)導(dǎo)致兩次對(duì)二叉樹的查詢操作：先查索引二叉樹找到節(jié)點(diǎn)的主鍵宅广，再查主鍵索引二叉樹取具體數(shù)據(jù)。
此時(shí)如果想實(shí)現(xiàn)一種條件下的翻頁(yè)效果些举，直觀可能會(huì)這樣寫SQL：
select * from table where update_time < CURDATE() limit 100 offset 10000;
此時(shí)MySQL經(jīng)歷的就是先根據(jù)條件找到10100條符合條件的記錄（經(jīng)過(guò)兩個(gè)二叉樹的查詢）跟狱，然后再拋棄前10000條。
那這里可以利用子查詢不會(huì)真正獲取數(shù)據(jù)的特性户魏，進(jìn)行優(yōu)化：
select * from table where id in (select id from table where update_time < CURDATE()) limit 100 offset 10000;
注意這里子查詢是根據(jù)輔助索引去查的驶臊，而主查詢只根據(jù)了主鍵去查。
在子查詢中并不會(huì)真正去訪問(wèn)主鍵索引二叉樹獲取數(shù)據(jù)叼丑，所以免去了10000次無(wú)效查詢关翎。
在子查詢獲取到id后，再用IN查詢?nèi)ピ谥麈I索引二叉樹上遍歷數(shù)據(jù)鸠信。
這種做法雖然也要查詢10000條無(wú)用的數(shù)據(jù)纵寝，但由于是直接使用主鍵索引，所以比直接查詢limit offset的做法會(huì)快兩倍左右星立。
解法3
用IN操作爽茴，對(duì)于量大的情況始終不太優(yōu)雅，因此還可以考慮用JOIN替代IN贞铣，自己JOIN自己：
select * from table as t1 inner join (select id from table where update_time < CURDATE() order by update_time desc limit 100 offset 10000) as t2 using (id);
這種做法經(jīng)過(guò)測(cè)試會(huì)比最原始的SQL快10倍闹啦。
這里還需要注意的是沮明，MySQL的JOIN有一個(gè)優(yōu)化點(diǎn)辕坝，即用小表做驅(qū)動(dòng)表去驅(qū)動(dòng)大表。
比如對(duì)于 t1 left join t2 的情況荐健，就建議把記錄數(shù)較小的表放在前面酱畅，前面的表示驅(qū)動(dòng)表，會(huì)掃描t1所有記錄然后再去t2查詢江场。
如果t1有M條記錄纺酸，t2 N條，使用t2的索引的情況下址否，時(shí)間復(fù)雜度是M * logN左右餐蔬，因此M的影響碎紊，也即t1的記錄數(shù)對(duì)時(shí)間影響更大。
不過(guò)這里由于使用的是INNER JOIN樊诺，MySQL對(duì)INNER JOIN會(huì)自動(dòng)使用小表仗考，因此問(wèn)題不大，實(shí)測(cè)下來(lái)耗時(shí)也相差無(wú)幾词爬。
更多解法
其實(shí)可以選擇的解法還有很多秃嗜，比如從業(yè)務(wù)層面限制要訪問(wèn)的數(shù)據(jù)，比如分表顿膨，比如其他奇詭的索引用法锅锨。
此外，這里介紹的解法恋沃，也更多地針對(duì)MySQL默認(rèn)使用的InnoDB引擎去做優(yōu)化必搞，在不同的數(shù)據(jù)庫(kù)存儲(chǔ)引擎下，可能會(huì)有其他更合適的解法囊咏。
關(guān)注我的公眾號(hào)【月亮與二進(jìn)制】顾画，鵝廠程序員的敲碼間隙，也能讀書觀影練劍寫字匆笤，分享給你我的世界
