# 資料庫觀念

---

## 指令類型

### 資料定義語言（DDL）

用來定義與管理**資料庫結構**。

包含：

* CREATE
* ALTER
* DROP
* TRUNCATE

---

### 資料操作語言（DML）

用來管理**資料內容**。

包含：

* SELECT
* INSERT
* UPDATE
* DELETE

---

### 資料控制語言（DCL）

用於管理資料庫的**存取權限與交易控制**。

包含：

* GRANT / REVOKE（權限控制）
* COMMIT（提交交易）
* ROLLBACK（回復交易）

---

## 資料庫索引

### 索引基本概念

* 多數 DBMS 會自動替 **Primary Key** 建立索引
* 搜尋邏輯基於 **Binary Search**
* 以「空間換時間」：索引本身是額外結構

---

### 索引結構

#### 叢集索引（Clustered Index）

* 一張表只能有一個
* 通常是主鍵
* 實際資料列依索引順序儲存
* 對連續範圍查詢效能最佳

#### 非叢集索引（Non-Clustered Index）

* 索引順序與實際資料順序無關
* 常用於 JOIN / WHERE / ORDER BY 的非主鍵欄位
* 索引過多會影響寫入與更新效能

---

## 刪除指令差異

| 指令 | DROP | TRUNCATE | DELETE |
| ----------- | ------- | -------- | ----------- |
| 速度 | 最快 | 中 | 最慢 |
| 影響範圍 | 結構 + 資料 | 僅資料 | 僅資料 |
| 可否 ROLLBACK | 否 | 否 | 是 |
| 自增值重置 | - | 是 | 否 |
| 執行方式 | 直接刪除物件 | 重建表結構 | 逐筆刪除並記錄 log |

---

## 指標分頁（Cursor Pagination）

避免使用 `LIMIT + OFFSET` 在大量資料時的效能問題。

### 核心概念

* 使用 **唯一鍵（通常是主鍵）** 作為分頁游標
* 以上一頁最後一筆資料作為起點

### 範例

-- 第一頁
SELECT * FROM users
ORDER BY id ASC
LIMIT 10;

-- 第二頁（上一頁最後一筆 id = 100）
SELECT * FROM users
WHERE id > 100
ORDER BY id ASC
LIMIT 10;

### 優點

* 不需掃描 offset 前的大量資料
* 大資料量時效能穩定

---

## FOR UPDATE（資料列鎖定）與 NOWAIT / SKIP LOCKED（並發控制）

SELECT seat_id FROM seats
WHERE event = 'X'
  AND section = 'A'
  AND not booked
FOR UPDATE SKIP LOCKED NOWAIT
LIMIT 1;

### 語法說明

* FOR UPDATE：對查詢到的資料列加上**排他鎖（Exclusive Lock）**，在交易提交或回滾前，其他交易無法修改或再次加鎖同一筆資料，用來確保資料一致性與避免重複選取資源。
* SKIP LOCKED：當查詢遇到已被其他交易鎖定的資料列時，**直接跳過這些資料列**，只回傳目前未被鎖定的資料，避免等待或阻塞。
* NOWAIT：若資料列已被其他交易鎖定，**不等待鎖釋放，立即回傳錯誤**，避免請求進入等待佇列

---

### FOR UPDATE 使用說明

#### FOR UPDATE 是什麼

`FOR UPDATE` 會對查詢結果中的資料列加上 **排他鎖（row-level exclusive lock）**，鎖定會持續到交易結束（COMMIT / ROLLBACK）。

在鎖定期間：

* 其他交易無法對這些資料列進行 UPDATE / DELETE
* 其他交易若也使用 FOR UPDATE，會被阻塞或失敗（取決於是否搭配 NOWAIT / SKIP LOCKED）

---

#### 什麼時候適合使用 FOR UPDATE

適合在「**先讀取，再修改**」且必須避免競爭條件（Race Condition）的流程中使用。

常見場景：

* 庫存扣減（先查庫存，再扣數量）
* 票務／座位選取（避免同一資源被多次選中）
* 金額或點數異動（避免重複計算）
* 任務或資源分派（確保同一筆資料只被一個 worker 處理）

---

#### 不適合使用 FOR UPDATE 的情境

* 純查詢、不會修改資料
* 長時間交易（會導致鎖定時間過久）
* 大量掃描但實際只會用到少量資料

此類情境容易造成鎖競爭與效能下降。

---

#### 使用 FOR UPDATE 的注意事項

* 務必搭配 **索引條件**，避免鎖到過多資料列
* 交易範圍要小、執行時間要短
* 高併發場景下，建議搭配 NOWAIT 或 SKIP LOCKED 控制等待行為

---

### SKIP LOCKED 使用說明

#### SKIP LOCKED 是什麼

`SKIP LOCKED` 在查詢時，**遇到已被其他交易鎖定的資料列會直接略過**，只回傳目前可取得鎖的資料列，不會等待。

它不會回傳錯誤，而是「盡量拿得到多少就拿多少」。

---

#### 什麼時候適合使用 SKIP LOCKED

適合在「**多個工作者同時消耗資源**」且每筆資料只需要被處理一次的場景。

常見場景：

* 任務佇列（job queue / background worker）
* 批次處理待辦事項
* 多人同時選擇不同資源（不在乎是哪一筆，只要可用即可）

---

#### SKIP LOCKED 的行為特性

* 不等待鎖定中的資料列
* 不保證資料連續或完整
* 查詢結果可能每次都不同（依當下鎖定狀態）

因此非常適合「吞吐量優先」而非「結果完整性優先」的流程。

---

#### 不適合使用 SKIP LOCKED 的情境

* 必須依序處理所有資料（不能跳過）
* 結果必須穩定、可重現
* 業務邏輯要求一定要取得指定那一筆資料

---

#### 常見搭配方式

SELECT id FROM jobs
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

* 多個 worker 同時執行
* 每個 worker 只會拿到不同的 job
* 避免 worker 彼此阻塞

---

### NOWAIT 使用說明

#### 適合使用的場景

* 高併發、即時回應需求高的系統
* 使用者操作需要快速成功或失敗結果
* 資源具唯一性且不可重複分配的情境

常見實例：

* 電商搶購／限量商品
* 票務選位系統
* 即時資源分配（伺服器、遊戲道具）

---

#### 有無 NOWAIT 的差異

**沒有 NOWAIT**

* 請求會等待鎖釋放
* 可能長時間阻塞
* 最後仍可能失敗（資源已被使用）

**使用 NOWAIT**

* 無法取得鎖時立即失敗
* 不佔用資料庫連線與資源
* 使用者可立即進行其他選擇

---

#### 設計上的核心思維

NOWAIT 的設計理念是：
**寧可快速失敗，也不要讓請求卡在資料庫層等待**。

適合用在「搶資源」而非「一定要成功」的流程設計中。

---

### 為什麼要用 NOWAIT

* 避免請求長時間等待
* 提升即時互動體驗
* 防止高併發下請求堆積

適合場景：

* 搶購系統
* 票務訂位
* 即時資源分配

---

### 有無 NOWAIT 的差異

**沒有 NOWAIT**

* 請求進入等待佇列
* 使用者可能等很久才失敗

**有 NOWAIT**

* 立即回應失敗
* 使用者可馬上選擇其他資源

---

## 使用者流程（以票務為例）

1. 瀏覽階段（無鎖）

* 查詢可用座位

2. 點選座位（加鎖）

* 執行 SELECT ... FOR UPDATE

3. 完成訂購

* COMMIT
* 釋放鎖定

---

## 鎖定策略比較

**策略 A：點選即上鎖**

* 優點：避免被搶
* 缺點：使用者猶豫會佔用資源

**策略 B：加入購物車才上鎖**

* 優點：使用者體驗佳
* 缺點：付款階段可能失敗

---

## 參考資料

* [https://medium.com/@jinghua.shih/rails-%E7%B6%B2%E7%AB%99%E6%95%88%E8%83%BD%E5%84%AA%E5%8C%96-%E4%BA%8C-%E8%B3%87%E6%96%99%E5%BA%AB%E7%B4%A2%E5%BC%95-database-index-bd89fa3757a](https://medium.com/@jinghua.shih/rails-%E7%B6%B2%E7%AB%99%E6%95%88%E8%83%BD%E5%84%AA%E5%8C%96-%E4%BA%8C-%E8%B3%87%E6%96%99%E5%BA%AB%E7%B4%A2%E5%BC%95-database-index-bd89fa3757a)

---

原始筆記連結：
[https://hackmd.io/@tian841224/HJu-wx7Cp](https://hackmd.io/@tian841224/HJu-wx7Cp)

資料庫設計層面
索引策略是最關鍵的。在關聯欄位（通常是外鍵）上建立索引是必須的，但要注意索引類型的選擇。叢集索引（Clustered Index）應該設在最常用的查詢條件上，非叢集索引（Non-Clustered Index）則用於關聯鍵和其他常用的篩選欄位。如果經常需要多欄位組合查詢，可以考慮複合索引。
**分割表（Table Partitioning）**對於超大資料量特別有效。可以按照日期、地區或其他業務邏輯來分割，這樣查詢時只需要掃描相關的分割區，大幅減少 I/O 負擔。
正規化與反正規化的平衡也很重要。雖然正規化能避免資料重複，但有時適度的反正規化（例如將常用的關聯欄位冗余存儲）能大幅提升查詢效能，只是要處理好資料一致性的問題。
查詢優化層面
避免 SELECT *，只選取需要的欄位。使用 INNER JOIN 而非 WHERE 子句來關聯表格，讓查詢優化器能更好地執行。如果只需要檢查存在性，使用 EXISTS 而非 IN。
對於超大量資料的關聯，可以考慮分批處理。例如先篩選出較小的資料集再進行關聯，或使用 TOP/OFFSET-FETCH 來分頁處理。
暫存表或臨時表在複雜查詢中很有用。先將中間結果存到暫存表，再進行後續的關聯操作，可以避免重複計算。
架構設計層面
如果是讀取密集的場景，可以考慮讀寫分離，使用主從複製讓查詢走從庫。或者建立物化視圖或彙總表，定期更新預先計算好的結果。
對於即時性要求不高的報表查詢，可以使用快取機制（Redis、Memcached）來存儲查詢結果。
資料分層架構也值得考慮，將熱資料和冷資料分開存放，歷史資料可以歸檔到較慢的儲存介質。
監控與調校
定期使用執行計畫分析（Execution Plan）來檢視查詢瓶頸，查看是否有表格掃描（Table Scan）或缺少索引的情況。關注統計資訊是否更新，因為過時的統計資訊會導致查詢優化器做出錯誤決策。
具體要採用哪些策略，需要根據你們的實際業務場景、查詢模式、資料增長速度和硬體資源來決定。如果你能分享更具體的需求，我可以提供更針對性的建議或程式碼範例。

postgresql 大量寫入效能較低

[OpenAI 如何將 PostgreSQL 擴展到支撐 8 億名使用者
](https://www.youtube.com/watch?v=1zVLBRIwCr0)