企业官网建设流程全解析

一、后端业务有几张表

一个典型的博客 / 内容社区后端，核心业务无非是：用户发文章、别人看文章、点赞收藏、评论互动。围绕这些业务，我们至少需要这几张表：

设计一张表，无非是回答三个问题：

1. 怎么建表：字段类型、长度、是否允许 NULL、默认值。
2. 怎么建索引：高频查询字段建索引，加速检索。
3. 怎么建约束：主键、唯一键、外键，保证数据不重复、不脏。

下面逐张表拆解，把「为什么这么设计」讲清楚。

二、用户表：小表大智慧

2.1 为什么用户表要「瘦」

用户必须登录，而登录、鉴权是最高频的操作——几乎每个请求都要校验用户身份。所以用户表的设计原则是：

只存核心字段，把不常用的、占空间的字段拆出去。

核心字段只有三个：id、username、password。

为什么「瘦」这么重要？

• 有利于分布式：表小，缓存友好，单表能承载更多数据，水平拆分（分库分表）也更简单。
• 有利于快速查询：一行数据短，一个数据页（InnoDB 默认 16KB）能装更多行，同样的查询扫描的页更少。
• 利于分表：字段少，分表键（通常是 userId）好选，拆分成本低。

像头像、个性签名（slogan）这类「不是每次都要」的数据，就单独建表，需要时再关联查询——这叫「垂直拆分」。

2.2 字段设计

• id：自增主键。自增意味着插入时顺序写入，磁盘顺序 IO，性能好。
• username：唯一键，不能重复，同时承担「按用户名搜索」的职责。
• password：绝不存明文。一般存加盐哈希后的结果（如 bcrypt），即使库被拖走，也拿不到原密码。

2.3 索引怎么建

先问自己：查询需求是什么？高频查询有哪些？索引是跟着查询走的，不是越多越好。

这个用户表有两个典型查询：

主键本身就是聚簇索引（InnoDB），而username需要唯一约束——唯一约束在 MySQL 里其实就是唯一索引，一个索引同时解决「查得快」和「不能重复」两件事。

2.4 建表语句

-- 用户表：只存核心字段，保持「瘦」CREATETABLEIFNOTEXISTS`user`(`id`INT(11)NOTNULLAUTO_INCREMENT,-- 自增主键`username`VARCHAR(255)NOTNULL,-- 用户名，唯一`password`VARCHAR(255)NOTNULL,-- 存 bcrypt 等加盐哈希，绝不明文PRIMARYKEY(`id`),-- 主键 = 聚簇索引UNIQUEKEY`uk_username`(`username`)-- 唯一索引：查得快 + 防重复)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

说明：utf8mb4是完整 UTF-8，能存 emoji；utf8mb4_unicode_ci是大小写不敏感的排序规则，用户名搜索时更符合直觉。

2.5 索引到底有哪几类？

这里单独展开讲一下索引分类，因为后面每张表都要用：

按「是否唯一」分：

按「存储结构」分（InnoDB）：

• 聚簇索引（Clustered Index）：表数据本身按主键顺序存储，主键就是聚簇索引，叶子节点存的是整行数据。
• 二级索引（Secondary Index）：非主键索引，叶子节点存的是主键值。查到主键后还要「回表」再去聚簇索引里拿整行。

核心原理：二级索引为什么要「回表」？因为它的叶子节点只存了主键，要拿完整记录得再走一遍聚簇索引。后面讲的「覆盖索引」就是通过「把查询字段都塞进索引」来避免回表，是常见优化手段。

按「字段个数」分：

• 单列索引：只对一列建索引。
• 联合索引（复合索引）：对多列建一个索引，遵循最左前缀原则。

按「实现方式」分（了解即可）：

• B+Tree 索引（默认，最常用）
• Hash 索引（仅等值查询，不支持范围）
• 全文索引 FULLTEXT（文章搜索）
• 空间索引（地理位置）

三、头像表：图片为什么不进数据库

3.1 思路：图片存文件，元信息存数据库

头像的本质是一张图片文件。图片不应该以二进制直接塞进 MySQL 的 BLOB 字段——又大又慢。正确做法：

图片文件放到静态资源服务器（OSS），数据库只存这张图片的「元信息」（MIME 类型、文件名、大小、归属用户），通过一个 URL 就能访问。

访问路径形如/public/avatar/:id。云厂商的 OSS（如阿里云 OSS）是独立静态资源服务器，上传后直接返回一个 CDN 地址，业务表里存这个地址即可。

3.2 建表语句

-- 头像表：只存图片元信息，不存图片本身CREATETABLEIFNOTEXISTS`avatar`(`id`INT(11)NOTNULLAUTO_INCREMENT,`mimetype`VARCHAR(255)NOTNULL,-- 图片类型，如 image/png`filename`VARCHAR(255)NOTNULL,-- 存到 OSS 上的文件名 / 路径`size`INT(11)NOTNULL,-- 文件大小（字节）`userId`INT(11)NOTNULL,-- 归属用户PRIMARYKEY(`id`),KEY`idx_userId`(`userId`),-- 普通索引：按用户查头像CONSTRAINT`avatar_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

要点：

• 外键userId关联user.id，保证头像一定属于存在的用户。
• idx_userId是普通索引，因为「查某用户头像」是高频操作（WHERE userId = ?）。

小提示：外键列 MySQL 会自动建索引（如果原本没有的话），这里显式写KEY idx_userId更清晰，也方便后续调整。

3.3 一次请求背后发生了什么？（DNS → CDN）

为什么头像要放独立静态服务器 / CDN？这得从一次访问说起。以掘金这类站点为例：

1. DNS 解析：浏览器访问juejin.cn，先从本地缓存 → 局域网 / 校园网 DNS → 运营商 DNS → 国家 / 根服务器，逐级递归查找，最终拿到 IP 地址。
2. 三次握手：拿到 IP 后 TCP 三次握手建立连接。这个 IP 往往不是真实业务服务器，而是nginx 反向代理服务器的地址。
3. 负载均衡：nginx 不做具体业务，只负责「负载均衡」——从一堆健康的服务器里挑一台，把请求代理过去。服务器集群里每台都有完整 Web 程序，都能对外服务。
4. 静态资源单独处理：图片、CSS、JS 这类静态资源有独立特征（不变、可缓存、体积大），由CDN（Content Delivery Network，内容分发网络）就近分发——用户在哪个城市，就从最近的 CDN 节点取资源，快且省带宽。

所以一条完整的链路是：

浏览器 → DNS 解析 → nginx 负载均衡 → 业务服务器集群（动态内容） └→ CDN 静态资源节点（头像 / 图片 / CSS / JS）

这也解释了为什么「数据库只存元信息 + 一个 URL」是正解：动态数据走数据库，静态资源走 CDN，各司其职。

四、文章表

4.1 字段设计

文章表的核心是标题和正文。正文可能很长，用LONGTEXT（最大 4GB）。作者是userId，外键关联用户表。

-- 文章表CREATETABLEIFNOTEXISTS`post`(`id`INT(11)NOTNULLAUTO_INCREMENT,`title`VARCHAR(255)NOTNULL,`content`LONGTEXT,-- 正文，可能很长，用 LONGTEXT`userId`INT(11)DEFAULTNULL,-- 作者，可为空（如草稿 / 匿名）PRIMARYKEY(`id`),KEY`idx_userId`(`userId`),CONSTRAINT`post_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

原大纲里longtext (255)是不对的：LONGTEXT不接受长度参数，长度由类型本身决定（TEXT64KB、MEDIUMTEXT16MB、LONGTEXT4GB）。

4.2 为什么给 userId 建普通索引

「查某作者的所有文章」（WHERE userId = ?）是常见需求，所以给userId建索引。而文章正文这种大字段（LONGTEXT）不适合建索引，索引会变得巨大且意义不大。

五、点赞表

5.1 多对多关系表

「用户点赞文章」是典型的多对多关系：一个用户点多个文章，一篇文章被多个用户点。多对多要拆成一张中间表（关联表）。

-- 点赞表：用户-文章 多对多中间表CREATETABLE`user_like_post`(`userId`INT(11)NOTNULL,`postId`INT(11)NOTNULL,PRIMARYKEY(`userId`,`postId`),-- 联合主键，保证同一人不能重复点赞同一文章KEY`idx_postId`(`postId`),-- 反向查询：这篇文章被谁点赞CONSTRAINT`user_like_post_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`),CONSTRAINT`user_like_post_ibfk_2`FOREIGNKEY(`postId`)REFERENCES`post`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

5.2 索引的经典优化点（重点）

这里有一个非常容易踩的坑：

联合主键(userId, postId)本身已经是一个联合索引了，就不要再单独给userId建索引，否则是浪费空间。

为什么？因为联合索引遵循最左前缀原则：(userId, postId)里userId在最左边，所以它已经能单独加速WHERE userId = ?的查询。

那为什么还要给postId单独建一个idx_postId呢？

因为postId在联合索引里不是最左列，无法单独用它走索引。当我们要查「这篇文章被哪些人点赞」（WHERE postId = ?）时，就需要postId的独立索引。

一句话总结：联合索引最左列不用重复建索引；非最左列若有独立查询需求，才需要单独建索引。

六、收藏表

收藏和点赞结构一模一样，也是「用户-文章」多对多中间表：

-- 收藏表：与点赞表结构对称CREATETABLE`user_favorite_post`(`userId`INT(11)NOTNULL,`postId`INT(11)NOTNULL,PRIMARYKEY(`userId`,`postId`),KEY`idx_postId`(`postId`),CONSTRAINT`user_favorite_post_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`),CONSTRAINT`user_favorite_post_ibfk_2`FOREIGNKEY(`postId`)REFERENCES`post`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

有人会问：点赞和收藏都是userId + postId，能不能合并成一张表加个type字段？可以，但业务上「点赞」和「收藏」是独立状态（可能赞了没收藏、收藏了没赞），拆开更清晰，也方便各自独立扩展（比如收藏可能加「收藏夹」维度）。是否合并要看业务复杂度，没有绝对对错。

七、评论表

7.1 支持楼中楼的评论

评论最大的特点是可能嵌套：回复别人的评论（楼中楼）。所以除了postId（评论属于哪篇文章）、userId（谁评论的），还有一个关键的parentId（回复的是哪条评论）。

-- 评论表：parentId 实现楼中楼CREATETABLE`comment`(`id`INT(11)NOTNULLAUTO_INCREMENT,`content`LONGTEXT,-- 评论内容`postId`INT(11)NOTNULL,-- 属于哪篇文章`userId`INT(11)NOTNULL,-- 谁评论的`parentId`INT(11)DEFAULTNULL,-- 回复哪条评论；NULL 表示顶层评论PRIMARYKEY(`id`),KEY`idx_postId`(`postId`),-- 查某篇文章下的所有评论KEY`idx_userId`(`userId`),-- 查某用户的评论KEY`idx_parentId`(`parentId`),-- 查某条评论的回复CONSTRAINT`comment_ibfk_1`FOREIGNKEY(`postId`)REFERENCES`post`(`id`),CONSTRAINT`comment_ibfk_2`FOREIGNKEY(`userId`)REFERENCES`user`(`id`),CONSTRAINT`comment_ibfk_3`FOREIGNKEY(`parentId`)REFERENCES`comment`(`id`)-- 自关联)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

7.2 自关联外键

注意最后一条外键parentId REFERENCES comment(id)，它引用了同一张表的主键——这叫自关联。评论可以回复评论，天然是树形结构。

三个索引分别对应三种高频查询：

八、标签表

8.1 多对多：tag 表 + post_tag 表

文章和标签也是多对多：一篇文章多个标签，一个标签多篇文章。需要两张表——标签本身一张，关联关系一张。

-- 标签表CREATETABLE`tag`(`id`INT(11)NOTNULLAUTO_INCREMENT,`name`VARCHAR(255)NOTNULL,PRIMARYKEY(`id`),UNIQUEKEY`uk_name`(`name`)-- 标签名唯一，避免重复标签)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;-- 文章-标签 关联表CREATETABLE`post_tag`(`postId`INT(11)NOTNULL,`tagId`INT(11)NOTNULL,PRIMARYKEY(`postId`,`tagId`),-- 联合主键：一篇文章下标签不重复KEY`idx_tagId`(`tagId`),-- 反向：查某标签下的所有文章CONSTRAINT`post_tag_ibfk_1`FOREIGNKEY(`tagId`)REFERENCES`tag`(`id`),CONSTRAINT`post_tag_ibfk_2`FOREIGNKEY(`postId`)REFERENCES`post`(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

原大纲只给了post_tag，但它的外键引用了tag，所以这里把tag表补上，保证代码能真正跑起来。同样的最左前缀道理：postId是联合主键最左列，不再重复建索引；tagId是非最左列且有反向查询，才单独建。

九、文件表

文章里可能插图、上传附件，所以还需要一张文件表，存上传文件的元信息。

-- 文件表：文章插图 / 附件元信息CREATETABLE`file`(`id`INT(11)NOTNULLAUTO_INCREMENT,`originalname`VARCHAR(255)NOTNULL,-- 用户上传时的原始文件名`mimetype`VARCHAR(255)NOTNULL,-- MIME 类型`filename`VARCHAR(255)NOTNULL,-- 存储后的文件名（通常是随机名，防覆盖）`size`INT(11)NOTNULL,-- 大小`postId`INT(11)DEFAULTNULL,-- 属于哪篇文章（可为空）`userId`INT(11)NOTNULL,-- 上传者`width`SMALLINT(6)NOTNULL,-- 图片宽`height`SMALLINT(6)NOTNULL,-- 图片高`metadata`JSONDEFAULTNULL,-- 额外元信息，JSON 灵活扩展PRIMARYKEY(`id`),KEY`idx_postId`(`postId`),KEY`idx_userId`(`userId`),CONSTRAINT`file_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`)ONDELETESETNULLONUPDATECASCADE,CONSTRAINT`file_ibfk_2`FOREIGNKEY(`postId`)REFERENCES`post`(`id`)ONDELETESETNULLONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ci;

两个值得注意的设计点：

1. metadata JSON：MySQL 5.7+ 支持 JSON 类型，可存非固定结构的元信息，比频繁加列更灵活。
2. ON DELETE SET NULL ON UPDATE CASCADE：这是外键的级联行为。
   • ON DELETE SET NULL：父记录（用户 / 文章）被删除时，子表外键列置 NULL，而不是删掉文件记录。
   • ON UPDATE CASCADE：父记录主键更新时，子表外键跟着更新。

注意一个矛盾点：userId字段是NOT NULL，却又用了ON DELETE SET NULL——删除用户时 MySQL 想把它置 NULL，但字段不允许 NULL，会报错。所以更合理的设计是让userId、postId也允许 NULL（DEFAULT NULL），级联规则才真正生效。这里正好说明：字段的可空性与外键级联规则要一致，否则运行时会踩坑。

十、项目准备：目录与假数据

最后是落地到工程。项目里建一个database文件夹：

database/ ├── blog.sql # 全部建表语句（按依赖顺序：user → post/tag → 关联表） └── seed.sql # 假数据（测试用）

几点建议：

1. 建表顺序：先建被引用的表（user、post、tag），再建带外键的关联表（avatar、comment、user_like_post、post_tag、file），否则外键约束会因找不到父表而报错。
2. 假数据：准备一些测试用户、几篇文章、点赞收藏评论记录，方便联调 API、验证索引是否生效（用EXPLAIN看执行计划）。
3. 脚本规范：blog.sql用CREATE TABLE IF NOT EXISTS，保证可重复执行不报错。

全文总结

这篇文章从一个博客后端的实际业务出发，走完了「拆表 → 定字段 → 建索引 → 加约束 → 落工程」的完整链路：

• 用户表：保持「瘦」，只存 id / username / password，利于分布式与分表；username 用唯一索引，password 存加盐哈希。
• 头像 / 文件表：图片不存数据库，文件放 OSS / CDN，库里只存元信息 + URL；动态数据与静态资源分离。
• 文章表：正文用 LONGTEXT；userId 建普通索引支持「按作者查文章」。
• 点赞 / 收藏表：多对多中间表，联合主键(userId, postId)防重复 + 加速最左查询，postId 单独建索引支持反向查询。
• 评论表：parentId 自关联实现楼中楼。
• 标签表：tag + post_tag 两张表表达多对多。

核心知识点复盘

常见问题 / 避坑指南

1. 索引不是越多越好：索引会拖慢写入（每次 INSERT / UPDATE 都要维护索引）并占空间。只为高频查询建，别为每个字段都建。
2. 联合索引别重复建最左列：(userId, postId)已覆盖userId，再单独建userId就是浪费。
3. 密码绝不存明文：用 bcrypt / argon2 等加盐哈希，防止拖库后密码泄露。
4. 长文本别建索引、别用长度参数：LONGTEXT 不接受(255)，也不适合建索引。
5. 字段可空性要和级联规则一致：NOT NULL字段配ON DELETE SET NULL会在运行时冲突。
6. 建表注意顺序：先父表后子表，外键才能成功创建；用IF NOT EXISTS保证可重复执行。
7. 图片 / 文件别进数据库：大文件塞 BLOB 会让库膨胀变慢，正确姿势是 OSS / CDN + 元信息。
8. 验证索引是否生效：用EXPLAIN SELECT ...看执行计划，确认key列真的用上了你建的索引。