MySQL数据库设计

一、设计流程

1. 需求分析

   • 确定业务场景与数据关系（1:1, 1:N, M:N）
   • 预估数据量级与访问模式（读写比例、QPS要求）
   • 示例：电商系统需处理$10^6$级订单数据，支持每秒$500$次查询

2. 概念模型设计

   • 绘制ER图明确实体关系
   • 标识核心实体：用户($U$)、商品($P$)、订单($O$)
   • 定义实体属性：$$U = {uid, name, email}$$

3. 逻辑模型设计

   • 转换ER图为关系模式
   • 建立关联表结构：

     Users (uid PK, name, email)
     Orders (oid PK, uid FK, total_price)
     

4. 物理模型设计

   • 选择存储引擎：InnoDB（支持事务）/MyISAM（读密集型）
   • 设计索引策略：对order_date字段建立B+树索引
   • 计算存储需求：$$存储量 = 记录数 \times 行平均大小$$

二、设计原则

1. 命名规范

   • 表名：snake_case格式，如order_details
   • 外键字段：关联表名_id，如user_id

2. 主键选择

   • 自增INT/BIGINT（适用大多数场景）
   • UUID（分布式系统）
   • 组合键：PRIMARY KEY (user_id, order_date)

3. 索引策略

   • 最左前缀原则：索引(a,b,c)可加速WHERE a=1 AND b>5
   • 覆盖索引：SELECT a,b FROM tbl WHERE c=1使用索引(c,a,b)

4. 范式应用

   • 第三范式（3NF）：消除传递依赖
   • 反范式设计：适度冗余提升查询性能

三、建模示例

场景：在线教育系统

1. ER模型：

   Student ----< Enrollment >---- Course
       |                             |
       |                             |
   Payment                      Instructor
   

2. 建表语句：

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    INDEX idx_email (email)
) ENGINE=InnoDB;

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    price DECIMAL(8,2) CHECK (price >= 0)
);

CREATE TABLE enrollments (
    enrollment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enroll_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE KEY unique_enrollment (student_id, course_id)
);

四、性能优化

1. 数据类型优化

   • 金额使用DECIMAL(10,2)而非FLOAT
   • 时间戳使用DATETIME(6)支持微秒级精度

2. 分区策略

   • 按时间范围分区：PARTITION BY RANGE (YEAR(order_date))
   • 列表分区：PARTITION BY LIST (region_code)

3. 查询优化

   • 避免SELECT *
   • 使用预编译语句防止SQL注入
   • 限制结果集：LIMIT 100

五、常见误区

1. 过度规范化

   • 错误：为地址信息拆分省/市/区三张表
   • 改进：适度冗余存储完整地址

2. 索引滥用

   • 错误：为所有查询字段单独建索引
   • 改进：使用复合索引并定期分析索引效率

3. 事务误用

   • 错误：10分钟长事务导致锁竞争
   • 改进：事务粒度控制在毫秒级