🔧 阿川の電商水電行
本番環境中資料量增加時，應用程式會突然變得緩慢
所有工程師都曾面對這個噩夢。其原因多數是 “不知不覺中讓DB痛苦的SQL”。
本文將探討在實務上特別造成重大影響的 5種致命SQL反模式，以及額外的 批次處理優化（批次INSERT）、EXISTS 與 IN 的差異，並且整理出以下幾點：
- 為什麼會變慢（原理）
- 該怎麼修正（改進方案）
- 會有什麼陷阱（權衡）
- 實務檢查清單
以技術書的水準清晰地整理。
目次
- 殺死索引的「函數・運算符」使用
- 摧毀應用性能的「N+1問題」
- 隱藏的性能殺手「SELECT *」
- 前方一致以外的 LIKE 搜尋
- 批次處理優化：批次INSERT
- EXISTS 與 IN 的區別
- 補充：使用
EXPLAIN的實務確認流程 - 現場實用的小技巧（Tips）
- 總結與下一步
1. NG1：殺死索引的「函數・運算符」使用
1.1 原理與NG範例
索引是根據 欄位的原始值 來建立的。在 WHERE 子句中對欄位使用函數或運算時，DB無法使用索引。
NG範例
WHERE YEAR(created_at) = 2024;問題點： created_at 的索引失效 → 對所有資料應用函數 → 全表掃描
圖解
1.2 改進方案與權衡
OK範例
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';優點： 不經過處理即可比較欄位值，從而充分利用索引。
實務檢查清單
- [ ] 在 WHERE 中是否有對欄位使用函數？
- [ ] 是否考慮替換成範圍搜尋？
- [ ] 引入函數索引時是否評估了改寫成本與維護成本？
2. NG2：摧毀應用性能的「N+1問題」
2.1 原理與NG範例
取得父表後，在結果的迴圈內個別取得子表的模式。
NG範例（附圖）
結果： 1 + N 查詢。如果使用者有1000條記錄，則發出1001條查詢。
2.2 改進方案與權衡
OK方案
- 使用 Eager Loading 進行 JOIN 或 IN 聚合
權衡
當 JOIN 變得龐大時，數據傳輸量和記憶體消耗會增加（寬JOIN問題）。
實務檢查清單
- [ ] 是否啟用 ORM 的 N+1 檢查功能（bullet、rack-mini-profiler等）？
- [ ] 在使用 Eager Loading 時，是否計測了回傳數據量？
3. NG3：隱藏的性能殺手「SELECT *」
3.1 原理與NG範例
NG範例
SELECT * FROM products;問題點：
- 轉送不必要的巨大欄位（如 BLOB等） → 網絡I/O膨脹
- 無法使用覆蓋索引，損失了加速的機會
圖解（數據傳輸量）
3.2 改進方案與權衡
OK範例
SELECT id, name, price FROM products;實務檢查清單
- [ ] API 或管理介面是否僅回傳必要的欄位？
- [ ] 是否考慮過能否使用覆蓋索引？
4. NG4：前方一致以外的 LIKE 搜尋
4.1 原理與NG範例
NG範例
WHERE name LIKE '%tanaka%';問題點： B-Tree索引無法使用，全表掃描發生。
圖解
4.2 改進方案與權衡
OK方案
WHERE name LIKE 'tanaka%';或引入全文搜索引擎（Elasticsearch、Solr、PGroonga）。
權衡
- 增加中介軟件會提高架構和運營成本
- 需要額外實作數據同步和一致性
實務檢查清單
- [ ] 搜索要求是否可以用前方一致處理？
- [ ] 在導入全文搜索時，是否試算過成本（運營、基礎設施）？
5. 批次處理優化：批次INSERT
5.1 原理與NG範例
逐行INSERT大量資料時，每次INSERT都需要進行一次事務處理，導致速度極慢。
NG範例
INSERT INTO logs(message) VALUES ('a');
INSERT INTO logs(message) VALUES ('b');
INSERT INTO logs(message) VALUES ('c');問題點：
- 每次都要來回傳輸網路
- 每次都要管理事務
5.2 改進方案：批次INSERT
OK範例
INSERT INTO logs(message) VALUES
('a'),
('b'),
('c');或利用DB/ORM提供的批次API。
圖解
5.3 權衡
- 如果批次大小過大，將增加記憶體使用量
- 如果事務時間過長，鎖定時間也會增加
實務檢查清單
- [ ] 是否逐行INSERT？
- [ ] 是否透過計測決定適當的批次大小（100〜10,000）？
- [ ] 是否確認ORM的批次API是否正確利用索引？
6. EXISTS 與 IN 的區別
6.1 原理
IN 會 對左側表的每一行搜尋右側的列表。
EXISTS 則僅 評估右側表中是否有符合的行。
NG範例（IN 濫用）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts);OK範例（EXISTS）
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM posts p WHERE p.user_id = u.id
);6.2 差異（概念圖）
6.3 何時使用？
- 右側為大量數據 → EXISTS 有優勢（可以提前結束）
- 右側為小型固定列表 → IN 有優勢（更容易優化）
實務檢查清單
- [ ] IN 的右側是否龐大？
- [ ] 使用 RIGHT JOIN + IS NOT NULL 是否更快？
- [ ] 是否實際透過 EXPLAIN 進行比較？
7. 補充：使用 EXPLAIN 的實務確認流程
為什麼要查看 EXPLAIN
- 可以了解DB實際使用了哪些索引，以及讀取了多少行。
最低限度要看哪些重點
- 執行計畫的最上層節點（Seq Scan、Index Scan 等）
- estimated rows / actual rows（如果可能）
- cost 與 actual time（Postgres）
小範例（PostgreSQL風的擬似EXPLAIN）
EXPLAIN ANALYZE SELECT id, name FROM products WHERE created_at >= '2024-01-01';
-- 範例輸出（摘要）
Index Scan using idx_products_created_at on products (cost=0.29..8.52 rows=10 width=64) (actual time=0.10..0.15 rows=12 loops=1)檢查清單
- [ ] 是否頻繁出現 Seq Scan？
- [ ] 實際的行數（actual rows）是否與預期相差很大？
8. 另外應該記住的小技巧（現場實用Tips）
- 啟用慢查詢日誌（slow query log），首先找到“火苗”
- 統計信息的更新（ANALYZE）如果過舊，將選擇不適當的計畫
- 監控索引的膨脹（特別是複合索引和函數索引）
- 查詢參數的型別是否一致（隱含型別轉換可能導致索引效率低下）
9. 總結與下一步
理解SQL背後發生了什麼，是成為強大工程師的必備技能。透過避免本文中的NG模式，並掌握批次處理和EXISTS的分別，可以顯著改善應用性能。
現在可以做的行動（3分鐘內完成）
- 將五個檢查清單應用到手邊的查詢中
- 確認慢查詢日誌，找出前10條記錄
- 對重要查詢執行
EXPLAIN (ANALYZE, BUFFERS)
🔥 最後：如果這篇文章對你有幫助
按讚、標記、追蹤 會是我的最大鼓勵！
未來也會持續發佈對初學者及資深人士有幫助的文章！！
我已經發佈了新文章！希望你會閱讀！
原文出處：https://qiita.com/Shiro_Shihi/items/b1b582c4528c5dd802f5
1) --- 會變成分隔線（上一行必須是空白）
2) # 會變成一級標題
3) ## 會變成二級標題
4) ### 會變成三級標題
5) **粗體文字**會顯示粗體文字
6) ```當第一行與最後一行會顯示程式碼
7) 請搜尋 Markdown 語法，了解各種格式
