SQL 效能調校

為什麼要調校？

比喻：效能調校就像交通規劃 🚗

一條路（查詢）如果有 100 萬台車（資料）， 沒有紅綠燈和分流（索引和優化），就會塞車（查詢很慢）。

EXPLAIN ANALYZE — 查詢計畫分析

-- 查看查詢的執行計畫
EXPLAIN ANALYZE
SELECT * FROM Students WHERE Email = 'test@gmail.com';

-- 沒有索引時：
Seq Scan on students            ← 全表掃描！
  Filter: (email = 'test@gmail.com')
  Rows Removed by Filter: 99999
  Planning Time: 0.1 ms
  Execution Time: 150.5 ms      ← 很慢

-- 有索引後：
Index Scan using idx_email      ← 用索引！
  Index Cond: (email = 'test@gmail.com')
  Planning Time: 0.1 ms
  Execution Time: 0.05 ms       ← 超快

關鍵字解讀

常見效能陷阱

1. SELECT * 的問題

-- ❌ 壞：取全部欄位（包含大型 TEXT 欄位）
SELECT * FROM Articles WHERE CategoryId = 5;

-- ✅ 好：只取需要的欄位
SELECT Id, Title, CreatedAt FROM Articles WHERE CategoryId = 5;

2. 在索引欄位上做運算

-- ❌ 壞：函數包住欄位 → 索引失效
SELECT * FROM Students WHERE UPPER(Name) = 'MIKE';
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024;

-- ✅ 好：避免在欄位上做運算
SELECT * FROM Students WHERE Name = 'Mike';  -- 用大小寫不敏感的比對
SELECT * FROM Orders
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

3. LIKE 開頭用萬用字元

-- ❌ 壞：開頭用 % → 索引失效
SELECT * FROM Students WHERE Name LIKE '%明';

-- ✅ 好：開頭用確定值
SELECT * FROM Students WHERE Name LIKE '小%';

4. OR 可能導致索引失效

-- ❌ 可能不用索引
SELECT * FROM Students WHERE Age = 20 OR Email = 'test@gmail.com';

-- ✅ 改用 UNION
SELECT * FROM Students WHERE Age = 20
UNION
SELECT * FROM Students WHERE Email = 'test@gmail.com';

N+1 查詢問題

-- ❌ N+1 問題（應用程式層常見）
-- 1. 先查所有訂單（1 次查詢）
SELECT * FROM Orders;
-- 2. 對每筆訂單查客戶名稱（N 次查詢）
SELECT Name FROM Customers WHERE Id = 1;
SELECT Name FROM Customers WHERE Id = 2;
-- ... 重複 N 次

-- ✅ 用 JOIN 一次搞定
SELECT o.*, c.Name AS CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id;

分頁優化

-- ❌ 大 OFFSET 很慢（掃描 + 丟棄前 100000 筆）
SELECT * FROM Orders
ORDER BY Id
LIMIT 10 OFFSET 100000;

-- ✅ 用游標分頁（Keyset Pagination）
SELECT * FROM Orders
WHERE Id > 100000              -- ← 直接從上一頁最後的 Id 開始
ORDER BY Id
LIMIT 10;

其他優化技巧

-- 1. 用 EXISTS 取代 IN（大子查詢時）
-- ❌
SELECT * FROM Students WHERE Id IN (SELECT StudentId FROM Enrollments);
-- ✅
SELECT * FROM Students s WHERE EXISTS (
    SELECT 1 FROM Enrollments e WHERE e.StudentId = s.Id
);

-- 2. 批次操作取代逐筆操作
-- ❌ 一筆一筆插入
INSERT INTO Logs VALUES (...);
INSERT INTO Logs VALUES (...);
-- ✅ 批次插入
INSERT INTO Logs VALUES (...), (...), (...), ...;

-- 3. 適當使用 Materialized View
CREATE MATERIALIZED VIEW mv_report AS
SELECT ... -- 複雜的報表查詢
;
REFRESH MATERIALIZED VIEW mv_report;  -- 定時刷新

效能檢查清單