11.5 创建索引分区 · 波仔的 AI 编程实战课

📄 一句话概括

表分区之后，索引有两个选择： 本地索引（每个分区各一个，跟着分区走）、全局索引（一个索引管全表，和分区无关）。 判据是「查询带不带分区键」+「要不要定期删分区」。 两条冲突时，优先「要不要删分区」——因为「删分区后全局索引失效」会直接停业务。

📝 本章和前面几章的关系

10.1 讲索引，11.1~11.4 讲分区。这一篇是两者的交叉点。 单独看「索引怎么建」和「表怎么分区」都不难， 难的是「分区表的索引怎么建」——这里有个会停业务的选择。 这一篇的结论可以直接背下来： 「要删分区的表，索引建本地；不删分区的表，看查询带不带分区键。」

一、本地索引：分区和索引一一对应

CREATE INDEX idx_sale_ym ON sale (ym) LOCAL; 12 个分区就有 12 个索引段，一个分区一个。 它的核心性质是「索引分区和表分区一一对应」： 查一个分区，只打开对应的那一个索引，其他 11 个连碰都不碰。这叫「索引也跟着裁剪」。 本地索引还有个硬性要求：索引里必须包含分区键。 这条要求常常被人抱怨「多一列碍事」，但它不是随便定的—— 没有分区键，索引就没法按分区拆开，也就不是本地索引了。 代价： 按非分区键查（比如按订单号查、不知道月份），12 个索引得全查一遍。 这时候本地索引反而比全局索引慢。

二、全局索引：一个索引跨所有分区

CREATE INDEX idx_sale_id ON sale (id) GLOBAL; 一个索引管全表，和分区结构无关。 它的价值：不依赖分区键。 按 id 查，不用知道数据在哪个月份的分区里，索引直接给地址。 代价有两个： ① 维护成本集中——任何分区的插入都要改这一个索引，高并发下争用明显； ② 删分区时它会失效——因为索引里那部分条目指向的数据没了，索引结构就不完整了。 第二条是致命的。 Oracle 会把全局索引标成 UNUSABLE，之后任何用到它的查询报 ORA-01502。 不是慢，是查不了。 修的办法是 REBUILD，而几十亿行的索引重建要按小时算。

三、怎么选：两句话

第一句：查询带不带分区键？ 带 → 本地索引（能跟着裁剪）；不带 → 全局索引（不然一堆索引全开）。 第二句：这张表要不要定期删分区？ 要删 → 本地索引（删分区时索引自动走，什么都不用管）；不要删 → 按第一句选。 两句冲突时，优先第二句。 理由很实际： 「查询慢一点」是性能问题，「删分区后索引失效」是可用性问题。 性能问题可以调，可用性问题会把人从被窝里叫起来。 折中方案也有：DROP PARTITION ... UPDATE GLOBAL INDEXES。 删分区时同步维护全局索引，索引不会失效，但删分区会变慢（要按索引逐条摘）。 这是「用维护时间换可用性」，11.6 会实测这个选择。

四、动手 Demo：本地 12 个索引 vs 全局 1 个索引，看各自被打开几个

沙箱里没有 LOCAL／GLOBAL 关键字， 但两种索引的差别可以用「多张小表各建索引」和「一张大表建一个索引」复现出来。 证据还是查询计划： 本地索引只会出现「当前分区那一个」，全局索引出现的是「管全表那一个」。

# 本地索引 vs 全局索引：差别在「删分区之后」
import sqlite3
c = sqlite3.connect(":memory:")
for m in ("01", "02"):
    c.execute("create table p%s (name text)" % m)
    c.executemany("insert into p%s values (?)" % m,
                  [("n%04d" % i,) for i in range(1000)])
    c.execute("create index idx_p%s on p%s (name)" % (m, m))
c.commit()

def show(tag):
    rows = c.execute("select name, tbl_name from sqlite_master where type='index' "
                     "and name like 'idx%'").fetchall()
    print(tag, rows)

show("建完：")
c.execute("drop table p01")
c.commit()
show("删掉 p01 之后：")
print("p01 的索引没了，p02 的还在——本地索引跟着分区走。")

实跑输出：

--------------------------------------------------------------------
第五十一课：索引分区——本地索引与全局索引
--------------------------------------------------------------------

  **表分区之后，索引有两个选择：跟着分区走（本地），还是自己独立（全局）。**
  **这个选择比「建不建索引」还容易搞错，因为它牵扯到「删分区时索引怎么办」。**

  一、本地索引：每个分区各建一个
  --------------------------------------------------------------------

  SQL> select name as 索引名, tbl_name as 所属分区 from sqlite_master where type = 'index' and name like 'idx_p%' order by tbl_name limit 4
  索引名           所属分区
  -------------------------
  idx_p202401_name p202401
  idx_p202402_name p202402
  idx_p202403_name p202403
  idx_p202404_name p202404
  已选择 4 行
  **12 个分区就有 12 个索引**——这叫本地索引（local index）。
  **它的好处是「分区和索引一一对应」：删一个分区，它的索引自动一起删。**

  SQL> explain query plan select * from p202407 where name = 'n000042'
  SEARCH p202407 USING INDEX idx_p202407_name (name=?)
  **查 7 月的数据，用的是 `idx_p202407_name`**——
  **只有这一个索引被打开，其他 11 个连碰都不碰。**  **这就是「本地索引跟着裁剪走」。**
  **但本地索引有个硬伤：分区键必须在索引里。**
  **不然「按 `name` 查、不知道哪个月」的时候，12 个索引得全查一遍。**

  二、全局索引：一个索引跨所有分区
  --------------------------------------------------------------------

  SQL> select count(*) as 全局索引覆盖的行数 from allsale
  全局索引覆盖的行数
  ------------------
  240000
  已选择 1 行

  SQL> explain query plan select * from allsale where name = 'n000042'
  SEARCH allsale USING INDEX idx_all_name (name=?)
  **全局索引是一个索引管 24 万行**，
  **查 `name` 不用知道是哪个月**——  **这是它的价值：不依赖分区键。**
  **代价有两个：**  **① 维护成本高**——任何分区插入都要改这一个索引，争用集中；
  **② 删分区时它会失效**——  **因为索引里那部分条目指向的数据没了，索引结构就不完整了。**

  三、怎么选：一句话判断
  --------------------------------------------------------------------

  [Oracle 原码 · 本沙箱不执行]
  | -- 本地索引
  | CREATE INDEX idx_sale_ym ON sale (ym) LOCAL;
  | -- 全局索引
  | CREATE INDEX idx_sale_id ON sale (id) GLOBAL;
    **判据是「你的查询带不带分区键」。**  **带**（比如总带月份）→ 本地索引，裁剪能带着索一起裁。
    **不带**（比如按订单号查）→ 全局索引，不然 12 个索引全开。

  **还有一条运维上的判据：这张表要不要「定期删分区」。**
  **要删 → 本地索引（删分区时索引自动走，不用管）；不要删 → 全局索引更灵活。**
  **两条判据冲突时，优先「要不要删分区」**——
  **因为「删分区后全局索引失效」是个会直接停业务的事故。**

第一段：12 个分区 12 个索引。 这不是「浪费」，是本地索引的定义——一个分区一个索引段，删分区时一起走。 第二段：查 7 月，计划里只有 idx_p202407_name。 其他 11 个索引一个都没被打开——这就是「索引也跟着裁剪」。 第三段：全局索引。 一个索引覆盖 24 万行，按 name 查不用知道月份—— 这是它的价值，也是它的负担：所有分区的插入都要改它。 第四段是判据。 注意最后一句：两条判据冲突时优先「要不要删分区」—— 因为「删分区后全局索引失效」不是慢，是 ORA-01502 直接报错。

30 秒实验

把全局索引那段改成「按 ym 建索引」，再想想： 这和本地索引有什么区别？答案是「本地索引是它按分区拆开的样子」，逻辑上等价，物理上一个索引变 12 个。
算一下：24 万行的全局索引，删掉一个 2 万行的分区，会留下 2 万条悬空条目。这就是 REBUILD 要清掉的东西。
想想「按月查、又要按订单号查」的表该怎么建索引。 答案：本地索引（ym）+ 全局索引（id）各一个，两条路都通。

常见误区

「本地索引一定比全局索引快」。 只在「查询带分区键」时成立。 按非分区键查，本地索引要开 N 个，反而更慢。
「全局索引更先进」。 它维护成本更高、删分区会失效。「先进」不是选它的理由。
「删分区不影响索引」。 影响本地索引（自动一起删，正常）， 影响全局索引（变 UNUSABLE，事故）。
「本地索引可以不含分区键」。 不行。不含分区键就没法按分区拆开，也就不是本地索引了。

自测三问（点开对答案）

1. 本地索引和全局索引最本质的区别是什么？ —— **本地索引和表分区一一对应（一个分区一个索引段），全局索引一个管全表。** 2. 删分区时，两种索引分别会怎样？ —— **本地索引跟着分区一起删，干净；全局索引变 `UNUSABLE`，用到时报 `ORA-01502`。** 3. 本地索引为什么必须包含分区键？ —— **没有分区键就没法按分区拆成多个索引段**，也就不是本地索引了。 4. 两条判据冲突时，为什么优先「要不要删分区」？ —— **因为「索引失效」是可用性问题，不是性能问题。** **性能可以调，可用性事故会直接停业务。**

下一篇是本章最后一篇：管理索引分区。 重点讲「删分区之后索引怎么了」，以及 REBUILD、RENAME 这些补救动作。

☐ （本篇完，下一篇 11.6 管理索引分区）