Database Design

Database Design

帮助开发者设计高效、可扩展的数据库结构，遵循最佳实践。

设计流程

1. 需求分析：理解业务实体和关系
2. 概念设计：绘制 ER 图
3. 逻辑设计：定义表结构和关系
4. 物理设计：索引、分区策略
5. 优化迭代：根据查询模式调整

Schema 设计原则

命名规范

-- 表名：小写复数，下划线分隔
users, order_items, user_profiles

-- 字段名：小写，下划线分隔
created_at, user_id, is_active

-- 主键：id 或 表名单数_id
id, user_id

-- 外键：关联表单数_id
user_id, order_id

基础表模板

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 更新时间触发器
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

索引策略

何时创建索引

索引类型选择

-- B-Tree：默认，适合等值和范围查询
CREATE INDEX idx_users_email ON users(email);

-- 复合索引：多字段查询
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引：条件过滤
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- 唯一索引：保证唯一性
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

关系设计

一对多

-- 用户有多个订单
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

多对多

-- 用户和角色的多对多关系
CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

常见反模式

性能优化

1. EXPLAIN 分析：检查查询计划
2. **避免 SELECT ***：只查询需要的字段
3. 分页优化：使用游标分页替代 OFFSET
4. 连接池：复用数据库连接
5. 读写分离：主从架构分担负载

参考资源

• PostgreSQL 文档: https://www.postgresql.org/docs/
• MySQL 优化指南: https://dev.mysql.com/doc/refman/8.0/en/optimization.html