03-数据库设计与范式¶

-- 订单表冗余用户名（避免JOIN用户表）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),  -- 冗余字段
    amount DECIMAL(10,2),
    created_at TIMESTAMP
);

-- 适用场景：订单查询频繁，用户名不常修改

-- 商品销量统计表（实时计算慢，预计算存储）
CREATE TABLE product_sales_stats (
    product_id INT PRIMARY KEY,
    total_sales INT DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    last_7_days_sales INT DEFAULT 0,
    last_30_days_sales INT DEFAULT 0,
    updated_at TIMESTAMP
);

-- 通过触发器或定时任务更新

-- 大表垂直拆分
-- 用户基础信息表（频繁查询）
CREATE TABLE users_basic (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    status TINYINT
);

-- 用户扩展信息表（偶尔查询）
CREATE TABLE users_extra (
    user_id INT PRIMARY KEY,
    bio TEXT,
    address VARCHAR(255),
    preferences JSON
);

-- 订单表只保留最近1年数据
CREATE TABLE orders (
    -- 当前活跃订单
);

CREATE TABLE orders_archive (
    -- 历史归档订单
    archive_date DATE
);

-- 普通索引
CREATE INDEX idx_name ON users(name);

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

-- 组合索引（最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);
-- 可以命中：WHERE name='张三'
-- 可以命中：WHERE name='张三' AND age=20
-- 不能命中：WHERE age=20（缺少最左列）

-- 前缀索引（长字符串）
CREATE INDEX idx_desc ON articles(description(100));

-- 全文索引（MySQL）
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 1. 主键选择
-- 推荐：自增整数
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ...
);

-- 2. 外键索引（自动创建）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    -- 自动创建user_id索引
);

-- 3. 覆盖索引（查询字段都在索引中）
CREATE INDEX idx_user_status ON users(user_id, status);  -- INDEX索引加速查询
-- SELECT user_id, status FROM users WHERE user_id = 1;
-- 不需要回表查询

-- 4. 避免冗余索引
-- 已有 idx_name_age，不需要再建 idx_name

-- 5. 定期分析和优化
ANALYZE TABLE users;
OPTIMIZE TABLE users;

-- 用户类型：学生、教师、管理员
-- 方案1：单表（适合类型差异小）
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_type ENUM('student', 'teacher', 'admin'),
    username VARCHAR(50),
    email VARCHAR(100),
    -- 学生特有
    student_number VARCHAR(20),
    enrollment_date DATE,
    -- 教师特有
    teacher_number VARCHAR(20),
    department VARCHAR(50),
    hire_date DATE,
    -- 管理员特有
    admin_level TINYINT
);

-- 方案2：多表（适合类型差异大）
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_type ENUM('student', 'teacher', 'admin'),
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE students (
    user_id INT PRIMARY KEY,
    student_number VARCHAR(20),
    enrollment_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE teachers (
    user_id INT PRIMARY KEY,
    teacher_number VARCHAR(20),
    department VARCHAR(50),
    hire_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 方案1：独立数据库（最安全，成本最高）
-- 每个租户一个数据库

-- 方案2：共享数据库，独立Schema
-- 每个租户一个Schema

-- 方案3：共享Schema，租户ID隔离（最常用）
CREATE TABLE tenants (
    tenant_id INT PRIMARY KEY,
    tenant_name VARCHAR(100),
    created_at TIMESTAMP
);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    tenant_id INT,  -- 租户ID
    username VARCHAR(50),
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id)
);

-- 所有查询都要带 tenant_id
SELECT * FROM users WHERE tenant_id = 1 AND username = '张三';

-- 不真正删除，而是标记删除
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    is_deleted BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 删除操作（实际是更新）
UPDATE products
SET is_deleted = TRUE, deleted_at = NOW()
WHERE product_id = 1;

-- 查询操作（默认过滤已删除）
SELECT * FROM products WHERE is_deleted = FALSE;

-- 恢复删除
UPDATE products
SET is_deleted = FALSE, deleted_at = NULL
WHERE product_id = 1;

-- 记录数据变更历史
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    updated_at TIMESTAMP
);

CREATE TABLE users_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    username VARCHAR(50),
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    operated_by INT,
    operated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 通过触发器自动记录
DELIMITER //
CREATE TRIGGER users_audit_trigger
AFTER UPDATE ON users
FOR EACH ROW
BEGIN  -- 事务保证操作原子性
    INSERT INTO users_audit (user_id, username, operation, operated_by)
    VALUES (OLD.user_id, OLD.username, 'UPDATE', @current_user_id);
END//
DELIMITER ;

-- 方案1：JSON数组（MySQL 5.7+）
CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    tags JSON  -- ["AI", "Python", "Deep Learning"]
);

-- 查询带特定标签的文章
SELECT * FROM articles WHERE JSON_CONTAINS(tags, '"AI"');

-- 方案2：关联表（更灵活，支持标签统计）
CREATE TABLE tags (
    tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_name VARCHAR(50) UNIQUE
);

CREATE TABLE article_tags (
    article_id INT,
    tag_id INT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(article_id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);

-- 查询带特定标签的文章
SELECT a.* FROM articles a
JOIN article_tags at ON a.article_id = at.article_id  -- JOIN连接多个表
JOIN tags t ON at.tag_id = t.tag_id
WHERE t.tag_name = 'AI';

-- 统计热门标签
SELECT t.tag_name, COUNT(*) AS article_count
FROM tags t
JOIN article_tags at ON t.tag_id = at.tag_id
GROUP BY t.tag_id  -- GROUP BY分组；HAVING过滤分组
ORDER BY article_count DESC;

-- 实验主表
CREATE TABLE experiments (
    exp_id VARCHAR(36) PRIMARY KEY,  -- UUID
    exp_name VARCHAR(100),
    project_id INT,
    model_type VARCHAR(50),
    status ENUM('running', 'completed', 'failed'),
    git_commit VARCHAR(40),
    config JSON,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    INDEX idx_project_status (project_id, status),
    INDEX idx_created_at (created_at)
);

-- 实验指标表
CREATE TABLE experiment_metrics (
    metric_id INT PRIMARY KEY AUTO_INCREMENT,
    exp_id VARCHAR(36),
    metric_name VARCHAR(50),
    metric_value DECIMAL(10,6),
    step INT,
    logged_at TIMESTAMP,
    FOREIGN KEY (exp_id) REFERENCES experiments(exp_id),
    INDEX idx_exp_metric (exp_id, metric_name, step)
);

-- 实验参数表（方便查询对比）
CREATE TABLE experiment_params (
    param_id INT PRIMARY KEY AUTO_INCREMENT,
    exp_id VARCHAR(36),
    param_name VARCHAR(50),
    param_value VARCHAR(255),
    FOREIGN KEY (exp_id) REFERENCES experiments(exp_id),
    INDEX idx_exp_param (exp_id, param_name)
);

-- 特征定义表
CREATE TABLE feature_definitions (
    feature_id INT PRIMARY KEY AUTO_INCREMENT,
    feature_name VARCHAR(100),
    feature_type ENUM('numeric', 'categorical', 'embedding'),
    entity_type ENUM('user', 'item', 'interaction'),
    description TEXT,
    owner VARCHAR(50),
    created_at TIMESTAMP
);

-- 离线特征表（批量计算）
CREATE TABLE offline_features (
    entity_id VARCHAR(50),
    feature_id INT,
    feature_value VARCHAR(255),
    computed_at TIMESTAMP,
    PRIMARY KEY (entity_id, feature_id),
    FOREIGN KEY (feature_id) REFERENCES feature_definitions(feature_id)
);

-- 在线特征表（实时更新，Redis更合适，这里做备份）
CREATE TABLE online_features (
    entity_id VARCHAR(50),
    feature_id INT,
    feature_value VARCHAR(255),
    updated_at TIMESTAMP,
    PRIMARY KEY (entity_id, feature_id),
    FOREIGN KEY (feature_id) REFERENCES feature_definitions(feature_id)
);