数据库方案
在上一章,我分析了各种技术方案。现在,让我从最简单的开始。
先用数据库实现,看看会发生什么。
表结构
我需要两张表:
数据设计要点
- 核心是在
articles里保存业务事实,而不是把规则散落在应用逻辑里。- 索引服务于高频查询,重点是缩小扫描范围,而不是堆更多字段。
- 关键字段包括
id、title、content、author_id、like_count、created_at、updated_at、article_id,它们决定后续查询和管理能力。
设计思路:
article_likes 表:
- 记录谁给哪篇文章点过赞
- UNIQUE KEY 保证一个用户对一篇文章只能点赞一次
- 用于查询"用户是否点赞"
articles.like_count 字段:
- 冗余字段,存储点赞总数
- 避免每次都 COUNT 查询
- 用于快速显示点赞数
为什么要有冗余字段?
没有冗余字段的方案:
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
问题:
- COUNT 查询慢(需要扫描索引)
- 列表页显示 20 篇文章,需要 20 次 COUNT
- 数据库压力大
有冗余字段的方案:
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
好处:
- 查询快,直接读取字段
- 不需要 COUNT
- 数据库压力小
代价:
- 需要维护冗余字段的一致性
- 点赞/取消点赞时需要更新
我选择了空间换时间。
点赞接口
点赞接口要做两件事:记录点赞关系 + 把总数加一。
这两件事必须放在同一个事务里,否则会出现”有记录没数字”或”有数字没记录”的错位。
def like_article(article_id, user_id):
"""点赞:写入点赞记录 + 原子自增计数"""
conn = db.begin()
try:
# 1. 写入点赞记录,(article_id, user_id) 唯一索引负责去重
conn.execute(
"INSERT INTO article_likes (article_id, user_id, created_at) "
"VALUES (%s, %s, NOW())",
(article_id, user_id),
)
# 2. 计数在数据库端自增,不读原值
conn.execute(
"UPDATE articles SET like_count = like_count + 1 WHERE id = %s",
(article_id,),
)
conn.commit()
except DuplicateKeyError:
# 已经赞过:回滚后按幂等返回,计数不受影响
conn.rollback()
return {"liked": True, "like_count": get_like_count(article_id), "changed": False}
return {"liked": True, "like_count": get_like_count(article_id), "changed": True}
三个必须注意的点:
1. 计数一定要让数据库自己算。
错误写法(会有丢失更新):
SELECT like_count FROM articles WHERE id = 1 # → 100
UPDATE articles SET like_count = 101 WHERE id = 1 # 两个并发请求都写 101
正确写法:
UPDATE articles SET like_count = like_count + 1 WHERE id = 1
单条 UPDATE 是原子的,两个并发请求分别把 100 变成 101、101 变成 102,结果一定是 102。完整的时间线分析见《并发问题》一章。
2. 幂等靠唯一索引,不靠代码里先查。
代码里”先 SELECT 判断是否已赞,再 INSERT”在并发下会失效:两个请求可能都查到”未赞”。真正兜底的是 (article_id, user_id) 联合唯一索引 + 捕获 DuplicateKeyError。如果这个索引缺失,重复点赞会让计数多加一次。
3. 失败时计数必须一起回滚。
INSERT 失败后如果事务没有回滚,like_count 就被多加了一次。
接口响应:
POST /api/articles/123/like
{"user_id": 1001}
首次点赞:
{"article_id": 123, "liked": true, "like_count": 101, "changed": true}
重复点赞(幂等,计数不变):
{"article_id": 123, "liked": true, "like_count": 101, "changed": false}
changed 告诉前端”状态是否真的变了”,重复点赞不该再播一次点赞动画。
取消点赞接口
取消点赞是点赞的逆操作,但多了一个细节:计数不能减到负数。
def unlike_article(article_id, user_id):
"""取消点赞:删除点赞记录 + 原子自减计数"""
conn = db.begin()
try:
affected = conn.execute(
"DELETE FROM article_likes WHERE article_id = %s AND user_id = %s",
(article_id, user_id),
).rowcount
if affected == 0:
conn.rollback()
# 本来就没赞过,幂等返回
return {"liked": False, "like_count": get_like_count(article_id), "changed": False}
# 只有真的删掉了记录才减计数,保证计数和记录一一对应
conn.execute(
"UPDATE articles SET like_count = like_count - 1 "
"WHERE id = %s AND like_count > 0",
(article_id,),
)
conn.commit()
except Exception:
conn.rollback()
raise
return {"liked": False, "like_count": get_like_count(article_id), "changed": True}
为什么用 DELETE 的 rowcount 而不是先查?
DELETE ... WHERE article_id = ? AND user_id = ? 返回的影响行数直接告诉你”这次是否真的删掉了一条”:
rowcount = 0 → 本来就没赞过 → 不减计数 → 天然幂等
rowcount = 1 → 赞过 → 减一次计数
如果先 SELECT 再 DELETE,中间的并发窗口会让计数和记录对不上。
AND like_count > 0 是最后一道防线。
即使历史数据已经被改坏,也不会把计数减成负数。代价是计数可能停在 0 而实际还剩 1 个赞——这种情况需要对账修复,而不是让数字显示成 -1。
并发点赞和取消点赞:
用户 A:点赞 用户 B:取消点赞(针对同一条记录)
│ │
▼ ▼
INSERT 点赞记录 DELETE 点赞记录
UPDATE count + 1 UPDATE count - 1
两个操作在事务里各自原子,但整体顺序取决于谁先提交。行锁会让它们串行化,最终 like_count 和记录数仍然一致。这一点《乐观锁》一章会展开。
查询文章详情
文章详情页要返回三样东西:文章内容、点赞总数、当前用户是否点赞。
def get_article_detail(article_id, user_id):
"""查询文章详情 + 点赞数 + 当前用户点赞状态"""
article = db.query(
"SELECT id, title, content, author_id, like_count, created_at "
"FROM articles WHERE id = %s",
(article_id,),
)
if not article:
return None
liked = db.query(
"SELECT 1 FROM article_likes "
"WHERE article_id = %s AND user_id = %s LIMIT 1",
(article_id, user_id),
)
return {
"article": article[0],
"like_count": article[0]["like_count"],
"liked": bool(liked),
}
为什么 like_count 直接读 articles 字段,而不是 COUNT(*)?
COUNT 查询:
SELECT COUNT(*) FROM article_likes WHERE article_id = 1
- 时间复杂度 O(n),点赞越多越慢
- 列表页 20 篇文章 = 20 次 COUNT
冗余字段:
SELECT like_count FROM articles WHERE id = 1
- O(1),直接读
- 代价:需要在写路径上维护一致性
这就是本章开头”空间换时间”的取舍。
列表页要避免 N+1 查询。
错误:逐篇查询点赞状态
for article in articles: # 1 次
liked = db.query("SELECT 1 FROM article_likes WHERE ...") # 20 次
# 合计 21 次查询
正确:一次批量查询
ids = [a["id"] for a in articles]
rows = db.query(
"SELECT article_id FROM article_likes "
"WHERE user_id = %s AND article_id IN (%s)" % (user_id, ",".join(map(str, ids)))
)
liked_ids = {r["article_id"] for r in rows}
# 合计 2 次查询
响应结构:
GET /api/articles/123?user_id=1001
{
"article": {
"id": 123,
"title": "创业者的一天",
"author_id": 42,
"like_count": 101,
"created_at": "2023-07-15 18:00:00"
},
"liked": true,
"like_count": 101
}
场景:并发点赞
如果有两个用户同时对一篇文章点赞,会发生什么?
初始状态:article.id = 1, like_count = 100
用户 A 点赞:
1. INSERT INTO article_likes (article_id, user_id) VALUES (1, 1001)
2. UPDATE articles SET like_count = like_count + 1 WHERE id = 1
用户 B 点赞(同时):
1. INSERT INTO article_likes (article_id, user_id) VALUES (1, 1002)
2. UPDATE articles SET like_count = like_count + 1 WHERE id = 1
期望结果:like_count = 102
实际结果:like_count = 102 ✅
看起来没问题?
但如果使用事务呢?
使用事务
事务的好处:
- 保证两个操作要么都成功,要么都失败
- 避免数据不一致
事务的代价:
- 锁定数据库资源
- 降低并发性能
单元测试
单元测试要覆盖三类场景:正常路径、幂等路径、并发路径。
import unittest
import threading
class LikeArticleTestCase(unittest.TestCase):
"""点赞接口的单元测试(TestDatabase 为测试库封装,每次用例独立)"""
def setUp(self):
self.db = TestDatabase()
self.db.execute(
"INSERT INTO articles (id, title, like_count) VALUES (1, '测试文章', 100)"
)
def test_first_like_increments_count(self):
"""首次点赞:计数 +1,状态为已赞"""
result = like_article(1, 1001)
self.assertTrue(result["liked"])
self.assertTrue(result["changed"])
self.assertEqual(result["like_count"], 101)
self.assertEqual(self.db.count_likes(1), 1) # 记录数也要是 1
def test_duplicate_like_is_idempotent(self):
"""重复点赞:计数不再增加,但返回已赞状态"""
like_article(1, 1001)
result = like_article(1, 1001)
self.assertTrue(result["liked"])
self.assertFalse(result["changed"]) # 状态没变
self.assertEqual(result["like_count"], 101) # 计数没变
self.assertEqual(self.db.count_likes(1), 1) # 记录也只有 1 条
def test_different_users_both_counted(self):
"""不同用户点赞:各自加一次"""
like_article(1, 1001)
result = like_article(1, 1002)
self.assertEqual(result["like_count"], 102)
self.assertEqual(self.db.count_likes(1), 2)
def test_unlike_removes_record_and_decrements(self):
"""取消点赞:记录删除,计数 -1"""
like_article(1, 1001)
result = unlike_article(1, 1001)
self.assertFalse(result["liked"])
self.assertTrue(result["changed"])
self.assertEqual(result["like_count"], 100)
self.assertEqual(self.db.count_likes(1), 0)
def test_unlike_when_not_liked_is_idempotent(self):
"""没赞过就取消:计数不能被减成负数"""
result = unlike_article(1, 1001)
self.assertFalse(result["liked"])
self.assertFalse(result["changed"])
self.assertEqual(result["like_count"], 100) # 仍然是 100
self.assertGreaterEqual(result["like_count"], 0)
def test_repeated_unlike_never_goes_negative(self):
"""连续取消也不会把计数压到负数"""
like_article(1, 1001)
unlike_article(1, 1001)
unlike_article(1, 1001)
self.assertEqual(self.db.get_count(1), 100)
def test_concurrent_likes_do_not_lose_update(self):
"""并发点赞:10 个请求,结果必须是 110,不是 101"""
def worker(user_id):
like_article(1, user_id)
threads = [
threading.Thread(target=worker, args=(user_id,))
for user_id in range(2000, 2010)
]
for t in threads:
t.start()
for t in threads:
t.join()
self.assertEqual(self.db.get_count(1), 110)
self.assertEqual(self.db.count_likes(1), 10)
if __name__ == "__main__":
unittest.main()
这三个用例最值得注意:
| 用例 | 断言的行为 | 失败说明 |
|---|---|---|
test_duplicate_like_is_idempotent | 重复点赞计数不变 | 幂等性被破坏,计数会虚高 |
test_unlike_when_not_liked_is_idempotent | 取消不存在的赞不减少计数 | 计数可能被减成负数 |
test_concurrent_likes_do_not_lose_update | 10 个并发点赞得到 110 | 存在丢失更新,要回看事务写法 |
为什么并发用例放在单元测试里?
因为它验证的是代码写法的正确性(用的是 like_count + 1 还是读改写),而不是系统的吞吐能力。真正压测并发量应该放到《并发问题》一章的压测脚本里。这里用 10 个线程就足够暴露”读改写”这种错误写法。
运行:
python -m unittest test_like.py -v
性能测试
我用 Apache Benchmark 测试了一下:
验证要点
- 命令只用于验证系统状态,读者不需要记具体参数。
看起来还不错?
但是,这只是单机测试。如果用户量增长呢?
上线一周后,数据如下:
文章数:1,500 篇
用户数:8,000 人
点赞总数:35,000 次
日均点赞:约 5,000 次
系统运行正常,没有出现明显问题。
我看了看数据库监控:
MySQL 状态:
- CPU:15-20%
- 连接数:30/200
- QPS:约 500
- 慢查询:0 条
article_likes 表:
- 记录数:35,000 条
- 索引大小:约 5MB
- 查询时间:< 1ms
一切看起来都很美好。
虽然目前运行正常,但我开始担心一些问题:
问题 1:数据一致性
解决方案:使用事务。
问题 2:并发更新
这是经典的”丢失更新”问题。
问题 3:性能瓶颈
当前:日均 5,000 次点赞,数据库 QPS 500
增长预测:
- 用户增长 10 倍 → 日均 50,000 次点赞
- 用户增长 100 倍 → 日均 500,000 次点赞
- 峰值可能达到 10 倍
数据库能承受吗?
我需要更深入地思考。
练习 1
为什么需要在 article_likes 表上创建 (article_id, user_id) 的联合唯一索引?
解题思路:
这道题问的是一个索引同时承担了几重职责,要从『约束』和『访问路径』两条线拆开看:它既是数据正确性的护栏,也是高频查询的入口。
详细步骤:
原因 1:保证数据唯一性
联合唯一索引确保一个用户对一篇文章只能有一条点赞记录:
数据设计要点
- 关键字段包括
INSERT,它们决定后续查询和管理能力。
原因 2:提升查询性能
查询”用户是否点赞”时,可以利用索引快速定位:
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
原因 3:避免回表查询
联合索引包含了查询所需的所有字段,不需要回表:
联合索引结构:(article_id, user_id) → 主键 ID
查询:SELECT id FROM article_likes WHERE article_id = 1 AND user_id = 100
执行:直接在索引中查找,不需要回表最终答案:
联合唯一索引同时做到三件事:用唯一约束保证『一个用户对一篇文章只能有一条点赞记录』;为『用户是否点赞』提供 O(log N) 的定位路径;并让 (article_id, user_id) → 主键 成为覆盖索引,查询时直接在索引里找到结果、不需要回表。
练习 2
如何解决并发更新导致的”丢失更新”问题?
解题思路:
这道题问的是丢失更新的四种解法,关键在于分清每种解法『是否需要先读旧值』——这决定了它是阻塞式的还是无锁的,也决定了在点赞这种低冲突场景下谁更划算。
详细步骤:
方案一:使用乐观锁
数据设计要点
- 这是一次表结构演进:随着业务能力增加,把新状态、新时间点或新归属关系补进数据模型。
方案二:使用数据库原子操作
数据设计要点
- 这里关注数据模型和约束关系,不需要记住具体语法。
这种方式不需要读取原值,直接在数据库层面完成计算。
方案三:使用 SELECT FOR UPDATE
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
方案四:使用 Redis 计数器
推荐方案:
对于点赞计数,推荐使用 方案二(原子操作) 或 方案四(Redis):
- 方案二简单高效,利用数据库原子性
- 方案四性能最好,适合高并发场景
最终答案:
四种解法分别是:乐观锁(读旧值 + 版本号校验)、数据库原子操作(like_count = like_count + 1,完全不读旧值)、SELECT FOR UPDATE 悲观锁(读时锁行,冲突时阻塞)、Redis 计数器(内存操作、性能最好、最终一致)。点赞计数推荐数据库原子更新(简单高效)或 Redis 计数器(高并发场景),悲观锁只在一致性要求极高时才用。
练习 3
设计一个接口,返回文章列表及每篇文章的点赞数和当前用户是否点赞。
要求:优化查询性能,避免 N+1 查询问题。
解题思路:
这道题的关键词是 N+1——先算清查询次数从 1+2N 能降到多少,再比较批量查询、LEFT JOIN、冗余字段三条路的查询次数与实现复杂度,最后落到具体推荐。
详细步骤:
问题分析:
N+1 查询问题:
优化方案一:批量查询
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
优化方案二:LEFT JOIN
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
优化方案三:使用冗余字段 + 批量查询是否点赞
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
性能对比:
| 方案 | 查询次数 | 复杂度 |
|---|---|---|
| N+1 查询 | 1 + 2N | 简单 |
| 批量查询 | 3 | 中等 |
| LEFT JOIN | 1 | 复杂 |
| 冗余字段 + 批量 | 2 | 简单 |
推荐方案:使用 冗余字段 + 批量查询是否点赞,简单高效。
最终答案:
N+1 的本质是『先查一批,再对每一条查一次』。改法有三种:用 WHERE article_id IN (...) 批量查询(3 次查询)、用 LEFT JOIN 一次取回(1 次查询)、或用冗余字段 + 批量查询『是否点赞』(2 次查询)。点赞列表页推荐『冗余字段 + 批量查询是否点赞』,查询次数从 1+2N 降到 2 次,同时保持实现简单。
练习 4
如何处理”点赞失败但用户已经点击”的情况?
解题思路:
这道题问的是前端乐观更新与后端真实状态不一致时如何收尾,核心是『UI 在什么时候被后端结果纠正』,因此按纠正时机分成悲观更新、乐观更新 + 回滚、乐观更新 + 定期同步三种。
详细步骤:
问题场景:
- 用户点击点赞按钮
- 前端立即显示点赞成功(乐观更新)
- 后端返回失败(已点赞、网络错误等)
- 前端状态与后端不一致
解决方案:
方案一:悲观更新(先请求,后更新 UI)
优点:数据准确 缺点:用户体验稍差(等待响应)
方案二:乐观更新 + 回滚
优点:用户体验好 缺点:实现复杂,需要处理回滚
方案三:乐观更新 + 定期同步
优点:用户体验最好 缺点:可能有短暂的不一致
推荐方案:
根据业务场景选择:
- 严格要求一致:使用方案一(悲观更新)
- 普通点赞功能:使用方案二(乐观更新 + 回滚)
- 社交应用:使用方案三(乐观更新 + 定期同步)
最终答案:
三种收尾方式:悲观更新(先请求、后更新 UI,数据最准但有等待)、乐观更新 + 回滚(体验好,失败时把 UI 回滚)、乐观更新 + 定期同步(体验最好,允许短暂不一致)。严格要求一致的场景用方案一;普通点赞功能用方案二;社交类应用可用方案三。无论哪种,失败响应都必须能触发 UI 纠正,否则界面会一直停留在假状态。
练习 5
设计数据库表结构,支持”查看用户点赞过的所有文章”功能。
解题思路:
这道题问的是『按用户维度反查文章列表』的表结构与索引设计,要先把查询模式写出来(WHERE user_id = ? ORDER BY created_at DESC),再倒推联合索引的列顺序,最后用覆盖索引、延迟关联、缓存三条手段做优化。
详细步骤:
需求分析:
- 查询用户点赞过的所有文章
- 支持分页
- 按点赞时间倒序排列
表结构设计:
数据设计要点
- 核心是在
article_likes里保存业务事实,而不是把规则散落在应用逻辑里。- 索引服务于高频查询,重点是缩小扫描范围,而不是堆更多字段。
- 关键字段包括
id、article_id、user_id、created_at,它们决定后续查询和管理能力。
查询 SQL:
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
索引分析:
索引 idx_user_created (user_id, created_at DESC)
查询:
WHERE user_id = ?
ORDER BY created_at DESC
执行计划:
- 使用索引 idx_user_created
- 索引扫描
- 避免文件排序(Using filesort)优化建议:
- 覆盖索引优化(如果只需要文章 ID):
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
- 延迟关联(分页优化):
数据设计要点
- 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
- 缓存优化:
最终答案:
在 article_likes 上建复合索引 idx_user_created (user_id, created_at DESC),让『某用户点赞过的所有文章、按点赞时间倒序』走索引扫描并避免 filesort。优化三步:只需要文章 ID 时用覆盖索引不回表;分页时用延迟关联(先从索引取 id 再回表关联文章表);高频查询的结果再加一层缓存。
-
如果用户在一篇文章上反复点赞、取消点赞,会对数据库产生什么影响?如何优化?
-
如何实现”点赞动画”效果?前后端如何配合?
-
如果需要统计”用户获得的点赞总数”(即用户所有文章的点赞数之和),如何设计?
💡 提示:这些问题没有标准答案,建议结合实际情况深入思考。
发现内容有问题?
告诉我们哪里需要改进。
