BizStationブログ

ビズステーション株式会社の公式ブログです。

Transactd 2.0　その２ QueryBuilder で簡単NoSQLクエリー

Transactd

Transactd 2.0　その１では、読み取りクエリーの使い方について説明しました。今回は、そのクエリーのコードをVisualに生成できるツール、QueryBuilderを紹介します。

QueryBuilderでは、GUIの画面上でデータベースやテーブル、キーの値や条件などを指定すると、Transactdのクエリーのソースコードを生成できます。生成されたコードをコピーアンドペーストするだけでデータベースへのアクセスコードが完成します。開発工程をとても簡単にしてくれること間違いなしです。生成可能ソースの言語は現在のところ、C++/PHP/Ruby/JScriptです。
また、実際にクエリーを実行して結果や実行速度を確認することもできます。

QueryBuilderのインストール

QueryBuilderは現在Beta版で、WindowsとMac OS X版があります。どちらも、インストールパッケージで簡単にインストールできます。アンインストールもクリーンにできますので是非試してみてください。


追記 (2016/11/15)
以前のバージョンはバグがありますので最新版(バージョン 3.6)のみリンクします。サーバーPluginも Version 3.6を使用してください。



f:id:bizstation:20140925162219p:plain

それでは、実際の使い方をご紹介します。（実際の操作を行うには、Transactdのサーバーが必要です。Pluginのインストールはこちら）

テーブルからレコードを読み取る

＋ボタンをクリックしてウィザードを開始します。

f:id:bizstation:20140925162734p:plain
データベースのホスト名、データベース名、スキーマテーブルの名前を指定して[Next]をクリックします。スキーマテーブルを特別に用意していない場合はtransactd_schemaと指定してください。自動で、そのデータベースのスキーマが生成されます。

f:id:bizstation:20140925163039p:plain
[Table]にテーブルリストが表示されるので、読み取るテーブルと、インデックスを選択します。
ユニークなインデックスにはグリーンのマークがついています。[Key Values]には指定したインデックスのフィールド名タイプとサイズが表示されます。
[Value]列に読み取りを開始するインデックスの値を入力します。もし、テーブルの先頭なら、最も小さな値を指定します。[Next]をクリックします。

f:id:bizstation:20140925163631p:plain
ここでは、SQLのselectに相当する、列の選択を行います。
左側に選択したテーブルのフィールド一覧が表示されるので、必要なフィールドを→ボタンで右側のリストに加えていきます。この例では4つのフィールドすべてを追加しています。
また、フィールド名に別名を付ける場合は[Alias]に別名を入力します。例では「名前」フィールドを「name」としています。[Next]をクリックします。

f:id:bizstation:20140925183028p:plain
最後に抽出条件を指定します。検索の始まりは最初に指定したので、終わりをid <= 100とします。左側のフィールド一覧からidを、右側の Record match conditions に→ボタンで入れます。[Conditions]に<= 100と入力します。 [Finish]をクリックして完了です。

ソースコードの表示

[PHP]タブをクリックするとクエリーの内容がPHPのソースコードで表示されます。
f:id:bizstation:20140925185503p:plain
生成されたソース全体は、単独で動作するように書かれていますが、クエリーの部分だけが選択された状態で表示されます。選択された部分のコードは以下のようになっています。

    $cp1 = new bzs\connectParams('tdap://localhost/querytest?dbfile=test.bdf');
    $db->c_use($cp1);
    $at1 = new bzs\activeTable($db, 'user');
    $at1->alias('名前', 'name');
    $q = new bzs\query();
    $q->select('id', 'name', 'group', 'tel');
    $q->where('id', '<=', '100')
        ->reject(65535)->limit(0)->optimize(bzs\queryBase::none);
    $rs = $at1->index(0)->keyValue('0')->read($q);

最後の行の$rsが結果のrecordsetです。

データの表示

[Run]をクリックするとクエリーを実行して、結果が[Data view]に表示されます。
f:id:bizstation:20140925190339p:plain
右上の緑のステータスの横には実行時間が表示されます。このサンプルはWindows 32Bitで表示していますが、WindowsのGetTickCountの計測の誤差が0.015程度あるため0と表示されています。100レコード程度だとほとんど0.01秒以下です。

Joinする

それでは次に上記の結果のgroupフィールドからそのグループの名前をJoinしてみます。グループの名前はgroupsテーブルから参照します。+ボタンをクリックしてウィザードを開始します。
f:id:bizstation:20140925191504p:plain
Joinを選択して[Next]をクリックします。

f:id:bizstation:20140925191648p:plain
再びデータベースを選択する画面が出ます。すなわち異なるデータベースのテーブルもJoin可能です。同じデータベースなら、[Current database]を選択します。

f:id:bizstation:20140925191941p:plain
ここでは、Joinするテーブルを選択し、レコードの検索に使うインデックスを指定します。
通常、マスタの参照であればユニークキーが存在します。groupsテーブルでは一意なフィールドがidではなくcodeというフィールドになっています。
次に、左側のレコードセット内のフィールドからJoinのキーとなるフィールドを選択し→ボタンで右側の[Join Key(s)]に入れます。これで、user.groupとgroups.codeでJoinするという意味になります。
[Next]をクリックします。

f:id:bizstation:20140925192658p:plain
最後に、groupsテーブルから追加する列を選択します。ここではnameを選択しました。またgroup_nameという別名を付けました。[Finish]をクリックして完了です。

それでは生成されたソースコードを見てみまししょう。先程と同じ部分は省きます。

    $cp2 = new bzs\connectParams('tdap://192.168.3.155/querytest?dbfile=test.bdf');
    $db->c_use($cp2);
    $at2 = new bzs\activeTable($db, 'groups');
    $at2->alias('name', 'group_name');
    $q->reset()->select('name')->optimize(bzs\queryBase::none);
    $at2->index(0)->join($rs, $q, 'group');

最初の行で異なるサーバーが指定されています。分散クエリーが簡単に実行できます。
実行すると、group_nameフィールドが追加されているのがわかります。実行時間もJoinをしても0.01秒以下で実行できています。
f:id:bizstation:20140925194826p:plain

GropuByをしてみる

次に、groupフィールドでグルーピングしてみましょう。各グループに属するユーザーの数をカウントしてみます。

先ほどまで同様、+ボタンをクリックしてウィザードを開始します。
[Operation]ページで[GroupBy]を選択して[Next]をクリックします。
[GroupBy]ページが表示されます。
f:id:bizstation:20140925203903p:plain

左上にrecordset内のフィールド一覧が表示されます。ここから、グルーピングのキーになるフィールドを→ボタンで[Grouping Key]に入れます。
次に、[Functions]からCountを選択し、→ボタンをクリックします。画像がありませんが、ここでカウントした結果を入れる列名を聞かれます。ここではuser_countとしました。
[Registerd　function]にはCountのresultがuser_countであると表示されています。[Finish]をクリックして完了です。

追加されたソースコードと実行結果を見てみましょう。

    $q = new bzs\groupQuery();
    $gq->keyField('group');
    $fns = new bzs\fieldNames();
    $fns->reset()->keyField();
    $f0 = new bzs\count(fns, 'user_count');
    $gq->addFunction($f0);
    $rs->groupBy(gq);

f:id:bizstation:20140925200503p:plain

各グループ20Usersづつであることがわかります。これでもまだ実行時間は0.01秒以下です。上段の[Records]列を見てください。Read Join GroupByの後のレコード数が表示されています。それまで100レコードだったものが、 GroupByで5レコードになったことがわかります。

まとめ

QueryBuilderはいかがだったでしょうか？　是非皆さんもダウンロードして試してみてください。簡単にTransactdのクエリーを作成できること、分散クエリーが可能なこと、高速に実行できることをお分りいただけると思います。
そして何より、開発がとてもスピーディーで正確で簡単になります。

Transactd 2.0　その１ SQLライクなクエリーと結果セット

Transactd

いよいよTransactd 2.0をリリースします。Transactd 2.0で新しくなったのは主に、

です。順番に詳しく紹介してゆきます。今回は「SQLライクなクエリーと結果セット」です。

Transactdの従来のAPIはどんな処理でも書けますが、SQLに慣れた方にとってはあまりフレンドリーではありませんでした。特に、JOINを含むレコードの読み取り・検索は、SQLと大きく異なっています。

そこで、SQLライクなAPIと結果を扱うrecordsetクラスが追加されました。
recordsetは連想配列によく似た結果セットで、rs[1]["name"]のように行・列に添え字でアクセスできます。内部実装はC++であり、PHPでもRubyでも高速にSQLライクな処理ができます。

紹介のためのサンプルコードはPHPで書いてみます。メソッドの多くは、メソッドチェーンができるようにthisを返します。

OrderBy

まずはOrderBy。recordset rsの"group"というフィールドで昇順にソートします。

$rs->orderBy("group");

逆順にするにはreverseを使います。

$rs->reverse();

複数のフィールドで昇順、降順が混在するorderByは、sortFieldsクラスにフィールド名とソート方向をセットして渡します。
"group"で昇順、"tel"で降順にするならば、以下のようになります。

$sort = new sortFields();
$sort->add('group', true).add('tel', false);
$rs->orderBy($sort);

GroupBy

次に、GroupBy。groupQueryオブジェクトに情報をセットしてrecordsetのgroupByメソッドに渡します。

$gq = new groupQuery();
...
$rs->groupBy(gq);

groupQueryオブジェクトには複数の計算関数を追加できます。計算関数には対象のフィールドと結果フィールド名を指定します。
例は"group"でグルーピングして各グループに属するレコード数をカウントし"group_count"という列に結果を格納します。

$gq = new groupQuery();
$gq->keyField("group");           //<---- グルーピングキーフィールドの指定
$func = new count("group_count"); //<---- 関数と結果列名の指定
$gq->addFunction($func);
$rs->groupBy($gq);

計算関数にはcountの他にもsum avg min max があります。

レコードごとに計算の対象とするかどうか指定したいときは、計算関数のオブジェクトにWhenフィルタを設定できます。
"tel" != ""の時だけカウントしたいときは

$func->when("tel", "<>" ,"");

でフィルタリングできます。さらに、

$func->when("tel", "<>" ,"")->and_("tel" , "<>", "090*");

のようにand_()とor_()条件をメソッドチェーンでつないでいくこともできます。

また、グルーピングのキーは複数のフィールドとすることもできます。

$gq->keyField("group", "name");

MatchBy

結果セットを得たあとでフィルタリングできます。マッチしない行はレコードセットから削除されます。

$rq = new recordsetQuery();
$rq->when("count", ">", "1")->and_("tel", "<>", "");
$rs->matchBy(rq);

途中結果をコピー

SQLでは完全な結果を得るのみで途中結果を得ることはできません。
たとえば、売上の明細とその金額の合計を得たい場合、明細のクエリーと合計を計算するクエリーの２つが必要です。（または合計を自分で計算します。）

Transactdでは、処理をクライアント側で順番に書くので、途中の結果セットをコピーしておくだけで両方の結果を得られ効率的です。

...
$rs2 = $rs->clone();
...

$rs2->groupBy(gq);
// rsは明細
// rs2は合計

レコードの読み取り

新しく読取専用クラスactiveTableクラスが追加されました。
activeTableには主に、read, join, outerJoin の3つのメソッドしかありません。

まずは単純なreadです。データベースとテーブル名からactiveTableを生成します。
次にクエリーを作成してactiveTableのreadに渡すと、結果セット(recordset)が返ってきます。
クエリーは、select, where, and_, or_メソッドでSQLライクに記述できます。

まず、activeTableを生成します。

$at = new activeTable($db, "user");

検索には必ずindexを番号で指定します。

$at->index(1);

検索を開始する先頭レコードの値を指定します。この値はindexで指定したキーフィールドの値です。

$at->index(1)->keyValue("1");

次にqueryオブジェクトを使ってSQLライクに検索条件を指定します。

$q = new query();
$q->select("id", "名前", "group")->where("group", "<=", "2")

フィールドに別名を付けてみましょう。

$at->alias("名前", "name");

最後に読み取ります。

$rs = $at->read($q);

全部まとめて書くと (クラスの名前空間 BizStation\Transactdは省略)

$db = new database();
$uri = "tdap://localhost/test?dbfile=transactd_schema";
$db->open($uri, TYPE_BDF, OPEN_NORMAL);
$at = new activeTable($db, "user");
$at->alias("名前", "name");
$q = new query();
$q->select("id", "name", "group")->where("group", "<=", "2")
$rs = $at->index(1)->keyValue("1")->read($q);

すべてのフィールドを読み取りたいときは、selectメソッドでフィールドを選択しないようにします。そうするとデフォルトですべてのフィールドが返ります。

$q = new query();
$q->where("group", "<=", "2")
$rs = $at->index(1)->keyValue("1")->read($q);

Join

さあいよいよ、核心のJoinです。
TransactdのJoinは、サーバーサイドでの結合ではなく、クライアントサイドで結合します。結合は、事前に取得したrecordsetとこれから読み取るテーブルのフィールドで行います。

1対1のJoin

例として$rsは上記の結果セットとします。結果セットのgroupフィールドにはグループ番号が入っています。この番号からgroupsテーブルのnameフィールドをJoinしてみましょう。

$at2 = new activeTable($db, "groups");
$q->select('name');                      //<--- name 列だけを読み取る
$at2->index(0)->join($rs, $q, "group");  //<--- 結果セットのgroup列をキーとしてJoin

これで、userテーブルから得た結果セットにgroupsテーブルのnameフィールドをJoinしました。
内部では、recordsetのgroupフィールドの値をgroupsのindexで指定されたキーフィールドにセットして、順にレコードを取得しています。この場合、index(0)はgroupsのプライマリーユニークキーなので、1対1のJoinです。

キーがマルチセグメントキーの場合は、それぞれのセグメントに対応するrecordsetのフィールドを指定します。
例えば、groupsテーブルのprimaryキーがtype + idなら

$at2 = new activeTable($db, "groups");
$q->select('name');                      
$at2->index(0)->join($rs, $q, "type", "group");

のようにjoinメソッドで2つのフィールドを指定します。

1対多のJoin

recordset内の1行に対して複数の相手レコードがある場合は1対多のJoinです。
この場合、指定するインデックスは、重複可能なキーか、マルチセグメントのユニークキーで下位セグメントが未指定、のどちらかになります。

呼び出しは、1対1の場合とほとんど同じです。
例えば、まず、1～100までのグループを読み取って、そこに属するユーザーをすべてJoinしてみましょう。
最初に1～100までのグループを読み取ります。

$at = new activeTable($db, "groups");
$at2->alias("id", "group_id");
$q = new query();
$q->where("group_id", "<=", "100")
$rs = $at->index(0)->keyValue("1")->read($q);

次にそこに属するユーザーのidとnameをJoinしてみます。

$at2 = new activeTable($db, "user");
$at2->alias("名前", "name");
$q->reset()->select("id", "name");
$at2->index(1)->join($rs, $q, "group_id"); //<--- index=1はgroupフィールドの重複可キー

これで、読み取ることができました。$q->reset()はqueryオブジェクトの状態をリセットして、オブジェクトを使いまわすために呼び出しています。

OuterJoin

先ほどまでのJoinはInnerJoinで、相手レコードが見つからなかった場合は、recordset内の該当行は削除されます。
それに対して、outerJoinメソッドを使うと、相手レコードが見つからなかった場合でも元の行はそのまま残ります。追加されたフィールドは数値0、文字列では""で初期化されます。

Joinの最適化オプション

TransactdのJoinには1つだけ最適化オプションがあります。

Joinメソッドは、テーブルにアクセスする前にrecordset内の各行のJoinのキー値を調べ、同じものがないか走査し、同値であればそれを省いて読み取りを行い、読み取られるレコード数を削減する処理を行っています。
ところが、各行のJoinのキー値がユニークであることがわかっている場合、この処理はムダです。そこでこの処理を省くためのオプションが用意されています。

recordesetの各行のJoinキー値がユニークであるとわかっている場合は、queryオブジェクトのoptimizeにjoinHasOneOrHasManyを指定します。

$q->optimize(joinHasOneOrHasMany);

Union

2つのrecordsetオブジェクトを和結合できます。$rsに$rs2を結合します。

$rs->unionRecordset($rs2);

ただし、2つのレコードセットのフィールドは順序も含めてすべて同じでなければなりません。もし異なる場合は例外が投げられます。

recordsetのそのほかの主な機能

列情報

recordsetのfieldDefsメソッドで、fielddefオブジェクトのコレクションが返ります。
例は、すべてのフィールド名を表示しています。

$fds = $rs->fieldDefs();
foreach($fd in $fds)
{
   echo $fd->name().PHP_EOL;
}
Top

recordsetの先頭N行を返します。

$rsTop10 = new recordset(); 
$rs->top($rsTop10, 10)

もし、$rsの行数がN行に満たない場合は、すべての行がコピーされます。

FirstとLast

先頭行および最終行を返します。

$firstRecord = $rs->first();
$lastRecord = $rs->last();
removeField

N番目(0Orign)の列を削除します。

$rs->removeField(3);
appendField

列を末尾に追加します。

$rs->appendField("mobile phone", ft_myvarchar/*type*/, 30/*length*/);

まとめ

ご覧いただいた通り、activeTableとrecordsetの組み合わせを用いると、SQLライクな表現でSQLとほぼ同様な結果を簡単に得ることができます。処理の順序や使用するインデックスの指定など、プログラマの思い通りにとてもフレキシブルに処理できます。

なお、SQLのような四則演算や文字列操作関数は、各言語で可能な処理ですので用意されていません。SQLは独立した言語なのでそのような関数がありますが、TransactdはAPIですので、それぞれの言語に用意されたものを使用できます。

次回は、今回ご紹介したSQLライクなクエリーのソースコードををVisualな操作で簡単に生成してくれる、queryBuilderをご紹介したいと思います。queryBuilderは、WindowsとMac OS X 10.9 でC++/PHP/Ruby/JScriptのソースを生成できます。

C++ クロスプラットフォーム開発

C++

しばらく日が空いてしまいましたが、相変わらず元気に Transactdに励んでいます。もうすぐTransactd 2.0 がリリースできそうですが、今回はC++コンパイラーのお話しです。
Transactdの基本ライブラリ開発はC++ですが、OSはWindwosとLinuxとMac OS X、コンパイラーはVisual Studio, C++Builder 32Bit 64Bit, Clang, GCC とマルチプラットフォーム、マルチコンパイラです。でもこれをワンソースでコンパイルできています。日々これらと格闘していると色々見えてきたのでその辺を書き留めておきたいと思います。

文字コード

Linux、Mac OS Xではutf8で書いていますが、定数に日本語文字列を使うときは、ソースファイルを必ずutf8 BOM付で保存します。LinuxではBOMなしが標準ですが、Visual Studio (VS)はBOMなしを理解してくれないのでBOM付にします。（4.3以前の古いGCCはBOMを読めないので注意）
WindowsではUNICODEが標準ですので、文字列は通常ワイド文字として扱います。WindowsとLinuxの両方に対応するために、文字列はすべて _T _TCHARマクロを使い、Linux用のtchar.hを作成して両方に対応させています。最初は少し面倒ですが、慣れればあまり大した問題ではないですね。

template

テンプレートへの対応は、C++Builder 32Bitが一番おバカで、これでコンパイルが通ればあとのコンパイラーは何とかなります。ですので、最初のコンパイルはほとんどC++Builder32です。このコンパイラーのテンプレート解析のクセはVSに通じるものがあって、Borlandの開発者はVSでコンパイルでできてC++Builderでできないものを潰していったのでは？と感じるものがあります。
GCCとClangは似ているようでそうでもなく、VSとも違い３つのコンパイラーはそれぞれ我が道を行っています。そうは言ってもC++ですので、それぞれに意味のあるエラーを吐いてくれます。あちらをとればこちらがダメといったことはかなり少なく、普通のテンプレートほとんど共通で使えます。

boost

boost様々です。プラットフォームに依存する部分をboostで書けばワンソースでマルチプラットフォームが簡単です。thread mutex socketやfile TESTなんかもそうです。ただ、C++Builder32はboost 1.39までしかサポートされていないので、それ以上の機能を使うときだけ、C++Builder32では少し異なる実装をする時があります。GCC VS Clangでは最新のboostが使えます。

デバッガ

Visualなデバッガに慣れてしまっていると Linuxでのgdbは悲惨です。Windowsでのデバッグの3倍くらいの時間がかかってしまいます。デバッグにはC++Builder32がお勧めです。理由は

  1. コンパイルがとても速い
  2. 充実した機能(実行コマンドライン、Visualで変数値のツールチップ表示やメモリダンプ、呼び出し履歴など)
  3. 安定動作

何よりも、コンパイルが早いのがデバッグには最高です。直す -> コンパイル -> 実行 -> デバッグ のサイクルをとても早く回すことができます。機能も欲しいものはほとんど揃っています。唯一、条件付きのブレークポイントが設定できませんのでif文を入れてブレークしたりしています。
gdbはLinuxでの動作に問題があるときにたまに使うくらいであまり頻繁には使いません。Linuxではデバッグ用のコード埋め込みやダンプのほうがやり易い場合も多くあります。
C++Builder 64Bit Clangのデバッガはリモートデバッグに似ていて、IDEとの間に別プロセスのエージェントが入っています。今のところこれがハングしてしまうことが多く、一応使えますが、安定して快適にとうところにはなっていません。

コンパイラとプラットフォームの違いの吸収

コンパイラによって Cライブラリやpragmaなどが違っています。また、プラットフォームでパスのセパレータやスレッドローカルストレージなど様々なものが異なっています。
これらは、それぞれ、compiler.hとcrosscompile.hにまとめ、マクロでの切り替えや不足する関数を補って、通常のソースは共通のコードで書けるようにしています。あまりきれいなソースではありませんが違いを埋めるマクロが詰まっていて、苦労の逸品？です。(transactdのソースにありますのでGPLv2で使えます。)

最適化

C++Builder32を少しお勧めしましたが、最大の欠点は実行速度が遅いことです。VSの1.5倍くらい遅くなります。PHPやRubyからVSでコンパイルしたTransactdクライアントを使うと、C++Builder32のネイティブより高速にDB処理ができます。ただ実際はDB処理だけではありませんので、総合的にはC++Builderの方が早くはなると思いますが、糊（スクリプト言語）の実行速度は下で働くC/C++ライブラリの最適化と、どれだけライブラリ呼び出しだけで済ませるかにかかるかと思います。スクリプト言語でゴリゴリ書くとスローになってしまいます。（TransactdのPHP,Ruby,ActiveXはすべてVSかGCCですのでご心配なく）
64Bit Windows上のVSとClangではVSの方が数パーセント速そうで、やはり総合的に見てVSはベターなコンパイラです。

まとめ

コンパイラ比較になってしまいましたが、個人的な好き嫌いで違いをまとめておきます。

コンパイラ 実行速度 コンパイル速度 デバッグしやすさ IDEの好み
GCC ◎ 〇 ☓ 使ってない
VisualStudio ◎ △(遅く感じる) 〇 〇
Clang(C++Builder64) 〇 〇 △ ◎
C++Builder32 △ ◎ ◎ ◎
何か参考になればいいですが。

次は、Transactd 2.0に乞うご期待！

第2回 MariaDB/MySQL コミュニティ イベント in Tokyo に行ってきました

MySQL MariaDB Transactd

2014/02/18に 第2回 MariaDB/MySQL コミュニティ イベント in Tokyo に行ってきました。
懇親会ではMontyさんと写真を撮ったり、色々なプラグインの開発者の方とお会いしたりでとても有意義な会でした。

MariaDBカンファレンスの感想

Montyさんがお見えになっていたこともあり、主題はMariaDBの紹介でした。簡単に言うと「MySQL5.6は出来が悪いよ。」「MariaDB10.0は品質、機能ともにMySQL5.6よりいいよ！」という感じでした。具体的には、

  • MySQL5.6のGTIDは設計がダメなので使えない。MariaDB10では設計を変更し完全に書き直している。
  • MariaDBは10.0より前からオプティマイザを改良していて、MySQLより5～ 20%位は速い、場合によっては10倍といったこともある。
  • バグが少なく、コミュニティからのフィードバックを積極的に受け入れサポート熱心。

一方の主張だけなので話半分くらいに聞いたとしても、MariaDBの方がいいのかな？といった印象を受けました。あと、多くの大口ユーザーやLinuxディストリビューションがMariaDBに移行したり、移行の予定を宣言している事例があげられました。ただ、その多くは実際の移行はまだこれからという感じでした。

MariaDB FEEDBACK PLUGIN

今まで知らなかったのですが、MariaDBの動作環境や構成をMariaDB Foundationに送信するための「MariaDB FEEDBACK PLUGIN」というプラグインがあり、集まった統計情報はWebで誰でも見られるようになっているそうです。
すべてのMariaDBインスタンスでこのプラグインが有効になっているわけではないですが、興味深い情報が見られます。

HandlerSocketの樋口さんとお話ししてきました！

いろいろな話をさせていただいたのですが、「SQLは得意ではない」は面白かったです。自分も樋口さんと同様、SQLは苦手で、「オプティマイザに依存すると検索パフォーマンスが読みづらい」という点で全く同じ意見でした。

HandlerSocket 2.0 と Transactd 狙いの違い

樋口さんは、HandlerSocketの検索条件の指定内容の強化とJoinを進められるとのことでした。

対してTransactdはと言いますと、1つのテーブルに対する検索条件はほぼ完全なのでこのままです。サーバー側でJoinする予定はいまのところありません（クライアント側でします）。

サーバー側でJoinをしてしまうと、テーブル単位でのシンプルなサーバー分割ができなくなってしまいます。ではなぜ樋口さんはJoinの実装を進められるかというと、NoSQLのメリットよりも、高速なSQL代替を狙っておられるようです。実際、ユニークインデックスでのJoinはSQLよりもかなり高速にできるようです。NoSQLのメリットを重視しているTransactdとは狙っているものがやや異なっていますが、Transactdでもやれば同じように出来るな、と思いました。

MroongaやSpiderの斯波さんと少しだけお話ししてきました

SpiderがHandler Interfaceに対応されたとのことで、Transactdも大丈夫かな？と思いお聞きしたとことろ、主にHandler Interfaceの下で動作しているので「たぶん大丈夫ですよ」とのこと。thdからの情報もかなり利用されるとのことで、うまくいかない場合はTransactdからthdへの情報提供を確認すれば良さそうです。今後確認していきたいと思います。

オプティマイザの神話

いろいろな方とお話しする中で、「テーブルアクセスは検索対象(範囲)のインデックスがあるか、無いかしかない。無い場合は、フルスキャンまたは範囲スキャンしかない」ということはあまり知られていないと感じました。ISAMライクなテーブルにインデックスを使ってナビゲートするプログラムを開発した経験のない方は、SQLしか使ったことが無い場合が多いでしょうから、やむを得ないのかも知れません。
中には「オプティマイザは想像を絶するミラクルなアクセスをしてくれる！」「まさかフルスキャンなんてしないだろう」と考えている方もいらっしゃるようです。
また、「少し複雑なSQL文になると、NoSQLだけでは自分（のプログラムコード）で同じ結果を得ることはできない」とも思われているようで（できないものは無いのですが）、TransactdなどNoSQLの利用の「カベ」はそのような誤解にあるとも思いました。

そしてTransactdは

今回のイベントの参加で、Transactdの今後の方向性もより明確に出来たように思っています。
アプリケーションの多くは テーブルの1レコード ≒ オブジェクトの1インスタンス という関連を持たせています。ところがテーブルをJoinすると、そのデータは「どのオブジェクトなのか」？どれでもない中途半端なものになります。このため、Transactdが提供する「テーブル単位のみのアクセス」は、O/Rマッピングとはとても相性の良いものになります。

また、Transactdを使うにあたり多くの方にとって障害になるのは、JoinやOrderByといった処理をどうするかということです。そのような点を踏まえて今後は、

  • コードからパフォーマンスを読めるようなAPIの作成
  • 簡単なO/Rマッピングと包含オブジェクトの読み取り（Joinに相当）APIの作成

といったことを中心に進めていきたいと思います。

これだけでは抽象的なので、C++の一例をあげます。
まずはパフォーマンスを読めるようなAPI。「idが10000から10100且つstatusが1のユーザー」をusersオブジェクトに読み取ります。usersはuserのコレクションです。

std::vector<user> users;
query.where("id", "<", 10100).and("status", "=", 1).reject(0);
userTable.index(key_id).keyValue(10000).read(users, query);

次はJoin相当の例です。userクラスにはそのユーザーが属するグループのオブジェクトを包含しています。user::grp()でグループオブジェクトが返るとします。例ではグループオブジェクトのデータを全ては読み取らず、nameのみ読み取っています。

query.select(_T("id"), _T("name"))
groupTable.index(key_id).readEach(users, &user::grp, query);

これでuser情報と所属するgroup名を読み取りました。
こんな感じのAPIを、C++だけでなくPHPなどにも用意していきたいと思います。

あ、それと、MariaDB-10.0.8用Trnasactdバイナリをダウンロードできるようにしておきました。
皆さんも是非mariaDB-10.0.8でTransactd 1.2を試してみてください。

MySQL パフォーマンスとtransactd その３

MySQL Transactd

その３は、select * from tablename where fieldname in(a,b,c...)です。
IN句による検索ですが、前回までと同じようにMySQLの処理とtransactdでの処理を見ていきましょう。

使用するインデックス解析

MySQLはまずSQL文を解析し、fieldnameフィールドをキーセグメントの先頭に持つインデックスが存在するか調べます。存在すれば、そのインデックスを使用したオペレーションhandler::ha_index_read_map(HA_READ_KEY_EXACT)を使い操作を組み立てます。無ければ、handler::ha_rnd_next()かhandler::ha_index_next()を使ったレコードスキャンをします。
どちらの方法であっても、a b cをfieldnameで示されたフィールドの型に従って昇順にソートした上で処理していきます。

MySQLでfieldnameフィールドのインデックスがある場合

handler::ha_index_read_map(HA_READ_KEY_EXACT)でa b cのソート結果順に検索し結果を返します。インデックスを使っての検索なので、重複を許可しない1セグメントのキーの場合は、1つあたりの検索時間は一定で、条件の個数分で検索が終了します。重複を許可するもしくはマルチセグメントのキーの場合は、handler::ha_index_next_sameを使って同じ値のレコードをすべて返します。
もし、見つからない値があってもエラーにはならず、その値の結果が返らないだけです。

MySQLでfieldnameフィールドのインデックスが無い場合

インデックスがないと、目的のレコードがどこにあるか不明なので、handler::ha_rnd_next()かhandler::ha_index_next()を使って先頭からすべてのレコードを検索します。当然複数レコードが存在する場合もあるので、最後のレコードまで完全にスキャンします。100万レコードあれば100万行すべてをスキャンすることになります。

transactdでfieldnameフィールドのインデックスがある場合

transactdでは、table::setKeyNum(keyNumber)でユニークなインデックスを指定し、table::setFilter("in a,b,c")で、値を列挙します。フィールド名を指定しないのは、インデックスを指定しているため、そのインデックスのフィールド値を列挙することが前提となるからです。もし、setKeyNum()でユニークなキーを指定しなかった場合は、setFilter()でstat()がSTATUS_FILTERSTRING_ERRORを返します。すなわち、in句ではユニークなキーでしか検索できません。ですので、意図せず全レコードをスキャンしてしまうことはありません。
インデックスが無い場合でのinに相当する処理は"fieldname = a or fieldname = b or fieldname = c"のフィルターを使えば実現できます。
また、複数セグメントでユニークなキーの場合は、セグメントの数だけ値を並べます。たとえば、2つのセグメントでキーを構成していて、3つのレコードを得たい場合は、"in a1,a2,b1,b2,c1,c2"のように各レコード2つづつ値を指定します。
検索の開始はtable::find()を呼び出します。サーバー側の処理は、クライアントから送られてきたキー番号をセットしhandler::ha_index_read_map(HA_READ_KEY_EXACT)をキー値セットの数だけ繰り返し呼び出して結果を返します。
クライアントでは順次読み出しますが、次のレコードはfindNext()で読み取れます。もし、サーバー側で2番目の値が見つからなかった場合は、findNext()で2番目に来るとstat()がSTATUS_NOT_FOUND_TIを返し、見つからなかったことを示します。もう一度findNext()を呼び出すと3番目の検索結果を得ることができます。SQLと違うのは、どの値が見つからなかったかを容易に知ることができる点です。また、結果の順は自動でソートされることはなく、in句で指定した順に返ります。
このような振る舞いは、in句を使ってJOINに相当する処理をするのにとても便利です。取得したがソートされていたり飛ばされていたりしていた場合、JOIN元と今取得した結果を結合する際に、どちらかをソートしたり検索するといった余計な処理が必要になります。transactdはテーブル単位での処理しかありませんので、このJOINに相当する処理がやりやすいかはとても重要なことです。

transactdでfieldnameフィールドのインデックスがない場合

これはこの前のインデックスがある場合で説明したとおり、in句では検索することはできません。代わりに"fieldname = a or fieldname = b or fieldname = c"のようなフィルターで処理します。インデックスの無いフィールドの場合、どこにそのレコードがあるのかわからないので、全レコードフルスキャンするしかありません。フルスキャンするにしてもどれかインデックスを指定します。innodbの場合、プライマリーキーはレコードのデータも含んでいて他のキーより高速ですから、通常はそれを指定します。

まとめ

MySQLでin句で指定したfieldnameにインデックスがない場合は、全レコードスキャンします。将来も含めたレコード数を想定してパフォーマンスに問題がでないかどうかよく検討して使いましょう。ユニークなインデックスがあれば、高速に検索して結果が返ります。ただし、見つからない値があっても、どれが見つからなかったかは自分で確認が必要です。
transactdでは、fieldnameにユニークなインデックスがある場合しかin句は使用できません。検索結果はfindNext()で値の列挙順にアクセスでき、見つからならなかったことも知ることができます。これによりJOINに相当する後処理がやりやすくなります。インデックスが無い場合は、"fieldname = a or fieldname = b or fieldname = c"のフィルターを使います。この場合の、パフォーマンスはSQLと同様ですが、可能であれば検索の開始位置と、rejectCountなどで検索範囲をコントロールしてパフォーマンスを改善しましょう。

MySQL パフォーマンスとtransactd その２の２

MySQL Transactd

前回はselect * from tablename where fieldname = xxxのfieldnameをキーセグメントの先頭に持つインデックスがない場合を書きました。今回は、インデックスがある場合です。

MySQLでfieldnameフィールドのインデックスがある場合

今回の例は条件式が一つですので簡単です。MySQLはまず、レコードバッファ内のfieldnameフィールドの位置にキー値(xxx)をセットしてhandler::ha_index_read_map(HA_READ_KEY_EXACT)を呼び出します。

レコードがなければ（エラーならば）検索終了です。レコードが存在する場合、uniqueキーでなければhandler::ha_index_next_same()をエラーになるまで繰り返し呼び出し、順次レコードを取得します。

handlerでアクセスしたレコード数は、fieldname=xxxの重複レコードの数だけです。インデックスがあれば、実際に存在するレコード数しかアクセスしないので、パフォーマンスは最大です。（正確には、innodbの場合このキーがprimaryかそうでないかで若干パフォーマンスは異なりますが、重複レコードが少なければ大差ありません。）

アクセスするレコード数が少ないので、仮にinnodbのキャッシュに乗っていなくても、それなりに高速です。また、勝手にキャッシュされるレコード数も最少で、キャッシュメモリを温存できます。インデックスがあると色々お得ですね。

transactdでfieldnameフィールドのインデックスがある場合

クライアントフィルターを使う場合

最初にtable::setKeyNum()でインデックス番号を指定し、table::setFV(fieldname, xxxx)でキー値をセットします。その後、uniqueキーならtable::seekEqual()で、そうでなければtable::seekGreater(orEqual=true)でレコードにアクセスします。

レコードが見つかればtable::stat()にゼロが返ります、uniqueキーでない時は、table::seekNext()で次のレコードに移動してtable::stat()とフィールドの値を調べます。値が異なるかtable::stat()がゼロ以外になるまでtable::seekNext()を繰り返し呼び出します。

最初にインデックスを指定しseekEqualやseekGreaterといった名前の関数を呼び出すので、コードからパフォーマンスを容易に想像できます。

サーバーフィルターを使う場合

通常はクライアントフィルターで十分ですが、重複値がたくさんあるようなら、サーバーフィルターを使って一気に取得し、通信のオーバヘッドを削減しましょう。

table::setKeyNum()でインデックス番号を指定し、table::setFV(fieldname, xxxx)でキー値をセットするまでは同じです。その後、table::setFiletr(fieldname=xxx, rejectCount=1, maxRecords=0)でサーバーフィルターをセットしてからtable::find()を呼び出します。

サーバー側では、handler::ha_index_read_map(HA_READ_KEY_OR_NEXT)を使ってスキャンの先頭に移動します。そして、handler::ha_index_next()で次のレコードに移動しながら、条件にマッチするレコードかを調べていきます。

今回は対象フィールドのインデックスを指定しているので、レコードはそのインデックスでソートされていることが保証されています。そのため、マッチしないレコードが見つかればすぐにループを抜けるように、rejectCount=1と指定することがポイントです。ここでもやはり、transactdは、スキャンの開始位置と終了条件を明確にコントロールできます。

まとめ

検索対象のフィールドがインデックスの先頭セグメントにあれば、検索パフォーマンスは最大です。ただし、重複レコードを許可するキーの場合は、重複レコードがどれくらいあるか想定し、パフォーマンスを読みましょう。これはMySQLでもtransactdでも同じです。

transactdでは、重複レコードの量に応じて、クライントフィルターかサーバーフィルターを選択しましょう。

このテーマの最後に、transactdを使った検索のひな形コード（C++）を書いておきます。（他の言語、Ruby ActiveX C# PHP などでも同じ名前のメソッドがあるので、ほとんど同じです。）
次は select * from tablename where fieldname in(a,b,c...)を取り上げたいと思います。

Transactd インデックスを使った検索（クライアントフィルター）

static const char keynum = 0;
tb->clearBuffer();
tb->setKeyNum(keynum);
tb->setFV("fieldname", "xxxx");
tb->seekGreater(true/*orEqual*/);
while (tb->stat() == 0)
{
    if (strcmp(tb->getFVstr("fieldname"),"xxxx")!=0)
        break;
    //ここに見つかった場合の処理を記述します。
    tb->seekNext();
}

Transactd インデックスを使った検索（サーバーフィルター）

static const char keynum = 0;
tb->clearBuffer();
tb->setKeyNum(keynum);
tb->setFilter("fieldname = xxxx", 1/*rejectCount*/, 0/*maxRecords*/);
tb->setFV("fieldname", "xxxx");
tb->find(tbale::findForword);
while (tb->stat() == 0)
{
    //ここに見つかった場合の処理を記述します。
    tb->findNext();
}

MySQL パフォーマンスとtransactd その２の１

MySQL Transactd

その２はselect * from tablename where fieldname = xxxです。長くなるのでまずは２の１から。

なんとも簡単なSQL文ですが、テーブルの定義やデータの状況によって全くパフォーマンスが異なってきます。

使用するインデックス解析

MySQLはまずSQL文を解析し、fieldnameフィールドをキーセグメントの先頭に持つインデックスが存在するか調べます。存在すれば、そのインデックスを使用したオペレーションhandler::ha_index_read_map(HA_READ_KEY_EXACT)を使い操作を組み立てます。無ければ、hanndler::ha_rnd_next()かhandler::ha_index_next()を使ったレコードスキャンをします。

実は、このインデックスの選択と、handler::ha_index_read_map()オペレーションが使えるかどうかが最も重要なのです。handler::ha_index_read_map()は、インデックスを使って一発で目的のレコードを取得します。そうでなければ、目的のレコードがテーブルのどこにあるのかわからないので、フルスキャンすることになります。

もし、MySQLのオプティマイザがうまくインデックスを見つけられないとき（今回の例のような単純なものの場合はありえませんが）は、USE INDEX (index_list)構文を使ってどのインデックスを使うのか指定します。（どのインデックスが使われているかは、EXPLAINコマンドのkeyで確認できます。）

transactdでインデックスを指定する

transactdでは、インデックスを使ってテーブルアクセスする際には、必ずどのインデックスを使うかをプログラマがtable::setKeyNum()で指定します。インデックスを使わないアクセスメソッドもありますが、それを使うということは「テーブルをスキャンすることをプログラマが選択している」ということになりますので、予想外にテーブルスキャンしてしまうことはありません。

そのため、当然ですが、インデックスがうまく選択されているかの心配や確認は必要なくなります。書いた通りです。

MySQLでfieldnameフィールドのインデックスがない場合

インデックスがないフィールドから目的の値をもつレコードを探すには、全レコードスキャンします。スキャンの範囲は、先頭レコードから最後のレコードです。

インデックスが付いていないと重複値の許可の有無もないので、1つ見つかってもやめることなく全レコードアタックして調べていきます。100万行もあったら、前回のcount(*)と同じように恐ろしい結果が待っています。レコード数が少ないうちは瞬時に結果が返りますが、多くなるとレコード数に比例して遅くなります。

SQL文でこれ以上遅い例はないでしょう。遅い理由のほとんどはこのようなフルレコードスキャンです。（MySQLのhandlerを使ったスキャンの操作はMySQL パフォーマンスとtransactd その１をご覧ください。）

Lmit句を追加すると、スキャンの範囲を少し狭くすることができるかも知れません。たとえば、Limit 1とすれば、該当する行が1個見つかったときに検索を中止できます。しかし、それが最後のレコードだった場合は、やはり全レコードスキャンなので、範囲を狭くすることができる「かも知れない」です。

transactdでfieldnameフィールドのインデックスがない場合(クライントフィルター)

transactdでは、インデックスを使うメソッドと使わないメソッドが分かれています。使わない場合はtable::stepFirst()で最初から始めて、table::stepNext()で次のレコードを順次取得し、条件に合った値を持つかどうかをクライント（プログラマ）が自分で調べます。（このようにクライントで値をチェックし、フィルタリングすることを、「クライアントフィルター」と呼んでいます。)

サーバー側では、クライアントのstepFirst()とstepNext()の呼び出しごとにhandler::ha_rnd_next()が呼ばれ、先頭から順次カーソルを移動しながらレコードを返します。

インデックスがない場合、コーディングでは先頭から最後までのループを書かざるを得ません。そのため、コードからパフォーマンスを想像することが容易になります。また、重複があるかどうかを事前にプログラマが知っているなら、最初に対象レコードが見つかった時点でループをやめることもできます。さらに、検索レコード数が多くなリ過ぎたら、パフォーマンスを優先し検索を諦めるといった追加条件を加えてフルスキャンを防止することも容易です。ユーザーインタフェースでのキャンセルなどもその一例です。

このクライントフィルターはプログラムとしてはごく自然で解りやすいものです。しかしstepNext()の度にサーバーと通信するため、通信のオーバーヘッドが発生します。このオーバーヘッドはループが多くなればなるほど無視できないものなります。そこでSQLのように通信が少なくて済む方法が次に紹介する「サーバーフィルター」です。

transactdでfieldnameフィールドのインデックスがない場合(サーバーフィルター)

transactdではもう一つ、「サーバーフィルター」という検索方法があります。SQLと同様にサーバー側でフィルタリングするので、通信回数を大幅に削減できます。

実際の手順は、①まずtable::setFilter("fieldname = xxxx", rejectCount, maxRecords)のようにフィルターを指定して、②table::seekFirst()で検索開始レコードに移動し、③table::find()で検索を開始します。サーバー側では、handler::ha_index_next()を使って1レコードずつ、レコードが検索対象か調べます。

スキャンの開始位置はプログラマがseekオペレーションで指定できます。今回の例は全レコードですので、seekFirst()で先頭に移動します。スキャンの終了はsetFilterのrejectCountとmaxRecordsで決まります。

maxRecordsはSQLのlimitとほぼ同じです。指定した数のレコードが見つかると検索を中止します。

面白いのはrejectCountです。マッチしなかったレコードがrejectCountに達すると検索を中止します。たとえば1000とすると、マッチしないレコードを合計1000レコードスキャンしたところで検索を中止します。もし、全レコードを最後まで検索したいのなら、rejectCountにゼロを指定します。

transactdのサーバーフィルターでは、プログラマが「検索の開始位置」と「1回の検索でのおおよそのスキャン数」をフィルター指定時に決めることができます。また、続きから検索を再開することもできますので、フルスキャンするにしても、定期的にユーザーのキャンセルを確認するといったことも可能です。

まとめ　(select * from tablename where fieldname = xxx)

MySQLは、インデックスを使えるかどうかを最初に判断します。それによって、その後のスキャン操作が異なってきます。

インデックスがない場合は、全レコードをスキャンします。インデックスを用意したのなら、それが使われているかEXPLAINで確認しましょう。

インデックスが無いことを承知でそのフィールドだけのwhere文を組み立てるのなら、将来も含めたレコード数を想定して使いましょう。具体的には、数十レコード程度なら気にせずこのままでも良いでしょう。数百より多い場合は、ハードウェア（ディスク、メモリなど）や同時アクセス数とパフォーマンスの要求レベルに応じてインデックスの追加やスキャンするレコードを削減するための条件追加も検討しましょう。

transactdのインデックスを使わない検索

transactdを利用したアクセスでも、SQLと同様に全レコードアクセスするしかありません。

クライアントフィルターによるアクセスは、コード上でループを書くことになるので、コードからパフォーマンスを想像できます。また、クライアントサイドでフィルターするので、その他の条件によって自由に途中でやめることができます。

さらに、通信回数を減らしてより高速にしたいときは、サーバーフィルターを使います。サーバーフィルターによるアクセスは、通信回数を大幅に少なくできます。また、検索の開始位置と、rejectCountとmaxRecordsによって1回の検索でのスキャンレコード数を制限することで終了位置をコントロールできます。パフォーマンスは、プログラマが組み立てられます。