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

参考:

[cygwin][ruby] Cygwin上でrubyのgemコマンドが動作しない問題の対処方法

新しいPC(Win8.1)にrubyのrefe, rrse環境をセットアップしようとして気がつきました。私の環境だと、gemコマンドの実行で以下のようなエラーになります。
※現状は、refeでなくrefe2を利用する必要があるらしいです。
% gem install refe
ERROR:  While executing gem ... (ArgumentError)
    invalid byte sequence in UTF-8


私の環境は以下の通り。
% uname -a
CYGWIN_NT-6.3 win8 1.7.33-2(0.280/5/3) 2014-11-13 15:47 x86_64 Cygwin
% ruby --version
ruby 2.0.0p598 (2014-11-13) [x86_64-cygwin]
% gem --version
2.4.1


ネット上の情報によると、LANG環境変数にC.BINARYを設定するとよいそうです。 私の環境でも、この方法でエラーを回避することができました。
% setenv LANG C.BINARY
% echo $LANG
C.BINARY


これでgemコマンドが正常に動作するようになり、refe/rrseのセットアップを完了しました!
% gem install refe
Fetching: refe-0.8.0.3.gem (100%)
Successfully installed refe-0.8.0.3
Parsing documentation for refe-0.8.0.3
Installing ri documentation for refe-0.8.0.3
Done installing documentation for refe after 3 seconds
1 gem installed
% gem install rrse
Fetching: rack-1.6.0.gem (100%)
Successfully installed rack-1.6.0
Fetching: progressbar-0.21.0.gem (100%)
Successfully installed progressbar-0.21.0
Fetching: bitclust-core-0.8.0.gem (100%)
Successfully installed bitclust-core-0.8.0
Fetching: rrse-0.6.1.gem (100%)
Successfully installed rrse-0.6.1
Parsing documentation for rack-1.6.0
Installing ri documentation for rack-1.6.0
Parsing documentation for progressbar-0.21.0
Installing ri documentation for progressbar-0.21.0
Parsing documentation for bitclust-core-0.8.0
Installing ri documentation for bitclust-core-0.8.0
Parsing documentation for rrse-0.6.1
Installing ri documentation for rrse-0.6.1
Done installing documentation for rack, progressbar, bitclust-core, rrse after 36 seconds
4 gems installed



参考: