Oracle教程FG082-索引优化策略

Part02-生产环境规划与建议

2.1 索引设计原则

索引设计的基本原则：

选择合适的列：为经常查询的列创建索引
考虑列的选择性：选择性高的列（唯一值多）适合创建索引
避免过多索引：过多的索引会增加DML操作的开销
考虑复合索引：对于多列查询，复合索引可能比多个单列索引更有效
注意索引列顺序：复合索引的列顺序应根据查询频率和选择性确定
考虑索引覆盖：如果索引包含查询所需的所有列，可以避免回表操作
避免在索引列上使用函数：这会导致索引失效
考虑分区索引：对于大表，分区索引可以提高性能

风哥提示：在生产环境中，索引设计应基于实际的查询模式和数据分布情况。

2.2 索引创建策略

索引创建的策略：

分析查询模式：了解应用程序的查询模式，确定需要创建索引的列
使用EXPLAIN PLAN：分析执行计划，查看索引的使用情况
考虑索引类型：根据列的特性选择合适的索引类型
测试索引效果：创建索引后测试查询性能，验证索引的有效性
监控索引使用：定期监控索引的使用情况，识别未使用的索引
调整索引策略：根据实际使用情况调整索引策略

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

2.3 索引维护建议

索引维护的建议：

收集统计信息：定期收集索引的统计信息，确保优化器能够正确评估索引的使用价值
重建索引：当索引出现碎片时，重建索引可以提高性能
监控索引大小：监控索引的大小，避免索引过大影响性能
删除未使用的索引：删除长期未使用的索引，减少存储空间和维护开销
使用分区索引：对于大表，使用分区索引可以提高维护效率
考虑索引压缩：对于重复值较多的索引，使用压缩可以减少存储空间

from oracle:www.itpux.com

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

在生产环境中实施索引优化的实施方案：

需求分析：分析应用程序的查询模式和性能需求。
现状评估：评估当前的索引使用情况和性能状况。
索引设计：根据查询模式设计合适的索引策略。
测试验证：在测试环境中创建索引并测试性能。
部署实施：在生产环境中部署索引策略。
监控维护：定期监控索引的使用情况和性能效果。
调整优化：根据实际使用情况调整索引策略。

Part04-生产案例与实战讲解

4.1 索引创建实战

示例：索引创建实战

— 创建示例表
CREATE TABLE fgedu_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
emp_dept VARCHAR2(50),
emp_salary NUMBER,
hire_date DATE
);/– 插入示例数据
INSERT INTO fgedu_employees VALUES (1001, ‘风哥1号’, ‘技术部’, 5000, SYSDATE – 365);INSERT INTO fgedu_employees VALUES (1002, ‘风哥2号’, ‘市场部’, 6000, SYSDATE – 180);INSERT INTO fgedu_employees VALUES (1003, ‘风哥3号’, ‘技术部’, 5500, SYSDATE – 90);INSERT INTO fgedu_employees VALUES (1004, ‘赵六’, ‘市场部’, 6500, SYSDATE – 60);INSERT INTO fgedu_employees VALUES (1005, ‘孙七’, ‘财务部’, 5800, SYSDATE – 30);– 插入更多数据
BEGIN
FOR i IN 1006 .. 10000 LOOP
INSERT INTO fgedu_employees VALUES (
i,
‘员工’ || TO_CHAR(i),
CASE MOD(i, 3)
WHEN 0 THEN ‘技术部’
WHEN 1 THEN ‘市场部’
ELSE ‘财务部’
END,
5000 + MOD(i, 2000),
SYSDATE – MOD(i, 365)
);END LOOP;COMMIT;END;/– 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘FGEDU_EMPLOYEES’);– 创建单列索引
CREATE INDEX idx_fgedu_emp_dept ON fgedu_employees(emp_dept);– 创建复合索引
CREATE INDEX idx_fgedu_emp_dept_salary ON fgedu_employees(emp_dept, emp_salary);– 创建函数索引
CREATE INDEX idx_fgedu_emp_hire_year ON fgedu_employees(EXTRACT(YEAR FROM hire_date));– 创建位图索引
CREATE BITMAP INDEX idx_fgedu_emp_dept_bitmap ON fgedu_employees(emp_dept);– 创建降序索引
CREATE INDEX idx_fgedu_emp_salary_desc ON fgedu_employees(emp_salary DESC);– 查看索引信息
SELECT index_name, index_type, table_name, uniqueness
FROM user_indexes
WHERE table_name = ‘FGEDU_EMPLOYEES’;

— 查看索引信息
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
—————————— ————————— ————— ———-
SYS_C0014001 NORMAL FGEDU_EMPLOYEES UNIQUE
IDX_FGEDU_EMP_DEPT NORMAL FGEDU_EMPLOYEES NONUNIQUE
IDX_FGEDU_EMP_DEPT_SALARY NORMAL FGEDU_EMPLOYEES NONUNIQUE
IDX_FGEDU_EMP_HIRE_YEAR FUNCTION-BASED NORMAL FGEDU_EMPLOYEES NONUNIQUE
IDX_FGEDU_EMP_DEPT_BITMAP BITMAP FGEDU_EMPLOYEES NONUNIQUE
IDX_FGEDU_EMP_SALARY_DESC NORMAL FGEDU_EMPLOYEES NONUNIQUE

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

4.2 索引使用分析

示例：索引使用分析

— 分析索引使用情况
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’;EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 分析复合索引使用
SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE emp_dept = ‘技术部’ AND emp_salary > 5000;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 分析函数索引使用
SELECT * FROM fgedu_employees WHERE EXTRACT(YEAR FROM hire_date) = 2025;EXPLAIN PLAN FOR SELECT * FROM fgedu_employees WHERE EXTRACT(YEAR FROM hire_date) = 2025;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 分析降序索引使用
SELECT * FROM fgedu_employees ORDER BY emp_salary DESC;EXPLAIN PLAN FOR SELECT * FROM fgedu_employees ORDER BY emp_salary DESC;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 分析索引覆盖
SELECT emp_id, emp_name FROM fgedu_employees WHERE emp_dept = ‘技术部’;EXPLAIN PLAN FOR SELECT emp_id, emp_name FROM fgedu_employees WHERE emp_dept = ‘技术部’;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 分析索引使用情况
Plan hash value: 3711770936

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 3333 | 232K| 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_EMPLOYEES | 3333 | 232K| 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_EMP_DEPT_SALARY | 3333 | | 2 (0)| 00:00:01 |
—————————————————————————————–

— 分析复合索引使用
Plan hash value: 3711770936

— 分析函数索引使用
Plan hash value: 2873529258

———————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————–
| 0 | SELECT STATEMENT | | 2739 | 191K| 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_EMPLOYEES | 2739 | 191K| 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_EMP_HIRE_YEAR | 2739 | | 2 (0)| 00:00:01 |
———————————————————————————————–

— 分析降序索引使用
Plan hash value: 3551662210

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 10000 | 695K| 21 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN DESCENDING | IDX_FGEDU_EMP_SALARY_DESC | 10000 | 695K| 21 (0)| 00:00:01 |
—————————————————————————————-

— 分析索引覆盖
Plan hash value: 3711770936

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 3333 | 66660 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FGEDU_EMPLOYEES | 3333 | 66660 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_FGEDU_EMP_DEPT_SALARY | 3333 | | 2 (0)| 00:00:01 |
—————————————————————————————–

学习交流加群风哥微信: itpux-com

4.3 索引优化实战

示例：索引优化实战

— 分析索引使用情况
SELECT index_name, table_name, num_rows, last_analyzed
FROM user_indexes
WHERE table_name = ‘FGEDU_EMPLOYEES’;– 收集索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(‘SYS’, ‘IDX_FGEDU_EMP_DEPT_SALARY’);– 检查索引碎片
SELECT index_name, blevel, leaf_blocks, distinct_keys, num_rows
FROM user_indexes
WHERE table_name = ‘FGEDU_EMPLOYEES’;– 重建索引
ALTER INDEX idx_fgedu_emp_dept_salary REBUILD;– 压缩索引
ALTER INDEX idx_fgedu_emp_dept_salary REBUILD COMPRESS 2;– 分析索引使用情况（使用AWR报告）
— 生成AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

— 查看未使用的索引
SELECT owner, index_name, table_name
FROM dba_indexes
WHERE owner = ‘SYS’
AND table_name = ‘FGEDU_EMPLOYEES’
AND index_name NOT IN (
SELECT DISTINCT index_name
FROM dba_hist_sql_plan
WHERE owner = ‘SYS’
AND object_name = ‘FGEDU_EMPLOYEES’
AND operation LIKE ‘%INDEX%’
);– 删除未使用的索引
DROP INDEX idx_fgedu_emp_dept_bitmap;

— 分析索引使用情况
INDEX_NAME TABLE_NAME NUM_ROWS LAST_ANALYZED
—————————— ————— ———- ————- —————
SYS_C0014001 FGEDU_EMPLOYEES 10000 2026-04-01 10:00:00
IDX_FGEDU_EMP_DEPT FGEDU_EMPLOYEES 10000 2026-04-01 10:00:00
IDX_FGEDU_EMP_DEPT_SALARY FGEDU_EMPLOYEES 10000 2026-04-01 10:00:00
IDX_FGEDU_EMP_HIRE_YEAR FGEDU_EMPLOYEES 10000 2026-04-01 10:00:00
IDX_FGEDU_EMP_DEPT_BITMAP FGEDU_EMPLOYEES 10000 2026-04-01 10:00:00
IDX_FGEDU_EMP_SALARY_DESC FGEDU_EMPLOYEES 10000 2026-04-01 10:00:00

— 检查索引碎片
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
—————————— ———- ———– ————- ———-
SYS_C0014001 2 134 10000 10000
IDX_FGEDU_EMP_DEPT 1 46 3 10000
IDX_FGEDU_EMP_DEPT_SALARY 2 134 3333 10000
IDX_FGEDU_EMP_HIRE_YEAR 1 36 2 10000
IDX_FGEDU_EMP_DEPT_BITMAP 1 1 3 10000
IDX_FGEDU_EMP_SALARY_DESC 2 134 2000 10000

学习交流加群风哥QQ113257174

Part05-风哥经验总结与分享

风哥提示：在进行索引优化时，应注意以下几点：

根据查询模式设计索引：了解应用程序的查询模式，为经常查询的列创建索引。
考虑列的选择性：选择性高的列适合创建索引，选择性低的列（如性别）不适合创建B树索引。
合理使用复合索引：对于多列查询，复合索引可能比多个单列索引更有效，但要注意列的顺序。
避免过多索引：过多的索引会增加DML操作的开销，应根据实际需要创建索引。
定期收集统计信息：定期收集索引的统计信息，确保优化器能够正确评估索引的使用价值。
监控索引使用情况：定期监控索引的使用情况，识别未使用的索引并及时删除。
重建碎片索引：当索引出现碎片时，重建索引可以提高性能。
考虑索引压缩：对于重复值较多的索引，使用压缩可以减少存储空间。
测试索引效果：创建索引后应测试查询性能，验证索引的有效性。
结合分区表使用分区索引：对于大表，分区索引可以提高查询和维护效率。

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

from oracle:www.itpux.com

风哥提示：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。学习交流加群风哥QQ113257174

生产环境建议：请根据实际情况调整配置和参数，确保生产环境的安全性和稳定性。

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

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