SELECTのAPI(4) インデックスの利用

SELECTのAPI(2) 様々なSELECT文への対応で述べた検索ではインデックスを活用した検索を実現できません。ここではインデックスを用いた検索のAPIを考えます。

例で使用するテーブル

これまで同様、次のようなテーブルがあるものとして話を進めます。

members: メンバーを格納したテーブル

member_id	family_name	first_name	age	sex	group_id
1011	中田	祐輔	22	0	1
1234	山下	沙希	17	1	2
1999	宮川	崇	18	0	2
2112	石田	菜々	21	1	1
2345	山崎	千佳	19	1	1

groups: メンバーの所属するグループを格納したテーブル

group_id	group_name
1	大学生
2	高校生

PerlShellKoherent::DBでのテーブルの取得

次のようにして、$membersと$groupsがTableオブジェクトを事前に保持しているものとします。また、結果格納用に$resultを定義してあるものとします。

my $members = $database->table('members');
my $groups = $database->table('groups');

my $result;

なぜインデックスを特別扱いするのか

様々な条件での検索を簡単に実装するために、ViewクラスのwhereメソッドではPerlのプログラムで検索条件を記述する仕様にしました。例えば、

-- 結婚可能なメンバーを取得（※日本では男性18歳以上、女性16歳以上が結婚可）
SELECT * FROM members WHERE (sex = 0 AND age >= 18) OR (sex = 1 AND age >= 16);

というSELECT文を考えてみましょう。サブルーチンを渡して複雑な条件を指定する代わりに、条件をオブジェクトで表す次のような方法も考えられます。

$result = $members->where(
        Or->new(
            And->new(
                Eq->new('sex', 0),
                Ge->new('age', 18)
                ),
            And->new(
                Eq->new('sex', 1),
                Ge->new('age', 16)
                )
            )
        );

この例では、Condition（条件）クラスを継承したAndクラスやOrクラス、Ge（以上：greater than or equal to)クラスのインスタンスを使って複雑な条件を表しています。しかし、この方法は見るからにわずらわしい上に可読性も損なっています。そのため、~~PerlShell~~Koherent::DBでは次のように条件サブルーチンを渡す仕様にしました。サブルーチンが真を返す行のみを結果として返します。

$result = $members->where(sub{
    my ($sex, $age) = ($_[0]->{'sex'}, $_[0]->{'age'});
    ($sex == 0 && $age >= 18) || ($sex == 1 && $age >=16);
});

見た目にはすっきりしますし余計なオブジェクトを生成する必要もありません。Perlのプログラムで直接条件を記述するため自由度が高く、また実装上もAndやOrのような演算子を改めて実装する必要がありません。

しかし、条件をコードで記述しているために（~~PerlShell~~Koherent::DB上での構文解析が発生しないために）、内部的にどのインデックスを活用すべきかを判断することが容易ではありません。AndクラスやOrクラスを使う方法であれば、その条件の構造を解析し適切なインデックスを利用して検索することもできるでしょう。

このような理由から、インデックスの利用に際してはwhereメソッドとは別のメソッドが必要となります。

インデックスを利用するメソッド

インデックスはテーブル、またはビューを対象として構築されます。そのため、インデックスを表すIndexオブジェクトは、対象となるViewオブジェクトから得るのが自然です。Indexクラスは検索のためのメソッドを提供し、それらの戻り値はViewオブジェクトとなります。

例えば、membersテーブルのプライマリーキーであるmember_idで検索をするためには、次のようなコードが考えられます。

# member_idが1234のメンバーを取得
result = $members->index('member_id')->eq(1234);

このときに指定している'member_id'はmember_idフィールドに対するインデックスという意味ではなく、membersテーブルに所属しているmember_idという名称のインデックスという意味になります。インデックスの名称はインデックスを作成する際に指定します。eqはイコールを表すメソッドです。

上記例のように、インデックスを利用するにはIndexオブジェクトを使用します。

基本的なレンジスキャン

~~PerlShell~~Koherent::DBでは、ツリーを用いてインデックスを実装し、レンジスキャン（範囲検索）を可能とする予定です。レンジスキャンを用いるには、レンジスキャン用のメソッド郡を提供する必要があります。

Indexクラスの基本的なレンジスキャン用メソッドとして、次のようなものを考えています。

# <, lt
$result = $members->index('member_id')->lt(1234);

# >, gt
$result = $members->index('member_id')->gt(1234);

# <=, le
$result = $members->index('member_id')->le(1234);

# >=, ge
$result = $members->index('member_id')->ge(1234);

BETWEEN

SQLのBETWEENを実現するために、betweenメソッドも提供しようと思います。

Indexクラスのgeメソッドとleメソッドを組み合わせてBETWEENを実現することはできません。これは、ツリーに対するレンジスキャンの結果に対してさらにレンジスキャンを実行する際に、インデックスの活用が困難なためです。このため、between用のメソッドを提供予定です。。

betweenメソッドは次のような使い方を考えています。

-- member_idが1000番台のメンバーを取得
SELECT * FROM members WHERE member_id BETWEEN 1000 AND 1999;

$result = $members->index('member_id')->between(1000, 1999);

複雑なレンジスキャン

BETWEENの他にも、1000 <= member_id < 2000のように、lt、gt、le、geを組み合わせたレンジスキャンが考えられます。これらのすべての組み合わせを用意することもできますが、冗長であるため次のようなrangeメソッドに集約することを考えています。

$result = $members->index('member_id')->range(1000, 1, 2000, 0);

rangeメソッドの引数は順に、「最小値、最小値を範囲に含むか、最大値、最大値を範囲に含むか」となります。

なお、このようなrangeメソッドを一つ実装するだけで、上記の様々なメソッドはすべて内部でrangeメソッドを呼び出すだけで実装できます。例えば、betweenメソッドはrange($min, 1, $max, 1)に対応します。最小値や最大値がundefの場合は、下限、上限がないものとすることで、ltやgt等のメソッドも実装できます。eqメソッドすらも、range($value, 1, $value, 1)として実装できますが、eqメソッドは多様されることになると考えられるため、パフォーマンスも考慮して独自実装した方が良いかもしれません（大したオーバーヘッドではないようにも思いますが）。

挟まれないレンジスキャン

12歳未満および65歳以上のような、上記のrangeメソッドで扱えないような範囲を検索する場合を考えます（ageに対してもインデックスが作成されているものとします）。

SQLで表すと

-- 12歳未満および65歳のメンバーを取得
SELECT * FROM members WHERE age < 12 OR age >= 60;

となります。~~PerlShell~~Koherent::DBでは、インデックスを利用した検索結果をunionメソッドで結合することでこれを実現します。

$result = $members->index('age')->lt(12)->union(
        $members->index('age')->ge(60));

インデックスを用いた結合

結合時にインデックスを用いるにも、独自メソッドを利用する必要があります。

SELECT * FROM members
INNER JOIN groups ON members.group_id = groups.group_id;

上記のような内部結合は、インデックスを用いない場合、次のようなコードで表されていました。

$result = $members->inner_join($groups, sub{
    my ($outer_row, $inner_row) = @_;
    $outer_row->{'group_id'} == $inner_row->{'group_id'};
});

しかし、上記のように外部表と内部表のそれぞれの行に対する結合条件を記述したのではインデックスを活用できません。なぜなら、外部表の行に対して内部表全体から検索を行う際にインデックスを利用するからです。そのため、インデックスを利用して結合を行う場合、行と行ではなく、行と表（のインデックス）に対する検索ロジックを記述しなければなりません。例えば、次のようなコードが考えられます。

$result = $members->index_inner_join($groups->index('group_id'), sub{
    my ($outer_row, $inner_index) = @_;
    $inner_index->eq($outer_row->{'group_id'});
});

引数のサブルーチンが返す行のみが、結合結果として採用されます。

考察

インデックス使用時、未使用時でメソッドを区別することで、ユーザは検索の度にインデックスを意識しなければならならなくなります。このため、SQLのような手軽な（オプティマイザが自動で適切にインデックスを選択してくれるような）検索はできなくなってしまいます。

一方で、SQLであってもパフォーマンスを意識すればインデックスの使用を考慮する必要があり、適切なインデックス利用のためのSQLチューニングが行われることも多々あります。Oracleのヒント句のように、インデックスの使用方法を無理やり指定することすらあります。そのような事情を考えると、初めからインデックスの使用方法を指定する仕様の方が望ましいとも考えられます。

僕の私見では、インデックスの仕組みを学ぶ機会もないままにSQLのようなインデックスの利用が隠蔽された環境におかれると、インデックスが何を行っているのか、インデックスには何ができるのかを理解していないケースも多いのではないかと思います。インデックスの使用方法の明記を強制することで開発者の理解が進み、インデックスの効かないとんでもない検索クエリが記述されてしまうケースを減らすことができるのではないでしょうか。

まとめ

~~PerlShell~~Koherent::DBでは、インデックスを利用して検索を行うにはIndexクラスのメソッドを用いる仕様とします（whereメソッドの内部で自動的にインデックスを活用するような実装は仕様上困難です）。IndexオブジェクトはViewオブジェクトのindexメソッドで取得します。

通常の検索とインデックスを用いた検索を分離することで、ユーザは検索の度にインデックスの利用を意識しなければならなくなります。しかし、実際には一つ一つのSQLのパフォーマンスが検討されることも多いため、インデックスの使用方法を明記しなければならないことが、一概に悪いとは言えません。また個人的には、インデックスを意識した記述が強制されることによって、インデックスが有効でない的外れな検索クエリを書いてしまうケースも減らせるのではないかと思います。