Relational database 關聯式資料庫 - paulip114/blog GitHub Wiki

正規化

image

正規化步驟

先把資料儲存在資料表內（未正規化的資料表） image

未符合第一正規化造成的問題 單一個欄位內有超過1個以上的值。 欄位長度無法確定（可能很多，也可能很少），必須預留空間，造成儲存空間的浪費。 image

第一正規化（1NF）

定義

  1. 要排除重複群的出現，要求每一列資料的欄位值都只能是單一值（基元值）。
  2. 沒有任何兩筆以上的資料完全重複。
  3. 資料表中需有主鍵（唯一值），其他所有欄位都相依於主鍵。
  4. 同一張資料表內，不建議用多個欄位表達同一個事情（如下圖）。

image

作法

  1. 確認是否有重複表達的欄位。
  2. 將欄位內重複的資料分別存為不同的Data Row資料。

回到選課表

image

可以將學號&課程代碼組合成唯一值（Key），透過學號&課程代碼，可以修改成績。

第二正規化（2NF）

定義

  1. 符合1NF。
  2. 消除「部分功能相依」，每一個非鍵欄位必須完全相依主鍵（學號=>學生，課程=>學分…），通常「主鍵有多個欄位」組成時會發生「部分功能相依」。 作法
  3. 檢查是否存在「部分功能相依」（可從多個欄位組成的主鍵開始檢查）。
  4. 將「部分功能相依」的欄位分割出去，另外組成新的資料表。

回到選課表

image

主鍵是由「學號」與「課程代碼」組成：

  • 姓名、性別，相依於 「學號」
  • 課程名稱、學分數、必選修、老師編號、老師姓名，相依於「課程代碼」 存在「部分功能相依」，需要將部分功能相依欄位分割（2NF），以下為分割後結果 image

發現「重複的資料」，因為需符合1NF，所以將重複的資料去除 image

紅底的欄位為「主鍵」

第三正規化（3NF）

定義

  1. 符合2NF。
  2. 各欄位之間沒有存在「遞移相依」的關係，也就是與「主鍵」無關的相依性。

作法

  1. 檢查是否存在「遞移相依」的欄位。
  2. 將「遞移相依」的欄位分割出去，另外組成新的資料表。 將有遞移相依的欄位分割出去
  3. 檢查是否存在遞移相依（Transitive dependency）或「間接相依」（與主鍵無關的相依性）。

image image

  • 「老師編號」相依於「課程代碼」而且「老師姓名」相依於「老師編號」，所以「老師姓名」與「課程編號」（主鍵）為無關的相依。
  • 「老師姓名」與主鍵存在著「遞移相依」的關係。 分割欄位 image

正規化結果

image

SQL JOINS

image

資料庫優化 Optimizee Database

資料庫索引（Database Index）

索引 (Index) 就像一本書的目錄，能讓你快速定位到內容的頁碼。如果沒有做索引，資料庫就必須從第一筆開始往下翻，一路比對到最後一筆，才能確定某個條件的所有符合結果 (也就是全表掃描Full Table Scan，或稱做遍歷 Traverse)。反之，如果有索引的話，資料庫建可以先在「目錄」裡找到符合的索引位置之後，再直接定位到相關資料列，這樣就能大幅減少搜尋的範圍。

常見的索引結構

B-Tree 索引

這是大多數關聯式資料庫的主要索引結構 (MySQL 的 InnoDB、PostgreSQL、MS SQL… 都以 B-Tree 為主)。適合處理「範圍搜尋」(range query) 或「精準搜尋」(point lookup)。像 WHERE age BETWEEN 18 AND 30，或 WHERE username = 'Alice' 這樣常見的寫法，都算是 B-Tree。

Hash 索引

部分資料庫或特殊引擎有支援 (像 PostgreSQL 可以建立 Hash Index)，它的特性是查詢速度快，但只適用「等值搜尋」(=)，範圍的查詢就不支援。

其他索引（如 GiST、GIN、R-Tree…）

主要用在全文檢索、地理空間索引等特殊場景，初學者先了解有這類東西即可。

什麼欄位該加索引？ 經常出現在 WHERE 條件或 JOIN 條件的欄位，通常適合加索引。例如 user_id, email, order_id。 需要頻繁做範圍搜尋或排序的欄位，也常常需要加索引。例如 created_at。 但這裡要稍微注意的是，索引雖然能提升查詢速度，卻也會佔用額外儲存空間，並在新增/更新/刪除資料時，增加維護索引的成本。因此「索引越多越好」並不正確，還是要考慮整體讀寫比與應用情境。

解讀查詢計劃 (EXPLAIN / Execution Plan)

EXPLAIN 是什麼？ 大部分的關聯式資料庫都提供一個指令（或語法）來查看 SQL 查詢的「執行計劃 (Execution Plan)」，像是：

MySQL/MariaDB 用 EXPLAIN SELECT ... PostgreSQL 可以用 EXPLAIN 或 EXPLAIN ANALYZE SELECT ... MS SQL Server 有圖形化的「Query Execution Plan」 Oracle 也有 EXPLAIN PLAN FOR SELECT ... 執行後系統會告訴你，從我們送出查詢指令、一直到我們收到查詢結果這之間，系統在背景下去用怎樣的順序和邏輯來找出我們想要的資料。比如這條查詢使用了哪個索引？預估要掃描多少筆資料？是否進行了「全表掃描 (table scan)」？有沒有用到「索引搜尋 (index seek)」或「索引範圍掃描 (index range scan)」？JOIN 時是用什麼連接方式 (Nested Loop, Hash Join…)？等等

為什麼要看 EXPLAIN？ 如果我們送出去的查詢很慢，通常可能是在某處出現了某些效能上的瓶頸。而我們可以透過 EXPLAIN 來知道瓶頸具體的發生點都在哪些地方。

你可以發現很多有趣的行為和細節，很可能跟我們在寫指令時所想像的不一樣。你可能會發現原來系統沒使用你以為會用的索引，導致做了全表掃描；或是原來某個 JOIN 條件寫得有問題，造成大量重複運算…等等。這時我們就可以利用這些資訊來思考，是不是可以藉由改寫原有的 SQL 語法的方式，讓資料庫的查詢效能得到提升。

實例：MySQL EXPLAIN

EXPLAIN
SELECT o.order_id, o.amount, c.customer_name
FROM orders AS o
JOIN customers AS c
   ON o.customer_id = c.customer_id
WHERE o.amount > 1000;

執行後，你可能會看到類似這樣的輸出 (簡化版)：

image type = range 代表此處對 orders 表採用索引範圍掃描 (range scan)。 possible_keys 和 key 顯示資料庫選擇了 idx_amount 這個索引。 rows = 500 表示預估只會掃 500 筆資料 (而不是整張表的數十萬筆)。 JOIN 到 customers 時，用的是 eq_ref（相等連結），並利用了 PRIMARY 這個主鍵索引。 如果某個查詢寫得不理想的話，很可能原本需要把整張表 50 萬筆資料全都掃一遍。現在透過正確的索引就只需要掃 500 筆，這效能差距就會很明顯。

SQL 查詢改寫與條件順序

為什麼要改寫？ 即使你已經建立了索引，但如果 SQL 語法寫得不恰當，資料庫可能就無法有效運用這些索引。比如下面就是一個常見的例子：

SELECT *
FROM users
WHERE LEFT(email, 5) = 'admin';   -- 錯誤示範

使用函數處理欄位的問題：即使在 email 欄位上有建立索引，但如果使用 LEFT(email, 5) = 'admin' 這種將欄位包在函數內的寫法，資料庫就無法使用索引來做前置比對 (prefix matching)。

索引友善的查詢寫法：正確的做法是用 email LIKE 'admin%'，此時才能有效利用索引。這是因為它是一個前綴查詢 (prefix query)，B-Tree 索引的結構非常適合處理這種模式。而使用 LEFT(email, 5) 的寫法，由於在欄位上套用了函數，即使有建立索引也會被迫進行全表掃描。

%放在哪裡也會有差別 如果你在搜尋時用百分比符號 % 包住關鍵字（像是 '%abc%'）或把 % 放在最前面（像是 '%abc'），資料庫都沒辦法有效地使用索引來加速搜尋。但如果只在關鍵字後面加上 %（像是 'abc%'），資料庫就能使用索引來快速找到符合的資料。

那如果一定要搜尋中間的關鍵字呢？這時候有幾個可行的解決方案：

全文檢索 (Full-Text Search)：大部分主流的資料庫都有內建的全文檢索引擎，比起使用 LIKE '%keyword%' 這種方式要來得有效率得多。 特殊索引類型：例如 PostgreSQL 的 GIN 索引就非常適合做這類的文字搜尋。這類索引雖然會佔用較多空間，但搜尋效能比起一般的 B-tree 索引要好上許多。 不過如果資料量不大，也沒有一定不能用 LIKE '%keyword%' 就是了。但我們還是可以利用一些方式來多少彌補一些效能的問題：

搭配其他能用到索引的條件來縮小搜尋範圍 設定合理的 LIMIT 來限制回傳筆數 條件順序與 AND/OR 的影響 當在 SQL 查詢中使用多個 AND 條件時，資料庫會自動挑選最快的方式來搜尋。

舉例來說，假設你的資料表有兩個索引：一個在 customer_id 欄位上，另一個在 amount 欄位上，或是有一個結合兩者的索引 (customer_id, amount)，資料庫會自動選擇最有效率的方式來執行查詢。

SELECT *
FROM orders
WHERE customer_id = 1001
 AND amount > 500;

但如果我們把這裡的AND 改成 OR 的話，資料庫通常就無法有效運用複合索引了。

這是因為複合索引的設計主要是為了處理 AND 條件的查詢。此時我們有兩種解決方案：一是採用其他的優化策略 (如為每個條件建立獨立的索引)，二是將查詢拆分成多個使用 UNION 的子查詢。

善用 LIMIT、分頁查詢 (Pagination) 與分批讀取

為什麼需要分頁或分批處理？ 有時候你需要從資料庫一次撈出幾萬筆資料，但其實顯示在前端或報表裡只需要幾筆關鍵資料。過多的資料傳輸量也會拖慢整體速度。比如我們可以只拿最新 10 筆，而不是整張表。一方面減少網路傳輸量，另一方面避免資料庫產生過重的負擔。

SELECT *
FROM big_table
ORDER BY created_at DESC
LIMIT 10;

使用 OFFSET 的效能隱憂 如果你在分頁時使用 OFFSET，像 LIMIT 10 OFFSET 1000000，有些資料庫仍然得先掃過前面的一百萬筆，再丟棄後再拿 10 筆，這樣效率就不會好了。

這時可以透過 「記錄最後一筆 id」 的方式來做「鍵值分頁 (Keyset Pagination)」，讓每次查詢只要比對「id 大於多少」，就可以繼續往下撈。對於「只往前 / 往後翻頁」的場景效率比較高。

-- 初始查詢

SELECT *
FROM big_table
WHERE id > 0  -- 從開始處查詢
ORDER BY created_at DESC
LIMIT 10;

-- 下一頁查詢（假設 last_seen_id 是上一次查詢的最後一筆 id）

SELECT *
FROM big_table
WHERE id > :last_seen_id
ORDER BY created_at DESC
LIMIT 10;

觀察寫入 (INSERT/UPDATE) 與讀取的平衡

索引過多，也會拖慢寫入 每多一個索引，就代表每次新增或更新資料，都要額外維護那個索引。若你的業務場景是高頻寫入 (例如每秒萬筆交易) 又加了一堆索引，寫入效能會嚴重下降。

所以在「讀多寫少」的場景裡 (如報表查詢系統)，可以大膽加更多索引，提高查詢速度；在「寫多讀少」的場景裡 (如即時交易系統)，就要謹慎選擇必要的索引。

觀察實際 Query Patterns 再來決定 建議使用資料庫的慢查詢紀錄或監控工具來找出最需要優化的地方。具體來說，要觀察哪些查詢最常被使用、哪些欄位最常被用來搜尋或篩選。接著，針對這些常用的欄位來加入索引，這樣效果會最好。

另外要定期檢查舊的索引是否還有在使用、以及是否可以合併或移除不需要的索引。這樣可以讓資料庫維持在最好的狀態。

效能優化是不斷迭代的 只要資料庫是持續有在使用的，它就會隨著時間成長，而且業務需求也會隨時間改變，所以它的結構與設計也需要跟著不斷調整。但無論如何，掌握「索引」與「查詢結構」的調整，是我們優化效能的第一步。只要你養成「查詢一寫完，就用 EXPLAIN 看一下」的好習慣，長期下來就能積累不少優化經驗，讓你的 SQL 隨時都能擁有處理大量資料的餘裕喔。

Reference: