編寫高效能 MySQL/Oracle SQL 語句核心原則（二）：NOT、IN/EXISTS、複合索引與統計資訊

5. NOT / != / <> 否定邏輯導致索引失效

• WHERE status != 'CLOSED'、WHERE NOT (status = 'CLOSED') 皆無法使用 status 索引。
• 改寫為正向邏輯：WHERE status IN ('OPEN','PENDING','PROCESSING') 或 WHERE status = 'OPEN' OR status = 'PENDING' ...。
• 若值域極大（如排除少數值），考慮位圖索引（Oracle）或分區表裁剪。

6. IN 子查詢 vs EXISTS 子查詢

改寫範例

-- 低效 IN
SELECT * FROM orders WHERE cust_id IN (SELECT id FROM customers WHERE region='US');

-- 高效 EXISTS
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.cust_id AND c.region='US');

SELECT 1 僅判斷存在性，不回傳實際資料，最輕量。

7. 複合索引最左前綴原則

• 索引 (a,b,c) 可支援：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3。
• 不可支援：WHERE b=2、WHERE c=3、WHERE a=1 AND c=3（跳過 b）。
• 查詢條件順序不影響優化器判斷，但WHERE 子句中需包含最左列。

8. 統計資訊與執行計畫

• 優化器依賴統計資訊（列直方圖、密度、NULL 比例）估算基數。
• 大量資料變更後必須更新統計：ANALYZE TABLE (MySQL)、DBMS_STATS.GATHER_TABLE_STATS (Oracle)、UPDATE STATISTICS (SQL Server)。
• 讀取執行計畫：EXPLAIN (MySQL/Oracle)、SET SHOWPLAN_ALL ON (SQL Server)，關注 type=ALL（全表掃描）、rows 巨大偏離實際、Using filesort、Using temporary。

總結清單

1. 欄位非 NULL 預設值，避免 IS NULL 掃描。
2. 索引列不參與運算/函數/隱性轉換。
3. LIKE 'prefix%' 走索引，LIKE '%suffix' 全掃。
4. ORDER BY 掛索引列，避免運算式。
5. 否定邏輯改正向 IN，NOT IN 改 NOT EXISTS。
6. 子查詢優先 EXISTS，避免 IN 臨時表開銷。
7. 複合索引遵循最左前綴，查詢帶最左列。
8. 定期更新統計資訊，讀取執行計畫驗證索引生效。