【Oracle】索引相关

于 2025-06-02 13:00:00 发布
阅读量827 收藏 12
点赞数 24
CC 4.0 BY-SA版权
分类专栏： Oracle 文章标签： oracle 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Go_ahead1025/article/details/148370361

在这里插入图片描述

个人主页：Guiat
归属专栏：Oracle

在这里插入图片描述

文章目录

正文

1. 索引基础概述

索引是Oracle数据库中提高查询性能的关键结构，它类似于书籍的目录，帮助数据库快速定位和访问数据。

1.1 索引的作用与价值

  • 加速数据检索操作
  • 减少I/O操作和CPU使用
  • 提高排序和分组效率
  • 强制实施唯一性约束
  • 优化连接操作

1.2 索引的工作原理
是
否
查询处理
索引存在?
索引扫描
全表扫描
通过ROWID访问表
读取所有数据块
返回结果

1.3 索引的存储结构

Oracle索引在物理上是独立于表数据的单独段，它们存储在自己的数据块中。
索引
索引段
索引区
索引块
索引条目
键值 + ROWID

1.4 索引与表的关系
ROWID
ROWID
ROWID
ROWID
表
行1
行2
行3
行4
索引
条目1
条目2
条目3
条目4

2. Oracle索引类型

Oracle提供了多种索引类型，每种类型都有其特定的用途和优势。

2.1 B树索引(B-Tree Index)

B树索引是Oracle中最常用的索引类型，适用于大多数场景。

2.1.1 B树索引结构
根节点
分支节点1
分支节点2
叶节点1
叶节点2
叶节点3
叶节点4

2.1.2 创建B树索引

-- 创建基本B树索引
CREATE INDEX emp_ename_idx ON employees(employee_name);

-- 创建唯一索引
CREATE UNIQUE INDEX emp_id_idx ON employees(employee_id);

-- 创建复合索引
CREATE INDEX emp_dept_job_idx ON employees(department_id, job_id);

-- 创建带表空间的索引
CREATE INDEX emp_salary_idx ON employees(salary)
TABLESPACE indexes
STORAGE (INITIAL 1M NEXT 1M);

-- 创建并行索引
CREATE INDEX emp_hire_idx ON employees(hire_date)
PARALLEL 4;

-- 创建带函数的索引
CREATE INDEX emp_upper_name_idx ON employees(UPPER(employee_name));

2.2 位图索引(Bitmap Index)

位图索引适用于基数低（唯一值较少）的列，如性别、状态等。

2.2.1 位图索引结构
位图索引
MALE (位图)
FEMALE (位图)
10010...
01101...

2.2.2 创建位图索引

-- 创建基本位图索引
CREATE BITMAP INDEX emp_gender_idx ON employees(gender);

-- 创建复合位图索引
CREATE BITMAP INDEX emp_dept_status_idx 
ON employees(department_id, status);

-- 创建带压缩的位图索引
CREATE BITMAP INDEX emp_region_idx 
ON employees(region_id)
COMPRESS 2;

2.3 函数索引(Function-Based Index)

函数索引基于列的表达式或函数创建，允许优化带有函数的查询。

2.3.1 函数索引应用场景
函数索引应用场景
大小写不敏感查询
日期计算
数学计算
条件表达式
自定义函数
UPPER(name)
TRUNC(date)
ROUND(salary/1000)
CASE WHEN...
my_package.function()

2.3.2 创建函数索引

-- 创建大小写不敏感索引
CREATE INDEX emp_upper_name_idx ON employees(UPPER(employee_name));

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

-- 创建数学计算索引
CREATE INDEX emp_annual_salary_idx ON employees(salary * 12);

-- 创建条件表达式索引
CREATE INDEX emp_salary_category_idx ON employees(
    CASE 
        WHEN salary < 5000 THEN 'LOW'
        WHEN salary BETWEEN 5000 AND 10000 THEN 'MEDIUM'
        ELSE 'HIGH'
    END
);

2.4 分区索引(Partitioned Index)

分区索引与分区表配合使用，提高大表的性能和可管理性。

2.4.1 分区索引类型
分区索引类型
本地分区索引
全局分区索引
与表使用相同分区方案
分区独立维护
分区自动对齐
使用独立分区方案
跨表分区查询
维护成本较高

2.4.2 创建分区索引

-- 假设有一个按日期范围分区的表
CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER,
    customer_id NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY')),
    PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('01-OCT-2023', 'DD-MON-YYYY')),
    PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
);

-- 创建本地分区索引
CREATE INDEX sales_date_idx ON sales(sale_date) LOCAL;

-- 创建带命名的本地分区索引
CREATE INDEX sales_customer_idx ON sales(customer_id) LOCAL (
    PARTITION sales_cust_q1_2023,
    PARTITION sales_cust_q2_2023,
    PARTITION sales_cust_q3_2023,
    PARTITION sales_cust_q4_2023
);

-- 创建全局分区索引
CREATE INDEX sales_amount_idx ON sales(amount)
GLOBAL PARTITION BY RANGE (amount) (
    PARTITION sales_small VALUES LESS THAN (1000),
    PARTITION sales_medium VALUES LESS THAN (5000),
    PARTITION sales_large VALUES LESS THAN (10000),
    PARTITION sales_xlarge VALUES LESS THAN (MAXVALUE)
);

2.5 反向键索引(Reverse Key Index)

反向键索引通过反转键值的字节顺序来减轻索引热点问题。

2.5.1 反向键索引工作原理
原始键值
反转过程
反转后键值
1234
反转
4321
5678
反转
8765

2.5.2 创建反向键索引

-- 创建反向键索引
CREATE INDEX emp_id_reverse_idx ON employees(employee_id) REVERSE;

-- 修改现有索引为反向键索引
ALTER INDEX emp_id_idx REBUILD REVERSE;

-- 将反向键索引改回普通索引
ALTER INDEX emp_id_reverse_idx REBUILD NOREVERSE;

2.6 索引组织表(Index-Organized Table)

索引组织表(IOT)是一种特殊的表，其数据按主键顺序存储在索引结构中。

2.6.1 索引组织表与堆表对比

索引组织表结构
堆表结构
按主键排序存储
表数据
溢出段
无序存储
表数据
有序指针
索引

2.6.2 创建索引组织表

-- 创建基本索引组织表
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    phone VARCHAR2(20)
) ORGANIZATION INDEX;

-- 创建带溢出段的索引组织表
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER,
    shipping_address VARCHAR2(200),
    billing_address VARCHAR2(200),
    notes VARCHAR2(1000)
) ORGANIZATION INDEX
  PCTTHRESHOLD 30
  OVERFLOW TABLESPACE users;

-- 创建带二级索引的索引组织表
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    category_id NUMBER,
    price NUMBER,
    description VARCHAR2(2000)
) ORGANIZATION INDEX;

-- 为索引组织表创建二级索引
CREATE INDEX products_category_idx ON products(category_id);

3. 索引管理与维护

3.1 查看索引信息

Oracle提供了多种数据字典视图来查看索引信息。

-- 查看当前用户的所有索引
SELECT index_name, index_type, table_name, uniqueness, status
FROM user_indexes
ORDER BY table_name, index_name;

-- 查看索引列信息
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- 查看索引大小
SELECT i.index_name, i.table_name, 
       ROUND(s.bytes/1024/1024,2) "Size (MB)"
FROM user_indexes i, user_segments s
WHERE i.index_name = s.segment_name
ORDER BY s.bytes DESC;

-- 查看索引统计信息
SELECT index_name, blevel, leaf_blocks, distinct_keys, 
       avg_leaf_blocks_per_key, avg_data_blocks_per_key
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 查看索引使用情况
SELECT o.name, i.name, i.used_in_join, i.used_in_predicate, 
       i.used_in_sort, i.used_in_groupby
FROM v$object_usage i, user_objects o
WHERE i.index_name = o.object_name
AND o.object_type = 'INDEX';

3.2 索引监控与分析

监控索引使用情况对于性能优化至关重要。

3.2.1 启用索引监控

-- 启用索引监控
ALTER INDEX emp_ename_idx MONITORING USAGE;

-- 查看索引使用情况
SELECT * FROM v$object_usage;

-- 禁用索引监控
ALTER INDEX emp_ename_idx NOMONITORING USAGE;

3.2.2 分析索引统计信息

-- 收集单个索引的统计信息
ANALYZE INDEX emp_ename_idx COMPUTE STATISTICS;

-- 使用DBMS_STATS收集索引统计信息
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'HR',
    indname => 'EMP_ENAME_IDX',
    estimate_percent => 100,
    degree => 4
  );
END;
/

-- 收集表及其所有索引的统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    cascade => TRUE,
    estimate_percent => 30,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    degree => 4
  );
END;
/

3.3 索引重建

随着时间推移，索引可能变得碎片化，需要重建以优化性能。

3.3.1 判断索引是否需要重建

-- 查看索引碎片化程度
SELECT index_name, blevel, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 高clustering_factor相对于表的块数表明索引可能需要重建
SELECT i.index_name, i.clustering_factor, t.blocks, 
       ROUND(i.clustering_factor/t.blocks, 2) "CF/Blocks Ratio"
FROM user_indexes i, user_tables t
WHERE i.table_name = t.table_name
AND i.table_name = 'EMPLOYEES';

3.3.2 索引重建方法

-- 基本索引重建
ALTER INDEX emp_ename_idx REBUILD;

-- 带表空间的索引重建
ALTER INDEX emp_ename_idx REBUILD TABLESPACE indexes;

-- 并行重建索引
ALTER INDEX emp_ename_idx REBUILD PARALLEL 4;

-- 在线重建索引（不锁表）
ALTER INDEX emp_ename_idx REBUILD ONLINE;

-- 重建带压缩的索引
ALTER INDEX emp_ename_idx REBUILD COMPRESS 2;

-- 重建分区索引的特定分区
ALTER INDEX sales_date_idx REBUILD PARTITION sales_q1_2023;

3.4 索引可见性管理

Oracle允许将索引设为不可见，这样优化器就不会使用它，但索引仍会维护。

-- 将索引设为不可见
ALTER INDEX emp_ename_idx INVISIBLE;

-- 将索引设为可见
ALTER INDEX emp_ename_idx VISIBLE;

-- 查看索引可见性
SELECT index_name, visibility
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 启用不可见索引的使用
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

3.5 索引分区维护

分区索引需要特殊的维护操作。

-- 添加新分区
ALTER TABLE sales ADD PARTITION sales_q1_2024
VALUES LESS THAN (TO_DATE('01-APR-2024', 'DD-MON-YYYY'));

-- 本地分区索引自动添加对应分区
-- 全局分区索引需要重建
ALTER INDEX sales_amount_idx REBUILD;

-- 合并分区
ALTER TABLE sales 
MERGE PARTITIONS sales_q1_2023, sales_q2_2023
INTO PARTITION sales_h1_2023;

-- 拆分分区
ALTER TABLE sales
SPLIT PARTITION sales_h1_2023 AT (TO_DATE('01-APR-2023', 'DD-MON-YYYY'))
INTO (PARTITION sales_q1_2023, PARTITION sales_q2_2023);

-- 交换分区
CREATE TABLE sales_q1_2023_temp AS SELECT * FROM sales PARTITION (sales_q1_2023) WHERE 1=0;
ALTER TABLE sales EXCHANGE PARTITION sales_q1_2023 WITH TABLE sales_q1_2023_temp;

4. 索引设计最佳实践

4.1 索引列选择策略
索引列选择策略
高选择性列
WHERE子句列
JOIN条件列
ORDER BY列
GROUP BY列
复合索引顺序
唯一值较多
分布均匀
频繁过滤条件
外键列
高选择性列在前
范围条件列在后

4.1.1 选择性计算

-- 计算列的选择性
SELECT COUNT(DISTINCT department_id) / COUNT(*) AS selectivity
FROM employees;

-- 选择性接近1表示唯一值多，适合建索引
-- 选择性接近0表示重复值多，不适合建索引

-- 查看表中各列的选择性
SELECT column_name, 
       COUNT(DISTINCT "COLUMN_VALUE") / COUNT(*) AS selectivity
FROM (
    SELECT department_id AS "COLUMN_VALUE" FROM employees
    UNION ALL
    SELECT job_id AS "COLUMN_VALUE" FROM employees
    UNION ALL
    SELECT manager_id AS "COLUMN_VALUE" FROM employees
)
GROUP BY column_name
ORDER BY selectivity DESC;

4.2 复合索引设计

复合索引的列顺序对性能有重大影响。

4.2.1 列顺序优化
复合索引列顺序
选择性高的列在前
等值条件列在前
范围条件列在后
排序列顺序匹配
(employee_id, department_id)
(department_id, hire_date)
WHERE dept_id = 10 AND hire_date > '2020-01-01'
ORDER BY dept_id, last_name

4.2.2 索引跳跃扫描

Oracle 11g引入的索引跳跃扫描允许优化器在某些情况下使用复合索引，即使查询中没有指定前导列。

-- 创建复合索引
CREATE INDEX emp_dept_job_idx ON employees(department_id, job_id);

-- 以下查询可能使用索引跳跃扫描
SELECT DISTINCT job_id FROM employees WHERE job_id = 'MANAGER';

-- 查看执行计划是否使用索引跳跃扫描
EXPLAIN PLAN FOR
SELECT DISTINCT job_id FROM employees WHERE job_id = 'MANAGER';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 查找"INDEX SKIP SCAN"操作

4.3 函数索引设计

函数索引可以优化带有表达式的查询。

4.3.1 常见函数索引场景

-- 大小写不敏感搜索
CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name));
-- 优化查询: WHERE UPPER(last_name) = 'SMITH'

-- 日期截断
CREATE INDEX emp_hire_month_idx ON employees(TRUNC(hire_date, 'MM'));
-- 优化查询: WHERE TRUNC(hire_date, 'MM') = TO_DATE('01-JAN-2020', 'DD-MON-YYYY')

-- 数学计算
CREATE INDEX emp_annual_salary_idx ON employees(salary * 12);
-- 优化查询: WHERE salary * 12 > 100000

-- 字符串操作
CREATE INDEX emp_email_domain_idx ON employees(SUBSTR(email, INSTR(email, '@') + 1));
-- 优化查询: WHERE SUBSTR(email, INSTR(email, '@') + 1) = 'oracle.com'

4.3.2 函数索引注意事项
函数索引注意事项
确保查询使用完全相同的表达式
函数必须是确定性的
避免过于复杂的表达式
考虑虚拟列替代
WHERE UPPER(name) = 'SMITH'
相同输入总是产生相同输出
影响维护成本
Oracle 11g及以上版本

4.4 虚拟列索引

Oracle 11g引入的虚拟列可以替代某些函数索引场景。

-- 添加虚拟列
ALTER TABLE employees ADD (
    annual_salary GENERATED ALWAYS AS (salary * 12) VIRTUAL,
    email_domain GENERATED ALWAYS AS (SUBSTR(email, INSTR(email, '@') + 1)) VIRTUAL
);

-- 在虚拟列上创建索引
CREATE INDEX emp_annual_salary_idx ON employees(annual_salary);
CREATE INDEX emp_email_domain_idx ON employees(email_domain);

-- 使用虚拟列的查询
SELECT * FROM employees WHERE annual_salary > 100000;
SELECT * FROM employees WHERE email_domain = 'oracle.com';

4.5 索引使用的常见误区
索引使用误区
过度索引
索引不生效
忽视维护成本
不考虑数据分布
增加DML开销
浪费存储空间
在索引列上使用函数
使用IS NULL/IS NOT NULL
使用OR条件
类型不匹配
插入/更新/删除开销
碎片化问题
低选择性列
数据倾斜

4.5.1 索引不生效的常见原因

-- 在索引列上使用函数（未创建函数索引）
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH'; -- 不使用last_name索引

-- 使用IS NULL（除非索引是位图索引或创建时包含NULL值）
SELECT * FROM employees WHERE manager_id IS NULL; -- 可能不使用manager_id索引

-- 使用OR条件（除非每个条件都有索引）
SELECT * FROM employees WHERE department_id = 10 OR job_id = 'MANAGER'; -- 可能不使用索引

-- 类型不匹配
SELECT * FROM employees WHERE employee_id = '100'; -- 隐式转换可能阻止使用索引

-- 使用否定条件
SELECT * FROM employees WHERE department_id != 10; -- 可能不使用department_id索引

-- 使用LIKE带前缀通配符
SELECT * FROM employees WHERE last_name LIKE '%Smith'; -- 不使用last_name索引

5. 高级索引技术

5.1 索引压缩

索引压缩可以减少索引大小，提高缓存效率。

-- 创建带压缩的B树索引
CREATE INDEX emp_dept_job_idx ON employees(department_id, job_id)
COMPRESS 1; -- 压缩前导列

-- 创建完全压缩的B树索引
CREATE INDEX emp_dept_job_loc_idx ON employees(department_id, job_id, location_id)
COMPRESS; -- 压缩所有列

-- 修改现有索引的压缩
ALTER INDEX emp_dept_job_idx REBUILD COMPRESS 2; -- 压缩前两列

-- 禁用索引压缩
ALTER INDEX emp_dept_job_idx REBUILD NOCOMPRESS;

5.2 部分索引

部分索引只为表中满足特定条件的行创建索引。

-- 创建部分索引
CREATE INDEX emp_high_salary_idx ON employees(employee_id)
WHERE salary > 10000;

-- 创建部分唯一索引
CREATE UNIQUE INDEX emp_active_email_idx ON employees(email)
WHERE status = 'ACTIVE';

-- 部分索引只对满足WHERE条件的查询有效
-- 以下查询可以使用部分索引
SELECT * FROM employees WHERE salary > 10000 AND department_id = 20;

-- 以下查询不能使用部分索引
SELECT * FROM employees WHERE salary > 5000 AND department_id = 20;

结语
感谢您的阅读！期待您的一键三连！欢迎指正！

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Guiat

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值