嘿！在這篇文章中，我將分享一些簡單的方法來加速你的 SQL 查詢並使其更有效率。我們將看一些實際的例子（主要聚焦於PostgreSQL，儘管這些概念也適用於其他 SQL 資料庫）。到最後，你將能夠優化查詢以獲得更好的性能和更快的回應時間。
1. 將 IN 替換為在虛擬表上的 JOIN
問題：
在IN子句中擁有大量值可能導致對每一行進行順序檢查，從而過載 CPU。
解決方案：
使用虛擬表（帶有VALUES）並將其JOIN到主表。這通常能讓數據庫更有效地利用索引。
範例
-- BEFORE:
EXPLAIN
SELECT order_id, city
FROM orders
WHERE city IN ('Berlin', 'Paris', 'Rome');
-- AFTER:
EXPLAIN
SELECT o.order_id, o.city
FROM orders AS o
JOIN (
VALUES ('Berlin'), ('Paris'), ('Rome')
) AS v(city_name)
ON o.city = v.city_name;為什麼它更快：
通過將值列表視為表格，PostgreSQL 有時可以創建比使用冗長IN子句更優化的執行計劃。
2. 使用 ANY(ARRAY [ ]) 而不是 IN（僅限 PostgreSQL）
問題：
類似於第一個案例，一個大型的IN列表可能會拖慢查詢速度，因為每個可能的匹配都必須逐一檢查。
解決方案：
試試 PostgreSQL 專用的語法 = ANY(ARRAY[...])，它可以在找到匹配時立即短路。
範例
-- BEFORE:
EXPLAIN
SELECT product_id, quantity
FROM order_items
WHERE product_id IN (101, 202, 303, 404);
-- AFTER:
EXPLAIN
SELECT product_id, quantity
FROM order_items
WHERE product_id = ANY(ARRAY[101, 202, 303, 404]);為什麼它更快：ANY 一旦遇到匹配項即可停止檢查，從而可能減少比較的次數。
3. 使用 JOIN 代替相關子查詢
問題：
相關子查詢會為外部查詢中的每一行重複執行，導致對同一數據進行多次掃描。
解決方案：
盡可能將相關子查詢替換為常規的JOIN或非相關子查詢。
範例
-- BEFORE:
EXPLAIN
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.amount > 1000
);
-- AFTER (JOIN):
EXPLAIN
SELECT DISTINCT c.customer_id, c.name
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.amount > 1000;為什麼它更快：
A JOIN 允許 PostgreSQL 使用索引並避免多次執行相同的子查詢。
4. 使用BETWEEN代替日期函數
問題：
像 EXTRACT(YEAR FROM order_date) = 2023 這樣的表達式會阻止資料庫使用日期索引。對欄位應用函數會使 PostgreSQL 無法識別索引範圍。
解決方案：
使用BETWEEN與明確的日期範圍，以允許直接在order_date欄位上使用索引。
範例
-- BEFORE:
EXPLAIN
SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023
AND EXTRACT(MONTH FROM order_date) = 5;
-- AFTER:
EXPLAIN
SELECT *
FROM orders
WHERE order_date BETWEEN '2023-05-01'::DATE
AND '2023-05-31'::DATE;為什麼它更快：BETWEEN 用於索引日期欄位時，能夠直接進行索引範圍掃描，跳過對每一行的函數呼叫。
5. 依賴 EXISTS 而不是 JOIN 來檢查存在性
問題：
如果你只需要確認另一個表中是否存在至少一筆相關的記錄，使用JOIN可能會獲取比所需更多的資料。
解決方案：
使用EXISTS在找到匹配時立即停止。
範例
-- BEFORE:
EXPLAIN
SELECT COUNT(DISTINCT o.order_id)
FROM orders AS o
JOIN order_items AS i
ON o.order_id = i.order_id;
-- AFTER:
EXPLAIN
SELECT COUNT(DISTINCT o.order_id)
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM order_items AS i
WHERE i.order_id = o.order_id
);為什麼它更快：
查詢不會檢索不必要的行。一旦找到匹配的記錄，它就知道條件已滿足。
6. 其他實用技巧
- 僅選擇您需要的欄位
SELECT *會載入所有欄位，若你只需要少數幾個欄位，這可能會拖慢查詢速度。
2. 新增 LIMIT
- 如果你只需要一小部分行，請指定
LIMIT。這讓資料庫可以跳過掃描所有內容。
3. 避免在條件中使用函數
- 對於字串操作，優先使用
LIKE 'ABC%'而非SUBSTRING(field, 1, 3) = 'ABC'，以便保持索引的可用性。
4. 優化聚合
- 使用
FILTER（在 PostgreSQL 中）或CASE來進行條件計數，這可能比多個UNION或重複的子查詢更有效率。
範例
-- BEFORE:
EXPLAIN
SELECT SUM(CASE WHEN status = 'NEW' THEN 1 END) AS new_orders,
SUM(CASE WHEN status = 'CLOSED' THEN 1 END) AS closed_orders
FROM orders;
-- AFTER:
EXPLAIN
SELECT COUNT(*) FILTER (WHERE status = 'NEW') AS new_orders,
COUNT(*) FILTER (WHERE status = 'CLOSED') AS closed_orders
FROM orders;5. 使用邏輯表達式代替 CASE 進行簡單的布爾檢查
- 例如，
(table1.is_deleted OR table2.is_deleted)比一個大的CASE表達式更清晰且更快。
7. 替代 DISTINCT 的方法：ROW_NUMBER()
目標：
高效提取唯一值，特別是在大型數據集中。
解決方案：
有時候使用ROW_NUMBER()（並按鍵列分區）比使用DISTINCT更快，特別是當這些列被索引時。
範例
-- BEFORE:
EXPLAIN
SELECT COUNT(DISTINCT user_id)
FROM logins
WHERE login_date BETWEEN '2023-01-01'::DATE
AND '2023-01-31'::DATE;
-- AFTER:
EXPLAIN
SELECT COUNT(user_id)
FROM (
SELECT user_id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY user_id) AS rn
FROM logins
WHERE login_date BETWEEN '2023-01-01'::DATE
AND '2023-01-31'::DATE
) AS tmp
WHERE rn = 1;為什麼它更快：ROW_NUMBER() 通過在每個群組內分配行號來避免繁重的去重排序操作。
我的想法
SQL 查詢優化是任何數據庫「支援」專案中的主要步驟。通過使用策略，例如用虛擬表替換IN或ANY(ARRAY[])，利用BETWEEN處理日期，選擇EXISTS而非完整的JOIN，以及使用FILTER進行條件聚合，您很可能會看到性能的顯著提升。
記住：
- 避免使用
SELECT * - 明智地使用索引
- 在
WHERE子句中要注意函數調用 - 盡快過濾數據
- 保持查詢的可讀性和高效性