排序、分組與深分頁：翻到第五千頁把資料庫拖垮

一支後台列表 API，回傳 20 筆資料、8KB 的 JSON，跑了 6 秒。從結果完全看不出問題——資料量這麼小，能慢到哪裡去？

因為排序的成本與回傳的資料量無關，它與「必須先看過多少列」有關。

SELECT * FROM orders WHERE status = 1
ORDER BY amount DESC LIMIT 20;

-- 符合 status = 1 的有 80 萬列
-- amount 上沒有索引，所以要「全部拿出來排一次」才知道前 20 名是誰
-- 回傳 20 列，實際處理 800,000 列

三個要在 EXPLAIN 裡找的字

• Using filesort：需要額外排序（ch01 提過，這章拆開講）。
• Using temporary：需要建暫存表。
• 大的 rows 配小的 LIMIT：這個組合本身就是訊號——它要看很多列，才能回答一個很小的問題。

先建立一個判斷框架

這一章所有的優化，本質上都在做同一件事之一：

三種手段對應三個層次的改動成本：改索引、改 SQL、改產品行為。而它們的效果剛好也是這個順序的反過來——改動越大，效果越徹底。

Using filesort 這個名字造成很多誤會。它不代表一定會寫檔案，它只代表「無法從索引直接取得順序，必須自己排一次」。

兩種排序方式

• 單路排序（single-pass）：把 SELECT 需要的所有欄位都放進 sort buffer，排完直接輸出。
• 雙路排序（two-pass）：只把排序欄位 + 主鍵放進 buffer，排完之後再依序回表取完整資料。

取捨很直接：單路只讀一次資料但佔記憶體多；雙路省記憶體但要回表第二次（隨機 I/O）。MySQL 會依「每列有多寬」自行選擇——所以 SELECT * 會把這個決策推向雙路，多付一次回表成本。這是「不要 SELECT *」這句老生常談，在排序場景下的具體理由。

sort_buffer_size 與磁碟合併

SELECT @@sort_buffer_size;   -- 預設 256KB（每個連線每次排序各一份）

裝不下的時候，MySQL 走外部歸併排序：把資料切成一塊塊，每塊排好寫成暫存檔案，最後多路合併。

怎麼確認它落磁碟了

SHOW STATUS LIKE 'Sort_merge_passes';
-- 這個數字持續增加 = 有排序在走磁碟合併

SHOW STATUS LIKE 'Sort_rows';   -- 排過的總列數，跟回傳列數對照就知道浪費多少

ORDER BY ... LIMIT 的優化

MySQL 對「排序後只要前 N 列」有一個專門優化：用大小為 N 的優先佇列（堆），只保留當前最好的 N 列，其餘直接丟掉。這樣即使有 80 萬列，記憶體也只需要裝 20 列。

• 好處：小 LIMIT 幾乎不會落磁碟。
• 但它仍然要「看過」全部 80 萬列——CPU 成本與 I/O 成本都在，只是省下記憶體。
• N 很大時（深分頁）這個優化就失效了——`LIMIT 100000, 10` 的堆要能裝 100,010 列。這正是下面深分頁那節的伏筆。

最好的排序是不排序。B+Tree 的葉節點本來就是有序的，只要索引的順序剛好就是你要的順序，資料庫直接照著讀就好——Extra 裡的 Using filesort 會消失。

成立的條件

KEY idx_m_s_c (merchant_id, status, created_at)

-- ✅ 免排序：等值條件鎖定前兩欄，第三欄在索引裡天生有序
WHERE merchant_id = 42 AND status = 1 ORDER BY created_at DESC

-- ❌ 要排序：中間的 status 沒有被等值鎖定，created_at 在索引裡不是全域有序的
WHERE merchant_id = 42 ORDER BY created_at DESC

-- ❌ 要排序：排序欄位不在同一個索引裡
WHERE merchant_id = 42 AND status = 1 ORDER BY amount DESC

規則可以濃縮成一句：ORDER BY 的欄位，必須緊接在「被等值條件鎖定的前綴」之後。這與 ch01 的最左前綴是同一條規則的兩面——那裡看的是能不能定位，這裡看的是能不能提供順序。

混合方向的排序

ORDER BY created_at DESC, id ASC   -- 一升一降

MySQL 8.0 之前這種寫法一定要 filesort。8.0 引進降序索引之後可以這樣建：

KEY idx_c_i (created_at DESC, id ASC)

注意 8.0 之前的語法允許你寫 DESC 但會默默忽略——所以看到舊 schema 裡有 DESC 不代表它真的是降序索引，要用 SHOW CREATE TABLE 在 8.0 上確認。

GROUP BY：MySQL 8.0 的行為變更

反過來說，這個變更也是好事：5.7 那個隱式排序是白付的成本。8.0 之後如果你真的不需要順序，可以少排一次。

loose index scan：連分組都免掉

Extra 出現 Using index for group-by 是最好的情況：直接在索引上跳著讀，連掃描都省了。條件比較苛刻——單表、GROUP BY 的欄位是索引前綴、聚合函數限於 MIN／MAX 這類。

-- idx_m_c (merchant_id, created_at)
SELECT merchant_id, MIN(created_at) FROM orders GROUP BY merchant_id;
-- Extra: Using index for group-by
-- 每個 merchant 只讀第一筆，不用掃完它所有訂單

Using temporary 比 Using filesort 更貴，因為它要先建一張表、把中間結果寫進去、可能再排一次。

什麼時候會產生

• GROUP BY 的欄位無法用索引完成分組
• GROUP BY 的欄位與 ORDER BY 的欄位不同
• DISTINCT 搭配 ORDER BY
• UNION（去重需要）——UNION ALL 不需要，這是兩者最重要的差別
• 衍生表（FROM (SELECT ...)）在某些情況下被物化

記憶體還是磁碟

MySQL 8.0 的內部暫存表預設用 TempTable 引擎，先放記憶體：

SELECT @@internal_tmp_mem_storage_engine;   -- TempTable
SELECT @@temptable_max_ram;                 -- 預設 1GB（全實例共用）
SELECT @@tmp_table_size, @@max_heap_table_size;

超過上限就轉成磁碟上的暫存表，成本瞬間跳一個數量級。監控這個：

SHOW STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables       建了幾張暫存表
-- Created_tmp_disk_tables  其中幾張跑到磁碟上 ← 這個比例要盯

最該警覺的組合

回扣 ch01 說過的：Using temporary; Using filesort 同時出現，而且 rows 很大——先建表、再寫入、再整批排序，三件貴事一次做完。後台的統計報表頁十之八九慢在這裡。

兩個實務上有效的處理

• 讓 GROUP BY 與 ORDER BY 對齊（或在 8.0 明確加上 ORDER BY NULL 表示不需要排序），常常就能消掉暫存表。
• 能用 UNION ALL 就不要用 UNION——如果你確定兩邊不會重複，去重那一步是白花的。

先破除一個常見的誤解：LIMIT 100000, 10 不是資料庫「跳」到第 100,001 列。它是老老實實把前面 100,000 列全部讀出來、然後丟掉。

SELECT * FROM orders WHERE status = 1
ORDER BY created_at DESC LIMIT 100000, 10;

逐步推導成本

解法一：延遲關聯（deferred join）

核心觀念：先在索引上完成分頁（不回表），拿到主鍵之後再回表一次。

-- 改寫前：跳過的 10 萬列，每一列都回表
SELECT * FROM orders WHERE status = 1
ORDER BY created_at DESC LIMIT 100000, 10;

-- 改寫後：內層只碰索引（覆蓋索引），只有最後 10 列回表
SELECT o.* FROM orders o
JOIN (
  SELECT id FROM orders
  WHERE status = 1
  ORDER BY created_at DESC
  LIMIT 100000, 10
) AS t ON t.id = o.id;

成立的前提是內層查詢必須能被索引覆蓋——需要一個 (status, created_at) 之類的索引，讓 WHERE、ORDER BY、以及取出 id（InnoDB 二級索引天生帶主鍵）都在索引裡完成。

• 優點：只改 SQL，API 介面不變，頁碼功能保留。
• 限制：成本仍隨頁碼線性成長，只是單價從「回表一次」降到「讀一個索引項」，大約快十倍。第 5000 頁還是會比第 1 頁慢。

解法二：keyset pagination（seek method）

治本的做法：不要用「跳過幾列」定位，改用「從哪個值之後開始」定位。

-- 第一頁
SELECT * FROM orders WHERE status = 1
ORDER BY created_at DESC, id DESC LIMIT 20;

-- 下一頁：帶著上一頁最後一筆的值回來
SELECT * FROM orders
WHERE status = 1
  AND (created_at, id) < ('2026-08-13 10:22:31', 88214)
ORDER BY created_at DESC, id DESC LIMIT 20;

• 成本恆定：每一頁都是索引上的一次定位 + 讀 20 列。第 5000 頁與第 1 頁一樣快。
• 要有唯一的 tie-break 欄位（上例的 id）。只用 created_at 的話，同一秒內的多筆資料會在翻頁時重複出現或被跳過。
• 順便解決了另一個問題：翻頁期間有新資料寫入時，OFFSET 分頁會讓某些資料重複出現或消失；keyset 不會。

徵狀

週二下午 3 點，監控告警：整個服務的 p99 從 80 毫秒衝到 4 秒。異常的不只訂單相關的 API——連會員登入、商品查詢這些完全不同的表，也一起變慢。

• 沒有部署、沒有流量尖峰（QPS 甚至比平常低）。
• 資料庫 CPU 只有 40%，但磁碟 I/O 打滿。
• 10 分鐘後自己恢復了，隔天下午 3 點又發生一次。

診斷

先抓當下在跑什麼（回扣 ch01 的救火三件套）：

SHOW PROCESSLIST;
-- 一句跑了 200 秒的 SELECT，來自後台匯出功能

EXPLAIN FOR CONNECTION 4471;
-- key=idx_created  type=range  rows=8420000  Extra=Using where

-- 那句 SQL 長這樣（每頁 100 筆，第 5000 頁）
SELECT * FROM orders WHERE created_at >= '2026-01-01'
ORDER BY created_at DESC LIMIT 499900, 100;

營運同事在後台一頁一頁往後翻找一筆舊訂單。單看這句 SQL：掃 50 萬列、回表 50 萬次、丟掉 499,900 列——很慢，但為什麼會影響到會員登入？

根因：Buffer Pool 被沖走

修法（依序）

真正的教訓

深分頁的根因通常不在 SQL，在介面設計允許了一個很貴的問題。所以這一節談的是 Java 這邊怎麼寫。

Spring Data 的 Pageable 預設就是 OFFSET 分頁

// 這行產生的就是 LIMIT ?, ?，頁碼越大越慢
Page<Order> page = orderRepo.findByStatus(1, PageRequest.of(4999, 100));

而且 Page（相對於 Slice）還會多發一句 COUNT(*) 來算總頁數——那句 count 在大表上常常比查詢本身更貴，因為它必須掃過所有符合條件的列。

// 不需要總筆數時改用 Slice，省掉那句 COUNT
Slice<Order> slice = orderRepo.findByStatus(1, PageRequest.of(0, 100));

keyset pagination 的介面長相

public record OrderPage(List<OrderDto> items, String nextCursor) {}

// cursor 就是「上一頁最後一筆的排序鍵」，通常編碼成不透明字串
@Query("""
    SELECT o FROM Order o
    WHERE o.status = :status
      AND (o.createdAt < :ts OR (o.createdAt = :ts AND o.id < :id))
    ORDER BY o.createdAt DESC, o.id DESC
    """)
List<Order> findNextPage(int status, LocalDateTime ts, long id, Pageable limit);

幾個實務要點：

• cursor 要不透明（Base64 或簽章）。直接暴露時間戳與 id 會讓前端開始自己拼接，之後就改不動了。
• 排序鍵必須唯一——所以一定要帶上 id 當 tie-break。
• 不要同時支援兩種分頁。留著頁碼參數就一定有人用，深分頁的問題就還在。

什麼時候 OFFSET 分頁是可以的

不要矯枉過正，OFFSET 在這些情況完全沒問題：

• 資料量本來就小（幾千列以內的設定檔、字典表）。
• 頁數被限制住——例如搜尋結果最多只給 20 頁，超過請縮小條件。這是最務實的折衷：既保留跳頁功能，又讓成本有上界。

還有一個常被忽略的成本：COUNT(*)

• 大表上的 COUNT(*) 沒有捷徑，即使走覆蓋索引也要掃完整個索引。
• 常見折衷：不顯示總筆數（改成「還有更多」）、顯示近似值（用 EXPLAIN 的 rows 估算，或維護一張計數表）、或只在第一頁算一次並快取。
• 「總共幾筆」這個需求的真實價值，往往遠低於它的成本——這是最值得回頭跟產品確認的一個欄位。

把這一章收成一個可以照著跑的流程。

四個監控指標

SHOW STATUS LIKE 'Sort_merge_passes';       -- 排序落磁碟的次數
SHOW STATUS LIKE 'Sort_rows';               -- 排過的總列數
SHOW STATUS LIKE 'Created_tmp_disk_tables'; -- 落磁碟的暫存表
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';-- 沒命中快取、真的去讀磁碟的次數

前三個持續成長代表這一章的問題正在發生；第四個突然跳高，就是上面那個「Buffer Pool 被沖走」的訊號。

什麼時候這一章的優化不該做

• 資料量小的表：幾千列的排序在記憶體裡是微秒等級。為它加索引，反而多付寫入成本。
• 一天跑一次的報表：慢 30 秒沒有關係——但要確認它不會沖掉 Buffer Pool 影響線上，這才是重點。移到複本或離峰執行，比優化它更有效。
• 還沒量測就先改成 keyset：它會犧牲跳頁功能。先確認真的有人在翻深頁（看存取日誌的 page 參數分佈），不要為了假想的問題砍掉真實的功能。

把這一章收成三句

下一章要處理的是另一種「一句 SQL 影響所有人」的機制，而且它比 Buffer Pool 更難診斷：鎖。

第二條主線在這一章特別密集，把帳一次算清楚。

五筆帳

• 加複合索引免掉 filesort → 寫入時要多維護一棵 B+Tree、多佔空間。排序欄位通常是 created_at 這類每列都不同的欄位，索引不會小。
• 調大 sort_buffer_size／join_buffer_size → 每連線每次操作各一份。這是最容易釀成事故的調參：改成 32MB、200 個連線同時排序就是 6.4GB，而那是從 Buffer Pool 搶來的，結果是「為了讓排序不落磁碟，害得所有查詢都不命中快取」。
• 延遲關聯 → SQL 變複雜、需要一個能覆蓋的索引。成本仍隨頁碼線性成長，只是單價變低。
• keyset pagination → 失去跳頁功能、cursor 要維護、前端要改。這是產品決定。
• 移到唯讀複本 → 隔離了 Buffer Pool 汙染，但引入複本延遲：剛寫入的資料可能查不到。這是把效能問題換成一致性問題（ch07 會談怎麼判斷能不能接受）。

這一章的診斷看不到的東西

• 排序期間持有的鎖。一個大排序在交易中執行時，它持有的鎖會一直到交易結束——排序花的每一秒，都是別人等鎖的一秒（ch05）。
• 誰在呼叫它。一句深分頁 SQL 出現在慢查詢日誌裡，日誌不會告訴你它是使用者翻頁、爬蟲掃描、還是某個排程每五分鐘跑一次（ch07）。
• ORM 有沒有偷偷加了東西。Hibernate 的分頁在有 JOIN FETCH 時會改成在記憶體裡分頁——把符合條件的資料全部撈進 JVM 再切。這時候 LIMIT 根本不會出現在 SQL 裡，而你會在應用日誌看到 OOM 而不是慢查詢（ch08 主講）。