[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を指定した場合と同様の効果が得られます。

参考：

• The WITHOUT ROWID Optimization