文章详情页_Oracle 索引回表

索引存储索引列的值和 ROWID，当执行数据查询，根据索引找到了指定的记录所在行后，还需要根据 ROWID 再查询表中数据，就是回表。

在数据库中，数据的存储都是以块为单位的，称为数据块。表中每一行数据都有唯一的地址标志 ROWID。每次使用 SQL 进行查询的时候，都要扫描数据块，找到行所在的 ROWID，再扫描该表的数据块。回表将会导致扫描更多的数据块。

回表

"回表" 一般就是指执行计划里显示的 TABLE ACCESS BY INDEX ROWID

查询的字段里有索引不包含的列

SQL> create table t1 as select * from dba_objects;

SQL> create index idx_t1 on t1(object_id);

SQL> select object_name, object_id from t1 where t1.object_id = 13;


执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

只查询索引列，但是需要回表过滤掉其他行

SQL> select object_id from t1 where object_id = 13 and owner = 'SCOTT';


执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     7 |   210 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1     |     7 |   210 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |    12 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

回表开销

索引查询要尽可能避免回表，如果不可避免，则需要关注聚簇因子是否过大。

创建表 T1，插入有序的数据

SQL> create table t1 as select * from dba_objects order by object_id;

SQL> create index idx_t1 on t1(object_id);

--查看聚簇因子
SQL> select 
  s.index_name, 
  s.table_name,
  s.clustering_factor, 
  s.num_rows,  
  s.blevel, 
  s.last_analyzed 
from user_indexes s where s.table_name = 'T1';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR   NUM_ROWS     BLEVEL LAST_ANALYZED
---------- ---------- ----------------- ---------- ---------- -------------
IDX_T1     T1                      1085      76090          1 2020/4/25 19:07:50

--回表开销
SQL> alter session set statistics_level = all;

SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id between 10000 and 30000;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID  4xz6z1b4rmg3v, child number 0
-------------------------------------
select * from t1 where object_id between 10000 and 30000

Plan hash value: 50753647

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |  19849 |00:00:00.15 |    2956 |    258 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |      1 |  15783 |  19849 |00:00:00.15 |    2956 |    258 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1 |      1 |  15783 |  19849 |00:00:00.08 |    1367 |     38 |
---------------------------------------------------------------------------------------------------------

表 T1 的聚簇因子比较小，回表的代价较低，产生了 2956 个 BUFFER。

创建表 T2，插入无序的数据

SQL> create table t2 as select * from dba_objects order by dbms_random.value;

SQL> create index idx_t2 on t2(object_id);

--查看聚簇因子
SQL> select 
  s.index_name, 
  s.table_name,
  s.clustering_factor, 
  s.num_rows,  
  s.blevel, 
  s.last_analyzed 
from user_indexes s where s.table_name = 'T2';

INDEX_NAME TABLE_NAME CLUSTERING_FACTOR   NUM_ROWS     BLEVEL LAST_ANALYZED
---------- ---------- ----------------- ---------- ---------- -------------
IDX_T2     T2                     76035      76091          1 2020/4/25 19:07:50

--回表开销
SQL> alter session set statistics_level = all;

SQL> select /*+ index(t2 idx_t2) */ * from t2 where object_id between 10000 and 30000;

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  7bugcp2muw142, child number 0
-------------------------------------
select /*+ index(t2 idx_t2) */ * from t2 where object_id between 10000
and 30000

Plan hash value: 2008370210

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |  19849 |00:00:00.59 |   21202 |    790 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |      1 |  17962 |  19849 |00:00:00.59 |   21202 |    790 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |      1 |  17962 |  19849 |00:00:00.08 |    1367 |      0 |
---------------------------------------------------------------------------------------------------------

表 T2 的聚簇因子比较大，回表的代价很高，产生了 21202 个 BUFFER。

回表优化

通过子查询解决（但这种方法并不一定效率高）

SQL> select object_id, object_name from t1 where object_id in (select /*+ cardinality(1 10) */ object_id from t1);


执行计划
----------------------------------------------------------
Plan hash value: 3529199878

---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        | 68847 |  6185K|   577   (2)| 00:00:07 |
|*  1 |  HASH JOIN             |        | 68847 |  6185K|   577   (2)| 00:00:07 |
|   2 |   SORT UNIQUE          |        | 68847 |   874K|   123   (1)| 00:00:02 |
|   3 |    INDEX FAST FULL SCAN| IDX_T1 | 68847 |   874K|   123   (1)| 00:00:02 |
|   4 |   INDEX FAST FULL SCAN | IDX_T1 | 68847 |  5311K|   123   (1)| 00:00:02 |
---------------------------------------------------------------------------------

通过复合索引解决

SQL> drop index idx_t1;

SQL> create index idx_t1 on t1(object_id, object_name);


SQL> select object_id, object_name from t1 where object_id between 10000 and 30000;

已选择19849行。


执行计划
----------------------------------------------------------
Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        | 15783 |  1217K|   104   (0)| 00:00:02 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 | 15783 |  1217K|   104   (0)| 00:00:02 |
---------------------------------------------------------------------------

参考资料

https://blog.csdn.net/iteye_4680/article/details/82582314

https://blog.csdn.net/xxzhaobb/article/details/80981537

原创文章，转载请注明出处：http://www.opcoder.cn/article/35/