この記事は1年以上編集されていないため、情報が古い可能性がございます。
ご注意ください。

このWordPress pluginを配布しています

あなたにはお判りでしょうか、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
public List<User> selectIn(List<Integer> userIdList) {
  try {
    return provider
     .getTemplate()
     .selectList(getFullNameSpace("selsectByAnyIds"), userIdList);
  } catch (Exception e) {
    throw new DatabaseRuntimeErrorException(e);
  }
}
  • XML
<select id="selsectByAnyIds" resultMap="BaseResultMap" parameterType="java.util.List">
  SELECT <include refid= "Base_Column_List" />  FROM users
  WHERE user_id IN
    <foreach item="item" collection="list" open="(" separator="," close=")">
      #{item, jdbcType=INTEGER}
    </foreach>
</select>

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

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

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

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

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

BLUK INSERT

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

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

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

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

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

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

INSERT ON DUPLICATE KEY UPDATE

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

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

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

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

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

小技

加算・減算する

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

  • XML
<update id="putRows" parameterType="java.util.List">
   INSERT INTO user_items ( <include refid= "Base_Column_List" /> ) VALUES
   <foreach item="item" collection="list" open="" separator="," close="">
     (
        #{item.userId,   jdbcType=BIGINT},
        #{item.itemId,   jdbcType=VVARCHAR},
        #{item.quantity, jdbcType=INTEGER},
        #{user.created,  jdbcType=BIGINT},
        null
     )
   </foreach>
   ON DUPLICATE KEY UPDATE
     quantity = quantity + VALUES(quantity),
     modified = VALUES(modified)
 </update>

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

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


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

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

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

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

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

  • 構造体CardParams
package net.atuweb.game.example;

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

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

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

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

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

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

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

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

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

おわりに

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

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

この記事はtomita@atuwebがお届けしました。

参考書籍

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

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


Java Persistence With Mybatis 3: A Practical Guide to Mybatis, a Simple Yet Powerful Java Persistence Framework

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



2016年04月03日:@Paramアノテーションについて追記

スポンサーリンク
ad_336
ad_336
  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存
コメントの入力は終了しました。