關聯式資料庫與 SQL

資料模型與正規化 基礎

關聯式資料庫（RDBMS）把資料存成「表（relation）」，每列（row / tuple）是一筆紀錄，每欄（column / attribute）有固定型別。它的核心價值有三個：宣告式查詢（SQL）、交易保證（ACID）、透過 schema 與約束（constraint）維持資料完整性。正規化（normalization）就是在設計 schema 時，透過拆表消除「資料重複」帶來的更新異常。

為什麼要正規化：三種異常

假設把廣告活動資訊和曝光紀錄塞進同一張大表 impressions(imp_id, campaign_id, campaign_name, advertiser_name, advertiser_email, ts, device)：

更新異常（update anomaly）

廣告主改 email，要更新上百萬列；只改到一部分就產生不一致。

插入異常（insertion anomaly）

新廣告活動還沒有任何曝光，就無處存放它的名稱。

刪除異常（deletion anomaly）

清掉某活動所有曝光（例如資料保存期限到了），連活動本身的資訊都一起消失。

1NF → 2NF → 3NF → BCNF

口訣：「The key, the whole key, and nothing but the key」——1NF 要有 key、2NF 依賴整個 key、3NF 只依賴 key。BCNF 是 3NF 的加強版，差別只在「候選鍵重疊」的少見情況。實務上做到 3NF 已經足夠。

何時反正規化（denormalization）

正規化減少重複、讓寫入簡單一致，但讀取時需要大量 JOIN。在讀多寫少、或資料量大到 JOIN 成本不可接受時，會刻意反正規化：

• 冗餘欄位：在 clicks 表冗餘存 campaign_id，報表查詢就不用再 JOIN impressions。
• 預先聚合表（summary / rollup table）：campaign_daily_stats(campaign_id, dt, impressions, clicks, spend)，由批次或串流任務更新，報表查這張表而不是掃原始事件。
• 計數快取欄位：posts.comment_count，避免每次 COUNT(*)。
• JSON 欄位：屬性稀疏且多變（例如 user attributes），用 MySQL JSON 或 PostgreSQL JSONB。

代價是：寫入要同步多處（或接受最終一致）、需要背景任務校正、資料不一致時要能修補。

Appier 情境：廣告活動 / 曝光 / 點擊表設計

CREATE TABLE campaigns (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  advertiser_id BIGINT UNSIGNED NOT NULL,
  name          VARCHAR(255)    NOT NULL,
  daily_budget  DECIMAL(12,4)   NOT NULL,         -- 金額用 DECIMAL，絕不用 FLOAT
  status        TINYINT         NOT NULL DEFAULT 0,
  start_at      DATETIME        NOT NULL,
  end_at        DATETIME        NOT NULL,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_adv_status (advertiser_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE impressions (
  id          BIGINT UNSIGNED NOT NULL,           -- 分散式 ID（如 Snowflake），趨勢遞增
  campaign_id BIGINT UNSIGNED NOT NULL,
  creative_id BIGINT UNSIGNED NOT NULL,
  user_id     VARCHAR(64)     NOT NULL,
  device      VARCHAR(16)     NOT NULL,
  cost        DECIMAL(12,6)   NOT NULL DEFAULT 0,
  ts          DATETIME(3)     NOT NULL,
  PRIMARY KEY (id, ts),                            -- 分區表的分區鍵必須包含在每個 unique key 中
  KEY idx_campaign_ts (campaign_id, ts)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (ts) (
  PARTITION p20261101 VALUES LESS THAN ('2026-11-02'),
  PARTITION p20261102 VALUES LESS THAN ('2026-11-03'),
  PARTITION pmax      VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE clicks (
  id            BIGINT UNSIGNED NOT NULL,
  impression_id BIGINT UNSIGNED NOT NULL,
  campaign_id   BIGINT UNSIGNED NOT NULL,         -- 反正規化冗餘，報表免 JOIN
  ts            DATETIME(3)     NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_imp (impression_id),              -- 同一曝光只算一次點擊（去重）
  KEY idx_campaign_ts (campaign_id, ts)
) ENGINE=InnoDB;

索引原理：為什麼是 B+ tree 高頻 進階

索引（index）是「用額外空間與寫入成本，換取查詢時不必全表掃描」的資料結構。要理解 InnoDB 為何選 B+ tree，得先理解瓶頸在磁碟 I/O：資料庫以「頁（page）」為單位在磁碟和記憶體（buffer pool）之間搬資料，InnoDB 預設頁大小是 16KB（innodb_page_size）。一次隨機 I/O 在 HDD 上約數毫秒、SSD 上約數十到一百多微秒，而記憶體存取約 100 奈秒——差了好幾個數量級。所以索引設計的目標是：讓一次查找需要讀的頁數（樹高）越少越好。

各種結構比較

B+ tree 的關鍵特性：

1. 只有葉子節點存資料（或指向資料），內部節點只存「key + 子頁指標」，所以一個 16KB 的內部頁能放上千個 key，扇出非常大、樹非常矮。
2. 所有查詢都走到葉子，路徑長度相同 → 效能穩定可預期。
3. 葉子節點以雙向鏈結串列串起，範圍查詢（BETWEEN、>、ORDER BY）找到起點後順著串列掃即可，不必回到上層。
4. 樹高通常 3～4 層，根節點與上層內部節點幾乎常駐 buffer pool，實際一次查找只需 1～2 次磁碟 I/O。

樹高估算（面試常考）

假設主鍵是 BIGINT（8 bytes），InnoDB 子頁指標約 6 bytes，內部節點每筆約 14 bytes：

• 每個內部頁可放約 16384 / 14 ≈ 1170 個指標。
• 假設每列資料 1KB，一個葉子頁約放 16 列。
• 樹高 2：1170 × 16 ≈ 1.9 萬列。
• 樹高 3：1170 × 1170 × 16 ≈ 2,190 萬列。
• 樹高 4：約 256 億列。

結論：千萬級資料的 B+ tree 只有 3 層。這也是「單表超過兩三千萬列要考慮分表」這個經驗法則的由來之一（但它不是硬限制，實際要看列寬、buffer pool 大小與存取模式）。如果主鍵改用 VARCHAR(64) UUID，內部節點每筆變大、扇出下降，樹也會變高。

索引設計：聚簇、回表、覆蓋、最左前綴 高頻

聚簇索引（clustered index）vs 二級索引（secondary index）

為什麼二級索引存主鍵而不是實體位址？因為聚簇索引的頁分裂、合併會移動資料列位置，存主鍵讓二級索引不必跟著更新。代價是：主鍵越長，所有二級索引都越肥，這也是不建議用長字串當主鍵的原因。

覆蓋索引（covering index）

如果查詢需要的欄位全都在二級索引裡（索引欄位 + 主鍵），就不必回表，EXPLAIN 的 Extra 會出現 Using index。

-- 索引 idx_campaign_ts (campaign_id, ts)，葉子實際存 (campaign_id, ts, id)
-- 覆蓋：只需要索引中的欄位
SELECT id, ts FROM impressions WHERE campaign_id = 7 AND ts >= '2026-11-01';

-- 需回表：cost 不在索引中
SELECT id, ts, cost FROM impressions WHERE campaign_id = 7 AND ts >= '2026-11-01';

-- 想讓上面也覆蓋：把 cost 加進索引（代價：索引更大、寫入更慢）
ALTER TABLE impressions ADD INDEX idx_campaign_ts_cost (campaign_id, ts, cost);

-- PostgreSQL 11+ 可以用 INCLUDE 把欄位只放在葉子、不參與排序
-- CREATE INDEX idx ON impressions (campaign_id, ts) INCLUDE (cost);

複合索引與最左前綴原則

複合索引 (a, b, c) 的 B+ tree 先按 a 排序，a 相同再按 b，再按 c——就像電話簿先按姓、再按名。所以只有「從最左邊開始連續使用」的條件能利用索引排序性：

設計原則：(1) 等值條件欄位放前面、範圍條件欄位放最後；(2) 高選擇性欄位優先（但要配合查詢模式，不是絕對）；(3) 盡量讓常用查詢變成覆蓋索引；(4) 考慮 ORDER BY/GROUP BY 能否直接利用索引順序；(5) 已有 (a, b) 就不需要單獨的 (a)，屬於冗餘索引。

索引選擇性（selectivity）

選擇性 = 不重複值數量 / 總列數，越接近 1 越好。gender、status 這種只有幾個值的欄位選擇性很低，單獨建索引通常沒用：優化器估算後會發現「走索引 + 大量回表」比全表掃描更慢，直接選擇全表掃描（經驗上，命中比例超過表的兩三成左右就可能放棄索引，實際由成本模型決定）。

-- 估算欄位選擇性
SELECT COUNT(DISTINCT device) / COUNT(*) AS sel_device,
       COUNT(DISTINCT user_id) / COUNT(*) AS sel_user
FROM impressions;

-- 長字串用前綴索引：找到夠高選擇性的最短前綴
SELECT COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) FROM landing_pages;
ALTER TABLE landing_pages ADD INDEX idx_url (url(20));  -- 代價：前綴索引無法做覆蓋索引與 ORDER BY

-- 查看優化器看到的統計資訊；統計過時可重算
SHOW INDEX FROM impressions;      -- Cardinality 欄位是估計值
ANALYZE TABLE impressions;

索引失效的常見情境

Hash 索引、全文索引與其他類型

Hash index

MEMORY 引擎支援顯式 HASH 索引；InnoDB 使用者只能建 B+ tree，但有內部的自適應雜湊索引（Adaptive Hash Index, AHI）：InnoDB 監測到某些索引頁被頻繁等值查詢時，自動在記憶體中為它們建 hash，加速查找。高併發下 AHI 的鎖可能成為瓶頸，有時會關掉（innodb_adaptive_hash_index=OFF）。PostgreSQL 有 USING HASH（10 版後才有 WAL 保護、可安全使用）。

Full-text index

InnoDB 5.6+ 支援 FULLTEXT，底層是倒排索引（inverted index）。中文、日文、韓文要用 WITH PARSER ngram。查詢用 MATCH(col) AGAINST('keyword' IN NATURAL LANGUAGE MODE)。功能與效能遠不及 Elasticsearch，適合輕量需求。

PostgreSQL 特殊索引

GIN（JSONB、陣列、全文檢索的倒排索引）、GiST（地理、範圍型別）、BRIN（Block Range Index，存每段頁的 min/max，適合與實體順序高度相關的大表，例如只追加的時間序列，索引極小）、部分索引（WHERE status = 'active'）、表達式索引。

唯一索引 vs 普通索引

唯一索引插入時必須讀頁檢查重複；普通索引可以利用 change buffer 把對二級索引頁的修改暫存，等該頁被讀進記憶體時再合併，減少隨機 I/O。寫多讀少的二級索引，若業務能保證唯一性，普通索引寫入較快。

-- 全文索引（含中文 ngram）
ALTER TABLE creatives ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
SELECT id, title FROM creatives
WHERE MATCH(title) AGAINST('運動鞋' IN NATURAL LANGUAGE MODE);

-- PostgreSQL：JSONB + GIN
-- CREATE INDEX idx_attrs ON user_profiles USING GIN (attrs jsonb_path_ops);
-- SELECT * FROM user_profiles WHERE attrs @> '{"country": "TW"}';

-- PostgreSQL：BRIN 適合按時間追加的大表
-- CREATE INDEX idx_imp_ts_brin ON impressions USING BRIN (ts);

EXPLAIN 解讀 高頻

「這條 SQL 很慢，你怎麼排查？」第一步就是看執行計畫。MySQL 的 EXPLAIN 顯示優化器預估的計畫；8.0.18+ 的 EXPLAIN ANALYZE 會真的執行並回報實際時間與列數。

EXPLAIN
SELECT c.name, COUNT(*) AS imps
FROM impressions i
JOIN campaigns c ON c.id = i.campaign_id
WHERE i.ts >= '2026-11-01' AND i.ts < '2026-11-02'
  AND c.advertiser_id = 3
GROUP BY c.name
ORDER BY imps DESC;

-- +----+-------+------+----------------+---------------+------+-----------------------------------------------+
-- | id | table | type | key            | ref           | rows | Extra                                         |
-- +----+-------+------+----------------+---------------+------+-----------------------------------------------+
-- |  1 | c     | ref  | idx_adv_status | const         |   12 | Using temporary; Using filesort               |
-- |  1 | i     | ref  | idx_campaign_ts| db.c.id       | 8200 | Using where; Using index                      |
-- +----+-------+------+----------------+---------------+------+-----------------------------------------------+

重要欄位

type 由好到壞

經驗法則：OLTP 查詢至少要到 range，最好是 ref 或更好。看到大表上的 ALL 或 index 要警覺。

Extra 常見值

Using index

覆蓋索引，不需回表。好事。

Using where

在 server 層對儲存引擎回傳的列再做過濾。

Using index condition

使用了 ICP，在引擎層用索引欄位先過濾。

Using filesort

無法利用索引順序，需要額外排序（記憶體中由 sort_buffer_size 決定，放不下會用磁碟暫存檔）。名稱有 file 但不一定落地。解法：讓 ORDER BY 欄位符合索引順序。

Using temporary

需要建立內部暫存表，常見於 GROUP BY、DISTINCT、UNION 欄位無法用索引時。大量資料時暫存表可能落地磁碟，成本高。

Using join buffer (hash join / Block Nested Loop)

被驅動表的 JOIN 欄位沒有索引。MySQL 8.0.18+ 以 hash join 取代 BNL，但通常仍代表該加索引。

Select tables optimized away

例如 MIN(id) 直接從索引兩端取得。

EXPLAIN ANALYZE（MySQL 8 與 PostgreSQL）

-- PostgreSQL：實際執行並顯示每個節點的真實時間、列數、緩衝區命中
EXPLAIN (ANALYZE, BUFFERS)
SELECT campaign_id, COUNT(*) FROM impressions
WHERE ts >= '2026-11-01' AND ts < '2026-11-02'
GROUP BY campaign_id;

-- HashAggregate  (cost=25432.10..25434.10 rows=200 width=16) (actual time=180.2..180.3 rows=152 loops=1)
--   Group Key: campaign_id
--   Buffers: shared hit=1200 read=8400
--   ->  Index Scan using idx_imp_ts on impressions  (cost=0.43..21000.00 rows=886000 width=8)
--                                                   (actual time=0.03..120.5 rows=901233 loops=1)
--         Index Cond: ((ts >= '2026-11-01') AND (ts < '2026-11-02'))
-- Planning Time: 0.2 ms
-- Execution Time: 180.6 ms

• cost=啟動成本..總成本是優化器估計的抽象單位；actual time 是毫秒、且是每個 loop 的平均值，要乘以 loops。
• 估計 rows 與實際 rows 差很多（例如估 10、實際 100 萬）是計畫選錯的主因 → 跑 ANALYZE 更新統計，或建立延伸統計（CREATE STATISTICS）。
• PG 節點類型：Seq Scan、Index Scan、Index Only Scan（需要 visibility map 夠新，否則仍要查 heap，Heap Fetches 會顯示）、Bitmap Heap Scan（先收集符合的頁再依序讀，適合中等選擇性）、Nested Loop / Hash Join / Merge Join。
• BUFFERS 的 shared hit 是 buffer cache 命中、read 是從 OS / 磁碟讀，能判斷冷熱快取對結果的影響。

-- MySQL 8.0.18+
EXPLAIN ANALYZE
SELECT * FROM impressions WHERE campaign_id = 7 ORDER BY ts DESC LIMIT 20;
-- -> Limit: 20 row(s)  (cost=... rows=20) (actual time=0.05..0.12 rows=20 loops=1)
--     -> Index lookup on impressions using idx_campaign_ts (campaign_id=7; iterate backwards)
--        (actual time=0.05..0.11 rows=20 loops=1)

-- 也可以用樹狀格式看預估計畫
EXPLAIN FORMAT=TREE SELECT ...;

交易與 ACID：各自如何實現 高頻 深入

交易（transaction）是一組要「全部成功或全部失敗」的操作。經典例子：廣告扣款時，「扣廣告主餘額」和「寫入扣款紀錄」必須一起成功。

Redo log 與 WAL（Write-Ahead Logging）

如果每次 commit 都把修改過的 16KB 資料頁寫回磁碟，會產生大量隨機寫，而且一頁只改了幾 bytes 也要寫整頁。WAL 的想法是：先把「改了什麼」以順序追加的方式寫到日誌，資料頁留在記憶體（dirty page），之後由背景執行緒批次刷回。順序寫比隨機寫快得多，且日誌記錄很小。

• redo log 是物理日誌（「第 X 號表空間第 Y 頁偏移 Z 改成某值」），固定大小、循環寫（8.0.30+ 由 innodb_redo_log_capacity 控制）。checkpoint 推進代表對應的 dirty page 已刷盤，那段 redo 可被覆寫。
• innodb_flush_log_at_trx_commit：1（預設）每次 commit 都寫入並 fsync，最安全；2 每次 commit 寫到 OS page cache、約每秒 fsync，MySQL 程序當掉不丟、但 OS 當機可能丟約 1 秒；0 每秒寫入並 fsync，MySQL 當掉就可能丟約 1 秒。
• Double write buffer：資料頁 16KB 但 OS/磁碟原子寫入單位通常較小，寫到一半當機會有「部分寫入頁（torn page）」，redo log 無法修復損毀的頁，所以 InnoDB 先把頁寫到 doublewrite 區再寫到原位置。PostgreSQL 用 full_page_writes（checkpoint 後第一次修改寫入整頁映像到 WAL）解決同樣問題。

Undo log

undo log 是邏輯日誌：INSERT 記錄「刪除這列」、DELETE 記錄「把這列加回來」、UPDATE 記錄「舊值」。它有兩個用途：rollback 與 MVCC 的版本鏈。每列資料有隱藏欄位 DB_TRX_ID（最後修改它的交易 ID）與 DB_ROLL_PTR（指向 undo log 中上一個版本），形成 undo chain。當沒有任何交易的 read view 需要舊版本時，purge 執行緒才會清除它們。所以長交易會讓 undo log 無法清理而膨脹，這是線上常見問題。

Binlog 與兩階段提交

binlog 是 MySQL Server 層的邏輯日誌（與引擎無關），記錄所有修改資料的事件，用於主從複製與時間點還原（PITR）、CDC（Debezium、Canal）。它和 redo log 的差異：

兩份日誌必須一致，否則主庫和從庫資料不同。MySQL 用內部的兩階段提交（2PC）：

崩潰復原時：若 redo 是 PREPARE 且 binlog 中有完整的對應交易 → 提交；binlog 沒有 → 回滾。如此保證 redo 與 binlog 一致。「雙 1 設定」（innodb_flush_log_at_trx_commit=1 + sync_binlog=1）是金融等級的安全設定，MySQL 用 group commit 把多個交易的 fsync 合併以降低成本。

隔離等級與 MVCC 高頻 深入

三種讀取異常

髒讀（dirty read）

讀到其他交易尚未 commit 的資料，而該交易之後 rollback 了。

不可重複讀（non-repeatable read）

同一交易內兩次讀同一列，結果不同（中間有人 UPDATE 並 commit）。

幻讀（phantom read）

同一交易內兩次範圍查詢，第二次多出（或少了）列（中間有人 INSERT/DELETE 並 commit）。

SERIALIZABLE 在 InnoDB 中是把所有普通 SELECT 隱式變成 SELECT ... FOR SHARE（加共享鎖），並行度很差；PostgreSQL 的 SERIALIZABLE 是 SSI（Serializable Snapshot Isolation），偵測到危險的讀寫相依時讓其中一個交易以 serialization failure 失敗，應用程式需重試。

MVCC 原理：read view + undo chain

MVCC（Multi-Version Concurrency Control）的核心是：寫入產生新版本，讀取依「快照」挑選可見的版本，所以普通的讀不必加鎖、讀寫不互相阻塞。InnoDB 中區分兩種讀：

• 快照讀（consistent read）：普通 SELECT，透過 read view 在 undo chain 中找可見版本，不加鎖。
• 當前讀（locking read）：SELECT ... FOR UPDATE / FOR SHARE、UPDATE、DELETE、INSERT，讀最新已提交版本並加鎖。

Read view 包含四個值：

m_ids

建立 read view 時所有活躍（未提交）交易的 ID 列表。

min_trx_id

m_ids 中最小值。

max_trx_id

建立時系統下一個要分配的交易 ID。

creator_trx_id

建立此 read view 的交易自己的 ID。

對某個版本，若其 DB_TRX_ID：

1. 等於 creator_trx_id → 自己改的，可見。
2. 小於 min_trx_id → 建立快照前就已提交，可見。
3. 大於等於 max_trx_id → 快照之後才開始的交易，不可見。
4. 介於兩者之間：在 m_ids 中 → 當時未提交，不可見；不在 → 已提交，可見。
5. 不可見就沿著 DB_ROLL_PTR 到 undo log 找上一個版本，重複判斷。

RC 與 RR 的差別就在 read view 的建立時機：

• READ COMMITTED：每個 SELECT 語句都建立新的 read view → 能看到其他交易剛提交的資料 → 不可重複讀。
• REPEATABLE READ：交易中第一次快照讀時建立 read view，之後重複使用 → 可重複讀。（START TRANSACTION WITH CONSISTENT SNAPSHOT 會立即建立。）

-- 兩個 session 示範 RR 下的快照讀 vs 當前讀
-- Session A                                   -- Session B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- 100
                                               UPDATE accounts SET balance = 50 WHERE id = 1;  -- autocommit
SELECT balance FROM accounts WHERE id = 1;   -- 仍是 100（快照讀）
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- 50（當前讀，讀最新）
UPDATE accounts SET balance = balance - 10 WHERE id = 1;  -- 基於 50 計算 → 40
SELECT balance FROM accounts WHERE id = 1;   -- 40（自己的修改可見）
COMMIT;

InnoDB 鎖、樂觀鎖與悲觀鎖、死結 高頻 深入

鎖的種類

共享鎖 S / 排他鎖 X

S 與 S 相容，其餘互斥。SELECT ... FOR SHARE（舊寫法 LOCK IN SHARE MODE）加 S；FOR UPDATE、UPDATE、DELETE 加 X。

記錄鎖（record lock）

鎖住索引記錄。重點：InnoDB 的行鎖是加在索引上的，若 UPDATE 的 WHERE 沒用到索引，會掃描並鎖住大量甚至全部記錄。

間隙鎖（gap lock）

鎖住兩個索引記錄之間的間隙，防止其他交易在其中插入，用來防幻讀。間隙鎖之間彼此相容（都只是「不准插入」）。僅在 RR 與 SERIALIZABLE 下用於搜尋與索引掃描。

臨鍵鎖（next-key lock）

記錄鎖 + 該記錄前面的間隙，即左開右閉區間 (prev, curr]。RR 下的預設加鎖單位，等值查詢唯一索引命中時會退化成記錄鎖。

插入意向鎖（insert intention lock）

INSERT 前在間隙上設定的特殊間隙鎖；多個交易插入同一間隙的不同位置不衝突，但會被其他交易的間隙鎖阻擋。

意向鎖（intention lock，IS / IX）

表級鎖。交易要對某列加 S/X 之前，先在表上加 IS/IX。目的：當有人要加表級鎖（例如 LOCK TABLES ... WRITE、某些 DDL）時，只要檢查表上的意向鎖，不必逐列檢查是否有行鎖。IS、IX 彼此相容，只和表級 S/X 衝突。

AUTO-INC 鎖

自增欄位分配用。8.0 預設 innodb_autoinc_lock_mode=2（interleaved），只用輕量 mutex，代價是批次插入的 ID 可能不連續。

MDL（metadata lock）

Server 層的表結構鎖。DDL 需要 MDL 寫鎖，若有長交易持有 MDL 讀鎖，DDL 會等待，而排在 DDL 後面的所有查詢也會被卡住——線上事故經典。

-- RR 下，假設 idx_age 上已有 age = 10, 20, 30
SELECT * FROM users WHERE age = 20 FOR UPDATE;
-- 非唯一索引等值：鎖住 (10, 20] 的 next-key lock + (20, 30) 的 gap lock
-- 其他交易 INSERT age = 15 或 25 會被阻塞；INSERT age = 35 不會

SELECT * FROM users WHERE id = 5 FOR UPDATE;
-- 主鍵等值且存在：只鎖 id = 5 這筆記錄（退化為 record lock）

-- 查看目前的鎖與等待（MySQL 8.0）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits\G

悲觀鎖 vs 樂觀鎖

import pymysql

conn = pymysql.connect(host="127.0.0.1", user="app", password="secret",
                       database="ads", autocommit=False)

# --- 悲觀鎖：扣廣告主餘額 ---
def charge_pessimistic(advertiser_id: int, amount_micros: int) -> bool:
    with conn.cursor() as cur:
        try:
            cur.execute("SELECT balance_micros FROM advertisers WHERE id = %s FOR UPDATE",
                        (advertiser_id,))
            (balance,) = cur.fetchone()
            if balance < amount_micros:
                conn.rollback()
                return False
            cur.execute("UPDATE advertisers SET balance_micros = balance_micros - %s WHERE id = %s",
                        (amount_micros, advertiser_id))
            cur.execute("INSERT INTO charges (advertiser_id, amount_micros) VALUES (%s, %s)",
                        (advertiser_id, amount_micros))
            conn.commit()
            return True
        except Exception:
            conn.rollback()
            raise

# --- 樂觀鎖：更新 campaign 設定（後台編輯，衝突少） ---
def update_budget_optimistic(campaign_id: int, new_budget: str, max_retry: int = 3) -> bool:
    for _ in range(max_retry):
        with conn.cursor() as cur:
            cur.execute("SELECT version FROM campaigns WHERE id = %s", (campaign_id,))
            (version,) = cur.fetchone()
            affected = cur.execute(
                "UPDATE campaigns SET daily_budget = %s, version = version + 1 "
                "WHERE id = %s AND version = %s",
                (new_budget, campaign_id, version))
            conn.commit()
            if affected == 1:
                return True
        # affected == 0：被其他人搶先修改，重讀後重試（或回報 409 Conflict 給前端）
    return False

# --- 最簡單也最常被忽略的：單一原子 UPDATE，不需要先 SELECT ---
def charge_atomic(advertiser_id: int, amount_micros: int) -> bool:
    with conn.cursor() as cur:
        affected = cur.execute(
            "UPDATE advertisers SET balance_micros = balance_micros - %s "
            "WHERE id = %s AND balance_micros >= %s",
            (amount_micros, advertiser_id, amount_micros))
        conn.commit()
        return affected == 1

死結（deadlock）範例與處理

InnoDB 預設開啟死結偵測（innodb_deadlock_detect=ON），用 wait-for graph 找環，回滾「代價較小」（修改列數較少）的交易，回傳錯誤碼 1213。另外還有 innodb_lock_wait_timeout（預設 50 秒）處理單純的長時間鎖等待（錯誤碼 1205）。極高併發熱點更新時，死結偵測本身會耗費大量 CPU，有人會關閉偵測改靠 timeout，但更好的做法是從業務上消除熱點。

避免死結的方法：

• 固定加鎖順序：轉帳時永遠先鎖 ID 較小的帳戶。
• 縮短交易：交易中不要有 RPC、使用者互動、sleep；先算好再開交易。
• 確保 UPDATE/DELETE 的 WHERE 走索引，避免鎖大量列。
• 批次操作按主鍵排序後再執行。
• 在可接受的場景使用 READ COMMITTED，減少間隙鎖（很多網際網路公司會把 MySQL 設成 RC + ROW binlog）。
• 應用程式要捕捉 1213 並重試整個交易，死結無法 100% 避免。

import random
import time
import pymysql

DEADLOCK = 1213
LOCK_WAIT_TIMEOUT = 1205

def run_in_tx(conn, fn, retries: int = 3):
    """執行交易函式，遇到死結或鎖等待逾時就以指數退避重試。"""
    for attempt in range(retries):
        try:
            with conn.cursor() as cur:
                result = fn(cur)
            conn.commit()
            return result
        except pymysql.err.OperationalError as e:
            conn.rollback()
            if e.args[0] in (DEADLOCK, LOCK_WAIT_TIMEOUT) and attempt < retries - 1:
                time.sleep((2 ** attempt) * 0.05 + random.uniform(0, 0.05))
                continue
            raise

def transfer(cur, src: int, dst: int, amount: int):
    first, second = sorted((src, dst))          # 固定加鎖順序避免死結
    cur.execute("SELECT id FROM accounts WHERE id IN (%s, %s) ORDER BY id FOR UPDATE",
                (first, second))
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))

# run_in_tx(conn, lambda cur: transfer(cur, 7, 3, 100))

查詢優化實戰 進階

N+1 查詢問題

先查 1 次拿到 N 個 campaign，再對每個 campaign 各查 1 次它的 creatives，共 N+1 次往返。每次往返即使只有 0.5ms，N = 1000 就是半秒，而且還會打滿連線池。ORM 的 lazy loading 最容易不知不覺造成 N+1。

from sqlalchemy import select
from sqlalchemy.orm import selectinload, joinedload

# 壞：N+1（每次存取 c.creatives 觸發一次 lazy load 查詢）
campaigns = session.scalars(select(Campaign).where(Campaign.advertiser_id == 3)).all()
for c in campaigns:
    print(c.name, len(c.creatives))

# 好 1：selectinload → 共 2 次查詢：SELECT campaigns...; SELECT creatives WHERE campaign_id IN (...)
stmt = select(Campaign).where(Campaign.advertiser_id == 3).options(selectinload(Campaign.creatives))

# 好 2：joinedload → 1 次 LEFT OUTER JOIN（一對多時會讓父列重複，資料量大時傳輸較浪費）
stmt = select(Campaign).where(Campaign.advertiser_id == 3).options(joinedload(Campaign.creatives))

# 不用 ORM 時：手動批次 + 在記憶體中分組
ids = [c.id for c in campaigns]
rows = session.execute(select(Creative).where(Creative.campaign_id.in_(ids))).scalars()
by_campaign = {}
for cr in rows:
    by_campaign.setdefault(cr.campaign_id, []).append(cr)

分頁：OFFSET 的問題與 keyset pagination

LIMIT 20 OFFSET 1000000 看似只回 20 列，但資料庫必須先掃過並丟棄前一百萬列（若需要回表，就是一百萬次回表），越往後越慢。此外資料在翻頁期間有新增 / 刪除時，會出現重複或漏掉的列。

-- 壞：深分頁
SELECT id, ts, cost FROM impressions
WHERE campaign_id = 7
ORDER BY ts DESC, id DESC
LIMIT 20 OFFSET 1000000;

-- 改善 1：延遲關聯（deferred join），先在覆蓋索引上找出主鍵，再回表 20 列
SELECT i.id, i.ts, i.cost
FROM impressions i
JOIN (
  SELECT id FROM impressions
  WHERE campaign_id = 7
  ORDER BY ts DESC, id DESC
  LIMIT 20 OFFSET 1000000
) t ON t.id = i.id
ORDER BY i.ts DESC, i.id DESC;

-- 最佳：keyset / seek / cursor pagination，用上一頁最後一筆當游標
-- 需要索引 (campaign_id, ts, id)；排序鍵必須唯一，所以加上 id 打破平手
SELECT id, ts, cost FROM impressions
WHERE campaign_id = 7
  AND (ts, id) < ('2026-11-01 10:00:00.123', 987654321)   -- 上一頁最後一筆
ORDER BY ts DESC, id DESC
LIMIT 20;

Keyset 的代價：無法直接跳到第 N 頁、只能上一頁/下一頁（正好符合無限捲動與 API cursor）。API 設計上通常把 (ts, id) 編碼成不透明的 base64 cursor 回給客戶端。

COUNT(*) 的成本

• InnoDB 沒有儲存精確的總列數，因為 MVCC 下不同交易看到的列數不同。COUNT(*) 需要掃描，優化器會挑最小的二級索引來掃（比聚簇索引小）。MyISAM 有存，但不支援交易。
• COUNT(*) = COUNT(1)（InnoDB 處理方式相同），都計算所有列；COUNT(col) 只計算 col 非 NULL 的列，語意不同。
• 大表的「總筆數」需求：(1) 用估計值 information_schema.TABLES.TABLE_ROWS 或 PG 的 pg_class.reltuples；(2) 用 Redis 或計數表維護（注意與 DB 的一致性）；(3) 產品面：顯示「10,000+」而非精確值；(4) 分析型查詢丟給 OLAP。

大表 ALTER：Online DDL、gh-ost、pt-osc

對上億列的表加欄位 / 加索引，若鎖表或複製整張表，可能持續數小時並阻塞寫入、造成複製延遲。

-- 明確指定演算法，若不支援會直接報錯而不是默默鎖表
ALTER TABLE campaigns ADD COLUMN bid_strategy VARCHAR(32) NULL, ALGORITHM=INSTANT;
ALTER TABLE impressions ADD INDEX idx_user_ts (user_id, ts), ALGORITHM=INPLACE, LOCK=NONE;

-- 執行 DDL 前先檢查有沒有長交易，避免 MDL 等待連鎖阻塞
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS secs, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;

gh-ost \
  --host=replica-1 --database=ads --table=impressions \
  --alter="ADD INDEX idx_user_ts (user_id, ts)" \
  --chunk-size=1000 --max-lag-millis=1500 \
  --max-load=Threads_running=50 \
  --cut-over=default --exact-rowcount --concurrent-rowcount \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

其他常見優化手法

• 只 SELECT 需要的欄位，避免 SELECT *（無法覆蓋索引、傳輸浪費、schema 變更時易出錯）。
• 批次寫入：多列 INSERT ... VALUES (...), (...) 或 LOAD DATA，一次交易提交多筆，大幅減少 fsync 與往返。
• 大量刪除 / 更新分批：DELETE ... WHERE ts < ? LIMIT 5000 迴圈執行，避免巨型交易造成 undo 膨脹與複製延遲；能用 DROP PARTITION 更好。
• Upsert：MySQL INSERT ... ON DUPLICATE KEY UPDATE、PG INSERT ... ON CONFLICT DO UPDATE，用於彙總計數。
• 避免在大表上 ORDER BY RAND()，改用隨機 ID 範圍。
• JOIN 欄位兩邊型別與字元集一致、被驅動表的 JOIN 欄位要有索引；小表驅動大表。

-- 串流處理每分鐘把聚合結果寫回 MySQL 的彙總表（冪等 upsert）
INSERT INTO campaign_hourly_stats (campaign_id, hour, impressions, clicks, spend_micros)
VALUES (7, '2026-11-03 10:00:00', 1200, 15, 3600000),
       (8, '2026-11-03 10:00:00',  800,  4, 1900000)
ON DUPLICATE KEY UPDATE
  impressions  = impressions  + VALUES(impressions),
  clicks       = clicks       + VALUES(clicks),
  spend_micros = spend_micros + VALUES(spend_micros);
-- 8.0.20+ 建議改用別名語法：VALUES (...) AS new ON DUPLICATE KEY UPDATE impressions = impressions + new.impressions
-- 注意：「累加」型 upsert 遇到重送會重複計算；需要 exactly-once 時改為「覆寫」型（直接寫入該批次計算出的總值）或記錄批次 ID

複製（Replication）與讀寫分離 進階

MySQL 複製流程：主庫寫 binlog → 從庫的 I/O thread 拉取 binlog 寫入本地 relay log → SQL thread（或多個 applier worker，平行複製）重放。用途：讀取擴展、高可用（主庫故障時提升從庫）、備份、分析查詢隔離。

非同步 / 半同步 / 群組複製

Binlog 格式

複製延遲與 read-your-writes

原因：從庫重放是單執行緒或有限平行（主庫是多執行緒併發寫入）、大交易（一次 DELETE 千萬列，從庫要重放同樣久）、從庫機器較弱、從庫上跑重型查詢、無主鍵表的 ROW 格式重放（每列都要全表掃描）。監控 SHOW REPLICA STATUS 的 Seconds_Behind_Source（不精準，可用 pt-heartbeat 寫心跳表量測）。

最常見的問題是使用者剛寫入就讀不到自己的資料（例如剛建立的廣告活動在列表中沒出現）。解法：

1. 寫後短時間內讀主庫：使用者寫入後在 session / cookie 記錄時間戳，N 秒內該使用者的讀取導向主庫。
2. 關鍵讀永遠走主庫：例如付款、餘額、設定頁。
3. GTID 等待：寫入後取得 GTID，讀從庫前執行 WAIT_FOR_EXECUTED_GTID_SET(gtid, timeout)，確認從庫已追上。
4. 前端樂觀更新：直接用寫入回應的資料更新畫面。
5. 延遲過大時自動把該從庫移出讀取池。

import time
from contextvars import ContextVar

READ_PRIMARY_WINDOW = 3.0   # 秒；需大於一般複製延遲
_last_write_at: ContextVar[float] = ContextVar("last_write_at", default=0.0)

class RoutingDB:
    """簡化的讀寫分離路由：寫走 primary；最近寫過的請求讀也走 primary。"""
    def __init__(self, primary_pool, replica_pools):
        self.primary = primary_pool
        self.replicas = replica_pools
        self._rr = 0

    def for_write(self):
        _last_write_at.set(time.monotonic())
        return self.primary

    def for_read(self, critical: bool = False):
        if critical or time.monotonic() - _last_write_at.get() < READ_PRIMARY_WINDOW:
            return self.primary
        self._rr = (self._rr + 1) % len(self.replicas)
        return self.replicas[self._rr]
# 實務上「最近寫入時間」要跨 request 保存（cookie / session / Redis，以 user_id 為 key）

高可用與故障切換

主庫故障時要選出資料最新的從庫提升為主庫，並讓其他從庫改指向它、讓應用程式改連新主庫（VIP / DNS / proxy）。工具：MHA（舊）、Orchestrator、MySQL InnoDB Cluster、雲端託管（AWS RDS Multi-AZ 用同步複製的 standby、Aurora 共享儲存層）。要注意腦裂（split brain）：舊主庫恢復後仍接受寫入，需要 fencing（例如把舊主庫設 read_only、在 proxy 層隔離）。GTID 讓從庫切換主庫時不必手算 binlog 位置。

分庫分表與連線池 進階

單機 MySQL 的極限可能在寫入 QPS、儲存容量、或單表大小造成的索引與維運成本。擴展順序通常是：SQL 與索引優化 → 加快取 → 讀寫分離 → 垂直拆分 → 水平分片。分片是最後手段，因為它會大幅增加複雜度。

垂直 vs 水平

Shard key 選擇與分片策略

選 shard key 的原則：

• 最常見的查詢都帶有它，讓查詢只打一個分片（例如 SaaS 以 tenant_id / advertiser_id，使用者資料以 user_id）。
• 高基數、分布均勻，避免熱點（不要用 country、status）。
• 相關資料落在同一分片，使交易與 JOIN 留在單機。
• 需留意超大租戶（一個大廣告主的資料量是小廣告主的萬倍），可能需要對大客戶二次拆分。
• 預先切成較多的邏輯分片（例如 1024 個），再映射到少量實體庫，擴容時搬移邏輯分片而非重新雜湊。

分片後的問題

跨分片查詢

不帶 shard key 的查詢要 scatter-gather 到所有分片再合併，延遲由最慢的分片決定。解法：建立以其他欄位為 key 的「索引表 / 異構副本」，或同步到 Elasticsearch / OLAP 處理分析與搜尋。

跨分片排序分頁

取第 N 頁要每片都取前 N×size 再合併，深分頁極慢；改用 keyset。

跨分片交易

需要 2PC / XA 或 Saga 等分散式交易模式（見 第 10 章 分散式系統）。設計上盡量避免。

全域唯一 ID

不能再用各庫的 AUTO_INCREMENT。方案：Snowflake（時間戳 + 機器 ID + 序號）、號段模式（Leaf segment）、UUID v7，詳見 第 10 章。

擴容與資料遷移

雙寫 + 回填 + 校驗 + 切換，或使用支援線上 resharding 的中介層。

工具：Vitess（YouTube 開源，MySQL 分片中介層，支援線上 resharding）、ShardingSphere、Citus（PostgreSQL 擴充）；或直接選分散式 SQL：TiDB、CockroachDB、Google Spanner、Aurora（只擴展讀與儲存，寫仍是單主）。

import hashlib

LOGICAL_SHARDS = 1024
# 邏輯分片 → 實體資料庫的對照（擴容時只改這張表並搬移對應邏輯分片）
SHARD_MAP = {range(0, 512): "mysql-a", range(512, 1024): "mysql-b"}

def logical_shard(advertiser_id: int) -> int:
    h = hashlib.md5(str(advertiser_id).encode()).digest()
    return int.from_bytes(h[:8], "big") % LOGICAL_SHARDS

def physical_db(advertiser_id: int) -> str:
    s = logical_shard(advertiser_id)
    for r, db in SHARD_MAP.items():
        if s in r:
            return db
    raise KeyError(s)

# 表名也可按邏輯分片命名：campaigns_0000 ... campaigns_1023
def table_name(advertiser_id: int) -> str:
    return f"campaigns_{logical_shard(advertiser_id):04d}"

連線池（connection pool）

為何需要：建立一條 MySQL 連線要 TCP 三次交握、（可能的）TLS 交握、認證、配置 session，耗時毫秒級；MySQL 預設每條連線一個執行緒（max_connections 預設 151），PostgreSQL 更是每條連線一個行程（數 MB 記憶體）。若每個 request 都開新連線，延遲高且資料庫很快被連線數壓垮。連線池預先建立並重複使用連線。

大小怎麼設：

• 不是越大越好。資料庫能真正並行執行的數量受 CPU 核心數與磁碟限制，連線過多只會增加 context switch 與鎖競爭，吞吐反而下降。
• 常見起點公式（出自 PostgreSQL wiki，HikariCP 文件引用）：connections = (核心數 × 2) + 有效磁碟數。這是資料庫端的總量起點，再以壓測調整。
• 總連線數 = 每個 instance 的池大小 × instance 數。K8s 上服務擴到 100 個 pod，每個 pod 池大小 20 → 2000 條連線，很容易超過 DB 上限。需要在中間加 ProxySQL / PgBouncer（transaction pooling）/ RDS Proxy 做連線多工。
• 重要參數：pool_size、max_overflow、取連線逾時 pool_timeout（應快速失敗而非無限等待）、pool_recycle（小於 MySQL wait_timeout，預設 8 小時，避免拿到被 server 關掉的連線）、pool_pre_ping（借出前檢查）。

from sqlalchemy import create_engine, text

engine = create_engine(
    "mysql+pymysql://app:secret@db-primary:3306/ads?charset=utf8mb4",
    pool_size=10,          # 常駐連線數
    max_overflow=5,        # 尖峰時可額外建立的連線數
    pool_timeout=2,        # 取不到連線 2 秒就拋例外（fail fast，避免請求堆積）
    pool_recycle=1800,     # 30 分鐘回收，避免被 server 端 wait_timeout 或 LB 斷線
    pool_pre_ping=True,    # 借出前 ping，自動丟棄壞連線
)

with engine.connect() as conn:          # 離開 with 自動歸還連線池（不是關閉 TCP）
    rows = conn.execute(text("SELECT id, name FROM campaigns WHERE advertiser_id = :aid"),
                        {"aid": 3}).all()

# 監控：engine.pool.status() → 目前借出、閒置、overflow 數量；應輸出成 metrics

SQL 面試題實戰區（15 題） 高頻

以下題目以 MySQL 8.0 語法為主（支援 window function 與 CTE），PostgreSQL 差異會註明。面試時的答題節奏：先確認表結構與邊界條件（NULL、平手、重複、時區）→ 講思路 → 寫 SQL → 說明索引與效能。

先記住 SQL 的邏輯執行順序，很多題目的錯誤都源於此：

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT（含 window function）→ DISTINCT → ORDER BY → LIMIT/OFFSET

所以：WHERE 不能用 SELECT 中定義的別名、不能用聚合函數；window function 的結果不能直接放在 WHERE，要包一層子查詢或 CTE；ORDER BY 可以用別名。

題 1：JOIN 種類圖解

範例資料：campaigns 有 id 1, 2, 3；clicks 的 campaign_id 有 2, 3, 3, 4（4 是孤兒資料）。

-- MySQL 模擬 FULL OUTER JOIN
SELECT c.id, k.campaign_id FROM campaigns c LEFT JOIN clicks k ON k.campaign_id = c.id
UNION ALL
SELECT c.id, k.campaign_id FROM campaigns c RIGHT JOIN clicks k ON k.campaign_id = c.id
WHERE c.id IS NULL;   -- 只補上右側孤兒，避免 INNER 部分重複

-- LEFT JOIN 的經典陷阱：把右表條件寫在 WHERE 會把 LEFT JOIN 變成 INNER JOIN
SELECT c.id, COUNT(k.id) AS clicks_today
FROM campaigns c
LEFT JOIN clicks k
  ON k.campaign_id = c.id
 AND k.ts >= CURDATE()          -- 正確：條件放 ON，沒點擊的 campaign 仍保留（count = 0）
GROUP BY c.id;
-- 若寫成 WHERE k.ts >= CURDATE()，k.ts 為 NULL 的列被過濾，沒點擊的 campaign 消失

題 2：GROUP BY + HAVING

找出「最近 7 天曝光數超過 10 萬、且平均 CPM 大於 2 美元」的廣告活動。

SELECT campaign_id,
       COUNT(*)                          AS imps,
       SUM(cost) / COUNT(*) * 1000       AS cpm
FROM impressions
WHERE ts >= NOW() - INTERVAL 7 DAY     -- WHERE：分組前過濾「列」，能用索引
GROUP BY campaign_id
HAVING COUNT(*) > 100000                -- HAVING：分組後過濾「組」，可用聚合
   AND SUM(cost) / COUNT(*) * 1000 > 2
ORDER BY imps DESC;

題 3：第 N 高的薪資（LeetCode 176 / 177）

邊界：有重複薪資時「第 2 高」指的是第 2 個不同的值；不存在時回傳 NULL。

-- 方法 1：DISTINCT + LIMIT OFFSET；外層包一層 SELECT 讓「沒有結果」變成 NULL
SELECT (
  SELECT DISTINCT salary FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1          -- 第 N 高就是 OFFSET N-1
) AS second_highest;

-- 方法 2：DENSE_RANK（相同薪資同名次，名次連續）
SELECT MAX(salary) AS nth_highest      -- 聚合在沒有列時回傳 NULL
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

-- 方法 3：不用 window function（舊版 MySQL）
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 方法 4：相關子查詢（概念題）：比它大的不同薪資恰好有 N-1 個
SELECT DISTINCT e1.salary FROM employees e1
WHERE 1 = (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e1.salary);

題 4：每組 Top N（ROW_NUMBER / RANK / DENSE_RANK）

每個廣告主花費最高的前 3 個活動（LeetCode 185 Department Top Three Salaries 的變形）。先搞清楚三個排名函數的差異（花費 100, 90, 90, 80）：

WITH ranked AS (
  SELECT advertiser_id, campaign_id, spend,
         DENSE_RANK() OVER (PARTITION BY advertiser_id ORDER BY spend DESC) AS rnk
  FROM campaign_spend
)
SELECT advertiser_id, campaign_id, spend
FROM ranked
WHERE rnk <= 3                  -- window 結果只能在外層過濾
ORDER BY advertiser_id, rnk;
-- 「嚴格只要 3 筆」用 ROW_NUMBER（需加 tie-breaker：ORDER BY spend DESC, campaign_id）
-- 「前 3 個不同金額，平手都要」用 DENSE_RANK
-- PostgreSQL 取每組第 1 筆還有專屬語法：SELECT DISTINCT ON (advertiser_id) ... ORDER BY advertiser_id, spend DESC

效能：window function 需要依 PARTITION BY + ORDER BY 排序，索引 (advertiser_id, spend) 可避免 filesort。資料量極大時可用 LATERAL（MySQL 8.0.14+ / PG）對每組各取 LIMIT 3，每組都走索引。

題 5：連續登入 N 天的使用者

技巧：「日期 − 該使用者內的序號」對連續日期會得到相同的錨點。例如 11/01、11/02、11/03、11/05 的序號是 1, 2, 3, 4，相減得 10/31、10/31、10/31、11/01 → 前三天是同一個連續段。

WITH daily AS (                                   -- 先去重：同一天多次登入只算一次
  SELECT DISTINCT user_id, DATE(login_at) AS d
  FROM logins
),
numbered AS (
  SELECT user_id, d,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS rn
  FROM daily
),
grp AS (
  SELECT user_id, d, DATE_SUB(d, INTERVAL rn DAY) AS anchor
  FROM numbered
)
SELECT user_id, MIN(d) AS streak_start, MAX(d) AS streak_end, COUNT(*) AS days
FROM grp
GROUP BY user_id, anchor
HAVING COUNT(*) >= 3;              -- 連續 3 天以上
-- PostgreSQL：d - (ROW_NUMBER() OVER (...))::int AS anchor

-- 另一種解法：LAG 比對前一天，適合找「斷點」
SELECT user_id, d,
       CASE WHEN DATEDIFF(d, LAG(d) OVER (PARTITION BY user_id ORDER BY d)) = 1
            THEN 0 ELSE 1 END AS is_new_streak
FROM daily;
-- 再對 is_new_streak 做 running SUM 就得到 streak 編號（gaps and islands 經典解法）

題 6：留存率（retention）

定義：某天首次活躍（新使用者）的人中，第 1 天 / 第 7 天後仍活躍的比例。

WITH activity AS (
  SELECT DISTINCT user_id, DATE(event_ts) AS d FROM events
),
first_day AS (
  SELECT user_id, MIN(d) AS cohort_day FROM activity GROUP BY user_id
)
SELECT f.cohort_day,
       COUNT(DISTINCT f.user_id) AS new_users,
       COUNT(DISTINCT CASE WHEN a.d = f.cohort_day + INTERVAL 1 DAY THEN a.user_id END)
         / COUNT(DISTINCT f.user_id) AS d1_retention,
       COUNT(DISTINCT CASE WHEN a.d = f.cohort_day + INTERVAL 7 DAY THEN a.user_id END)
         / COUNT(DISTINCT f.user_id) AS d7_retention
FROM first_day f
LEFT JOIN activity a
  ON a.user_id = f.user_id
 AND a.d IN (f.cohort_day + INTERVAL 1 DAY, f.cohort_day + INTERVAL 7 DAY)
GROUP BY f.cohort_day
ORDER BY f.cohort_day;
-- 最近 7 天內的 cohort 還無法計算 D7（分母仍在、分子必為 0），報表上應顯示為 NULL 而不是 0%

題 7：廣告 CTR 計算（impressions / clicks 表）

表：impressions(id, campaign_id, ts)、clicks(id, impression_id, campaign_id, ts)。求每個活動昨天的曝光、點擊、CTR。

-- 錯誤示範：直接 JOIN 再 COUNT，會因一對多造成重複計數（fan-out），且 JOIN 兩張大表很貴
-- SELECT i.campaign_id, COUNT(i.id), COUNT(c.id) FROM impressions i LEFT JOIN clicks c ON ... GROUP BY ...

-- 正確：先各自聚合，再 JOIN 聚合後的小結果
WITH imp AS (
  SELECT campaign_id, COUNT(*) AS impressions
  FROM impressions
  WHERE ts >= CURDATE() - INTERVAL 1 DAY AND ts < CURDATE()
  GROUP BY campaign_id
),
clk AS (
  SELECT campaign_id, COUNT(DISTINCT impression_id) AS clicks   -- 同一曝光多次點擊只算一次
  FROM clicks
  WHERE ts >= CURDATE() - INTERVAL 1 DAY AND ts < CURDATE()
  GROUP BY campaign_id
)
SELECT imp.campaign_id,
       imp.impressions,
       COALESCE(clk.clicks, 0)                                AS clicks,
       ROUND(COALESCE(clk.clicks, 0) / NULLIF(imp.impressions, 0), 4) AS ctr
FROM imp
LEFT JOIN clk ON clk.campaign_id = imp.campaign_id
ORDER BY ctr DESC;

題 8：每日 DAU 與 DAU/MAU

-- 每日 DAU
SELECT DATE(event_ts) AS d, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_ts >= '2026-10-01' AND event_ts < '2026-11-01'
GROUP BY DATE(event_ts)
ORDER BY d;

-- 每日的「滾動 30 天 MAU」與黏著度 DAU/MAU：COUNT(DISTINCT) 不能直接當 window function 用，改用自連接日期範圍
WITH daily_users AS (
  SELECT DISTINCT DATE(event_ts) AS d, user_id FROM events
),
days AS (SELECT DISTINCT d FROM daily_users)
SELECT days.d,
       COUNT(DISTINCT CASE WHEN du.d = days.d THEN du.user_id END) AS dau,
       COUNT(DISTINCT du.user_id)                                  AS mau_30d
FROM days
JOIN daily_users du
  ON du.d BETWEEN days.d - INTERVAL 29 DAY AND days.d
GROUP BY days.d
ORDER BY days.d;

效能：GROUP BY DATE(event_ts) 會在每列計算函數，但這裡是全範圍掃描聚合所以可接受；WHERE 條件必須寫成範圍才能用索引。大規模的 distinct count 實務上用 HyperLogLog 近似（Redis PFADD/PFCOUNT、BigQuery APPROX_COUNT_DISTINCT），誤差約 1% 以內但記憶體極省（見 第 6 章）。

題 9：7 日移動平均

WITH daily AS (
  SELECT DATE(ts) AS d, SUM(cost) AS spend
  FROM impressions
  WHERE campaign_id = 7
  GROUP BY DATE(ts)
)
SELECT d, spend,
       AVG(spend) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma7_rows,
       SUM(spend) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM daily
ORDER BY d;

-- ROWS 是「往前 6 列」：若某天完全沒資料（缺日期），會錯拿到更早的日期！
-- 解法 1：用 RANGE 按值計算（MySQL 8 支援日期 RANGE INTERVAL）
--   AVG(spend) OVER (ORDER BY d RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)
--   但缺日期時分母仍只算有資料的天數；要把缺的天當 0，需先補齊日期（見題 14 遞迴 CTE 產生日期序列）
-- 解法 2：前 6 天資料不足 7 天時，可用 COUNT(*) OVER (...) 判斷並輸出 NULL

題 10：找出重複資料並刪除

-- 找出重複：同一使用者、同一曝光被重複上報的點擊
SELECT impression_id, COUNT(*) AS cnt
FROM clicks
GROUP BY impression_id
HAVING COUNT(*) > 1;

-- 刪除重複、保留 id 最小的那筆
-- MySQL 不允許 DELETE 時在子查詢直接讀同一張表（Error 1093），用 JOIN 自連接：
DELETE c1
FROM clicks c1
JOIN clicks c2
  ON c1.impression_id = c2.impression_id
 AND c1.id > c2.id;

-- 或用 window function（包一層衍生表繞過 1093 限制）
DELETE FROM clicks
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY impression_id ORDER BY id) AS rn
    FROM clicks
  ) t WHERE t.rn > 1
);

-- 最後加上唯一約束，從根本防止再次發生；之後用 INSERT IGNORE / ON DUPLICATE KEY 冪等寫入
ALTER TABLE clicks ADD UNIQUE KEY uk_imp (impression_id);

題 11：NULL 行為陷阱

SQL 是三值邏輯：TRUE、FALSE、UNKNOWN。任何與 NULL 的比較（包括 NULL = NULL）結果都是 UNKNOWN，WHERE 只保留 TRUE。

-- 經典 bug：找出「沒有被點擊過的曝光」
-- 若 clicks.impression_id 中有任何一列是 NULL，這個查詢會回傳 0 列！
SELECT id FROM impressions
WHERE id NOT IN (SELECT impression_id FROM clicks);

-- 安全寫法 1：NOT EXISTS（不受 NULL 影響，且優化器通常轉成 anti-join）
SELECT i.id FROM impressions i
WHERE NOT EXISTS (SELECT 1 FROM clicks c WHERE c.impression_id = i.id);

-- 安全寫法 2：LEFT JOIN ... IS NULL
SELECT i.id FROM impressions i
LEFT JOIN clicks c ON c.impression_id = i.id
WHERE c.id IS NULL;

題 12：UNION vs UNION ALL

• UNION 會去重：需要排序或雜湊整個結果集（EXPLAIN 會看到 UNION RESULT 與 Using temporary）。
• UNION ALL 直接串接，不去重，快得多。
• 原則：確定沒有重複、或重複是有意義的（例如合併多個分表的事件計數）時，一律用 UNION ALL。
• 欄位數與型別要對應；欄位名稱取第一個 SELECT 的；ORDER BY / LIMIT 作用於整個結果時要放最後（子查詢內的 ORDER BY 需搭配 LIMIT 才有意義）。

-- 合併曝光與點擊成一條事件流
SELECT 'imp' AS type, campaign_id, ts FROM impressions WHERE ts >= CURDATE()
UNION ALL
SELECT 'click', campaign_id, ts FROM clicks WHERE ts >= CURDATE()
ORDER BY ts DESC
LIMIT 100;

-- 把 OR 改寫成 UNION ALL 以利用兩個不同索引（注意兩邊可能重疊時要排除）
SELECT * FROM campaigns WHERE advertiser_id = 3
UNION ALL
SELECT * FROM campaigns WHERE name = 'Black Friday' AND advertiser_id <> 3;

題 13：子查詢 vs JOIN vs EXISTS

「找出有任何點擊的活動」三種寫法：

-- IN 子查詢（semi-join）
SELECT id, name FROM campaigns
WHERE id IN (SELECT campaign_id FROM clicks WHERE ts >= CURDATE());

-- EXISTS（相關子查詢，找到一筆就停）
SELECT id, name FROM campaigns c
WHERE EXISTS (SELECT 1 FROM clicks k WHERE k.campaign_id = c.id AND k.ts >= CURDATE());

-- JOIN：一對多會產生重複列，需要 DISTINCT（成本更高），語意也較不直接
SELECT DISTINCT c.id, c.name
FROM campaigns c JOIN clicks k ON k.campaign_id = c.id
WHERE k.ts >= CURDATE();

• MySQL 5.5 以前 IN (subquery) 常被執行成「對外表每一列跑一次子查詢」，所以有「用 JOIN 取代 IN」的老建議。5.6+ 有 semi-join 優化（materialization、FirstMatch、LooseScan 等），8.0.16+ 也會把 EXISTS 轉成 semi-join，現在三者效能常常相近，用 EXPLAIN 實測才是正解。
• 語意：只需判斷「存在與否」用 EXISTS / IN（semi-join）；需要右表欄位才用 JOIN。「不存在」用 NOT EXISTS（NULL 安全）。
• 相關子查詢放在 SELECT 清單中（scalar subquery）對每一列執行一次，大結果集時要注意。

題 14：CTE 與遞迴 CTE

CTE（WITH）讓複雜查詢分段可讀；MySQL 8.0+ 與 PG 都支援。PG 12 以前 CTE 一定會被物化（optimization fence），12+ 若只被引用一次會被內聯；需要時可用 MATERIALIZED / NOT MATERIALIZED 指定。

-- 遞迴 CTE 1：產生連續日期，補齊報表缺的日子（缺的天顯示 0）
WITH RECURSIVE dates AS (
  SELECT DATE('2026-10-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2026-10-31'
)
SELECT dates.d, COALESCE(s.clicks, 0) AS clicks
FROM dates
LEFT JOIN campaign_daily_stats s ON s.dt = dates.d AND s.campaign_id = 7
ORDER BY dates.d;
-- MySQL 遞迴深度預設上限 cte_max_recursion_depth = 1000；PG 可直接用 generate_series()

-- 遞迴 CTE 2：組織 / 帳號階層（代理商 → 子帳號 → 廣告主），找出某節點底下所有後代
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth, CAST(id AS CHAR(500)) AS path
  FROM accounts WHERE id = 1                     -- 錨點（anchor member）
  UNION ALL
  SELECT a.id, a.parent_id, a.name, t.depth + 1, CONCAT(t.path, '/', a.id)
  FROM accounts a
  JOIN tree t ON a.parent_id = t.id              -- 遞迴成員（recursive member）
  WHERE t.depth < 20                             -- 防止資料中有環造成無限遞迴
)
SELECT * FROM tree ORDER BY path;

題 15：條件聚合（pivot）與累計花費

把每個活動各裝置的曝光數轉成欄位，並計算當日累計花費（預算 pacing 用）。

-- Pivot：用 CASE / SUM(條件) 做條件聚合，一次掃描算出多個指標
SELECT campaign_id,
       SUM(device = 'ios')     AS ios_imps,       -- MySQL 中布林即 0/1
       SUM(device = 'android') AS android_imps,
       SUM(CASE WHEN device NOT IN ('ios', 'android') THEN 1 ELSE 0 END) AS other_imps
FROM impressions
WHERE ts >= CURDATE()
GROUP BY campaign_id;
-- PostgreSQL 寫法：COUNT(*) FILTER (WHERE device = 'ios')

-- 每小時花費與當日累計，找出何時超過日預算
WITH hourly AS (
  SELECT campaign_id, DATE_FORMAT(ts, '%Y-%m-%d %H:00') AS hr, SUM(cost) AS spend
  FROM impressions
  WHERE ts >= CURDATE()
  GROUP BY campaign_id, hr
),
running AS (
  SELECT h.*, SUM(spend) OVER (PARTITION BY campaign_id ORDER BY hr) AS cum_spend
  FROM hourly h
)
SELECT r.campaign_id, MIN(r.hr) AS budget_exhausted_at
FROM running r
JOIN campaigns c ON c.id = r.campaign_id
WHERE r.cum_spend >= c.daily_budget
GROUP BY r.campaign_id;

SQL Injection、參數化查詢與 ORM 取捨 基礎

SQL injection

當使用者輸入被字串拼接進 SQL，攻擊者就能改變 SQL 的結構。

# 危險：字串拼接 / f-string
name = request.args["name"]                 # 攻擊者輸入：' OR '1'='1
cur.execute(f"SELECT * FROM users WHERE name = '{name}'")
# 實際執行：SELECT * FROM users WHERE name = '' OR '1'='1'  → 回傳所有使用者
# 輸入 '; DROP TABLE users; -- 在允許多語句的驅動上更可怕

# 安全：參數化查詢（placeholder），值與 SQL 結構分開傳送
cur.execute("SELECT * FROM users WHERE name = %s", (name,))       # PyMySQL / mysqlclient
cur.execute("SELECT * FROM users WHERE name = $1", name)          # asyncpg（PostgreSQL）
session.execute(text("SELECT * FROM users WHERE name = :n"), {"n": name})   # SQLAlchemy

# IN 清單：產生對應數量的 placeholder，而不是拼接值
ids = [3, 5, 8]
placeholders = ", ".join(["%s"] * len(ids))
cur.execute(f"SELECT * FROM campaigns WHERE id IN ({placeholders})", ids)

# 識別字（表名、欄名、排序方向）無法參數化 → 一律用白名單
ALLOWED_SORT = {"created_at", "name", "daily_budget"}
sort = request.args.get("sort", "created_at")
if sort not in ALLOWED_SORT:
    raise ValueError("invalid sort column")
direction = "DESC" if request.args.get("desc") == "1" else "ASC"
cur.execute(f"SELECT id, name FROM campaigns ORDER BY {sort} {direction} LIMIT %s", (50,))

• 為什麼參數化能防禦：server-side prepared statement 把 SQL 先解析成執行計畫，參數只會被當成「值」，無法改變語法樹。PyMySQL 屬於 client-side 參數替換，但驅動會正確跳脫字串，同樣安全——前提是用驅動的 placeholder，而不是自己做字串格式化。
• 縱深防禦：資料庫帳號最小權限（應用程式帳號不給 DROP / GRANT）、關閉多語句、WAF、錯誤訊息不要把 SQL 錯誤細節回給前端、ORM 的 raw query 也要參數化。
• 第二階攻擊（second-order injection）：先存入惡意字串，之後在別處被拼接執行——從資料庫讀出的資料也不可信。

ORM 的取捨

from datetime import datetime
from decimal import Decimal
from sqlalchemy import BigInteger, ForeignKey, Numeric, String, create_engine, func, select
from sqlalchemy.dialects.mysql import insert as mysql_insert
from sqlalchemy.orm import (DeclarativeBase, Mapped, Session, mapped_column,
                            relationship, selectinload)

class Base(DeclarativeBase):
    pass

class Campaign(Base):
    __tablename__ = "campaigns"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    advertiser_id: Mapped[int] = mapped_column(BigInteger, index=True)
    name: Mapped[str] = mapped_column(String(255))
    daily_budget: Mapped[Decimal] = mapped_column(Numeric(12, 4))
    version: Mapped[int] = mapped_column(default=0)
    creatives: Mapped[list["Creative"]] = relationship(back_populates="campaign", lazy="raise")
    __mapper_args__ = {"version_id_col": version}   # 內建樂觀鎖：UPDATE 自動帶 WHERE version = ?

class Creative(Base):
    __tablename__ = "creatives"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    campaign_id: Mapped[int] = mapped_column(ForeignKey("campaigns.id"))
    format: Mapped[str] = mapped_column(String(32))
    campaign: Mapped[Campaign] = relationship(back_populates="creatives")

engine = create_engine("mysql+pymysql://app:secret@db:3306/ads", pool_pre_ping=True)

with Session(engine) as session, session.begin():          # begin() 結束時自動 commit / 例外時 rollback
    # 1) ORM 查詢 + 預先載入，避免 N+1（lazy="raise" 會讓意外的 lazy load 直接報錯）
    stmt = (select(Campaign)
            .where(Campaign.advertiser_id == 3)
            .options(selectinload(Campaign.creatives))
            .order_by(Campaign.id)
            .limit(50))
    for c in session.scalars(stmt):
        print(c.name, [cr.format for cr in c.creatives])

    # 2) 聚合查詢：直接用 Core 風格，回傳 tuple 而不是物件，較省資源
    rows = session.execute(
        select(Campaign.advertiser_id, func.count(), func.sum(Campaign.daily_budget))
        .group_by(Campaign.advertiser_id)
    ).all()

    # 3) 悲觀鎖
    camp = session.scalars(select(Campaign).where(Campaign.id == 7).with_for_update()).one()
    camp.daily_budget = Decimal("500.0000")                  # flush 時發出 UPDATE（帶 version 檢查）

    # 4) 批次 upsert：用方言專屬語法，ORM 物件逐筆 add 太慢
    stmt = mysql_insert(Campaign.__table__).values([
        {"id": 101, "advertiser_id": 3, "name": "A", "daily_budget": 100, "version": 0},
        {"id": 102, "advertiser_id": 3, "name": "B", "daily_budget": 200, "version": 0},
    ])
    stmt = stmt.on_duplicate_key_update(name=stmt.inserted.name,
                                        daily_budget=stmt.inserted.daily_budget)
    session.execute(stmt)

面試自測題