一、业务流程：主流程短，分支多

主流程只有六步，画出来是一根直线：

右列六条分支，才是设计真正要处理的东西。 而且它们有个共同特点：都不在「主流程」的图上，只在「想一想」的时候才会冒出来。 第五条特别值得看：「改一篇」的失败原因有两种——别人改（越权）和已被删（不存在）。 这两种在代码里都表现为「影响 0 行」，但给用户的提示应该不同：一个是「无权操作」， 一个是「笔记不存在」。 如果统一成「操作失败」，用户会反复点。

二、界面预览：三个页面就够

第三行的「按钮要不要按权限显示」是个假问题：按钮藏起来不等于不能调用。 真正决定权限的是后端那条 SQL 的 user_id 条件，不是按钮在不在。 藏按钮只是「不让用户困惑」，不是「安全措施」。 很多人把这两件事混起来，于是出现「前端藏了按钮，后端没判断」的系统。 这种系统的漏洞用浏览器开发者工具就能触发——改一下请求参数就行。

三、列表页：两条查询，别硬凑成一条

列表页要两样东西：当前页的十条数据，以及「一共多少条」用来算页数。 最直接的写法是两条 SQL。有人觉得这样「多一次往返」，想合成一条。

第三列的「较慢」是实测出来的，不是猜的。 原因在于窗口函数要先在整个结果集上算一遍再截断，而 COUNT(*) 只数不排序。 所以「合并成一条」在这里既没换来速度，又增加了写错的概率。 第四列的「高」指的是那个坑：COUNT(*) OVER () 算的是「结果集」的行数， 一旦结果集被 LIMIT 截断，它就数成了十条。 演示里把这个错法真跑了一遍：本该是 2000，算出来是 10。

四、总数该不该缓存

演示里两万行的表上，数一次要六毫秒左右——因为它没走索引，真扫了一遍。 这个数不算大，但它是「每次翻页都要算一次」的。

第三行是最省事的：很多产品根本不需要「第 12 页 / 共 300 页」这种页码，「加载更多」就够了。 这时候连 COUNT(*) 都不用发，问题直接消失。 所以遇到「这个数要算很久」的时候，先问一句「这个数真的需要吗」，往往比优化更快。 如果确实要显示，那就回到那个老问题：这个数字能容忍多旧。 「共 2003 条」和「约 2000 条」在体验上没有差别，所以缓存几十秒完全够用。

五、动手 Demo：列表页的两条查询，和窗口函数的那个坑

这一段在 2000 篇笔记上对比三种做法：两条查询、一条窗口函数、 以及「窗口函数被 LIMIT 截断」的错法。最后量一次总数查询的耗时。

import sqlite3, time

c = sqlite3.connect(u"des.db", isolation_level=None)
c.executescript(u"""
CREATE TABLE notes (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL,
  title TEXT, is_deleted INTEGER DEFAULT 0);
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO notes (user_id, title) VALUES (?,?)",
              [(i % 10 + 1, u"笔记%04d" % i) for i in range(1, 20001)])
c.execute(u"COMMIT")

# ① 两条查询
t = time.perf_counter()
total = c.execute(u"SELECT COUNT(*) FROM notes WHERE user_id=? AND is_deleted=0",
                  (3,)).fetchone()[0]
page = c.execute(u"SELECT id, title FROM notes WHERE user_id=? AND is_deleted=0 "
                 u"ORDER BY id DESC LIMIT 10", (3,)).fetchall()
t2 = time.perf_counter() - t
print(u"两条查询 %.3f ms，总数 %d，当前页 %d 条" % (t2 * 1000, total, len(page)))

# ② 一条窗口函数
t = time.perf_counter()
rows = c.execute(u"SELECT id, title, COUNT(*) OVER () AS total FROM notes "
                 u"WHERE user_id=? AND is_deleted=0 "
                 u"ORDER BY id DESC LIMIT 10", (3,)).fetchall()
t1 = time.perf_counter() - t
print(u"窗口函数 %.3f ms，total 列 %d" % (t1 * 1000, rows[0][2]))

# ③ 错法：窗口函数被 LIMIT 截断
n = c.execute(u"SELECT COUNT(*) FROM (SELECT id, COUNT(*) OVER () AS t "
              u"FROM notes WHERE user_id=? LIMIT 10)", (3,)).fetchone()[0]
print(u"截断后算出 %d（真实 %d）" % (n, total))

# ④ 数一次总数的成本
t = time.perf_counter()
for _ in range(50):
    c.execute(u"SELECT COUNT(*) FROM notes WHERE user_id=? AND is_deleted=0",
              (3,)).fetchone()
print(u"数一次总数 %.2f ms" % ((time.perf_counter() - t) / 50 * 1000))
c.close()

实跑输出：

--------------------------------------------------------------------
列表页的两条查询，和窗口函数的那个坑
--------------------------------------------------------------------

**教材 23.2 是系统设计：系统目标、业务流程、系统预览。**
**笔记系统的流程很短：注册 → 登录 → 列表 → 写 → 改 → 删。**
**但「列表」这一屏要用到两条查询：当前页的数据、以及一共多少条。**

  ① 分页要两条查询：一条取数，一条数总数
  --------------------------------------------------------------------
总数             2000 条
当前页            10 条
两条查询           5.669 ms

  ② 能不能一条搞定：窗口函数
  --------------------------------------------------------------------
一条查询           9.069 ms
total 列        2000（每行都带一遍）
**窗口函数看着很聪明：一次扫描就拿到「当前页」和「总数」。**
**但它有一个坑——`COUNT(*) OVER ()` 是在【结果集】上算的，**
**而这个结果集被 `LIMIT` 截断了就会算错。**
子查询里加 LIMIT    算出 10（真实是 2000）
**10 对 2000——窗口函数被 `LIMIT` 截断之后，数出来的就是「十条」。**
**所以窗口函数要用对位置：`LIMIT` 在外层、窗口函数在内层，而且内层不能被 `LIMIT` 截断。**
**再看耗时：一条窗口函数 9.07 毫秒，两条普通查询 5.67 毫秒——合并反而更慢。**
**原因是窗口函数要对「整个结果集」算一遍再截断，而 `COUNT(*)` 只数不排序。**
**所以「合并成一条」在这里既不更快，又更容易写错——大多数项目用两条，是理性选择。**

  ③ 总数查询该不该缓存
  --------------------------------------------------------------------
数一次总数          5.772 ms
**两万行的表上，数一次要 5.8 毫秒——因为它没走索引，是真扫了一遍。**
**而笔记系统里「总条数」这个数字，用户其实不需要精确到条——显示「约 2000 条」和「2003 条」，**
**体验没有差别。**
**所以很多系统会把它缓存几十秒，或者干脆显示「上一页/下一页」而不显示页码。**
**这一条和 22 章那个「首页总单量」是同一个判断：这个数字能容忍多旧。**

第一段和第二段的耗时对比是这一段的核心：两条查询反而更快。 所以「合并成一条减少往返」这个思路，在这里是错的——它减少的往返在进程内几乎不花钱， 多出来的窗口函数计算却要花钱。 第三段那个错法最值得记：COUNT(*) OVER () 算的是「结果集」的行数。 结果集一旦被 LIMIT 截断，它数出来的就是十条。 而这个错误的表现是「页码只显示 1 页」——看起来像分页没做好，其实是总数算错了。 第四段的数是「每次都算」的成本。两万行要六毫秒左右，说明它没走索引。 这也是一个可优化点：在 (user_id, is_deleted) 上建复合索引，这个数会降下来。 但更省事的做法是「不显示总页数」——问题直接消失。

30 秒实验

1. 在 (user_id, is_deleted) 上建复合索引，重跑第四段，看数一次总数降到多少。
2. 把第三段的子查询改成外层加 LIMIT、内层不加，看会不会算对。
3. 把 LIMIT 10 改成 LIMIT 100，看窗口函数那一段的耗时涨多少。

常见误区

1. 为了「少一次往返」硬把两条查询合成窗口函数。实测反而更慢，而且容易写错。 往返在进程内不花钱，窗口函数的计算要花钱。
2. 以为「藏起按钮」就是权限控制。按钮是前端的事，改一下请求参数就绕过了。 权限在后端那条 SQL 的 user_id 条件上。
3. 把所有失败都提示成「操作失败」。「无权操作」和「笔记不存在」在代码里都是「影响 0 行」， 但用户该看到的提示不同。

设计定了，下一篇讲开发必备：目录怎么分、依赖怎么管，以及 Python 项目一个特有的成本——解释器启动。

• ☐ （本篇完，下一篇 23.3 系统开发必备）

相关笔记

• 需求分析
• 系统开发必备
• 系统设计
• 🛢️ MySQL从入门到精通 · 索引