背景
今年3月份時(shí)候，線上發(fā)生一次大事故察滑。公司主要后端服務(wù)器發(fā)生宕機(jī)打厘，所有接口超時(shí)。宕機(jī)半小時(shí)后贺辰，又自動(dòng)恢復(fù)正常户盯。但是過(guò)了2小時(shí)，又再次發(fā)生宕機(jī)饲化。
通過(guò)接口日志莽鸭，發(fā)現(xiàn)MySQL數(shù)據(jù)庫(kù)無(wú)法響應(yīng)服務(wù)器。在阿里云的技術(shù)支持的幫助下吃靠，發(fā)現(xiàn)了MySQL數(shù)據(jù)庫(kù)中存在大量慢查詢硫眨，導(dǎo)致CPU負(fù)載過(guò)高。最后巢块，根據(jù)慢查詢?nèi)罩窘父螅ㄎ坏搅顺鰡?wèn)題的SQL和業(yè)務(wù)接口。
業(yè)務(wù)接口是一個(gè)分頁(yè)接口族奢，莫名被刷到7000多頁(yè)姥闭，偏移量（offset）高達(dá)20w多。每當(dāng)這條SQL執(zhí)行時(shí)越走，數(shù)據(jù)庫(kù)CPU直接打滿泣栈。查詢時(shí)間超過(guò)1分鐘才有響應(yīng)。由于慢查詢導(dǎo)致數(shù)據(jù)庫(kù)CPU使用率爆滿弥姻，其他業(yè)務(wù)的數(shù)據(jù)庫(kù)請(qǐng)求無(wú)法得到及時(shí)響應(yīng)南片，接口超時(shí)。最后庭敦，拖垮主服務(wù)器疼进。
limit分頁(yè)查詢性能問(wèn)題
MySQL Limit 語(yǔ)法格式：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
分頁(yè)查詢時(shí)，我們會(huì)在 LIMIT 后面?zhèn)鲀蓚€(gè)參數(shù)秧廉，一個(gè)是偏移量（offset）伞广，一個(gè)是獲取的條數(shù)（limit）。當(dāng)偏移量很小時(shí)疼电，查詢速度很快嚼锄，但是當(dāng) offset 很大時(shí)，查詢速度就會(huì)變慢蔽豺。
下面我們以一個(gè)實(shí)例区丑，講解一下分頁(yè)性能問(wèn)題。假設(shè)有一張 300w 條數(shù)據(jù)的表修陡，對(duì)其進(jìn)行分頁(yè)查詢沧侥。
select * from tbl_works limit 1, 10 // 32.8ms
select * from tbl_works limit 10, 10 // 34.2ms
select * from tbl_works limit 100, 10 // 35.4ms
select * from tbl_works limit 1000, 10 // 39.6ms
select * from tbl_works limit 10000, 10 // 5660ms
select * from tbl_works limit 100000, 10 // 61.4 秒
select * from tbl_works limit 1000000, 10 // 273 秒
可以看到，隨著偏移量（offset）的增加魄鸦，查詢時(shí)間變得越長(zhǎng)宴杀。對(duì)于普通的業(yè)務(wù)而言，超過(guò)1秒的查詢是絕對(duì)不可以忍受的拾因。上例中旺罢，當(dāng)偏移的起始位置超過(guò)10萬(wàn)時(shí)，分頁(yè)查詢的時(shí)間超過(guò)61秒绢记。當(dāng)偏移量超過(guò)100萬(wàn)時(shí)扁达，查詢時(shí)間竟然長(zhǎng)達(dá)273秒。
從上例中庭惜，我們可以總結(jié)出：LIMIT分頁(yè)查詢的時(shí)間與偏移量值成正比罩驻。當(dāng)偏移量越大時(shí)，查詢時(shí)間越長(zhǎng)护赊。這種情況惠遏，會(huì)隨著業(yè)務(wù)的增加，數(shù)據(jù)的增多骏啰，會(huì)越發(fā)的明顯节吮。那么，如何優(yōu)化這種情況呢判耕？答案是透绩，覆蓋索引。
優(yōu)化方法
對(duì)于LIMIT分頁(yè)查詢的性能優(yōu)化，主要思路是利用覆蓋索引字段定位數(shù)據(jù)帚豪，然后再取出內(nèi)容碳竟。
不使用覆蓋索引，查詢耗時(shí)情況：
SELECT * FROM `tbl_works`
WHERE `status`=1
LIMIT 100000, 10 // 78.3 秒
1）子查詢分頁(yè)方式
SELECT * FROM tbl_works
WHERE id >= (SELECT id FROM tbl_works limit 100000, 1)
LIMIT 20 // 54ms
子查詢分頁(yè)方式狸臣，首先通過(guò)子查詢和覆蓋索引定位到起始位置ID莹桅，然后再取所需條數(shù)的數(shù)據(jù)。
缺點(diǎn)是烛亦，不適用于結(jié)果集不以ID連續(xù)自增的分頁(yè)場(chǎng)景诈泼。在復(fù)雜分頁(yè)場(chǎng)景，往往需要通過(guò)過(guò)濾條件煤禽，篩選到符合條件的ID铐达，此時(shí)的ID是離散且不連續(xù)的。如果使用上述的方式檬果，并不能篩選出目標(biāo)數(shù)據(jù)瓮孙。
當(dāng)然，我們也可以對(duì)此方法做一些改進(jìn)汁汗，首先利用子查詢獲取目標(biāo)分頁(yè)的 ids衷畦，然后再根據(jù) ids 獲取內(nèi)容。
根據(jù)直覺(jué)將SQL改造如下：
SELECT * FROM tbl_works
WHERE id IN (SELECT id FROM tbl_works limit 100000, 10)
// 錯(cuò)誤信息：
// This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
然而知牌，并不盡人意祈争。我們得到一個(gè)錯(cuò)誤提示。
錯(cuò)誤信息的含義是角寸，子查詢不能有 limit操作菩混。于是，我們對(duì)SQL進(jìn)行了改造扁藕，對(duì)子查詢包了一層：
SELECT t1.* FROM tbl_works t1
WHERE t1.id in (SELECT t2.id from (SELECT id FROM tbl_works limit 100000, 10) as t2) // 53.9ms
執(zhí)行成功沮峡，且查詢效率很高。但是亿柑，這種寫(xiě)法非常繁瑣邢疙。我們可以使用下面的 join 分頁(yè)方式，達(dá)到相同的優(yōu)化效果望薄。實(shí)際上疟游，兩者的原理是相同的。
2）join 分頁(yè)方式
SELECT * FROM tbl_works t1
JOIN (SELECT id from tbl_works WHERE status=1
limit 100000, 10) t2
ON t1.id = t2.id // 53.6 ms
這條SQL的含義是痕支，通過(guò)自連接與join定位到目標(biāo) ids颁虐，然后再將數(shù)據(jù)取出。在定位目標(biāo) ids時(shí)卧须，由于 SELECT的元素只有主鍵 ID另绩，且status 存在索引儒陨，因此MySQL只需在索引中，就能定位到目標(biāo) ids笋籽，不用在數(shù)據(jù)文件上進(jìn)行查找蹦漠。因而，查詢效率非常高干签。
覆蓋索引（Cover Index）
如果索引包含所有滿足查詢需要的數(shù)據(jù)的索引成為覆蓋索引(Covering Index)津辩，也就是平時(shí)所說(shuō)的不需要回表操作。
簡(jiǎn)單的說(shuō)容劳，覆蓋索引覆蓋所有需要查詢的字段（即，大于或等于所查詢的字段）闸度。MySQL可以通過(guò)索引獲取查詢數(shù)據(jù)竭贩，因而不需要讀取數(shù)據(jù)行。
覆蓋索引的好處：
- 索引大小遠(yuǎn)小于數(shù)據(jù)行大小莺禁。因而留量，如果只讀取索引，則能極大減少對(duì)數(shù)據(jù)訪問(wèn)量哟冬。
- 索引按順序儲(chǔ)存楼熄。對(duì)于IO密集型的范圍查詢會(huì)比隨機(jī)從磁盤(pán)讀取每一行數(shù)據(jù)的IO要少。
- 避免對(duì)主鍵索引的二次查詢浩峡。二級(jí)索引的葉子節(jié)點(diǎn)包含了主鍵的值可岂，如果二級(jí)索引包含所要查詢的值，則能避免二次查詢主鍵索引（聚簇索引翰灾，聚簇索引既存儲(chǔ)了索引缕粹，也儲(chǔ)存了值）。
總結(jié)
通過(guò)利用覆蓋索引纸淮，能極大的優(yōu)化了Limit分頁(yè)查詢的效率平斩。在真正的實(shí)踐中，除了使用覆蓋索引咽块，優(yōu)化查詢速度外绘面，我們還可以使用 Redis 緩存，將熱點(diǎn)數(shù)據(jù)進(jìn)行緩存儲(chǔ)存侈沪。
背景描述的事故揭璃，我們考慮了時(shí)間成本和業(yè)務(wù)復(fù)雜度后，最后采取的是限制分頁(yè)和增加緩存峭竣。所謂的限制分頁(yè)塘辅，即在不影響閱讀體驗(yàn)的前提下，只允許用戶可以查看前幾千條的數(shù)據(jù)皆撩。經(jīng)測(cè)驗(yàn)扣墩，偏移量較小時(shí)的查詢效率較令人滿意哲银，查詢效率接近使用覆蓋索引查詢的速度。
