Oracle 常见索引有哪些？B 树索引和位图索引怎么选？

简化版

Oracle 最常用的是 B 树索引，适合高选择性字段、等值查询、范围查询和排序。位图索引适合低基数字段和数据仓库场景，比如性别、状态、地区等枚举值，但不适合高并发 OLTP 频繁更新表。索引选择要看查询条件、字段选择性、数据量、DML 频率和组合条件。

详细版

Oracle 常见索引包括：

B 树索引：默认索引，OLTP 最常用；
唯一索引：保证唯一性，常配合唯一约束；
组合索引：多个列共同参与过滤或排序；
函数索引：对表达式或函数结果建索引；
位图索引：适合低基数字段和分析查询；
分区索引：配合分区表使用；
反向键索引：缓解递增键插入热点。

位图索引不是“更高级的索引”，它适合读多写少的分析场景。高并发更新下，位图索引可能导致锁竞争和维护成本变高。

完整版教学

一、B 树索引是 Oracle 默认选择

创建普通索引：

CREATE INDEX idx_users_email ON users(email);

B 树索引适合：

主键、唯一键；
高选择性字段；
等值查询；
范围查询；
排序和分组辅助；
OLTP 高频点查。

比如手机号、订单号、用户 ID、创建时间这类字段，通常优先考虑 B 树索引。

二、选择性决定索引收益

选择性可以理解为“这个条件能过滤掉多少数据”。如果字段值很分散，索引收益通常更大。

例如用户表的手机号几乎唯一：

WHERE phone = '13800000000'

索引可以快速定位少量行。

如果字段只有男女两个值：

WHERE gender = 'M'

命中数据可能占半张表。对于 OLTP 查询，普通 B 树索引未必比全表扫描更划算。

三、组合索引要匹配查询方式

组合索引：

CREATE INDEX idx_order_user_time ON orders(user_id, created_at);

适合：

WHERE user_id = :userId
  AND created_at >= :beginTime
ORDER BY created_at

组合索引不是把所有字段随便堆进去。要考虑：

高频查询条件；
等值列和范围列顺序；
排序字段；
字段选择性；
索引是否过宽；
DML 维护成本。

四、函数索引用于表达式查询

如果 SQL 经常这样写：

WHERE UPPER(username) = 'TOM'

普通 username 索引可能无法直接发挥作用，可以考虑函数索引：

CREATE INDEX idx_users_upper_name ON users(UPPER(username));

但函数索引要求查询表达式和索引表达式能匹配，且函数结果要稳定。滥用函数索引会增加维护成本。

五、位图索引适合数据仓库

位图索引用 bitmap 表示某个值对应哪些行。它适合低基数字段，比如：

性别；
订单状态；
是否有效；
地区编码；
商品类别。

在数据仓库中，经常有多个低基数字段组合过滤：

WHERE gender = 'F'
  AND status = 'ACTIVE'
  AND region = 'BJ'

位图之间可以高效做 AND、OR 运算。

但在 OLTP 高频更新表中，位图索引维护成本和锁竞争都可能很明显，所以要谨慎。

六、常见误区与追问

索引类型	典型场景	高风险用法
B 树索引	OLTP 点查、范围、排序	低选择性字段单独建索引收益低
函数索引	`UPPER(col)`、表达式过滤	SQL 表达式不匹配就用不上
位图索引	数据仓库低基数字段组合过滤	高频 UPDATE/DELETE 的交易表

记忆钩子：Oracle 索引不是“B 树 vs 位图谁高级”，而是“OLTP 多用 B 树，数据仓库低基数组合过滤才考虑位图”。

举个数字例子：1000 万行订单里 status 只有 5 个值，单查 status='PAID' 可能命中 300 万行，B 树索引回表成本很高；但数据仓库里同时过滤 status='PAID'、region='BJ'、channel='APP'，多个位图可以做 AND 运算，把候选集快速缩小。若这张表每秒更新 3000 笔状态，位图索引维护和锁竞争就可能成为灾难。

误区：低基数字段一定不能建索引。 OLTP 单列 B 树收益可能低，但数据仓库里的低基数字段适合位图索引组合过滤。
误区：位图索引比 B 树索引更先进。 二者面向场景不同，位图索引在频繁 DML 表上代价很高。
误区：函数索引建了就所有函数查询都能用。 查询表达式要和索引表达式匹配，函数结果也应稳定。
追问：组合索引列顺序怎么考虑？ 要结合等值条件、范围条件、排序、选择性和最常见 SQL，不是把所有字段堆进去。
追问：反向键索引解决什么问题？ 它可缓解递增键索引右端热点，但不适合依赖范围扫描的查询。
追问：索引过多有什么代价？ 每次 INSERT、UPDATE、DELETE 都要维护索引，还会占用空间和缓存，优化器选择也更复杂。

七、加强记忆

Oracle 索引选择先想 B 树：高选择性、点查、范围、排序都靠它。位图索引记住“低基数、读多写少、数据仓库”，不要放到高并发频繁更新的核心交易表上。索引不是越多越好，收益来自查询过滤，代价体现在写入维护。

简化版

详细版