兩個後端工程師該懂的 SQL 觀念

1. Transaction 資料庫交易

Transaction 是變更資料的流程，一個 transaction 當中可以包含多個針對資料的操作（INSERT/UPDATE/DELETE）。 執行 transaction 的結果有兩種：

1. Transaction 內的所有操作都成功，結束交易並保存資料變動結果。
2. Transaction 內有一個以上的操作失敗，結束交易並使資料回到交易開始前的狀態。

Transaction 的典型應用情境：銀行轉帳

假設今天某銀行C的客戶A與B之間有轉帳交易，由A轉出十萬元給B。倘若這筆轉帳交易在途中發生不可預期的錯誤，導致A的帳戶雖然扣款十萬元，B卻未如期收到十萬元，對A而言可是莫大的損失，C也可能被金管會重罰。Transaction就是為了避免這類意外而存在的機制。以下用SQL語法列舉兩個Transaction的範例，象徵銀行用戶之間有100元的轉帳交易。

-- 先告訴資料庫即將開始交易
BEGIN;

-- 變更資料，假設 id 1 及 id 2 兩者的 balance 在交易開始前皆為 100。
UPDATE database.Tables SET balance = 0 WHERE id = 1;
UPDATE database.Tables SET balance = 200 WHERE id = 2;

-- 確定上述交易無誤，保存資料變動結果。
COMMIT;

-- 先告訴資料庫即將開始交易
BEGIN;

-- 變更資料，假設 id 1 及 id 2 兩者的 balance 在交易開始前皆為 100。
UPDATE database.Tables SET balance = 0 WHERE id = 1;
UPDATE database.Tables SET balance = 200 WHERE id = 2;

// 取消上述交易，資料回到開始交易前的狀態。
ROLLBACK;

Transaction 四大特性：ACID

ACID取自四大特性（Atomicity、Consistency、Isolation、Durability）的英文字首。

• Atomicity 原子性
  Transaction 彷彿不可分割的原子，即使是由多個SQL語句組成，資料操作的結果也只能是全部完成變更或全部維持不變。
• Consistency 一致性
  資料操作之標的在 transaction 前後都必須符合資料表的規範。例如：資料型別為字串的欄位不能在 transaction 後改為儲存整數、不允許空值的欄位不能在 transaction 後出現空值。
• Isolation 隔離性
  若有多筆 transaction 在同一時段內執行，各筆 transaction 資料操作之標的在 transaction 結束前不能影響其他 transaction。以銀行轉帳為例，假設有A、B、C三人，其帳戶各有100元，欲執行以下兩筆 transaction：

1. A 轉出 100 給 B
2. B 轉出 200 給 C

由於 B 的帳戶在交易開始前只有100元，因此第二筆 transaction 的執行時間哪怕是僅比第一筆早一秒也會失敗。不能因為預期第一筆 transaction 會率先且成功執行，就讓B從帳戶中轉出不足的金額。

• Durability 永久性
  Transaction結束後，已變動的資料若無追加操作就不能變動，即使系統或硬體發生障礙，資料也必須維持在障礙發生前的狀態。

2. Index 索引

索引的種類

• single-column
• multi-column
• unique
• partial
• implicit indexes

不該使用索引的時機

• 不該為了添加索引而添加索引
• 不該在小規模表單使用索引
• 不該在經常更新的表單使用索引（否則系統必須經常更新索引）
• 不該在允許空值的欄位使用索引
• 不該在含有大數值的欄位使用索引