如何在Oracle中优化SQL索引选择？正确使用索引的技巧（索引.正确.优化.选择.技巧...）

答案是优化Oracle SQL索引需基于执行计划、谓词分析、基数、复合索引设计及统计信息维护；索引未被使用常因统计信息过期、隐式转换、函数操作、数据倾斜或表过小；通过EXPLAIN PLAN、V$SQL_PLAN、索引监控和AWR报告判断索引有效性；复合索引强调多列查询的前缀匹配，覆盖索引则通过包含所有查询列避免回表，提升性能。

如何在oracle中优化sql索引选择？正确使用索引的技巧

在Oracle中优化SQL索引选择，核心在于深刻理解数据访问模式、查询谓词，以及Oracle优化器的工作机制。这并非简单地“给每个列都加个索引”就能解决的问题，而是要像一位侦探，仔细分析SQL的意图，再对症下药。正确使用索引的技巧，说白了，就是让索引成为你查询的“快车道”，而不是“堵车点”。

解决方案

优化SQL索引选择，首先要抛开直觉，回归数据和查询本身。我的经验是，从以下几个层面入手，效果往往立竿见影：

从
```
EXPLAIN PLAN
```
开始，永远。任何关于索引的讨论，如果脱离了实际的执行计划，那都是空中楼阁。
```
EXPLAIN PLAN FOR SELECT ...
```
配合
```
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```
是你最好的朋友。它会告诉你，优化器到底选择了什么路径，哪些索引被使用了，哪些没有，以及为什么。这是理解问题和验证解决方案的起点。
理解谓词（Predicates）的威力。索引是为
```
WHERE
```
子句、
```
JOIN
```
条件、
```
ORDER BY
```
和
```
GROUP BY
```
子句服务的。
- ```
WHERE
```
  子句：等值查询（
```
=
```
  ）、范围查询（
```
<
```
  ,
```
>
```
  ,
```
BETWEEN
```
  ,
```
LIKE 'value%'
```
  ）是索引的理想场景。
- ```
JOIN
```
  条件：连接列是另一个重要的索引候选。高效的连接通常依赖于连接列上的索引。
- ```
ORDER BY
```
  和
```
GROUP BY
```
  ：如果这些操作的列能被索引覆盖，可以避免昂贵的排序操作。
关注列的基数（Cardinality）。基数指的是列中不重复值的数量。
- 高基数列：比如用户ID、订单号，这类列非常适合创建单列B-tree索引。因为每个索引键值对应的数据行很少，索引查找效率高。
- 低基数列：比如性别（男/女）、状态（有效/无效），如果单独对这类列创建B-tree索引，效果可能不佳，甚至不如全表扫描。优化器可能会认为索引查找并回表（table lookup）的成本高于直接扫描整个表。但它们可能适合作为复合索引的一部分，或者在数据仓库场景下考虑位图索引（Bitmap Index）。
巧妙运用复合索引（Composite Indexes）。当你的查询条件涉及多个列时，复合索引往往是最佳选择。例如，
```
WHERE region = 'ASIA' AND status = 'ACTIVE' AND order_date > SYSDATE - 30
```
。
- 列的顺序至关重要：一般原则是，将选择性最高的列（或最常用于等值查询的列）放在复合索引的最前面。然后是范围查询的列，最后是用于排序的列。一个索引
```
(col1, col2, col3)
```
  可以用于
```
col1
```
  、
```
(col1, col2)
```
  、
```
(col1, col2, col3)
```
  的查询，但不能直接用于
```
col2
```
  或
```
col3
```
  的查询。
- 覆盖索引：如果一个复合索引包含了查询所需的所有列（包括
```
SELECT
```
  列表中的列），那么Oracle甚至不需要访问表本身，直接从索引中就能获取所有数据，这被称为“覆盖索引”。这能极大地提升查询性能。
维护好统计信息。 Oracle的CBO（Cost-Based Optimizer）是基于统计信息来做决策的。如果统计信息过时或不准确，CBO就可能做出错误的执行计划，即使有完美的索引也无济于事。定期运行
```
DBMS_STATS.GATHER_SCHEMA_STATS
```
或针对特定表和索引收集统计信息是不可或缺的。
索引并非越多越好。每个索引都会增加DML（INSERT、UPDATE、DELETE）操作的开销，因为每次数据变动，相关的索引也需要更新。同时，过多的索引也会占用存储空间。因此，要定期审查和清理那些很少被使用或从未使用过的索引。

为什么有些索引明明存在，SQL却偏偏不用？

这简直是Oracle DBA和开发者最常遇到的“谜团”之一。明明索引在那里，查询却慢如蜗牛，

EXPLAIN PLAN

一看，赫然写着

TABLE ACCESS FULL

。这背后通常有几个常见原因，它们像“陷阱”一样，常常让人防不胜防：

统计信息过期或缺失：这是最最常见的元凶。Oracle的CBO是“算成本”的，它需要准确的统计信息来估算各种操作的代价。如果统计信息告诉CBO，某个表只有10行数据，或者某个索引列的选择性很差（比如所有值都一样），CBO很可能就会觉得全表扫描比索引查找加回表的成本更低。它不是“不用”，而是“认为不值得用”。
隐式类型转换： SQL语句中的数据类型与表列的数据类型不匹配，导致Oracle在内部进行隐式转换。例如，如果
```
employee_id
```
是
```
NUMBER
```
类型，但你写了
```
WHERE employee_id = '123'
```
。Oracle在比较前会把
```
employee_id
```
列的值转换为字符串，这样一来，索引就失效了，因为它无法在原始的
```
NUMBER
```
索引上进行查找。
在索引列上使用了函数：比如
```
WHERE TRUNC(order_date) = SYSDATE
```
。由于
```
TRUNC
```
函数作用在了
```
order_date
```
列上，Oracle无法直接利用
```
order_date
```
列上的索引。它必须对每一行数据计算
```
TRUNC(order_date)
```
的值，然后才能进行比较。解决方案是创建函数索引（Function-Based Index），即
```
CREATE INDEX idx_order_date_trunc ON orders (TRUNC(order_date));
```
。
操作符不当或不符合索引特性：
- 前导通配符的
```
LIKE
```
  ：
```
WHERE product_name LIKE '%apple%'
```
  。由于查询条件以通配符开头，Oracle无法使用B-tree索引进行范围扫描。它必须扫描所有索引条目或全表。
- ```
NOT IN
```
  或
```
!=
```
  ：这些操作符在某些情况下可能导致优化器选择全表扫描，因为它可能认为排除的成本高于包含。
- ```
OR
```
  条件：复杂的
```
OR
```
  条件有时会让优化器难以选择单一索引，可能退而求其次进行全表扫描。
数据倾斜（Data Skew）：即使统计信息是新的，如果某个列的数据分布极度不均匀（比如一个状态列，99% 的记录都是 'ACTIVE'），当查询
```
WHERE status = 'ACTIVE'
```
时，CBO可能会判断扫描99%的索引和回表，不如直接全表扫描来得快。
表太小：对于只有几十、几百行的表，索引的维护和查找开销可能比直接全表扫描还要大。优化器会很“聪明”地选择全表扫描，因为那是更经济的方式。
优化器提示（Hints）的滥用或误用：有时为了强制使用某个索引，我们可能会添加
```
/*+ INDEX(table_alias index_name) */
```
提示。但如果这个提示与优化器的默认判断相悖，或者其他条件不满足，反而可能导致性能下降。更糟糕的是，如果索引被删除或改名，提示就会失效。

如何判断一个索引是否真的有效，而不是白白占用空间？

判断索引的有效性，就像评估一个投资项目，不能只看投入，更要看产出。一个“好”的索引，是能显著提升查询性能，且其带来的收益大于其维护成本的。以下是一些实用的判断方法：

```
EXPLAIN PLAN
```
分析：这是最直接、最基础的手段。对于你关心的关键SQL，运行
```
EXPLAIN PLAN
```
。
- 关注
```
OPERATION
```
  列：是否出现了
```
INDEX UNIQUE SCAN
```
  、
```
INDEX RANGE SCAN
```
  、
```
INDEX FULL SCAN
```
  等字样？这表示索引被使用了。
- 关注
```
COST
```
  和
```
ROWS
```
  ：比较有无索引时的执行计划，成本和返回行数的变化。如果索引能大幅降低成本，那它就是有效的。
- 看
```
ACCESS PREDICATES
```
  和
```
FILTER PREDICATES
```
  ：
```
ACCESS PREDICATES
```
  表示索引被用来快速定位数据，
```
FILTER PREDICATES
```
  表示数据被取出来后，再在内存中进行过滤。索引能作为
```
ACCESS PREDICATES
```
  出现，是其价值所在。
```
V$SQL_PLAN
```
或
```
GV$SQL_PLAN
```
：对于已经在生产环境中执行过的SQL，可以通过查询
```
V$SQL_PLAN
```
（或
```
GV$SQL_PLAN
```
用于RAC环境）来查看其历史执行计划。
- 找到你的SQL ID，然后查询
```
V$SQL_PLAN
```
  。这能让你了解在实际运行中，优化器是如何选择的。
- 结合
```
V$SQL
```
  查看SQL的执行次数、平均执行时间等指标，评估索引对实际性能的影响。
索引使用监控 (
```
ALTER INDEX ... MONITORING USAGE
```
)： Oracle提供了一个非常有用的功能。
- 执行
```
ALTER INDEX index_name MONITORING USAGE;
```
  来开启对特定索引的监控。
- 运行你的应用或关键SQL工作负载一段时间。
- 查询
```
V$OBJECT_USAGE
```
  视图。
```
USED
```
  列会显示
```
YES
```
  或
```
NO
```
  ，告诉你索引是否被使用过。
- 完成后，记得执行
```
ALTER INDEX index_name NOMONITORING USAGE;
```
  关闭监控，避免不必要的开销。
- 注意：即使
```
USED
```
  为
```
NO
```
  ，也可能只是在当前监控周期内没有被使用，或者只在非常特殊的边缘查询中才会被用到。所以这只是一个参考，不能作为唯一依据。
AWR/ASH 报告分析：对于整个数据库的性能调优，AWR（Automatic Workload Repository）和ASH（Active Session History）报告是强大的工具。
- 它们能帮你识别出Top SQL语句，然后你可以针对这些高消耗的SQL去分析它们的执行计划，看索引的使用情况。
- 如果某个索引对应的表是Top SQL的频繁访问对象，但索引从未出现在Top SQL的执行计划中，那这个索引就很可能是无效的。
DML操作的性能影响：别忘了索引是DML操作的负担。如果一个索引很少被查询使用，但它所在的表有大量的
```
INSERT
```
、
```
UPDATE
```
、
```
DELETE
```
操作，那么这个索引可能就是负资产。你可以尝试在测试环境禁用或删除它，然后观察DML操作的性能是否有提升。
索引的物理属性：通过
```
DBA_INDEXES
```
或
```
USER_INDEXES
```
视图查看索引的
```
BLEVEL
```
（B-tree level）和
```
LAST_ANALYZED
```
。
- ```
BLEVEL
```
  过高（比如超过4-5层）可能意味着索引过于庞大或碎片化，查找效率会降低。但这不是绝对的，一个非常大的表，索引
```
BLEVEL
```
  高是正常的。
- ```
LAST_ANALYZED
```
  告诉你统计信息何时更新。如果很久没更新，即使索引设计合理，也可能因为统计信息不准而失效。

综合以上方法，你会对索引的有效性有一个全面的认识。

复合索引（Composite Index）和覆盖索引（Covering Index）有什么区别和最佳实践？

这两种索引类型在优化SQL查询中都扮演着重要角色，但它们的侧重点和应用场景有所不同。理解它们的区别和最佳实践，能帮助我们更精准地设计索引。

复合索引 (Composite Index)

定义：复合索引，顾名思义，是建立在表上两个或更多列上的索引。例如，

CREATE INDEX idx_emp_dept_job ON employees (department_id, job_id);

核心目的：它主要用于优化那些查询条件（

WHERE

子句）、连接条件（

JOIN

）或排序条件（

ORDER BY

）涉及多个列的SQL语句。通过将多个相关列组织在一起，Oracle可以更快地定位到满足这些多列条件的行。

最佳实践：

列的顺序至关重要（Leading Column Principle）：这是复合索引最核心的考量。Oracle只能从复合索引的“前缀”开始使用。
- 如果索引是
```
(A, B, C)
```
  ：
  - ```
  WHERE A = 'x'
```
  可以使用索引。
- ```
WHERE A = 'x' AND B = 'y'
```
    可以使用索引。
  - ```
  WHERE A = 'x' AND B = 'y' AND C = 'z'
```
  可以使用索引。
- ```
WHERE B = 'y'
```
    或
```
WHERE C = 'z'
```
    则无法单独使用这个索引。
- 选择性原则：通常，将选择性最高的列（即不重复值最多的列）放在最前面，或者将最常用于等值查询的列放在最前面。
- 等值与范围：如果查询中有等值条件和范围条件（
```
WHERE col1 = 'X' AND col2 > 'Y'
```
  ），那么将等值条件的列（
```
col1
```
  ）放在前面，范围条件的列（
```
col2
```
  ）放在后面，这样索引能更有效地过滤。
- 排序优化：如果查询有
```
ORDER BY col1, col2
```
  ，并且
```
col1
```
  也用于
```
WHERE
```
  条件，那么将
```
col1
```
  放在复合索引的最前面，可以避免额外的排序操作。
避免冗余索引：如果你已经有了
```
(A, B)
```
的复合索引，通常不需要再单独创建一个
```
(A)
```
的索引，因为
```
(A, B)
```
已经可以满足对
```
A
```
的查询。但这不是绝对的，有时为了减少索引大小或DML开销，单独的
```
(A)
```
索引可能仍然有价值。

覆盖索引 (Covering Index)

定义：覆盖索引是一种特殊的复合索引（或甚至可以是单列索引），它的独特之处在于，它包含了SQL查询所需的所有列。这意味着，当Oracle使用这个索引时，它不需要再访问表本身来获取其他数据，所有需要的信息都可以在索引中直接找到。

核心目的：消除“回表”（Table Access by ROWID）操作。回表是索引查找后，根据ROWID再去数据块中读取完整行数据的过程，这通常是I/O密集型操作。覆盖索引通过避免回表，可以显著提升查询性能，尤其对于返回大量行或表行较宽的查询。

最佳实践：

识别候选查询：寻找那些
```
SELECT
```
列表只包含少量列，并且这些列经常出现在
```
WHERE
```
、
```
JOIN
```
或
```
ORDER BY
```
子句中的查询。
- 例如：
```
SELECT order_id, customer_id FROM orders WHERE order_date >= SYSDATE - 7 ORDER BY order_id;
```
  如果有一个索引
```
(order_date, order_id, customer_id)
```
  ，它就能覆盖这个查询。
使用
```
INCLUDE
```
子句 (Oracle 11gR1 及更高版本)：这是创建覆盖索引的利器。
- ```
CREATE INDEX idx_name ON table_name (key_col1, key_col2) INCLUDE (non_key_col1, non_key_col2);
```
- ```
key_col1, key_col2
```
  是索引的“键列”，它们决定了索引的排序和唯一性（如果定义了唯一索引）。
- ```
non_key_col1, non_key_col2
```
  是“非键列”，它们被存储在索引的叶子块中，但不参与索引的排序或键值比较。
- 优点：这样可以创建更小、更高效的覆盖索引。因为非键列不参与键的比较，索引的B-tree结构会更浅，DML操作的开销也相对较小。如果所有列都作为键列，索引会变得非常大，DML开销也会更高。
权衡存储和DML开销：覆盖索引通常比普通索引更大，因为它存储了更多的列数据。这意味着更多的磁盘空间，以及在DML操作时更多的索引维护工作。因此，只有当查询性能的提升非常显著，且该查询是应用的关键瓶颈时，才考虑创建覆盖索引。
避免过度设计：不要为了覆盖所有可能的查询而创建巨大的覆盖索引。这会导致索引膨胀，反而可能降低整体性能。专注于优化少数几个最高优先级的查询。