《高性能 MySQL》讀書筆記二：查詢優化、重構、排序、分頁、子查詢實戰精華

《高性能 MySQL》讀書筆記二：查詢優化、重構、排序、分頁、子查詢實戰精華

基於《高性能 MySQL》第 4 章核心觀點，結合實戰經驗整理：為什麼要優化查詢、訪問類型層級、三種 WHERE 應用方式、重構策略、關聯查詢執行機制、排序優化、子查詢陷阱、最值優化、GROUP BY/DISTINCT、LIMIT 分頁優化十大專題。

一、優化核心指標

• 響應時間 = 服務時間 + 排隊時間 + 傳輸時間
• 掃描行數：EXPLAIN 中 rows 列，核心衡量指標
• 訪問類型（由慢到快）：ALL 全表掃描 → index 索引掃描 → range 范圍 → ref 索引查找 → eq_ref 唯一索引 → const 常數 → system 系統表

二、WHERE 條件應用三層次（由優到劣）

1. 存儲引擎層：索引中直接過濾不匹配行（Using index condition/ICP）
2. Server 層覆蓋索引：Using index，從索引直接返回，無需回表
3. Server 層數據過濾：Using where，讀取完整行後再過濾，開銷最大

三、掃描大量行卻返回少量行的優化路徑

1. 索引覆蓋掃描：將所需列全部放入索引
2. 改變表結構：建立匯總表/物化視圖
3. 重寫查詢：引導優化器選擇更優路徑

四、查詢重構策略

• 切分大查詢：分批刪除/更新，避免長事務鎖表、填滿 Redo Log
• 分解關聯查詢：應用層組裝多表單表查詢結果，優勢：
  ① 緩存單表結果復用
  ② 減少鎖競爭
  ③ 避免重複訪問同一表
  ④ 利用 IN 列表替代 JOIN

五、MySQL 關聯查詢執行機制

• 嵌套循環：驅動表逐行 → 非驅動表索引查找匹配
• 優化器自動選擇驅動表：通常選行數少、索引好表
• STRAIGHT_JOIN：強制指定連接順序
• GROUP BY/ORDER BY 單表列：才能利用索引排序

六、排序優化

• filesort：內存排序（sort_buffer_size）或磁盤臨時文件排序
• 關聯查詢排序兩種模式：
  ① ORDER BY 列全屬驅動表 → 驅動表階段排序（Using filesort）
  ② 否則 → 關聯完成寫臨時表 → 全量排序（Using temporary; Using filesort）
• MySQL 5.6+ LIMIT 推送優化：不再全量排序

七、子查詢優化陷阱

• IN (SELECT ...) 最差：外層表掃描 → 逐行執行子查詢
• 重寫為 JOIN 或 EXISTS
• GROUP_CONCAT 構造 IN 列表：WHERE id IN (SELECT GROUP_CONCAT(id) FROM ...)
• 特例：EXISTS 避免 DISTINCT 去重開銷，有時比 JOIN 更快

八、最值優化