バッチ失敗時にBNLについて軽く調べてみた

これは Livesense Advent Calendar 2023 DAY 22 の記事です。

先日開発した内容でバッチが失敗し続けた件のメモ書きを兼ねて、そのことについて書きたいと思います。

概要

定期的に更新バッチしているのでが、その時に毎回テーブルを洗い替えをしています。
洗い替えする時には INSERT INTO tableA(column1, column2) SELECT column1, column2 FROM tableB といったいわゆる、INSERT SELECTを利用しています。

MySQLではこの構文ではSELECT先のテーブルに共有ロックがかかります。
変更に対して書き込み頻度の高いテーブルを参照しようとしたところ、デッドロックが多発していまう問題が起きました。
そこでSELECT先のテーブルに対しては、一時テーブルを作成しそのテーブルを参照することにして、デッドロックを回避しました。
しかし、処理時間が大幅に伸び、時間内にバッチが終了せず、バッチ二重起動を防ぐ処理で失敗し続けるという事態が発生しました。
原因は一時テーブルに対してインデックスをはっていなかったため、参照先テーブルに対するJOINに時間がかかっていたことでした。
この時の実行計画をみたところ、BNL joinといったあまりみない表示になってたので、調べてみました。

実行計画のイメージ(table,rowsやkeysはダミーにしています)

id	select_type	table	partitionstype	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ref	keys	id_and_place	6	const,const,const	１１１１１１１		Using where; Using temporary; Using filesort
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	１１１１１１１		Using where; Using join buffer (flat, BNL join)

BNL joinとは

BNL joinはインデックスが利用できない場合に全件スキャンをする最適化アルゴリズムの一つです。
公式サイトの 8.2.1.7 Nested Loop 結合アルゴリズム をみると、単純な二重ループではなくある程度バッファリングしてループを回る処理であることが読み取れます。
バッファサイズは join_buffer_size のシステム変数に格納されています。
これによって単純なNested Loopよりもスキャンの回数を減らすことができます。
しかしテーブルをフルスキャンすることに変わりなく、できるなら使用しない方が良い方法です。

対処方法

大きいテーブルをJOINする時にキーとなるカラムにインデックスをはりましょう。