71、Oracle索引与分区：B-tree索引、位图索引、表分区、索引分区

各位同学，今天我们来聊聊Oracle里两个非常重要的概念——索引和分区。说实话，这两个东西单独拎出来都不算难，但把它们放在一起用，很多新手就容易懵了。我在项目中见过不少因为索引分区没设计好，导致查询从毫秒级直接掉到分钟级的惨案。嗯，咱们今天就把它彻底讲透。

一、B-tree索引：最常用的索引结构

B-tree索引，说白了就是Oracle默认的索引类型。你创建一个普通索引，默认就是B-tree。它的结构像一棵倒着长的树，根节点、分支节点、叶子节点。叶子节点里存的是索引列的值和对应的ROWID。

我个人习惯把B-tree索引想象成一本字典的目录。你想查一个字，先翻目录，找到页码，再翻到正文。B-tree索引也是这个道理——先通过索引找到ROWID，再通过ROWID去表里拿数据。

适用场景：

  • 高基数列（比如身份证号、订单号）
  • 等值查询和范围查询
  • 排序操作（ORDER BY）

举个例子，我在一个电商项目里，订单表有5000万行数据。按订单号查询，没索引的时候要全表扫描，大概40秒。加了个B-tree索引后，直接降到0.01秒。你想想看，这差距有多大。

-- 创建B-tree索引（默认就是B-tree）
CREATE INDEX idx_order_id ON orders(order_id);

-- 查看索引类型
SELECT index_name, index_type 
FROM user_indexes 
WHERE table_name = 'ORDERS';

小技巧：B-tree索引对NULL值不敏感。如果查询条件里经常用到IS NULL，可以考虑加一个函数索引，比如CREATE INDEX idx_null ON table(NVL(col, 0))。

二、位图索引：低基数列的利器

位图索引和B-tree完全不同。它用位图（bitmap）来表示每一行数据是否包含某个值。每个不同的值对应一个位图，位图里每一位代表一行数据。

为什么会用到位图索引？你想想看，如果一列只有几个值，比如性别（男/女）、状态（待支付/已支付/已取消），用B-tree索引其实效率不高。因为每个值对应的行数太多，索引扫描反而比全表扫描还慢。

我记得有一次帮一个金融客户优化报表查询。他们的交易状态列只有5个值，但表有2亿行。原来用的B-tree索引，查询某个状态的数据要跑3分钟。我改成位图索引后，同样的查询只要2秒。客户当场就愣住了。

-- 创建位图索引
CREATE BITMAP INDEX idx_status ON transactions(status);

-- 位图索引特别适合AND/OR组合查询
SELECT COUNT(*) 
FROM transactions 
WHERE status = '已支付' 
  AND channel = '线上';

注意：位图索引不适合高并发DML操作的表。因为一个位图索引条目可能锁住多行数据，并发更新时容易死锁。我曾经在一个OLTP系统里用过位图索引，结果生产环境频繁报锁等待，后来赶紧换掉了。

三、表分区：把大表拆成小表

表分区，说白了就是把一张大表物理上拆成多个小表，但逻辑上还是一张表。每个分区可以独立管理，比如单独备份、单独压缩、单独放在不同的表空间。

Oracle支持几种分区方式：

分区类型 说明 适用场景
范围分区 按列值的范围划分 时间字段（如按月份分区）
列表分区 按列值的离散值划分 地区、状态等枚举值
哈希分区 按哈希算法均匀分布 数据分布均匀，无明确分区键
复合分区 先按一种方式分区，再按另一种方式子分区 大型数据仓库

我个人最常用的是范围分区，特别是按时间分区。比如日志表，按月份分区，查询最近一个月的数据时，Oracle会自动只扫描对应的分区，其他分区碰都不碰。这就是所谓的「分区裁剪」。

-- 按月份范围分区
CREATE TABLE logs (
    log_id NUMBER,
    log_date DATE,
    message VARCHAR2(4000)
)
PARTITION BY RANGE (log_date) (
    PARTITION p_2024_01 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
    PARTITION p_2024_02 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
    PARTITION p_2024_03 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

分区的好处：

  • 查询性能提升（只扫描相关分区）
  • 管理方便（可以单独TRUNCATE某个分区）
  • 数据生命周期管理（旧分区可以归档或删除）

四、索引分区：让索引也分区

表分区了，索引怎么办？如果不做索引分区，索引还是全局的，那分区带来的性能优势就大打折扣了。索引分区有两种方式：

1. 局部索引（Local Index）

每个分区对应一个独立的索引。说白了就是「分区内的索引」。局部索引和表分区一一对应，每个分区只维护自己的索引。

-- 创建局部索引
CREATE INDEX idx_logs_date_local ON logs(log_date) LOCAL;

2. 全局索引（Global Index）

索引不分区，或者按另一种方式分区。全局索引覆盖所有分区数据，适合跨分区查询。

-- 创建全局索引
CREATE INDEX idx_logs_id_global ON logs(log_id) GLOBAL;

我在项目中遇到过一个问题：有个按时间分区的表，但业务经常按用户ID查询。如果只建局部索引，每次查询都要扫描所有分区，性能很差。后来我建了一个全局索引在用户ID上，查询速度直接起飞。

经验之谈：

  • 如果查询条件经常包含分区键，用局部索引就够了
  • 如果查询条件不包含分区键，考虑全局索引
  • 全局索引在分区维护操作（如TRUNCATE分区）后需要重建，局部索引不受影响

五、知识体系结构图

下面这张图帮你理清索引和分区的核心关系：

Oracle索引与分区知识体系 索引 B-tree索引 位图索引 高基数列、等值/范围查询 低基数列、OLAP/报表 分区 范围分区 列表分区 哈希分区 按时间/数值范围 按离散值 均匀分布 索引分区 局部索引 全局索引 与分区一一对应 跨分区查询

六、实战建议与避坑指南

最后，我总结几条实战经验，希望能帮你少走弯路：

  • 索引不是越多越好。每个索引都会增加DML操作的开销。我曾经在一个表上建了8个索引，结果插入数据慢得像蜗牛。后来删掉冗余索引，插入性能提升了10倍。
  • 分区键的选择很关键。尽量选查询条件里经常出现的字段。如果分区键和查询条件不匹配，分区就失去了意义。
  • 定期维护索引。B-tree索引在大量DML操作后会产生碎片，定期重建或整理索引能保持性能。我一般会在业务低峰期跑一个索引重建脚本。
  • 位图索引慎用。除非你确定表是只读的或者极少更新，否则别在生产环境的OLTP系统里用位图索引。我在一个项目里吃过这个亏，后来全改成B-tree了。

曾经踩过的坑：有一次我帮客户做分区表迁移，忘了重建全局索引。结果数据导进去后，跨分区查询直接走全表扫描，性能惨不忍睹。从那以后，每次做分区维护操作，我都会检查索引状态：SELECT index_name, status FROM user_indexes WHERE status != 'VALID';

好了，关于Oracle索引和分区的内容就讲到这里。这些知识在实际工作中非常实用，特别是处理大表时，用好索引和分区，性能能提升好几个数量级。希望今天的分享对你有帮助。

公众号:蓝海资料掘金营，微信deep3321

← 上一章 📖 返回目录 下一章 →