小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】データ・アクセス方式の違いによる性能差

はじめに

Oracleには、6つのデータ・アクセス方式があります。

  • 全表スキャン(TABLE ACCESS FULL)
  • 索引一意スキャン(INDEX UNIQUE SCAN)
  • 索引レンジスキャン(INDEX RANGE SCAN)
  • 索引フルスキャン(INDEX FULL SCAN)
  • 索引スキップスキャン(INDEX SIKP SCAN)
  • 索引高速フルスキャン(INDEX FAST FULL SCAN)

これらの性能差を比較してみたいと思います。

用意したテーブル
create table table01
(
 id char(8),           --ID
 name varchar(50),     --名前
 furigana varchar(50), --ふりがな
 seibetsu varchar(3),  --性別
 birthday char(8),     --生年月日
 primary key( id )
);
CREATE INDEX test_idx1 ON hoge.table01(birthday) TABLESPACE USERS;
CREATE INDEX test_idx2 ON hoge.table01(seibetsu, birthday) TABLESPACE USERS;

このテーブルに1千万件のレコードを挿入します。
性別の男女比は50%ずつ、生年月日は365日の期間でランダムです。

全表スキャン(TABLE ACCESS FULL)

インデックスの貼られていないカラムをキーに検索します。

set timing on
set autotrace on
select count(*) from table01 where name = 'テスト 太郎10000000';
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |    40 | 41738   (1)| 00:08:21 |
|   1 |  SORT AGGREGATE    |         |     1 |    40 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE01 |     1 |    40 | 41738   (1)| 00:08:21 |
------------------------------------------------------------------------------
索引一意スキャン(INDEX UNIQUE SCAN)

主キーで検索します。

set timing on
set autotrace on
select count(*) from table01 where id = '10000000';
-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |     9 |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0030122 |     1 |     9 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
索引レンジスキャン(INDEX RANGE SCAN)

主キー以外のインデックスを使用して検索します。

set timing on
set autotrace on
select name from table01 where birthday = '20131201';
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     9 |    79   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_IDX1 | 27397 |   240K|    79   (0)| 00:00:01 |
-------------------------------------------------------------------------------
索引フルスキャン(INDEX FULL SCAN)

再現できませんでした。

索引スキップスキャン(INDEX SIKP SCAN)

複合インデックスの1列目の条件が無効(notを使用している)で2列目が有効な場合

set timing on
set autotrace on
select count(*) from table01 where not seibetsu = '男' and birthday = '20131201';
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    13 |    94   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE  |           |     1 |    13 |            |          |
|*  2 |   INDEX SKIP SCAN| TEST_IDX2 | 13436 |   170K|    94   (0)| 00:00:02 |
------------------------------------------------------------------------------
索引高速フルスキャン(INDEX FAST FULL SCAN)

問い合わせに必要なすべての列がインデックスに含まれていて、インデックスキーの1つ以上の列にnot null制約が指定されている場合

set timing on
set autotrace on
select count(*) from table01;
------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |  6530   (2)| 00:01:19 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C0030122 |    10M|  6530   (2)| 00:01:19 |
------------------------------------------------------------------------------
速いやつらだけで比較する

f:id:replication:20150606155443p:plain

  UNIQUE SCAN RANGE SCAN SKIP SCAN
consistent gets 3 80 103
physical reads 3 80 0
Cost(%CPU) 2 79 94
Time(sec) 1 1 2

速い順に並べると、
UNIQUE SCAN > RANGE SCAN > SKIP SCANの順。

遅いやつらだけで比較する

f:id:replication:20150606155920p:plain

  FAST FULL SCAN TABLE ACCESS FULL
consistent gets 24064 152873
physical reads 24040 152866
Cost(%CPU) 6530 41738
Time(sec) 79 501
まとめ

速い順に並べると、
INDEX UNIQUE SCAN > INDEX RANGE SCAN > INDEX SKIP SCAN > INDEX FAST FULL SCAN > INDEX FULL SCAN(未確認) > TABLE ACCESS FULL
となります。

参考記事

INDEX SKIP SCANは遅いらしい。

INDEX SKIP SCANは「FULL」という単語がついていないので問題視されないことが多いのですが、実際にはかなり遅いですので何らかの対策を打つべきです。INDEX SKIP SCANはINDEX FULL SCANを少しでも速くするための救済措置と理解する方が適切です。

INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011 - SH2の日記INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011 - SH2の日記