MySQL表设计

表设计是数据库设计的核心环节，直接影响数据库的性能、可维护性和数据完整性。本文档将详细介绍MySQL表设计的最佳实践、规范化原则和设计考虑因素。

表设计原则

1. 数据完整性

数据完整性确保数据的准确性和一致性，包括：

实体完整性：确保每行数据都有唯一的标识

sql

-- 主键约束确保实体完整性
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL
);

域完整性：限制列的取值范围和类型

sql

-- 使用数据类型和约束确保域完整性
CREATE TABLE employees (
    id INT PRIMARY KEY,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age TINYINT CHECK (age >= 18 AND age <= 65),
    department ENUM('IT', 'HR', 'Finance', 'Marketing')
);

引用完整性：维护表之间的关系

sql

-- 外键约束确保引用完整性
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

用户定义完整性：根据业务需求定义的约束

sql

-- 检查约束确保用户定义的完整性
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) NOT NULL,
    discount DECIMAL(5,2) DEFAULT 0.00,
    CHECK (price > 0),
    CHECK (discount >= 0 AND discount <= 0.5), -- 最大50%折扣
    CHECK (price * (1 - discount) > 0) -- 折后价格必须大于0
);

2. 规范化原则

规范化是消除数据冗余的过程，通常分为几个范式：

第一范式（1NF）：确保每个列都是原子的，不可再分

sql

-- 遵循1NF：每个列都是原子值
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL
    -- 错误示例：address VARCHAR(200) 包含多个信息（街道、城市、邮编等）
);

-- 正确的1NF设计
CREATE TABLE customer_addresses (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    street VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

第二范式（2NF）：在1NF基础上，消除部分依赖

sql

-- 违反2NF的设计
-- orders表包含订单信息和产品信息，产品信息依赖于product_id而非整个主键
CREATE TABLE orders_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100), -- 这个依赖于product_id，而非主键
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 遵循2NF的设计
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

第三范式（3NF）：在2NF基础上，消除传递依赖

sql

-- 违反3NF的设计
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100), -- 依赖于department_id，而非主键
    department_budget DECIMAL(12,2) -- 也依赖于department_id
);

-- 遵循3NF的设计
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    budget DECIMAL(12,2) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

3. 反规范化

在某些情况下，为了提高查询性能，可以适度反规范化：

sql

-- 反规范化示例：在订单表中存储客户信息以避免连接查询
CREATE TABLE orders_denormalized (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    customer_name VARCHAR(100), -- 反规范化：存储客户名称
    customer_email VARCHAR(100), -- 反规范化：存储客户邮箱
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL
);

主键设计

自增主键 vs 业务主键

自增主键：

sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL
);

UUID主键：

sql

CREATE TABLE users_uuid (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()), -- UUID主键
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL
);

复合主键：

sql

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    assigned_date DATE,
    PRIMARY KEY (user_id, role_id) -- 复合主键
);

主键选择考虑因素

性能：整数自增主键通常性能最好
分布式：UUID适合分布式系统
业务需求：某些业务场景需要有意义的主键

索引设计

索引类型

主键索引：

sql

-- 主键自动创建唯一索引
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 自动创建主键索引
    name VARCHAR(100) NOT NULL
);

唯一索引：

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uk_email (email) -- 唯一索引
);

普通索引：

sql

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_author (author_id), -- 普通索引
    INDEX idx_created (created_at), -- 普通索引
    FULLTEXT INDEX ft_title_content (title, content) -- 全文索引
);

复合索引：

sql

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered') NOT NULL,
    created_at DATE NOT NULL,
    
    INDEX idx_user_status_created (user_id, status, created_at) -- 复合索引
);

索引设计原则

选择性原则：选择性高的列优先创建索引
最左前缀原则：复合索引遵循最左前缀原则
覆盖索引：尽量让索引包含查询所需的所有列

关系设计

一对一关系

sql

-- 用户和用户资料一对一关系
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY, -- 与users表的主键相同
    bio TEXT,
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

一对多关系

sql

-- 用户和订单一对多关系
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT NOT NULL, -- 外键指向users表
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

多对多关系

sql

-- 学生和课程多对多关系
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- 关联表
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE NOT NULL,
    grade DECIMAL(3,2),
    PRIMARY KEY (student_id, course_id), -- 复合主键
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

字符集和排序规则

sql

-- 推荐使用utf8mb4字符集
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

分区表设计

sql

-- 按日期分区的订单表
CREATE TABLE orders_partitioned (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

设计考虑因素

性能考虑

适当的数据类型：选择最小的数据类型以节省空间
索引策略：根据查询模式创建合适的索引
避免过度规范化：适度反规范化以提高查询性能

可维护性考虑

清晰的命名约定：使用有意义的表名和列名
注释：为表和列添加注释
文档化：记录表结构和业务逻辑

扩展性考虑

预留字段：考虑未来可能的扩展需求
垂直分区：将大表拆分为多个相关表
水平分区：考虑数据量增长时的分区策略

命名约定

sql

-- 推荐的命名约定
CREATE TABLE user_accounts (        -- 使用复数形式，下划线分隔
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) COMMENT '用户账户表';

设计检查清单

在完成表设计后，检查以下项目：

[ ] 是否选择了合适的数据类型
[ ] 是否定义了适当的约束
[ ] 是否设计了合适的索引
[ ] 是否考虑了数据完整性
[ ] 是否遵循了规范化原则
[ ] 是否考虑了性能影响
[ ] 是否使用了清晰的命名约定
[ ] 是否添加了必要的注释

总结

良好的表设计是数据库系统成功的基础。在设计表时，需要平衡规范化、性能、可维护性和扩展性等多个因素。通过遵循最佳实践和设计原则，可以创建出高效、可靠、易于维护的数据库表结构。

MySQL表设计 #

表设计原则 #

1. 数据完整性 #

2. 规范化原则 #

3. 反规范化 #

主键设计 #

自增主键 vs 业务主键 #

主键选择考虑因素 #

索引设计 #

索引类型 #

索引设计原则 #

关系设计 #

一对一关系 #

一对多关系 #

多对多关系 #

字符集和排序规则 #

分区表设计 #

设计考虑因素 #

性能考虑 #

可维护性考虑 #

扩展性考虑 #

命名约定 #

设计检查清单 #

总结 #