CLOVER🍀

That was when it all began.

MySQL Connector/JのrewriteBatchedStatementsプロパティとPreparedStatementの関係を確認する

これは、なにをしたくて書いたもの?

HikariCPのMySQL Connector/J(MySQLのJDBCドライバー)に関するWikiを見ていて、ふと疑問に思ったことがありまして。

MySQL Configuration · brettwooldridge/HikariCP Wiki · GitHub

それは、rewriteBatchedStatementsプロパティ(JDBCバッチ更新時にinsert文およびreplace文をバルクに書き換える機能)と
useServerPrepStmtsプロパティ(サーバーサイドPrepared Statementを使う)の両方がtrue(有効)になっていたことですね。

確か、rewriteBatchedStatementsプロパティはクライアントサイドPrepared Statementでなければ使えなかったのでは?と
思ったのですが、rewriteBatchedStatementsプロパティにはそのような記述がなかったのであらためて確認することにしました。

MySQL Connector/J Developer Guide / Connector/J Reference / Configuration Properties Performance Extensions / rewriteBatchedStatements

MySQL Connector/JのrewriteBatchedStatementsプロパティについて

MySQL Connector/JのrewriteBatchedStatementsプロパティは、executeBatch実行時にinsert文とreplace文をバルク表現に書き換える
機能です。

Should the driver use multi-queries, regardless of the setting of 'allowMultiQueries', as well as rewriting of prepared statements for INSERT and REPLACE queries into multi-values clause statements when 'executeBatch()' is called?

MySQL Connector/J Developer Guide / Connector/J Reference / Configuration Properties Performance Extensions / rewriteBatchedStatements

こういう文ですね。

INSERT INTO tbl_name (a,b,c)
    VALUES(1,2,3), (4,5,6), (7,8,9);

MySQL Connector/Jでは、rewriteBatchedStatementsプロパティをtrueにすることで通常のinsert文やreplace文をaddBatchしておいて、
executeBatch実行時にバルク表現に書き換えることができます。

プログラム内で指定したSQL文が書き換わるという、けっこう豪快な機能ですね。

このプロパティですが、以前はドキュメントにサーバーサイドPrepared Statementでは使えないという記述があったようです。

Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option

このためか、rewriteBatchedStatementsプロパティはクライアントサイドPrepared Statementのみで使えるというエントリーを
そこそこ見かけます。

なのですが、現在のドキュメントのrewriteBatchedStatementsプロパティには、このような記述はありません。HikariCPでもサーバーサイド
Prepared Statementを使用するような設定を記載していたので、実際のところどうなのか確認してみようというのが今回の目的です。

結論を先に書いておくと、現在においてはクライアントサイドPrepared Statement、サーバーサイドPrepared Statementのいずれを
使っても、rewriteBatchedStatementsプロパティを使用したバッチ更新のバルク表現の書き換えは有効です。

これがいつからそうなったのかということですが、Connector/J 5.1のリリースノートを見ると、2007年のようです。あれ…?

Changes in MySQL Connector/J 5.0.5 (2007-03-02)

The rewriteBatchedStatements feature can now be used with server-side prepared statements.

MySQL Connector/J 5.1 Release Notesのp.69より。

ところで、クライアントサイドとサーバーサイドという2つのPrepared Statementについて書いてきましたが、クライアントサイドの
Prepared Statementの存在が不思議な感じがしますね。

これは、初期のMySQLはPrepared Statementをサポートしていなかったため、Connector/Jでjava.sql.PreparedStamentインターフェースの
実装をクライアント側で作成していたからです。要するに、インターフェースのエミュレーションです。このため、サーバー側で
解析済みのSQL文が構築されるわけではありません。バインドパラメーターの適用も文字列置換です。

一方で、サーバーサイドのPrepared Statementが追加されても、この機能そのものに問題があった時期もあったためデフォルトでは
クライアントサイドのPrepared Statementが使われるようになっています。

Two variants of prepared statements are implemented by Connector/J, the client-side and the server-side prepared statements. Client-side prepared statements are used by default because early MySQL versions did not support the prepared statement feature or had problems with its implementation. Server-side prepared statements and binary-encoded result sets are used when the server supports them.

MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.4 JDBC API Implementation Notes

よって、サーバーサイドPrepared Statementを使うには、useServerPrepStmtsプロパティを明示的にtrueにする必要があります。

このあたりの事情、今はどうなんでしょうね?

今回は、rewriteBatchedStatementsプロパティを有効にしつつ、useServerPrepStmtsプロパティを有効にするかどうかで動作が
どう変わるかを見ていこうと思います。

環境

今回の環境は、こちら。

$ java --version
openjdk 17.0.6 2023-01-17
OpenJDK Runtime Environment (build 17.0.6+10-Ubuntu-0ubuntu122.04)
OpenJDK 64-Bit Server VM (build 17.0.6+10-Ubuntu-0ubuntu122.04, mixed mode, sharing)


$ mvn --version
Apache Maven 3.9.1 (2e178502fcdbffc201671fb2537d0cb4b4cc58f8)
Maven home: $HOME/.sdkman/candidates/maven/current
Java version: 17.0.6, vendor: Private Build, runtime: /usr/lib/jvm/java-17-openjdk-amd64
Default locale: ja_JP, platform encoding: UTF-8
OS name: "linux", version: "5.15.0-67-generic", arch: "amd64", family: "unix"

MySQLのバージョンは、こちら。

 MySQL  localhost:3306 ssl  practice  SQL > select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.0285 sec)

MySQLは172.17.0.2で動作しているものとし、データベースpractice、アカウントはkazuhira/passwordで接続できるものと します。

準備

Maven依存関係等はこちら。

    <dependencies>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.0.32</version>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.9.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>3.24.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>3.0.0-M7</version>
            </plugin>
        </plugins>
    </build>

動作確認は、テストコードで行うことにします。

テストコードの雛形

テストコードの雛形は、こちら。

src/test/java/org/littlewings/mysql/BulkInsertTest.java

package org.littlewings.mysql;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.Map;
import java.util.Properties;

import static org.assertj.core.api.Assertions.assertThat;

public class BulkInsertTest {
    interface SqlConsumer<T> {
        void accept(T var) throws SQLException;
    }

    void withConnection(Map<String, String> additionalProperties, SqlConsumer<Connection> consumer) throws SQLException {
        String url;

        Properties properties = new Properties();
        properties.setProperty("user", "kazuhira");
        properties.setProperty("password", "password");
        properties.setProperty("characterEncoding", "utf-8");
        properties.setProperty("connectionCollation", "utf8mb4_0900_bin");

        properties.putAll(additionalProperties);

        Connection connection = DriverManager.getConnection("jdbc:mysql://172.17.0.2:3306/practice", properties);
        try (connection) {
            connection.setAutoCommit(false);

            try {
                consumer.accept(connection);

                connection.commit();
            } catch (SQLException e) {
                connection.rollback();

                throw e;
            }
        }
    }

    @BeforeEach
    void setUp() throws SQLException {
        withConnection(Map.of(), connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    drop table if exists book""")) {
                ps.executeUpdate();
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    create table book(
                      isbn varchar(14),
                      title varchar(100),
                      price int,
                      primary key(isbn)
                    )""")) {
                ps.executeUpdate();
            }
        });
    }

    void bindParameterAndAddBatch(PreparedStatement ps, String isbn, String title, int price) throws SQLException {
        ps.setString(1, isbn);
        ps.setString(2, title);
        ps.setInt(3, price);
        ps.addBatch();
    }

    // ここに、テストを書く!!
}

MySQLへの接続は、共通のメソッドにまとめました。接続時のプロパティに関しては、追加で指定できるようにしています。

    interface SqlConsumer<T> {
        void accept(T var) throws SQLException;
    }

    void withConnection(Map<String, String> additionalProperties, SqlConsumer<Connection> consumer) throws SQLException {
        String url;

        Properties properties = new Properties();
        properties.setProperty("user", "kazuhira");
        properties.setProperty("password", "password");
        properties.setProperty("characterEncoding", "utf-8");
        properties.setProperty("connectionCollation", "utf8mb4_0900_bin");

        properties.putAll(additionalProperties);

        Connection connection = DriverManager.getConnection("jdbc:mysql://172.17.0.2:3306/practice", properties);
        try (connection) {
            connection.setAutoCommit(false);

            try {
                consumer.accept(connection);

                connection.commit();
            } catch (SQLException e) {
                connection.rollback();

                throw e;
            }
        }
    }

動作確認で使うテーブルは、テストの都度再作成することにしました。

    @BeforeEach
    void setUp() throws SQLException {
        withConnection(Map.of(), connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    drop table if exists book""")) {
                ps.executeUpdate();
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    create table book(
                      isbn varchar(14),
                      title varchar(100),
                      price int,
                      primary key(isbn)
                    )""")) {
                ps.executeUpdate();
            }
        });
    }

PreparedStatementにパラメーターをバインドし、バッチ更新の対象として追加するメソッドも作成しておきました。

    void bindParameterAndAddBatch(PreparedStatement ps, String isbn, String title, int price) throws SQLException {
        ps.setString(1, isbn);
        ps.setString(2, title);
        ps.setInt(3, price);
        ps.addBatch();
    }

とりあえず実行してみる

まずは、rewriteBatchedStatementsプロパティもuseServerPrepStmtsプロパティも指定せずに実行してみましょう。

    @Test
    void insertPlain() throws SQLException {
        withConnection(Map.of(), connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(1, 1, 1);
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    select count(*) from book""");
                 ResultSet rs = ps.executeQuery()) {
                rs.next();

                assertThat(rs.getInt(1)).isEqualTo(3);
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    select title from book where isbn = ?""")) {
                ps.setString(1, "978-4798161488");
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    assertThat(rs.getString(1)).isEqualTo("MySQL徹底入門 第4版 MySQL 8.0対応");
                }
            }
        });
    }

3つのinsert文を、バッチ更新で実行。

            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(1, 1, 1);
            }

結果は、それぞれ1が3件格納された配列になります。

登録されたデータの確認もしていますが、以降は件数のみにします。

rewriteBatchedStatementsプロパティのみを使う

では、rewriteBatchedStatementsプロパティを有効にしてみましょう。

    @Test
    void insertMultiValues() throws SQLException {
        withConnection(Map.of("rewriteBatchedStatements", "true"), connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);
                bindParameterAndAddBatch(ps, "978-4295000198", "やさしく学べるMySQL運用・管理入門【5.7対応】", 2860);
                bindParameterAndAddBatch(ps, "978-4798147406", "詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)", 3960);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(
                        -2,
                        -2,
                        -2,
                        -2,
                        -2
                );
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    select count(*) from book""");
                 ResultSet rs = ps.executeQuery()) {
                rs.next();

                assertThat(rs.getInt(1)).isEqualTo(5);
            }
        });
    }

この内容は、バルクinsertに書き換えられて実行されます。

            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);
                bindParameterAndAddBatch(ps, "978-4295000198", "やさしく学べるMySQL運用・管理入門【5.7対応】", 2860);
                bindParameterAndAddBatch(ps, "978-4798147406", "詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)", 3960);

                int[] results = ps.executeBatch();

この時、MySQLの設定でgeneral_logをONにしていると、MySQLに対して実際に発行されたクエリーをログで確認できます。

 MySQL  localhost:3306 ssl  practice  SQL > show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.0053 sec)

ログに出力されたSQLは、こちらでした。

2023-03-25T17:50:32.124797Z       776 Query     insert into book(isbn, title, price)
values('978-4798161488', 'MySQL徹底入門 第4版 MySQL 8.0対応', 4180),('978-4873116389', '実践ハイパフォーマンスMySQL 第3版', 4224),('978-4295012559', '1週間でMySQLの基礎が学べる本', 2860),('978-4295000198', 'やさしく学べるMySQL運用・管理入門【5.7対応】', 2860),('978-4798147406', '詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)', 3960)

バルクinsertに書き換えられています。また、クライアントサイドのPrepared Statementで動作しているので、値がそのまま入っていますね。

ところで、executeBatchの戻り値が不思議なことになっています。

                assertThat(results).containsExactly(
                        -2,
                        -2,
                        -2,
                        -2,
                        -2
                );

全部-2です。これはどういうことかというと、定数で書き直すと以下の意味になっています。

                assertThat(results).containsExactly(
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO
                );

この説明はドキュメントに書かれています。

Be aware that when using "rewriteBatchedStatements=true" with "INSERT ... ON DUPLICATE KEY UPDATE" for rewritten statements, the server returns only one value for all affected (or found) rows in the batch, and it is not possible to map it correctly to the initial statements; in this case the driver returns "0" as the result for each batch statement if total count was zero, and 'Statement.SUCCESS_NO_INFO' if total count was above zero.

MySQL Connector/J Developer Guide / Connector/J Reference / Configuration Properties Performance Extensions / rewriteBatchedStatements

つまり、更新件数が0でなかった場合はStatement.SUCCESS_NO_INFOが返ります。

Statement.SUCCESS_NO_INFOの意味はこちら。

バッチ文が正常に実行されたが、影響を受けた行数が不明なことを示す定数です。

java.sql.Statement / SUCCESS_NO_INFO

つまり、バルク表現に書き換えた場合は、executeBatchの戻り値から更新行数を把握することはできなくなるわけですね。更新が0件
だった場合は0が格納されることになりますが。

実装箇所はこちらです。

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ClientPreparedStatement.java#L722

これがrewriteBatchedStatementsプロパティのみを有効にした時の挙動です。

ちなみに、実行対象の文がひとつだと書き換えは行われないようです。そして対象の文が2つでも書き換えは行われました。

    @Test
    void insertMultiValues2() throws SQLException {
        withConnection(Map.of("rewriteBatchedStatements", "true"), connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(1);
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    select count(*) from book""");
                 ResultSet rs = ps.executeQuery()) {
                rs.next();

                assertThat(rs.getInt(1)).isEqualTo(1);
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(Statement.SUCCESS_NO_INFO, Statement.SUCCESS_NO_INFO);
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    select count(*) from book""");
                 ResultSet rs = ps.executeQuery()) {
                rs.next();

                assertThat(rs.getInt(1)).isEqualTo(3);
            }
        });
    }

useServerPrepStmtsプロパティを有効にする

次は、rewriteBatchedStatementsプロパティに加えて、useServerPrepStmtsプロパティを有効にしてみましょう。

    @Test
    void insertMultiValuesUseServerSidePreparedStatement() throws SQLException {
        withConnection(Map.of(
                "useServerPrepStmts", "true",
                "rewriteBatchedStatements", "true"
                ),
                connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);
                bindParameterAndAddBatch(ps, "978-4295000198", "やさしく学べるMySQL運用・管理入門【5.7対応】", 2860);
                bindParameterAndAddBatch(ps, "978-4798147406", "詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)", 3960);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO
                );
            }

クライアントサイドPrepared Statementを使った時と、テストコード上の動作に変化はありません。

この時のMySQLサーバー側のログを確認してみます。

2023-03-25T18:22:59.277847Z       805 Prepare   insert into book(isbn, title, price)
values(?, ?, ?)
2023-03-25T18:22:59.279233Z       805 Query     SELECT @@session.transaction_read_only
2023-03-25T18:22:59.281089Z       805 Prepare   insert into book(isbn, title, price)
values(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?)
2023-03-25T18:22:59.283637Z       805 Execute   insert into book(isbn, title, price)
values('978-4798161488', 'MySQL徹底入門 第4版 MySQL 8.0対応', 4180),('978-4873116389', '実践ハイパフォーマンスMySQL 第3版', 4224),('978-4295012559', '1週間でMySQLの基礎が学べる本', 2860),('978-4295000198', 'やさしく学べるMySQL運用・管理入門【5.7対応】', 2860),('978-4798147406', '詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)', 3960)
2023-03-25T18:22:59.284343Z       805 Close stmt
2023-03-25T18:22:59.322643Z       805 Close stmt

QueryからPrepare、Execute、Closeに変化しました。サーバーサイドPrepared Statementが使われているようです。
そして、バルク表現に書き換えられたSQLがPrepared Statementになっていますね…。それに、よく見ると書き換える前のinsert文も
あります…。

最初はこの挙動を不思議に思って、クライアントサイドのPrepared Statementにフォールバックしているのではないかと
emulateUnsupportedPstmtsプロパティを無効にしてみました。

    @Test
    void insertMultiValuesUseStrictServerSidePreparedStatement() throws SQLException {
        withConnection(Map.of(
                "useServerPrepStmts", "true",
                "emulateUnsupportedPstmts", "false",
                "rewriteBatchedStatements", "true"
                ),
                connection -> {
            try (PreparedStatement ps = connection.prepareStatement("""
                    insert into book(isbn, title, price)
                    values(?, ?, ?)""")) {
                bindParameterAndAddBatch(ps, "978-4798161488", "MySQL徹底入門 第4版 MySQL 8.0対応", 4180);
                bindParameterAndAddBatch(ps, "978-4873116389", "実践ハイパフォーマンスMySQL 第3版", 4224);
                bindParameterAndAddBatch(ps, "978-4295012559", "1週間でMySQLの基礎が学べる本", 2860);
                bindParameterAndAddBatch(ps, "978-4295000198", "やさしく学べるMySQL運用・管理入門【5.7対応】", 2860);
                bindParameterAndAddBatch(ps, "978-4798147406", "詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)", 3960);

                int[] results = ps.executeBatch();

                assertThat(results).containsExactly(
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO,
                        Statement.SUCCESS_NO_INFO
                );
            }

            try (PreparedStatement ps = connection.prepareStatement("""
                    select count(*) from book""");
                 ResultSet rs = ps.executeQuery()) {
                rs.next();

                assertThat(rs.getInt(1)).isEqualTo(5);
            }
        });
    }

動作は変わらずでしたが。

emulateUnsupportedPstmtsプロパティは、サーバーサイドPrepared Statementでサポートしていない機能を使おうとした場合に
クライアントサイドPrepared Statementにフォールバックするかどうかです。デフォルトは有効になっています。

MySQL Connector/J Developer Guide / Connector/J Reference / Configuration Properties / Prepared Statements / emulateUnsupportedPstmts

サーバーサイドPrepared Statementのサポートしている機能かどうか、確認している箇所はこちら。

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ConnectionImpl.java#L1585-L1587

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ConnectionImpl.java#L514-L526

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/core-api/java/com/mysql/cj/util/StringUtils.java#L1544-L1577

これを見ると、対象はcall、xa、create table、do、set、show warnings、/* ping */がサーバーサイドPrepared Statementの
サポート対象外で、そもそもバルクへの書き換えは範囲外になっていません。

というわけで、クライアントサイドPrepared Statement、サーバーサイドPrepared Statementのどちらを使っていても
rewriteBatchedStatementsプロパティは機能することがわかりました。

どうなっているのか

rewriteBatchedStatementsプロパティを有効にすると、クライアントサイドPrepared Statement、サーバーサイドPrepared Statementの
どちらを使っても、addBatchで対象のクエリーが溜め込まれます。

executeBatchの動作を少し追っていってみましょう。

executeBatchの呼び出し。

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/StatementImpl.java#L793-L796

次に、バルク処理への書き換えに移ります。

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ClientPreparedStatement.java#L409

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ClientPreparedStatement.java#L652-L653

クライアントサイドPrepared Statementでの、書き換えを行っている箇所。

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ClientPreparedStatement.java#L1104-L1105

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ClientPreparedStatement.java#L661-L676

サーバーサイドPrepared Statementでは、以下で書き換えの指示を行います。

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ServerPreparedStatement.java#L641-L642

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/core-api/java/com/mysql/cj/QueryInfo.java#L476-L494

実際のところ、executeBatchを実行する時(=MySQLサーバーへクエリーの実行を支持するタイミング)でSQLの書き換えを行って
いるみたいですね。

なので、Connection#prepareStatementで作成したサーバーサイドPrepared Statementは実際には使用せず、executeBatch実行時に
再構成したクエリーでサーバーサイドPrepared Statementを作り直し、こちらを実行しているようです。

とすると、サーバーサイドPrepared Statementを使った時に書き換え前のinsert文と書き換え後のバルクinsert文の両方が出現していた
理由がわかりますね。

2023-03-25T18:22:59.277847Z       805 Prepare   insert into book(isbn, title, price)
values(?, ?, ?)
2023-03-25T18:22:59.279233Z       805 Query     SELECT @@session.transaction_read_only
2023-03-25T18:22:59.281089Z       805 Prepare   insert into book(isbn, title, price)
values(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?),(?, ?, ?)
2023-03-25T18:22:59.283637Z       805 Execute   insert into book(isbn, title, price)
values('978-4798161488', 'MySQL徹底入門 第4版 MySQL 8.0対応', 4180),('978-4873116389', '実践ハイパフォーマンスMySQL 第3版', 4224),('978-4295012559', '1週間でMySQLの基礎が学べる本', 2860),('978-4295000198', 'やさしく学べるMySQL運用・管理入門【5.7対応】', 2860),('978-4798147406', '詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)', 3960)
2023-03-25T18:22:59.284343Z       805 Close stmt
2023-03-25T18:22:59.322643Z       805 Close stmt

すごい挙動をしますね…。

また、ソースコードを見ていてちょっと混乱したのがクラスとしてのサーバーサイドPrepared Statementと
クライアントサイドPrepared Statementの関係です。

これはなかなかの驚きなのですが、ServerPreparedStatementクラスはClientPreparedStatementクラスのサブクラスとして実装されて
います。

public class ServerPreparedStatement extends ClientPreparedStatement {

https://github.com/mysql/mysql-connector-j/blob/8.0.32/src/main/user-impl/java/com/mysql/cj/jdbc/ServerPreparedStatement.java#L66

ここに継承関係があると思っていなかったので、追うのにちょっと苦労しました…。

途中でこの関係に気づきましたが…。

というわけで、現在の挙動はわかりましたね。

まとめ

MySQL Connector/JのrewriteBatchedStatementsプロパティは、クライアントサイドPrepared Statementと
サーバーサイドPrepared Statementのどちらでも有効なことが確認できました。

また、その書き換えのタイミングもわかったわけですが、けっこう驚きの内容でした…。

そして、rewriteBatchedStatementsプロパティはクライアントサイドPrepared Statementのみで有効だとずっと思っていたので、
こういうところもたまに見返す必要がありますね、というのが気づきでした。

MySQL Connector/Jで設定した方が良さそうなプロパティについて

MySQLのJDBCドライバーであるConnector/Jですが、どのようなプロパティを設定した方がいいのかよく忘れるので。

いい情報はないかな?と思ったら、HikariCPのWikiによくまとまっていました。

MySQL Configuration · brettwooldridge/HikariCP Wiki · GitHub

記載時点(Connector/J 8.0.32時点)で、以下のようなプロパティが記載されています。
※Connector/Jのプロパティとそのまま対比できるように整形しました

prepStmtCacheSize=250
prepStmtCacheSqlLimit=2048
useServerPrepStmts=true
useLocalSessionState=true
rewriteBatchedStatements=true
cacheResultSetMetadata=true
cacheServerConfiguration=true
elideSetAutoCommits=true
maintainTimeStats=false
  • prepStmtCacheSize
    • Prepared Statementのキャッシュ数。デフォルトは25
    • cachePrepStmtsをtrueにする必要あり
  • prepStmtCacheSqlLimit
    • キャッシュするPrepared Statementの(SQL文の)長さ
    • cachePrepStmtsをtrueにする必要あり
    • デフォルトは256
  • cachePrepStmts
    • 有効(true)にするとクライアントサイドまたはサーバーサイドのPrepared Statementの適合性をチェックする(キャッシュの有効化)
    • デフォルトは無効(false)
  • useServerPrepStmts
    • 有効(true)にするとサーバーサイドPrepared Statementを使用する
    • デフォルトは無効(false)
  • rewriteBatchedStatements
    • 有効(true)にすると、executeBatchの呼び出しを伴うinsertおよびreplaceをバルクinsertまたはバルクreplaceに書き換える
    • デフォルトは無効(false)
  • useLocalSessionState
    • 有効(true)にするとオートコミットやトランザクションの分離レベルをローカルの情報を元に判断し、MySQLサーバーへの問い合わせを減らす
    • デフォルトは無効(false)
  • cacheResultSetMetadata
    • 有効(true)にすると、ResultSetMetaDataをキャッシュする
    • デフォルトは無効(false)
  • cacheServerConfiguration
    • 有効(true)にすると、 show variablesとshow collationの結果をキャッシュする
    • デフォルトは無効(false)
  • elideSetAutoCommits
    • 有効(true)にすると、Connection#setAutoCommit実行時にサーバーとドライバーの状態が異なる時のみset autocommit=nクエリーを発行する
    • デフォルトは無効(false)
  • maintainTimeStats
    • 無効(false)にすると、MySQLサーバーへの接続が失敗した時に詳細なエラーメッセージを出力するために維持するアイドル時間等の内部タイマーを無効にする
    • デフォルトは有効(true)

あとは、これに加えてcharacterEncodingとconnectionCollationを指定しておけばいいのかな、という気がします。

MySQL Connector/JとCharacter Set/Character Set Results/Connection Collationとの設定、関係がよくがわからなかったので調べてみる - CLOVER🍀

参考

MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.3.13 Performance Extensions

Connector/J Performance Gems