標籤: SQL 優化

嘿！在這篇文章中，我將分享一些簡單的方法來加速你的 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. 其他實用技巧

1. 僅選擇您需要的欄位

• 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子句中要注意函數調用
• 盡快過濾數據
• 保持查詢的可讀性和高效性