SQL数据库索引优化实战技巧

在 SQL 优化中，索引优化是性价比最高的手段之一。正确的索引设计能将 SQL 执行速度提升 10 倍、100 倍甚至 1000 倍。其核心逻辑是通过构建有序的数据结构，将全表扫描的 O(n) 复杂度降低到 O(log n) 的精确检索，本质是用空间换时间。

1、索引基础

1.1、什么是索引

索引是数据库中的可选对象，与表绑定，存储了两类信息：表中一列或多列的值，以及这些值对应的行物理地址（ROWID）。借助索引，数据库可直接定位到目标数据行，无需遍历整个表。

索引类似书籍的目录：通过目录找到页码，直接翻到对应页，而非从第一页逐页查找。

1.2、索引的核心作用

• 加速查询：减少数据访问量，提升 SELECT、UPDATE、DELETE 的执行效率
• 加速排序和分组：若 ORDER BY 或 GROUP BY 列存在索引，Oracle 可直接利用索引的有序性，省去排序操作
• 保证数据唯一性：唯一索引能确保列值不重复，可靠性高于应用程序层面的检查
• 加速表连接：多表 JOIN 时，连接列上的索引可显著提升效率

1.3、索引的开销

索引并非没有代价，其成本包括：

• 存储开销：索引占用磁盘空间，通常为表大小的 10% 至 30%
• 写入开销：每次 INSERT、UPDATE、DELETE 操作，所有相关索引均需更新，导致写操作变慢
• 维护开销：长期使用后索引会产生碎片，需定期重建或合并

因此，索引需在真正需要的列上创建，避免盲目使用。

2、索引类型

2.1、B 树索引（默认索引）

• 特点：最通用的索引类型，支持等值查询、范围查询、排序、分组
• 适用场景：高基数列（如员工号、订单号），OLTP 系统常用
• 不适用场景：低基数列（如性别、状态），以及频繁更新的列
• 创建语法：CREATE INDEX idx_emp_ename ON emp(ename);

2.2、唯一索引

• 特点：保证索引列值唯一，主键约束自动创建唯一索引
• 适用场景：主键、唯一键列
• 性能优势：查到第一个匹配项即停止，速度优于普通索引
• 创建语法：CREATE UNIQUE INDEX idx_emp_empno ON emp(empno);

2.3、复合索引（组合索引）

• 特点：基于多列创建，生产环境中使用最广泛
• 核心原则：等值查询的列放前面，范围查询的列放后面，过滤性好的列优先
• 前缀性原则：查询条件必须包含索引的前导列，否则索引无效
• 创建语法：CREATE INDEX idx_emp_deptno_sal ON emp(deptno, sal);

2.4、函数索引

• 特点：基于列的函数或表达式创建索引，解决“在索引列上用函数导致索引失效”的问题
• 适用场景：查询中经常对列使用函数或表达式
• 创建语法：
  • 例如 WHERE UPPER(ename)='SMITH' 导致索引失效，可如下解决：
  • CREATE INDEX idx_emp_upper_ename ON emp(UPPER(ename));

2.5、位图索引

• 特点：使用位图存储索引信息，每位代表一行数据是否包含某个键值
• 适用场景：低基数列（如性别、状态），数据仓库，只读表，批量更新
• 不适用场景：高并发 OLTP 系统，频繁更新的列（更新成本极高）
• 创建语法：CREATE BITMAP INDEX idx_emp_gender ON emp(gender);

2.6、反向键索引

• 特点：反转索引键值的字节顺序，用于缓解“索引热点块”问题
• 适用场景：序列生成的主键列，如订单号、用户 ID。这些值连续递增，会导致索引右侧叶子节点成为热点区域
• 创建语法：CREATE INDEX idx_orders_order_id ON orders(order_id) REVERSE;

2.7、分区索引

• 特点：与分区表配套使用，每个分区对应一个独立的索引分区
• 类型：
  • 本地分区索引：索引分区和表分区一一对应，推荐使用
  • 全局分区索引：索引分区与表分区不对应，灵活性较低
• 适用场景：大于 10GB 的分区表
• 本地创建语法：CREATE INDEX idx_trans_trans_date ON transactions(trans_date) LOCAL;

2.8、索引组织表（IOT）

• 特点：表的数据按索引顺序存储，表本身就是索引，无单独的表段
• 适用场景：小表，或主要依赖主键访问的表
• 优势：无需回表，查询速度快
• 创建语法：

CREATE TABLE emp_iot (  empno NUMBER PRIMARY KEY,  ename VARCHAR2(20),  sal NUMBER) ORGANIZATION INDEX;

3、索引扫描类型

执行计划中的索引扫描类型直接影响 SQL 性能。需熟悉每种扫描的触发条件和特点。

扫描类型	英文名称	触发条件	性能	说明
唯一索引扫描	INDEX UNIQUE SCAN	唯一索引的等值查询	最优	最多返回一行，找到即停
索引范围扫描	INDEX RANGE SCAN	范围查询（>、<、BETWEEN、LIKE 'xxx%'）	优	扫描索引的部分叶子节点
索引快速全扫描	INDEX FAST FULL SCAN	查询所有索引列，无需排序	中	多块读，扫描整个索引，比全表扫描快
索引全扫描	INDEX FULL SCAN	需要排序，且排序列是索引列	中	单块读，扫描整个索引，结果有序
索引跳跃扫描	INDEX SKIP SCAN	复合索引的前导列未出现在条件中，但后续列有过滤	差	11g+ 特性，性能不稳定，不推荐依赖
全表扫描	TABLE ACCESS FULL	无合适索引，或全表扫描更快	最差	扫描整个表的所有数据块

关键注意事项：

• 索引快速全扫描是多块读，结果无序；索引全扫描是单块读，结果有序，可直接用于排序
• 索引跳跃扫描是 Oracle 的补救手段，性能不可靠，应尽量避免依赖
• 小表（如少于 1000 行）时，全表扫描可能比索引扫描更快——因为索引扫描需要两次 IO（先查索引再回表），反而更慢

实例：某公司 ERP 索引优化

以下是一个真实案例。

S（Situation - 情境）：某数据库运行两年后，频繁的 DML 操作导致多个核心索引碎片率超过 40%，索引高度从 3 层增至 5 层，查询性能明显下降。

T（Task - 任务）：目标明确：制定索引维护计划，清除碎片，降低索引高度，恢复性能。

A（Action - 行动）：

1、分析所有核心索引的碎片率
2、对碎片率超过 30% 的索引，使用 ALTER INDEX ... REBUILD ONLINE 重建
3、碎片率在 10% 至 30% 之间的索引，使用 COALESCE 合并
4、所有操作在业务低峰期分批执行，确保不影响正常业务

R（Result - 结果）：平均索引高度从 5 层降至 3 层，索引范围扫描性能提升 40% 到 60%。整个流程在线完成，业务未受任何影响。后续建立了每季度索引维护计划，避免问题累积。