数据库设计 database-design

---

名称：数据库设计 描述：用于PostgreSQL、MySQL和NoSQL数据库的数据库模式设计、优化和迁移模式。用于设计模式、编写迁移或优化查询。 来源：wshobson/agents 许可证：MIT

数据库设计

模式设计原则

规范化指南

-- 1NF：原子值，无重复组
-- 2NF：无对复合键的部分依赖
-- 3NF：无传递依赖

-- 用户表（规范化）
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 地址表（独立实体）
CREATE TABLE addresses (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  street VARCHAR(255),
  city VARCHAR(100),
  country VARCHAR(100),
  is_primary BOOLEAN DEFAULT false
);

为性能进行反规范化

-- 当读取性能比写入一致性更重要时
CREATE TABLE order_summaries (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),
  customer_name VARCHAR(255),  -- 从客户表反规范化
  total_amount DECIMAL(10,2),
  item_count INTEGER,
  last_updated TIMESTAMPTZ DEFAULT NOW()
);

索引设计

常见索引模式

-- B-tree（默认）用于相等和范围查询
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（顺序很重要！）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 部分索引用于特定条件
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- GIN索引用于数组/JSONB列
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- 覆盖索引（包含额外列）
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);

索引分析

-- 检查索引使用情况
SELECT
  schemaname, tablename, indexname,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查找缺失索引
SELECT
  relname, seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY seq_tup_read DESC;

迁移模式

安全迁移模板

-- 始终使用事务
BEGIN;

-- 添加带默认值的列（在PG 11+中非阻塞）
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- 并发创建索引（不锁定表）
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);

-- 分批回填数据
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 10000;
COMMIT;

零停机迁移

1. 添加新列（可空）
2. 部署代码，同时写入两列
3. 回填旧数据
4. 部署代码，从新列读取
5. 删除旧列

查询优化

EXPLAIN 分析

-- 始终使用 EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- 需要关注的关键指标：
-- - 顺序扫描 vs 索引扫描
-- - 实际行数 vs 估计行数
-- - 缓冲区：共享命中 vs 读取

常见优化

-- 对于大型集，使用 EXISTS 替代 IN
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 使用键集（游标）分页替代 OFFSET
SELECT * FROM posts
WHERE created_at < '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 对复杂查询使用 CTE
WITH active_users AS (
  SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);

约束与数据完整性

-- 主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 带级联的外键
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 检查约束
ALTER TABLE products ADD CONSTRAINT chk_price_positive
  CHECK (price >= 0);

-- 唯一约束
ALTER TABLE users ADD CONSTRAINT uniq_users_email UNIQUE (email);

-- 排除约束（无重叠范围）
ALTER TABLE reservations ADD CONSTRAINT excl_no_overlap
  EXCLUDE USING gist (room_id WITH =, tsrange(start_time, end_time) WITH &&);

最佳实践

• 使用 UUID 用于公开 ID，SERIAL/BIGSERIAL 用于内部
• 始终添加 created_at 和 updated_at 时间戳
• 对重要数据使用软删除（deleted_at）
• 在分布式系统中设计为最终一致性
• 在迁移文件中记录模式决策
• 部署前在生产规模数据上测试迁移