在 MySQL 中，SQL 優化是性能調優的核心環節，尤其是在數據量大、并發高的情況下。這里整理一份 MySQL 常見 SQL 語句優化指南，從查詢寫法、索引使用到執行計劃分析，涵蓋實用技巧：

---

1. 查詢語句層面的優化

? 避免 SELECT *

• 問題：會取出不必要的列，增加 I/O 和網絡傳輸。
• 優化：只取需要的列。

-- 不推薦
SELECT * FROM user;-- 推薦
SELECT id, username, email FROM user;

---

? 減少子查詢，優先使用 JOIN

• 問題：子查詢可能生成臨時表，效率低。
• 優化：能用 JOIN 就不用子查詢。

-- 子查詢
SELECT name FROM employee WHERE dept_id IN (SELECT id FROM department WHERE name = '研發部'
);-- JOIN 優化
SELECT e.name 
FROM employee e 
JOIN department d ON e.dept_id = d.id
WHERE d.name = '研發部';

---

? 合理使用 LIMIT

• 問題：LIMIT offset, size 偏移量大時性能差。
• 優化：利用索引字段加條件過濾。

-- 慢
SELECT * FROM orders LIMIT 100000, 20;-- 推薦（基于自增主鍵優化）
SELECT * FROM orders 
WHERE id > 100000 
LIMIT 20;

---

? 避免 OR，改用 IN 或 UNION ALL

• 問題：OR 會導致索引失效。
• 優化：使用 IN 或 UNION ALL。

-- 不推薦
SELECT * FROM user WHERE status = 1 OR status = 2;-- 推薦
SELECT * FROM user WHERE status IN (1, 2);

---

? 模糊查詢優化

• 問題：LIKE '%abc' 不能走索引。

• 優化：

  • 改寫為 LIKE 'abc%'（前綴匹配可以走索引）。
  • 或者使用 全文索引 (FULLTEXT) / ElasticSearch。

-- 慢
SELECT * FROM article WHERE title LIKE '%優化%';-- 推薦
ALTER TABLE article ADD FULLTEXT(title);
SELECT * FROM article WHERE MATCH(title) AGAINST('優化');

---

? 避免在 WHERE 中對列做函數運算

• 問題：索引失效。
• 優化：把函數移到等式右邊。

-- 不推薦
SELECT * FROM user WHERE YEAR(create_time) = 2024;-- 推薦
SELECT * FROM user 
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

---

2. 索引使用優化

? 建立合適的索引

• 單列索引：適合高頻查詢字段。
• 復合索引：遵循 最左前綴原則。

CREATE INDEX idx_user_email ON user(email);
CREATE INDEX idx_order_user_time ON orders(user_id, create_time);

---

? 覆蓋索引 (Covering Index)

• 查詢字段都在索引中，不需要回表。

-- 索引包含 (user_id, create_time)
SELECT user_id, create_time 
FROM orders 
WHERE user_id = 123;

---

? 避免過多索引

• 問題：索引會增加寫操作開銷。
• 優化：只在查詢頻繁的字段建索引，刪除無用索引。

---

3. 執行計劃分析

? 使用 EXPLAIN

分析查詢是否走索引。

EXPLAIN SELECT * FROM orders WHERE user_id = 100;

重點關注字段：

• type：優先級 system > const > eq_ref > ref > range > index > ALL
• key：實際使用的索引
• rows：掃描行數，越少越好

---

4. 表結構與數據優化

• 分庫分表：大表 (>千萬行) 考慮拆分。
• 冷熱數據分離：歷史數據歸檔，減少主表數據量。
• 合理字段類型：能用 INT 不用 BIGINT，能用 CHAR(10) 不用 VARCHAR(255)。

---

5. 常見優化 checklist

• 是否避免了 SELECT *
• 是否有合適的索引
• 是否避免在索引列上使用函數、計算
• 是否避免大 offset 的分頁
• 是否利用了 EXPLAIN 分析執行計劃

---