2015年2月8日日曜日

[sqlite] WITHOUT ROWIDオプションを使った性能改善の解説

SQLite 3.8.2以降のバージョンでは、テーブル生成時のSQLに"WITHOUT ROWID"という指定ができるようになっているのをご存じでしょうか。この"WITHOUT ROWID"を利用することで、ストレージ使用量削減、処理高速化が可能です。
本家のページを参考に使用方法、どういう仕組みで何が最適化されるか、をまとめておきます。


使用方法:

テーブル生成時のSQLの末尾にWITHOUT ROWIDを追加するだけです(下線部)。
WITHOUT ROWIDを用いてテーブルを生成する際のSQL例:
CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) WITHOUT ROWID;

ただし、WITHOUT ROWID指定時には以下の点に気をつけてください。
  • PRIMARY KEYが必要
  • INTEGER PRIMARY KEYの場合はWITHOUT ROWID指定なしでも同等の効果が得られる
  • 以下の機能・APIが使えない
    • AUTOINCREMENT
    • sqlite3_last_insert_rowid()
    • incremental blob I/O
    • sqlite3_update_hook()

WITHOUT ROWID指定テーブルと通常テーブルの違い:

通常の(ROWID)テーブルを生成する際のSQL例:
CREATE TABLE IF NOT EXISTS old_wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
);
 
上記のような通常のSQLで生成したテーブルと、WITHOUT ROWID指定で生成したテーブルには以下の違いがあります。通常のROWIDテーブルとWITHOUT ROWIDテーブルとでは、ROWID値を保持するカラムをテーブル内部に生成するかどうか、という点が根本的に異なります。
通常(ROWID)テーブルは、PRIMARY KEYの指定の有無にかかわらず、必ず内部にROWIDカラムを生成し、ROWID用のB-Treeが構築されます(キー:ROWID、バリュー:word, cnt)。PRIMARY KEYが指定された場合には、ROWID用のB-Treeに加えて、PRIMARY KEY用のB-Treeがさらに構築されます(キー:word、バリュー:ROWID)。
これに対し、WITHOUT ROWIDテーブルではROWIDカラムが生成されなくなり、PRIMARY KEYで指定したカラムのB-Treeだけが構築されます(キー:word、バリュー:cnt)。
この違いにより例に挙げたテーブルでは(概算ですが)、必要ストレージが1/2になり、かつ、PRIMARY KEY指定でcntを参照する際の検索速度が2倍になる、メリットが得られます。なぜこのメリットが得られるのかを事項で説明します。

WITHOUT ROWIDテーブルで性能が改善される理由:

以下のSQLを実行を例にとって両テーブルの処理の違いを説明します。
SELECT cnt FROM wordcount WHERE word='xyzzy';

上記のSQL実行時、通常(ROWID)テーブルでは2つのB-Treeに対する捜査が必要になります。具体的には、最初にwordをキーに持つB-Treeを検索し、該当するROWIDを取り出します。その後、ROWIDをキーに持つB-Treeを検索することでcntを取り出します。
これに対しWITHOUT ROWIDテーブルではwordをキーに持つ、一つのB-Treeに対する捜査でcntを取り出すことができます。

  • 必要ストレージの改善:
    • 通常(ROWID)テーブルでは各wordの値を2個ずつ保存する
    • WITHOUT ROWIDテーブルでは1個ずつでよい
  • 検索処理の改善
    • 通常(ROWID)テーブルではwordのB-TreeとROWIDのB-Tree、2回のバイナリサーチが必要
    • WITHOUT ROWIDテーブルではwordのB-Treeに対する1回のバイナリサーチでよい

INTEGER PRIMARY KEY利用時にWITHOUT ROWID指定なしでも最適化される理由:

WITHOUT ROWID指定なしの通常(ROWID)テーブルで、INTEGER PRIMARY KEYを利用する場合、PRIMARY KEYに指定したカラムはROWIDのエイリアスになります。このエイリアス機能によりPRIMARY KEY用のB-TreeをROWIDとは別に構築する必要がなくなり、WITHOUT ROWIDを指定した場合と同様の効果が得られます。

参考:

0 件のコメント:

コメントを投稿