PostgreSQL 数据库设计与性能优化

-- 第一范式 (1NF)：原子性
-- ❌ 违反 1NF
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  phones VARCHAR(200)  -- "13800138000,13900139000"
);

-- ✅ 符合 1NF
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE user_phones (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  phone VARCHAR(20)
);

-- 第二范式 (2NF)：完全依赖
-- ❌ 违反 2NF
CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  product_name VARCHAR(100),  -- 依赖 product_id，非完整主键
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);

-- ✅ 符合 2NF
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);

-- 第三范式 (3NF)：消除传递依赖
-- ❌ 违反 3NF
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department_id INTEGER,
  department_name VARCHAR(100)  -- 依赖 department_id
);

-- ✅ 符合 3NF
CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department_id INTEGER REFERENCES departments(id)
);

-- 场景：频繁查询订单总金额
-- 范式化设计：需要 JOIN 计算
SELECT 
  o.id,
  SUM(oi.quantity * p.price) as total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY o.id;

-- 反范式化设计：冗余存储
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10, 2);

-- 使用触发器维护
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE orders 
  SET total_amount = (
    SELECT SUM(oi.quantity * p.price)
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = NEW.order_id
  )
  WHERE id = NEW.order_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

-- B-tree 索引（默认）
-- 适用于等值查询、范围查询、排序
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- 哈希索引
-- 仅适用于等值查询
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- GIN 索引
-- 适用于数组、JSONB、全文搜索
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
CREATE INDEX idx_posts_content ON posts USING GIN (to_tsvector('english', content));

-- GiST 索引
-- 适用于几何数据、范围类型、全文搜索
CREATE INDEX idx_locations_coords ON locations USING GIST (coordinates);

-- 部分索引
-- 仅索引满足条件的行
CREATE INDEX idx_orders_pending ON orders(created_at) 
WHERE status = 'pending';

-- 表达式索引
-- 索引计算结果
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- 多列索引
-- 注意列顺序
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 1. 选择性高的列优先
-- ❌ 低选择性
CREATE INDEX idx_users_gender ON users(gender);  -- 男/女

-- ✅ 高选择性
CREATE INDEX idx_users_email ON users(email);

-- 2. 覆盖索引
-- 查询只需要索引中的数据
CREATE INDEX idx_orders_covering ON orders(user_id, status, total_amount);

-- 可以只扫描索引
SELECT total_amount FROM orders 
WHERE user_id = 123 AND status = 'completed';

-- 3. 避免过度索引
-- 每个索引都会增加写入开销
-- 定期检查未使用的索引
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan as index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查看执行计划
EXPLAIN ANALYZE
SELECT * FROM articles 
WHERE category = 'tech' 
AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 输出示例：
-- Limit  (cost=0.43..8.45 rows=20 width=104) (actual time=0.025..0.035 rows=20 loops=1)
--   ->  Index Scan using idx_articles_category_date on articles  (cost=0.43..401.23 rows=1000 width=104) (actual time=0.024..0.032 rows=20 loops=1)
--         Index Cond: ((category = 'tech'::text) AND (created_at > '2024-01-01 00:00:00'::timestamp))
-- Planning Time: 0.150 ms
-- Execution Time: 0.058 ms

-- 关键指标：
-- cost: 估算成本
-- rows: 估算行数 vs 实际行数
-- actual time: 实际执行时间
-- loops: 循环次数

-- 1. 避免 SELECT *
-- ❌ 
SELECT * FROM users WHERE id = 123;

-- ✅
SELECT id, name, email FROM users WHERE id = 123;

-- 2. 使用 EXISTS 替代 IN
-- ❌ 慢
SELECT * FROM articles 
WHERE author_id IN (SELECT id FROM users WHERE role = 'admin');

-- ✅ 快
SELECT * FROM articles a
WHERE EXISTS (
  SELECT 1 FROM users u 
  WHERE u.id = a.author_id AND u.role = 'admin'
);

-- 3. 使用 JOIN 替代子查询
-- ❌ 
SELECT * FROM articles 
WHERE author_id = (SELECT id FROM users WHERE email = 'test@example.com');

-- ✅
SELECT a.* FROM articles a
JOIN users u ON a.author_id = u.id
WHERE u.email = 'test@example.com';

-- 4. 分页优化
-- ❌ OFFSET 深分页
SELECT * FROM articles 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ 游标分页
SELECT * FROM articles 
WHERE created_at < '2024-01-01 00:00:00'  -- 上一页最后一条的时间
ORDER BY created_at DESC 
LIMIT 20;

-- 5. 批量操作
-- ❌ 逐条插入
INSERT INTO logs (message) VALUES ('log1');
INSERT INTO logs (message) VALUES ('log2');
INSERT INTO logs (message) VALUES ('log3');

-- ✅ 批量插入
INSERT INTO logs (message) VALUES 
  ('log1'),
  ('log2'),
  ('log3');

-- PostgreSQL 支持的隔离级别
-- READ UNCOMMITTED（实际等同于 READ COMMITTED）
-- READ COMMITTED（默认）
-- REPEATABLE READ
-- SERIALIZABLE

-- 设置事务隔离级别
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ...
COMMIT;

-- 查看当前隔离级别
SHOW transaction_isolation;

-- 行级锁
SELECT * FROM users WHERE id = 123 FOR UPDATE;
SELECT * FROM users WHERE id = 123 FOR SHARE;

-- 表级锁
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;

-- 死锁处理
-- PostgreSQL 自动检测死锁
-- 设置锁超时
SET lock_timeout = '5s';

-- 查看锁
SELECT 
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.relation = blocking_locks.relation
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

-- 数据库大小
SELECT 
  pg_database.datname,
  pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- 表大小
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- 缓存命中率
SELECT 
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- 慢查询
SELECT 
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 连接数
SELECT 
  count(*) as total_connections,
  state
FROM pg_stat_activity
GROUP BY state;

-- postgresql.conf 关键配置

-- 内存配置
shared_buffers = '4GB'              # 共享缓冲区，通常为系统内存的 25%
work_mem = '256MB'                  # 排序/哈希操作内存
maintenance_work_mem = '1GB'        # 维护操作内存
effective_cache_size = '12GB'       # 预估可用缓存

-- WAL 配置
wal_buffers = '64MB'                # WAL 缓冲区
checkpoint_completion_target = 0.9  # 检查点完成目标
max_wal_size = '4GB'               # 最大 WAL 大小

-- 查询优化
random_page_cost = 1.1              # SSD 存储
effective_io_concurrency = 200      # SSD 存储
max_worker_processes = 8            # CPU 核心数
max_parallel_workers_per_gather = 4 # 并行查询 workers

-- pg_dump 备份
-- 备份单个数据库
pg_dump -h localhost -U postgres -d mydb > backup.sql

-- 备份为自定义格式
pg_dump -h localhost -U postgres -d mydb -Fc > backup.dump

-- 并行备份
pg_dump -h localhost -U postgres -d mydb -j 4 -Fd > backup_dir/

-- pg_restore 恢复
-- 恢复自定义格式
pg_restore -h localhost -U postgres -d mydb backup.dump

-- 并行恢复
pg_restore -h localhost -U postgres -d mydb -j 4 backup_dir/

-- 时间点恢复 (PITR)
-- 1. 配置 WAL 归档
archive_mode = on
archive_command = 'cp %p /archive/%f'

-- 2. 基础备份
pg_basebackup -h localhost -U postgres -D /backup/base

-- 3. 恢复到指定时间
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2024-01-01 12:00:00'