Debian下Oracle索引优化策略

Debian下Oracle索引优化策略

一 索引设计与选型

• 优先选择B-Tree索引，覆盖大多数等值与范围查询场景。
• 低基数列（如性别、状态）在OLAP环境可考虑位图索引；在OLTP并发写入场景慎用位图索引以避免锁争用。
• 对经常带表达式过滤的列建立函数索引（如 UPPER、日期函数），让表达式可直接走索引。
• 大表按时间或业务键做分区表，并配套Local 分区索引，可显著降低扫描与维护成本。
• 设计复合索引时遵循“高选择性在前、常用组合在前”的顺序，并尽量设计为覆盖索引以减少回表。
• 控制索引数量：OLTP 建议每表不超过5–10个，OLAP 可放宽至10–20个，避免写入放大与锁竞争。

二 创建与维护的实操要点

• 使用在线创建减少阻塞：CREATE INDEX … ONLINE；批量导入后再建索引通常更高效。
• 评估并行创建与NOLOGGING（在可恢复窗口内）以缩短构建时间并降低日志压力。
• 定期重建/合并碎片化严重的索引，并用不可见索引做灰度验证，确认收益后再放开。
• 对高重复前缀的复合索引启用索引压缩（如 COMPRESS 1）以节省空间与提升扫描效率。
• 删除长期未使用或冗余的索引，减少 DML 维护成本与日志量。

三 SQL写法与执行计划控制

• 避免在索引列上使用函数或运算（如 WHERE UPPER(name)=…），否则易致索引失效；改用函数索引或改写条件。
• 谨慎使用NOT、IS NULL、IS NOT NULL等操作，可能导致索引不可用；必要时通过函数索引或改写逻辑适配。
• 优先采用覆盖索引与合理的复合索引顺序，减少回表与排序。
• 使用EXPLAIN PLAN或 DBMS_XPLAN 查看执行计划，必要时用索引提示（如 /*+ INDEX(t idx_name) */）进行定向，但避免长期依赖 Hint。
• 绑定变量减少硬解析，统一 SQL 文本，提升共享池命中。

四 监控评估与容量规划

• 利用 AWR/ADDM 定期识别全表扫描、索引缺失与热点等待，结合业务高峰做针对性优化。
• 通过数据字典（如 DBA_INDEXES、DBA_IND_STATISTICS）监控索引使用频率，清理UNUSED或低效索引。
• 维护统计信息：EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>‘SCHEMA’, tabname=>‘TABLE’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>‘FOR ALL COLUMNS SIZE AUTO’); 保证优化器选择准确。
• 结合 v$session_wait 等视图定位行锁、I/O 等待与扫描路径问题，闭环验证索引改动的收益。

五 场景化建议与示例

• 高并发 OLTP 订单表：主键 +（买家ID, 订单时间）复合索引 +（商品ID, 状态）复合索引 + 必要的外键索引，总数控制在6–8个，避免过多索引拖慢写入。
• 报表型大表（按日分区）：按sale_date分区，建立Local分区索引；对常用维度（如渠道、状态）结合位图索引与函数索引加速聚合与过滤。
• 低基数列查询：如按性别/状态过滤的报表，使用位图索引；在 OLTP 写入密集场景改用B-Tree或表达式改写，规避锁争用。
• 索引数量边界：理论上受 MAX_INDEXES 限制可至1000，生产上建议 OLTP 5–10、OLAP 10–20，并持续审计与清理。