MySQL 深度分頁優化

理解總結：

分頁使用limit ，前提是要排序好的數據，這時候，就推薦使用帶索引的字段排序，因為索引是天然有序的，不需要像是無序的字段一樣，全表掃描，如果太大的話，還filesort ，利用文件排序，排序完成之后，才能分頁，很慢。但是，如果分頁過深的話，比如limit100萬，仍然無需要查詢到100萬數據，中間有大量的io操作（回表查詢其它字段），這時候考慮用上子查詢，先查到100萬位置的往后10條數據（直接用id主鍵查，因為沒有回表，直接索引查，所以很快），然后再關聯10條數據，取得完整的數據。

舉例：

1. 沒有查詢條件，沒有排序

耗時0.613s

select id,m_id, name, identity_no, address, create_time, modify_time  from t1 limit 1000000, 20;

加上主鍵排序

耗時0.41

**select** id,m_id, name, identity_no, address, create_time, modify_time  **from** t1 **order** **by** id limit 1000000, 20;

加上主鍵排序，使用了主鍵索引，天然有序，所以只讀取前n條數據，所以更快。

2. 帶排序-排序字段沒有索引

select id,m_id, name, identity_no, address, create_time, modify_time 
from t1 
order by create_time desc 
limit 10000, 20;

耗時2秒左右

select id,m_id, name, identity_no, address, create_time, modify_time
from t2
order by create_time desc
limit 10000, 20;

與t1基本相同，只是加了索引，耗時0.9s左右

對比：沒有索引的表，全表掃描，排序用到filesort 。有索引的話，可以利用索引排序，limit 的話，掃描的數據有少。

3. 排序字段有索引，但是分頁很深，從100w開始取20條。

select id,m_id, name, identity_no, address, create_time, modify_time 
from t2 
order by create_time desc 
limit 1000000, 20;

很慢，沒有走索引，因為MySQL優化器發現這條sql查詢超過一定的比例，就會自動轉成全表掃描。

加force index(idx)，強制走索引。有效果，但是不明顯。

結論：即使有索引，再深一點的分頁也會有問題，要避免

5. 解決方案

聯表子查詢

-- 改為：
SELECT   id, m_id, NAME, identity_no, address, create_time, modify_time 
FROM t2
JOIN ( SELECT id FROM t2 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );

變成0.7s；原來15s。

-- 在t1執行：
SELECT   id, m_id, NAME, identity_no, address, create_time, modify_time 
FROM t1
JOIN ( SELECT id FROM t1 ORDER BY create_time desc LIMIT 1000000, 20 ) x USING ( id );

這個也很快，2.8s。原來18s+

分析：

直接通過索引樹就能拿到查詢字段的值，索引快的原因是，子查詢查詢的方式，減少了回表查詢操作，進而減少了大量的回表IO，因為高效。

參考：https://juejin.cn/post/6985478936683610149