在开发后端业务时数据库设计往往是地基。地基不稳上层应用写得再花哨也白搭。本文将带你走一遍博客系统核心表的设计过程涵盖用户、文章、点赞、收藏、评论、标签、文件等模块重点聊聊怎么建表、怎么建索引、怎么建约束以及背后的性能考量。同时我们还会深入探讨头像等静态资源的存储与访问链路看看一个 URL 背后隐藏的网络架构。每张表都会明确其对应的业务场景让你不仅知道怎么建还知道为什么建。一、用户表小而美才是王道业务场景用户注册、登录、身份认证。系统需要存储用户的核心凭证用户名和密码用于每次请求的身份校验。用户表是所有业务的基础几乎所有其他表都通过userId与它关联。用户表是系统的核心几乎所有业务都要和它关联。但你会发现很多成熟系统的用户表字段并不多。为什么用户表要尽量“瘦”。原因很简单用户数据高频访问表越小单页能缓存的数据越多查询越快。分布式场景下用户表可能被频繁同步、分片字段少更容易维护。有些非核心信息头像、个人简介可以拆到扩展表按需加载。所以用户表通常只保留最核心的字段id、username、password。建表语句如下CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;索引设计id作为自增主键物理存储顺序与插入顺序一致对 InnoDB 友好。name用户名必须唯一所以加唯一索引既能保证业务唯一性又能加速按用户名登录的查询。密码不能存明文这个属于应用层逻辑但设计表时要注意字段长度够用一般存加密后的哈希。一句话总结用户表保持精简高频字段建索引唯一约束交给数据库。二、头像表静态资源与数据库的协作业务场景用户上传和更换头像。头像图片文件本身存放在静态资源服务器或对象存储中数据库只保存文件的元数据类型、文件名、大小以及该头像属于哪个用户。这样用户信息页可以快速加载头像 URL而不需要把图片二进制塞进数据库。用户头像这类图片通常不会直接以二进制形式存在数据库里。最佳实践是将图片上传到静态资源服务器比如阿里云 OSS、自建 Nginx 静态目录。数据库中只保存元信息文件类型、文件名、大小、用户 ID 等。这样做的好处减轻数据库存储压力图片不占数据库空间。静态资源可以走 CDN 加速用户就近访问。文件与业务数据解耦便于扩展比如更换存储方案。头像表的建表语句CREATE TABLE avatar ( id int(11) NOT NULL AUTO_INCREMENT, mimetype varchar(255) NOT NULL, filename varchar(255) NOT NULL, size int(11) NOT NULL, userId int(11) NOT NULL, PRIMARY KEY (id), KEY userId (userId), CONSTRAINT avatar_userId FOREIGN KEY (userId) REFERENCES user (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;索引与外键userId建立普通索引因为经常要根据用户 ID 查询头像。外键约束保证数据一致性用户删除时头像记录如何处理这里没有指定级联删除实际项目中可以根据业务决定。三、静态资源与网络架构深度解析前面说到头像不存数据库而是放在静态资源服务器上。那么当你在浏览器里输入一个头像 URL比如https://p26-passport.byteacctimg.com/img/user-avatar/e086fe227e6e33b2c831e0c8197939e8~130x130.awebp这背后发生了什么为什么这个 URL 看起来跟juejin.cn域名完全不一样要回答这个问题我们需要从DNS 解析开始一路走到CDN 节点。3.1 DNS 解析递归寻找“门牌号”当你访问juejin.cn时浏览器需要先知道这个域名对应的 IP 地址。这个过程叫做 DNS 解析是一个逐级递归查找的过程本地缓存浏览器先看自己有没有缓存过该域名的 IP有就直接用没有则继续。操作系统缓存如果浏览器没缓存操作系统会查自己的 hosts 文件和 DNS 缓存。局域网 DNS 服务器比如校园网、公司内网的 DNS 服务器它可能已经缓存了结果直接返回否则向上级查询。网络服务商 DNS 服务器比如电信、联通、移动的 DNS它们维护着大量域名记录就像一本巨大的账本如果命中则返回。国家/顶级 DNS 服务器如果还没找到会继续向上直到根服务器。根服务器知道.com顶级域名的权威 DNS 在哪里。权威 DNS 服务器最终找到管理juejin.cn的 DNS 服务器它返回该域名对应的 IP 地址。整个过程虽然描述起来很长但实际耗时通常只有几十毫秒到几百毫秒而且各级缓存大大加速了重复访问。3.2 负载均衡与反向代理Nginx 的“交通警察”角色DNS 返回的 IP 地址往往不是某台具体应用服务器的 IP而是Nginx 负载均衡服务器的 IP。为什么因为一个大型网站比如掘金不可能只用一台服务器。它有服务器集群——很多台独立 IP 的服务器每台都部署了相同的 Web 应用都能处理请求。但用户访问时不可能随机挑一台需要一个“调度员”来分配流量这个调度员就是 Nginx。Nginx 在这里扮演两个角色反向代理接收客户端请求然后转发给内部集群中的某台服务器再把响应返回给客户端。客户端并不知道真正处理请求的是哪台服务器。负载均衡根据预设策略轮询、最少连接、IP hash 等从集群中选出一台“健康”的服务器将请求代理过去。所以你的请求到达 Nginx 后Nginx 会判断如果是动态请求比如登录、发文章就转发给应用服务器比如 Nest.js 写的后端服务。如果是静态资源请求图片、CSS、JS 文件Nginx 可能直接处理如果配置了静态目录或者转发给专门的静态资源服务器/CDN。3.3 静态资源服务器与 CDN就近取货的“快递站”对于图片、CSS、JS 这类不常变化、可共享的资源如果每次都由应用服务器返回会浪费大量计算资源和带宽。更好的做法是将这些资源放在独立的静态资源服务器上通常配置简单、性能高、专注文件传输。更进一步使用CDNContent Delivery Network内容分发网络。CDN 服务商在全国乃至全球部署了大量缓存节点用户访问时会被引导到离他最近的节点获取资源速度飞快。回到开头的头像 URLp26-passport.byteacctimg.com是字节跳动旗下的 CDN 域名~130x130.awebp表示图片经过了压缩和尺寸裁剪。这个 URL 看起来跟juejin.cn不同是因为静态资源和主站域名分离了——主站域名解析到 Nginx 负载均衡器而静态资源域名解析到 CDN 的智能调度系统根据用户地理位置返回最优节点 IP。3.4 一次完整的访问流程假设你在浏览器访问掘金首页输入juejin.cnDNS 解析返回最近的 Nginx 负载均衡服务器 IP。浏览器与 Nginx 建立 TCP 连接三次握手发送 HTTP 请求。Nginx 识别请求类型首页 HTML 是动态请求转发给某台 Nest.js 应用服务器。页面里的 CSS、JS、头像图片等URL 指向 CDN 域名浏览器会单独发起请求。对于 CDN 域名的请求DNS 解析返回距离你最近的 CDN 节点 IP。浏览器与 CDN 节点建立连接获取静态资源。如果节点上没有缓存CDN 会回源到源站可能是对象存储或静态服务器拉取然后缓存并返回给你。整个流程中应用服务器只处理业务逻辑静态资源交给 CDN 分担大大提升了整体性能和用户体验。记住数据库存元数据文件存对象存储/静态服务器访问走 CDN应用服务器专注业务。四、文章表内容为王业务场景博客的核心功能——文章的发布、展示、编辑和删除。用户登录后可以撰写文章文章需要有标题、正文和作者信息。文章列表页、详情页都依赖这张表提供数据。文章表是博客的核心通常包含标题、正文、作者 ID。注意正文可能很长使用LONGTEXT类型而标题用VARCHAR。CREATE TABLE post ( id int(11) NOT NULL AUTO_INCREMENT, title varchar(255) NOT NULL, content longtext, userId int(11) NOT NULL, PRIMARY KEY (id), KEY userId (userId), CONSTRAINT post_userId FOREIGN KEY (userId) REFERENCES user (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;userId加索引因为“查询某个用户的文章列表”是高频操作。外键约束保证文章一定属于某个存在的用户。五、点赞表联合主键与最左前缀的经典案例业务场景用户对文章进行点赞/取消点赞。点赞功能是社交互动的基础需要记录“哪个用户点赞了哪篇文章”同时要保证一个用户对同一篇文章只能点赞一次。点赞数通常会显示在文章卡片上需要快速统计。点赞关系是典型的多对多一个用户可以点赞多篇文章一篇文章可以被多个用户点赞。常见设计是用中间表保存关系CREATE TABLE user_like_post ( userId int(11) NOT NULL, postId int(11) NOT NULL, PRIMARY KEY (userId, postId), KEY postId (postId), CONSTRAINT fk_like_user FOREIGN KEY (userId) REFERENCES user (id), CONSTRAINT fk_like_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;为什么联合主键是(userId, postId)业务上一个用户对一篇文章的点赞是唯一的联合主键正好保证不重复。查询“某用户点赞了哪些文章”时WHERE userId ?可以直接走联合主键索引效率高。那为什么还要单独给postId建索引因为查询“某篇文章被哪些用户点赞”也很常见WHERE postId ?。如果只有联合主键(userId, postId)由于最左前缀原则这个查询无法使用该索引因为第一列userId未提供会导致全表扫描。所以额外建一个postId索引两个方向的查询都高效。记住联合索引遵循最左前缀设计时要考虑所有高频查询条件。六、收藏表同点赞表一样的设计思路业务场景用户收藏喜欢的文章便于日后查阅。收藏功能与点赞类似但语义不同——点赞是表达态度收藏是为了保存。收藏表同样需要记录用户和文章的多对多关系并且要防止重复收藏。收藏关系和点赞关系几乎一致可以创建类似的表CREATE TABLE user_favorite_post ( userId int(11) NOT NULL, postId int(11) NOT NULL, PRIMARY KEY (userId, postId), KEY postId (postId), CONSTRAINT fk_fav_user FOREIGN KEY (userId) REFERENCES user (id), CONSTRAINT fk_fav_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;不用重复造轮子和点赞表几乎一样。七、评论表支持树状结构的自关联业务场景用户对文章发表评论也可以回复其他用户的评论形成楼中楼。评论是博客互动的重要部分需要支持一级评论和二级回复展示时通常按照时间或树状结构排序。评论除了属于文章和用户外还可能是“评论的评论”回复。常见做法是增加一个parentId字段指向父评论的id如果是一级评论则parentId为NULL。CREATE TABLE comment ( id int(11) NOT NULL AUTO_INCREMENT, content longtext, postId int(11) NOT NULL, userId int(11) NOT NULL, parentId int(11) DEFAULT NULL, PRIMARY KEY (id), KEY postId (postId), KEY userId (userId), KEY parentId (parentId), CONSTRAINT fk_comment_user FOREIGN KEY (userId) REFERENCES user (id), CONSTRAINT fk_comment_parent FOREIGN KEY (parentId) REFERENCES comment (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT fk_comment_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;设计要点postId和userId都要建索引按文章查评论、按用户查评论都是高频操作。parentId也建索引如果需要查询某个评论的所有回复或者构建评论树会用到。外键策略删除文章时级联删除评论ON DELETE CASCADE删除父评论时子评论的parentId设为NULLON DELETE SET NULL这样评论不会凭空消失而是变成一级评论。八、标签表多对多关系的标准解法业务场景文章可以打上多个标签如“前端”、“后端”、“数据库”用户可以通过标签筛选文章。标签是内容分类的一种灵活方式一篇文章可以有多个标签一个标签下也可以有多篇文章。文章可以有多个标签标签也能对应多篇文章典型多对多。需要一张标签表tag一张中间表post_tag。CREATE TABLE tag ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE post_tag ( postId int(11) NOT NULL, tagId int(11) NOT NULL, PRIMARY KEY (postId, tagId), KEY tagId (tagId), CONSTRAINT fk_pt_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_pt_tag FOREIGN KEY (tagId) REFERENCES tag (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;联合主键(postId, tagId)保证一篇文章不重复打同一个标签。额外给tagId建索引方便“查询某个标签下的所有文章”。外键都设置级联删除文章或标签删除时自动清理中间表。九、文件表统一管理上传的附件业务场景博客文章中可能包含图片、附件等文件用户也可能上传其他类型的文件比如封面图。文件表统一存储这些文件的元数据并提供与文章、用户的关联方便管理和检索。除了头像文章里可能还有图片、附件等。可以设计一个通用的文件表保存文件的元数据甚至可以存一些 JSON 格式的扩展信息比如图片宽高。CREATE TABLE file ( id int(11) NOT NULL AUTO_INCREMENT, originalname varchar(255) NOT NULL, mimetype varchar(255) NOT NULL, filename varchar(255) NOT NULL, size int(11) NOT NULL, postId int(11) DEFAULT NULL, userId int(11) NOT NULL, width smallint(6) DEFAULT NULL, height smallint(6) DEFAULT NULL, metadata json DEFAULT NULL, PRIMARY KEY (id), KEY postId (postId), KEY userId (userId), CONSTRAINT fk_file_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_file_user FOREIGN KEY (userId) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;postId可以为空表示该文件可能不属于某篇文章比如用户头像。metadata使用 JSON 类型灵活存储额外信息比如图片 EXIF、视频时长等。索引userId和postId分别支持“查用户上传的文件”和“查文章包含的附件”。十、全局思考索引到底怎么建回顾以上表设计索引的创建并不是拍脑袋而是遵循几个原则高频查询字段必须建索引。比如userId、postId几乎每张关联表都有。唯一性字段建唯一索引。比如用户名、标签名。联合索引要注意最左前缀。联合主键(userId, postId)可以覆盖userId查询但覆盖不了postId查询所以额外建单列索引。不要过度索引。每个索引都会占用磁盘空间并拖慢写操作插入、更新、删除都需要维护索引。外键列通常要建索引否则外键约束检查可能全表扫描。数据库设计没有银弹一切从业务查询出发。十一、结语数据库设计是后端开发的基石一个合理的表结构能让你的应用性能更好、扩展更容易、代码更清晰。本文通过一个博客系统的核心表设计展示了如何从业务需求出发设计表、索引和约束。同时我们也深入理解了静态资源从上传到访问的整个链路明白了为什么头像 URL 长那样以及 DNS、Nginx、CDN 在其中的作用。记住表结构反映业务模型索引服务查询性能约束保障数据完整性而网络架构决定了数据的流向和访问速度。下次再设计数据库或搭建系统时不妨先问自己几个问题这张表会被怎么查询哪些字段需要唯一数据之间是什么关系哪些数据适合放数据库哪些适合放静态资源/CDN答案清楚了建表和部署自然水到渠成。