文章目录一、后端业务有几张表二、用户表小表大智慧2.1 为什么用户表要「瘦」2.2 字段设计2.3 索引怎么建2.4 建表语句2.5 索引到底有哪几类三、头像表图片为什么不进数据库3.1 思路图片存文件元信息存数据库3.2 建表语句3.3 一次请求背后发生了什么DNS → CDN四、文章表4.1 字段设计4.2 为什么给 userId 建普通索引五、点赞表5.1 多对多关系表5.2 索引的经典优化点重点六、收藏表七、评论表7.1 支持楼中楼的评论7.2 自关联外键八、标签表8.1 多对多tag 表 post_tag 表九、文件表十、项目准备目录与假数据全文总结核心知识点复盘常见问题 / 避坑指南一、后端业务有几张表一个典型的博客 / 内容社区后端核心业务无非是用户发文章、别人看文章、点赞收藏、评论互动。围绕这些业务我们至少需要这几张表表名作用核心字段user用户id / username / passwordavatar头像图片元信息 userIdpost文章title / content / userIduser_like_post点赞userId postIduser_favorite_post收藏userId postIdcomment评论content / postId / userId / parentIdtag / post_tag标签标签与文章多对多file文件上传文件的元信息设计一张表无非是回答三个问题怎么建表字段类型、长度、是否允许 NULL、默认值。怎么建索引高频查询字段建索引加速检索。怎么建约束主键、唯一键、外键保证数据不重复、不脏。下面逐张表拆解把「为什么这么设计」讲清楚。二、用户表小表大智慧2.1 为什么用户表要「瘦」用户必须登录而登录、鉴权是最高频的操作——几乎每个请求都要校验用户身份。所以用户表的设计原则是只存核心字段把不常用的、占空间的字段拆出去。核心字段只有三个id、username、password。为什么「瘦」这么重要有利于分布式表小缓存友好单表能承载更多数据水平拆分分库分表也更简单。有利于快速查询一行数据短一个数据页InnoDB 默认 16KB能装更多行同样的查询扫描的页更少。利于分表字段少分表键通常是 userId好选拆分成本低。像头像、个性签名slogan这类「不是每次都要」的数据就单独建表需要时再关联查询——这叫「垂直拆分」。2.2 字段设计id自增主键。自增意味着插入时顺序写入磁盘顺序 IO性能好。username唯一键不能重复同时承担「按用户名搜索」的职责。password绝不存明文。一般存加盐哈希后的结果如 bcrypt即使库被拖走也拿不到原密码。2.3 索引怎么建先问自己查询需求是什么高频查询有哪些索引是跟着查询走的不是越多越好。这个用户表有两个典型查询查询场景路由走的索引按 id 查某个用户GET /user/:id主键索引id登录 / 搜索时按用户名查WHERE username ?唯一索引username主键本身就是聚簇索引InnoDB而username需要唯一约束——唯一约束在 MySQL 里其实就是唯一索引一个索引同时解决「查得快」和「不能重复」两件事。2.4 建表语句-- 用户表只存核心字段保持「瘦」CREATETABLEIFNOTEXISTSuser(idINT(11)NOTNULLAUTO_INCREMENT,-- 自增主键usernameVARCHAR(255)NOTNULL,-- 用户名唯一passwordVARCHAR(255)NOTNULL,-- 存 bcrypt 等加盐哈希绝不明文PRIMARYKEY(id),-- 主键 聚簇索引UNIQUEKEYuk_username(username)-- 唯一索引查得快 防重复)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;说明utf8mb4是完整 UTF-8能存 emojiutf8mb4_unicode_ci是大小写不敏感的排序规则用户名搜索时更符合直觉。2.5 索引到底有哪几类这里单独展开讲一下索引分类因为后面每张表都要用按「是否唯一」分类型说明示例主键索引 PRIMARY KEY唯一且非空一张表一个id唯一索引 UNIQUE值不能重复允许 NULLusername普通索引 KEY / INDEX只加速查询允许重复postId按「存储结构」分InnoDB聚簇索引Clustered Index表数据本身按主键顺序存储主键就是聚簇索引叶子节点存的是整行数据。二级索引Secondary Index非主键索引叶子节点存的是主键值。查到主键后还要「回表」再去聚簇索引里拿整行。核心原理二级索引为什么要「回表」因为它的叶子节点只存了主键要拿完整记录得再走一遍聚簇索引。后面讲的「覆盖索引」就是通过「把查询字段都塞进索引」来避免回表是常见优化手段。按「字段个数」分单列索引只对一列建索引。联合索引复合索引对多列建一个索引遵循最左前缀原则。按「实现方式」分了解即可BTree 索引默认最常用Hash 索引仅等值查询不支持范围全文索引 FULLTEXT文章搜索空间索引地理位置三、头像表图片为什么不进数据库3.1 思路图片存文件元信息存数据库头像的本质是一张图片文件。图片不应该以二进制直接塞进 MySQL 的 BLOB 字段——又大又慢。正确做法图片文件放到静态资源服务器OSS数据库只存这张图片的「元信息」MIME 类型、文件名、大小、归属用户通过一个 URL 就能访问。访问路径形如/public/avatar/:id。云厂商的 OSS如阿里云 OSS是独立静态资源服务器上传后直接返回一个 CDN 地址业务表里存这个地址即可。3.2 建表语句-- 头像表只存图片元信息不存图片本身CREATETABLEIFNOTEXISTSavatar(idINT(11)NOTNULLAUTO_INCREMENT,mimetypeVARCHAR(255)NOTNULL,-- 图片类型如 image/pngfilenameVARCHAR(255)NOTNULL,-- 存到 OSS 上的文件名 / 路径sizeINT(11)NOTNULL,-- 文件大小字节userIdINT(11)NOTNULL,-- 归属用户PRIMARYKEY(id),KEYidx_userId(userId),-- 普通索引按用户查头像CONSTRAINTavatar_ibfk_1FOREIGNKEY(userId)REFERENCESuser(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;要点外键userId关联user.id保证头像一定属于存在的用户。idx_userId是普通索引因为「查某用户头像」是高频操作WHERE userId ?。小提示外键列 MySQL 会自动建索引如果原本没有的话这里显式写KEY idx_userId更清晰也方便后续调整。3.3 一次请求背后发生了什么DNS → CDN为什么头像要放独立静态服务器 / CDN这得从一次访问说起。以掘金这类站点为例DNS 解析浏览器访问juejin.cn先从本地缓存 → 局域网 / 校园网 DNS → 运营商 DNS → 国家 / 根服务器逐级递归查找最终拿到 IP 地址。三次握手拿到 IP 后 TCP 三次握手建立连接。这个 IP 往往不是真实业务服务器而是nginx 反向代理服务器的地址。负载均衡nginx 不做具体业务只负责「负载均衡」——从一堆健康的服务器里挑一台把请求代理过去。服务器集群里每台都有完整 Web 程序都能对外服务。静态资源单独处理图片、CSS、JS 这类静态资源有独立特征不变、可缓存、体积大由CDNContent Delivery Network内容分发网络就近分发——用户在哪个城市就从最近的 CDN 节点取资源快且省带宽。所以一条完整的链路是浏览器 → DNS 解析 → nginx 负载均衡 → 业务服务器集群动态内容 └→ CDN 静态资源节点头像 / 图片 / CSS / JS这也解释了为什么「数据库只存元信息 一个 URL」是正解动态数据走数据库静态资源走 CDN各司其职。四、文章表4.1 字段设计文章表的核心是标题和正文。正文可能很长用LONGTEXT最大 4GB。作者是userId外键关联用户表。-- 文章表CREATETABLEIFNOTEXISTSpost(idINT(11)NOTNULLAUTO_INCREMENT,titleVARCHAR(255)NOTNULL,contentLONGTEXT,-- 正文可能很长用 LONGTEXTuserIdINT(11)DEFAULTNULL,-- 作者可为空如草稿 / 匿名PRIMARYKEY(id),KEYidx_userId(userId),CONSTRAINTpost_ibfk_1FOREIGNKEY(userId)REFERENCESuser(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;原大纲里longtext (255)是不对的LONGTEXT不接受长度参数长度由类型本身决定TEXT64KB、MEDIUMTEXT16MB、LONGTEXT4GB。4.2 为什么给 userId 建普通索引「查某作者的所有文章」WHERE userId ?是常见需求所以给userId建索引。而文章正文这种大字段LONGTEXT不适合建索引索引会变得巨大且意义不大。五、点赞表5.1 多对多关系表「用户点赞文章」是典型的多对多关系一个用户点多个文章一篇文章被多个用户点。多对多要拆成一张中间表关联表。-- 点赞表用户-文章 多对多中间表CREATETABLEuser_like_post(userIdINT(11)NOTNULL,postIdINT(11)NOTNULL,PRIMARYKEY(userId,postId),-- 联合主键保证同一人不能重复点赞同一文章KEYidx_postId(postId),-- 反向查询这篇文章被谁点赞CONSTRAINTuser_like_post_ibfk_1FOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTuser_like_post_ibfk_2FOREIGNKEY(postId)REFERENCESpost(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;5.2 索引的经典优化点重点这里有一个非常容易踩的坑联合主键(userId, postId)本身已经是一个联合索引了就不要再单独给userId建索引否则是浪费空间。为什么因为联合索引遵循最左前缀原则(userId, postId)里userId在最左边所以它已经能单独加速WHERE userId ?的查询。那为什么还要给postId单独建一个idx_postId呢因为postId在联合索引里不是最左列无法单独用它走索引。当我们要查「这篇文章被哪些人点赞」WHERE postId ?时就需要postId的独立索引。一句话总结联合索引最左列不用重复建索引非最左列若有独立查询需求才需要单独建索引。六、收藏表收藏和点赞结构一模一样也是「用户-文章」多对多中间表-- 收藏表与点赞表结构对称CREATETABLEuser_favorite_post(userIdINT(11)NOTNULL,postIdINT(11)NOTNULL,PRIMARYKEY(userId,postId),KEYidx_postId(postId),CONSTRAINTuser_favorite_post_ibfk_1FOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTuser_favorite_post_ibfk_2FOREIGNKEY(postId)REFERENCESpost(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;有人会问点赞和收藏都是userId postId能不能合并成一张表加个type字段可以但业务上「点赞」和「收藏」是独立状态可能赞了没收藏、收藏了没赞拆开更清晰也方便各自独立扩展比如收藏可能加「收藏夹」维度。是否合并要看业务复杂度没有绝对对错。七、评论表7.1 支持楼中楼的评论评论最大的特点是可能嵌套回复别人的评论楼中楼。所以除了postId评论属于哪篇文章、userId谁评论的还有一个关键的parentId回复的是哪条评论。-- 评论表parentId 实现楼中楼CREATETABLEcomment(idINT(11)NOTNULLAUTO_INCREMENT,contentLONGTEXT,-- 评论内容postIdINT(11)NOTNULL,-- 属于哪篇文章userIdINT(11)NOTNULL,-- 谁评论的parentIdINT(11)DEFAULTNULL,-- 回复哪条评论NULL 表示顶层评论PRIMARYKEY(id),KEYidx_postId(postId),-- 查某篇文章下的所有评论KEYidx_userId(userId),-- 查某用户的评论KEYidx_parentId(parentId),-- 查某条评论的回复CONSTRAINTcomment_ibfk_1FOREIGNKEY(postId)REFERENCESpost(id),CONSTRAINTcomment_ibfk_2FOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTcomment_ibfk_3FOREIGNKEY(parentId)REFERENCEScomment(id)-- 自关联)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;7.2 自关联外键注意最后一条外键parentId REFERENCES comment(id)它引用了同一张表的主键——这叫自关联。评论可以回复评论天然是树形结构。三个索引分别对应三种高频查询索引对应查询idx_postId打开文章加载该文所有评论idx_userId查某个用户发过的评论idx_parentId展开某条评论的回复列表八、标签表8.1 多对多tag 表 post_tag 表文章和标签也是多对多一篇文章多个标签一个标签多篇文章。需要两张表——标签本身一张关联关系一张。-- 标签表CREATETABLEtag(idINT(11)NOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,PRIMARYKEY(id),UNIQUEKEYuk_name(name)-- 标签名唯一避免重复标签)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 文章-标签 关联表CREATETABLEpost_tag(postIdINT(11)NOTNULL,tagIdINT(11)NOTNULL,PRIMARYKEY(postId,tagId),-- 联合主键一篇文章下标签不重复KEYidx_tagId(tagId),-- 反向查某标签下的所有文章CONSTRAINTpost_tag_ibfk_1FOREIGNKEY(tagId)REFERENCEStag(id),CONSTRAINTpost_tag_ibfk_2FOREIGNKEY(postId)REFERENCESpost(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;原大纲只给了post_tag但它的外键引用了tag所以这里把tag表补上保证代码能真正跑起来。同样的最左前缀道理postId是联合主键最左列不再重复建索引tagId是非最左列且有反向查询才单独建。九、文件表文章里可能插图、上传附件所以还需要一张文件表存上传文件的元信息。-- 文件表文章插图 / 附件元信息CREATETABLEfile(idINT(11)NOTNULLAUTO_INCREMENT,originalnameVARCHAR(255)NOTNULL,-- 用户上传时的原始文件名mimetypeVARCHAR(255)NOTNULL,-- MIME 类型filenameVARCHAR(255)NOTNULL,-- 存储后的文件名通常是随机名防覆盖sizeINT(11)NOTNULL,-- 大小postIdINT(11)DEFAULTNULL,-- 属于哪篇文章可为空userIdINT(11)NOTNULL,-- 上传者widthSMALLINT(6)NOTNULL,-- 图片宽heightSMALLINT(6)NOTNULL,-- 图片高metadataJSONDEFAULTNULL,-- 额外元信息JSON 灵活扩展PRIMARYKEY(id),KEYidx_postId(postId),KEYidx_userId(userId),CONSTRAINTfile_ibfk_1FOREIGNKEY(userId)REFERENCESuser(id)ONDELETESETNULLONUPDATECASCADE,CONSTRAINTfile_ibfk_2FOREIGNKEY(postId)REFERENCESpost(id)ONDELETESETNULLONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;两个值得注意的设计点metadata JSONMySQL 5.7 支持 JSON 类型可存非固定结构的元信息比频繁加列更灵活。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也允许 NULLDEFAULT NULL级联规则才真正生效。这里正好说明字段的可空性与外键级联规则要一致否则运行时会踩坑。十、项目准备目录与假数据最后是落地到工程。项目里建一个database文件夹database/ ├── blog.sql # 全部建表语句按依赖顺序user → post/tag → 关联表 └── seed.sql # 假数据测试用几点建议建表顺序先建被引用的表user、post、tag再建带外键的关联表avatar、comment、user_like_post、post_tag、file否则外键约束会因找不到父表而报错。假数据准备一些测试用户、几篇文章、点赞收藏评论记录方便联调 API、验证索引是否生效用EXPLAIN看执行计划。脚本规范blog.sql用CREATE TABLE IF NOT EXISTS保证可重复执行不报错。全文总结这篇文章从一个博客后端的实际业务出发走完了「拆表 → 定字段 → 建索引 → 加约束 → 落工程」的完整链路用户表保持「瘦」只存 id / username / password利于分布式与分表username 用唯一索引password 存加盐哈希。头像 / 文件表图片不存数据库文件放 OSS / CDN库里只存元信息 URL动态数据与静态资源分离。文章表正文用 LONGTEXTuserId 建普通索引支持「按作者查文章」。点赞 / 收藏表多对多中间表联合主键(userId, postId)防重复 加速最左查询postId 单独建索引支持反向查询。评论表parentId 自关联实现楼中楼。标签表tag post_tag 两张表表达多对多。核心知识点复盘知识点一句话总结垂直拆分大而全的表拆成「核心字段 扩展表」关联查询按需取聚簇索引 vs 二级索引主键是聚簇索引存整行二级索引存主键值非覆盖时需回表最左前缀原则联合索引(A, B)能加速 A 前缀查询不能单独加速 B唯一约束 唯一索引MySQL 里唯一约束通过唯一索引实现一石二鸟多对多关系拆成中间表联合主键表达「不重复」自关联外键parentId 引用本表主键实现树形 / 层级结构外键级联ON DELETE / ON UPDATE 控制父记录变更时子表行为需与字段可空性一致常见问题 / 避坑指南索引不是越多越好索引会拖慢写入每次 INSERT / UPDATE 都要维护索引并占空间。只为高频查询建别为每个字段都建。联合索引别重复建最左列(userId, postId)已覆盖userId再单独建userId就是浪费。密码绝不存明文用 bcrypt / argon2 等加盐哈希防止拖库后密码泄露。长文本别建索引、别用长度参数LONGTEXT 不接受(255)也不适合建索引。字段可空性要和级联规则一致NOT NULL字段配ON DELETE SET NULL会在运行时冲突。建表注意顺序先父表后子表外键才能成功创建用IF NOT EXISTS保证可重复执行。图片 / 文件别进数据库大文件塞 BLOB 会让库膨胀变慢正确姿势是 OSS / CDN 元信息。验证索引是否生效用EXPLAIN SELECT ...看执行计划确认key列真的用上了你建的索引。