为何在查询中索引未被使用 (Doc ID 1549181.1)-白红宇

为何在查询中索引未被使用 (Doc ID 1549181.1)

阅读量：6948 次

发布时间：2019-06-27

本文共 9131 字，大约阅读时间需要 30 分钟。

* 为何在查询中索引未被使用 (Doc ID 1549181.1)

文档内容

适用于:

Oracle Database - Standard Edition - 版本号 8.1.7.4 和更高版本号

Oracle Database - Personal Edition - 版本号 8.1.7.4 和更高版本号

Oracle Database - Enterprise Edition - 版本号 8.1.7.4 和更高版本号

本文档所含信息适用于全部平台

用途

这篇文章用来解答以下的问题：为什么我的索引没有被使用?

排错步骤

“为什么索引没有被使用”是一个涉及面较广的问题。有非常多种原因会导致索引没有被使用。

以下是一些非常实用的检查列表。请点击以下链接来查看文章的详细内容：

高速检查

表上是否存在索引？
检查您觉得应该通过索引訪问的表上是否真的有定义索引。那些索引可能已经被删掉或者在创建的时候就失败了 – 比方一种可能的场景是，在对表做导入或 load 操作后，因为软件或人为错误造成索引没有被创建。以下的语句能够用来检查索引是否存在。
```
SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;
```

索引是否应该被使用？
Oracle 不会只由于有索引存在就一定要使用索引。假设一个查询须要检索出这个表里全部的记录（比方说表之间做连接操作），那为什么还要既訪问索引的全部数据又訪问表的全部数据呢？在这样的情况下只訪问表的数据会更快。
对全部的查询 Oracle Optimizer 会基于统计信息来计算各种訪问路径，包含索引。从而选出最优的一个。

索引本身的问题

索引列或者索引的前置列是否在单表（non-join）查询的 Where 条件中（predicate list）？
假设不是，至少须要索引前置列在查询谓词列表中，查询才干使用索引。（例外：请见以下的 Skip Scan）。
演示样例:
在列 EMP.EMPNO 上定义了单列索引 EMPNO_I1，同一时候在列 EMP.EMPNO 和 EMP.DEPT 上定义了联合索引 EMPNO_DEPT_I2（EMP.EMPNO为索引前置列）。
那么必须在查询谓词列表中（where从句）使用列 EMP.EMPNO。优化器才干使用这两个索引中的某一个。
```
SELECT ename, sal, deptno FROM emp WHERE empno<100;
```
例外:
- 仅仅要索引中包括查询所需的全部列, 并且至少有一个索引列中含有非空约束，CBO 就行使用索引高速全扫描(INDEX_FFS)。运行 INDEX_FFS 不须要索引前置列。须要注意的是 INDEX_FFS 不能保证返回的行是排序的。
  结果的顺序是与读取索引块的顺序一致的。仅仅有当使用了 'order by' 子句时才干保证结果是排序的。请參照:
  
  Ordering of Result Data
  
  Index Fast Full Scan Usage To Avoid Full Table Scans
- CBO 能使用 Index Skip Scan (INDEX_SS). 运行 INDEX_SS 不须要索引前置列。
  请參照:
  
  Index Skip Scan Feature
- CBO 可以选用一个索引来避免排序。可是索引列必须存于在 order by 子句中才可以。
  请參照
  
  When will an ORDER BY use an Index to Avoid Sorting?
  
  Driving ORDER BY using an Index

索引列是否用在连接谓词中（join predicates）？
比如。以下这个连接谓词定义了怎样在表 emp 和 dept 的 deptno 列上做连接：
```
emp.deptno = dept.deptno
```
假设索引列是连接谓词的一部分，那么查询在运行时使用了哪种类型的连接？
- 哈希/排序合并连接（Hash / Sort Merge Join）: 对于哈希连接和排序合并，在连接运行的时候，外部表的信息还没有获得。因此无法进行对内部表的行检索。
  它的处理方式是将外部表和内部表分别查询后将结果合并。
  哈希连接和排序合并的内部表不能通过连接的索引列单独被訪问。这是连接类型的运行机制的限制。嵌套循环连接有所不同，它们同意通过索引查询内部表的连接列。
- 嵌套循环连接（Nested Loops Join）:嵌套循环连接读取外部表。然后利用所收集的信息訪问内部表。
  该算法同意对内部表基于索引进行查询。
仅仅有嵌套循环连接（Nested loops join）同意索引在内部表中仅基于连接列进行查找。
另外，连接的顺序（join order）是否同意使用索引?
一个嵌套循环连接的外部表必须已经訪问过，才干够在内部表中使用索引。查看 explain plan，以确定哪些訪问路径已经使用。
因为这个限制，表的连接顺序是非常重要的。
比如：如果我们通过"emp.deptno = dept.deptno"来对 EMP 和 DEPT 做连接，而且在 EMP.DEPTNO 有一个索引。并如果查询中没有与 EMP.DEPTNO 相关的其它谓词，EMP 是在 DEPT 前被訪问，然后没有值可用于在 EMP.DEPTNO 索引中查询。
在这样的连接顺序下，要想使用这个索引我们仅仅能使用全索引扫描或索引高速全扫描。在这样的情况下。全表扫描（FTS）的成本可能更小。

索引列在 IN 或者多个 OR 语句中？
比方：
```
emp.deptno IN (10,23,34,....)
```
或
```
emp.deptno = 10OR emp.deptno = 23OR emp.deptno = 34....
```
这样的情况下查询可能已经被转化为不能使用索引的语句。
请參照:

Optimization of large inlists/multiple OR`s

索引列是否被函数改动？
索引不能用于被函数改动的列。函数索引（function based indexes）能够用来解决问题。

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration

Database Concepts

Chapter 3 Indexes and Index-Organized Tables

Overview of Function-Based Indexes

http://docs.oracle.com/database/121/CNCPT/indexiot.htm#CBBGIIFB

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration

Database Performance Tuning Guide

Chapter 2 Designing and Developing for Performance

Section 2.5.3 Table and Index Design

隐式类型转换（implicit type conversion）是什么？
假设进行比較的两个值的数据类型不同，则 Oracle 必须将当中一个值进行类型转换使其可以比較。
这就是所谓的隐式类型转换。通常当开发者将数字存储在字符列时会导致这样的问题的产生。Oracle 在执行时会强制转化当中一个值，（因为固定的规则）在索引字符列使用 to_number。因为加入函数到索引列所以导致索引不被使用。实际上，Oracle 也仅仅能这么做，类型转换是一个应用程序设计因素。因为转换是在每行都进行的，这会导致性能问题。
详见:

ORA-01722 ORA-01847 ORA-01839 or ORA-01858 From Queries with Dependent Predicates

是否在语义（semantically）上无法使用索引？
出于对查询总体成本的考虑。一个成本较低的运行计划中可能是无法使用索引的。某索引可能已经被考虑在某种连接排序及方法中。可是成本最低的那个运行计划中却无法从“语义”角度使用该索引。

错误类型的索引扫描？
比如：高速全索引扫描而不是索引范围扫描
这可能是优化器选择了所需的索引，但却使用了客户不希望的扫描方法。在这样的情况下，利用 INDEX_FFS，INDEX_ASC 和 INDEX_DESC 提示来强制使用须要的扫描类型。
请參照:

Init.ora Parameter "FAST_FULL_SCAN_ENABLED" Reference Note

我们还能够定义索引的排序顺序为递增或递减。Oracle 对待降序索引就好像它是基于函数的索引。因此与缺省使用的升序的运行计划不同。通过查看运行计划，您看不到使用升序或降序，须要额外检查视图 DBA_IND_COLUMNS 的'DESCEND'列。

是否索引列为可空？
索引不存储 NULL 值。除非该索引为联合索引（即多列索引），或者它是一个位图索引。
仅仅有至少有一个索引列有值。联合索引才存储空值。联合索引中尾部的空值也会被存放在索引中。假设全部列的值都为空，这行将不会存储在索引中。由于索引中缺乏 NULL 值，那么一些结果中可能会返回 NULL 值（如count）的操作可能会被禁用索引。这是由于优化器不能保证在单独使用索引时能够获得准确的信息。
关于使用 NOT IN 和 NULL 的其它一些考虑,请參考

Use of indexes with NOT IN subquery

位图索引同意存储空值。
因此优化器会使用这些索引,不管它们的结果可信与否。索引上的空值有时非常实用，特别对于某些类型的 SQL 语句，如与聚合函数 COUNT 查询。
演示样例:
```
SELECT count(*) FROM emp;
```
位图索引的很多其它信息请參考

All about Bitmap Indexes

NLS_SORT是否设置为二进制（BINARY）？
假设 NLS_SORT 未设置为二进制，索引将不会被使用。这是由于索引是基于 Key 值的二进制顺序来建立的（pre-sorted使用二进制值）。不管优化器设置为何种方法。NLS_SORT 不是二进制时。将使用全表扫描，。
很多其它关于NLS_SORT和索引的使用，请參考:

Init.ora Parameter "NLS_SORT" Reference

Linguistic Sorting - Frequently Asked Questions (section 4.)

是否使用的是不可见索引（invisible indexes）？
从 Oracle Database 11g Release 1開始，您能够创建不可见索引或将一个已经存在的索引标记为不可见。Optimizer 不会考虑不可见索引，除非在 session 或 system 级将參数 OPTIMIZER_USE_INVISIBLE_INDEXES 设置为 TRUE。DML 操作还是会维护这些不可见索引的。详见：

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration

Database Administrator's Guide

Understand When to Use Unusable or Invisible Indexes

优化器和成本计算相关问题

是否存在准确且合适的统计信息（Statistics）？
CBO 依赖于准确的、最新的和完整的统计信息来确定一个特定查询的最佳运行计划。假设使用 CBO，请确保统计信息已经收集。假设没有统计信息， CBO 将使用提前定义的统计信息。这样是非常可能不会产生良好的计划或让应用程序使用索引。
请參照:

Cost Based Optimizer - Common Misconceptions and Issues - 10g and Above

请注意，CBO 会依据开销(COST)来决定使用不同的索引。除了主要的表和索引的信息之外，假设说在某些列上数据分布是不均匀的，那么还须要收集这些列的数据的分布。关于收集统计信息的建议。请參见下面文档：

Optimizer Statistics - Central Point

Statistics Gathering: Frequency and Strategy Guidelines

在普通情况下，对象的数据或结构的改变会使曾经的统计信息不准确。因此应该又一次收集新的统计信息。比如，对表装载了大量的数据后，须要收集新的统计信息。安装新补丁集（Patchset）后,也建议又一次收集统计信息。表訪问最佳效果是统计信息是在同样版本号的数据库中生成的。下边的文档讨论了为什么数据库升级后查询的性能会下降：

TROUBLESHOOTING: Server Upgrade Results in Slow Query Performance

一个索引是否与其他的索引有同样的等级或者成本（cost）？
对于同样开销（COST）的索引。CBO 会使用多种办法将不同的索引区分开。如将索引名称按字母顺序排序，全然匹配的索引扫描会选择更大的NDK(不同键值的个数)的索引（不适用于高速全扫描）或选择叶块数量较少的索引。请注意一般非常少发生这样的情况。
请查看

Handling of equally ranked (RBO) or costed (CBO) indexes

索引的选择度不高？
- 索引的选择度不高
  使用它可能不是一个好的选择...
  列数据不是平均分布的。
- CBO 假定列数据不会倾斜，并均匀分布。
  假设不是这样，那么统计信息可能没有反映真实情况，那么即使某些值的选择度高，索引也会由于整个列的选择度不高而不适用索引。
  假设是这样的情况，那么应考虑採用直方图记录更准确的列的数据分布或者採用提示（hint）。
- 统计信息不准确导致索引看起来选择性不高而不被选择。可能的规避方法:
- 收集更精确的统计值。
  请查看
  
  Optimizer Statistics - Central Point
  
  Statistics Gathering: Frequency and Strategy Guidelines
- 对于数据分布不均匀的列考虑收集列的统计信息
- 使用 hint 或 outline。请參考
  
  QREF: SQL Statement HINTS
  
  How to specify an INDEX Hint

在整体成本中，表扫描的成本占大部分
通常来说。当使用索引的时候，我们须要再次检索表本身来找到索引中不存在的字段的值。这个操作比检索索引本身的开销要大非常多。因为 optimizer 是基于整体的成本来计算运行计划，假设通过索引检索表的成本非常大，而且超过了某个阀值。optimizer 就会考虑其它的訪问路径。
比方
```
SELECT empno FROM emp WHERE empno=5
```
这条语句可能会使用基于列 empno的索引，由于全部须要的数据都存放在索引中所以不须要再对表做而外的訪问。反之：
```
SELECT ename FROM emp WHERE empno=5
```
这条语句会须要对表做而外的訪问，由于 ename 字段没有存放在索引中。检索 ename 的开销会随着查询返回记录条数的添加而变得昂贵。
Optimizer 使用"Clustering Factor"来推断假设使用 index 的话须要而外对表做多少次訪问，详见：

Clustering Factor

訪问空索引并不意味着比訪问有值的索引高效。
Reorganization, Truncation 或删除操作不一定会影响 SQL 语句运行的成本。须要注意的是删除操作并不会从对象中真正释放空间。也就是说，删除操作不会重置对象的高水位线。Truncate 操作会重置高水位线。空块的存在会使索引/表扫描的成本比实际应该的成本高。删掉并重建会重组对象的结构从而有可能会有帮助（也有可能变坏）。
这类问题通常在比較两个有同样数据的不同系统查询性能时更easy看到。

參数设置
某些參数的设置可能会影响索引的使用。比方在大多数情况下都建议使用 DB_FILE_MULTIBLOCK_READ_COUNT 和 OPTIMIZER_INDEX_COST_ADJ 的默认值。除非某些特定的操作有特定的建议，使用其他值会使索引的成本不现实的降低或变大从而极大的降低查询的性能。

其他问题

是否使用了视图/子查询？
查询涉及到视图或者子查询时可能会被改写，导致不使用索引（虽然该改写的目标之中的一个是扩展很多其它的訪问路径）。这些改写(rewrite)一般来说都是合并(merging)操作。
请查看

Optimizing statements that contain views or subqueries

是否存在远程表(remote table)？
通常远程表不会使用索引。
索引在分布式查询中的使用依赖于被发送到远程的查询。
CBO 将评估远程訪问的成本，并评估比較发送或者不发送索引的谓词到远程网站的成本。因此。CBO 能够做出有关远程表上使用索引的更加明智的决定。一个很有效的方法就是,在远程建立包括相关谓词的视图并强制使用索引。之后在本地查询中使用这个视图。请參考

Distributed Queries

是否使用并行运行（PX）?
在并行运行时索引的採用比在串行运行((serial execution))时更加严格。一个高速检測的方法就是禁用并行。然后查看该索引是否被使用。

是否是包括了子查询的Update语句？
在一些情况下，基于成本的考虑，索引没有被选使用是由于它依赖于一个子查询返回的值。
这样的情况下，能够使用提示（hint）来强制使用索引。
请參考

Using hints to optimize an Update with a subquery that is not using an index on the updated table.

查询是否使用了绑定变量？
CBO 对 like 或范围谓词的绑定变量不能产生准确的成本(cost)。
这可能会导致索引不被选择。请參考

Predicate Selectivity

查询是否引用了带有延迟约束的列?
假设一个表中的某一列上含有延迟约束（比方 NOT NULL）而且这一列上有索引，那么无论这个约束当前是延迟状态或是被显式地设置为马上使用，我们都不会考虑使用这一列上的索引。比如：

CREATE TABLE tdc
( x INT CONSTRAINT x_not_null NOT NULL DEFERRABLE INITIALLY DEFERRED RELY,
y INT CONSTRAINT y_not_null NOT NULL,
z VARCHAR2(30)
);
CREATE INDEX t_idx ON tdc(x);

SET CONSTRAINTS ALL IMMEDIATE; <-- 将全部延迟约束置为马上使用
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT COUNT(1) FROM tdc;    <-- 索引不会被使用
Execution Plan
----------------------------------------------------------
Plan hash value: 2532426293
-------------------------------------------------------------------
| Id | Operation          | Name | Rows | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TDC |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------

这个现象在下面 bug 中记录。关闭为"not a bug":

optimizer ignoring deferrable constraints even when not deffered and not in tx

索引提示（hint）不工作

请使用表的别名。请參考

Why is my hint ignored?

How to specify an INDEX Hint

实用的 hints:

FIRST_ROWS	相当于提示使用索引
ORDERED	强制查询的关联顺序（join order of a query）。Oracle 推荐使用 LEADING hint 由于它更好用。
LEADING	这个 hint 告诉 optimizer 先使用指定的表做连接。它比 ORDERED 更好用。
INDEX	强制使用索引扫描，并禁用高速模式（INDEX_FFS）
INDEX_FFS	强制使用高速索引扫描INDEX_FFS
INDEX_ASC	强制使用升序的索引范围扫描（Ascending Index Range Scan）
INDEX_DESC	强制使用降序的索引范围扫描（Descending Index Range Scan）