JavaによるMySQLのINSERT高速化
JavaプログラムでJDBCを使用して接続している場合以下の方法でMySQLのマルチプルインサートを使用することができる。
マルチプルインサートは複数のINSERT文を一回のSQLで発行する方法で、高速化が図れる。
やり方
①ドライバの呼び出し部分に以下の文字列をパラメータとして記載
・rewriteBatchedStatements=true
②Javaプログラムで「addBatch」「executeBatch」を使用し以下のように記載
PreparedStatement statement = connection.prepareStatement(sql);
for (Bean bean : List) {
statement.setString(1, bean.getNo);
statement.setString(2, bean.getName);
statement.addBatch();
}
statement.executeBatch();
補足：SELECTを高速化したい場合、PreparedStatement.setFechSizeを使用すると取得する件数の単位を帰らるので高速化できる。
思い込み？
こんなことがあった。
ある機能を設計してたメンバーが、レビュー依頼を投げてきた。その中で、あるデータをテーブルAに登録するのか、テーブルBに登録するのかという問題があり、そのメンバーはテーブルAに登録するとしていた。
画面上の検索処理は、テーブルA、テーブルBのデータをそれぞれ起点として検索結果を表示する用になっており、どちらのテーブルに登録しても検索結果は変わらない。
違いは、テーブルAは登録処理が複雑で、登録データが画面上に表示されない。
テーブルBは登録処理がシンプルで、登録データは画面上に表示される。
ユーザーは私用説明の際、特に理由を説明せずに、テーブルAに登録して欲しいと言った。
メンバーはそれを聞いて、画面表示させたくないんだと判断しテーブルAに登録する設計を書いた。
レビュー時に、なぜテーブルAに登録するのか？と指摘したところ、メンバーは「テーブルAに登録してしまうと画面に表示されてしまいます。このデータは画面に表示したくないのでテーブルAに登録する設計にしました」と答えた。
僕「なぜ画面に出したくないって判断したのの？」
メ「ユーザーがテーブルAに登録して欲しいと言ったからです。」
僕「でもテーブルAの登録処理は複雑だから工数かかるよね？」
メ「でも画面に出したくない要望が優先ではないでしょうか？」
ユーザーは仕様を理解してるので、テーブルAに登録して欲しいと、わざわざ指定したのだから、画面に表示したくないんだろう、というのがメンバーの言い分。
ここには大きな落とし穴があって、ユーザーの気持ちを汲んでいるようにも見えるが、ユーザーは一言も「表示したくない」とは言っていない。メンバーは仕様を知ってるユーザーがテーブルAを指定しているんだから「表示したくない」に決まっていると思い込んでいる。
後日ユーザーに確認したところ、「たしかに表示しないのがベストだが、それで工数がかかるのならば、表示されるテーブルBに登録で構わない」との返答だった。
ユーザーとしては、画面表示＜工数だった。
ここから言えることは、ユーザーがやりたいことがユーザーの最優先事項とは限らないということ。やりたいことを聞いた上で、そうやるとこうなりますけどいいですか？とメリットデメリットを聞いた上で判断してもらうことで、隠された最優先事項が見えてくる。
ユーザーがやりたいと言ってるからやるのではなくて、なぜやりたいのかを確認して、それを実現するためのメリットデメリットを見せた上で判断してもらうことがユーザーの最重要項目を把握することになるというお話。
sql_mode=STRICT_TRANS_TABLESにはまった話
MySQL5.6からSQLモードに「STRICT_TRANS_TABLES」がデフォルトで追加された。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 1.7.3.3 無効データの制約
これに関連して、プロシージャーの実行にはまったのでメモ。
環境は以下の通り。
・MySQL5.7
・クライアント：DBeaver
・ドライバ：JDBCドライバ
上記の環境で、DBeaverからストアドプロシージャーを適用し、実行すると「1366 (HY000): Incorrect string value」というエラーが発生。
ストアドプロシージャーの中で、int型変数を空文字（''）で初期化しようとしていたことを怒られたのですが、
この現象、以前は発生していませんでした。
以前はと何が違うかというと、クライアントにMySQL workbenchを使用していました。
今回、諸事情により、クライアントをDBeaverに変更したことでこの事象が発生するようになりました。
調査した結果、DBeaverが使用しているJDBCドライバは、デフォルトで「STRICT_TRANS_TABLES」を使用する仕様（バグではなさそう。JDBC準拠？）になっており、
DBeaverでつなぐとセッション変数のsql_modeに強制的に「STRICT_TRANS_TABLES」が追加されていたことがわかりました。
MySQL Bugs: #24526: JDBC connector forces strict mode regardless of server setting
MySQL :: MySQL 5.1 リファレンスマニュアル :: 24.4.4.3 Java 、JDBC および MySQL のタイプ
しかも、もう一つはまった要因として、
「ストアドプロシージャーはCreateされた時のsql_modeで実行される」ということ。
このおかげで、いくらsql_modeを変更してもストアドプロシージャーの動きが変わらずエラーが出続けていました。
対応策として、以下のどちらかで、正常終了するようです。
①JDBCドライバの接続のＵＲＬでjdbc:mysql://localhost/dbname?jdbcCompliantTruncation=false とする。
②セッション変数のsql_modeから「STRICT_TRANS_TABLES」を外した状態で、Create Procedure を行う。
ちなみにストアドプロシージャーが何のsql_modeに紐づいているかを確認するには、「SHOW CREATE PROCEDURE test1 \G」を実行するとよいです。
2019/09/05追記：
DBvearで①の設定をするためには、画面左端の「データペースナビゲーション」から
・該当のデータベースを右クリック→「編集 接続」
・開いた画面の「ドライバーのプロパティ」から「jdbcCompliantTruncation」を「false」に設定
MySQLパフォーマンスチューニング
その１ ボトルネックをみつける。
Explainにを読んでどこに時間がかかっているか確認する。
対象SQLのＪＯＩＮを一つずつ外してみる。
その２ インデックスの検討。
正しいインデックを使っているか。
複合インデックスを使う。
余計なインデックスを外す
その３ ＳＱＬの改善。
サブクエリ、相関サブクエリをJOIN句に書き換える。
LEFT JOINをExists文に書き換える。
UNIONに書き換える。
UNIONを外す。
その４ テーブルＩＯの改善。
バルクインサートを使う。
ファイルに書き出してからloadコマンドで取り込む。
メモリテーブルに変更する。
その５ ロジックの検討。
ぐるぐる系をどかん系に変更する。
ワークテーブルを使う。
その６ ハードの改善
メモリを増やす。
性能の良いハードディスクに替える。
「羊と鋼の森」を読んだ。
バイブルにしたい本に出会いました。
文体が軽やかで、すらすら読めます。すらすら読めすぎて、気がついたら物語に引き込まれてました。
登場する主人公含め調律師4人は、それぞれ人間的で、それでいて魅力的です。
音楽、ピアノ、技術。苦悩、葛藤、憧れ。調律師という職業と主人公の半人前感が、エンジニアとして通じるものがあり、大事なことを教えてくれている気がました。
