数据库方案


最简单的方案

在上一章,我分析了各种技术方案。现在,让我从最简单的开始。

先用数据库实现,看看会发生什么。


数据库设计

表结构

我需要两张表:

数据设计要点

  • 核心是在 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_update10 个并发点赞得到 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) 的联合唯一索引?

MySQL索引唯一约束

解题思路:

这道题问的是一个索引同时承担了几重职责,要从『约束』和『访问路径』两条线拆开看:它既是数据正确性的护栏,也是高频查询的入口。

详细步骤:

原因 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

如何解决并发更新导致的”丢失更新”问题?

并发控制事务MySQL

解题思路:

这道题问的是丢失更新的四种解法,关键在于分清每种解法『是否需要先读旧值』——这决定了它是阻塞式的还是无锁的,也决定了在点赞这种低冲突场景下谁更划算。

详细步骤:

方案一:使用乐观锁

数据设计要点

  • 这是一次表结构演进:随着业务能力增加,把新状态、新时间点或新归属关系补进数据模型。

方案二:使用数据库原子操作

数据设计要点

  • 这里关注数据模型和约束关系,不需要记住具体语法。

这种方式不需要读取原值,直接在数据库层面完成计算。

方案三:使用 SELECT FOR UPDATE

数据设计要点

  • 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。

方案四:使用 Redis 计数器

推荐方案:

对于点赞计数,推荐使用 方案二(原子操作) 或 方案四(Redis):

  • 方案二简单高效,利用数据库原子性
  • 方案四性能最好,适合高并发场景

最终答案:

四种解法分别是:乐观锁(读旧值 + 版本号校验)、数据库原子操作(like_count = like_count + 1,完全不读旧值)、SELECT FOR UPDATE 悲观锁(读时锁行,冲突时阻塞)、Redis 计数器(内存操作、性能最好、最终一致)。点赞计数推荐数据库原子更新(简单高效)或 Redis 计数器(高并发场景),悲观锁只在一致性要求极高时才用。

练习 3

设计一个接口,返回文章列表及每篇文章的点赞数和当前用户是否点赞。

要求:优化查询性能,避免 N+1 查询问题。

SQL优化N+1问题性能

解题思路:

这道题的关键词是 N+1——先算清查询次数从 1+2N 能降到多少,再比较批量查询、LEFT JOIN、冗余字段三条路的查询次数与实现复杂度,最后落到具体推荐。

详细步骤:

问题分析:

N+1 查询问题:

优化方案一:批量查询

数据设计要点

  • 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。

优化方案二:LEFT JOIN

数据设计要点

  • 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。

优化方案三:使用冗余字段 + 批量查询是否点赞

数据设计要点

  • 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。

性能对比:

方案查询次数复杂度
N+1 查询1 + 2N简单
批量查询3中等
LEFT JOIN1复杂
冗余字段 + 批量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)

优化建议:

  1. 覆盖索引优化(如果只需要文章 ID):

数据设计要点

  • 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
  1. 延迟关联(分页优化):

数据设计要点

  • 查询目标是快速定位状态、任务或资源,避免在关键路径上做大范围扫描。
  1. 缓存优化:

最终答案:

在 article_likes 上建复合索引 idx_user_created (user_id, created_at DESC),让『某用户点赞过的所有文章、按点赞时间倒序』走索引扫描并避免 filesort。优化三步:只需要文章 ID 时用覆盖索引不回表;分页时用延迟关联(先从索引取 id 再回表关联文章表);高频查询的结果再加一层缓存。


思考题

  1. 如果用户在一篇文章上反复点赞、取消点赞,会对数据库产生什么影响?如何优化?

  2. 如何实现”点赞动画”效果?前后端如何配合?

  3. 如果需要统计”用户获得的点赞总数”(即用户所有文章的点赞数之和),如何设计?

💡 提示:这些问题没有标准答案,建议结合实际情况深入思考。

发现内容有问题?

告诉我们哪里需要改进。