MySQL

 

MySQLで大量レコードのjoin

MySQLのパフォーマンス・チューニングです。レコード数が多い場合、inner join/left outer joinは非常に遅くなります。joinするデータ自体を減らしたり、joinするタイミングを遅らせる等でパフォーマンスを改善しましょう。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告

そんなことはありません。MySQLもPostgresもOracleもSQLServerも遅くなります。

Oracleのオプティマイザは賢いですが、基本的に大量レコードのjoinは避けるべきです。

データ量が多すぎてテンポラリ領域を使う(バッファに乗り気らない)から、です。

スキーマ構造によるので一概には言えませんが、大体1000件辺りから微妙に遅くなり、1万件を超えると一気に遅くなります。

1万件程度なら割と力技(マシンパワー)で何とかなるので、1万件を超えた辺りから注意してみて下さい。

よくある例では、ログを元にしたアクセスレポートの生成時に、大量レコードのjoinが必要になり易いかもしれませんね。

データ量が多いならデータ量を減らせばいいじゃないという単純な解です。

例えば、PKの1〜1000までをselectしてjoin、PKの1001〜2000までをselectしてjoio、それをunion allする、等。

小刻みにjoinし、union allでjoin結果を結合する感じです。

以下は、PKをキーに小刻みにselect・joinし、union allしている例です。

select * from t1 inner join t2 using(i1) where i1=1
union all
select * from t1 inner join t2 using(i1) where i1=2
union all
select * from t1 inner join t2 using(i1) where i1=3
;

SQLではコード値のみをselectし、アプリケーション側で事前にキャッシュしたマスタで1レコードづつjoinをします。

例えばマスタをMap<pk, entity>等と事前にキャッシュしておき、ループ処理時にmap.get(entity.cd).nameといった感じでjoinをします。

以下はs2jdbcを使って、ユーザの職種名を取得する例です。

private Map<Integer, JobCategory> jobCategory;

public void test1(List<User> users) {
	for (User user : users) {
		String jobCategoryName = jobCategory.get(user.jobCategoryCd).jobCategoryName;
	}
}

アプリケーションのループ処理時に、1レコード毎にPKをキーにマスタをselectしてしまう方法も有りです。

以下はs2jdbcを使って、ユーザが住んでいる都道府県名を取得する例です。

@Resource
protected JdbcManager jdbcManager;

public void test2(List<User> users) {
	for (User user : users) {
		Prefecture pref = jdbcManager.from(Prefecture.class)
			.where(new SimpleWhere().eq(PrefectureNames.prefectureCd,user.prefectureCd)).getSingleResult();
	}
}

パフォーマンスを考慮し、検索時は可能な限りPKかインデックスを設定したカラムをキーに検索して下さい。

更に言うと、カバリングインデックスになるようインデックスを設定してSQLを発行するとよいです。

ORMを使う場合はカバリングインデックスが有効になりにくいので、その場合はクエリキャッシュを有効にして下さい。

マスタへの検索は大抵似同じクエリになるので、クエリキャッシュが効きやすく、それ程速度低下は無いかと思います。

但しORMによる自動生成SQLは全カラムをselectする巨大なSQLになるので、キャッシュサイズは多めに設定するとよいです。

treeおすすめの書籍です!
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格:4,830円(税込、送料別)

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド
価格:3,465円(税込、送料別)

【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格:3,129円(税込、送料別)

◯ 広告