答案是优化Oracle SQL索引需基于执行计划、谓词分析、基数、复合索引设计及统计信息维护；索引未被使用常因统计信息过期、隐式转换、函数操作、数据倾斜或表过小；通过EXPLAIN PLAN、V$SQL_PLAN、索引监控和AWR报告判断索引有效性；复合索引强调多列查询的前缀匹配，覆盖索引则通过包含所有查询列避免回表，提升性能。
在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
。
纳米搜索
纳米搜索：360推出的新一代AI搜索引擎
30 查看详情 关注
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操作时更多的索引维护工作。因此，只有当查询性能的提升非常显著，且该查询是应用的关键瓶颈时，才考虑创建覆盖索引。
避免过度设计： 不要为了覆盖所有可能的查询而创建巨大的覆盖索引。这会导致索引膨胀，反而可能降低整体性能。专注于优化少数几个最高优先级的查询。
总结区别：
侧重点： 复合索引侧
以上就是如何在Oracle中优化SQL索引选择？正确使用索引的技巧的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/591879.html
微信扫一扫 
支付宝扫一扫 
