在MySQL中如何使用覆蓋索引優化limit分頁查詢

背景

今年3月份時候，線上發生一次大事故。公司主要后端服務器發生宕機，所有接口超時。宕機半小時后，又自動恢復正常。但是過了2小時，又再次發生宕機。

通過接口日志，發現Mysql數據庫無法響應服務器。在阿里云的技術支持的幫助下，發現了Mysql數據庫中存在大量慢查詢，導致CPU負載過高。最后，根據慢查詢日志，定位到了出問題的SQL和業務接口。

業務接口是一個分頁接口，莫名被刷到7000多頁，偏移量（offset）高達20w多。每當這條SQL執行時，數據庫CPU直接打滿。查詢時間超過1分鐘才有響應。由于慢查詢導致數據庫CPU使用率爆滿，其他業務的數據庫請求無法得到及時響應，接口超時。最后，拖垮主服務器。

limit分頁查詢性能問題

MySQL Limit 語法格式：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

分頁查詢時，我們會在 LIMIT 后面傳兩個參數，一個是偏移量（offset），一個是獲取的條數（limit）。當偏移量很小時，查詢速度很快，但是當 offset 很大時，查詢速度就會變慢。

下面我們以一個實例，講解一下分頁性能問題。假設有一張 300w 條數據的表，對其進行分頁查詢。

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）的增加，查詢時間變得越長。對于普通的業務而言，超過1秒的查詢是絕對不可以忍受的。上例中，當偏移的起始位置超過10萬時，分頁查詢的時間超過61秒。當偏移量超過100萬時，查詢時間竟然長達273秒。

從上例中，我們可以總結出：LIMIT分頁查詢的時間與偏移量值成正比。當偏移量越大時，查詢時間越長。這種情況，會隨著業務的增加，數據的增多，會越發的明顯。那么，如何優化這種情況呢？答案是，覆蓋索引。

優化方法

對于LIMIT分頁查詢的性能優化，主要思路是利用覆蓋索引字段定位數據，然后再取出內容。

不使用覆蓋索引，查詢耗時情況：

SELECT * FROM `tbl_works`

WHERE `status`=1

LIMIT 100000, 10 // 78.3 秒

1）子查詢分頁方式

SELECT * FROM tbl_works

WHERE id >= (SELECT id FROM tbl_works limit 100000, 1)

LIMIT 20 // 54ms

子查詢分頁方式，首先通過子查詢和覆蓋索引定位到起始位置ID，然后再取所需條數的數據。

缺點是，不適用于結果集不以ID連續自增的分頁場景。在復雜分頁場景，往往需要通過過濾條件，篩選到符合條件的ID，此時的ID是離散且不連續的。如果使用上述的方式，并不能篩選出目標數據。

當然，我們也可以對此方法做一些改進，首先利用子查詢獲取目標分頁的 ids，然后再根據 ids 獲取內容。

根據直覺將SQL改造如下：

SELECT * FROM tbl_works

WHERE id IN (SELECT id FROM tbl_works limit 100000, 10)

// 錯誤信息： // This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

然而，并不盡人意。我們得到一個錯誤提示。

錯誤信息的含義是，子查詢不能有 limit操作。于是，我們對SQL進行了改造，對子查詢包了一層：

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

執行成功，且查詢效率很高。但是，這種寫法非常繁瑣。我們可以使用下面的 join 分頁方式，達到相同的優化效果。實際上，兩者的原理是相同的。

2）join 分頁方式

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的含義是，通過自連接與join定位到目標 ids，然后再將數據取出。在定位目標 ids時，由于 SELECT的元素只有主鍵 ID，且status 存在索引，因此MySQL只需在索引中，就能定位到目標 ids，不用在數據文件上進行查找。因而，查詢效率非常高。

覆蓋索引（Cover Index）

如果索引包含所有滿足查詢需要的數據的索引成為覆蓋索引(Covering Index)，也就是平時所說的不需要回表操作。

簡單的說，覆蓋索引覆蓋所有需要查詢的字段（即，大于或等于所查詢的字段）。MySQL可以通過索引獲取查詢數據，因而不需要讀取數據行。

覆蓋索引的好處：

索引大小遠小于數據行大小。因而，如果只讀取索引，則能極大減少對數據訪問量。

索引按順序儲存。對于IO密集型的范圍查詢會比隨機從磁盤讀取每一行數據的IO要少。

避免對主鍵索引的二次查詢。二級索引的葉子節點包含了主鍵的值，如果二級索引包含所要查詢的值，則能避免二次查詢主鍵索引（聚簇索引，聚簇索引既存儲了索引，也儲存了值）。

總結

通過利用覆蓋索引，能極大的優化了Limit分頁查詢的效率。在真正的實踐中，除了使用覆蓋索引，優化查詢速度外，我們還可以使用 Redis 緩存，將熱點數據進行緩存儲存。

背景描述的事故，我們考慮了時間成本和業務復雜度后，最后采取的是限制分頁和增加緩存。所謂的限制分頁，即在不影響閱讀體驗的前提下，只允許用戶可以查看前幾千條的數據。經測驗，偏移量較小時的查詢效率較令人滿意，查詢效率接近使用覆蓋索引查詢的速度。

參考資料

mysql使用limit分頁優化方案

mysql高效索引之覆蓋索引

MySQL的limit用法和分頁查詢的性能分析及優化

mysql分頁查詢總結

高性能的MySQL（5）索引策略-覆蓋索引與索引排序

理解InnoDB的聚集索引（譯）

聚簇索引和二級索引

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。