あなたにはお判りでしょうか、 N連ガチャでN回インサートを実行している ソースに巡り合った時の衝撃。

Webアプリケーションでスループットが悪化する大きな要因はDBなどの ストレージアクセス です。

例えば「0.001秒のクエリ」でも、100回実行すれば単純に0.1秒を要します。
「たった0.1秒」かもしれませんが、同時接続が1,000リクエスト、10,000リクエストもあるシステムではいかがでしょうか。

これを、「100回分のSQLを1回で実行する」ことができれば、所要時間が数分の1で済みます。


Java + Spring Frameworkで利用されることが多いORマッパーはHibernate、JPA、MyBatisなどでしょうか。

この記事では「 MyBatisの動的SQLでパフォーマンスを維持、改善するための、ちょっと複雑なSQLを書く方法 」をご紹介いたします。

環境は以下の通りです。

  • Java 8
  • Spring Framework 4.1
  • MyBatis 3系
  • MySQL 5.6

MyBatis

MyBatis
http://blog.mybatis.org/

特徴

  • SQL文とオブジェクトのマッピングを行う永続化フレームワーク
  • XMLにSQLを記述する
  • Joinにも対応した高度なマッピング
  • 任意のSQLを組み立てることができる 自由度の高いシステム

「どのようなSQLが実行されるか」を 完璧に把握することが可能 で、このことは「いつどこでどのようなSQLが実行されるか」が問題となる、高アクセスなWebアプリケーションが求めていることです。

動的 SQLについては詳しいドキュメントがありますので、以下もご覧ください。

mybatis - 動的 SQL
http://www.mybatis.org/mybatis-3/ja/dynamic-sql.html

mybatis-spring

Spring FrameworkでMyBatisを利用するにはMyBatis-Springを利用します。

サンプルコードも掲載がありますので、導入について以下をご覧ください。

http://www.mybatis.org/spring/ja/

IN演算子

引数にリストを与え、XML内でforeachします。
ほぼ同じコードでDELETEもできますし、IN演算子を利用する機会はとても多いです。

  • Dao
1public List<User> selectIn(List<Integer> userIdList) {
2  try {
3    return provider
4     .getTemplate()
5     .selectList(getFullNameSpace("selsectByAnyIds"), userIdList);
6  } catch (Exception e) {
7    throw new DatabaseRuntimeErrorException(e);
8  }
9}
  • XML
1<select id="selsectByAnyIds" resultMap="BaseResultMap" parameterType="java.util.List">
2  SELECT <include refid= "Base_Column_List" />  FROM users
3  WHERE user_id IN
4    <foreach item="item" collection="list" open="(" separator="," close=")">
5      #{item, jdbcType=INTEGER}
6    </foreach>
7</select>

引数にjava.util.Listが与えられた場合、引数は自動的にlistという変数名が与えられるようです。

  • 実行されるSQL
1SELECT * FROM users WHERE user_id IN (1,2,3)

SQLはしっかりプレースホルダが当たりますので、インジェクション対策もOK。

1SELECT * FROM users WHERE user_id IN (?,?,?)

プレースホルダですから文字列型でも自分でクォートする必要はなく、同様のxmlの記述でOKでした。

BLUK INSERT

InsertをまとめてSQLの実行回数を減らします。

  • Dao
1public Integer insertRows(List<User> users) {
2  try {
3    return provider
4     .getTemplate()
5     .insert(getFullNameSpace("insertRows"), users);
6  } catch (Exception e) {
7    throw new DatabaseRuntimeErrorException(e);
8  }
9}
  • XML
 1<insert id="insertRows" parameterType="java.util.List">
 2   INSERT INTO users ( <include refid= "Base_Column_List" /> ) VALUES
 3   <foreach item="user" collection="list" open="" separator="," close="">
 4     (
 5        #{user.userId,  jdbcType=BIGINT},
 6        #{user.name,    jdbcType=VARCHAR},
 7        #{user.email,   jdbcType=VARCHAR},
 8        #{user.created, jdbcType=BIGINT},
 9        null
10     )
11   </foreach>
12 </update>

INと同様にここでは引数の型をListとしましたため、foreach.collectionは”list”で受けとります。

単一のアイテムはオブジェクトで複数のメンバがありますが、ドット(.)を打てばUserそれぞれのメンバにアクセスすることができます。
簡単ですね!

  • 実行されるSQL
1INSERT INTO users (user_id, name, email, created, modified) VALUES
2(1, "USER1", "EMAIL1", 1468681200000),
3(2, "USER2", "EMAIL2", 1468681200000),
4(3, "USER3", "EMAIL3", 1468681200000)

実際のSQLは、”xmlの改行”がそのまま出力されてしまうため見づらくなってしまいますね。

INSERT ON DUPLICATE KEY UPDATE

「SELECTして、レコードの有無をチェックする」手間を省くことができます。
INSERTできなかったらUPDATEすればいいじゃない。

  • Dao
1public Integer putRows(List<User> entities) {
2  try {
3    return provider
4     .getTemplate()
5     .update(getFullNameSpace("putRows"), entities);
6  } catch (Exception e) {
7    throw new DatabaseRuntimeErrorException(e);
8  }
9}
  • XML
 1<update id="putRows" parameterType="java.util.List">
 2   INSERT INTO users ( <include refid= "Base_Column_List" /> ) VALUES
 3   <foreach item="user" collection="list" open="" separator="," close="">
 4     (
 5        #{user.userId,  jdbcType=BIGINT},
 6        #{user.name,    jdbcType=VARCHAR},
 7        #{user.email,   jdbcType=VARCHAR},
 8        #{user.created, jdbcType=BIGINT},
 9        null
10     )
11   </foreach>
12   ON DUPLICATE KEY UPDATE
13     name    = VALUES(active),
14     email   = VALUES(email),
15     created = VALUES(created)
16 </update>

バルクインサートとほとんど同じですが、こちらは_UPDATE扱いになる_ことにご注意ください。

  • 実行されるSQL
1INSERT INTO users (user_id, name, email, created, modified) VALUES
2(1, "USER1", "EMAIL1", 1468681200000),
3(2, "USER2", "EMAIL2", 1468681200000),
4(3, "USER3", "EMAIL3", 1468681200000)
5ON DUPLICATE KEY UPDATE
6  name  = VALUES(active),
7  email = VALUES(email),
8  modified = VALUES(modified)

ここのSQLが嘘だったらごめんなさい。
期待通りにSQLが実行されることは検証済みです。

小技

加算・減算する

quantity = quantity + nというSQLが利用できます。
「ON DUPLICATE KEY UPDATE」と組み合わせて、次のXML定義で複数行を一括で更新することができました。

  • XML
 1<update id="putRows" parameterType="java.util.List">
 2   INSERT INTO user_items ( <include refid= "Base_Column_List" /> ) VALUES
 3   <foreach item="item" collection="list" open="" separator="," close="">
 4     (
 5        #{item.userId,   jdbcType=BIGINT},
 6        #{item.itemId,   jdbcType=VVARCHAR},
 7        #{item.quantity, jdbcType=INTEGER},
 8        #{user.created,  jdbcType=BIGINT},
 9        null
10     )
11   </foreach>
12   ON DUPLICATE KEY UPDATE
13     quantity = quantity + VALUES(quantity),
14     modified = VALUES(modified)
15 </update>

複数のパラメーターを利用する

記事公開後にSNSで 複数引数は構造体作らなくても@Paramアノテーションで可能 というコメントをいただきました。


MyBatisでは、 JavaソースからXMLに渡すことができる引数は1つ のようです。
(※思い違いでしたらご指摘ください。)

そのため、いろいろな引数を渡す場合は単一の型でラップします。 複数のValueObjectをListで渡す例は今まで見てきましたが、都度ValueObjectを用意するのもつらい時があります。

そういったときは、必要なパラメーターをすべてラップした構造体を追加することで対処します。
具体的には次の感じです。

  1. 構造体を用意し
  2. xmlのparameterTypeでその構造体を指定
  3. Daoから同様に引数を与える

例として「所持カードを数枚SELECTするSQL」を見てみましょう。

  • 構造体CardParams
1package net.atuweb.game.example;
2
3class CardParams {
4    private Long userId;
5    private List<Integer> userCardIdList;
6}
  • Dao
 1public List<User> selsectByAnyIds(Long userId, List<Integer> userCardIdList) {
 2  try {
 3    CardParams params = new CardParams();
 4    params.setUserId(userId);
 5    params.setUserCardIdList(userCardIdList)  
 6    return provider
 7      .getTemplate()
 8      .selectList(getFullNameSpace("selsectByAnyIds"), params);
 9  } catch (Exception e) {
10    throw new DatabaseRuntimeErrorException(e);
11  }
12}
  • XML
 1<select id="selsectByAnyIds" resultMap="BaseResultMap" parameterType="net.atuweb.game.example.CardParams">
 2  SELECT <include refid= "Base_Column_List" />  FROM user_cards
 3  WHERE
 4    user_id = #{userId, jdbcType=BIGINT}
 5  AND
 6    user_card_id IN
 7    <foreach item="userCardId" collection="userCardIdList" open="(" separator="," close=")">
 8      #{userCardId, jdbcType=INTEGER}
 9    </foreach>
10</select>

parameterTypeにValueObjectや構造体を指定したときは、そのメンバに直接アクセスできます。

配列の指定要素に直アクセスする

上記は「SQL用の構造体を用意する」ことで複数の引数を与えました。
しかしながら「小さい構造体が増えすぎて、パッケージエクスプローラーにたくさんのファイルが並ぶ」というデメリットもあります。

こちらは「構造体を追加せず、ValueObjectだけで頑張る」サンプルです。

例として、「複数のユーザを、あるステータスにUPDATEする」ケースを見てみましょう。

  • ValueObject
1@data
2class UserStatus {
3    private Long userId;
4    private Integer status;
5    private Long created;
6    private Long modified;
7}
  • Dao
 1public List<User> updateStateRows(List<Long> userIdList, Integer status, Long modified) {
 2  try {
 3    List<UserStatus> entities = new ArrayList<>();
 4    for (Long userId : userIdList) {
 5        UserStatus vo = new UserStatus();
 6        vo.setUserId(userId);
 7        vo.setStatus(status);
 8        vo.setModified(modified);
 9        entities.add(vo);
10    }
11    return provider
12      .getTemplate()
13      .selectList(getFullNameSpace("updateStateRows"), entities);
14  } catch (Exception e) {
15    throw new DatabaseRuntimeErrorException(e);
16  }
17}
  • XML
 1<update id="updateStateRows" parameterType="java.util.List">
 2UPDATE <include refid="Tabele_Name" />
 3SET
 4  status   = #{list[0].status, jdbcType=INTEGER},
 5  modified = #{list[0].upTime, jdbcType=BIGINT}
 6WHERE user_id IN
 7<foreach item="item" collection="list" open="(" separator="," close=")">
 8  #{item.userId, jdbcType=INTEGER}
 9</foreach>
10</update>

stateとmodifiedはすべて同じ値ですから、listの中のどこから取得しても同じはず。
ということでlistの最初の要素に直接アクセスしてみました。

list[0]という書き方がOKだと分かったときはちょっと感動でしたね。

おわりに

確かに、MyBatisでは柔軟なSQLを書くことができるということが確認できたと思います。
また、今回は取り上げませんでしたが、JOINしたテーブルをマッピングすることもでき、マッピング機能の柔軟性もあることは認識済みです。

DBサーバはスケールしにくく、かつコストも高いため、SQLのチューニングは必須ですから、MyBatisは良い選択肢ですね。
XMLにSQLを書くことに抵抗を感じない と、よりHappyになれる気がします。

参考書籍

Java + SpringへのMyBatis導入については以下に詳しく説明が記載されています。
こちらの書籍は難易度高めです。

Spring3入門 ――Javaフレームワーク・より良い設計とアーキテクチャ

長谷川 裕一,大野 渉,土岐 孝平
出版社:技術評論社  発売日:2012-11-02

Amazonで詳細を見る

MyBatisの書籍には洋書がありますね。

プログラムがすでに共通言語ですから、英語が苦手でもある程度読めるものですね。
Kindle版もあります。