一文搞懂 SQL 建表、索引与约束：博客系统数据库设计实战

黄敬峰
6 阅读7分钟

本文以一个博客系统为例，手把手带你搞懂数据库表设计、索引优化、约束配置，以及三种表关系的设计思路。

前言

做后端开发，数据库设计是绕不开的一环。很多同学对建表一知半解，索引不知道什么时候建，约束也搞不清楚。

今天我们就用一个博客系统作为例子，把 SQL 建表这件事讲清楚。

博客系统有哪些业务？

  • 用户注册登录
  • 发布文章
  • 点赞、收藏
  • 评论（支持回复）
  • 标签分类
  • 文件上传

涉及 6 张核心表：用户表、头像表、文章表、点赞表、评论表、标签表、文件表。

下面逐个拆解。

一、用户表：一切的起点

设计思路

用户表是整个系统的核心，查询频率最高。所以字段要精简，只存登录必须的信息：

  • id：主键，自增
  • name：用户名，唯一
  • 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

索引说明

索引类型作用
PRIMARY KEY (id)主键索引按 id 查询最快，/user/:id
UNIQUE KEY (name)唯一索引用户名不能重复，搜索用户时加速

二、头像表：一对一关系

设计思路

头像图片本身存在静态服务器或阿里云 OSS 上，数据库只存元信息：

  • 文件名
  • 文件类型（mimetype）
  • 文件大小
  • 属于哪个用户

建表语句

CREATE TABLE `avatar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mimetype` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `avatar_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

索引说明

索引类型作用
PRIMARY KEY (id)主键索引按 id 查头像
KEY (userId)普通索引根据用户 id 查头像，高频查询

外键约束

FOREIGN KEY (`userId`) REFERENCES `user` (`id`)

意思是：userId 必须在 user 表中存在。不能给一个不存在的用户上传头像。

三、文章表：一对多关系

设计思路

一个用户可以写多篇文章，所以是一对多关系。文章表通过 userId 关联用户表。

content 字段用 longtext，因为文章内容可能很长。

建表语句

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` longtext COLLATE utf8mb4_unicode_ci,
  `userId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `post_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

索引说明

索引类型作用
PRIMARY KEY (id)主键索引按 id 查文章
KEY (userId)普通索引查某用户的所有文章

四、点赞表：多对多关系

设计思路

用户和文章是多对多关系：

  • 一个用户可以点赞多篇文章
  • 一篇文章可以被多个用户点赞

用联合主键 (userId, postId) 保证同一个用户不能重复点赞。

建表语句

CREATE TABLE `user_like_post` (
  `userId` int(11) NOT NULL,
  `postId` int(11) NOT NULL,
  PRIMARY KEY (`userId`, `postId`),
  KEY `postId` (`postId`),
  CONSTRAINT `user_like_post_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
  CONSTRAINT `user_like_post_ibfk_2` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

联合主键的好处

PRIMARY KEY (userId, postId)
  • 保证唯一性：同一个用户不能对同一篇文章点赞两次
  • 覆盖查询：查"张三点过哪些赞"直接用主键前缀，不需要单独建 userId 索引

级联操作

ON DELETE CASCADE ON UPDATE CASCADE
  • 删了文章 → 点赞记录自动删除
  • 文章 id 变了 → 点赞记录跟着变

五、评论表：自引用（树状结构）

设计思路

评论支持"回复评论"，就是树状结构。用 parentId 字段指向自己的 id：

  • parentId = null → 顶层评论
  • parentId = 1 → 回复评论 1
文章A
  ├─ 评论1（parentId = null）
  │    ├─ 评论2（parentId = 1）
  │    └─ 评论3（parentId = 1）
  └─ 评论4（parentId = null）

建表语句

CREATE TABLE `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` longtext COLLATE utf8mb4_unicode_ci,
  `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 `comment_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
  CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`parentId`) REFERENCES `comment` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `comment_ibfk_3` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

索引说明

索引类型作用
PRIMARY KEY (id)主键索引按 id 查评论
KEY (postId)普通索引查某篇文章的所有评论
KEY (userId)普通索引查某用户的所有评论
KEY (parentId)普通索引查某条评论的回复

级联操作

  • 删文章 → 评论跟着删（CASCADE）
  • 删评论 → 子评论的 parentId 变 null（SET NULL），子评论变成顶层评论

六、标签表：多对多（中间表）

设计思路

文章和标签是多对多关系：

  • 一篇文章可以有多个标签
  • 一个标签可以对应多篇文章

用中间表 post_tag 来关联。

建表语句

标签表：

CREATE TABLE `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

中间表：

CREATE TABLE `post_tag` (
  `postId` int(11) NOT NULL,
  `tagId` int(11) NOT NULL,
  PRIMARY KEY (`postId`, `tagId`),
  KEY `tagId` (`tagId`),
  CONSTRAINT `post_tag_ibfk_1` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `post_tag_ibfk_2` FOREIGN KEY (`tagId`) REFERENCES `tag` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

数据示例

post_tag 表：
postId=1  tagId=1   （文章A → "数据库"）
postId=1  tagId=2   （文章A → "MySQL"）
postId=2  tagId=1   （文章B → "数据库"）

七、文件表：复合场景

设计思路

文件可能属于某篇文章（文章配图），也可能是独立上传的（头像、临时文件），所以 postId 允许为空。

建表语句

CREATE TABLE `file` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `originalname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mimetype` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int(11) NOT NULL,
  `postId` int(11) DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `width` smallint(6) NOT NULL,
  `height` smallint(6) NOT NULL,
  `metadata` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `postId` (`postId`),
  KEY `userId` (`userId`),
  CONSTRAINT `file_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
  CONSTRAINT `file_ibfk_2` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;

知识点总结

三种索引

类型作用示例
Primary Key主键索引，唯一标识user.id
Unique Key唯一索引，防重复user.name、tag.name
Key普通索引，加速查询avatar.userId

三种约束

约束作用例子
NOT NULL非空，必须填用户名不能为空
FOREIGN KEY外键，值必须在关联表中存在文章的 userId 必须是真实用户
ON DELETE CASCADE级联删除删帖子 → 评论跟着删

三种表关系

关系设计方式例子
一对一外键关联用户 ↔ 头像
一对多外键关联用户 → 文章
多对多中间表 + 联合主键文章 ↔ 标签
自引用外键指向自己评论 → 评论的回复

索引该不该建？

场景需要建吗原因
WHERE id = 1不用主键自带索引
WHERE name = '张三'不用UNIQUE KEY 自带
WHERE userId = 1要建普通字段没索引
联合主键的前缀字段不用联合主键已覆盖

最后

数据库设计看似复杂，核心就三件事：

  1. 建表：合理拆分字段，核心表精简
  2. 索引：高频查询字段加索引，但别滥用
  3. 约束：用外键和级联操作保证数据一致性

掌握这三点，大部分业务场景都能应付了。

avatar
文章
阅读
粉丝