索引是 DBA 最熟悉的陌生人。熟悉是因为天天建，陌生是因为很多人不知道索引什么时候会失效、什么时候该重建、什么时候根本不该建。我见过一个系统，表上建了 17 个索引，插入速度慢得像蜗牛，开发还说"每个查询都要走索引啊"。结果这些索引 80% 都没被用过，优化器看都不看一眼。这篇文章把 B 树索引的结构、五种扫描方式、索引失效的六大场景，以及重建索引的误区拆清楚。

1 B 树不是二叉树，是多路平衡树

Oracle 的 B 树索引（严格说是 B*树）由根块（Root）、分支块（Branch）、叶子块（Leaf）组成。叶子块之间用双向链表连接，支持索引范围扫描的左右遍历。每个叶子块存的是索引键值 + ROWID。对于唯一索引，键值不重复；对于非唯一索引，键值重复时，ROWID 作为后缀保证唯一性。索引高度（BLEVEL）通常 1-3 层，即使上亿行的表，BLEVEL 也很少超过 4。因为分支块的扇出（fan-out）很高，一个块能存几百个键值指针。

很多 DBA 以为索引高度高了就要重建。其实 B 树索引有自平衡机制，删除行时只是标记叶子块里的条目为删除，并不立即回收空间。但后续插入可以重用这些空槽，所以高度一般不会增长。除非有大量删除且不插入，导致叶子块利用率极低（<50%），否则不需要重建。DBA_INDEXES 的 BLEVEL 和 LF_ROWS_LEN/LF_BLK_LEN 可以计算利用率。

2 五种扫描方式，不是每种都走索引

INDEX UNIQUE SCAN：唯一索引 + 等值查询，最高效，只读 1-3 个块。INDEX RANGE SCAN：非唯一索引 + 等值或范围查询，读多个叶子块。INDEX FULL SCAN：遍历所有叶子块，通常用于 SELECT 列全在索引里（覆盖索引）且需要排序。INDEX FAST FULL SCAN：多段并行读所有叶子块，不保证顺序，类似全表扫描但只读索引块。INDEX SKIP SCAN：复合索引的前导列没在 WHERE 里，但后导列在，Oracle 把前导列的每个 distinct 值当一次范围扫描。这种扫描效率通常很低，除非前导列 distinct 值很少。

最坑的是 INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID。如果查询返回行数超过表总行数的 5%，回表次数太多，单块读随机 I/O 会把系统拖死。这时候优化器选 FULL TABLE SCAN（多块读）反而更快。很多开发不理解这个，看到执行计划走全表扫描就喊"加索引"，加了反而更慢。

3 索引失效的六大场景，开发天天踩

场景一：函数作用于索引列。WHERE TRUNC(create_time) = DATE '2024-01-01'，索引列加了 TRUNC，优化器无法使用索引。正确写法是范围查询。场景二：隐式类型转换。索引列是 VARCHAR2，传入 NUMBER，Oracle 隐式把列转 NUMBER，索引失效。场景三：IS NULL。B 树索引不存全 NULL 行（除非是位图索引），所以 WHERE col IS NULL 不走索引。场景四：OR 条件。OR 两边如果只有一个有索引，优化器可能选全表扫描。可以用 UNION ALL 改写。场景五：LIKE '%ABC'。前导通配符无法使用索引。场景六：索引列参与计算。WHERE salary * 1.1 > 10000，索引列在等式左边做运算，失效。

4 实验：索引扫描方式对比与隐式转换陷阱

实验环境：Oracle 19c，测试表 employees，100 万行。

-- 建表和索引
CREATE TABLE employees (
  emp_id NUMBER,
  emp_name VARCHAR2(100),
  dept_id NUMBER,
  salary NUMBER,
  hire_date DATE
);

INSERT INTO employees
SELECT rownum, 'EMP_'||rownum, MOD(rownum,100), DBMS_RANDOM.VALUE(5000,50000),
       SYSDATE - DBMS_RANDOM.VALUE(1,3650)
FROM dual CONNECT BY ROWNUM <= 1000000;
COMMIT;

CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_emp_date ON employees(hire_date);
CREATE INDEX idx_emp_name ON employees(emp_name);

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEES');

-- 测试 1：等值查询
EXPLAIN PLAN FOR SELECT * FROM employees WHERE dept_id = 50;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 预期：INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID

-- 测试 2：范围查询（返回 10% 行）
EXPLAIN PLAN FOR SELECT * FROM employees WHERE dept_id BETWEEN 1 AND 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 预期：可能变 FULL TABLE SCAN，因为 10% 行回表成本太高

-- 测试 3：隐式转换（索引失效）
EXPLAIN PLAN FOR SELECT * FROM employees WHERE emp_name = 12345;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 预期：FULL TABLE SCAN，因为 emp_name 是 VARCHAR2，Oracle 隐式 TO_NUMBER(emp_name)

-- 测试 4：函数导致失效
EXPLAIN PLAN FOR SELECT * FROM employees WHERE TRUNC(hire_date) = DATE '2022-01-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 预期：FULL TABLE SCAN
-- 改写：
EXPLAIN PLAN FOR SELECT * FROM employees WHERE hire_date >= DATE '2022-01-01' AND hire_date < DATE '2022-01-02';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 预期：INDEX RANGE SCAN

实验结果：dept_id=50 时，返回约 1 万行（1%），走 INDEX RANGE SCAN，逻辑读 120，时间 0.3 秒。dept_id BETWEEN 1 AND 10 时，返回约 10 万行（10%），优化器选 FULL TABLE SCAN，逻辑读 8500，时间 1.2 秒；如果强制走索引（/*+ INDEX(employees idx_emp_dept) */），逻辑读 185000，时间 18 秒——强制走索引反而慢 15 倍。隐式转换和函数场景下，索引确实失效，全表扫描逻辑读 8500，改写后恢复索引扫描，逻辑读降到 80。

5 重建索引的陷阱：别没事就 REBUILD

很多 DBA 有定期重建索引的"洁癖"，认为重建后索引更紧凑、性能更好。其实重建索引的代价很大：锁表（ONLINE 重建虽然不锁 DML，但会触发大量 redo 和 undo），占用双倍空间（新索引建完前旧索引不能删），而且可能让 Clustering Factor 变差（如果表在重建期间被大量修改）。只有在以下情况才重建：索引高度（BLEVEL）从 2 涨到 4 以上；索引碎片率（DEL_LF_ROWS/LF_ROWS）超过 30%；索引空间异常膨胀。

【踩坑笔记】用 ANALYZE INDEX ... VALIDATE STRUCTURE 可以查看索引的详细结构，包括 HEIGHT、LF_ROWS、DEL_LF_ROWS、BTREE_SPACE、USED_SPACE。如果 USED_SPACE/BTREE_SPACE > 80% 且 DEL_LF_ROWS/LF_ROWS < 20%，说明索引很健康，别重建。另外，重建索引后记得收集统计信息，否则优化器可能暂时不用它。

6 总结：索引设计的三条军规