MySQLの本当のカーソル検索

• カーソル検索でのメモリ対策
• Integer.MIN_VALUE技
  • configure() で StatementConfig
  • dfpropで固定的にMIN_VALUE
• MIN_VALUE技の副作用
  • Streaming result set ... still active
  • キャッシュなしの一件ずつフェッチ!?
• useCursorFetchは惜しい!?

カーソル検索でのメモリ対策

MySQLのJDBCドライバでは、カーソル検索をしてもメモリ対策になりません。 検索結果のResultSetを構築した時点で、全ての検索結果のデータをメモリに持ってしまうからです。 (少なくとも Connector/J-5.1.46 においては)

つまり、DBFlute側で一件ずつフェッチのようにカーソル検索をしていても、それはあくまで "論理的なカーソル検索" に過ぎず、JDBCドライバーの中でJavaVMのメモリ上に全件保持されてしまい、大量件数の場合は OutOfMemoryError の可能性があるのです。

Integer.MIN_VALUE技

JDBCパラメータの FetchSize に Integer.MIN_VALUE を指定すると一件ずつフェッチの動作になり、大量件数の検索に対応できます。 DBFluteでは、StatementConfig を使って FetchSize を指定するか、カーソル検索におけるデフォルトの FetchSize を指定することで実現できます。 (useCursorFetchオプションについては後述)

configure() で StatementConfig

ConditionBeanでは、configure()メソッドで StatementConfig を設定できます。

e.g. ConditionBeanで Integer.MIN_VALUE を設定 @Java
memberBhv.selectCursor(cb -> {
    cb...

    // このように設定することで、一件ずつフェッチになって大量件数の検索できる
    // もしくは、littleAdjustmentMap.dfpropで設定すれば、cbごとの設定は必要なし
    cb.configure(conf -> conf.fetchSize(Integer.MIN_VALUE));
}, ...

dfpropで固定的にMIN_VALUE

ただ、つどつどCBや外だしSQLの検索するごとに設定するとなると、漏れが発生する可能性があります。 dfpropにて、カーソル検索のときだけ常に FetchSize を Integer.MIN_VALUE に することができます。開発始めから想定するのであれば、configure()よりもdfpropの方が良いでしょう。

e.g. ConditionBeanや外だしSQLで Integer.MIN_VALUE を設定 @littleAdjustmentMap.dfprop
    # /- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    # o cursorSelectFetchSize: (NotRequired - Default null)
    #  The fetch size of JDBC parameter for cursor select.
    #  For example, specify Integer.MIN_VALUE to enable fetch of MySQL.
    #
    ; cursorSelectFetchSize = Integer.MIN_VALUE
    # - - - - - - - - - -/

MIN_VALUE技の副作用

Streaming result set ... still active

ただ、Integer.MIN_VALUEの設定をすると今度は、カーソル検索のコールバックの中で、カーソル検索と同じトランザクションを利用したDBアクセスができなくなる ため、運用途中からの統一的な適用には注意が必要です(このため、DBFluteもデフォルトにできず...他のDBMSではこういうことはない)。

e.g. Integer.MIN_VALUEしたときに、コールバック内でDBアクセスしたときの例外 @Log
Caused by: java.sql.SQLException:
  Streaming result set com.mysql.jdbc.RowDataDynamic@7646731d is still active.
  No statements may be issued when any streaming result sets are open and in use on a given connection.
  Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:870)
    ...

なので、コールバックの中でDBアクセスをする場合は、別トランザクションにする必要があります。 (どのみち、カーソル検索を使う時は、業務的に一件ずつトランザクションで処理をすることが多いでしょう)

e.g. Integer.MIN_VALUE時のカーソル検索のコールバック内で更新処理 @Java
memberBhv.selectCursor(cb -> {
    cb...
}, member -> { // この中では、まだカーソル検索の接続が継続中
    ...

    // この処理を別トランザクションにする
    // トランザクションのかけ方は、フレームワーク (e.g. DIコンテナ) によって変わる
    memberBhv.update(member);
});

キャッシュなしの一件ずつフェッチ!?

【追記】こちらの話、解決されたかもしれない？

最新(2022/04/19時点)のJDBCドライバーのコードを読むと...ResultsetRowsCursor.java にて、fetchSizeのしっかり意識したコードになっているように見えます。 検証していませんが、MIN_VALUEじゃなくても20とか50とか指定すれば複数件キャッシュでのフェッチになるかもしれません。

実際に試してみないとですね by jflute

追記前の話

また、この設定により、フェッチ処理が(キャッシュなしで)一件ずつになるため、逆にパフォーマンスが劣化する可能性を否定できません。 一応、DBFluteではそのパフォーマンス問題に悩む場合の回避機能があります。

Integer.MIN_VALUEを利用した場合のパフォーマンス劣化対策として、 カーソル検索の内部動作を "べたなページング" 方式にするオプションがあります。 単に 1000 件ずつページング検索して、カーソル検索っぽく振る舞うやり方です。@since 1.0.3

littleAdjustmentMap.dfprop の cursorSelectFetchSize に Integer.MIN_VALUE が指定されていて、かつ、MySQLの場合は、ConditionBeanに customizeCursorSelect() というメソッドが生成され、CursorSelectOption が指定できるようになります。そのオプションで、byPagingOrderByPK() もしくは byPagingSimply() を指定すると、べたページングになります。

e.g. べたページング、IDソート @Java
MemberCB cb = new MemberCB();
cb.customizeCursorSelect(new CursorSelectOption().byPagingOrderByPK(1000));
cb.query().addOrderBy_MemberId_Asc();

厳密には、通常のカーソル検索と結果が同じになるとは限りません。 内部的に検索を繰り返しているため、他のプロセスによる更新の影響を後の方の検索が受ける可能性があるためです。 ただ、MySQLのトランザクション分離レベルが RepeatableRead (デフォルト)であれば、その心配はないかもしれませんが、業務的にそれを許容できるかどうかは 必ず 確認してください。(RepeatableReadであっても、カーソル検索部分がトランザクションになってなければ無意味ですので要注意)

byPagingOrderByPK()は、PKでソートされていることが前提となり、PKソートでない場合は例外が発生します。 安全性が "比較的" 高いため、べたページングで一番推奨されているやり方です。 (他のプロセスの更新で順序が入れ替わる可能性は "比較的" 低いため)

byPagingSimply() は、PK以外のソートで利用できますがページングの最中で順序がバラバラになり、 同じレコードがもう一度処理されてしまう可能性と処理されるはずのレコードが処理されない可能性が否定できないため非推奨です。 RepeatableReadであれば大丈夫である可能性がありますが、利用する場合は利用する側で厳密な確認を 必ず してください。