第五章：索引原理与应用

文档信息

适用版本：Oracle 12c / 18c / 19c / 21c
阅读时间：约 50 分钟
前置知识：第四章数据库与表操作

5.1 索引概述

5.1.1 索引工作原理

┌─────────────────────────────────────────────────────────────────┐
│                      B-Tree 索引结构                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│                         [ROOT]                                   │
│                        /      \                                  │
│                    [BRANCH]   [BRANCH]                           │
│                    /    \      /    \                            │
│                 [LEAF] [LEAF] [LEAF] [LEAF]                     │
│                    │      │      │      │                        │
│                 ────────────────────────────────────             │
│                    │      │      │      │                        │
│                 ┌─────┬─────┬─────┬─────┬─────┐                │
│                 │ 10  │ 20  │ 30  │ 40  │ 50  │ ← 键值         │
│                 │ ROW │ ROW │ ROW │ ROW │ ROW │ ← 行指针       │
│                 └─────┴─────┴─────┴─────┴─────┘                │
│                                                                 │
│  索引查找流程：                                                  │
│  1. 从 ROOT 开始查找                                            │
│  2. 根据 BRANCH 判断目标范围                                    │
│  3. 定位到 LEAF 节点                                            │
│  4. 通过 ROWID 直接访问数据行                                    │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

5.1.2 索引 vs 全表扫描

特性	索引扫描	全表扫描
适用场景	高选择性列	低选择性列
I/O 次数	少量 I/O	大量 I/O
结果顺序	按索引顺序	无序
排序	自动有序	可能需额外排序
回表	可能需要	直接访问

5.2 B-Tree 索引

5.2.1 B-Tree 结构详解

-- 创建 B-Tree 索引
CREATE INDEX idx_emp_name ON employees(last_name, first_name);

-- 查看索引结构
SELECT 
    index_name,
    index_type,
    table_name,
    uniqueness,
    compression,
    leaf_blocks,
    distinct_keys,
    clustering_factor
FROM user_indexes
WHERE index_name = 'IDX_EMP_NAME';

-- 查看索引列
SELECT 
    index_name,
    column_name,
    column_position,
    descend
FROM user_ind_columns
WHERE index_name = 'IDX_EMP_NAME'
ORDER BY column_position;

5.2.2 复合索引设计原则

复合索引列顺序原则：

1. 等值查询列放在前面
   ✅ CREATE INDEX idx ON table(a, b) WHERE a=? AND b=?
   ❌ CREATE INDEX idx ON table(b, a)

2. 高选择性列放前面
   ✅ 性别(低) + 身份证号(高) → 列顺序: 身份证号, 性别

3. 考虑覆盖索引
   ✅ SELECT name, age FROM table WHERE name='张三';
   ✅ CREATE INDEX idx ON table(name) INCLUDE (age); -- 覆盖索引

复合索引使用规则：
┌──────────────────────────────────────────────────┐
│ 查询条件              │ 索引使用情况              │
├──────────────────────────────────────────────────┤
│ WHERE a=? AND b=?    │ ✅ 完全使用 (a,b)         │
│ WHERE a=?            │ ✅ 使用前导列 (a)          │
│ WHERE b=?            │ ❌ 无法使用 (b不是前导列)   │
│ WHERE b=? AND a=?    │ ✅ 优化器重排使用 (a,b)    │
└──────────────────────────────────────────────────┘

5.3 Bitmap 索引

5.3.1 Bitmap 原理

┌─────────────────────────────────────────────────────────────────┐
│                      Bitmap 索引原理                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  示例：employees 表                                             │
│  ─────────────────────────────                                  │
│  EMP_ID  GENDER  STATUS                                        │
│  ──────  ──────  ──────                                        │
│    1001    M      ACTIVE                                        │
│    1002    F      INACTIVE                                      │
│    1003    M      ACTIVE                                         │
│    1004    F      ACTIVE                                         │
│                                                                 │
│  Bitmap 索引结构：                                               │
│  ─────────────────                                              │
│  GENDER='M'  →  1010  (1001,1003 为 M)                         │
│  GENDER='F'  →  0101  (1002,1004 为 F)                         │
│                                                                 │
│  STATUS='ACTIVE'  →  1011  (1001,1003,1004 活跃)               │
│                                                                 │
│  Bitmap 位运算：                                                 │
│  ────────────                                                   │
│  M + ACTIVE  →  1010 AND 1011 = 1010  →  1001,1003             │
│  F + ACTIVE  →  0101 AND 1011 = 0001  →  1004                  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

5.3.2 Bitmap 索引应用场景

-- 创建 Bitmap 索引
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);
CREATE BITMAP INDEX idx_emp_dept ON employees(department_id);

-- 适合场景：
-- 1. 低基数列（枚举值少）
-- 2. 多个 Bitmap 索引组合查询
-- 3. 数据仓库 / OLAP 系统
-- 4. 只读或极少更新表

-- 不适合场景：
-- 1. 高基数列（唯一值多）
-- 2. OLTP 频繁更新
-- 3. 并发写入多的表

5.3.3 Bitmap Join 索引

-- 预计算连接结果
CREATE BITMAP INDEX idx_sales_cust_name
ON sales(customers.customer_name)
FROM sales, customers
WHERE sales.customer_id = customers.customer_id;

5.4 函数索引与表达式索引

5.4.1 函数索引

-- 大小写不敏感查询
CREATE INDEX idx_emp_upper_email ON employees(UPPER(email));

-- 查询可使用索引
SELECT * FROM employees WHERE UPPER(email) = 'JOHN.DOE@EXAMPLE.COM';

-- 计算列索引
CREATE INDEX idx_emp_annual_sal ON employees(salary * 12);

-- 日期函数索引
CREATE INDEX idx_emp_hire_year ON employees(EXTRACT(YEAR FROM hire_date));

-- 自定义函数索引
CREATE INDEX idx_emp_name_reverse ON employees(REVERSE(last_name));

-- FBI 使用示例
SELECT * 
FROM employees 
WHERE LOWER(last_name) = 'smith' 
AND hire_date >= TO_DATE('2020-01-01', 'YYYY-MM-DD');

5.5 索引监控与诊断

5.5.1 监控索引使用

-- 开启索引监控
ALTER INDEX idx_emp_name MONITORING USAGE;

-- 执行查询
SELECT * FROM employees WHERE last_name = 'Smith';

-- 检查使用情况
SELECT 
    index_name,
    table_name,
    monitoring,
    used,
    start_monitoring,
    end_monitoring
FROM v$object_usage
WHERE index_name = 'IDX_EMP_NAME';

-- 关闭监控
ALTER INDEX idx_emp_name NOMONITORING USAGE;

5.5.2 查看索引效率

-- 查看索引统计信息
SELECT 
    index_name,
    blevel,                    -- B-Tree 层数
    leaf_blocks,               -- 叶子块数
    distinct_keys,             -- 唯一键数
    avg_leaf_blocks_per_key,   -- 每键叶子块数
    avg_data_blocks_per_key,  -- 每键数据块数
    clustering_factor,         -- 聚簇因子
    num_rows
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- Clustering Factor 说明：
-- 接近表块数：数据有序，索引效率高
-- 接近行数：数据无序，需大量 I/O

5.5.3 识别无效/不可用索引

-- 查看索引状态
SELECT 
    owner,
    index_name,
    index_type,
    status,
    num_rows,
    last_analyzed
FROM dba_indexes
WHERE status NOT IN ('VALID', 'N/A')
OR last_analyzed IS NULL;

-- 状态说明：
-- VALID: 正常
-- UNUSABLE: 不可用，需要重建
-- N/A: 分区索引未使用特定分区

-- 重建不可用索引
ALTER INDEX idx_name REBUILD;
ALTER INDEX idx_name REBUILD ONLINE;

-- 检查分区索引状态
SELECT 
    index_name,
    partition_name,
    status
FROM dba_ind_partitions
WHERE status != 'USABLE';

5.6 索引优化策略

5.6.1 何时创建索引

┌─────────────────────────────────────────────────────────────────┐
│                      索引创建决策树                               │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  列的唯一值比例 > 95% ?                                         │
│      ↓ 是                                                       │
│  ✅ 创建 B-Tree 索引                                            │
│                                                                 │
│  列的唯一值比例 5-95% + 多列组合查询 ?                           │
│      ↓ 是                                                       │
│  ✅ 创建复合 B-Tree 索引                                        │
│                                                                 │
│  列的唯一值比例 < 5% + 只读表 ?                                 │
│      ↓ 是                                                       │
│  ✅ 创建 Bitmap 索引                                            │
│                                                                 │
│  WHERE/ORDER BY 使用函数或表达式 ?                               │
│      ↓ 是                                                       │
│  ✅ 创建函数索引                                                │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

5.6.2 索引优化技巧

-- 1. 覆盖索引（避免回表）
-- 查询涉及列都在索引中
CREATE INDEX idx_emp_covering ON employees(department_id, salary) 
INCLUDE (first_name, last_name);  -- 覆盖列

-- 2. 分区索引
-- 本地索引：与分区一致，便于分区操作
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 全局索引：跨分区
CREATE INDEX idx_orders_date ON orders(order_date) GLOBAL;

-- 3. 压缩索引
CREATE INDEX idx_emp_name ON employees(last_name) COMPRESS 1;

-- 4. 不可见索引
CREATE INDEX idx_emp_new ON employees(manager_id) INVISIBLE;
ALTER INDEX idx_emp_new VISIBLE;  -- 启用
ALTER INDEX idx_emp_new INVISIBLE; -- 禁用

-- 5. 虚拟列索引
ALTER TABLE employees ADD (full_name AS (first_name || ' ' || last_name));
CREATE INDEX idx_emp_fullname ON employees(full_name);

5.7 执行计划分析

5.7.1 读取执行计划

-- 方法1: EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'Smith';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 方法2: AUTOTRACE
SET AUTOTRACE ON;
SELECT * FROM employees WHERE last_name = 'Smith';
SET AUTOTRACE OFF;

-- 方法3: DBMS_XPLAN
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

5.7.2 索引扫描类型

操作	说明	提示
INDEX UNIQUE SCAN	唯一索引扫描	返回 0-1 行
INDEX RANGE SCAN	范围扫描	返回多行
INDEX FULL SCAN	索引全扫描	有序返回
INDEX FAST FULL SCAN	索引快速全扫描	无序，多块读
INDEX SKIP SCAN	跳跃扫描	复合索引前导列未使用
INDEX JOIN SCAN	索引连接	多索引合并

5.7.3 优化案例

-- 问题查询
SELECT employee_id, first_name, salary
FROM employees
WHERE TO_CHAR(hire_date, 'YYYY') = '2024';

-- 优化：使用函数索引
CREATE INDEX idx_hire_year ON employees(EXTRACT(YEAR FROM hire_date));

-- 优化后查询
SELECT employee_id, first_name, salary
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2024;

-- 问题查询
SELECT * FROM orders WHERE status != 'CANCELLED';

-- 优化建议：如果是低选择性的排除查询，考虑分区
CREATE BITMAP INDEX idx_order_status ON orders(status);

5.8 本章小结

✅ B-Tree 索引：默认索引，适合高选择性列
✅ Bitmap 索引：适合低基数列，多列组合查询
✅ 函数索引：优化函数查询
✅ 复合索引：遵循最左前缀原则设计
✅ 索引监控：定期检查索引使用情况
✅ 执行计划：分析索引效率，指导优化

📖 下章预告：视图与存储过程

下一步：学习第六章：视图与存储过程