波仔
📊 第6章 大数据的积累
📄 一句话概括

操作型库为「改一行、查一行」而生，分析型库为「扫全表、做聚合」而生。 演示里扫一列：SQL 表 0.0249 秒，独立内存列 0.0020 秒，差 12.6 倍。 账可以直接算：求一列和，行存要读 3.8MB，列存只要 1.5MB。

📝 本章和前面几章的关系

第 5 章讲数据怎么进来。这一章讲进来之后怎么存。 教材 6.1、6.2 两节：数据池与数据仓库、分析型数据库。 第 2.2 篇讲过池和仓的区别（原样与洗净）。这一篇往下走一层：仓库内部该用什么结构。

一、先看操作型库擅长什么

演示建了一张二十万行的表，主键是单号，然后按主键查一千次。 总耗时 0.0402 秒，平均每次 40.2 微秒。 四十微秒是什么概念？一次网络往返通常几毫秒，所以这个查询快到可以忽略不计。

原因是主键上有一棵 B 树，定位一行只要走几层。 这就是操作型数据库的核心能力：改一行、查一行，几万次。 订单库、用户库、库存库，全是这种活。它们的要求是「每次都很快」，不是「一次算很多」。

二、再看它不擅长什么

同样这张表，求金额列的总和：0.0249 秒。 然后把这一列单独放在内存里，再求一次：0.0020 秒。差 12.6 倍。 为什么？因为表是按行存的。要读金额，得把整行读出来（单号、用户、件数、金额），另外三列白读了。

这就是分析型负载的典型样子：只关心一两列，但要扫全表所有行。 操作型库的结构对这件事很不友好——它为「快速找到某一行」优化，不为「快速扫过某一列」优化。

三、这笔账能直接算出来

演示第四段算了一笔：一条记录 20 字节（三个整数各 4 字节、一个浮点 8 字节）。 求金额列总和：行存要读整行，20 字节 × 20 万 = 3.8MB； 列存只读那一列，8 字节 × 20 万 = 1.5MB。 同样一件事，读的字节数差四倍。 数据量大、列数多的时候，这个倍数会拉到几十倍。

反过来，查一整行的时候行存一次就够，列存要拼四列。所以两种存法各擅长一半。 没有全能选手——这句话是这一篇的核心。 教材 6.2 节讲的「操作型数据库与分析型数据库的不同之处」，本质就是这张对照表。

四、为什么非要分两个库

把两种活放在一个库上，一定会有一边难受。 业务库天天改一行，每改一次都要更新所有索引；如果它按列存，改一行要动好几处。 分析库天天扫全表，如果它按行存，读一列要拖上十几列没用数据。

所以真实系统通常是两套：业务库管交易，定期把数据同步到分析库。 同步这一步就是第 5 章讲的收集——从业务库抽出来，洗干净，灌进仓库。 第 2.2 篇的池和仓，在这儿又出现了一次。 这也解释了一个常见困惑：为什么公司里同一个「订单数」，业务后台和报表系统经常对不上。 因为它们查的是两个库，同步有时间差，口径也可能不同。

五、动手 Demo：点查一千次，再扫一列，两种库各多久

建一张二十万行的表，主键是单号。 按主键查一千次，看平均每次多少微秒。 再求金额列总和，跟「列已经单独放在内存里」比一次。

con.execute('create table t(o int primary key, u int, q int, a real)')
con.executemany('insert into t values(?,?,?,?)', rows)
con.commit()

# 点查：操作型最擅长的活
for k in range(1000):
    con.execute('select * from t where o = ?', (100000 + k * 97,))

# 扫一列：分析型最常干的活
tot = con.execute('select sum(a) from t').fetchone()[0]

# 同一列如果本来就按列存着
col_a = [r[3] for r in rows]
tot2 = sum(col_a)

实跑输出：

--------------------------------------------------------------------
操作型库补一行快，分析型库扫一列快
--------------------------------------------------------------------

  ① 建一个操作型库：主键 + 逐行插入
  --------------------------------------------------------------------
  200000 行，装载耗时 0.898 秒，库文件 6508544 字节

  ② 点查一行：操作型最擅长的活
  --------------------------------------------------------------------
  按主键查 1000 次，耗时 0.0402 秒（每次 40.2 微秒）
  主键就是一棵 B 树，定位一行只要走几层。这是操作型数据库的核心能力：改一行、查一行。

  ③ 扫一列：分析型最常干的活
  --------------------------------------------------------------------
  求 a 列总和：59700000.00，耗时 0.0249 秒
  同一列已经独立放在内存里：59700000.00，耗时 0.0020 秒
  差 12.6 倍。因为表是按行存的，读 a 列得把整行都读出来，另外三列白读。读的字节数多，自然就慢。

  ④ 这笔账可以直接算
  --------------------------------------------------------------------

| 要算的活 | 行存要读 | 列存要读 |
|---|---|---|
| 求 a 列总和 | 20 字节 × 200000 行 = 3.8 MB | 8 字节 × 200000 行 = 1.5 MB |
| 查单号 150000 | 定位一行，读 20 字节 | 要扫 4 列才凑齐一行 |

  看第一行：同样求一列和，行存要读整行，列存只读一列，差四倍。
  数据量大、列数多的时候，这个倍数会拉到几十倍。
  第二行反过来：查一整行的时候，行存一次就够，列存要拼四列。所以两种存法各擅长一半，没有全能选手。
  教材 6.2 节讲的「操作型数据库与分析型数据库的不同之处」，本质就是这一张表。

  ⑤ 那为什么还要分两种库
  --------------------------------------------------------------------

|  | 操作型（OLTP） | 分析型（OLAP） |
|---|---|---|
| 每天干的活 | 改一行、查一行，几万次 | 扫全表、做聚合，几次 |
| 优化目标 | 点查的延迟 | 扫描的吞吐 |
| 存储布局 | 按行 | 按列 |
| 典型 | MySQL、订单库 | ClickHouse、DWH |

  把两种活放在一个库上，一定会有一边难受。
  所以真实系统通常是：业务库管交易，定期把数据同步到分析库。
  这就回到了第 2.2 篇的池和仓——池子接原始数据，仓库供分析。

第一段的 40.2 微秒值得记一下。 它说明「点查」这件事在操作型库上便宜到可以忽略，所以业务代码里一次查一行完全没问题。 第三段那个 12.6 倍是关键对照。 注意 SQL 那边没建任何针对 a 列的索引——建了也没用，因为要算全表总和，索引帮不上。 第四段那张字节账是算出来的，不是测出来的。但它比耗时更可靠：字节数不随机器变，耗时随。 最后一段提到「两个系统数字对不上」，这是真实工作中最常见的争吵来源。 记住它多半不是 bug，是同步时差和口径差异。

30 秒实验

  1. 给 a 列加一个普通索引，再跑一次 sum(a)，看有没有变快。（应该没有。）
  2. 把点查改成 where u = ?（非主键），看 1000 次要多久——全表扫和主键定位差多少倍。
  3. 把 N 从 20 万改成 2 万，看 12.6 倍的差距有没有变化。

常见误区

  1. 「一个数据库什么都能干。」能干，但总有一边难受。交易和分析对存储结构的要求是相反的。
  2. 「加了索引就快了。」索引对点查有用，对「扫全表求和」几乎没用——因为还是要读所有行。
  3. 「报表数字和后台不一致是 bug。」先查是不是两个库、同步有没有延迟、口径是不是一样。 这三条能解释九成的「对不上」。
自测三问（点开对答案） 1. 为什么按主键查一行只要 40 微秒？ —— 因为主键上有一棵 B 树，定位一行只要走几层，不用扫全表。这是操作型数据库的核心结构。 2. 为什么求一列和在行存上要慢 12 倍？ —— 因为行存按行组织，读一列得把整行读出来，另外几列白读。读的字节数是列存的四倍，还要多做解析。 3. 为什么公司里两个系统的订单数会对不上？ —— 常见三个原因：查的是两个库、同步有延迟、口径定义不同。很少是真的算错了。

下一篇专门讲列式存储：它为什么读得少，还压得动。

相关笔记