ORM 產生的 SQL：列表頁一次發出 200 條查詢

前七章的每一個工具，都要求你手上先有一句 SQL。在 ORM 專案裡，拿到那句 SQL 本身就是第一道關卡。

開發環境：看得到就好

spring:
  jpa:
    properties:
      hibernate.format_sql: true
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE   # 沒有這行就看不到參數值

另外不要用 spring.jpa.show-sql=true：它直接寫 System.out、不經過 logger、沒有時間戳、也無法分級關閉。

正式環境：要能回答「幾句、多久、誰發的」

日誌只能告訴你 SQL 長什麼樣。真正需要的三個數字——一個請求發了幾句、各自多久、從哪一行程式發出——要靠攔截層：

• datasource-proxy 或 p6spy：包住 DataSource，逐句記錄執行時間，並且可以按請求計數。
• Hibernate Statistics：hibernate.generate_statistics=true，可以取得一個 session 內的查詢次數、快取命中、實體載入數。
• SQL 註解標籤（ch07 那招）：讓資料庫端的 digest 對得回程式碼的端點。

// 整合測試裡直接把 N+1 擋下來——這是最划算的一道防線
@Test
void orderList_should_not_be_n_plus_1() {
    QueryCountHolder.clear();
    mockMvc.perform(get("/api/orders?page=0"));
    assertThat(QueryCountHolder.getGrandTotal().getSelect()).isLessThanOrEqualTo(3);
}

N+1 的定義很簡單：本來一句可以解決的事，變成 1 句主查詢 + N 句關聯查詢。但它的來源有三種，而且解法不完全一樣。

來源①：@ManyToOne 預設就是 EAGER

@Entity
public class Order {
    @ManyToOne                       // ← 沒寫 fetch，預設是 EAGER！
    private Member member;

    @ManyToOne
    private Merchant merchant;
}

來源②：LAZY，但在迴圈裡碰到了

List<Order> orders = repo.findByStatus(1);      // 1 句
for (Order o : orders) {
    dto.setMemberName(o.getMember().getName()); // 每一圈觸發一次 SELECT
}

這是最常見的一種，而且在 DTO 轉換層特別容易發生——因為那段程式碼看起來只是在搬欄位，完全沒有「查詢」的樣子。ch07 那個 820 萬次的案例正是這種。

來源③：集合的逐一初始化

for (Order o : orders) {
    total += o.getItems().size();   // 每一圈初始化一個集合 = 一句 SELECT
}

更糟的是巢狀：訂單 → 品項 → 商品，就是 20 → 200 → 2000。

怎麼一眼認出它

• 日誌裡連續出現一模一樣的 SQL，只有參數不同。
• 資料庫端：ch07 說的那個簽名——QPS 暴增但前端流量沒變，而且每句都很快。
• Hibernate Statistics 的 queryExecutionCount 與回傳筆數呈正比。

解法① JOIN FETCH / EntityGraph

@Query("SELECT o FROM Order o JOIN FETCH o.member JOIN FETCH o.merchant WHERE o.status = :s")
List<Order> findWithRefs(int s);

// 或用 EntityGraph，不必改 JPQL
@EntityGraph(attributePaths = {"member", "merchant"})
List<Order> findByStatus(int status);

• 適合 ToOne 關聯：一句 JOIN 就解決，資料不會重複。
• 對 ToMany 要小心：一對多的 JOIN 會讓主表欄位重複 N 次（笛卡爾積）；兩個以上的集合同時 fetch 會直接拋 MultipleBagFetchException，或產生列數相乘的災難。
• 配分頁會出事——下一節專講。

解法② @BatchSize（最務實，但最少人用）

@Entity
public class Order {
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    @BatchSize(size = 50)          // 或全域：hibernate.default_batch_fetch_size: 50
    private List<OrderItem> items;
}

Hibernate 會把 N 次單筆查詢合併成 WHERE order_id IN (?, ?, ... )：200 句變成 4 句。

解法③ DTO 投影（列表頁的最佳解）

@Query("""
    SELECT new com.shop.dto.OrderRow(o.id, o.amount, m.name, mc.name)
    FROM Order o JOIN o.member m JOIN o.merchant mc
    WHERE o.status = :s
    """)
List<OrderRow> findRows(int s);

• 一句 SQL 取齊需要的欄位，而且只取需要的欄位（回扣 ch04／ch06 的 SELECT * 三個代價）。
• 不進 persistence context：沒有 dirty checking 成本、沒有 lazy 載入的可能、記憶體用量小得多。

怎麼選

把 JOIN FETCH 和分頁放在一起，Hibernate 會照做，但會在日誌裡留下一行警告：

HHH000104: firstResult/maxResults specified with collection fetch;
           applying in memory

它實際做了什麼

正確做法：兩段式查詢

// 第一段：只查主表的 id，分頁在資料庫端完成（可以用 ch04 的 keyset）
@Query("SELECT o.id FROM Order o WHERE o.status = :s ORDER BY o.createdAt DESC")
List<Long> findIds(int s, Pageable page);

// 第二段：用 id 一次把集合抓齊，沒有分頁就不會觸發記憶體分頁
@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.id IN :ids")
List<Order> findWithItems(List<Long> ids);

或者更簡單：第一段照常分頁，集合關聯交給 @BatchSize——效果幾乎一樣，而且不必寫兩個方法。

順帶一提：Page 與 Slice

ch04 提過：Page 會多發一句 COUNT(*)。在 ORM 專案裡它還有一個額外的代價——那句 count 會把 JOIN FETCH 一起帶進去（Hibernate 通常會移除 fetch，但複雜查詢下不一定），變成一句昂貴又無用的統計。不需要總頁數時一律用 Slice。

Open Session In View（spring.jpa.open-in-view）預設是 true。Spring Boot 啟動時甚至會印一行 WARN 提醒你，但幾乎沒有人去讀它。

它做了什麼

它讓 Hibernate 的 session 一直開到 HTTP 回應寫完為止。好處很直接：controller 或序列化階段存取 lazy 關聯時不會拋 LazyInitializationException——它讓開發變順，代價藏在後面。

代價一：連線佔用時間 = 整個請求時間

代價二：把 N+1 藏起來

沒有 OSIV 的話，在 controller 碰 lazy 關聯會直接拋例外——問題在開發階段就暴露了。有了 OSIV，它不會拋例外，只會安靜地多發幾百句 SQL。ch07 那個案例能活到正式環境，OSIV 是共犯。

要不要關

spring:
  jpa:
    open-in-view: false

• 關掉的代價：所有需要的關聯都必須在 service 層（交易範圍內）明確載入完，否則序列化時就是 LazyInitializationException。
• 而這個代價其實是好事：它強迫你決定「這個 API 到底需要哪些資料」，而不是讓序列化過程去隨機觸發查詢。
• 務實建議：新專案一開始就關；既有專案先關在測試環境跑一輪，把爆出來的地方逐一改成 DTO 投影或明確 fetch。

寫入端有兩個預設值會讓「明明設定了批次」的努力白費。

設定批次的正確組合

spring:
  jpa:
    properties:
      hibernate.jdbc.batch_size: 500
      hibernate.order_inserts: true    # 把同型別的 INSERT 排在一起才批得起來
      hibernate.order_updates: true

陷阱①：JDBC URL 沒開 rewriteBatchedStatements

jdbc:mysql://host:3306/db?rewriteBatchedStatements=true

陷阱②：GenerationType.IDENTITY 會直接停用批次

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)   // MySQL 自增
private Long id;

推導一次為什麼：Hibernate 必須在 persist() 當下就拿到主鍵值（實體要進 persistence context 就得有 id），而自增值只有在 INSERT 真的執行之後才拿得到。於是它只能一句一句送，批次完全失效。

• MySQL 沒有 sequence，所以 JPA 這邊能做的有限（TABLE 策略有自己的競爭問題）。
• 實務解法：大量寫入直接繞過 ORM——用 JdbcTemplate.batchUpdate() 或原生的多值 INSERT。這不是妥協，是選對工具（見下一節）。

順帶：dirty checking 的成本

Hibernate 在 flush 時會逐一比對 session 裡每個實體的每個欄位與載入時的快照。一次載入五萬個實體，即使只改了一個，flush 也要比對五萬次。

@Transactional(readOnly = true)   // 讓 Hibernate 跳過 dirty checking
public List<OrderRow> list() { ... }

唯讀查詢一律加 readOnly = true：省下快照與比對成本，而且它同時是「這個方法不會寫資料」的宣告（Spring 也可以據此路由到唯讀複本）。DTO 投影則根本不進 persistence context，連快照都不會建。

這是 ch07 那場故障的另一半——同一件事，從應用端看。

徵狀

• 訂單列表 API 回應 800 毫秒（改版前 60 毫秒）。
• 資料庫端每一句都是 1~3 毫秒，慢查詢日誌完全是空的。
• 壓測時應用端 CPU 先滿，資料庫 CPU 隨後跟上。
• 本機開發完全感覺不到——因為本機資料庫在同一台機器，每次往返只要 0.1 毫秒。

診斷

先量最基本的數字：一個請求發了幾句 SQL。

// datasource-proxy 的計數結果
GET /api/orders?page=0  →  SELECT: 213, total time: 640ms

213 句。拆解來源：

修法（依序）

教訓

把 ORM 用在它不擅長的地方，是這一章大部分問題的共同起點。知道邊界在哪，比熟練任何技巧都有用。

ORM 擅長的

• 以聚合根為單位的 CRUD：載入一個訂單、改幾個欄位、存回去。dirty checking、樂觀鎖、關聯維護都在幫你。
• 領域邏輯集中在實體上的場景。

ORM 不擅長的（換工具）

混用時的兩個注意事項

• 原生 SQL 繞過了 persistence context。用 @Modifying 的批次 UPDATE 之後，session 裡的實體還是舊值——要加 clearAutomatically = true，或明確 flush／clear。
• 交易邊界仍然由 Spring 管：JdbcTemplate 與 JPA 在同一個 @Transactional 裡會共用同一條連線與同一個交易（前提是用同一個 DataSource），這點是安全的。

一個判斷句

要不要用 ORM 處理某段邏輯，問這個問題就好：

這一軌從 EXPLAIN 一路走到 ORM。表面上是八個不同的主題，但它們一直在講同樣的三件事。

① 估算 vs 實測

優化器看的是估算，你該看的是實測。

• ch01：rows／filtered 是估算，EXPLAIN ANALYZE 才是實測。
• ch02：估算來自 20 頁的採樣——它失準的時候，計畫就會很有邏輯地走錯。
• ch03：JOIN 把估算誤差乘上被驅動表的查詢次數。
• ch07：「感覺很慢」也是一種估算。先量再說——而且要量對東西（總耗時，不是最慢那句）。

② 代價守恆

每一個加速手段，都在別的地方付錢。沒有例外，只有「你知不知道帳單寄到哪裡」。

• 索引付寫入與空間（ch06）；hint 付未來的維護（ch02）。
• keyset 分頁付「不能跳頁」這個功能（ch04）；反範式付一致性維護（ch06）。
• 調大 buffer 付的是 Buffer Pool 的記憶體（ch03、ch04）；RC 隔離等級付的是幻讀（ch05）。
• OSIV 付的是連線佔用時間，而且帳單延後到正式環境的尖峰才寄到（本章）。

③ 先量再調

照「症狀 → 計畫 → 實測 → 根因」的順序走，不要一上來就調旋鈕。

• ch01：先看它打算做什麼，再問它為什麼慢。
• ch05：1205 找長交易、1213 找加鎖順序——症狀決定方向。
• ch07：症狀決定工具。用錯工具比不查更糟，因為它會給你「一切正常」這個最誤導的結論。
• 本章：而最後一站永遠要問——這句 SQL 為什麼會被要求執行？被要求了幾次？

還有一條沒有明說的線

如果只帶走一句