資料庫 MOC

用途：從 schema 怎麼設計、到查詢為什麼慢，串成一條線。 上層：API 與後端 MOC

基礎與速查

• SQL Basic — SQL 關鍵字速查：每個關鍵字一句話解釋 ＋ 範例（MySQL）
• 什麼是 jsonb

Schema 設計與正規化

從「這個欄位到底跟誰有關」開始，一路問到「那要不要退回去」：

正規形式

• 正規化的五個階段對應三種相依性 — 先看全景：五個階段其實是三種相依性各自的清理工作
• 正規化的三個階段是同一個問句的三種問法 — 1NF/2NF/3NF 是同一個問句的三種問法
• 遞移相依：欄位靠另一個普通欄位才連到主鍵 — 3NF 要消除的東西長什麼樣
• superkey 就是能唯一指出一列的欄位組合 — 用猜謎測試一次定義 superkey / 候選鍵 / 鍵值欄位
• 單一主鍵讓 2NF 自動成立，但重複一點都沒少 — 代理鍵只是把罪名換掉
• 正規化的判準是候選鍵，不是主鍵 — 教材寫「主鍵」的地方要自動換成「候選鍵」再判一次
• BCNF 拿掉 3NF「只檢查非鍵值欄位」的前提 — 兩條條文只差一個子句，而那子句就是漏洞
• BCNF 保證無損 join，但不保證保持相依 — 推到 BCNF 要付的帳，也是刻意停在 3NF 的理由
• 多值相依的判準是矩形完整，不是感覺獨立 — 4NF：把「獨立」換成能驗資料的測試
• 5NF 的判準不在資料裡，在業務規則裡 — 5NF：拆兩張 lossy、拆三張剛好，以及不准拆的反例
• 跨來源的正規形式定義寬嚴不同，記判準比記條號可靠 — 同一個條號各家收的東西不一樣，別把條號當標準答案

該拆與不該拆

• 衍生欄位該拿掉的理由不是遞移相依 — 兩種該拿掉的欄位，判準不同
• 保留成交價不是反正規化，是另一個事實 — 算得出來但以後算不回來的要存
• 改漏一列的代價不是工多，是資料安靜地不一致 — 正規化到底在買什麼保險
• 拆表把成本集中在一次 join，不拆表把成本攤到每一列 — 反正規化的帳要兩邊一起算
• 反正規化要排在索引與物化視圖後面 — 先量、再索引、再讀模型，最後才反正規化

來源：資料庫正規化(一~三)（1NF~3NF）；BCNF 以上來自 ExplainThis〈什麼是資料庫正規化？〉與 tigercosmos〈Good RDB Design with the Concept of Normal Forms〉，連結見各篇筆記的 source

查詢效能原理

• 執行計畫是一棵樹，從下往上讀 — 縮排就是層級，最深的先跑，時間是累積的
• cost 和 actual time 的兩個數字是「啟動..總計」，不是範圍 — 不是「介於 a 到 b」，有 LIMIT 時這個分別會決定計畫
• 估計和實際差很多，代表資料庫對這張表的認識壞了 — 先修猜測，才輪到「要不要加索引」
• EXPLAIN ANALYZE 會真的執行查詢 — 對寫入語句要包 BEGIN/ROLLBACK
• loops 大於 1 時，actual time 和 rows 是每次迴圈的平均 — 不乘回去，巢狀迴圈底下永遠看起來很便宜
• Nested Loop 的執行順序是上外下內，不是最深的先跑 — 順序由兄弟關係決定，不是深度
• Index Cond 是在索引上定位，Filter 是撈出來再逐筆比 — 兩個關鍵字的分界就是「索引夠不夠」
• 複合索引的欄位 = Index Cond 的欄位 ＋ Filter 裡的等值欄位 — 從計畫反推該建什麼索引的三步 SOP
• 建索引的代價是毫秒，收益可能是秒 — 要不要建：讀寫比、數量級、連線霸佔風險、既有索引數
• WHERE 決定回傳幾列，索引才決定要看過幾列 — 掃描量由索引決定，不是由條件決定
• 全表掃描和索引掃描的適用場景是相反的 — 兩條存取路徑的步驟差在「要不要先定位」，適用場景剛好相反
• B-tree 靠一個節點放多個鍵把樹壓矮，換到更少的讀取次數 — 索引為什麼用 B+ tree：層數＝讀取次數
• 叢集索引一張表只有一個，非叢集索引是另一份帶 pointer 的結構 — 兩種索引的分工
• 條件欄位沒命中索引，索引仍有三種可能被用到 — 覆蓋索引／避開 filesort／index merge
• JOIN 貴在每一列都要去另一張表配對 — 三種 join 演算法與列數放大
• 對齊日期用 self-join，對齊前後順序用視窗函數 — 先決定語意再比成本
• OFFSET 分頁的總成本是 O(頁數平方)，cursor 分頁才是 O(頁數) — 深分頁與 keyset 分頁
• 同一個寫法在互動式列表沒事，在全量掃描必爆 — 慣例的適用範圍由負載形狀決定
• 把欄位包進函數，WHERE 就用不到那個欄位的索引 — sargable：欄位要光禿禿地站在比較運算子一邊
• 索引是拿寫入換查詢，寫多讀少的表加索引是純虧 — 收益有條件，成本無條件
• 「資料庫撐不住」不是一個問題，是三個 — 寫入吞吐／容量／分析查詢，處方互相衝突

MySQL vs PostgreSQL（儲存結構決定一切）

從「資料行躺在哪裡」一路推到選型與運維成本：

• MySQL 把資料放在主鍵索引的葉子，PostgreSQL 放在獨立的 Heap — 索引組織表 vs 堆疊表，後面所有差異的源頭
• 加上二級索引之後，主鍵查詢的快慢反過來 — InnoDB 二級索引存主鍵值 → 回表；判準是查詢分布不是單點比較
• PG 的寫入放大來自位置過期，不是欄位變動 — 索引列的右半邊記著位置，搬家就全部過期
• PG 的 HOT 什麼時候會失效？ — 兩份材料預設相反，待查
• 舊版本放哪裡，決定了誰會膨脹 — VACUUM／Bloat vs Undo Log／Purge，是一筆運維成本
• MySQL 一個連線一個執行緒，PG 一個連線一個進程 — PgBouncer 是必備技能不是加分項
• Binlog 是邏輯複製，WAL 串流是物理複製 — 靈活（CDC）vs 一致性

來源：MySQL 與 PostgreSQL 怎麼選？

交易與一致性

• ACID 的隔離性是可調的，預設擋不住 write skew — 框架自帶，約束和隔離等級是你的責任

資料庫類型與擴展

從「一台機器夠不夠」問到「那要換成哪一種」：

• 垂直擴展換機器，水平擴展換複雜度 — RDB 偏垂直的原因：它承諾的事需要看得到全部資料
• key-range sharding 用順序換熱點，hash 用均勻換範圍查詢 — 分片只決定一件事：資料該去哪台
• Key-Value 的限制是為了換分片而付的價 — 它就是一張不准 JOIN 的表，而那正是它能無痛分片的原因
• Document DB 快在局部性：把該一起用的東西實際存在一起 — 反正規化做到底的那一端
• Columnar 不是 NoSQL，是換了儲存佈局的關聯式資料庫 — 資料模型／儲存佈局／工作負載是三個正交的軸
• jsonb 能比資料模型，不能比擴展架構 — 想換的理由是 schema 會變就別換資料庫

SQL 方言與限制

• DELETE 搭 JOIN 的寫法 MySQL 與 PostgreSQL 不相容 — MySQL 別名 vs PostgreSQL USING
• MySQL 的 DELETE 子查詢不能直接引用同一張表 — 要多包一層 derived table
• PostgreSQL 的 date 加 INTERVAL 會變成 timestamp — date + 1 回 date，date + interval 回 timestamp

相關

• 瀏覽器內 SQLite：把查詢下推到 Worker 的 WASM heap — 把資料庫搬進瀏覽器的案例

---