MySQL 数据库设计规范

1. 概述

1.1 目的

本规范旨在统一 MySQL 数据库设计标准，确保数据库设计的一致性、可维护性和高性能，为开发团队提供明确的数据库设计指导原则。

1.2 适用范围

所有使用 MySQL 数据库的项目
数据库设计、开发、维护人员
系统架构师和开发工程师

1.3 基本原则

一致性原则：统一的命名规范和设计模式
性能优先：设计时考虑查询性能和数据访问模式
可维护性：结构清晰，便于后续维护和扩展
安全性：确保数据安全和访问控制

2. 命名规范

2.1 基本规则

使用小写字母和下划线（snake_case）
避免使用 MySQL 保留字和关键字
命名要有意义，避免使用缩写
长度控制在 64 个字符以内

2.2 数据库命名

-- 格式：项目名_模块名
-- 示例
duke_user_center    -- 用户中心数据库
duke_order_system   -- 订单系统数据库
duke_payment_gateway -- 支付网关数据库

2.3 表命名规范

-- 格式：模块前缀_实体名
-- 示例
sys_user           -- 系统用户表
sys_role           -- 系统角色表
sys_permission     -- 系统权限表
biz_order          -- 业务订单表
biz_product        -- 业务产品表
log_operation      -- 操作日志表

2.4 字段命名规范

-- 主键字段
id                  -- 主键，统一使用 id

-- 外键字段
user_id            -- 用户ID外键
order_id           -- 订单ID外键
parent_id          -- 父级ID

-- 时间字段
created_at         -- 创建时间
updated_at         -- 更新时间
deleted_at         -- 删除时间（软删除）

-- 状态字段
status             -- 状态字段
is_deleted         -- 是否删除（布尔类型）
is_enabled         -- 是否启用（布尔类型）

-- 业务字段
user_name          -- 用户名
email_address      -- 邮箱地址
phone_number       -- 手机号码

2.5 索引命名规范

-- 主键索引
PRIMARY KEY        -- 主键索引（系统自动命名）

-- 唯一索引
uk_table_column    -- 唯一索引，如：uk_user_email

-- 普通索引
idx_table_column   -- 普通索引，如：idx_user_created_at

-- 复合索引
idx_table_col1_col2 -- 复合索引，如：idx_user_status_created

-- 外键索引
fk_table_column    -- 外键索引，如：fk_order_user_id

3. 表设计规范

3.1 基础表结构

每个表必须包含以下基础字段：

CREATE TABLE example_table (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    created_by BIGINT UNSIGNED COMMENT '创建人ID',
    updated_by BIGINT UNSIGNED COMMENT '更新人ID',
    is_deleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
    version INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '版本号（乐观锁）',
    PRIMARY KEY (id),
    INDEX idx_created_at (created_at),
    INDEX idx_updated_at (updated_at),
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='示例表';

3.2 字段设计规范

3.2.1 数据类型选择

-- 整数类型
TINYINT(1)         -- 布尔值：0/1
SMALLINT           -- 小整数：-32768 到 32767
INT                -- 整数：-2147483648 到 2147483647
BIGINT             -- 大整数：主键、外键推荐使用

-- 浮点类型
DECIMAL(10,2)      -- 金额字段，精确计算
FLOAT              -- 单精度浮点
DOUBLE             -- 双精度浮点

-- 字符串类型
CHAR(32)           -- 固定长度字符串，如：UUID
VARCHAR(255)       -- 变长字符串，常用长度
TEXT               -- 长文本，最大 65535 字符
LONGTEXT           -- 超长文本，最大 4GB

-- 时间类型
DATE               -- 日期：2023-12-25
TIME               -- 时间：14:30:00
DATETIME           -- 日期时间：2023-12-25 14:30:00
TIMESTAMP          -- 时间戳，自动更新

3.2.2 字段约束

-- 非空约束
NOT NULL           -- 必填字段

-- 默认值
DEFAULT 0          -- 数字默认值
DEFAULT ''         -- 字符串默认值
DEFAULT CURRENT_TIMESTAMP -- 时间默认值

-- 唯一约束
UNIQUE KEY         -- 唯一性约束

-- 检查约束（MySQL 8.0+）
CHECK (status IN (0, 1, 2)) -- 状态值检查

3.3 表关系设计

3.3.1 一对一关系

-- 用户基本信息表
CREATE TABLE user_basic (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
);

-- 用户详细信息表
CREATE TABLE user_profile (
    user_id BIGINT UNSIGNED NOT NULL,
    real_name VARCHAR(50),
    phone VARCHAR(20),
    avatar_url VARCHAR(255),
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES user_basic(id) ON DELETE CASCADE
);

3.3.2 一对多关系

-- 用户表
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- 订单表
CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

3.3.3 多对多关系

-- 用户表
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- 角色表
CREATE TABLE roles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    role_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

-- 用户角色关联表
CREATE TABLE user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

4. 索引设计规范

4.1 索引设计原则

为经常查询的字段创建索引
为外键字段创建索引
为排序字段创建索引
避免为低区分度字段创建单列索引
单表索引数量不超过 5 个

4.2 主键索引

-- 推荐使用 BIGINT 自增主键
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)  -- 主键索引自动创建
);

4.3 唯一索引

-- 业务唯一字段
CREATE UNIQUE INDEX uk_user_email ON users(email);
CREATE UNIQUE INDEX uk_user_phone ON users(phone);
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);

4.4 普通索引

-- 查询字段索引
CREATE INDEX idx_user_status ON users(status);
CREATE INDEX idx_user_created_at ON users(created_at);
CREATE INDEX idx_order_user_id ON orders(user_id);

4.5 复合索引

-- 多字段查询索引（遵循最左匹配原则）
CREATE INDEX idx_user_status_created ON users(status, created_at);
CREATE INDEX idx_order_user_status ON orders(user_id, status);

4.6 索引优化建议

-- 1. 覆盖索引：包含查询所需的所有字段
CREATE INDEX idx_user_query ON users(status, created_at, username);

-- 2. 前缀索引：为长字符串字段创建前缀索引
CREATE INDEX idx_user_email_prefix ON users(email(10));

-- 3. 函数索引：为计算字段创建索引（MySQL 8.0+）
CREATE INDEX idx_user_email_lower ON users((LOWER(email)));

5. SQL 编写规范

5.1 基本规范

-- 1. 使用明确的字段列表，禁止使用 SELECT *
SELECT id, username, email, created_at FROM users WHERE status = 1;

-- 2. 使用参数化查询，防止 SQL 注入
-- 正确示例
SELECT * FROM users WHERE id = ? AND status = ?;

-- 3. 合理使用事务
START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100.00);
UPDATE users SET balance = balance - 100.00 WHERE id = 1;
COMMIT;

5.2 查询优化

-- 1. 使用 LIMIT 限制结果集
SELECT * FROM users WHERE status = 1 LIMIT 10;

-- 2. 使用合适的 WHERE 条件
-- 避免在 WHERE 子句中使用函数
-- 错误示例
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 正确示例
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- 3. 使用 EXISTS 替代 IN（大数据量时）
-- 推荐
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 4. 合理使用 JOIN
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

5.3 分页查询优化

-- 1. 使用游标分页（推荐）
SELECT * FROM users
WHERE id > 1000
ORDER BY id
LIMIT 20;

-- 2. 传统分页优化
SELECT * FROM users
WHERE status = 1
ORDER BY created_at DESC
LIMIT 10000, 20;  -- 深度分页性能较差

6. 性能优化规范

6.1 表结构优化

-- 1. 选择合适的存储引擎
-- InnoDB：支持事务、外键、行级锁（推荐）
-- MyISAM：查询性能好，但不支持事务

-- 2. 合理设置字符集
-- 推荐使用 utf8mb4
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

-- 3. 控制表字段数量
-- 单表字段数量建议不超过 50 个
-- 大字段（TEXT/BLOB）考虑独立建表

6.2 查询性能优化

-- 1. 使用 EXPLAIN 分析查询计划
EXPLAIN SELECT * FROM users WHERE status = 1 AND created_at > '2023-01-01';

-- 2. 避免全表扫描
-- 确保 WHERE 条件字段有索引

-- 3. 优化子查询
-- 使用 JOIN 替代子查询
SELECT u.* FROM users u
INNER JOIN (SELECT DISTINCT user_id FROM orders WHERE status = 1) o
ON u.id = o.user_id;

6.3 批量操作优化

-- 1. 批量插入
INSERT INTO users (username, email, status) VALUES
('user1', 'user1@example.com', 1),
('user2', 'user2@example.com', 1),
('user3', 'user3@example.com', 1);

-- 2. 批量更新
UPDATE users SET status = 0 WHERE id IN (1, 2, 3, 4, 5);

-- 3. 分批处理大数据量
-- 每批处理 1000 条记录

7. 安全规范

7.1 数据安全

-- 1. 敏感数据加密存储
-- 密码使用 BCrypt 或 Argon2 加密
-- 手机号、身份证号等敏感信息加密存储

-- 2. 数据脱敏
-- 开发环境使用脱敏数据
-- 日志中不输出敏感信息

-- 3. 访问控制
-- 按最小权限原则分配数据库权限
-- 生产环境禁止使用 root 账号

7.2 SQL 安全

-- 1. 防止 SQL 注入
-- 使用参数化查询
-- 输入验证和过滤

-- 2. 权限控制
-- 应用程序使用专用数据库账号
-- 限制数据库账号权限范围

8. 维护规范

8.1 变更管理

-- 1. DDL 变更流程
-- 开发环境 → 测试环境 → 预生产环境 → 生产环境
-- 必须提供回滚脚本

-- 2. 版本控制
-- 数据库变更脚本纳入版本控制
-- 使用 Flyway 或 Liquibase 管理数据库版本

-- 示例：添加字段
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP NULL COMMENT '最后登录时间';

-- 回滚脚本
ALTER TABLE users DROP COLUMN last_login_at;

8.2 监控和备份

-- 1. 性能监控
-- 监控慢查询日志
-- 监控数据库连接数
-- 监控磁盘空间使用

-- 2. 备份策略
-- 每日全量备份
-- 实时增量备份
-- 定期验证备份完整性

-- 3. 日志管理
-- 开启慢查询日志
-- 定期清理日志文件

9. 最佳实践

9.1 设计最佳实践

遵循三范式：减少数据冗余，保证数据一致性
适度反范式：在性能要求高的场景下，允许适度冗余
预留扩展字段：为未来需求预留扩展空间
统一时间格式：使用 UTC 时间存储，应用层转换时区

9.2 开发最佳实践

使用连接池：合理配置数据库连接池参数
事务控制：合理使用事务，避免长事务
异常处理：完善的异常处理和回滚机制
代码审查：SQL 代码必须经过审查

9.3 运维最佳实践

定期维护：定期分析表、优化索引
容量规划：根据业务增长规划数据库容量
高可用设计：主从复制、读写分离
灾难恢复：制定完整的灾难恢复方案

10. 常见问题与解决方案

10.1 性能问题

-- 问题：查询慢
-- 解决方案：
-- 1. 分析执行计划
EXPLAIN SELECT * FROM users WHERE status = 1;

-- 2. 添加合适索引
CREATE INDEX idx_user_status ON users(status);

-- 3. 优化查询语句
-- 避免 SELECT *，使用具体字段

10.2 数据一致性问题

-- 问题：数据不一致
-- 解决方案：
-- 1. 使用事务保证原子性
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 2. 使用乐观锁防止并发更新
UPDATE users SET username = 'new_name', version = version + 1
WHERE id = 1 AND version = 1;

10.3 存储空间问题

-- 问题：存储空间不足
-- 解决方案：
-- 1. 定期清理历史数据
DELETE FROM log_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 2. 数据归档
-- 将历史数据迁移到归档表或数据仓库

-- 3. 表分区
-- 按时间或范围对表进行分区

11. 总结

本规范涵盖了 MySQL 数据库设计的各个方面，从命名规范到性能优化，从安全控制到运维管理。遵循这些规范可以：

提高开发效率：统一的规范减少沟通成本
保证数据质量：规范的设计确保数据一致性
优化系统性能：合理的索引和查询优化
降低维护成本：清晰的文档和规范化的流程

建议团队定期回顾和更新本规范，确保其与业务发展和技术演进保持同步。