外观
Oracle 索引设计原则
索引设计概述
索引是提高 Oracle 数据库查询性能的重要手段，合理的索引设计能够显著提高查询速度，减少 I/O 操作，降低系统资源消耗。然而，不恰当的索引设计不仅无法提高性能，反而会导致系统性能下降，增加维护成本。
索引设计是一个复杂的过程，需要考虑多种因素，包括：
- 业务查询模式
- 数据分布特征
- 表的大小和增长趋势
- DML 操作频率
- 系统资源状况
索引基本概念
索引的类型
Oracle 支持多种索引类型，每种类型适用于不同的场景：
- B树索引：最常用的索引类型，适用于等值查询和范围查询
- 位图索引：适用于低基数列，如性别、状态等
- 函数索引：基于函数或表达式的索引，适用于频繁使用函数查询的场景
- 分区索引：与分区表关联的索引，适用于大型表
- 反向索引：将索引键值反转存储，适用于序列生成的主键
- 位图连接索引：基于多表连接的位图索引，适用于数据仓库环境
- 全文索引：用于文本搜索的索引
- 空间索引：用于地理空间数据的索引
索引的结构
B树索引是 Oracle 中最常用的索引类型，其结构如下：
- 根节点：索引的顶层节点，包含指向分支节点的指针
- 分支节点：中间层节点，包含索引键值和指向子节点的指针
- 叶节点：底层节点，包含索引键值和指向表中数据行的指针
- 双向链表：叶节点之间通过双向链表连接，便于范围查询
索引设计核心原则
1. 基于查询模式设计索引
索引设计的首要原则是基于实际的查询模式，而不是表结构。DBA 应该分析系统中的查询语句，识别频繁执行的查询，然后为这些查询设计合适的索引。
识别关键查询
sql
-- 查看最近 7 天执行频率最高的 SQL
SELECT
sql_id,
sql_text,
executions,
elapsed_time / executions / 1000000 AS avg_elapsed_seconds
FROM
dba_hist_sqlstat
WHERE
executions > 100
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE - 7)
ORDER BY
executions DESC;分析查询的 WHERE 子句
为查询的 WHERE 子句中频繁使用的列创建索引，特别是那些具有高选择性的列。
2. 考虑列的选择性
列的选择性是指列中不同值的数量与总行数的比率。选择性越高，索引的效率越高。
- 高选择性列：如主键、唯一键、身份证号等，适合创建 B 树索引
- 低选择性列：如性别、状态等（不同值数量少于 10%），适合创建位图索引
计算列的选择性
sql
-- 计算列的选择性
SELECT
column_name,
num_distinct,
num_rows,
ROUND(num_distinct / num_rows, 4) AS selectivity
FROM
dba_tab_col_statistics
WHERE
owner = 'SCOTT'
AND table_name = 'EMP';3. 复合索引的列顺序
创建复合索引时，列的顺序至关重要，应遵循以下原则：
- 将选择性最高的列放在最前面
- 将频繁用于等值查询的列放在前面
- 将用于范围查询的列放在后面
- 考虑查询中列的使用频率
复合索引示例
sql
-- 假设查询频繁使用以下 WHERE 子句
-- WHERE department_id = 10 AND salary > 5000 AND hire_date > '01-JAN-2020'
-- 推荐的复合索引顺序
CREATE INDEX idx_emp_dept_sal_hire ON EMP(department_id, salary, hire_date);4. 避免过多的索引
每个索引都会带来维护成本，特别是在频繁进行 DML 操作的表上。过多的索引会导致：
- 插入、更新、删除操作变慢
- 索引占用过多存储空间
- 优化器选择执行计划的时间增加
- 索引维护开销增加
一般来说，一个表的索引数量不应超过 5-7 个。
5. 考虑索引的存储开销
索引会占用存储空间，特别是对于大型表。DBA 应该考虑：
- 索引的大小
- 索引的增长趋势
- 存储设备的容量和性能
估算索引大小
sql
-- 估算索引大小
SELECT
index_name,
ROUND(bytes / 1024 / 1024, 2) AS index_size_mb
FROM
dba_segments
WHERE
owner = 'SCOTT'
AND segment_type = 'INDEX';6. 为外键创建索引
为外键列创建索引可以提高以下操作的性能：
- 从父表删除或更新主键时
- 子表与父表的连接查询
- 子表的外键列查询
此外，为外键创建索引还可以避免锁升级，防止死锁。
7. 考虑索引的使用方式
索引的使用方式主要有以下几种：
访问路径：
- 索引唯一扫描（INDEX UNIQUE SCAN）：用于主键或唯一索引的等值查询
- 索引范围扫描（INDEX RANGE SCAN）：用于范围查询
- 索引全扫描（INDEX FULL SCAN）：用于查询所有索引列的情况
- 索引快速全扫描（INDEX FAST FULL SCAN）：使用多块读的方式扫描整个索引
- 索引跳跃扫描（INDEX SKIP SCAN）：用于复合索引中前导列未被使用的情况
连接方式：
- 嵌套循环连接（NESTED LOOPS）：适合小结果集的连接
- 哈希连接（HASH JOIN）：适合大结果集的连接
- 排序合并连接（SORT MERGE JOIN）：适合有序数据的连接
8. 定期评估索引的有效性
DBA 应该定期评估索引的有效性，识别并删除无用的索引。
识别无用的索引
sql
-- 查看最近 7 天未使用的索引
SELECT
owner,
index_name,
table_name
FROM
dba_indexes
WHERE
owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
AND index_name NOT IN (
SELECT DISTINCT index_name
FROM dba_hist_sql_plan
WHERE
operation LIKE '%INDEX%'
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE - 7)
);索引设计步骤
1. 收集需求
- 了解业务查询模式
- 识别核心业务流程
- 分析数据分布特征
- 确定表的大小和增长趋势
2. 分析查询语句
- 收集系统中的查询语句
- 识别频繁执行的查询
- 分析查询的 WHERE 子句和连接条件
- 分析查询的排序和分组需求
3. 设计索引方案
- 为频繁查询的列创建索引
- 设计复合索引的列顺序
- 选择合适的索引类型
- 考虑索引的存储位置
4. 评估索引方案
- 估算索引的存储空间
- 评估索引对 DML 操作的影响
- 使用 EXPLAIN PLAN 分析查询执行计划
- 进行性能测试
5. 实施和监控
- 创建索引
- 监控索引的使用情况
- 定期收集统计信息
- 评估索引的有效性
不同场景的索引设计
1. 在线事务处理 (OLTP) 系统
OLTP 系统的特点是频繁的 DML 操作和简单的查询，索引设计应遵循以下原则：
- 优先考虑主键和唯一键索引
- 为频繁查询的列创建 B 树索引
- 限制索引数量，一般不超过 5 个
- 避免使用位图索引
- 为外键创建索引
2. 数据仓库 (DW) 系统
数据仓库系统的特点是大量的数据加载和复杂的查询，索引设计应遵循以下原则：
- 优先考虑位图索引和位图连接索引
- 为维度表的主键创建 B 树索引
- 为事实表的外键创建位图索引
- 考虑使用分区索引
- 为频繁用于过滤和分组的列创建索引
3. 混合系统
混合系统同时包含 OLTP 和数据仓库的特点，索引设计应根据具体的业务需求进行权衡：
- 对于 OLTP 部分，采用 OLTP 系统的索引设计原则
- 对于数据仓库部分，采用数据仓库系统的索引设计原则
- 考虑使用分区表和分区索引，分离冷热数据
索引设计最佳实践
1. 优先使用 B 树索引
B 树索引是 Oracle 中最常用的索引类型，适用于大多数场景。只有在特定情况下才考虑使用其他类型的索引。
2. 合理设计复合索引
- 复合索引的列数一般不超过 4 个
- 将选择性最高的列放在最前面
- 考虑查询中列的使用顺序
- 避免冗余索引
3. 避免在频繁更新的列上创建索引
频繁更新的列会导致索引频繁维护，增加系统开销。如果必须在这些列上创建索引，应考虑以下措施：
- 减少索引数量
- 使用批量更新操作
- 定期重建索引
4. 考虑索引的存储位置
- 将索引和表存储在不同的表空间中，减少 I/O 争用
- 对于大型索引，考虑使用单独的表空间
- 对于频繁访问的索引，存储在高性能存储上
5. 定期收集统计信息
准确的统计信息对于优化器选择正确的执行计划至关重要。DBA 应该定期收集表和索引的统计信息：
sql
-- 收集表和索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCOTT',
tabname => 'EMP',
cascade => TRUE,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);6. 考虑使用索引压缩
索引压缩可以减少索引占用的存储空间，提高 I/O 性能。Oracle 支持以下索引压缩方式：
- 前缀压缩：压缩索引键值的前缀部分
- 高级索引压缩：压缩整个索引键值
sql
-- 创建压缩索引
CREATE INDEX idx_emp_dept ON EMP(department_id) COMPRESS;
-- 创建高级压缩索引
CREATE INDEX idx_emp_dept_sal ON EMP(department_id, salary) COMPRESS ADVANCED;7. 避免使用索引的情况
在以下情况下，应避免使用索引：
- 表的行数较少（一般少于 1000 行）
- 查询需要返回表中大部分数据（超过 20%）
- 频繁进行全表扫描的表
- 临时表
19c 和 21c 索引设计新特性
Oracle 19c 新特性
- 自动索引：Oracle 19c 引入了自动索引功能，能够自动识别和创建索引，减少人工干预
- 索引优化建议：通过 SQL Tuning Advisor 提供更准确的索引优化建议
- 索引重建增强：改进了索引重建算法，减少了索引重建的时间和资源消耗
- 分区索引增强：支持在线分区索引维护
Oracle 21c 新特性
- 实时索引监控：提供实时的索引使用监控，能够及时识别无用的索引
- 智能索引设计：引入了机器学习算法，能够根据查询模式自动设计最优索引
- 索引压缩增强：改进了索引压缩算法，提高了压缩率
- 新索引类型：引入了一些新的索引类型，如 JSON 索引、区块链表索引等
生产环境索引管理
1. 索引创建策略
- 在业务低峰期创建索引
- 对于大型表，使用 ONLINE 选项创建索引
- 监控索引创建过程，确保不会影响系统性能
sql
-- 在线创建索引
CREATE INDEX idx_emp_dept ON EMP(department_id) ONLINE;2. 索引重建策略
- 定期重建膨胀的索引
- 重建索引前，确保有有效的备份
- 对于大型索引，使用 ONLINE 选项重建
sql
-- 在线重建索引
ALTER INDEX idx_emp_dept REBUILD ONLINE;3. 索引监控
- 监控索引的使用率
- 监控索引的膨胀情况
- 监控索引对 DML 操作的影响
- 设置索引相关的告警
4. 索引迁移策略
- 当表结构发生变化时，评估索引的有效性
- 当查询模式发生变化时，调整索引设计
- 定期审查索引设计，确保符合业务需求
常见问题 (FAQ)
如何确定是否需要创建索引？
确定是否需要创建索引的方法包括：
- 分析查询的执行计划，查看是否存在全表扫描
- 监控查询的响应时间，判断是否需要优化
- 分析表的大小和增长趋势
- 考虑 DML 操作的频率
复合索引和多个单列索引有什么区别？
- 复合索引：多个列组合成一个索引，适用于同时使用这些列的查询
- 多个单列索引：每个列单独创建索引，Oracle 会选择其中一个或多个索引进行合并
复合索引的优点是查询效率更高，维护成本更低；缺点是不够灵活，只适用于特定的查询模式。
如何选择复合索引的列顺序？
选择复合索引的列顺序应考虑以下因素：
- 列的选择性，选择性高的列放在前面
- 列在查询中的使用频率，频繁使用的列放在前面
- 列的查询类型，等值查询的列放在前面，范围查询的列放在后面
什么时候使用位图索引？
位图索引适用于以下情况：
- 低基数列（不同值数量少于 10%）
- 数据仓库环境
- 只读或很少更新的表
- 频繁用于 AND、OR、IN 查询的列
如何监控索引的使用情况？
监控索引使用情况的方法包括：
- 查询 V$SQL_PLAN 视图，查看索引的使用情况
- 查询 DBA_HIST_SQL_PLAN 视图，查看历史索引使用情况
- 使用 Oracle Enterprise Manager 监控索引使用率
- 启用索引监控功能
sql
-- 启用索引监控
ALTER INDEX idx_emp_dept MONITORING USAGE;
-- 查看索引监控结果
SELECT * FROM V$OBJECT_USAGE WHERE index_name = 'IDX_EMP_DEPT';
-- 禁用索引监控
ALTER INDEX idx_emp_dept NOMONITORING USAGE;如何处理索引碎片？
处理索引碎片的方法包括：
- 重建索引
- 合并索引（使用 ALTER INDEX COALESCE）
- 收缩索引（对于分区索引）
总结
索引设计是 Oracle 数据库性能优化的重要组成部分，合理的索引设计能够显著提高查询性能。DBA 应该基于业务查询模式，考虑列的选择性、复合索引的列顺序、索引的维护成本等因素，设计出最优的索引方案。
在索引设计过程中，DBA 应该遵循核心原则，根据不同的系统类型（OLTP、数据仓库）选择合适的索引类型，定期评估索引的有效性，确保索引能够真正提高系统性能。
随着 Oracle 版本的升级，索引设计和管理功能不断增强，DBA 应充分利用这些新特性，提高索引设计的效率和效果。通过合理的索引设计和管理，可以有效提高数据库系统的性能和可靠性，满足业务需求。