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

正文

1. 索引基础概述

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

1.1 索引的作用与价值

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

1.2 索引的工作原理

1.3 索引的存储结构

Oracle索引在物理上是独立于表数据的单独段，它们存储在自己的数据块中。

1.4 索引与表的关系

2. Oracle索引类型

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

2.1 B树索引(B-Tree Index)

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

2.1.1 B树索引结构

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 位图索引结构

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 函数索引应用场景

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 反向键索引工作原理

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 INDEXPCTTHRESHOLD 30OVERFLOW 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收集索引统计信息
BEGINDBMS_STATS.GATHER_INDEX_STATS(ownname => 'HR',indname => 'EMP_ENAME_IDX',estimate_percent => 100,degree => 4);
END;
/-- 收集表及其所有索引的统计信息
BEGINDBMS_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 索引列选择策略

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 employeesUNION ALLSELECT job_id AS "COLUMN_VALUE" FROM employeesUNION ALLSELECT manager_id AS "COLUMN_VALUE" FROM employees
)
GROUP BY column_name
ORDER BY selectivity DESC;

4.2 复合索引设计

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

4.2.1 列顺序优化

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 函数索引注意事项

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 索引使用的常见误区

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;

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