作为一名资深后端开发，你有没有遇到过这样的场景：接手一个新项目，看到前任同事设计的数据库表结构，简直让人怀疑人生？表名莫名其妙，字段命名混乱，关系不清不楚，查询效率低下..

这让我深刻意识到了：哪怕是一些"高级开发"，也并不知道怎么去设计一个好的表结构。

于是决定和大家一起探讨如何更好地设计表结构。所有观点都是结合多年的经验得来，不一定正确，如有错误之处欢迎大家指正。

一、表名设计：第一眼就要知道是干什么的

1.1 有意义的前缀 + 清晰的表名

前缀在大型系统中是有必要的，可以区分不同业务模块，但关键是前缀要有明确含义，表名要语义清晰。

❌ 不好的命名：

DC_COURSE_TESTPAPER      -- TESTPAPER是问卷还是试卷？
TB_USER_INFO            -- TB前缀无意义，INFO太泛泛
T_ORDER_DTL             -- DTL是detail的缩写？
DATA_TBL_001            -- 完全看不懂

✅ 好的命名：

DC_COURSE_QUESTIONNAIRES    -- DC表示Distance Course远程课程系统
SYS_USER_PROFILES          -- SYS表示系统核心模块
ORDER_ITEMS                -- 订单商品明细
LMS_STUDENT_SCORES         -- LMS表示Learning Management System

什么时候需要前缀？

• 多个业务系统共用数据库：USER_, ORDER_, PRODUCT_
• 区分不同数据类型：LOG_, CONFIG_, TEMP_
• 大型项目的模块划分：CRM_, ERP_, CMS_

1.2 用完整的英文单词而不是拼音

❌ 不好的命名：

kecheng_wenjuan         -- 拼音
user_xinxi             -- 中英混合
订单_items             -- 中英混合

✅ 好的命名：

course_questionnaires  -- 纯英文，语义清晰
user_profiles         -- 纯英文
order_items          -- 纯英文

原因：

• 英文是编程的通用语言，团队成员更容易理解
• 避免编码问题

1.3 表名要体现业务含义，不要只是技术实现

❌ 不好的命名：

data_table_001
temp_storage
middle_table
relation_mapping

✅ 好的命名：

student_scores        -- 学生成绩
file_uploads         -- 文件上传记录
course_enrollments   -- 课程报名
user_preferences     -- 用户偏好设置

除非是临时用的表，不参与任何业务逻辑，只是用来做数据处理或者测试。

二、字段命名：见名知意

2.1 布尔字段用 is_ 开头

❌ 不好的命名：

active    -- 是激活还是活跃？
delete    -- 删除状态还是删除动作？
flag      -- 什么标志？

✅ 好的命名：

is_active     -- 是否激活
is_deleted    -- 是否已删除
is_verified   -- 是否已验证

用 is_ 开头的好处：

1. 一眼就能看出是布尔值 - 看到 is_active 就知道这个字段要么是 true 要么是 false
2. 避免歧义 - 像 active 这样的名字，你搞不清楚它表示的是状态还是动作
3. 代码可读性更好 - 写代码的时候，if (user.is_active) 比 if (user.active) 更容易理解

2.2 时间字段统一后缀

❌ 不好的命名：

create_time
update_date
delete_at
register_datetime

✅ 好的命名：

created_at    -- 创建时间
updated_at    -- 更新时间
deleted_at    -- 删除时间
registered_at -- 注册时间

好处：

1. 一眼就能看出是时间字段
2. 避免命名混乱
3. _at 在英语里表示"在某个时间点"，比 _time 更准确
4. 特别推荐用 deleted_at 做逻辑删除字段，能看出来删除时间，支持数据恢复

2.3 外键字段统一 _id 后缀

❌ 不好的命名：

user          -- 这是用户ID还是用户对象？
course        -- 课程ID？
teacher_key   -- 什么key？

✅ 好的命名：

user_id       -- 用户ID
course_id     -- 课程ID
teacher_id    -- 教师ID

2.4 额外的经验

除了基本的命名规范，还有一些实用的经验：

1. 逻辑删除用时间字段 - 用 deleted_at 比 is_deleted 好，能看出来删除时间，支持数据恢复和历史追踪
2. 状态字段用枚举 - 不要用数字 1、2、3 表示状态，用 status 字段，值用 'pending'、'approved'、'rejected' 这样的英文单词
3. 金额字段用 decimal - 不要用 float 或 double，用 decimal(10,2) 这样的类型，避免浮点数精度问题
4. 密码字段要加密 - 密码字段名用 password_hash 或 encrypted_password，不要直接叫 password
5. 软删除要加索引 - 如果经常查询未删除的数据，给 deleted_at 字段加索引，提高查询性能

三、表结构设计：关系清晰、适度冗余

3.1 一对多关系：外键放在多的一边

让我们用用户和订单的业务关系来举例：

用户表 (users)：

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

订单表 (orders)：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,  -- 外键放在多的一边
    order_no VARCHAR(32),
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

这样设计的好处：

• 通过 user_id 就知道订单属于哪个用户
• JOIN 一下就能拿到用户的所有订单
• 新增订单字段不影响用户表

3.2 多对多关系：中间表命名要体现关系

多对多关系的中间表命名要根据具体情况来选择：

情况1：有业务含义的关系表比如学生和课程的关系，不只是简单关联，还有报名时间、状态等业务信息：

❌ 不好的设计：

student_course_rel    -- rel是什么关系？
sc_mapping           -- 缩写看不懂
middle_table         -- 完全不知道什么意思

✅ 好的设计： 课程报名表 (course_enrollments)：

CREATE TABLE course_enrollments (
    id BIGINT PRIMARY KEY,
    student_id BIGINT,
    course_id BIGINT,
    enrolled_at TIMESTAMP,
    status VARCHAR(20),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

情况2：纯粹的关联关系表如果只是单纯的多对多映射，没有额外的业务属性，用mapping也是可以的：

用户角色关联表 (user_role_mappings)：

CREATE TABLE user_role_mappings (
    user_id BIGINT,
    role_id BIGINT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

如何选择命名？

• 有业务含义的关系：用具体的业务名词，如enrollments、orders、friendships
• 纯粹的映射关系：可以用mappings、relations或直接用实体1_实体2s
• 关键是保持团队内命名风格的统一

3.3 适当的字段冗余：提升查询效率

有时候为了避免复杂的JOIN查询，适当冗余是非常有必要的。

最典型的就是冗余上级ID：

订单详情表 (order_items)：

CREATE TABLE order_items (
    idBIGINT PRIMARY KEY,
    order_id BIGINT,
    user_id BIGINT,      -- 冗余字段
    product_id BIGINT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGNKEY (order_id) REFERENCES orders(id),
    FOREIGNKEY (user_id) REFERENCESusers(id),
    FOREIGNKEY (product_id) REFERENCES products(id)
);

为什么要冗余 user_id？

• 查询用户的所有购买记录时，直接查 order_items 表就行
• 不需要先通过 orders 表再关联到 order_items
• 一个查询代替了两表JOIN

四、索引设计：让查询飞起来

4.1 索引基本原则

1. 区分度最大的字段放在前面 - 在复合索引中，把选择性高的字段放在前面
2. 避免冗余和重复索引 - (a,b)和(a)这样的索引就是冗余的
3. 控制索引数量 - 每个表的索引数量建议不超过5个
4. 不要索引大型字段 - 有很多字符的字段建议考虑前缀索引

4.2 常见索引设计场景

场景1：经常用于查询条件的字段

-- 用户经常按邮箱查询
CREATE INDEX idx_users_email ON users(email);

-- 订单经常按用户和状态查询
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

场景2：经常用于排序的字段

-- 按创建时间排序
CREATE INDEX idx_orders_created_at ON orders(created_at);

场景3：经常用于JOIN的字段

-- 外键字段通常需要索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

4.3 索引优化实战

避免索引失效的情况：

-- ❌ 这样查询会导致索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- ✅ 应该这样写
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

复合索引的最左前缀原则：

-- 创建复合索引
CREATEINDEX idx_orders_user_status_created ON orders(user_id, status, created_at);

-- ✅ 这些查询能用到索引
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1ANDstatus = 'paid';
SELECT * FROM orders WHERE user_id = 1ANDstatus = 'paid'AND created_at > '2023-01-01';

-- ❌ 这个查询用不到索引
SELECT * FROM orders WHEREstatus = 'paid';

五、规范化与反规范化：平衡的艺术

5.1 规范化设计

规范化是为了减少数据冗余，提高数据一致性和完整性。

**第一范式(1NF)**：字段不可再分

-- ❌ 不符合1NF
CREATETABLE orders (
    idBIGINT,
    product_names VARCHAR(255)  -- 存储"商品A,商品B,商品C"
);

-- ✅ 符合1NF
CREATETABLE orders (
    idBIGINT
);

CREATETABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    quantity INT
);

**第二范式(2NF)**：消除部分依赖

-- ❌ 不符合2NF
CREATETABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    product_name VARCHAR(100),  -- 依赖于product_id，不依赖于order_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ✅ 符合2NF
CREATETABLE orders (
    idBIGINT PRIMARY KEY,
    user_id BIGINT
);

CREATETABLE products (
    idBIGINT PRIMARY KEY,
    product_name VARCHAR(100)
);

CREATETABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGNKEY (order_id) REFERENCES orders(id),
    FOREIGNKEY (product_id) REFERENCES products(id)
);

5.2 反规范化设计

反规范化是为了优化数据的读取性能，适用于读取操作频繁或数据量极大的系统。

增加冗余列：

-- 在订单详情中冗余商品名称，避免JOIN查询
CREATE TABLE order_items (
    id BIGINT PRIMARY KEY,
    order_id BIGINT,
    product_id BIGINT,
    product_name VARCHAR(100),  -- 冗余字段
    quantity INT,
    price DECIMAL(10,2)
);

预计算字段：

-- 在订单表中预计算总金额，避免每次查询都SUM
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    total_amount DECIMAL(10,2),  -- 预计算字段
    status VARCHAR(20),
    created_at TIMESTAMP
);

六、性能优化实战技巧

6.1 查询优化

使用EXISTS替代IN：

-- ❌ 性能较差
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

-- ✅ 性能更好
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');

*避免SELECT ：

-- ❌ 不推荐
SELECT * FROM users WHERE email = 'user@example.com';

-- ✅ 推荐
SELECT id, username, email FROM users WHERE email = 'user@example.com';

6.2 分页查询优化

传统分页的问题：

-- ❌ 当OFFSET很大时性能很差
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 100000;

优化后的分页：

-- ✅ 使用游标分页
SELECT * FROM orders WHERE id < 100000 ORDER BY id DESC LIMIT 10;

6.3 批量操作优化

批量插入：

-- ❌ 逐条插入
INSERTINTOusers (username, email) VALUES ('user1', 'user1@example.com');
INSERTINTOusers (username, email) VALUES ('user2', 'user2@example.com');
INSERTINTOusers (username, email) VALUES ('user3', 'user3@example.com');

-- ✅ 批量插入
INSERTINTOusers (username, email) VALUES
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com'),
    ('user3', 'user3@example.com');

七、总结

数据库表设计是一门艺术，需要在规范化与性能、可读性与效率之间找到平衡点。一个好的表结构设计应该具备以下特点：

1. 语义清晰：表名和字段名一看就知道是干什么的
2. 关系明确：表之间的关系一目了然
3. 性能良好：合理的索引设计，避免全表扫描
4. 易于维护：结构清晰，便于后续扩展和修改

记住，没有完美的设计，只有最适合当前业务场景的设计。在实际开发中，要根据具体的业务需求、数据量大小、查询模式等因素来综合考虑。

希望今天的分享能帮助你在下次设计数据库表结构时，不再让人"人麻了"！

在实际项目中，建议团队制定统一的数据库设计规范，并通过代码审查来确保规范的执行。只有这样，才能保证整个项目的数据库设计质量。

阅读原文：原文链接