前言

分頁是一個很普通的功能，只要是個後端開發就要寫分頁，那為什麼要分頁？

• 從業務上來講，即使系統返回所有數據，用户絕大多數情況下是不會看後面的數據的。
• 技術上，因為要考慮取數據的成本，目標服務器磁盤、內存、網絡帶寬，以及請求發起方自身是否能承受大批量數據。

MySQL 分頁語法

 select * from table limit 0, 20

思考: 使用分頁，上面提到的第 2 點，這些成本真的能降低嗎？

建表，造數據

 CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `name` varchar(255) DEFAULT NULL,
  `balance` int DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3571068 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=REDUNDANT 

-- 先初始化數據
DELIMITER //  
CREATE PROCEDURE InsertRandomAccounts(IN num_rows INT)  
BEGIN  
  DECLARE i INT DEFAULT 0;  
  WHILE i < num_rows DO  
    SET @name = CONCAT('Account', LPAD(i, 6, '0'));  
    SET @balance = FLOOR(RAND() * 10000); -- 隨機生成一個0到9999之間的餘額  
    SET @create_time = NOW() - INTERVAL FLOOR(RAND() * 3650) DAY; -- 隨機生成一個過去3650天內的創建時間  
    SET @update_time = NOW(); -- 更新時間設置為當前時間  
      
    INSERT INTO account (name, balance, create_time, update_time)  
    VALUES (@name, @balance, @create_time, @update_time);  
      
    SET i = i + 1;  
  END WHILE;  
END //  
DELIMITER ;  
  
-- 調用存儲過程插入兩百萬條數據  （插入數據前可先將索引刪除來提升效率）
CALL InsertRandomAccounts(2000000);  
  
-- 刪除存儲過程（如果你不再需要它）  
DROP PROCEDURE IF EXISTS InsertRandomAccounts;

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

select id,name,balance from account a limit 1000000,20;

100w 之後的數據，耗時：

0.31 sec

加主鍵排序

select id,name,balance from account a order by id limit 1000000,20;

耗時：有所降低

0.22 sec

執行計劃對比：
1:

2:

可以看到帶主鍵排序使用了主鍵索引，且只讀取了需要的前 n 條數據，所以快。

因此, 結論 1：即使業務上看起來沒有任何條件還不需要排序，也加上 order by 主鍵。

這裏其實有另一個問題：如果不帶排序條件，MySQL 默認是什麼排序？

通常認為是主鍵，但通過查資料發現並不一定，這裏有個物理順序和邏輯順序的區別，如：刪除原有數據後再插入複用舊 id 的數據，可能會由於存放在不同頁上造成物理順序與邏輯順序不一致，此時可以通過優化表改善：optimize table table_name。

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

select * from account order by update_time desc limit 1000,20;

執行時間：
0.98s

排序字段有索引：（注：上條SQL執行前已將idx_update_time索引刪除）

select * from account order by update_time desc limit 1000,20;

執行時間：
0.04s

執行計劃對比：

1:

2：

可以看到有索引的表，直接走索引取前 n 條數據，不需要全表掃描，也不會用到 filesor。

結論 2：給常用字段加索引，包括排序字段。

新的問題：
以上 2 個場景看似已可以解決大部分分頁問題，但：

排序字段有索引就一定快嗎？1k的時候速度較快，換成查詢 1w 之後的數據呢？
如果當前表已經有多個索引，不適合再添加索引了呢？

3： 排序字段有索引，但分頁再深一點： 從 100w 開始取 20 條

select * from account order by update_time desc limit 1000000,20;

耗時：非常慢
4.1s

執行計劃：

通過執行計劃發現，並沒有走索引，為什麼沒有走索引？
因為 mysql 優化器發現這條 sql 查詢行數超過一定比例（據説是 30%，但測試下來並不完全是）就會自動轉換為全表掃描，能不能強制走索引呢？
可以的，加 force index(idx)。
4：強制索引

select * from account force index(idx_update_time) order by update_time desc limit 1000000,20;

強制索引後的執行計劃：

看下執行時間：
9.52s
反而更慢了，看來MySQL內部做的優化還是很充分的，數據量大的時候直接不走索引了

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

以上嘗試結果都沒有很好的解決深分頁性能問題，那是否有更好的解決方案？
有！

5： 帶 last\_條件查詢

select *from t2
where id > #{last_id},update_time > #{last_update_time}
order by update desc
limit 0, 20;

性能與正常淺分頁沒差別，但前提是 last_*字段有索引。

同時，該方案受使用場景限制，如跳頁、多排序字段等，last_*都將無法使用。

推薦使用場景：沒有頁碼的應用，如：滑動加載下一頁、只有上下頁按鈕等。

6：聯表子查詢

將場景 4 中強制索的 sql 改為子查詢，先測試排序字段有索引的 t2 表。

select * from account a join (select id from account order by update_time desc limit 1000000,20) b on a.id = b.id;

執行時間：0.25 s
效果明顯。

刪除idx_update_time索引
sql 複製代碼-- 在 執行：

select * from account a join (select id from account order by update_time desc limit 1000000,20) b on a.id = b.id;

執行時間：1.45s
效果明顯。（原 sql 執行時長：0.25s）

改成子查詢關聯後，無論有沒有索引都節省了大量時間，下面來分析下原因。

這兩條關聯查詢的執行計劃對比差距還是在於子查詢是否使用索引排序，1 使用索引所以快。

對比子查詢與非子查詢執行計劃：
1、

2、

區別：
全表掃描與強制索引區別上面已經講過，那強制索引跟子查詢區別是什麼？

看起來就只是多了一個 Using index，那什麼是 Using index 呢?

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

區別：

乍一看發現這兩條查詢沒有區別，不僅沒區別，子查詢還比直接查詢複雜了，但它反而快了，為什麼？

這裏的關鍵其實就在於 Using filesort。
Using filesort 時，mysql 的兩種排序策略。

一、單路排序

根據條件將所有查詢字段數據取出到 sort buffer 緩衝區。

緩衝區滿了根據排序字段執行一次排序（快排）把然後把排序後的數據寫到臨時文件。

將所有數據取出排序後，對所有臨時文件按順序做合併（歸併排序）再寫回到文件，直到最後所有文件合併完成。

從臨時文件中讀取滿足分頁條件所需數據返回，如果首次歸併就可以取到分頁數據則直接返回（淺分頁）。

雙路排序

根據查詢條件將 row_id 和排序字段取出放到 sort buffer （區別 1）。

緩衝區滿了根據排序字段執行一次排序（快排）把然後把排序後的數據寫到臨時文件。

將所有數據取出排序後，對所有臨時文件按順序做合併（歸併排序）再寫回到文件，直到最後所有文件合併完成。

從臨時文件中讀取滿足分頁條件的 row_id，再通過 row_id 讀取對應行數據返回（區別 2）。

MySQL 在 4.1 之前都是雙路排序，之後優化改為滿足條件默認單路排序，條件為：查詢字段數據大小小於 max_length_for_sort_data 值，但改到最小值測試也沒有看到變化。
因此：子查詢方式快的原因可以確定了，子查詢只取了 create_time+id 到 sort buffer（相當於雙路排序的做法）, 相比直接查詢，省去了絕大部分字段，減少大量臨時文件 IO 操作，因此提高查詢效率。

關注VX-公眾號，回覆 J01 獲取後端學習面試資源