查詢優化是後端工程師的核心基本功。SQL 寫得不好，輕則頁面卡頓，重則庫掛服務雪崩。本文系統總結索引策略、SQL 改寫、EXPLAIN 深度解析、優化器 Hint 等實戰技巧，助你構建高性能數據訪問層。

一、常用查詢優化速查表

場景	優化手段	核心原則
MAX/MIN	列加索引	B+Tree 最左/最右葉子節點 O(1) 取值
COUNT	用 COUNT(col) 而非 COUNT(*)	前者不統計 NULL，避免全表掃描（MyISAM 除外）
子查詢	改寫為 JOIN	衍生表無索引，JOIN 可用索引、優化器更優
GROUP BY	子查詢內先 WHERE/GROUP BY	減少外層臨時表，避免 Using temporary
ORDER BY + LIMIT	1. 用主鍵/索引列排序 2. 記住上次最大 ID，用 WHERE id > last_id ORDER BY id LIMIT N	避免 filesort，利用索引有序性

二、索引創建鐵律

1. 覆蓋查詢列：WHERE、ORDER BY、GROUP BY、ON/JOIN 條件列
2. 列越小越好：INT < BIGINT < VARCHAR < TEXT/BLOB
3. 聯合索引最左前綴：高區分度列靠左，(a,b,c) 支援 a/a,b/a,b,c
4. 區分度公式：COUNT(DISTINCT col)/COUNT(*)，越接近 1 越好
5. 避免過度索引：寫多讀少表索引 ≤ 3 個，每個索引增加 INSERT/UPDATE 開銷
6. 定期清理：pt-index-usage 分析未用索引並 DROP

聯合索引失效三大陷阱

1. WHERE 條件跳過左列（如 WHERE b=1 無法用 idx(a,b)）
2. 排序方向混用（ORDER BY a ASC, b DESC 無法用索引排序）
3. 範圍查詢後列失效（WHERE a=1 AND b>2 AND c=3 中 c 無法用索引）

三、表結構優化：垂直/水平拆分

垂直拆分（列拆分）

• 不常用列移到擴展表（如 user_profile）
• 大字段（TEXT/BLOB）獨立表，避免主表行溢出
• 高頻共用列留主表（user_core）

水平拆分（分表/分庫）

• Hash 取模：shard_id = user_id % N
• 範圍/時間分片：按月/按區間
• 挑戰：跨分片查詢、全局統計報表
• 對策：前端分片路由查詢，後台用匯總表/數據倉庫（ClickHouse/Doris）

四、Query Cache：雙刃劍，謹慎使用

• query_cache_type=1 全開：適用「極少寫、極高重複讀」
• query_cache_type=2 按需：SELECT SQL_CACHE ... 顯式開啟
• SQL_NO_CACHE 強制繞過
• 生產建議：5.7+ 建議關閉（type=0, size=0），改用 Redis 等外部緩存

五、MySQL 自動優化與索引選擇

優化器根據「滿足條件行數 / 總行數」比例決策是否用索引：

• 比例 ≤ 30% 左右 → 用索引
• 比例 > 30% → 全表掃描更快
• 不同引擎閾值不同，可用 FORCE INDEX 強制干預

六、基於索引的排序優化

-- 原查詢：WHERE 用 name 索引，ORDER BY sale_date 無索引 → filesort
SELECT * FROM sales WHERE name='xxx' ORDER BY sale_date DESC;

-- 優化：建立聯合索引 (name, sale_date)
ALTER TABLE sales DROP INDEX idx_name, ADD INDEX idx_name_sale (name, sale_date);

注意：WHERE 中不能出現排序列（sale_date），否則需單獨索引，否則查詢又慢。

七、優化器 Hint 實戰

Hint	作用	場景
STRAIGHT_JOIN	強制連接順序	優化器選錯驅動表時
USE INDEX(idx_a, idx_b)	限制候選索引範圍	索引過多導致優化器選錯
IGNORE INDEX(idx_bad)	排除特定索引	某索引導致錯誤執行計劃
FORCE INDEX(idx_good)	強制使用某索引	確信某索引最優但優化器不選
SQL_BUFFER_RESULT	結果集寫臨時表，快釋放鎖	大結果集、長網絡傳輸
SQL_BIG_RESULT	分組/去重時用磁盤臨時表排序	GROUP BY/DISTINCT 大數據集

八、EXPLAIN 實戰深度解析

案例 1：單表範圍查詢 + 排序

CREATE TABLE article (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  author_id INT UNSIGNED, category_id INT UNSIGNED,
  views INT UNSIGNED, comments INT UNSIGNED,
  title VARBINARY(255), content TEXT
);

-- 需求：category_id=1 且 comments>1 時，views 最大的 article_id
EXPLAIN SELECT author_id FROM article
WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1\G

初始輸出：type=ALL, key=NULL, Extra=Using where; Using filesort → 全表掃描 + 文件排序，最差。

嘗試 1：ADD INDEX x(category_id, comments, views) → type=range, key=x, key_len=8, Extra=Using where; Using filesort。
原因：comments>1 是範圍條件，導致其右側 views 索引失效。

優化：去掉中間範圍列，建 ADD INDEX y(category_id, views) → type=ref, key=y, key_len=4, Extra=Using where。
結果：ref 類型，無 filesort，rows=1，完美。

案例 2：LEFT JOIN 優化右表

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card\G
-- 初始：兩表皆 type=ALL
-- 在 book(card) 建索引 → book 變 ref，rows 大幅下降
-- 在 class(card) 建索引 → 無效果（LEFT JOIN 驅動表固定為左表）

結論：LEFT JOIN 必須優化右表（被驅動表）；RIGHT JOIN 優化左表；INNER JOIN 優化器自動選擇小表驅動大表，雙邊建索引最保險。

案例 3：三表 LEFT JOIN 串聯

ALTER TABLE book ADD INDEX y(card);
ALTER TABLE phone ADD INDEX z(card);
EXPLAIN SELECT * FROM class
LEFT JOIN book ON class.card=book.card
LEFT JOIN phone ON book.card=phone.card;
-- 結果：class=ALL, book=ref, phone=ref (Using index)，總 rows 極小

九、總結：優化閉環

1. 慢查詢收集：long_query_time=1 + pt-query-digest 分析 Top SQL
2. EXPLAIN 診斷：關注 type（ALL/index/range/ref/eq_ref/const）、key、rows、Extra
3. 索引調整：遵循最左前綴、覆蓋索引、避免範圍列後失效
4. SQL 改寫：子查詢→JOIN、OR→UNION、分頁優化、Hint 干預
5. 架構層面：讀寫分離、分庫分表、緩存層、異步化
6. 驗證回歸：壓測對比 TPS/RT，上線後持續監控