深分頁怎麼導致索引失效了？提供6種優化的方案！

上篇文章說到索引失效的幾種規則，其中就有包括 深分頁回表太多導致索引失效 的場景

本篇文章來聊聊深分頁場景中的問題並提供幾種優化方案，以下是本篇文章的思維導圖：

深分頁問題

那麼什麼是深分頁問題呢？

在MySQL的limit中:limit 100,10MySQL會根據查詢條件去存儲引擎層找到前110條記錄，然後在server層丟棄前100條記錄取最後10條

這樣先掃描完再丟棄的記錄相當於白找，深分頁問題指的就是這種場景（當limit的偏移量過大時會導致性能開銷）

-- 0.04s
select SQL_NO_CACHE * from student where age = 18 limit 10;
-- 4.049s
select SQL_NO_CACHE * from student where age = 18 limit 5000,10;

該表中存在二級索引：idx_age_name 是學生年齡age和學生名稱name的聯合索引（該二級索引上隻存在字段age,name,id）

這條SQL會基於MySQL最優成本選擇使用 idx_age_name 或者 聚簇索引

假設它使用二級索引 idx_age_name，我們來分析一下使用二級索引的流程：

1. 它會先根據二級索引定位到第一條滿足age=18的記錄
2. 由於二級索引上的記錄沒有完整字段，因此會回表查詢聚簇索引獲取完整字段
3. 將結果返回給server層，並根據這條記錄的next找到下一條記錄

循環1-3的過程，在二級索引上找到滿足查詢條件age=18的前5010條記錄(或者直到不滿足age=18)，然後舍棄前5000條，取最後10條

在這個過程中：先查二級索引接著回表獲取完整記錄然後返回給server層再查下一條記錄

由於二級索引是聯合索引，當age相等時，主鍵id不一定是有序的，這樣回表就會產生隨機IO

當深分頁場景使用二級索引時會涉及回表（隨機IO），如果偏移量太大回表的數據量也會很大，MySQL認為成本太大不偏向使用二級索引從而導致索引失效

那麼該如何優化深分頁這樣的問題呢？從這裡分析可以得到從兩個方面進行優化：

1. 讓它不要回表，避免回表的開銷
2. 讓它不要舍棄前XX條記錄，避免白查詢

接下來結合不同的方法進行討論

覆蓋索引避免回表

當業務上允許時可以使用覆蓋索引避免回表，查完二級索引就交給server層再去查下一條記錄

-- 4.049s
select SQL_NO_CACHE * 
from student 
where age = 18 limit 5000,10;
-- 0.034s
select SQL_NO_CACHE id,age,student_name 
from student 
where age = 18 limit 5000,10;

雖然說覆蓋索引依舊會舍棄前XX條記錄，但沒有回表的開銷已經快了不少

但如果業務上不允許還是要查較多在聚簇索引上的字段，又或者偏移量還是太大的情況，我們還是需要使用其他的方案

遊標分頁

為了避免limit中的偏移量，可以自己來存儲該偏移量

我們可以使用上次查詢的最大值來當作這次的查詢條件（遊標分頁）

--  12.899s
select * from seat 
where seat_code = 'caicaiseat' 
limit 99990,10; -- 最後一條記錄 seat_id = 988380
select * from seat 
where seat_code = 'caicaiseat' limit 100000,10;
--  0.189s  滿足查詢條件情況下主鍵有序 可以使用上一次記錄
select * from seat 
where seat_code = 'caicaiseat' and seat_id > 上次查詢最大記錄 
limit 10;
select * from seat 
where seat_code = 'caicaiseat' and seat_id > 988380 
limit 10;

其中limit 100000,10 與 seat_id > 988380 limit 10 查詢結果相同，但是這種做法是有前提條件的

前提條件

1. 需要一個列來記錄上一次查詢的最大值（通常是主鍵，後面就直接用主鍵表達，大傢明白就好），並且滿足查詢條件時主鍵需要是有序的
2. 因為本次查詢需要依賴上一次查詢的主鍵最大值，因此分頁查詢隻能是連續的，不能進行跳頁（比如查完第一頁直接查第一百頁）

在上面案例SQL中會使用二級索引 idx_seat_code (seat_code,seat_id)，當使用二級索引時，由於seat_code是等值查詢，滿足條件時id是有序的

如果是原來的SQL使用這種做法會導致查詢出來的結果不一致，因為在二級索引上當age = 18時主鍵不一定有序

select SQL_NO_CACHE * from student 
where age = 18 and id > 6726705  
limit 10;

亂序該如何解決呢？當然是排序呀！

select SQL_NO_CACHE * from student 
where age = 18 and id > 上次查詢最大記錄 
order by id 
limit 10;

但是排序又會帶來新的問題：可能更偏向使用聚簇索引（全表掃描），如果使用二級索引還需要對id排序（臨時表），具體還要查看執行計劃分析

遊標分頁排序下的SQL和原始limitSQL結果是不同的，因為原始的id無序，但它們都滿足（業務）查詢條件age=18，隻是做分頁時順序不同

-- 原始limit SQL
select SQL_NO_CACHE * from student 
where age = 18 
limit 5000,10;
-- 遊標分頁
select SQL_NO_CACHE * from student 
where age = 18 and id > 上次查詢最大記錄 
order by id 
limit 10;

使用遊標分頁時需要使用主鍵記錄每次查詢的最大值，並且需要滿足查詢條件後主鍵值是有序的，隻能在連續分頁的場景使用，不能跳頁，比如滑動分頁（一邊滑動一邊分頁）

子查詢定位

另一種避免limit 偏移量太大的方式是通過子查詢定位到第一條記錄

子查詢也是類似於遊標分頁，定位第一條記錄就相當於先找到偏移量

select * from seat 
where seat_code = 'caicaiseat' 
limit 100000,10;
-- 0.068s 通過二級索引先定位到主鍵值
select * from seat 
where seat_code = 'caicaiseat'
and seat_id >= 
    (select seat_id from seat 
    where seat_code = 'caicaiseat' 
    limit 100000,1) 
limit 10;

使用子查詢 select seat_id from seat where seat_code = 'caicaiseat' limit 100000,1 定位到第一條記錄的主鍵值

然後再通過 seat_id >= 定位到的第一條記錄 limit 10 查出需要的10條記錄

子查詢定位的方案也有使用前提：

1. 子查詢可以使用二級索引快速定位（不用回表）
2. 滿足查詢條件後主鍵需要有序（因為使用 seat_id >= ）

子查詢定位支持跳頁，但需要使用二級索引定位且滿足條件後主鍵值有序

in 子查詢

在遊標分頁與子查詢使用二級索引定位的場景中總是需要記錄偏移量的列（主鍵）有序，遇到無序的場景還需要排序，增加性能開銷

有沒有更好的辦法避免排序呢？

id >= X 需要主鍵有序，但是 id in (x,x...) 似乎就不需要主鍵有序了呀

使用子查詢常用的搭配in，因為分頁時子查詢數據量也不大，可以使用in來進行查詢

select SQL_NO_CACHE * from student 
where age = 18 
and id in 
    (select id from student 
    where age = 18 
    limit 5000,10);

但是MySQL好像不支持in與limit同時使用，這樣使用會報錯

1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

於是隻能把子查詢的結果封裝成臨時表

select SQL_NO_CACHE * 
from student 
where age = 18 
and id in 
    (select id 
    from (select id 
          from student 
          where age = 18 
          limit 5000,10) 
    tmp);

in 子查詢的方案即支持跳頁又不用排序，雖然會生成臨時表但數據量較少

聯表查詢 子查詢

熟悉MySQL中in優化（半連接）的同學，一定能夠知道in與內連接的奇妙關系

在某些符合條件的場景下，in會被優化為內連接

（感興趣或者不熟悉的同學可以看這篇文章MySQL半連接優化）

當然我們也可以手動編寫內連接的聯表查詢來讓其進行關聯

-- 4.049s 原始
select SQL_NO_CACHE * 
from student 
where age = 18 
limit 5000,10;
-- 0.033s 聯表   子查詢
select SQL_NO_CACHE * 
from student s 
inner join (select id tmp_id 
            from student 
            where age = 18 
            limit 5000,10) tmp
on s.id = tmp_id

聯表查詢的思路與in相同，都能夠支持跳頁和主鍵無序

需求溝通

其實這幾種方案要麼實現不回表，要麼實現不用偏移量，在解決這類問題時其實要先與需求進行溝通：

1、是否有必要查這麼多記錄，記錄太多時能否提醒用戶減少查詢范圍（比如起止時間）

2、如果不能修改查詢條件的情況下且數據量確實大（比如查18歲學生還有很多分頁），考慮是否能讓用戶翻頁翻到這麼後呢？

...

總結

本文描述發生深分頁問題的原因以及各種解決方案，總結如下：

深分頁問題是因為MySQL limit時，會先把記錄查詢出來，再舍棄前XX條記錄所導致的

不同的方案適合不同的業務場景，在收到數據量較大的分頁需求時先進行溝通，無法避免時再做優化

如果需要查詢的列在二級索引上都存在，可以使用二級索引（覆蓋索引）避免回表

如果滿足查詢條件後主鍵有序並且業務上不用跳頁那麼可以選擇遊標分頁

如果滿足查詢條件後主鍵有序並且業務上需要支持跳頁，可以選擇子查詢

如果滿足查詢條件後主鍵（記錄偏移量的列）無序，那麼可以選擇in或聯表的方案

作者：菜菜的後端私房菜
鏈接：
https://juejin.cn/post/7329194915682680870