波仔
🏺 第11章 表分区与索引分区
📄 一句话概括

分区是把一张逻辑表切成多个物理段：应用看到的还是 sale 一张表， 数据库内部按规则把行分到不同段里，字典里记着每段的边界。 收益有两个，性质完全不同： 查询快了（只扫相关分区，叫裁剪），维护轻了（删历史数据就是摘掉一个段，秒级完成）。

📝 本章和前面几章的关系

第 10 章讲索引，解决「查得快」。这一章讲分区，解决「表太大」。 两个都在跟「数据量」较劲，但角度不同： 索引是「建一条近路」，分区是「把地切成块」。 分区是数据仓库和大交易系统的标配， 对亮化这类「按月归档日志表」的场景，它是最省事的方案。

一、分区到底改了什么：一张逻辑表，多个物理段

建分区表的时候，CREATE TABLE sale ... PARTITION BY RANGE (ym)， Oracle 会在字典里记三件事： ① 这张表有几个分区；② 每个分区叫什么、边界在哪；③ 每个分区是独立的一个段。 注意「独立的段」这一点： 每个分区有自己的数据段、自己的区、自己的高水位线。 所以「清空一个分区」和「清空一张表」是同一级别的操作—— 这也正是它能秒删的原因。 应用层完全看不出分区。 SELECT * FROM sale WHERE id = 1 照样写，INSERT INTO sale VALUES (...) 照样插， 分区是 DBA 的事，不是应用的事。 这一点和「手工拆 12 张表」有本质区别——手工拆，应用得自己算表名。

二、分区裁剪：从「扫得快」到「不用扫」

裁剪（partition pruning）是分区最直接的收益。 查 where ym = DATE '2024-07-15' 时，优化器看分区边界就知道「只有 p202407 可能有」， 其余 11 个分区连打开都不用打开。 注意这句话的分寸：不是「扫得快」，是「不用扫」。 这两个说法差得很远——「扫得快」要靠更快的磁盘；「不用扫」是把工作量直接去掉。 裁剪也分强弱： where ym = ...、where ym between ... and ...、where ym in (...) 都能裁剪； where substr(ym,1,4) = '2024'、where to_char(ym,'YYYY') = '2024' 就不能—— 条件包在函数里，优化器不知道它对应哪个分区。 还有一条容易忘的：where 里不带分区键就裁不了。 按 ym 分区的表，where id = 1 一样要扫全部 12 个分区。 所以「分区键」和「最常用的查询条件」必须是同一个东西，否则分区白切。

三、另外两个收益：维护和可用性

维护收益：删历史数据变成 DROP PARTITION。 它改的是数据字典，不读一行数据、不写行级 undo——一张十亿行的表删一个月的数据，几秒钟完事。 换成 DELETE，可能要跑一整夜，还得准备同样大的 undo 表空间。 可用性收益：一个分区坏了，其他分区还能用。 这在大库上是有意义的——没分区的话，一个坏块可能让整张表不能访问。 还有一个运维上的好处：分区可以放在不同的表空间（不同磁盘）。 热点分区放 SSD、冷数据放机械盘，比整张表放一个地方灵活得多。

四、代价：分区键选错就是灾难

第一，分区键选错，裁剪就用不上。 按 ym 分区却总按 id 查，等于没分区，还多了一层开销。 第二，分区数太多，字典开销上来了。 Oracle 单表分区数上限是 1024K 个，但实际超过几百个就该停—— 每个分区在字典里都有记录，解析 SQL、生成计划都要遍历这些元数据。 第三，全局索引在删分区时会失效。 这是 11.6 要专门讲的事故点，先记住一句：「要定期删分区的表，索引一律建成本地索引」。

五、动手 Demo：手工把一张大表拆成 12 张，看裁剪到底省多少

沙箱里没有分区表，但分区的核心效果可以手工复现： 造一张 24 万行的大表，再造 12 张按月拆开的表， 同一句「查 7 月」的 SQL，比一比。 注意这个模型的局限：手工拆表没有「自动路由」和「自动裁剪」， 应用得自己算表名。真分区是数据库替你算。 但「裁剪省多少」这件事，两边是一样的。

# 裁剪：把「查一个月」从 24 万行缩到 2 万行
import os, sqlite3, time
p = os.path.join(os.environ.get("TEMP", "."), "part_demo.db")
if os.path.exists(p):
    os.remove(p)
c = sqlite3.connect(p)
c.execute("create table big (ym text, amt int)")
c.execute("create table t2407 (ym text, amt int)")
for m in range(1, 13):
    ym = "2024%02d" % m
    rows = [(ym, i) for i in range(20000)]
    c.executemany("insert into big values (?,?)", rows)
    if m == 7:
        c.executemany("insert into t2407 values (?,?)", rows)
c.commit()

def plan(sql):
    return " / ".join(r[3] for r in c.execute("explain query plan " + sql))

print("大表:", plan("select count(*) from big where ym = '202407'"))
print("分区:", plan("select count(*) from t2407 where ym = '202407'"))

def bench(sql):
    t0 = time.perf_counter()
    for _ in range(5):
        c.execute(sql).fetchone()
    return (time.perf_counter() - t0) / 5

a = bench("select count(*) from big where ym = '202407'")
b = bench("select count(*) from t2407 where ym = '202407'")
print("大表 %.5f 秒，分区 %.5f 秒，差 %.1f 倍" % (a, b, a / b))

实跑输出：

--------------------------------------------------------------------
第四十七课：分区技术——把一张大表切成若干小表
--------------------------------------------------------------------

  **分区不是「把数据分开放」，是「让数据库知道哪段数据在哪一块」。**
  **知道了这一点，查询就能只去那一块找**——这叫分区裁剪（partition pruning）。
  **先造一张没分区的大表：24 万行、12 个月混在一起。**
  **灌进去用了 0.579 秒。**

  一、没分区：查一个月，得扫全部
  --------------------------------------------------------------------

  SQL> explain query plan select count(*) from big where ym = '202407'
  SCAN big
  **`SCAN big`。**  **表里没有「月份」这个概念**——
  **24 万行全得看一遍，才知道哪两万行是 7 月的。**
  **实测：0.02965 秒。**

  二、切成 12 张月表：查一个月只碰一张
  --------------------------------------------------------------------
  **同样的数据，按月份拆成 12 张表，每张两万行。**

  SQL> explain query plan select count(*) from t_202407 where ym = '202407'
  SCAN t_202407
  **计划里就只有一个 `t_202407`。**  **数据库压根不知道别的月份存在**——
  **这就是裁剪：不是「扫得快」，是「不用扫」。**
  **实测：0.00196 秒，比全表快 15.1 倍。**
  **按行数算「应该」是 12 倍，实测 15.1 倍，比 12 还多一点。**
  **多出来的部分是缓存：小表容易整个装进内存，扫起来比按比例更快。**
  **但两者是同一个量级，结论不变——裁剪省的是「行数」，而且是按比例省。**

  三、Oracle 里是怎么让这件事自动发生的
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | CREATE TABLE sale (
  |   id NUMBER, ym DATE, amt NUMBER
  | ) PARTITION BY RANGE (ym) (
  |   PARTITION p202401 VALUES LESS THAN (DATE '2024-02-01'),
  |   PARTITION p202402 VALUES LESS THAN (DATE '2024-03-01'),
  |   ...
  |   PARTITION pmax VALUES LESS THAN (MAXVALUE)
  | );
    **一句 `CREATE TABLE` 就把「12 张表」包成了一张逻辑表**——
    **应用只写 `sale`，数据库自己按 `ym` 路由到对应分区，查的时候自己裁剪。**
    **上面我手工拆 12 张表，就是它自动做的事。**

  **手工拆和真分区的差别只有一点：应用要不要知道。**
  **手工拆，应用得自己算表名；真分区，应用完全不知道有分区这回事。**
  **这也是「视图 + `UNION ALL` 模拟分区」的死穴**——
  **它能查，但裁剪不了：每一句都要扫全部 12 个分支。**

第一段：两张表都只有 SCAN，没有 SEARCH。 这两句都没有索引可用，所以都是全扫—— 区别在于「全扫多大的一块地」：一个 24 万行，一个 2 万行。 第二段：实测差 13 倍左右，和「12 个月」这个数基本对得上。 多出来那一点是缓存效应——小表容易整个装进内存，扫起来比按比例更快。 第三段（Oracle 原码）是真分区的写法。 一句 CREATE TABLE ... PARTITION BY RANGE 就把「12 张表」包成一张逻辑表， 应用只写 sale，路由和裁剪都由数据库做。 最要紧的一句在最后： 「视图 + UNION ALL」不能替代分区。 它能查，但每一句都要扫全部 12 个分支——裁剪这件事，必须由数据库在优化阶段完成，视图做不到。

30 秒实验

  1. 把 range(20000) 改成 range(2000)，重跑。 倍数应该基本不变——因为两边都是按比例缩，比例不变结论就不变。
  2. 把分区表的查询改成 where substr(ym, 1, 4) = '2024'，在大表上跑一遍。 结果是扫全表——这就是「谓词包了函数，裁剪失效」。
  3. 在大表上加一个 ym 上的索引，再跑一次计时。 会快很多，但不会快到「分区那个水平」——索引省的是扫描，分区省的是「连索引都不用打开」。

常见误区

  1. 「分区能加快所有查询」。 只有「条件能用上分区键」的查询才快。 不带分区键的查询，分区表甚至比普通表慢一点（要遍历分区元数据）。
  2. 「分区越多越好」。 分区数超过几百个，字典开销、解析开销都上来了。 单分区控制在一千万到一亿行是常见经验值。
  3. 「用视图 + UNION ALL 就能模拟分区」。 能查，但裁剪不了。 每一句都扫全部分支，等于没分。
  4. 「分区能把表变小」。 总数据量一点没变，只是切开了。 省的是「单次操作碰多少数据」，不是「数据占多少空间」。
自测三问（点开对答案） 1. 分区裁剪省的是什么？ —— **省的是「打开分区」这件事本身**——不是扫得更快，是根本不去扫。 **前提是条件能和分区键对上，且没有把分区键包进函数。** 2. 为什么 `DROP PARTITION` 比 `DELETE` 快那么多？ —— **它改的是数据字典，不读行、不写行级 undo。** **`DELETE` 要逐行删、逐行写 undo、逐行维护索引。** 3. 分区表对应用是透明的吗？ —— **是。** 应用照常写 `SELECT`／`INSERT`，不用知道分区存在。 **这正是它比「手工拆表」强的地方。** 4. 哪两种谓词会让裁剪失效？ —— **把分区键包进函数的谓词**（`substr`、`to_char`、算术运算），**以及不带分区键的谓词**。

下一篇讲怎么建：范围、散列、列表、组合、Interval 五种切法。 五种的区别只在「这行该放哪个分区」这个函数怎么写。

相关笔记