MySQL数据库设计规范与最佳实践指南

🎯 前言

你是否遇到过这些问题：数据库跑得越来越慢？表结构越改越乱？查询语句写得像意大利面条？其实，这些问题的根源往往在于数据库设计阶段就埋下了祸根！

一个好的数据库设计就像建筑的地基，看似简单但决定了整个系统的稳定性和扩展性。本文将深入讲解MySQL数据库设计的核心原则，从三大范式到反范式设计，从表结构设计到字段类型选择，帮你构建高性能、易维护的数据库架构。

为什么数据库设计如此重要？

1. 性能基石：好的设计是高性能的前提，索引再多也救不了烂设计
2. 维护成本：清晰的结构降低开发和运维成本
3. 业务扩展：合理的设计为业务增长提供坚实基础
4. 数据质量：规范的设计保证数据的一致性和完整性

1. 数据库设计三大范式详解

1.1 第一范式（1NF）：字段原子性

核心原则： 表中的每个字段都不可再分，每个字段都是原子性的。

❌ 违反第一范式的设计

-- 错误示例：联系方式字段包含多个信息
CREATE TABLE users_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    contact_info VARCHAR(200)  -- 包含"手机:13800138000,邮箱:zhang@example.com"
);

-- 插入数据
INSERT INTO users_bad VALUES 
(1, '张三', '手机:13800138000,邮箱:zhang@example.com');

问题分析：

• 🚫 查询某个用户的手机号需要字符串截取
• 🚫 无法对手机号或邮箱建立有效索引
• 🚫 数据格式不统一，容易出错

✅ 符合第一范式的设计

-- 正确示例：每个字段都是原子性的
CREATE TABLE users_good (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    INDEX idx_phone (phone),
    INDEX idx_email (email)
);

-- 插入数据
INSERT INTO users_good (name, phone, email) VALUES 
('张三', '13800138000', 'zhang@example.com');

-- 现在可以轻松查询
SELECT * FROM users_good WHERE phone = '13800138000';
SELECT * FROM users_good WHERE email LIKE '%@example.com';

1.2 第二范式（2NF）：完全函数依赖

核心原则： 非主键字段必须完全依赖于主键，不能只依赖主键的一部分。

❌ 违反第二范式的设计

-- 错误示例：订单商品表设计不当
CREATE TABLE order_items_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),    -- 只依赖product_id
    product_price DECIMAL(10,2),  -- 只依赖product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

问题分析：

• 🚫 product_name和product_price只依赖于product_id，不依赖order_id
• 🚫 同一商品信息重复存储，浪费空间
• 🚫 商品信息更新时需要修改多处

✅ 符合第二范式的设计

-- 正确示例：将商品信息分离到独立表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    product_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items_good (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL, -- 下单时的价格，防止历史数据错乱
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 查询时通过JOIN获取完整信息
SELECT oi.*, p.product_name 
FROM order_items_good oi
JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_id = 1001;

1.3 第三范式（3NF）：消除传递依赖

核心原则： 非主键字段之间不能存在传递依赖关系。

❌ 违反第三范式的设计

-- 错误示例：员工表包含部门信息
CREATE TABLE employees_bad (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    dept_name VARCHAR(50),    -- 依赖于dept_id，而不是emp_id
    dept_location VARCHAR(100) -- 依赖于dept_id，而不是emp_id
);

问题分析：

• 🚫 dept_name和dept_location传递依赖于emp_id（通过dept_id）
• 🚫 部门信息重复存储
• 🚫 部门信息更新需要修改多行记录

✅ 符合第三范式的设计

-- 正确示例：分离部门信息
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    dept_location VARCHAR(100),
    manager_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees_good (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2),
    hire_date DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    INDEX idx_dept_id (dept_id)
);

-- 查询员工及部门信息
SELECT e.emp_name, e.salary, d.dept_name, d.dept_location
FROM employees_good e
JOIN departments d ON e.dept_id = d.dept_id;

2. 反范式设计：性能与规范的平衡

2.1 什么时候需要反范式？

虽然范式化设计有很多优点，但在某些场景下，适度的反范式化可以显著提升性能：

• 高并发查询场景：减少JOIN操作
• 报表统计场景：预计算常用指标
• 缓存热点数据：避免频繁关联查询

2.2 反范式设计实践

场景1：订单汇总信息

-- 范式化设计：需要JOIN计算
CREATE TABLE orders_normalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(20)
);

CREATE TABLE order_items_normalized (
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- 查询订单总金额需要复杂计算
SELECT 
    o.order_id,
    o.customer_id,
    SUM(oi.quantity * oi.unit_price) as total_amount
FROM orders_normalized o
JOIN order_items_normalized oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_id;

-- 反范式设计：冗余总금额字段
CREATE TABLE orders_denormalized (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    status VARCHAR(20),
    total_amount DECIMAL(12,2), -- 冗余字段，提升查询性能
    item_count INT,             -- 冗余字段，商品总数
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 通过触发器或应用程序维护冗余字段
DELIMITER //
CREATE TRIGGER update_order_summary 
AFTER INSERT ON order_items_normalized
FOR EACH ROW
BEGIN
    UPDATE orders_denormalized 
    SET total_amount = (
        SELECT SUM(quantity * unit_price) 
        FROM order_items_normalized 
        WHERE order_id = NEW.order_id
    ),
    item_count = (
        SELECT SUM(quantity) 
        FROM order_items_normalized 
        WHERE order_id = NEW.order_id
    )
    WHERE order_id = NEW.order_id;
END//
DELIMITER ;

-- 现在查询订单信息变得非常简单
SELECT order_id, customer_id, total_amount, item_count 
FROM orders_denormalized 
WHERE customer_id = 1001;

场景2：用户统计信息

-- 反范式设计：在用户表中增加统计字段
CREATE TABLE users_with_stats (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    -- 反范式字段：统计信息
    order_count INT DEFAULT 0,           -- 订单总数
    total_spent DECIMAL(12,2) DEFAULT 0, -- 消费总金额
    last_order_date DATE,                -- 最后下单时间
    vip_level ENUM('bronze', 'silver', 'gold', 'platinum') DEFAULT 'bronze',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_vip_level (vip_level),
    INDEX idx_last_order_date (last_order_date)
);

-- 维护统计信息的存储过程
DELIMITER //
CREATE PROCEDURE UpdateUserStats(IN p_user_id INT)
BEGIN
    UPDATE users_with_stats 
    SET 
        order_count = (
            SELECT COUNT(*) 
            FROM orders_denormalized 
            WHERE customer_id = p_user_id
        ),
        total_spent = (
            SELECT COALESCE(SUM(total_amount), 0) 
            FROM orders_denormalized 
            WHERE customer_id = p_user_id AND status = 'completed'
        ),
        last_order_date = (
            SELECT MAX(order_date) 
            FROM orders_denormalized 
            WHERE customer_id = p_user_id
        ),
        vip_level = CASE 
            WHEN total_spent >= 10000 THEN 'platinum'
            WHEN total_spent >= 5000 THEN 'gold'
            WHEN total_spent >= 1000 THEN 'silver'
            ELSE 'bronze'
        END
    WHERE user_id = p_user_id;
END//
DELIMITER ;

2.3 反范式设计注意事项

最佳实践：

1. 谨慎使用：只在性能瓶颈明确时使用
2. 数据一致性：通过触发器、存储过程或应用程序保证一致性
3. 监控维护：定期检查冗余数据的准确性
4. 文档记录：详细记录反范式设计的原因和维护方式

3. 表结构设计原则

3.1 命名规范

3.1.1 表名命名规范

-- ✅ 推荐的表名命名方式
CREATE TABLE user_profiles (        -- 使用下划线分隔，复数形式
    id INT PRIMARY KEY
);

CREATE TABLE order_items (          -- 关联表明确表达关系
    order_id INT,
    product_id INT
);

CREATE TABLE product_categories (   -- 清晰表达业务含义
    category_id INT PRIMARY KEY
);

-- ❌ 不推荐的命名方式
CREATE TABLE UserProfile (          -- 驼峰命名，容易出错
    id INT PRIMARY KEY
);

CREATE TABLE t_user (               -- 无意义前缀
    id INT PRIMARY KEY  
);

CREATE TABLE user (                 -- 单数形式，不够清晰
    id INT PRIMARY KEY
);

3.1.2 字段命名规范

CREATE TABLE users (
    -- ✅ 主键统一命名
    user_id INT PRIMARY KEY AUTO_INCREMENT,  -- 或简单的 id
    
    -- ✅ 字段名清晰明确
    username VARCHAR(50) NOT NULL,
    email_address VARCHAR(100),
    phone_number VARCHAR(20),
    birth_date DATE,
    
    -- ✅ 布尔字段使用is_前缀
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    
    -- ✅ 时间字段统一后缀
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,  -- 软删除
    
    -- ✅ 状态字段使用枚举
    account_status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    
    -- ✅ 外键字段清晰命名
    department_id INT,
    manager_id INT,
    
    -- 索引命名规范
    INDEX idx_username (username),
    INDEX idx_email (email_address),
    INDEX idx_dept_status (department_id, account_status),
    
    -- 外键约束命名规范
    CONSTRAINT fk_users_department 
        FOREIGN KEY (department_id) REFERENCES departments(dept_id),
    CONSTRAINT fk_users_manager 
        FOREIGN KEY (manager_id) REFERENCES users(user_id)
);

3.2 主键设计策略

3.2.1 自增主键 vs UUID

-- 方案1：自增主键（推荐用于大部分场景）
CREATE TABLE orders_auto_inc (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    
    INDEX idx_customer_date (customer_id, order_date)
);

-- 优点：
-- ✅ 性能最优，插入效率高
-- ✅ 存储空间小（8字节 vs 36字节）
-- ✅ 索引效率高，B+树结构紧凑
-- ✅ 排序天然有序

-- 缺点：
-- ❌ 分布式环境下需要协调
-- ❌ 可能暴露业务信息（订单量等）

-- 方案2：UUID主键（适用于分布式系统）
CREATE TABLE orders_uuid (
    order_id CHAR(36) PRIMARY KEY,  -- 或BINARY(16)存储
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    
    INDEX idx_customer_date (customer_id, order_date)
);

-- 使用UUID函数生成
INSERT INTO orders_uuid (order_id, customer_id, order_date, total_amount)
VALUES (UUID(), 1001, '2024-01-15', 299.99);

-- 优点：
-- ✅ 全局唯一，适合分布式
-- ✅ 不暴露业务信息
-- ✅ 可离线生成

-- 缺点：
-- ❌ 存储空间大
-- ❌ 插入性能相对较差
-- ❌ 无序性可能导致页分裂

3.2.2 复合主键设计

-- 适用场景：多对多关系表
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT,
    
    PRIMARY KEY (user_id, role_id),  -- 复合主键
    
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    FOREIGN KEY (assigned_by) REFERENCES users(user_id)
);

-- 时间序列数据的复合主键
CREATE TABLE user_login_logs (
    user_id INT NOT NULL,
    login_date DATE NOT NULL,
    login_hour TINYINT NOT NULL,  -- 0-23
    login_count INT DEFAULT 1,
    
    PRIMARY KEY (user_id, login_date, login_hour),
    
    INDEX idx_date_hour (login_date, login_hour)
);

3.3 索引设计策略

3.3.1 单列索引 vs 复合索引

-- 根据查询模式设计索引
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    brand_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 常见查询模式分析
    
    -- 查询1: WHERE category_id = ? AND status = 'active'
    INDEX idx_category_status (category_id, status),
    
    -- 查询2: WHERE brand_id = ? AND price BETWEEN ? AND ?
    INDEX idx_brand_price (brand_id, price),
    
    -- 查询3: WHERE status = 'active' ORDER BY created_at DESC
    INDEX idx_status_created (status, created_at),
    
    -- 查询4: 全文搜索
    FULLTEXT INDEX ft_product_name (product_name)
);

-- 复合索引使用示例
-- ✅ 能使用idx_category_status索引
SELECT * FROM products 
WHERE category_id = 5 AND status = 'active';

-- ✅ 能使用idx_category_status索引（最左前缀原则）
SELECT * FROM products WHERE category_id = 5;

-- ❌ 不能使用idx_category_status索引
SELECT * FROM products WHERE status = 'active';

3.3.2 覆盖索引设计

-- 设计覆盖索引避免回表查询
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 覆盖索引：包含查询所需的所有字段
    INDEX idx_status_cover (status, username, email, last_login_at),
    
    -- 普通索引
    UNIQUE INDEX uk_username (username),
    UNIQUE INDEX uk_email (email)
);

-- ✅ 使用覆盖索引，无需回表
EXPLAIN SELECT username, email, last_login_at 
FROM user_profiles 
WHERE status = 'active';
-- Extra: Using index

-- ❌ 需要回表查询
EXPLAIN SELECT username, email, phone, last_login_at 
FROM user_profiles 
WHERE status = 'active';
-- Extra: Using index condition

4. 字段类型选择策略

4.1 数值类型选择

4.1.1 整数类型对比

-- 实际应用示例
CREATE TABLE user_analytics (
    user_id INT NOT NULL,                    -- 用户ID，4字节足够
    login_count MEDIUMINT UNSIGNED DEFAULT 0, -- 登录次数，3字节
    age TINYINT UNSIGNED,                    -- 年龄，1字节
    year_registered SMALLINT UNSIGNED,       -- 注册年份，2字节
    total_points BIGINT UNSIGNED DEFAULT 0,  -- 积分可能很大，8字节
    
    -- 状态字段用最小的类型
    is_vip BOOLEAN DEFAULT FALSE,           -- 等同于TINYINT(1)
    account_level TINYINT UNSIGNED DEFAULT 1, -- 1-10级别
    
    PRIMARY KEY (user_id)
);

4.1.2 小数类型选择

-- 精确小数：DECIMAL
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    total_amount DECIMAL(10,2) NOT NULL,     -- 精确到分，金额计算
    tax_rate DECIMAL(5,4) NOT NULL,          -- 税率，如0.0825
    discount_amount DECIMAL(8,2) DEFAULT 0,
    
    -- 避免使用FLOAT/DOUBLE处理金额
    -- ❌ price FLOAT,  -- 可能有精度损失
    -- ❌ amount DOUBLE -- 金融计算不推荐
);

-- 近似小数：FLOAT/DOUBLE
CREATE TABLE product_ratings (
    product_id INT NOT NULL,
    average_rating FLOAT(3,2),              -- 评分，如4.85
    rating_count INT UNSIGNED DEFAULT 0,
    
    -- 科学计算可以使用DOUBLE
    weight_kg DOUBLE,                       -- 重量，可能需要高精度
    
    PRIMARY KEY (product_id)
);

-- 金额计算最佳实践：使用整数存储分
CREATE TABLE orders_alternative (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    total_amount_cents INT UNSIGNED NOT NULL, -- 存储分为单位
    currency_code CHAR(3) DEFAULT 'CNY',      -- 货币代码
    
    -- 查询时转换为元
    -- SELECT total_amount_cents / 100.0 AS total_amount_yuan
);

4.2 字符串类型选择

4.2.1 定长 vs 变长字符串

-- 定长字符串：CHAR
CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,        -- ISO国家代码，固定2位
    currency_code CHAR(3) NOT NULL,          -- 货币代码，固定3位
    phone_prefix CHAR(5),                    -- 电话前缀，相对固定
    
    country_name VARCHAR(100) NOT NULL       -- 国家名称，变长
);

-- 变长字符串：VARCHAR
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,           -- 用户名，变长
    email VARCHAR(254) NOT NULL,             -- 邮箱最大长度254
    first_name VARCHAR(50),                  -- 姓名，变长
    last_name VARCHAR(50),
    
    -- 状态用ENUM更高效
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    
    -- 长文本用TEXT
    bio TEXT,                                -- 个人简介
    
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
);

-- 不同VARCHAR长度的存储开销
-- VARCHAR(50)   实际存储：内容长度 + 1字节长度前缀
-- VARCHAR(255)  实际存储：内容长度 + 1字节长度前缀  
-- VARCHAR(256)  实际存储：内容长度 + 2字节长度前缀
-- VARCHAR(65535) 实际存储：内容长度 + 2字节长度前缀

4.2.2 TEXT类型使用

-- 不同TEXT类型的选择
CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    summary TINYTEXT,                        -- 摘要，最大255字节
    content MEDIUMTEXT,                      -- 正文，最大16MB
    
    -- 索引优化：TEXT字段需要指定长度
    INDEX idx_title (title),
    INDEX idx_summary (summary(100))         -- TEXT索引需要指定前缀长度
);

-- JSON字段使用（MySQL 5.7+）
CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    preferences JSON,                        -- JSON数据类型
    
    -- JSON字段的虚拟列和索引
    theme VARCHAR(50) GENERATED ALWAYS AS (preferences->>'$.theme') STORED,
    language VARCHAR(10) GENERATED ALWAYS AS (preferences->>'$.language') STORED,
    
    INDEX idx_theme (theme),
    INDEX idx_language (language)
);

-- JSON数据操作示例
INSERT INTO user_preferences (user_id, preferences) VALUES 
(1, '{"theme": "dark", "language": "zh-CN", "notifications": {"email": true, "sms": false}}');

-- 查询JSON数据
SELECT user_id, preferences->>'$.theme' as theme 
FROM user_preferences 
WHERE preferences->>'$.language' = 'zh-CN';

4.3 日期时间类型选择

4.3.1 日期时间类型对比

-- 日期时间字段设计最佳实践
CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(100) NOT NULL,
    
    -- 日期字段
    event_date DATE NOT NULL,               -- 活动日期
    registration_deadline DATE,             -- 报名截止日期
    
    -- 时间字段  
    start_time TIME,                        -- 开始时间，如 14:30:00
    duration_minutes SMALLINT UNSIGNED,     -- 持续时间（分钟）
    
    -- 完整的日期时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 特定时区的时间用DATETIME
    scheduled_at DATETIME,                  -- 预定时间，不受时区影响
    
    INDEX idx_event_date (event_date),
    INDEX idx_created_at (created_at)
);

-- 时间戳存储的替代方案
CREATE TABLE user_sessions (
    session_id CHAR(32) PRIMARY KEY,
    user_id INT NOT NULL,
    
    -- 方案1：使用TIMESTAMP（推荐）
    login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expire_at TIMESTAMP,
    
    -- 方案2：使用BIGINT存储Unix时间戳
    -- login_timestamp BIGINT UNSIGNED,     -- 毫秒级时间戳
    -- expire_timestamp BIGINT UNSIGNED,
    
    INDEX idx_user_login (user_id, login_at),
    INDEX idx_expire (expire_at)
);

4.4 枚举和集合类型

4.4.1 ENUM类型使用

-- ENUM类型适合固定的状态值
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    
    -- 订单状态，使用ENUM
    status ENUM('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled') 
           DEFAULT 'pending',
    
    -- 支付方式
    payment_method ENUM('credit_card', 'debit_card', 'paypal', 'alipay', 'wechat_pay'),
    
    -- 优先级
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_status (status),
    INDEX idx_priority (priority)
);

-- ENUM的优点：
-- ✅ 存储效率高（1-2字节）
-- ✅ 自动数据验证
-- ✅ 查询效率高

-- ENUM的缺点和注意事项：
-- ❌ 修改枚举值需要ALTER TABLE  
-- ❌ 排序按内部数值而非字符串
-- ❌ 不利于国际化

-- 查询ENUM字段
SELECT status, COUNT(*) as count
FROM orders 
GROUP BY status
ORDER BY FIELD(status, 'pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled');

4.4.2 SET类型使用

-- SET类型适合多选场景
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    
    -- 用户权限，可以选择多个
    permissions SET('read', 'write', 'delete', 'admin', 'export', 'import') DEFAULT 'read',
    
    -- 通知设置
    notification_types SET('email', 'sms', 'push', 'wechat') DEFAULT 'email',
    
    -- 兴趣标签
    interests SET('technology', 'sports', 'music', 'travel', 'food', 'art', 'reading') DEFAULT '',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SET字段的查询操作
-- 插入数据
INSERT INTO users (username, permissions, notification_types, interests) VALUES
('john_doe', 'read,write,export', 'email,push', 'technology,music,travel');

-- 查询包含特定权限的用户
SELECT username, permissions 
FROM users 
WHERE FIND_IN_SET('admin', permissions);

-- 查询包含多个权限的用户
SELECT username, permissions 
FROM users 
WHERE permissions & (1 | 2 | 16);  -- read | write | export

-- 添加权限
UPDATE users 
SET permissions = CONCAT(permissions, ',admin')
WHERE user_id = 1 AND NOT FIND_IN_SET('admin', permissions);

5. 数据库设计最佳实践总结

5.1 设计原则清单

✅ 应该做的事情

1. 规范命名

   • 表名、字段名使用下划线命名法
   • 名称要有业务含义，避免拼音和缩写
   • 布尔字段使用is_前缀

2. 合理选择数据类型

   • 使用最小满足需求的数据类型
   • 金额使用DECIMAL，避免FLOAT
   • 状态字段使用ENUM

3. 索引设计

   • 基于查询模式设计复合索引
   • 利用覆盖索引避免回表
   • 定期分析索引使用情况

4. 约束设计

   • 合理使用外键约束
   • 添加CHECK约束保证数据质量
   • 重要字段设置NOT NULL

❌ 不应该做的事情

1. 过度范式化

   • 不要盲目追求第三范式
   • 考虑查询性能适度反范式

2. 数据类型误用

   • 避免用VARCHAR存储数值
   • 不要用TEXT存储短字符串
   • 避免NULL值过多

3. 索引滥用

   • 不要在每个字段都建索引
   • 避免过长的复合索引
   • 删除无用的索引

5.2 性能优化要点

-- 优化示例：商品表设计
CREATE TABLE products_optimized (
    -- 主键使用自增INT
    product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    
    -- 字符串字段长度适中
    product_name VARCHAR(100) NOT NULL,
    product_code VARCHAR(50) NOT NULL,
    
    -- 分类用外键关联
    category_id SMALLINT UNSIGNED NOT NULL,
    brand_id SMALLINT UNSIGNED NOT NULL,
    
    -- 价格用整数存储（分为单位）
    price_cents INT UNSIGNED NOT NULL,
    
    -- 库存信息
    stock_quantity MEDIUMINT UNSIGNED DEFAULT 0,
    reserved_quantity MEDIUMINT UNSIGNED DEFAULT 0,
    
    -- 状态用ENUM
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    
    -- 属性用JSON存储（MySQL 5.7+）
    attributes JSON,
    
    -- 时间字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 软删除
    deleted_at TIMESTAMP NULL,
    
    -- 索引设计
    UNIQUE KEY uk_product_code (product_code),
    INDEX idx_category_status (category_id, status),
    INDEX idx_brand_status (brand_id, status),
    INDEX idx_price_status (price_cents, status),
    INDEX idx_stock (stock_quantity),
    INDEX idx_created (created_at),
    
    -- 外键约束
    CONSTRAINT fk_products_category 
        FOREIGN KEY (category_id) REFERENCES categories(category_id),
    CONSTRAINT fk_products_brand 
        FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

5.3 维护和监控

-- 定期维护SQL脚本

-- 1. 检查表大小和索引使用情况
SELECT 
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size(MB)',
    TABLE_ROWS,
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) AS 'Index Size(MB)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 2. 检查未使用的索引
SELECT 
    s.TABLE_SCHEMA,
    s.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME
FROM information_schema.STATISTICS s
LEFT JOIN information_schema.INDEX_STATISTICS i
    ON s.TABLE_SCHEMA = i.TABLE_SCHEMA 
    AND s.TABLE_NAME = i.TABLE_NAME 
    AND s.INDEX_NAME = i.INDEX_NAME
WHERE s.TABLE_SCHEMA = 'your_database'
    AND i.INDEX_NAME IS NULL
    AND s.INDEX_NAME != 'PRIMARY';

-- 3. 检查数据分布
SELECT 
    status,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM products), 2) as percentage
FROM products 
GROUP BY status;

💡 总结

数据库设计是一门平衡的艺术，需要在规范性、性能、维护性之间找到最佳平衡点。记住以下核心原则：

1. 先理解业务：设计前充分理解业务需求和查询模式
2. 遵循规范：在三大范式基础上适度优化
3. 选择合适的数据类型：存储效率和查询性能并重
4. 索引策略：基于实际查询设计，避免过度索引
5. 持续优化：定期审查和优化表结构

好的数据库设计是系统性能的基石，值得我们投入足够的时间和精力去规划和完善！