Oracle 索引与优化器

Oracle 索引与优化器的重点不是背“B-tree 快、Bitmap 慢”，而是理解：

Oracle 怎么通过统计信息估算行数和成本，为什么通过索引拿到 ROWID 后还要访问表块，为什么同一条 SQL 可能因为数据分布、绑定变量、统计信息变化而走完全不同的计划。

学习目标

学完这一页，你要能掌握：

B-tree、Bitmap、函数索引、组合索引分别适合什么场景。
ROWID 为什么是 Oracle 索引定位表行的关键。
INDEX RANGE SCAN、INDEX UNIQUE SCAN、TABLE ACCESS FULL、TABLE ACCESS BY INDEX ROWID 分别代表什么。
优化器为什么依赖统计信息、直方图和基数估算。
绑定变量为什么能减少硬解析，又为什么可能带来计划不适配。
商业 OLTP 系统如何给资产、订单、报表查询设计索引。

索引的本质

没有索引时，Oracle 需要扫描表块，然后逐行判断条件。

mermaid

flowchart TD
    A["没有合适索引"] --> B["读取大量表块"]
    B --> C["逐行判断 where 条件"]
    C --> D["过滤结果"]
    D --> E["排序或 Join"]

有索引时，Oracle 先在索引结构中定位 key，再通过 ROWID 找表行。

mermaid

flowchart TD
    A["where asset_no = :assetNo"] --> B["访问 B-tree 索引"]
    B --> C["Root Block"]
    C --> D["Branch Block"]
    D --> E["Leaf Block<br/>key + ROWID"]
    E --> F["TABLE ACCESS BY INDEX ROWID"]
    F --> G["读取表块中的完整行"]

索引能减少读块数量，但也带来写入维护成本。更新索引列时，索引也要变；索引越多，DML 成本越高。

常见索引类型

索引	适合场景	不适合场景
B-tree Index	高选择性字段、范围查询、排序、OLTP 主力索引	低基数字段大范围统计不一定最佳
Bitmap Index	低基数字段、数据仓库、少更新多统计	高并发 OLTP 高频更新
Function-based Index	`upper(name)`、`trunc(date)` 等表达式查询	表达式不稳定或 SQL 写法不统一
Composite Index	多条件过滤、排序	列顺序错误会收益很低
Unique Index	业务唯一性和精确定位	非唯一业务不能强加

B-tree 索引和 ROWID

Oracle B-tree 索引通常由 root、branch、leaf block 组成。leaf block 保存 key 和 ROWID。

mermaid

flowchart TD
    A["Root Block"] --> B["Branch Block"]
    B --> C["Leaf Block"]
    C --> D["Key"]
    C --> E["ROWID"]
    E --> F["Data Block"]
    F --> G["Row"]

ROWID 可以理解为表中行的物理定位信息。通过 ROWID，Oracle 可以快速找到对应数据块和行位置。

常见计划：

text

INDEX UNIQUE SCAN UK_ASSET_NO
TABLE ACCESS BY INDEX ROWID MEDICAL_ASSET

含义是：先通过唯一索引找到 ROWID，再回表拿完整列。

组合索引怎么设计

医疗资产列表页：

sql

select id, asset_no, asset_name, status
from medical_asset
where hospital_id = :hospital_id
  and status = :status
order by created_at desc
fetch first 20 rows only;

Bitmap 索引为什么慎用于 OLTP

Bitmap 索引适合低基数字段，例如性别、是否有效、少量状态。

mermaid

flowchart TD
    A["status 字段只有少量值"] --> B["Bitmap 保存值到行集合的位图"]
    B --> C["多个条件可做位运算"]
    C --> D["数据仓库统计很快"]

但在 OLTP 高频更新中，它的问题是：

一个 Bitmap 片段可能对应多行。
更新一行可能影响一段位图。
并发更新不同记录也可能争用相同位图片段。
锁冲突和维护成本比普通 B-tree 更明显。

所以：

场景	建议
交易表状态频繁变化	优先 B-tree
报表库、数仓表、低更新	可以考虑 Bitmap
低基数字段但高并发更新	不要只因“基数低”就建 Bitmap

函数索引

如果 SQL 对列做函数，普通索引可能用不上。

sql

select id, asset_no
from medical_asset
where upper(asset_no) = upper(:asset_no);

可以建立函数索引：

sql

create index idx_asset_no_upper
on medical_asset(upper(asset_no));

注意：

SQL 表达式必须和索引表达式匹配。
函数索引也要维护，写入有成本。
如果可以在入库时统一大小写，可能比查询时函数处理更简单。

优化器怎么选计划

Oracle 成本优化器不是凭感觉选计划，而是根据对象统计信息估算不同路径成本。

mermaid

flowchart TD
    A["SQL 文本"] --> B["解析和语义检查"]
    B --> C["查询转换"]
    C --> D["读取表、列、索引统计信息"]
    D --> E["估算选择率和基数"]
    E --> F["枚举访问路径和 Join 顺序"]
    F --> G["计算成本"]
    G --> H["选择成本较低计划"]

统计信息包括：

信息	作用
表行数	判断全表扫描成本
块数	判断需要读多少数据块
列 NDV	估算等值条件选择率
直方图	处理数据倾斜
索引层级和叶子块	估算索引访问成本
聚簇因子	判断索引顺序和表块分布是否接近

收集统计信息：

sql

begin
  dbms_stats.gather_table_stats(
    ownname => user,
    tabname => 'MEDICAL_ASSET',
    cascade => true,
    method_opt => 'for all columns size auto'
  );
end;
/

执行计划怎么看

sql

explain plan for
select id, asset_no, asset_name
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc;

select * from table(dbms_xplan.display);

真实执行后的计划更有价值：

sql

select * from table(
  dbms_xplan.display_cursor(null, null, 'allstats last')
);

常见节点：

节点	含义	排查重点
`TABLE ACCESS FULL`	全表扫描	大表高频 SQL 是否不可接受
`INDEX UNIQUE SCAN`	唯一索引精确定位	通常很好
`INDEX RANGE SCAN`	索引范围扫描	范围过大仍慢
`TABLE ACCESS BY INDEX ROWID`	根据 ROWID 回表	回表次数太多会慢
`NESTED LOOPS`	嵌套循环	外层行数大时风险高
`HASH JOIN`	哈希连接	PGA/TEMP 压力
`SORT ORDER BY`	排序	是否可用索引避免排序

绑定变量与计划稳定性

绑定变量好处：

sql

select id, asset_no
from medical_asset
where hospital_id = :hospital_id
  and status = :status;

好处	说明
SQL 文本稳定	更容易复用游标
减少硬解析	降低 CPU 和 Shared Pool 压力
降低注入风险	参数和 SQL 结构分离

但如果数据倾斜明显，比如大医院有 1000 万资产，小医院只有 100 条，复用同一个计划可能不适合所有参数。处理方向包括统计信息、直方图、SQL Plan Baseline、拆 SQL、必要时提示或重写。

商业项目索引设计

场景	查询特点	索引建议
资产详情	`asset_no = ?`	唯一 B-tree
医院资产列表	`hospital_id + status + created_at`	组合 B-tree
审批待办	`assignee + status + created_at`	组合 B-tree
低更新报表状态统计	状态低基数、大量聚合	数仓可考虑 Bitmap
大表按日期归档	时间范围扫描	分区 + 本地索引
大小写不敏感查询	`upper(code)`	函数索引或规范化字段

可运行 Demo

sql

create table medical_asset (
  id number primary key,
  asset_no varchar2(64) not null,
  hospital_id number not null,
  status varchar2(20) not null,
  asset_name varchar2(128) not null,
  created_at date default sysdate not null,
  constraint uk_medical_asset_no unique(asset_no),
  constraint ck_medical_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create sequence seq_medical_asset start with 1 increment by 1;

create index idx_asset_hospital_status_time
on medical_asset(hospital_id, status, created_at);

insert into medical_asset(id, asset_no, hospital_id, status, asset_name)
values(seq_medical_asset.nextval, 'A001', 1001, 'USED', 'CT-001');

commit;

explain plan for
select id, asset_no, asset_name
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc;

select * from table(dbms_xplan.display);

观察：

是否使用 idx_asset_hospital_status_time。
是否出现 TABLE ACCESS BY INDEX ROWID。
如果返回列很多，回表是否可能成为瓶颈。

常见坑

坑	后果	正确做法
只看有没有索引	回表多仍然慢	看执行计划和实际行数
高频更新字段建 Bitmap	锁竞争严重	OLTP 优先 B-tree
函数包裹索引列	普通索引失效	函数索引或字段规范化
统计信息过期	计划选错	定期收集统计信息
绑定变量一律认为好	数据倾斜下计划不适配	结合直方图和计划管理
组合索引列顺序随便放	过滤和排序无法充分利用	按高频 SQL 设计

面试标准回答

text

Oracle 常见索引有 B-tree、Bitmap、函数索引和组合索引。B-tree 索引由 root、branch、leaf block 组成，leaf block 保存 key 和 ROWID，查询时先通过索引定位 ROWID，再访问表数据块。Bitmap 索引适合低基数字段和少更新的数据仓库统计，不适合高并发 OLTP 频繁更新。Oracle 优化器基于统计信息、选择率、基数和成本选择执行计划，统计信息不准、数据倾斜、绑定变量复用不合适都可能导致计划错误。排查时要看执行计划中的访问路径、回表、Join、排序和估算行数。

追问：

ROWID 在索引访问里起什么作用？
Bitmap 索引为什么不适合 OLTP？
绑定变量为什么既能优化又可能带来问题？
统计信息不准会导致什么执行计划问题？

关联知识点

知识点	说明
Oracle 从零到生产级掌握	主学习线
核心全过程原理	SQL 执行、解析、内存、事务全链路
事务与锁	锁、一致性读、Undo/Redo
性能排查	等待事件、AWR/ASH、慢 SQL

JDK 8/11/17/21 LTS演进

线程池

Oracle 索引与优化器 ​

学习目标 ​

索引的本质 ​

常见索引类型 ​

B-tree 索引和 ROWID ​

组合索引怎么设计 ​

Bitmap 索引为什么慎用于 OLTP ​

函数索引 ​

优化器怎么选计划 ​

执行计划怎么看 ​

绑定变量与计划稳定性 ​

商业项目索引设计 ​

可运行 Demo ​

常见坑 ​

面试标准回答 ​

关联知识点 ​

Oracle 索引与优化器

学习目标

索引的本质

常见索引类型

B-tree 索引和 ROWID

组合索引怎么设计

Bitmap 索引为什么慎用于 OLTP

函数索引

优化器怎么选计划

执行计划怎么看

绑定变量与计划稳定性

商业项目索引设计

可运行 Demo

常见坑

面试标准回答

关联知识点