前言

昨天簡單整理了資料庫和 PostgreSQL 的基本知識，今天繼續整理資料庫的重要概念。

主鍵與索引

主鍵（Primary Key）

主鍵是資料表中每一列的唯一識別碼，每個資料表只能有一個主鍵。

-- 自動遞增主鍵
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255)
);

-- UUID 主鍵
CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id INTEGER
);

-- 複合主鍵
CREATE TABLE user_roles (
  user_id INTEGER,
  role_id INTEGER,
  PRIMARY KEY (user_id, role_id)
);

索引（Index）

索引可以加快資料查詢速度，就像書的目錄。

-- 建立索引
CREATE INDEX idx_users_email ON users(email);

-- 多欄位索引
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 查看索引
\d users

何時使用索引：

• 經常用於 WHERE、JOIN、ORDER BY 的欄位
• 大資料表（數千筆以上）

不適合使用索引：

• 小型資料表
• 經常更新的欄位
• 低選擇性的欄位（如布林值）

資料表關聯

外鍵（Foreign Key）

外鍵用來建立資料表之間的關聯，確保資料完整性。

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  user_id INTEGER NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

ON DELETE 選項：

• CASCADE：刪除使用者時，連帶刪除該使用者的所有文章
• SET NULL：刪除使用者時，文章的 user_id 設為 NULL
• RESTRICT：如果有文章，不允許刪除使用者

JOIN 查詢

INNER JOIN（內連接）

只返回兩個資料表都有匹配的資料：

SELECT 
  posts.title,
  users.name as author
FROM posts
INNER JOIN users ON posts.user_id = users.id;

LEFT JOIN（左連接）

返回左表的所有資料，右表沒有匹配的顯示 NULL：

SELECT 
  users.name,
  COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.name;

多對多關係

透過中介表建立多對多關係：

-- 標籤資料表
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

-- 中介表
CREATE TABLE post_tags (
  post_id INTEGER,
  tag_id INTEGER,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- 查詢文章及其標籤
SELECT 
  p.title,
  STRING_AGG(t.name, ', ') as tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.title;

約束條件（Constraints）

約束條件用來確保資料的完整性和正確性。

NOT NULL

CREATE TABLE products (
  name VARCHAR(100) NOT NULL,    -- 必填
  price DECIMAL(10, 2) NOT NULL  -- 必填
);

UNIQUE

CREATE TABLE users (
  email VARCHAR(255) UNIQUE NOT NULL,    -- 不可重複
  username VARCHAR(50) UNIQUE NOT NULL   -- 不可重複
);

CHECK

CREATE TABLE products (
  price DECIMAL(10, 2) CHECK (price > 0),
  stock INTEGER CHECK (stock >= 0)
);

DEFAULT

CREATE TABLE orders (
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  shipping_fee DECIMAL(10, 2) DEFAULT 0.00
);

組合使用

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER CHECK (age >= 18 AND age <= 120),
  balance DECIMAL(10, 2) DEFAULT 0 CHECK (balance >= 0),
  status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned')),
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

交易（Transaction）

交易確保一系列操作要麼全部成功，要麼全部失敗。

ACID 特性

• Atomicity（原子性）：全部成功或全部失敗
• Consistency（一致性）：交易前後資料保持一致
• Isolation（隔離性）：多個交易互不干擾
• Durability（持久性）：交易完成後資料永久保存

基本語法

BEGIN;  -- 開始交易

-- 執行操作
INSERT INTO users (email, name) VALUES ('test@example.com', 'Test');
UPDATE products SET stock = stock - 1 WHERE id = 1;

COMMIT;  -- 提交交易
-- 或
ROLLBACK;  -- 回滾交易

實際案例：轉帳操作

-- 建立帳戶資料表
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  balance DECIMAL(10, 2) NOT NULL DEFAULT 0,
  CHECK (balance >= 0)
);

-- 轉帳：Alice 轉 200 元給 Bob
BEGIN;

UPDATE accounts SET balance = balance - 200 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

COMMIT;

Savepoint（儲存點）

BEGIN;

INSERT INTO users (email, name) VALUES ('user1@example.com', 'User1');
SAVEPOINT sp1;

INSERT INTO users (email, name) VALUES ('user2@example.com', 'User2');
ROLLBACK TO sp1;  -- 只回滾到儲存點

COMMIT;

查詢效能優化

EXPLAIN 分析查詢

-- 分析查詢執行計畫
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- 詳細分析
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

輸出解讀：

• Seq Scan：循序掃描（慢，沒用到索引）
• Index Scan：索引掃描（快）
• cost：估計成本
• rows：預計返回的列數

常見效能問題與優化

1. 使用索引

-- 沒有索引（慢）
SELECT * FROM posts WHERE user_id = 1;

-- 建立索引（快）
CREATE INDEX idx_posts_user_id ON posts(user_id);

2. 只查詢需要的欄位

-- 避免
SELECT * FROM users;

-- 建議
SELECT id, name, email FROM users;

3. 使用 LIMIT

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

4. 使用 JOIN 避免 N+1 問題

-- 避免多次查詢
SELECT * FROM users;  -- 然後對每個 user 查詢 posts

-- 使用 JOIN 一次查詢
SELECT u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

5. 避免在索引欄位使用函式

-- 不會使用索引
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- 會使用索引
SELECT * FROM users WHERE email = 'alice@example.com';

練習：建立部落格資料庫

-- 使用者資料表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 文章資料表
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  user_id INTEGER NOT NULL,
  view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 標籤資料表
CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

-- 文章標籤關聯表
CREATE TABLE post_tags (
  post_id INTEGER,
  tag_id INTEGER,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- 留言資料表
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 建立索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- 查詢：取得文章列表，包含作者、標籤、留言數
SELECT 
  p.id,
  p.title,
  u.name as author,
  STRING_AGG(DISTINCT t.name, ', ') as tags,
  COUNT(DISTINCT c.id) as comment_count,
  p.created_at
FROM posts p
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, p.title, u.name, p.created_at
ORDER BY p.created_at DESC;