MySQL 高效查詢語句設計七大原則：索引、排序、連接、子查詢與正則優化

SQL 查詢效能優化 80% 取決於語句寫法。本文總結七大高效查詢設計原則：索引策略、排序簡化、避免全表掃描、消除關聯子查詢、正則優化、臨時表加速、排序替代隨機 I/O。

一、合理使用索引 (查詢加速基石)

• ✅ 經常 JOIN 的非外鍵欄位建立索引
• ✅ GROUP BY/ORDER BY 高頻欄位建立索引
• ✅ 條件欄位區分度高 (基數大) 建立索引
• ❌ 區分度低欄位 (如性別、狀態碼) 不建索引，反拖慢寫入
• ✅ 多欄位排序/過濾建立複合索引 (遵循最左前綴原則)
• 🔧 定期用工具檢查索引碎片，大量更新後重建索引

二、避免或簡化排序操作

排序是昂貴操作，優化器能利用索引順序時會跳過排序：

• ❌ 索引缺少排序欄位
• ❌ ORDER BY 順序與索引順序不一致
• ❌ 排序欄位來自不同表 (JOIN 後排序)

對策： 建立覆蓋索引、合併表減少 JOIN、必要時只排序核心欄位。

三、消除大表順序掃描 (全表掃描殺手)

巢狀查詢中順序存取會導致指數級行數檢查：

-- 3層巢狀各1000行 = 10億次檢查

核心解法： 連接欄位**必須建立索引**

-- 學生表+選課表 JOIN 於 學號，學號雙方皆需索引

UNION 替代 OR 條件避免全掃

-- 原: OR 條件導致全表掃描
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008;

-- 優化: UNION 分別走索引
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008;

四、避免關聯子查詢 (Correlated Subquery)

外層每行觸發內層查詢，效能呈 N×M 級：

-- 低效: 關聯子查詢
SELECT * FROM orders o
WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE cust_id = o.cust_id);

-- 高效: JOIN 或窗口函數 (MySQL 8.0+)
SELECT o.* FROM orders o
JOIN (SELECT cust_id, AVG(amount) AS avg_amt FROM orders GROUP BY cust_id) a
  ON o.cust_id = a.cust_id
WHERE o.amount > a.avg_amt;

五、避免複雜正則表達式

• LIKE '%keyword%' 開頭萬用字元無法用索引 → 全表掃描
• zipcode LIKE '98___' 固定長度模式同樣無法用索引
• 優化： 改用範圍查詢 zipcode > '98000' AND zipcode < '99000' 走索引
• 避免非起始子串： SUBSTR(zipcode,2,2) > '80' 無法用索引

六、臨時表加速複雜重複查詢

將中間結果集物化到臨時表，減少重複排序與掃描：

-- 步驟1: 建立臨時表 (一次排序)
CREATE TEMPORARY TABLE cust_with_balance AS
SELECT c.name, r.balance, c.postcode
FROM cust c JOIN rcvbles r ON c.customer_id = r.customer_id
WHERE r.balance > 0
ORDER BY c.name;

-- 步驟2: 多次高效查詢臨時表
SELECT * FROM cust_with_balance WHERE postcode > '98000';

⚠️ 臨時表不反映主表即時變更，高頻寫入場景謹慎使用。

七、用排序替代非順序磁碟存取

隨機 I/O (磁頭跳躍) 遠慢於順序讀寫。將非順序存取轉為排序 + 順序掃描：

-- 原: 隨機存取大量離散行
-- 優化: 先排序再順序處理，利用緩衝池預讀