在编写sql时要尽量考虑到sql的性能问题,其中回表现象在一定程度上对sql的性能会产生较大的影响
据库表中数据存储都是以块为单位,称为数据块;表中每行数据都有唯一的地址标志ROWID
案例:
create table temp(a varchar2(10),b int);
回表现象分析
select a from temp where b=2;
1)假设b字段上没有索引
- 那么该条SQL将进行表扫描,扫描所有该表的数据块
- 从数据块中找到记录,并且进行过滤
所以这种情况下没有索引将会导致扫描该表所有数据块,性能低下
执行计划
----------------------------------------------------------
Plan hash value: 1896031711
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEMP | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
2)假设b字段上有索引
- 那么该条SQL将进行索引扫描,在索引中找到b=2的位置,一般只需要扫描3个块左右就找到了
- 获得所有b=2的行的rowid
- 根据rowid再查询数据(这就是回表),如果数据量少,那么回表次数就少,如果需要的数据全部在索引中,那么就不会再回表了,例如a也在索引中,如果a不在索引中,那么仍然要回表一次查出a。
create index idx_b on temp(b);
执行计划
----------------------------------------------------------
Plan hash value: 1733176997
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP | 2 | 40 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
TABLE ACCESS BY INDEX ROWID| TEMP | 2 | 40 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
执行计划里出现table access by rowid说明要回表,产生的原因是select字段里有索引不包含的列
如果查询的是
select b from temp where b=2;
执行计划
----------------------------------------------------------
Plan hash value: 403326743
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_B | 2 | 26 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"=2)
Note
-----
- dynamic sampling used for this statement (level=2)
此时查询的字段,包含在索引列中,索引不会产生回表现象
总结:如果有可能的话,尽量只在索引上查询,不用回表或者只少量回表