ちなみに、コードは /lang/sql/mysql_timeline - CodeRepos::Share - Trac に置いてあります。C++ で約120行 (＋同程度のラッパライブラリ) なので、簡単に理解できると思います。実際のアクセスパターンをコードに直接書けばいいので、むしろ SQL をチューニングするより簡単かもしれません。コアのコードはこんな感じ。

int4store(follower_keybuff, user_id); if (follower_tbl->file->index_read_map(follower_tbl->record[0], follower_keybuff, 1, HA_READ_PREFIX) == 0) { do { unsigned follower_id = follower_follower_id_fld->val_int(); int4store(message_keybuff, follower_id); if (message_tbl->file->index_read_map(message_tbl->record[0], message_keybuff, 1, HA_READ_PREFIX_LAST) == 0) { do { if (! test_add_id(message_id_fld->val_int())) { break; } } while (message_tbl->file->index_prev(message_tbl->record[0]) == 0 && message_user_id_fld->val_int() == follower_id); } } while (follower_tbl->file->index_next(follower_tbl->record[0]) == 0 && follower_user_id_fld->val_int() == user_id); }

使い方はこんな感じ。ユーザID 123 のタイムラインが表示されます。

% g++ -I ~/dev/mysql/51/32-src/include -I ~/dev/mysql/51/32-src/sql -I ~/dev/mysql/51/32-src/regex -g -Wall -O1 -fno-rtti -fno-exceptions -shared -o timeline.so timeline.cc (snip) % cp timeline.so ~/dev/mysql/51/32-bin/lib/plugin % mysql -u root -p test mysql> CREATE FUNCTION timeline returns int soname 'timeline.so'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TEMPORARY TABLE fetch_timeline_tt (id int unsigned NOT NULL) ENGINE=heap; Query OK, 0 rows affected (0.00 sec) mysql> SELECT timeline(123); mysql> SELECT message.* FROM message INNER JOIN fetch_timeline_tt USING (id);

実装はしていませんが、過去の任意の時間のタイムラインを抽出する機能をつけることも簡単でしょう (message->index_read_map の呼出条件を変えるだけ)。

なんだかんだ言って、MySQL の API の奇麗さと InnoDB のチューニングのすばらしさを見せつけられた感じです。中途半端に自前のストレージを書くより、大樹によりかかるほうが楽だなと痛感しました。複雑な条件での検索を実行したいけど SQL では上手に書けない、というケースでは、この手法は結構現実的な選択肢になるのかもしれません。

6月13日追記: rev. 13812 でページング処理に対応しました。SELECT timeline(user_id,max_message_id); のように書くことで、メッセージ ID が max_message_id 未満のタイムラインを取り出すことができます。処理速度は最新タイムライン取得と同等かなと思っていたのですが、1,300 タイムライン/秒程度と、やや劣化しました。インデックスサーチの条件が先頭４バイトから８バイトになったためかな、と思います。

また、ユーザー毎の最新のメッセージIDをキャッシュできるようにしました (内部でユーザIDを鍵とする配列を準備し、そこにメッセージIDの最大値を格納)。コンパイル時に -DCACHE_MAX_MESSAGE_IDS=1 とすることで有効になります。ただ、この場合は、InnoDB 内の状態と timeline UDF 内のキャッシュ情報を同期させる必要があるので、必要に応じて timeline_set_maxid() 関数 (これも UDF として実装してあります) を呼び出すようにしてください。

-- MySQL 起動時に最新メッセージIDをキャッシュ SELECT timeline_set_maxid(user_id,max(id)) FROM message GROUP BY user_id; -- INSERT, UPDATE, DELETE にトリガーを設定 (手動で SQL 発行してもいいですが) CREATE TRIGGER timeline_update_maxid_after_insert AFTER INSERT ON MESSAGE FOR EACH ROW BEGIN SELECT timeline_set_maxid( NEW.user_id, (SELECT max(id) FROM message WHERE user_id=NEW.user_id) ) INTO @unused; END; (以下略)

ただ、ここまでしても最新タイムラインの取得速度は 1,710 タイムライン/秒から 2,000 タイムライン/秒程度までしか向上しませんでした。結局、いったんテンポラリテーブルに結果のメッセージIDを格納してから、それを別の SQL クエリで結合して取り出しているのがオーバーヘッドになっていると考えられます。この点を最適化しようと思うと、MySQL 本体に手を加える必要があるので、ちょっと今すぐには、やる気にはなりません。需要はなくはないと思うので、誰かやってみませんか？