oracle中索引的作用 oracle索引怎么建
答案是优化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登录后复制登录后复制登录后复制登录后复制。关注
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索引选择？正确使用索引的技巧的详细内容，更多请关注乐哥常识网其它相关文章！