0

mySQLのJOINと個別取得のベンチマーク

以前はなんとしても一発のクエリでデータを取得してやるみたいなのがあったけど、ソーシャルゲームなんかを作ってるとmySQLの激しい結合のせいでパフォーマンスが落ちるって事がよくあります。
「結合が悪いというかそれは設計の問題だ!」という意見は今は無視。
確かに、経験上結合やめて高速になったことはあったけど、結合にして高速になったて事はなかったので、「結合の利用は控えめに」となってた。
そんな軽い認識でいたけど、気になったのでモヤモヤを晴らすためにもどれだけパフォーマンスに影響するのか気になったのでベンチマークをとってみました。

まず一般的な取得の方法は以下の3つ。
1対1の関係のメンバーとアイテムというテーブルからすべてのデータを取得したい場合
A、結合で取得
良い点:単純な結合なら楽チン。
悪い点:結合が激しくなるとインデックスがどうなってるかワケがわからなくなる。

B、メンバーテーブルをループで回す。
メンバーテーブルの外部キーを取得して都度アイテムテーブルにアクセスして取得
良い点:少ないレコードの場合手っ取り早い。
悪い点:明らかにクエリー数が増えるのでパフォーマンスに悪影響。

C、アイテムテーブルを予め配列にしておく。(プライマリーキーを配列のキーに設定)
メンバーテーブルをループで回して、用意しておいたアイテム配列から取得。
良い点:高速。クエリー数が少なくて済む。
悪い点:検索の時に面倒な事が多い。本来DBですべき処理をプログラム上で処理する必要がある。

実務で使うのはAでパフォーマンスが落ちたらCを考える。Bは書き捨てのコードを書く時くらい。

ベンチマークとったのもこのメンバーとアイテムの関係です。
■メンバーテーブル
CREATE TABLE IF NOT EXISTS `member` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`membername` varchar(255) NOT NULL,
`item_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM;

でレコードを10,000件登録。

■アイテムテーブル
CREATE TABLE IF NOT EXISTS `item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`itemname` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

でレコード数を10件登録。

使ったスクリプト

実行環境
– php:5.3.6
– mySQL:5.1.55
– memcache:1.4.5

以下を実行(memcacheも入れてみた)
// 単純にメンバー名を取得
getMember();
// itemテーブルをjoinさせて表示[A]
getMemberItemWithJoin();
// 1件づつitemテーブルからitemidを取得して表示[B]
getMemberItemWithOneByOneFromDB();
// itemテーブルを予め取得しておいて表示[C]
getMemberItemWithAllFromDB();
// memcacheにあるitemデータを取得しながら表示[B]
getMemberItemWithOneByOneFromMemcache($memcache);
// memcacheにあるitemデータを予め取得しておいて表示[C]
getMemberItemWithAllFromMemcache($memcache);

結果。
何度かやって2と3は稀に順位が変わったけどそれ以外は変わらず。
1 getMember: 0.026777982711792 seconds
2 getMemberItemWithAllFromDB: 0.028398036956787 seconds
3 getMemberItemWithAllFromMemcache: 0.036365985870361 seconds
4 getMemberItemWithJoin: 0.048348903656006 seconds
5 getMemberItemWithOneByOneFromMemcache: 0.6713719367981 seconds
6 getMemberItemWithOneByOneFromDB: 1.0738849639893 seconds

1は当たり前なので無視。
結合か纏めて取得かで単純に言えば纏めて取得の方が速かった。上の分類でいうとCのやり方が一番早い。
上記でも書いたように検索時に面倒だったり、コードが長くなったりにもするけどこの分母の少なさでこれだけ差が出るとパフォーマンス重視のサイトにはやっぱり結合は控えるべき。
KVSももちろん使うべき。
なんとも幼稚臭い内容だけど、実際ベンチマークを取ると理解が深まるのがいい。

大阪のWEB屋です。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です