SQLite::ExplainPP -- sqlite3 の explain 文の出力の整形モジュール

SQLite3 の SQL 文のオプティマイザがどのようにループの最適化をおこなったのかをてっとり早く調べるには、SQL コンパイラが生成した仮想データベース・エンジン(VDBE)のアセンブラのソースをEXPLAIN 文を使って眺めるのが早道です。なのですが……、EXPLAIN 文の出力のままでは、オペランドの種類がわかりにくい上に、ジャンプ先がどこにあるのか一目では見当がつきません。
そこで、EXPLAIN 文の出力を整形して可読性を上げる Perl モジュールを書いてみました。ただし、VDBE は SQLiteバージョンが上がるにつれて変化しており、全部に対応するのはめんどくさいので SQLite3 の 3.0.0 から 3.5.4 に限定しました。ちなみに、私が日常的に使用している環境では、Ubuntu Linux 8.04LTS に付属のバージョンが 3.4.2 で、MacOS X Tiger 付属のバージョンが 3.4.0 です。最近、コード作りに使っている Google Gears の sqlite3 のバージョンが 3.6.x なので対象外ですが、そのうち Javascript で書き直してみるかもしれません。
使い方は、あらかじめ作成しておいたデータベース・ファイルを使って、EXPLAIN 文を実行させるようにしました。例えば、趣味的に記述した掲示板のトップページ用の SELECT 文のコンパイル結果を調べるには、次のようにします。newメソッドにデータベース・ファイル名を渡し、explainメソッドにDBIのselectall_arrayrefメソッドのように SQL ステートメントアトリビュート、バインドするパラメータを渡してやります。すると、整形された文字列を返しますので、print するなりします。

#!/usr/bin/perl
use strict;
use warnings;
use SQLite::ExplainPP;

my $pp = SQLite::ExplainPP->new('bbs-develop.db');
print $pp->explain(q{
    EXPLAIN SELECT L1.thread_id, L1.comment_seqno, C.body
      FROM thread AS T1, comment_list AS L1, comment AS C
     WHERE T1.id IN (
            SELECT T2.id
              FROM thread AS T2
             ORDER BY T2.raise_comment_id DESC
             LIMIT 8 OFFSET ?
       )
       AND (
            T1.first_comment_id = C.id
            OR C.id IN (
                 SELECT L2.comment_id
                   FROM comment_list AS L2
                  WHERE T1.id = L2.thread_id
                  ORDER BY L2.comment_id DESC
                  LIMIT 10
            )
       )
       AND T1.id = L1.thread_id
       AND C.id = L1.comment_id
     ORDER BY T1.raise_comment_id DESC, C.id ASC;
}, undef, 16);

__END__
CREATE TABLE comment (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
   ,ctime DATETIME NOT NULL
   ,body TEXT NOT NULL
);

CREATE TABLE thread (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
   ,first_comment_id INTEGER NOT NULL UNIQUE
   ,raise_comment_id INTEGER NOT NULL UNIQUE
   ,count_comment INTEGER NOT NULL CHECK (count_comment > 0)
);
CREATE INDEX thread_raise_id ON thread (raise_comment_id DESC);

CREATE TABLE comment_list (
    comment_id INTEGER NOT NULL PRIMARY KEY
   ,thread_id INTEGER NOT NULL
   ,comment_seqno INTEGER NOT NULL CHECK (comment_seqno > 0)
);
CREATE INDEX comment_thread_list ON comment_list (thread_id, comment_id);

出力は長いので、2つの入れ子の一番目の SELECT 文に対応する部分だけを抜き出して見ました。左からアドレス、ラベル、ニモニック、3つのパラメータの順に並んでいます。ラベルはジャンプの方向によってFかLの接頭語をつけています。Fはアドレスが進む方向へのジャンプ先、Lは戻る方向へのジャンプ先を表します。戻る方向はループの場合がほとんどなので、ループのLにしました。オペランドがカーソル番号のときは cur をつけ、メモリセルのときは mem を付け、テーブルかインデックスの rootpage は sqlite_master テーブルを参照してテーブル名を求めています。整数イミーディエートは10進表記で、フラグは16進表記にしています。使われていないオペランドアスタリスクでつぶしています。
アドレス 19 からの 3 ステップでメモリセル mem[1] に LIMIT 8 の値を格納しています。アドレス 24 からの 3 ステップで、プリペアード・ステートメントの bind 値に渡した一番目の値(上では OFFSET の 16)を var(1) から取り出して、メモリセル mem[3] に格納しています。アドレス 36 でテーブル thread のインデックス thread_raise_id をカーソル cur[9] にオープンして、アドレス 40 からインデックスの末尾から Prev ループ(ラベルはL0040)を回し、メモリセル mem[3] 個分を読み飛ばした後に、メモリセル mem[1] 個分をオープン済みの作業インデックスのカーソル cur[7] に格納しているのが、読み取りやすくなっています。オリジナルの EXPLAIN の出力はオペランドに数字が 3 つ並んでいるだけなので、vdbe.c のインストラクションの動作の記述とつきあわせないと何をやっているのか、わかりにくいことこの上ありませんでした。ちょっとしたことですが、オペランドの意味を把握しやすくするだけで、可読性が上がっています。

0019       Integer        8        *        *
0020       MustBeInt      0x0000   0        *
0021       MemStore       mem[1]   0x0001   *
  :
0024       Variable       var(1)   *        *
0025       MustBeInt      0x0000   0        *
0026       MemStore       mem[3]   0x0000   *
  :
0036 F0036 Integer        0        *        *
0037       OpenRead       cur[9]   index(thread_raise_id,7) keyinfo(1,BINARY)
0038       SetNumColumns  cur[9]   2        *
0039       Last           cur[9]   F0052    *
0040 L0040 MemIncr        -1       mem[3]   *
0041       IfMemNeg       mem[3]   F0043    *
0042       Goto           *        F0051    *
0043 F0043 IdxRowid       cur[9]   *        *
0044       NotNull        -1       F0047    *
0045       Pop            1        *        *
0046       Goto           *        F0049    *
0047 F0047 MakeRecord     1        0        'c'
0048       IdxInsert      cur[7]   0x0000   *
0049 F0049 MemIncr        -1       mem[1]   *
0050       IfMemZero      mem[1]   F0052    *
0051 F0051 Prev           cur[9]   L0040    *
0052 F0052 Close          cur[9]   *        *
0053 F0053 Rewind         cur[7]   F0128    *

モジュールのソースはだらだらと長いのですけど以下に。

package SQLite::ExplainPP;
use strict;
use warnings;
use DBI;
use DBI::Const::GetInfoType;

our $VERSION = "0.01";

sub new {
    my $class = shift;
    my($dbname) = @_;
    my $self = bless {}, $class;
    $self->{dbh} = DBI->connect("dbi:SQLite:dbname=$dbname", "", "")
        or die "cannot open dbname '$dbname'";
    $self;
}

sub DESTROY { shift->disconnect }

sub disconnect {
    my $self = shift;
    $self->{dbh}->disconnect if defined $self->{dbh};
    $self->{dbh} = undef;
    $self;
}

sub dbh { shift->{dbh} }

sub required_sqlite_version {
    my $self = shift;
    $self->{sqlite_version} ||= $self->dbh->get_info($GetInfoType{SQL_DBMS_VER});
    my $v = sprintf "%d%03d%03d", split /\./, $self->{sqlite_version};
    3_000_000 <= $v && $v < 3_005_005;
}

sub rootpage {
    my $self = shift;
    $self->{master} ||= $self->dbh->selectall_arrayref(q{
        SELECT type, name, rootpage FROM sqlite_master;
    });
    $self->{rootpage} ||= {
        map {
            my($type, $name, $rootpage) = @$_;
            $rootpage => {type => $type, name => $name};
        } @{$self->{master}}
    };
    $self->{rootpage};
}

my %Instruction = (
    # from Ubuntu Linux 8.04LTS sqlite3-0 source package.
    # sqlite3-3.4.2/src/vdbe.c 1.639 2007/07/26 06:50:06 danielk1977
    Goto => ['reserved', 'addr', 'reserved'],
    Gosub => ['reserved', 'addr', 'reserved'],
    Return => ['reserved', 'reserved', 'reserved'],
    Halt => ['integer', 'flag', 'string'],
    Integer => ['integer', 'reserved', 'reserved'],
    Int64 => ['reserved', 'reserved', 'string'],
    Real => ['reserved', 'reserved', 'string'],
    String8 => ['reserved', 'reserved', 'string'],
    String => ['integer', 'reserved', 'string'],
    Null => ['reserved', 'reserved', 'reserved'],
    HexBlob => ['reserved', 'reserved', 'string'],
    Blob => ['integer', 'reserved', 'string'],
    Variable => ['bindvar', 'reserved', 'reserved'],
    Pop => ['integer', 'reserved', 'reserved'],
    Dup => ['integer', 'flag', 'reserved'],
    Pull => ['integer', 'reserved', 'reserved'],
    Push => ['integer', 'reserved', 'reserved'],
    Callback => ['integer', 'reserved', 'reserved'],
    Concat => ['integer', 'flag', 'reserved'],
    Add => ['reserved', 'reserved', 'reserved'],
    Multiply => ['reserved', 'reserved', 'reserved'],
    Subtract => ['reserved', 'reserved', 'reserved'],
    Divide => ['reserved', 'reserved', 'reserved'],
    Remainder => ['reserved', 'reserved', 'reserved'],
    CollSeq => ['reserved', 'reserved', 'pointer'],
    Function => ['flag', 'integer', 'pointer'],
    BitAnd => ['reserved', 'reserved', 'reserved'],
    BitOr => ['reserved', 'reserved', 'reserved'],
    ShiftLeft => ['reserved', 'reserved', 'reserved'],
    ShiftRight => ['reserved', 'reserved', 'reserved'],
    AddImm => ['integer', 'reserved', 'reserved'],
    ForceInt => ['flag', 'addr', 'reserved'],
    MustBeInt => ['flag', 'addr', 'reserved'],
    RealAffinity => ['reserved', 'reserved', 'reserved'],
    ToText => ['reserved', 'reserved', 'reserved'],
    ToBlob => ['reserved', 'reserved', 'reserved'],
    ToNumeric => ['reserved', 'reserved', 'reserved'],
    ToInt => ['reserved', 'reserved', 'reserved'],
    ToReal => ['reserved', 'reserved', 'reserved'],
    Eq => ['flag', 'addr', 'pointer'],
    Ne => ['flag', 'addr', 'pointer'],
    Lt => ['flag', 'addr', 'pointer'],
    Le => ['flag', 'addr', 'pointer'],
    Gt => ['flag', 'addr', 'pointer'],
    Ge => ['flag', 'addr', 'pointer'],
    And => ['reserved', 'reserved', 'reserved'],
    Or => ['reserved', 'reserved', 'reserved'],
    Negative => ['reserved', 'reserved', 'reserved'],
    AbsValue => ['reserved', 'reserved', 'reserved'],
    Not => ['reserved', 'reserved', 'reserved'],
    BitNot => ['reserved', 'reserved', 'reserved'],
    Noop => ['reserved', 'reserved', 'reserved'],
    If => ['flag', 'addr', 'reserved'],
    IfNot => ['flag', 'addr', 'reserved'],
    IsNull => ['integer', 'addr', 'reserved'],
    NotNull => ['integer', 'addr', 'reserved'],
    SetNumColumns => ['cursor', 'integer', 'reserved'],
    Column => ['cursor', 'integer', 'pointer'],
    MakeRecord => ['integer', 'addr', 'string'],
    MakeIdxRec => ['integer', 'addr', 'string'],
    Statement => ['db', 'reserved', 'reserved'],
    AutoCommit => ['flag', 'flag', 'reserved'],
    Transaction => ['db', 'flag', 'reserved'],
    ReadCookie => ['db', 'cookie', 'reserved'],
    SetCookie => ['db', 'cookie', 'reserved'],
    VerifyCookie => ['db', 'integer', 'reserved'],
    OpenRead => ['cursor', 'rootpage', 'pointer'],
    OpenWrite => ['cursor', 'rootpage', 'pointer'],
    OpenEphemeral => ['cursor', 'integer', 'pointer'],
    OpenPseudo => ['cursor', 'reserved', 'reserved'],
    Close => ['cursor', 'reserved', 'reserved'],
    MoveGe => ['cursor', 'addr', 'reserved'],
    MoveGt => ['cursor', 'addr', 'reserved'],
    MoveLt => ['cursor', 'addr', 'reserved'],
    MoveLe => ['cursor', 'addr', 'reserved'],
    Distinct => ['cursor', 'addr', 'reserved'],
    Found => ['cursor', 'addr', 'reserved'],
    NotFount => ['cursor', 'addr', 'reserved'],
    IsUnique => ['cursor', 'addr', 'reserved'],
    NotExists => ['cursor', 'addr', 'reserved'],
    Sequence => ['cursor', 'reserved', 'reserved'],
    NewRowid => ['cursor', 'memory', 'reserved'],
    Insert => ['cursor', 'flag', 'string'],
    Delete => ['cursor', 'flag', 'reserved'],
    ResetCount => ['flag', 'reserved', 'reserved'],
    RowData => ['cursor', 'reserved', 'reserved'],
    RowKey => ['cursor', 'reserved', 'reserved'],
    Rowid => ['cursor', 'reserved', 'reserved'],
    NullRow => ['cursor', 'reserved', 'reserved'],
    Last => ['cursor', 'addr', 'reserved'],
    Sort => ['cursor', 'addr', 'reserved'],
    Rewind => ['cursor', 'addr', 'reserved'],
    Next => ['cursor', 'addr', 'reserved'],
    Prev => ['cursor', 'addr', 'reserved'],
    IdxInsert => ['cursor', 'flag', 'reserved'],
    IdxDelete => ['cursor', 'reserved', 'reserved'],
    IdxRowid => ['cursor', 'reserved', 'reserved'],
    IdxGT => ['cursor', 'addr', 'reserved'],
    IdxGE => ['cursor', 'addr', 'string'],
    IdxLT => ['cursor', 'addr', 'reserved'],
    IdxLE => ['cursor', 'addr', 'reserved'],
    Destroy => ['rootpage', 'db', 'reserved'],
    Clear => ['rootpage', 'db', 'reserved'],
    CreateTable => ['db', 'reserved', 'reserved'],
    CreateIndex => ['db', 'reserved', 'reserved'],
    ParseSchema => ['db', 'flag', 'pointer'],
    LoadAnalysis => ['db', 'reserved', 'reserved'],
    DropTable => ['db', 'reserved', 'string'],
    DropIndex => ['db', 'reserved', 'string'],
    DropTrigger => ['db', 'reserved', 'string'],
    IntegrityCk => ['memory', 'db', 'reserved'],
    FifoWrite => ['reserved', 'reserved', 'reserved'],
    FifoRead => ['reserved', 'reserved', 'reserved'],
    ContextPush => ['reserved', 'reserved', 'reserved'],
    ContextPop => ['reserved', 'reserved', 'reserved'],
    MemStore => ['memory', 'flag', 'reserved'],
    MemLoad => ['memory', 'reserved', 'reserved'],
    MemMax => ['memory', 'reserved', 'reserved'],
    MemIncr => ['integer', 'memory', 'reserved'],
    IfMemPos => ['memory', 'addr', 'reserved'],
    IfMemNeg => ['memory', 'addr', 'reserved'],
    IfMemZero => ['memory', 'addr', 'reserved'],
    MemNull => ['memory', 'reserved', 'reserved'],
    MemInt => ['integer', 'memory', 'reserved'],
    MemMove => ['memory', 'memory', 'reserved'],
    AggStep => ['memory', 'integer', 'pointer'],
    AggFinal => ['memory', 'integer', 'pointer'],
    Vacuum => ['reserved', 'reserved', 'reserved'],
    IncVacuum => ['db', 'addr', 'reserved'],
    Expire => ['flag', 'reserved', 'reserved'],
    TableLock => ['db', 'rootpage', 'string'],
    VBegin => ['reserved', 'reserved', 'pointer'],
    VCreate => ['db', 'reserved', 'string'],
    VDestroy => ['db', 'reserved', 'string'],
    VOpen => ['cursor', 'reserved', 'pointer'],
    VFilter => ['cursor', 'addr', 'string'],
    VRowid => ['cursor', 'reserved', 'reserved'],
    VColumn => ['cursor', 'integer', 'reserved'],
    VNext => ['cursor', 'addr', 'reserved'],
    VRename => ['reserved', 'reserved', 'pointer'],
    VUpdate => ['flag', 'flag', 'pointer'],
);

sub explain {
    my $self = shift;
    unless ($self->required_sqlite_version) {
        warn "require 3.0.0 <= sqlite_version < 3.5.5, but $self->{sqlite_version}";
        return;
    }
    my $explain = $self->dbh->selectall_arrayref(@_);
    $self->{label} = [('') x @$explain];
    for (@$explain) {
        my($addr, $mnemonic, @p) = @$_;
        for my $n (0 .. 2) {
            if ($Instruction{$mnemonic}[$n] eq 'addr'
                && $p[$n] != 0
                && $self->{label}[$p[$n]] ne 'L'
            ) {
                $self->{label}[$p[$n]] = $p[$n] < $addr ? 'L' : 'F';
            }
        }
    }
    my $output;
    for (@$explain) {
        my($addr, $mnemonic, @p) = @$_;
        for my $n (0 .. 2) {
            my $opland = 'opland_' . $Instruction{$mnemonic}[$n];
            $p[$n] = $self->$opland($p[$n]);
        }
        my $label =
            $self->{label}[$addr] ? $self->sprintf_label($addr)
            : "";
        $output .= sprintf "%04d %5s %-13s  %-8s %-8s %s\n",
            $addr, $label, $mnemonic, @p;  
    }
    $output;
}

sub sprintf_label {
    my $self = shift;
    my($addr) = @_;
    sprintf("%s%04d", $self->{label}[$addr], $addr);
}

sub opland_addr {
    my $self = shift;
    my($addr) = @_;
    $addr == 0 ? $addr : $self->sprintf_label($addr);
}

sub opland_bindvar {
    my $self = shift;
    my($no) = @_;
    "var($no)";
}

sub opland_cookie {
    my $self = shift;
    my($no) = @_;
    "cookie($no)";
}

sub opland_cursor {
    my $self = shift;
    my($no) = @_;
    "cur\[$_[0]\]";
}

sub opland_db {
    my $self = shift;
    my($no) = @_;
    "db($no)";
}

sub opland_flag {
    my $self = shift;
    my($i) = @_;
    sprintf "0x%04x", $i;
}

sub opland_integer {
    my $self = shift;
    my($i) = @_;
    "$i";
}

sub opland_memory {
    my $self = shift;
    my($no) = @_;
    "mem\[$_[0]\]";
}

sub opland_pointer {
    my $self = shift;
    my($desc) = @_;
    $desc || 'NULL';
}

sub opland_reserved { '*' }

sub opland_rootpage {
    my $self = shift;
    my($page) = @_;
    my $x = $self->rootpage->{$page};
    "$x->{type}($x->{name},$page)";
}

sub opland_string {
    my $self = shift;
    my($s) = @_;
    "'$s'";
}

1;

__END__

=head1 NAME

    SQLite::ExplainPP -- pretty formatter for VDBE of output of SQLite3 EXPLAIN.

=head1 AUTHOR

MIZUTANI, Tociyuki, E<lt>tociyuki\100gmail.comE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2008 by MIZUTANI, Tociyuki

This library is free software; you can redistribute it
and/or modify it under the same terms as Perl itself.

=cut