SQL优化必懂概念 - 回表（TABLE ACCESS BY INDEX ROWID） - 《SQL优化入门》

当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的 rowid。通过索引中记录的rowid 访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。

在进行SQL 优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！

大家还记得 1.3 节中错误的执行计划吗？

select * from test where owner='SYS';

30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id | Operation                  | Name     | Rows  | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |          |  2499 |   236K|   73   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID| TEST     |  2499 |   236K|   73   (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN           | IDX_OWNER|  2499 |       |    6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')

执行计划中加粗部分（TABLE ACCESS BY INDEX ROWID）就是回表。索引返回多少行数据，回表就要回多少次，每次回表都是单块读（因为一个 rowid 对应一个数据块）。该 SQL 返回了 30 808 行数据，那么回表一共就需要 30 808 次。

请思考：上面执行计划的性能是耗费在索引扫描中还是耗费在回表中？

为了得到答案，请大家在 SQLPLUS 中进行实验。为了消除 arraysize 参数对逻辑读的影响，设置arraysize=5000。arraysize 表示 Oracle 服务器每次传输多少行数据到客户端，默认为 15。如果一个块有 150 行数据，那么这个块就会被读 10 次，因为每次只传输 15 行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。

set arraysize 5000
set autot trace
select owner from test where owner='SYS';

30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 373050211
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |  2499 | 14994 |     6   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_OWNER |  2499 | 14994 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         74  consistent gets
          0  physical reads
          0  redo size
     155251  bytes sent via SQL*Net to client
        486  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30808  rows processed

从上面的实验可见，索引扫描只耗费了 74 个逻辑读。

select * from test where owner='SYS';

30808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3932013684
-------------------------------------------------------------------------------------
| Id |Operation                   | Name     | Rows  | Bytes | Cost(%CPU)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |          |  2499 |   236K|   73   (0)| 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID| TEST     |  2499 |   236K|   73   (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN           | IDX_OWNER|  2499 |       |    6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        877  consistent gets
          0  physical reads
          0  redo size
    3120934  bytes sent via SQL*Net to client
        486  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30808  rows processed
SQL> set autot off
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
  2    from test
  3   where owner = 'SYS';
    BLOCKS
----------
       796

SQL 在有回表的情况下，一共耗费了 877 个逻辑读，那么这 877 个逻辑读是怎么来的呢？

SQL 返回的 30 808 条数据一共存储在 796 个数据块中，访问这 796 个数据块就需要消耗 796 个逻辑读，加上索引扫描的 74 个逻辑读，再加上 7 个逻辑读[其中 7=ROUND（30808/5000）]，这样累计起来刚好就是 877 个逻辑读。

因此我们可以判断，该 SQL 的性能确实绝大部分损失在回表中！

更糟糕的是：假设 30 808 条数据都在不同的数据块中，表也没有被缓存在 buffer cache 中，那么回表一共需要耗费 30 808 个物理 I/O，这太可怕了。

大家看到这里，是否能回答为什么返回表中5% 以内的数据走索引、超过表中5%的数据走全表扫描？根本原因就在于回表。

在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表次数太多，从而导致性能严重下降。

Oracle12c 的新功能批量回表（TABLE ACCESS BY INDEX ROWID BATCHED）在一定程度上改善了单行回表（TABLE ACCESS BY INDEX ROWID）的性能。关于批量回表本书不做讨论。

什么样的 SQL 必须要回表？

Select * from table where ...

这样的 SQL 就必须回表，所以我们必须严禁使用 **Select ***。那什么样的 SQL 不需要回表？

Select count(*) from table

这样的 SQL 就不需要回表。

当要查询的列也包含在索引中，这个时候就不需要回表了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。

当一个 SQL 有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID 前面有’’*’’），也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。

关于如何创建组合索引，这问题太复杂了，我们在本书 8.3 节、9.1 节以及第 10 章都会反复提及如何创建组合索引。