index range scan,index fast full scan,index skip scan发生的条件

http://blog.youkuaiyun.com/robinson1988/article/details/4980611

ndex range scan(索引范围扫描):

1.对于unique index来说,如果where 条件后面出现了<,> ,between ...and...的时候,那么就可能执行index range scan,如果where条件后面是=,那么就会执行index unique scan。

2.对于none unique index来说 如果where 条件后面出现了=,>,<,betweed...and...的时候,就有可能执行index range scan。

3.对于组合索引来说,如果where条件后面出现了组合索引的引导列,那么可能执行index range scan。

 

index fast full scan(索引快速全扫描):

如果select 语句后面中的列都被包含在组合索引中,而且where后面没有出现组合索引的引导列,并且需要检索出大部分数据,那么这个时候可能执行index fast full scan。index fast full scan 发生的条件:

1.必须是组合索引。2.引导列不在where条件中

 

index skip scan(索引跳跃式扫描)

当查询可以通过组合索引得到结果,而且返回结果很少,并且where条件中没有包含索引引导列的时候,可能执行index skip scan

索引跳跃式扫描发生的条件:

1.必须是组合索引。

2.引导列没有出现在where条件中

 

下面通过一个简单的实验验证一下上诉理论:

SQL> create table test as select * from dba_objects;

表已创建。

SQL> create unique index ind_id on test(object_id);    ind_id是唯一索引

索引已创建。

SQL> create index ind_owner on test(owner);             ind_owner是非唯一索引

索引已创建。

SQL> create index ooo on test(owner,object_name,object_type);  ooo是组合索引

索引已创建。

SQL> exec dbms_stats.gather_table_stats('ROBINSON','TEST');

PL/SQL 过程已成功完成。

SQL> set autot trace
SQL> select owner from test where object_id=10;
执行计划
----------------------------------------------------------
Plan hash value: 2544773305

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SQL> select owner from test where object_id<10;

已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 1361604213

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     7 |    77 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     7 |    77 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_ID |     7 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

对于唯一索引,发生index range scan的时候就是返回多行记录,where 后面有 >,<,between ..and..

SQL> select owner from test where owner='SCOTT';

已选择13行。
执行计划
----------------------------------------------------------
Plan hash value: 2280863269

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |  2936 | 17616 |     7   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_OWNER |  2936 | 17616 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------

对于非唯一索引,即使where后面的限制条件是=,但是有可能返回多行,所以进行index range scan

SQL> select object_name,object_type from test where owner='ROBINSON';

已选择15行。
执行计划
----------------------------------------------------------
Plan hash value: 2845720098

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |  2936 |   114K|    23   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| OOO  |  2936 |   114K|    23   (0)| 00:00:01 |
-------------------------------------------------------------------------

因为000不是唯一索引,而且where后面用到了索引ooo的引导列,所以进行index range scan.

SQL> select owner, object_name,object_type from test where object_name='EMP' ;
执行计划
----------------------------------------------------------
Plan hash value: 1799988433

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     2 |    80 |    19   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | OOO  |     2 |    80 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------

因为查询所需要的信息可以通过索引ooo获得,并且where后面没有引导列owner,而且返回的行数很少(这里只有一行),所以CBO选择index skip scan,这里autotrace没有显示返回多少行,下面显示一下

SQL> set autot off
SQL> select owner, object_name,object_type from test where object_name='EMP' ;

OWNER                          OBJECT_NAME          OBJECT_TYPE
------------------------------ -------------------- -------------------
SCOTT                          EMP                  TABLE

SQL> select owner, object_name,object_type from test where object_type='INDEX';

已选择1701行。
执行计划
----------------------------------------------------------
Plan hash value: 3464522019

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1721 | 68840 |    70   (3)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| OOO  |  1721 | 68840 |    70   (3)| 00:00:01 |
-----------------------------------------------------------------------------

因为查询所需的信息可以通过索引ooo获得,并且where后面没有引导列owner,而且返回的行数较多(1701行),所以CBO选择index fast full scan,这样避免了全表扫描。

<think>好的,我现在需要分析用户提供的这个EXPLAIN输出结果。首先,我得先理解这个表格的结构和各列的含义。表格中有Id、Description、Owner、Name、Rows、Cost、Bytes、Remark这些列,然后还有一个Predicate Information部分。 用户提供的执行计划有三个步骤,Id从0到2。看起来像是Oracle的执行计划,因为使用了TABLE ACCESS BY INDEX ROWID和INDEX RANGE SCAN这样的术语,这常见于Oracle的EXPLAIN PLAN输出。而之前的对话中用户提到的是SQL中的EXPLAIN,但不同数据库的执行计划格式不同,所以需要确认是否是Oracle的环境。 首先,逐行分析每个步骤: Id 0是SELECT STATEMENT,通常表示整个查询的开始,Rows为1,Cost为8,这可能意味着优化器估计返回一行数据,成本为8个单位。 Id 1是TABLE ACCESS BY INDEX ROWID,属于表T_APC_CMP_LOT_PROC_DATA,Owner是APC_USERC。Rows也是1,Cost同样是8。这说明这一步是通过索引找到ROWID,然后访问表的数据行。因为Rows是1,估计只读取一行数据。 Id 2是INDEX RANGE SCAN,索引名称是IDX_T_APC_CMP_LOT_PROC_DATA_1892552960986292227,同样属于APC_USERC。Rows和Cost都是8。INDEX RANGE SCAN意味着使用了索引的范围扫描,通常是在有等值或范围条件发生。 Predicate Information部分指出Id 2的条件是LOT_ID = 'LOTB' AND PRODUCT_ID = 'BM01A'。这说明在索引扫描阶段应用了这两个列的等值条件。 接下来,需要将这些信息整合起来。执行顺序通常是从最内层开始,即Id 2先执行,然后是Id 1,最后是Id 0。所以执行过程应该是:首先通过索引IDX_T_APC_CMP_LOT_PROC_DATA_1892552960986292227进行范围扫描,使用LOT_ID和PRODUCT_ID的条件,找到符合条件的ROWID,然后通过ROWID访问表T_APC_CMP_LOT_PROC_DATA获取数据行,最后返回结果。 需要确认索引是否被正确使用。索引名称看起来像是自动生成的,可能是在创建时没有指定名称,系统自动分配了一个。索引的字段应该是LOT_ID和PRODUCT_ID,或者其中至少一个,因为查询条件用到了这两个字段。如果索引是建立在(LOT_ID, PRODUCT_ID)上,那么这就是一个复合索引,查询条件刚好是这两个字段的等值匹配,所以索引范围扫描是有效的。这种情况下,索引的使用效率应该很高,因为能快速定位到符合条件的行。 Rows列显示每一步估计返回1行,这说明优化器认为这两个条件过滤后只有一行数据。这可能是因为统计信息显示这两个值的组合在表中唯一,或者数据分布导致这样的估计。但实际数据可能不同,需要确认统计信息是否最新,否则估计可能会有偏差。 Cost列都是8,可能意味着整个查询的成本为8,这通常是一个较低的成本,说明查询效率较高。因为使用了索引,减少了需要扫描的数据块数量。 在Predicate Information中,access部分显示索引扫描的条件,说明这两个条件用于索引的访问,而不是过滤(如果是filter,则是在索引扫描后应用的条件)。这说明索引确实被用来快速定位数据,而不是在读取数据后再过滤,这有利于性能。 接下来,需要检查是否存在潜在的问题。例如,如果索引不是复合索引,而只是单独某个字段,那么另一个字段的条件可能是在表访问阶段进行过滤,这会影响效率。但根据Predicate Information中的access,两个条件都用于索引访问,因此推测索引是建立在(LOT_ID, PRODUCT_ID)上的复合索引,这样效率最佳。 另外,TABLE ACCESS BY INDEX ROWID意味着在找到索引条目后,还需要根据ROWID去表中获取数据。如果查询需要的列都包含在索引中,就可以使用索引覆盖,避免表访问,提高性能。但在这个例子中,SELECT *会返回所有列,如果索引不包含所有列,就必须访问表。如果查询只需要索引中的列,可以考虑创建覆盖索引来优化。 总结来说,这个执行计划显示查询有效地使用了复合索引,通过索引范围扫描快速定位到符合条件的行,然后通过ROWID访问表获取数据。估计返回一行,成本较低,说明查询性能良好。需要确认索引是否存在以及是否为复合索引,同时确保统计信息准确,以保持执行计划的正确性。</think>以下是对该执行计划的逐层解析与优化建议: --- ### 一、执行计划结构分析 ```plaintext ------------------------------------------------------------------------------------------------------------------------------------- | Id | Description | Owner | Name | Rows | Cost | Bytes | Remark | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | 8 | | | | 1 | TABLE ACCESS BY INDEX ROWID | APC_USERC | T_APC_CMP_LOT_PROC_DATA | 1 | 8 | | | | 2 | INDEX RANGE SCAN | APC_USERC | IDX_T_APC_CMP_LOT_PROC_DATA_1892552960986292227 | 1 | 8 | | | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by id): ----------------------------------------- 2 - access: LOT_ID = 'LOTB' AND PRODUCT_ID = 'BM01A' ``` --- ### 二、关键指标解读 #### 1. **索引使用情况** - **索引名称**:`IDX_T_APC_CMP_LOT_PROC_DATA_1892552960986292227` - **索引字段**:通过`access`条件可知,索引包含 `LOT_ID` 和 `PRODUCT_ID` 字段(复合索引) #### 2. **执行路径** ```mermaid graph LR A[INDEX RANGE SCAN] --> B[TABLE ACCESS BY ROWID] --> C[返回结果] ``` #### 3. **性能指标** | 字段 | 值 | 说明 | |------------|------|----------------------------------------------------------------------| | `Rows` | 1 | 预估扫描1行数据(条件过滤精准) | | `Cost` | 8 | 执行成本较低(通常小于100为高效) | | `Access` | 复合条件 | 索引直接命中`LOT_ID='LOTB' AND PRODUCT_ID='BM01A'` 无需额外过滤 | --- ### 三、执行计划深度解析 #### 1. **INDEX RANGE SCAN (Id=2)** - **触发条件**:WHERE子句中的等值查询`LOT_ID = 'LOTB' AND PRODUCT_ID = 'BM01A'` - **效率评估**: - 复合索引精准匹配两个字段,直接定位到目标数据页 - 无`Filter`操作,说明所有条件均通过索引完成筛选 #### 2. **TABLE ACCESS BY INDEX ROWID (Id=1)** - **必要性分析**:由于查询使用`SELECT *`,需要回表获取完整数据 - **优化空间**: - 若仅需索引列数据,可改为`SELECT LOT_ID, PRODUCT_ID`实现**索引覆盖** - 添加其他字段到索引实现覆盖索引(需权衡写入性能) #### 3. **整体性能评价** - ✅ 索引设计合理,精准命中查询条件 - ✅ 预估扫描行数极少(Rows=1) - ⚠️ 回表操作存在优化空间(见下文建议) --- ### 四、潜在问题排查 #### 1. **数据实际分布验证** - 检查表中实际满足`LOT_ID='LOTB' AND PRODUCT_ID='BM01A'`的记录数 - 若实际行数远大于1,说明统计信息过期,需更新: ```sql ANALYZE TABLE T_APC_CMP_LOT_PROC_DATA COMPUTE STATISTICS; ``` #### 2. **索引碎片检测** - 定期检查索引碎片率(特别是频繁更新的表): ```sql SELECT index_name, blevel, leaf_blocks FROM user_indexes WHERE index_name = 'IDX_T_APC_CMP_LOT_PROC_DATA_1892552960986292227'; ``` - `blevel` > 3 或 `leaf_blocks`异常增长时需重建索引 --- ### 五、优化建议 #### 1. **覆盖索引优化** ```sql -- 当前索引 CREATE INDEX idx_current ON T_APC_CMP_LOT_PROC_DATA(LOT_ID, PRODUCT_ID); -- 优化为覆盖索引(添加常用查询字段) CREATE INDEX idx_covering ON T_APC_CMP_LOT_PROC_DATA( LOT_ID, PRODUCT_ID, created_time, -- 示例:高频查询字段 status ); ``` #### 2. **查询语句改写** ```sql -- 原查询 SELECT * FROM T_APC_CMP_LOT_PROC_DATA WHERE LOT_ID = 'LOTB' AND PRODUCT_ID = 'BM01A'; -- 优化为仅取必要字段 SELECT LOT_ID, PRODUCT_ID, status FROM T_APC_CMP_LOT_PROC_DATA WHERE LOT_ID = 'LOTB' AND PRODUCT_ID = 'BM01A'; ``` #### 3. **执行计划监控** - 定期检查执行计划稳定性: ```sql SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => 'xxx')); ``` --- ### 六、扩展知识:Oracle执行计划关键操作符 | 操作符 | 说明 | |---------------------------|----------------------------------------------------------------------| | `TABLE ACCESS FULL` | 全表扫描(警惕大数据量表) | | `INDEX UNIQUE SCAN` | 唯一索引扫描(主键查询) | | `INDEX SKIP SCAN` | 跳跃扫描(复合索引前导列未参与查询时触发) | | `NESTED LOOPS` | 嵌套循环连接(小数据集连接场景) |
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值