2. 首页 > Oracle教程 > 正文

Oracle教程FG304-Oracle索引优化实战

教程整理：风哥教程  |  更新时间：2026-01-10  |  教程分类：Oracle教程  |  文档学习：11

内容大纲

内容简介：本文主要介绍Oracle数据库的索引优化，包括索引的类型、创建、管理和优化方法。风哥教程参考Oracle官方文档索引优化相关内容，为生产环境提供完整的索引优化解决方案。

Part01-基础概念与理论知识

1.1 索引概念

Oracle索引是一种数据库对象，用于提高查询性能。索引通过创建一个指向表中数据的指针结构，使得数据库可以快速定位和访问数据，而不需要扫描整个表。索引的主要作用是加速数据检索，提高查询性能。

1.2 索引类型

  • B树索引：最常用的索引类型，适用于等值查询和范围查询
  • 位图索引：适用于低基数列，如性别、状态等
  • 函数索引：基于函数或表达式创建的索引
  • 分区索引：基于分区表创建的索引
  • 反向键索引：适用于序列生成的列，减少索引争用
  • 复合索引：基于多个列创建的索引

1.3 索引优化方法

  • 创建适当的索引：根据查询需求创建适当的索引
  • 使用复合索引：对于多列查询，使用复合索引
  • 优化索引列顺序：将选择性高的列放在前面
  • 定期重建索引：维护索引的效率
  • 监控索引使用情况：识别未使用的索引
  • 删除未使用的索引：减少维护开销

Part02-生产环境规划与建议

2.1 索引优化规划

制定合理的索引优化规划：

  • 分析数据库的查询模式
  • 识别高频查询和慢查询
  • 制定索引创建策略
  • 建立索引管理的流程和规范
  • 定期执行索引优化
  • 跟踪索引优化的效果

2.2 索引优化建议

索引优化建议：

  • 根据查询需求创建适当的索引
  • 使用复合索引，提高查询性能
  • 优化索引列顺序，提高索引效率
  • 定期重建索引，维护索引的效率
  • 监控索引使用情况，识别未使用的索引
  • 删除未使用的索引，减少维护开销

2.3 索引优化结果管理

索引优化结果管理建议：

  • 保存索引优化的历史数据
  • 建立索引优化的审核机制
  • 跟踪索引使用的变化趋势
  • 分析索引性能的瓶颈
  • 与开发团队分享索引优化结果，提高应用程序性能

Part03-生产环境项目实施方案

3.1 索引配置与管理

# 1. 创建B树索引
SQL> CREATE INDEX idx_fgedu_orders_customer_id ON fgedu.fgedu_orders(customer_id);

# 2. 创建复合索引
SQL> CREATE INDEX idx_fgedu_orders_customer_date ON fgedu.fgedu_orders(customer_id, order_date);

# 3. 创建位图索引
SQL> CREATE BITMAP INDEX idx_fgedu_orders_status ON fgedu.fgedu_orders(status);

# 4. 创建函数索引
SQL> CREATE INDEX idx_fgedu_orders_upper_name ON fgedu.fgedu_orders(UPPER(customer_name));

# 5. 重建索引
SQL> ALTER INDEX idx_fgedu_orders_customer_id REBUILD;

# 6. 分析索引
SQL> ANALYZE INDEX idx_fgedu_orders_customer_id COMPUTE STATISTICS;

# 7. 删除未使用的索引
SQL> DROP INDEX idx_fgedu_orders_unused;

3.2 索引监控

# 1. 查看索引信息
SQL> SELECT * FROM dba_indexes WHERE table_name = ‘FGEDU_ORDERS’;

# 2. 查看索引使用情况
SQL> SELECT * FROM v$object_usage WHERE index_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

# 3. 查看索引统计信息
SQL> SELECT * FROM dba_ind_statistics WHERE index_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

# 4. 查看索引大小
SQL> SELECT segment_name, bytes FROM dba_segments WHERE segment_type = ‘INDEX’ AND segment_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

# 5. 监控索引使用
SQL> ALTER INDEX idx_fgedu_orders_customer_id MONITORING USAGE;

# 6. 查看索引监控结果
SQL> SELECT * FROM v$object_usage WHERE index_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

3.3 索引优化

# 1. 分析索引使用情况
# 查看索引使用情况和性能指标

# 2. 优化索引结构
# 根据分析结果优化索引结构

# 3. 重建索引
# 定期重建索引，维护索引的效率
SQL> ALTER INDEX idx_fgedu_orders_customer_id REBUILD;

# 4. 分析索引
# 收集索引统计信息
SQL> ANALYZE INDEX idx_fgedu_orders_customer_id COMPUTE STATISTICS;

# 5. 删除未使用的索引
# 删除未使用的索引，减少维护开销
SQL> DROP INDEX idx_fgedu_orders_unused;

# 6. 验证调优效果
# 查看调优后的索引性能

3.4 索引优化结果管理

# 1. 保存索引优化历史数据
# 将索引使用情况保存到表中，用于后续分析

# 2. 建立索引优化的审核机制
# 定期审核索引使用情况，确保索引资源的合理使用

# 3. 跟踪索引使用的变化趋势
# 分析索引使用的变化趋势，预测索引需求

# 4. 分析索引性能的瓶颈
# 识别索引性能的瓶颈，采取相应的措施

# 5. 与开发团队分享索引优化结果
# 提供索引优化结果给开发团队，帮助优化应用程序

Part04-生产案例与实战讲解

4.1 索引配置与管理实战

# 1. 连接数据库
$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Apr 4 12:00:00 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

# 2. 查看表结构
SQL> DESC fgedu.fgedu_orders;

Name Null? Type
—————————————– ——– —————————-
ORDER_ID NOT NULL NUMBER
CUSTOMER_ID NOT NULL NUMBER
ORDER_DATE NOT NULL DATE
AMOUNT NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(20)

# 3. 创建B树索引
SQL> CREATE INDEX idx_fgedu_orders_customer_id ON fgedu.fgedu_orders(customer_id);

Index created.

# 4. 创建复合索引
SQL> CREATE INDEX idx_fgedu_orders_customer_date ON fgedu.fgedu_orders(customer_id, order_date);

Index created.

# 5. 创建位图索引
SQL> CREATE BITMAP INDEX idx_fgedu_orders_status ON fgedu.fgedu_orders(status);

Index created.

# 6. 查看索引信息
SQL> SELECT index_name, index_type, table_name FROM dba_indexes WHERE table_name = ‘FGEDU_ORDERS’;

INDEX_NAME INDEX_TYPE TABLE_NAME
————————————– ————————— ——————————
PK_FGEDU_ORDERS NORMAL FGEDU_ORDERS
IDX_FGEDU_ORDERS_CUSTOMER_ID NORMAL FGEDU_ORDERS
IDX_FGEDU_ORDERS_CUSTOMER_DATE NORMAL FGEDU_ORDERS
IDX_FGEDU_ORDERS_STATUS BITMAP FGEDU_ORDERS

4.2 索引监控与分析实战

# 1. 启用索引监控
SQL> ALTER INDEX idx_fgedu_orders_customer_id MONITORING USAGE;

Index altered.

# 2. 执行查询，使用索引
SQL> SELECT * FROM fgedu.fgedu_orders WHERE customer_id = 100;

ORDER_ID CUSTOMER_ID ORDER_DATE AMOUNT STATUS
———- ———– ——————- ———- ——————–
1000 100 2026-04-01 00:00:00 5000 PENDING
1001 100 2026-04-02 00:00:00 6000 COMPLETED
1002 100 2026-04-03 00:00:00 7000 PENDING

# 3. 查看索引监控结果
SQL> SELECT * FROM v$object_usage WHERE index_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
————————————– ———————————– ———- —- ——————- ——————-
IDX_FGEDU_ORDERS_CUSTOMER_ID FGEDU_ORDERS YES YES 04-APR-26 12.05.00 AM

# 4. 查看索引统计信息
SQL> SELECT index_name, blevel, leaf_blocks, distinct_keys FROM dba_ind_statistics WHERE index_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
————————————– —— ———– ————-
IDX_FGEDU_ORDERS_CUSTOMER_ID 1 100 1000

# 5. 查看索引大小
SQL> SELECT segment_name, bytes/1024/1024 AS MB FROM dba_segments WHERE segment_type = ‘INDEX’ AND segment_name = ‘IDX_FGEDU_ORDERS_CUSTOMER_ID’;

SEGMENT_NAME MB
—————————————- ———-
IDX_FGEDU_ORDERS_CUSTOMER_ID 1.0625

4.3 索引优化实战

# 1. 分析索引使用情况
# 查看索引使用情况和性能指标

# 2. 执行查询，查看执行计划
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders WHERE customer_id = 100 AND order_date > ‘2026-04-01’;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
Plan hash value: 1234567890

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 100 | 2 (0)| 00:00:01 |
| 1 | INDEX RANGE SCAN| IDX_FGEDU_ORDERS_CUSTOMER_DATE | 2 | 100 | 2 (0)| 00:00:01 |
———————————————————————————————————-

# 3. 重建索引
SQL> ALTER INDEX idx_fgedu_orders_customer_id REBUILD;

Index altered.

# 4. 分析索引
SQL> ANALYZE INDEX idx_fgedu_orders_customer_id COMPUTE STATISTICS;

Index analyzed.

# 5. 执行查询，验证索引性能
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.fgedu_orders WHERE customer_id = 100;

ORDER_ID CUSTOMER_ID ORDER_DATE AMOUNT STATUS
———- ———– ——————- ———- ——————–
1000 100 2026-04-01 00:00:00 5000 PENDING
1001 100 2026-04-02 00:00:00 6000 COMPLETED
1002 100 2026-04-03 00:00:00 7000 PENDING

Elapsed: 00:00:00.01

# 6. 识别未使用的索引
SQL> SELECT index_name FROM dba_indexes WHERE table_name = ‘FGEDU_ORDERS’ AND index_name NOT IN (SELECT index_name FROM v$object_usage WHERE used = ‘YES’);

no rows selected

# 7. 优化复合索引顺序
# 根据查询模式，调整复合索引顺序
SQL> DROP INDEX idx_fgedu_orders_customer_date;
SQL> CREATE INDEX idx_fgedu_orders_date_customer ON fgedu.fgedu_orders(order_date, customer_id);

# 8. 验证新索引性能
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.fgedu_orders WHERE order_date > ‘2026-04-01’ AND customer_id = 100;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
Plan hash value: 9876543210

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 2 | 100 | 2 (0)| 00:00:01 |
| 1 | INDEX RANGE SCAN| IDX_FGEDU_ORDERS_DATE_CUSTOMER | 2 | 100 | 2 (0)| 00:00:01 |
———————————————————————————————————-

Part05-风哥经验总结与分享

5.1 索引优化最佳实践

  • 创建适当的索引：根据查询需求创建适当的索引
  • 使用复合索引：对于多列查询，使用复合索引
  • 优化索引列顺序：将选择性高的列放在前面
  • 定期重建索引：维护索引的效率
  • 监控索引使用情况：识别未使用的索引
  • 删除未使用的索引：减少维护开销

5.2 索引优化注意事项

  • 避免过度索引，索引过多会增加维护开销
  • 根据查询模式优化索引结构
  • 定期重建索引，维护索引的效率
  • 监控索引使用情况，识别未使用的索引
  • 与开发团队保持沟通，了解查询需求

5.3 索引优化建议

  • 建立索引优化流程，定期执行索引分析和优化
  • 培训DBA，提高索引优化能力
  • 建立索引优化结果的审核机制
  • 跟踪索引使用的变化趋势
  • 与Oracle支持团队保持沟通，获取索引优化的最佳实践

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息