現象重現

-- 32 秒！
SELECT * FROM tables WHERE m_id IN (50,49) ORDER BY id DESC LIMIT 10;

-- 去掉 ORDER BY：0.05 秒
SELECT * FROM tables WHERE m_id IN (50,49) LIMIT 10;

原因：MySQL 先按 m_id 索引篩選，再對結果集檔案排序，最後 LIMIT。大 offset 時排序開銷巨大。

優化策略

策略 1：強制使用覆蓋索引（適用於中小 offset）

-- 建立複合索引
ALTER TABLE tables ADD INDEX idx_mid_id (m_id, id);

-- 強制使用索引
SELECT * FROM tables FORCE INDEX (idx_mid_id) 
WHERE m_id IN (50,49) ORDER BY id DESC LIMIT 10;

• 原理：索引已按 (m_id, id) 排序，直接逆序掃描前 10 行，避免 filesort。
• 實測：32 秒 → 0.0068 秒

策略 2：延遲關聯 / 子查詢優化大 offset

-- 優化前：掃描 50 行丟棄 50 行
SELECT * FROM member ORDER BY last_active LIMIT 50, 5;

-- 優化後：子查詢只掃描索引取 ID，主查詢回表 5 行
SELECT * FROM member 
INNER JOIN (
  SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5
) AS t USING (member_id);

• 原理：子查詢 Covering Index 僅讀索引，主查詢按主鍵精準回表。
• 適用：offset 極大（百萬級）場景。

策略 3：應用層游標分頁（最高效，適合無限滾動）

-- 首頁
SELECT * FROM tables WHERE m_id IN (50,49) ORDER BY id DESC LIMIT 20;

-- 翻頁：記住上一頁最小 id
SELECT * FROM tables WHERE m_id IN (50,49) AND id < 12345 ORDER BY id DESC LIMIT 20;

• 無 offset，直接索引範圍掃描，效能恆定。
• 需前端配合傳遞 last_id（游標）。

伺服器層面調優

• 只取需要的欄位：禁用 SELECT *，減少網路傳輸與記憶體。
• 合理 WHERE：避免全表掃描，善用複合索引。
• 避免重複查詢：合併同表同條件的 UPDATE，減少事務鎖定時間。
• 調整隔離級別：報表查詢可用 READ COMMITTED 或 REPEATABLE READ 配合一致性讀。

索引設計口訣

WHERE 條件列在前，排序分組列在後，選取列覆蓋索引，深分頁不再愁。