你的查詢為什麼這麼慢？從資料庫設計看後端工程師的基本功

身為後端工程師，CRUD 寫多了，很容易把資料庫當成黑盒子。遇到效能問題，第一反應就是「加個索引試試」。但索引不是萬靈丹，資料庫效能調優更不是玄學。本文將從資料庫設計的角度出發，帶你理解查詢背後的原理，擺脫「索引大法好」的迷思，真正掌握後端工程師的基本功。

索引不是萬靈丹

B-Tree 索引的原理

最常見的索引類型是 B-Tree 索引。它像一棵平衡樹，讓你可以快速找到特定值的資料列。想像一下，你要在一本沒有目錄的書裡找特定章節，只能一頁一頁翻。有了目錄（索引），就可以直接跳到目標頁面。

但索引並非免費午餐。每次新增、修改、刪除資料，索引也需要更新，這會增加寫入操作的成本。而且，索引會佔用額外的儲存空間。

Covering Index (覆蓋索引)

當你的查詢只需要索引就能提供所有需要的資料時，就稱為 Covering Index。這時候資料庫不需要回表 (table lookup) 查詢原始資料列，效能會大幅提升。

例如，假設我們有一個 users 表格，欄位有 id, name, email, age。

1
2
3
4
5
6
7
8


CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  age INT
);

CREATE INDEX idx_name_email ON users (name, email);

如果我們執行以下查詢：

1

SELECT name, email FROM users WHERE name = 'John Doe';

因為 idx_name_email 索引包含了 name 和 email 兩個欄位，資料庫可以直接從索引中取得結果，而不需要讀取 users 表格的原始資料。

過多索引的副作用

過多的索引會導致：

寫入效能下降：每次寫入操作都需要更新所有索引。
儲存空間增加：索引會佔用額外的儲存空間。
查詢最佳化器混亂：資料庫的查詢最佳化器可能會選擇錯誤的索引，導致效能下降。

因此，索引的設計需要謹慎考慮，只建立必要的索引。

正規化 vs 反正規化

正規化的優缺點

正規化旨在減少資料冗餘，提高資料一致性。它將資料分解成多個表格，並使用外鍵建立關聯。

優點：

資料一致性：避免資料冗餘導致的不一致。
更新效率：修改資料只需要更新一個地方。
儲存空間節省：減少資料冗餘。

缺點：

查詢複雜度增加：需要 JOIN 多個表格才能取得完整的資料。
查詢效能下降：JOIN 操作會增加查詢成本。

反正規化的優缺點

反正規化則允許資料冗餘，將相關的資料儲存在同一個表格中。

優點：

查詢效能提升：減少 JOIN 操作。
查詢複雜度降低：更容易取得完整的資料。

缺點：

資料一致性降低：資料冗餘可能導致不一致。
更新效率下降：修改資料需要更新多個地方。
儲存空間增加：增加資料冗餘。

什麼時候該為了效能犧牲正規化

在高流量系統中，查詢效能至關重要。如果某些查詢非常頻繁，且 JOIN 操作的成本很高，可以考慮反正規化。

例如，假設我們有一個 orders 表格和一個 customers 表格。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

如果我們經常需要查詢訂單的客戶名稱，可以將客戶名稱儲存在 orders 表格中，進行反正規化。

1

ALTER TABLE orders ADD COLUMN customer_name VARCHAR(255);

這樣就可以避免 JOIN 操作，提高查詢效能。但需要注意的是，在更新客戶名稱時，需要同時更新 orders 表格中的 customer_name 欄位，以保持資料一致性。

N+1 問題與批次查詢

什麼是 N+1 問題

N+1 問題是 ORM 使用者最常踩的坑之一。它指的是在查詢一個父物件時，需要額外執行 N 次查詢才能取得其關聯的子物件。

例如，假設我們有一個 posts 表格和一個 comments 表格。每個 post 可以有多個 comment。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  content TEXT
);

CREATE TABLE comments (
  id INT PRIMARY KEY,
  post_id INT,
  content TEXT,
  FOREIGN KEY (post_id) REFERENCES posts(id)
);

使用 ORM 查詢所有 post 及其對應的 comment，可能會產生 N+1 問題。

產生 N+1 查詢的 ORM 寫法 (Python Django)

1
2
3
4
5


# 產生 N+1 查詢
posts = Post.objects.all()
for post in posts:
  comments = post.comment_set.all()  # 每個 post 都會執行一次查詢
  print(f"Post: {post.title}, Comments: {len(comments)}")

這段程式碼會先執行一次查詢取得所有 post，然後針對每個 post 執行一次查詢取得其 comment。如果 post 的數量是 N，總共會執行 N+1 次查詢。

使用 eager loading 優化後的版本 (Python Django)

1
2
3
4
5


# 使用 eager loading 避免 N+1 查詢
posts = Post.objects.all().prefetch_related('comment_set')
for post in posts:
  comments = post.comment_set.all()  # comment_set 已經被 prefetch，不會再執行查詢
  print(f"Post: {post.title}, Comments: {len(comments)}")

這段程式碼使用 prefetch_related 進行 eager loading，在第一次查詢時就將所有 post 的 comment 一併取得。這樣只需要執行兩次查詢，一次取得所有 post，一次取得所有 comment。

使用 raw SQL 優化 (Python Django)

有時候，ORM 的優化仍然不夠，需要使用 raw SQL 才能達到最佳效能。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


from django.db import connection

def get_posts_with_comment_counts():
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT
                p.id,
                p.title,
                COUNT(c.id) AS comment_count
            FROM
                posts p
            LEFT JOIN
                comments c ON p.id = c.post_id
            GROUP BY
                p.id, p.title
        """)
        results = cursor.fetchall()
    return results

posts_with_counts = get_posts_with_comment_counts()
for post_id, title, comment_count in posts_with_counts:
    print(f"Post: {title}, Comments: {comment_count}")

這段程式碼使用 raw SQL 執行一個 JOIN 查詢，直接取得每個 post 的 comment 數量。只需要執行一次查詢，效能最高。

查詢效能分析：EXPLAIN

理解查詢在底層如何執行，是優化效能的關鍵。可以使用 EXPLAIN 指令來分析 SQL 查詢的執行計畫。

1

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

EXPLAIN 會顯示查詢使用的索引、掃描的資料列數、JOIN 的方式等等。透過分析 EXPLAIN 的結果，可以找出效能瓶頸，並進行優化。例如，如果發現查詢沒有使用索引，可以考慮建立索引。如果發現 JOIN 操作的成本很高，可以考慮反正規化。

總結

資料庫效能調優是一個複雜的課題，需要深入理解資料庫的原理和特性。本文介紹了索引、正規化/反正規化、N+1 問題等常見的效能問題，並提供了具體的解決方案。希望這些知識能幫助你擺脫「索引大法好」的迷思，真正掌握後端工程師的基本功，寫出高效能的程式碼。記住，資料庫不是黑盒子，理解你的查詢在底層如何執行，是後端工程師從 Junior 晉升的關鍵門檻。