MySQL 實戰優化案例：主鍵掃描陷阱與覆蓋索引設計

案例一：ORDER BY 主鍵 + WHERE 過濾的陷阱

問題 SQL

SELECT * FROM game_shares_buy_list 
WHERE price >= '2.00' 
ORDER BY tran_id DESC LIMIT 10;

表結構

PRIMARY KEY (tran_id), KEY ind_username (username)

執行計劃誤區

• 優化器選擇 PRIMARY 索引倒序掃描，避免排序
• 但 price >= 2.00 僅 4,087 行（佔 0.26%），需掃描大量不符合行
• 實測：7.06 秒

強制索引也未達最佳

FORCE INDEX(ind_game_shares_buy_list_price)
-- 1.01 秒，仍需回表排序

終極優化：子查詢先取 ID 再回表

SELECT * 
FROM game_shares_buy_list t1
JOIN (
  SELECT tran_id 
  FROM game_shares_buy_list 
  WHERE price >= '2.00' 
  ORDER BY tran_id DESC LIMIT 10
) t2 ON t1.tran_id = t2.tran_id;
-- 0.00 秒

原理：子查詢僅掃描 price 索引取 10 個 ID，外層主鍵點查 10 次，避免大量回表與排序。

案例二：Session 表主鍵設計缺陷

原表結構

PRIMARY KEY (session_id) -- VARCHAR(255) UTF8
session_data TEXT

問題現象

• 主鍵過長（255 字元 × 3 = 765 位元組），輔助索引葉節點存主鍵值，導致所有二級索引膨脹
• TEXT 欄位無法放入覆蓋索引，查詢必須回表
• 大量 INSERT 造成頁分裂與鎖競爭

重構方案

CREATE TABLE game_session (
  id INT AUTO_INCREMENT PRIMARY KEY,           -- 4 字節整數主鍵
  session_id VARCHAR(30) CHARSET utf8 COLLATE utf8_bin NOT NULL,
  session_expires INT UNSIGNED NOT NULL DEFAULT 0,
  client_ip VARCHAR(16),
  session_data VARCHAR(200),                   -- TEXT → VARCHAR 可入索引
  KEY ind_session_id (session_id, session_data, session_expires) -- 覆蓋索引
) ENGINE=InnoDB;

優化收益

1. 主鍵 4 字節 → 二級索引體積大幅縮減
2. 查詢 session_data, session_expires 走覆蓋索引，零回表
3. 自增主鍵避免頁分裂，寫入效能提升