一、索引是什么：一棵排好序的树，外加行地址

没有索引时，找一行只能全表扫。 索引就是「另外存一份按某列排序的清单」，清单的每一项是「列值 + 行地址」。 Oracle 的索引默认是 B 树（平衡树）。 它的关键性质是「所有叶子在同一深度」—— 所以不管找哪个值，读的块数都一样，不会「有的快有的慢」。 一个三层高的 B 树能管多少行？ 按每个块存几百个条目算，三层就是几百万到上千万行。 这也是为什么「表从两万行涨到两千万行，索引查找几乎不变慢」——树高只多一层。 行地址有两种： 普通堆表的索引存 ROWID（文件号 + 块号 + 行号），索引组织表（IOT）的「索引」就是表本身， 叶子直接放整行。 这就是第 9 章说的「IOT 按主键查只要读三块」的由来。

二、什么样的列该建索引

三类列值得建： ① 经常出现在 WHERE 里的列；② 经常 JOIN 的列（外键）； ③ 经常 ORDER BY／GROUP BY 的列。 三类列不值得建： ① 只有几百行的表（全扫更快，优化器也知道）；② 区分度极低的列（性别只有两个值， 索引扫一半不如全扫）；③ 更新极频繁的列（每改一次都要维护索引）。 「区分度」这个词值得记住： 一个值平均重复多少次，叫「基数」。 性别 基数 2，身份证号 基数等于行数。 基数越低，索引越没用——因为查一个值要取出全表一半的行，还不如直接扫。 复合索引还有个「最左前缀」的规矩： (a, b, c) 上的索引，where a = ?、where a = ? and b = ? 都能用， 但 where b = ? 用不上。 像电话簿按「姓、名」排，你只知道名字，还是得从头翻。

三、创建索引：五种写法

最基本的：CREATE INDEX idx_emp_ename ON emp (ename); 加 UNIQUE 就是唯一索引，加 BITMAP 就是位图索引（适合低基数列，比如性别、状态）。 复合索引：CREATE INDEX idx_emp_dept_job ON emp (deptno, job); 列的顺序很重要——按「最左前缀」的规矩，(deptno, job) 和 (job, deptno) 是两个不同的索引。 函数索引：CREATE INDEX idx_emp_up ON emp (UPPER(ename)); 它解决的问题是「where UPPER(ename) = 'SMITH' 用不上普通索引」—— 因为普通索引存的是原值，不是大写值。 建索引的两个选项： ONLINE 让建索引时不阻塞 DML（企业版才有）；NOLOGGING 少写 redo、建得更快，但建完要备份。 大表建索引一定要加 ONLINE，不然整个表在几十分钟里只能读不能写。

四、修改索引：REBUILD、RENAME、COALESCE、UNUSABLE

ALTER INDEX idx REBUILD;——重建。 删除频繁的表，索引叶子会变稀疏（块里一半是空的），重建一次能小一圈、快一点。 ALTER INDEX idx RENAME TO idx_new;——改名。 什么时候用？换命名规范的时候，不用停业务。 ALTER INDEX idx COALESCE;——合并碎片，比 REBUILD 轻， 不重建整棵树，只把相邻的稀疏叶子拼起来。 ALTER INDEX idx UNUSABLE;——先停用。 这是「要重建但不想等」时的过渡手段： 标成不可用几毫秒完成，等你挑个窗口再 REBUILD。 代价是这期间查这张表用不上它，可能变慢。

五、删除索引与显示索引信息

DROP INDEX idx_emp_ename; 删之前先确认没人在用—— 「没人用」不是问人，是看有没有 SQL 依赖它（这靠执行计划和工作量视图判断，不靠猜）。 三张字典视图： USER_INDEXES（有哪些索引、状态、是否唯一）、USER_IND_COLUMNS（每个索引建在哪几列、 列的顺序）、USER_OBJECTS（状态是不是 VALID）。

一个常被忽略的事实：主键和唯一约束会自动建索引。 所以 USER_INDEXES 里会看到一堆名字像 PK_EMP、SYS_C0012345 的索引， 那不是谁手工建的，是约束带出来的。

六、动手 Demo：同一个查询，加个索引差出两个数量级

这一篇的 Demo 全都用「查询计划」当证据。 计划里写 SCAN 就是全扫，写 SEARCH ... USING INDEX 就是走了索引—— 这是最不容易糊弄人的证据。 我造两万行数据，跑同一句查询 300 次，分别在「有索引」和「没索引」两种情况下计时。 再单独测一遍「索引对插入的代价」，两头都量出来。

# 索引省的是什么：先看计划，再看时间
import os, sqlite3, time, random
p = os.path.join(os.environ.get("TEMP", "."), "idx_demo.db")
if os.path.exists(p):
    os.remove(p)
c = sqlite3.connect(p)
c.execute("create table t (id integer primary key, name text, deptno int, pad text)")
c.executemany("insert into t (name, deptno, pad) values (?,?,?)",
              [("n%05d" % random.randrange(99999), random.randrange(1, 21), "x" * 120)
               for _ in range(20000)])
c.commit()

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

print("无索引:", plan("select * from t where name = 'n12345'"))
c.execute("create index idx_t_name on t (name)")
print("有索引:", plan("select * from t where name = 'n12345'"))

names = ["n%05d" % random.randrange(99999) for _ in range(200)]

def bench():
    t0 = time.perf_counter()
    for nm in names:
        c.execute("select count(*) from t where name = ?", (nm,)).fetchone()
    return time.perf_counter() - t0

fast = min(bench() for _ in range(5))
c.execute("drop index idx_t_name")
slow = min(bench() for _ in range(5))
print("走索引 %.4f 秒，全扫 %.4f 秒，差 %.1f 倍" % (fast, slow, slow / fast))

实跑输出：

--------------------------------------------------------------------
第四十三课：索引对象——先看它扫多少块，再看它省多少时间
--------------------------------------------------------------------

  **索引这件事，讲原理容易，讲清楚「省了多少」需要数。**
  **沙箱里没有 Oracle 的 `V$` 视图，但有一样更好的东西：查询计划。**
  **计划里会明说「`SCAN`（全扫）还是 `SEARCH`（走索引）」，这就是最硬的证据。**

  一、先看「扫」这个字：没有索引时计划长什么样
  --------------------------------------------------------------------
  **两万行数据，查一个名字。**

  SQL> explain query plan select * from t where name = 'n12345'
  SCAN t
  **`SCAN t` 三个字就是「从头到尾把两万行翻一遍」。**
  **翻完才知道有没有——没有索引时，Oracle 也只能这么干。**

  二、建上索引，同一句 SQL 的计划变了
  --------------------------------------------------------------------

  SQL> create index idx_t_name on t (name)

  SQL> explain query plan select * from t where name = 'n12345'
  SEARCH t USING INDEX idx_t_name (name=?)
  **`SEARCH t USING INDEX idx_t_name (name=?)`**——
  **这句话翻译成中文是「先在索引里找到 `name` 等于这个值的行号，再回表取行」。**
  **注意括号里那一段：它说明索引的「哪个位置被用上了」。**
  **写 `(name=?)` 是等值查找，索引直接定位到那一条；写 `(name>?)` 就是范围扫描，索引只帮着找到起点，再顺着叶子往下读。**

  三、实测：两万行里查一行，差多少
  --------------------------------------------------------------------
  **同一份数据、同一条查询、同样 300 次，只差一个索引：**
  **全扫 1.5633 秒，走索引 0.01049 秒，快了 149.0 倍。**
  **这不是「快一点」，是「换了个量级」。**
  **因为全扫的代价随行数线性涨，走索引只随「树高」涨——两万行和两千万行，树高只多一两层。**

  四、代价：索引让插入变慢
  --------------------------------------------------------------------
  **三万行、同一个表结构，只差「有没有这两个索引」：**
  **不带索引 0.2309 秒，带两个索引 0.2735 秒，慢了 18%。**
  **索引不是白给的：每插一行，每个索引都要在 B 树上找到位置、插进去、可能还要分裂页。**
  **两个索引就多两次维护，代价大概就是两三成。**
  **所以「索引越多越快」是错的**——写多的表，索引是负担；读多的表，索引才是加速器。

  五、显示索引信息：字典里怎么查
  --------------------------------------------------------------------

  SQL> select name as 索引名, tbl_name as 表名, sql from sqlite_master where type = 'index' and tbl_name = 't'
  索引名     表名 定义
  ---------------------------------------------------
  idx_t_name t    CREATE INDEX idx_t_name on t (name)
  已选择 1 行
  **`pragma index_list(t)` 是 SQLite 的说法，列出「这张表上有哪些索引」：**
  **idx_t_name  唯一=0  来源=c**
  **Oracle 里对应的三张视图是 `USER_INDEXES`（有哪些索引）、`USER_IND_COLUMNS`（索引建在哪几列）、`USER_OBJECTS`（状态是否 `VALID`）。**

  六、唯一索引与删除索引
  --------------------------------------------------------------------

  SQL> create unique index uk_t_name on t (name)

  ERROR 位于第 1 行：
  UNIQUE constraint failed: t.name
  **`UNIQUE constraint failed`——表里本来就有重名（随机数撞上了）。**
  **建唯一索引会先校验一遍现有数据，不合法就整个失败，索引不会建出来。**

  SQL> select count(*) - count(distinct name) as 重名行数 from t
  重名行数
  --------
  1826
  已选择 1 行
  **先把重名清掉，再建就成功：**

  SQL> delete from t where id not in (select min(id) from t group by name)

  SQL> create unique index uk_t_name on t (name)

  SQL> select name as 索引名 from sqlite_master where type='index' and name like 'uk%'
  索引名
  ---------
  uk_t_name
  已选择 1 行

  [Oracle 原码 · 本沙箱不执行]
  | ALTER INDEX idx_t_name REBUILD;          -- 重建，消除碎片
  | ALTER INDEX idx_t_name RENAME TO idx_new; -- 改名
  | ALTER INDEX idx_t_name UNUSABLE;         -- 先停用，再重建
  | DROP INDEX idx_t_name;                   -- 删除
  **`REBUILD` 是索引的「换新房子」**——**删除频繁的表，索引叶子会稀疏，重建一次能小一圈、快一点。**


  七、删掉索引，计划原路退回
  --------------------------------------------------------------------
  **`name` 上现在有两个索引（普通索引和唯一索引），两个都删掉，再看计划：**

  SQL> drop index idx_t_name

  SQL> drop index uk_t_name

  SQL> explain query plan select * from t where name = 'n12345'
  SCAN t
  **两句 `DROP INDEX`，计划就退回 `SCAN` 了。**
  **所以「加了索引没生效」的第一步，永远是先看计划里有没有 `SEARCH`。**

第一段是计划对比：SCAN t 对 SEARCH t USING INDEX idx_t_name (name=?)。 括号里的 (name=?) 说明「等值查找生效了」——索引帮着一路走到那个值，不用从头翻。 第二段是时间对比，也是最容易被人引用的一个数。 但引用这个数要带上条件：两万行、单机、本地文件、300 次查询里取最快的一遍。 换成「几千万行、走网络、第一次查询」，倍数会变——变的不是「索引有用」这个结论，是具体倍数。 第三段单独测了代价：三万行、只差两个索引，插入慢了大约两三成。 这个数比「快 150 倍」重要，因为它说明索引不是白给的。 一个表上建五个索引，插入就要多维护五次 B 树。 还有个细节：唯一索引建的时候会先校验现有数据， 表里已经有重名就整个失败，索引不会建出来。 所以「先清数据、再建唯一索引」这个顺序不能反。

30 秒实验

1. 把 (name) 改成 (deptno, name)，跑 where deptno = 5 and name = 'x'，看计划。 能用上。再跑 where name = 'x'，用不上了——最左前缀的规矩。
2. 把索引改成 create index ... on t (UPPER(name))， 然后对比 where name = ? 和 where upper(name) = ? 的计划。 一个走 SCAN，一个走 SEARCH。
3. 把行数从两万加到二十万，重跑计时。 全扫的时间会涨十倍，走索引几乎不变——这就是「树高只多一层」的实测版。

常见误区

1. 「索引越多查询越快」。 写入变慢、占空间、优化器可能选错索引。 判断标准是「读多还是写多」，不是「能不能加」。
2. 「给所有列都建一个单列索引，总有一个能用上」。 多列条件时优化器往往只能用上一个，剩下的还是过滤。 该建的是复合索引，顺序按「最常用的等值列在前」。
3. 「区分度低的列建位图索引就行」。 位图索引适合低基数列，但它锁的粒度是「整个位图段」， 写多的表上会互相阻塞，只适合读多写少的分析型表。
4. 「加了索引就一定会用」。 在列上算东西（where sal * 12 > 12000）、 类型不匹配（数字列传字符串）、隐式转换，都会让索引失效。 验证办法只有一个：看执行计划。

下一篇讲视图：一句存起来的 SQL，查它等于跑那一句。 重点在「可更新视图」和 WITH CHECK OPTION——这两个是视图最容易出事的地方。

• ☐ （本篇完，下一篇 10.2 视图对象）

相关笔记

• 视图对象
• 创建数据表
• 数据表概述
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引