進階資料庫優化學習筆記

目標：讓資料庫在高併發、大資料量下依然穩定快速，直接對應「API 應對前端高頻呼叫」的核心需求。

---

連線池（Connection Pool）

為什麼需要連線池

每次建立資料庫連線都有成本（TCP 握手、身分驗證、資源配置）。連線池預先建立好一批連線放著，需要時借用、用完歸還，避免重複建立/關閉連線的開銷。

new PrismaClient() 背後已自動管理連線池，預設上限公式大致是：

連線數上限 = num_physical_cpus * 2 + 1

為什麼連線數不能亂設

資料庫本身有「同時能接受多少連線」的上限（PostgreSQL 預設約 100）。如果部署了多個服務實例，每個都各自開很大的連線池，加總可能超過資料庫負荷，導致新請求連不上、服務癱瘓。

設定方式（.env 的 DATABASE_URL 加參數）：

DATABASE_URL="postgresql://user@localhost:5432/db?connection_limit=10&pool_timeout=20"

• connection_limit：此實例最多同時開幾條連線

• pool_timeout：借不到連線時最多等幾秒，超過則丟出逾時錯誤而非無限卡住

粗略估算公式：資料庫總連線數上限 ÷ 同時運行的服務實例數 = 每實例的 connection_limit（保留餘裕給其他服務/管理工具）

---

交易（Transaction）：原子性保證

為什麼需要

一組操作（例如刪除使用者時要連同刪掉他的文章）必須「要嘛全部成功、要嘛全部失敗（rollback 復原）」，這個特性叫原子性（Atomicity），避免出現「刪了一半」的髒資料。

陣列寫法（操作彼此不相依）

await prisma.$transaction([
  prisma.post.deleteMany({ where: { authorId: userId } }),
  prisma.user.delete({ where: { id: userId } }),
]);

任何一步失敗，前面已執行的操作都會自動 rollback。

互動式交易（後面操作需要用到前面的查詢結果）

await prisma.$transaction(async (tx) => {
  const user = await tx.user.findUnique({ where: { id: userId } });
  if (!user) throw createAppError('使用者不存在', 404); // 拋錯會自動觸發 rollback

  await tx.post.deleteMany({ where: { authorId: userId } });
  await tx.user.delete({ where: { id: userId } });
});

tx 是交易專用的 Prisma Client，用法跟平常完全一樣。

---

樂觀鎖 vs 悲觀鎖：併發寫入衝突

問題情境

「先讀出數值、加 1、再寫回去」的寫法，在高併發下會遺失更新（Lost Update）：兩個請求同時讀到舊值，各自加 1 寫回，結果少算一次。

// ⚠️ 有問題的寫法
const post = await prisma.post.findUnique({ where: { id: postId } });
await prisma.post.update({
  where: { id: postId },
  data: { likeCount: post.likeCount + 1 },
});

悲觀鎖（Pessimistic Lock）

用 FOR UPDATE 鎖住該列，其他交易需排隊等待，保證正確但可能造成效能瓶頸：

await prisma.$transaction(async (tx) => {
  const post = await tx.$queryRaw`SELECT * FROM "Post" WHERE id = ${postId} FOR UPDATE`;
  await tx.post.update({ where: { id: postId }, data: { likeCount: post[0].likeCount + 1 } });
});

最推薦：資料庫原子操作

對「遞增/遞減」這類需求，直接叫資料庫自己做，不用先讀再算：

await prisma.post.update({
  where: { id: postId },
  data: { likeCount: { increment: 1 } }, // SQL: SET "likeCount" = "likeCount" + 1
});

整個「讀取 + 計算 + 寫入」在資料庫端原子性完成，不需額外加鎖，效能優於悲觀鎖。原則：遇到數值遞增/遞減，優先用 increment/decrement。

---

查詢效能分析：EXPLAIN ANALYZE

批次寫入測試資料

// 用 createMany 一次批次寫入，比迴圈裡一筆一筆 create 快非常多
await prisma.post.createMany({ data: postsData });

解讀執行計畫

EXPLAIN ANALYZE SELECT * FROM "Post" WHERE title = '某個標題';

沒有索引（Seq Scan，全表掃描）：

Seq Scan on "Post" (actual time=0.05..2.34 rows=50 loops=1)
  Filter: (title = '某個標題'::text)
  Rows Removed by Filter: 4950   -- 白工：檢查後被排除的筆數
Execution Time: 2.41 ms

有索引（Index Scan / Bitmap Index Scan）：

Bitmap Heap Scan on "Post" (actual time=0.03..0.08 rows=100 loops=1)
  ->  Bitmap Index Scan on "Post_authorId_idx"
        Index Cond: ("authorId" = 1)
Execution Time: 0.12 ms

資料量越大，兩者差距呈指數放大。核心心法：先量測，再優化，不要憑感覺猜。

---

複合索引（Composite Index）

情境

同時用多個欄位查詢/排序時（例如「查某作者、依時間排序」），單一欄位各自建索引無法疊加效果，需要複合索引：

model Post {
  @@index([authorId, createdAt])
}

順序很重要

@@index([authorId, createdAt]) ≠ @@index([createdAt, authorId])。

原則：常用來做精準比對（=）的欄位放前面，用來做範圍查詢或排序的欄位放後面。

SELECT * FROM "Post" WHERE "authorId" = 1 ORDER BY "createdAt" DESC LIMIT 10;
-- 對應索引順序：[authorId, createdAt]

---

快取策略：Redis

為什麼需要

同一份資料被高頻重複查詢，卻短時間內沒有變化，與其每次都查資料庫，不如把結果暫存在記憶體，速度快上百倍。

npm install redis

// lib/redis.js —— 整個專案共用同一個連線
import { createClient } from 'redis';
const redisClient = createClient({ url: process.env.REDIS_URL || 'redis://localhost:6379' });
redisClient.on('error', (err) => console.error('Redis 連線錯誤:', err));
await redisClient.connect();
export default redisClient;

Cache-Aside 模式

先問快取有沒有現成資料，沒有才查資料庫，查完存入快取：

const cacheKey = `posts:page=${page}:limit=${limit}`;

const cached = await redisClient.get(cacheKey);
if (cached) {
  const { data, meta } = JSON.parse(cached);
  return successResponse(res, 200, data, meta);
}

// 查資料庫...

await redisClient.set(cacheKey, JSON.stringify({ data: items, meta }), { EX: 60 }); // 60 秒後過期
successResponse(res, 200, items, meta);

快取失效策略（Cache Invalidation）

解法 1：TTL 過期時間（{ EX: 60 }）——最簡單，代價是「最多會有 N 秒資料不同步」，適合變動不頻繁的資料。

解法 2：主動清除——資料被修改時立刻刪除相關快取：

const keys = await redisClient.keys('posts:page=*');
if (keys.length > 0) {
  await redisClient.del(keys);
}

任何會改變資料的操作（新增/編輯/刪除）都要連帶清快取。

什麼資料適合快取

適合	不適合
查詢頻繁、變動不頻繁（文章列表、標籤）	每個使用者不同且需即時性（即時感測資料）
計算成本高（統計報表）	涉及金錢、庫存等零誤差需求
多人共用、非隱私資料	變動極度頻繁（快取幾乎瞬間失效）

---

讀寫分離（Read/Write Splitting）

核心概念

準備唯讀「複本（Replica）」資料庫，資料庫自動把主庫（Primary）的異動同步過去：

• 寫入（INSERT/UPDATE/DELETE）→ 一律打向 Primary

• 讀取（SELECT）→ 分散打向多台 Replica

複製延遲（Replication Lag）與最終一致性

Primary → Replica 的複製通常是非同步的，存在極短暫時間差。使用者剛寫入 Primary 後，緊接著讀取若打向 Replica，可能還讀不到最新資料——這叫最終一致性（Eventual Consistency）：資料最終會一致，但不保證立刻一致。

應對方式：

• 使用者自己剛寫入的資料，短時間內讀取導向 Primary（或直接用寫入 API 回傳的資料更新畫面）

• 一致性要求高的場景（帳戶餘額）讀取也導向 Primary

• 一致性要求低的場景（瀏覽列表）讀取導向 Replica，換取效能

Prisma 讀寫分離設定概念

import { readReplicas } from '@prisma/extension-read-replicas';

const prisma = new PrismaClient().$extends(
  readReplicas({ url: [process.env.REPLICA_URL_1, process.env.REPLICA_URL_2] })
);

await prisma.user.create({ data: {...} });        // 自動打向 Primary
await prisma.$replica().post.findMany();           // 明確指定打向 Replica

---

水平擴展與資料分片（Sharding）

當寫入流量大到單一台 Primary 也撐不住，可考慮把資料依規則拆分到多台資料庫（例如依 [user.id](user.id) 範圍分片）。

代價：跨分片的統計查詢、JOIN 變得困難，系統複雜度大幅提升，維運成本高。

---

資料庫優化的優先順序（核心心法）

1. 檢查查詢語法本身（N+1 問題、索引是否正確）

2. 加上適當快取，減少重複查詢

3. 資料庫伺服器規格加大（垂直擴展）

4. 讀寫分離，分散讀取壓力

5. 真的撐不住寫入流量，才考慮分片

不要一開始就上分片或多台資料庫——大部分中小型專案，把索引、N+1、快取做好，就能撐住相當大的流量。過早引入複雜架構會讓維護成本失控。