SQL优化案例 - INDEX FAST FULL SCAN 优化案例 - 《SQL优化入门》

2016 年，北京一位游戏公司的朋友说以下 SQL 最慢的时候要执行 40 分钟，最快的时候只需要几秒至十来秒就可以执行完毕。

SELECT COUNT(DISTINCT IDFA)
  FROM SYS_ACTIVATION_SDK_IOS T1
 WHERE CREATE_TIME >= TRUNC(sysdate)
   AND CREATE_TIME < TRUNC(sysdate) + 1
   AND GAME_ID = 153
   AND NOT EXISTS (SELECT /*+ hash_aj */ IDFA
          FROM SYS_ACTIVATION_SDK_IOS T2
         WHERE CREATE_TIME < TRUNC(sysdate)-1
           AND T2.GAME_ID = 153
            AND T1.IDFA = T2.IDFA) ;

执行计划如下。

Execution Plan
------------------------------------------------------------------------
Plan hash value: 3686453232
------------------------------------------------------------------------
| Id  | Operation           | Name                        | Rows| Bytes |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                             |   1 |    76 |
|   1 |  SORT GROUP BY      |                             |   1 |    76 |
|*  2 |   FILTER            |                             |     |       |
|*  3 |    HASH JOIN ANTI   |                             | 93  |  7068 |
|*  4 |     INDEX RANGE SCAN| SYS_ACTIVATION_SDK_IOS_IDX1 | 304 | 11552 |
|*  5 |     INDEX RANGE SCAN| SYS_ACTIVATION_SDK_IOS_IDX1 | 888K|   32M |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(SYSDATE@!)<TRUNC(SYSDATE@!)+1)
   3 - access("T1"."IDFA"="T2"."IDFA")
   4 - access("GAME_ID"=153 AND "CREATE_TIME">=TRUNC(SYSDATE@!) AND
      "CREATE_TIME"<TRUNC(SYSDATE@!)+1)
   5 - access("T2"."GAME_ID"=153 AND "CREATE_TIME"<TRUNC(SYSDATE@!)-1)
       filter("T2"."IDFA" IS NOT NULL)

该 SQL 是一个自关联，SQL 语句里面有 HASH: HASH_AJ 提示 SQL 采用 HASH ANTI JOIN 进行关联。该 SQL 的确走的是 HASH ANTI JOIN，而且都是通过同一个索引访问数据，没有回表。表 SYS_ACTIVATION_SDK_IOS 有 14G，索引 SYS_ACTIVATION_SDK_IOS_IDX1 有 2.5G，根据（game_id,create_time,idfa）创建。

两表关联，我们要搞清楚表大小以及表过滤之后返回的行数。这里表大小已经清楚。

查看 T1 返回行数。

SELECT  COUNT(DISTINCT IDFA)
  FROM SYS_ACTIVATION_SDK_IOS T1
 WHERE CREATE_TIME >= TRUNC(sysdate)
   AND CREATE_TIME < TRUNC(sysdate) + 1
   AND GAME_ID = 153;

T1 返回 11 799 行数据。我们查看 T2 返回行数。

select count(*)
  from (SELECT IDFA
          FROM SYS_ACTIVATION_SDK_IOS T2
         WHERE CREATE_TIME < TRUNC(sysdate) - 1
           AND T2.GAME_ID = 153);

T2 返回 1 251 009 行数据。现在我们得到信息，小表 T1（11 799）与较大表 T2（1 251 009）进行关联。一般情况下，小表与大表关联，可以让小表作为 NL 驱动表，大表走连接列索引。在确定能否走 NL 之前，要先检查两个表之间的关系，同时检查表连接列的数据分布，于是我们执行如下 SQL。

SELECT IDFA, COUNT(*)
  FROM SYS_ACTIVATION_SDK_IOS
 GROUP BY IDFA
 ORDER BY 2 DESC;

我们发现 IDFA 基数很低，数据分布不均衡。因为 IDFA 基数很低，所以不能让 T1 与 T2 走嵌套循环，只能走 HASH 连接。执行计划中，T1 与 T2 本来就是走的 HASH 连接，连接方式是正确的，所以问题只能出现在访问路径上。T1 走的是 INDEX RANGE SCAN，返回了 11 799 行数据，T2 走的也是 INDEX RANGE SCAN，返回了 1 251 009 行数据。INDEX RANGE SCAN 是单块读，一般用于返回少量数据，这里返回 1 251 009 行数据显然不合适，因为 INDEX RANGE SCAN 没有回表，所以应该让其走 INDEX FAST FULL SCAN。

SELECT COUNT(DISTINCT IDFA)
  FROM SYS_ACTIVATION_SDK_IOS T1
 WHERE CREATE_TIME >= TRUNC(sysdate)
   AND CREATE_TIME < TRUNC(sysdate) + 1
   AND GAME_ID = 153
   AND NOT EXISTS (SELECT /*+ hash_aj index_ffs(t2) */
         IDFA
          FROM SYS_ACTIVATION_SDK_IOS T2
         WHERE CREATE_TIME < TRUNC(sysdate) - 1
           AND T2.GAME_ID = 153
           AND T1.IDFA = T2.IDFA);

最终该 SQL 可以在 1 分钟内执行完毕。该 SQL 跑得慢根本原因就是 INDEX RANGE SCAN 是单块读。

为什么该 SQL 有时要执行 40 多分钟，而有时只需要执行几秒至十来秒呢？原因在于 buffer cache 缓存。当 buffer cache 缓存了索引 SYS_ACTIVATION_SDK_IOS_IDX1，SQL 就能在几秒至十几秒执行完毕；如果 buffer cache 没有缓存 SYS_ACTIVATION_SDK_IOS_IDX1，执行计划中 Id=5 走的是 INDEX RANGE SCAN，导致大量单块读，所以会执行 40 分钟左右。更正了执行计划之后，该 SQL 最慢可以在 1 分钟内执行完毕。