連線池與 N+1：應用層持久化#

資料庫調優不只發生在資料庫內部。一條 SQL 從應用程式發出前，要經過取得連線、準備語句、組裝事務等步驟；查詢回來後又要映射成物件。這些「應用層持久化（application-level persistence）」的環節常常才是延遲與吞吐的真正瓶頸。本章聚焦 JDBC 連線池與 ORM 場景下最常見的兩個殺手：連線管理失當與 N+1 查詢。範例以 Java/JDBC 為主，概念對其他語言同樣適用。資料庫內部的緩衝池見 08 Buffer Pool，連線數與 wait_timeout 等伺服器端參數見 06 效能調優。

為什麼必須要有連線池#

建立一條資料庫連線並不便宜。一次冷建連要付出：

sequenceDiagram
    participant App as 應用程式
    participant OS as 作業系統
    participant DB as MySQL

    App->>OS: TCP 三次握手
    OS->>DB: SYN / SYN-ACK / ACK
    App->>DB: TLS 握手（若啟用）
    App->>DB: 認證（帳密、權限載入）
    DB-->>App: 連線就緒
    Note over App,DB: 以上每次新連線都重來一遍<br/>累計可達數十毫秒

在沒有連線池時，每個請求都重走 TCP 握手 ➡️ TLS ➡️ 認證 ➡️ 用完關閉，握手成本往往高於查詢本身。連線池把連線建好後留存複用：

• 請求只需「借出 / 歸還」一條已就緒的連線，省去握手與認證。
• 池子上限同時充當並行閘門，保護資料庫不被瞬間湧入的連線打垮（MySQL 每條連線也要耗記憶體與執行緒）。

連線池大小不是越大越好。資料庫的有效並行受限於 CPU 核數與磁碟，連線數遠超過此值只會加劇上下文切換與鎖競爭。一個常被引用的經驗起點是 連線數 ≈ (核心數 × 2) + 有效磁碟數，再依壓測微調。池子過大常常比過小更傷。

連線池參數與生命週期#

以 HikariCP 為例的關鍵參數：

stateDiagram-v2
    [*] --> 建立: 池初始化 / 擴容
    建立 --> 空閒
    空閒 --> 使用中: getConnection() 借出
    使用中 --> 空閒: close() 歸還（非真正關閉！）
    空閒 --> 汰換: 超過 maxLifetime / idleTimeout
    汰換 --> [*]
    使用中 --> 洩漏: 忘記 close()
    洩漏 --> [*]: leakDetectionThreshold 報警

在連線池下，connection.close() 的語義是歸還給池，不是真正斷線。maxLifetime 必須設得比 MySQL 的 wait_timeout 短（例如 DB 8 小時、池設 30 分鐘），否則伺服器已悄悄關閉的連線仍被池視為可用，下次借出就得到一條「死連線」，引發隨機性 connection reset 錯誤。

PreparedStatement 與語句快取#

PreparedStatement（預備語句）用佔位符 ? 把 SQL 模板與參數分離，帶來兩個好處：

1. 防 SQL 注入：參數永遠當作值而非可執行片段。
2. 可被快取與複用：相同模板的執行計畫與解析結果可重用，省去重複的硬解析。

// 好：模板固定，可進語句快取，且防注入
String sql = "SELECT id, name FROM users WHERE dept_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setLong(1, deptId);
    try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}

// 壞：字串拼接，每次都是新 SQL 文字，無法快取，且有注入風險
String bad = "SELECT id, name FROM users WHERE dept_id = " + deptId;

在 MySQL JDBC 驅動上，需顯式開啟驅動端語句快取才真正受益：

# JDBC URL 參數
cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true

useServerPrepStmts=true 讓語句在伺服器端真正預備（server-side prepare）；否則驅動只在客戶端做佔位符替換。是否啟用伺服器端預備要壓測——它省了重複解析，但每條連線都會佔用伺服器的語句快取記憶體。

N+1 查詢：最常見的隱形效能黑洞#

N+1 是 ORM 延遲載入（lazy loading）下的典型反模式：先用 1 條查詢撈出 N 筆主物件，存取每筆的關聯時又各觸發 1 條查詢，總共 N+1 條。

flowchart TD
    Q0["1 條: SELECT * FROM orders 撈 N 筆訂單"] --> Loop
    subgraph Loop["遍歷每筆訂單"]
        Q1["SELECT * FROM customer WHERE id=?  (訂單 1)"]
        Q2["SELECT * FROM customer WHERE id=?  (訂單 2)"]
        Qn["... 共 N 條 ..."]
    end
    style Q0 fill:#fff3e0
    style Loop fill:#ffcdd2

對照解法——用一次 JOIN 或 IN 取代 N 條往返：

// N+1：每筆訂單再查一次客戶（延遲載入觸發）
List<Order> orders = orderRepo.findAll();          // 1 條
for (Order o : orders) {
    System.out.println(o.getCustomer().getName());  // 每次觸發 1 條，共 N 條
}

// 解法 A：JOIN FETCH 一次抓回（JPA/Hibernate）
// SELECT o FROM Order o JOIN FETCH o.customer
//   → 翻譯成單條帶 JOIN 的 SQL

// 解法 B：先收集 ID，用單條 IN 批次查詢
List<Long> custIds = orders.stream().map(Order::getCustomerId).distinct().toList();
Map<Long, Customer> byId = customerRepo.findAllByIdIn(custIds).stream()
        .collect(toMap(Customer::getId, c -> c));   // 1 條 IN 查詢

偵測手段：

• 開啟 ORM 的 SQL 日誌（Hibernate show_sql / hibernate.generate_statistics），觀察單一請求發出的查詢條數。
• 用 datasource-proxy / p6spy 攔截並計數每個請求的 SQL，超閾值即告警。
• 後端對照 06 效能調優 的慢查詢日誌——N+1 的單條都不慢，但總和驚人，所以常逃過慢查詢監控，必須在應用層計數。

N+1 之所以危險，在於它在小資料量的開發環境完全無感（N=3 時只是 4 條查詢），上線遇到 N=10000 才爆炸。把「每請求 SQL 條數」納入測試斷言，是最有效的防線。

批次寫入：往返次數與吞吐#

逐條 INSERT 同樣是 N 次往返。JDBC batch 把多條語句攢成一批一次送出：

String sql = "INSERT INTO events(type, payload) VALUES(?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (Event e : events) {
        ps.setString(1, e.type());
        ps.setString(2, e.payload());
        ps.addBatch();
        if (++count % 500 == 0) ps.executeBatch();   // 每 500 條送一批
    }
    ps.executeBatch();
}

批次大小（batch size）的取捨：

• 太小（如 1–10）：往返開銷攤不掉，吞吐提升有限。
• 太大（如 100000）：佔用大量記憶體、撐大單一事務的 undo 與鎖範圍（見 03 事務與鎖 的長事務危害），失敗時整批回滾成本高。
• 經驗甜蜜點通常落在 數百到數千 之間，需壓測。

MySQL JDBC 必須在 URL 加 rewriteBatchedStatements=true，驅動才會把多條 INSERT 真正改寫成單條多值的 INSERT ... VALUES (...),(...),(...)。不加這個參數，addBatch 仍是逐條送出，吞吐毫無改善——這是 Java 批次寫入最常見的踩坑。

事務與連線的生命週期綁定#

一個事務的所有語句必須跑在同一條連線上。因此事務開啟即「霸佔」一條池中連線直到提交/回滾：

• 事務越長，連線被佔越久，池可用連線越少——這把 03 的「長事務危害」放大到了連線池層面：長事務不只佔 undo 與列鎖，還佔住稀缺的池連線。
• 切忌在事務中夾雜遠端呼叫（HTTP/RPC）或慢運算：那會讓連線被一個與資料庫無關的等待綁死。
• 唯讀操作不要包進事務。

連線洩漏#

連線洩漏指借出後因例外或忘記 close() 而未歸還，可用連線被慢慢吃光，最終所有請求卡在 connectionTimeout：

// 正確：try-with-resources 保證任何路徑都歸還
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    // ...
}   // 自動 close() → 歸還池

# HikariCP 開啟洩漏偵測：連線借出超過此毫秒數未歸還即印堆疊
leakDetectionThreshold=20000

連線洩漏的典型症狀是「服務跑一段時間後請求集體逾時，重啟即恢復、過陣子又復發」。看到這種週期性退化，先查池的 active/idle 指標與 leakDetectionThreshold 日誌，再懷疑資料庫本身。

本章小結#

應用層持久化的優化主軸是減少往返與佔用：用連線池省握手、用批次與 JOIN 省往返、用短事務省佔用。資料庫內部再快，也救不了一個發出 10000 條 N+1 查詢的請求。