💨
[InnoDB]INSERT-SELECT実行時のテーブルロックの原因を読み解く

2024/09/28に公開
MySQL
InnoDB
tech
 背景以下記事の続きです

https://zenn.dev/kkyoka/articles/f7fa23d878022e
データ移行時でINSERT-SELECTをした際に、行ロック想定だったのにもかかわらずテーブルロックがかかってしまいました。

その原因が曖昧なままだったので引き続き調べてみます。

 EXPLAINで調べる実行前にやっておけよ定期

 実行クエリのEXPLAINを確認テーブルロックになっていたということは、SELECTクエリのEXPLAIN結果のTYPEがALLになっているのでは？と予想。
クエリ
explain select
            *
        from
            hoge_table1
                join
            hoge_table2 on hoge_table1.hoge_table2_id = hoge_table2.id
                join
            hoge_table3 on hoge_table2.hoge_table3_id = hoge_table3.id
        where
            hoge_table1.fuge_colmun2 is not null and
            hoge_table1.fuge_colmun3 != 50 and
            hoge_table1.deletes_at is null and
            hoge_table3.fuge_colmun1 = 1
        ;
結果


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
hoge_table1

range
idx_hoge_table1,idx_fuge_colmun3
idx_fuge_colmun2
5

3916698
2.82
Using index condition; Using where

1
SIMPLE
hoge_table2

eq_ref
PRIMARY,idx_fuge_colmun3
PRIMARY
4
hoge_table1.hoge_table2_id
1
100


1
SIMPLE
hoge_table3

eq_ref
PRIMARY
PRIMARY
4
hoge_table2.hoge_table3_id
1
10
Using where

rowsが3916698(全件数は7500万件ほど)なのとtypeがrange,eq_refになっているところから、フルテーブルスキャンしているわけではなさそう
EXPLAINではテーブルロックの原因は特定できなかった🤔

 EXPLAINの結果で気になった点をさらに確認WHERE句に使用しているhoge_table3.fuge_colmun1はindexの貼っていないカラムなのにtype:ALLにならないことが気になったのでそこも調べてみました👀
ALLになるパターン
クエリ
explain select
            *
        from
            hoge_table1
                join
            hoge_table2 on hoge_table1.hoge_table2_id = hoge_table2.id
                join
            hoge_table3 on hoge_table2.hoge_table3_id = hoge_table3.id
        where
            hoge_table1.fuge_colmun2 is not null and
            hoge_table1.fuge_colmun3 is not null and
            hoge_table3.fuge_colmun1 = 1
        ;
結果(着目した部分のみ抜粋)


id
table
type


1
hoge_table3
ALL

1
hoge_table2
ref

1
hoge_table1
ref

ALLにならないパターン
クエリ
explain select
            *
        from
            hoge_table1
                join
            hoge_table2 on hoge_table1.hoge_table2_id = hoge_table2.id
                join
            hoge_table3 on hoge_table2.hoge_table3_id = hoge_table3.id
        where
            hoge_table1.fuge_colmun2 is not null and
            hoge_table1.fuge_colmun3 is not null and
            hoge_table1.fuge_colmun4 is not null and // この条件を追加！
            hoge_table3.fuge_colmun1 = 1
;
結果(着目した部分のみ抜粋)


id
table
type


1
hoge_table1
range

1
hoge_table2
eq_ref

1
hoge_table3
eq_ref

1つ目のクエリはhoge_table1への条件が緩いため、hoge_table3をフルテーブルスキャンする必要がある
2つ目のクエリははhoge_table1への条件が厳しいため、hoge_table1を先にスキャンしてその結果に紐づくhoge_table3に対してhoge_table3.fuge_colmun1 = 1の条件を適用している
そのためhoge_table3をフルテーブルスキャンする必要がないように思える

 実際にクエリ実行して、しらみつぶしに調べるトランザクション貼ってクエリ実行し、INSERTクエリを流して待機になるかを調べていきました。

 主キー(idカラム)で条件指定してみるWHERE句にidで全件指定してみる
対象件数は200万件ほど(この調査はローカル環境でやっていたので本番のデータ数とは異なります...)
結果
テーブルロックがかかった

想定原因
最大のidも対象になっているのでネクストキーロックの可能性が高い
ちなみにid >= {max値-1}で指定しても(対象件数1件)ロックがかかったので全件指定しているときに限らずである


WHERE句にidでid BETWEEN 1 and {max値-1}を指定してみる
対象件数は200万件ほど
結果
テーブルロックがかからなかった

想定原因
ネクストキーロックを回避したからかな
主キーの指定は一意にデータを特定できるから優先的に行ロックになるみたい



 等価検索のみにしてみるWHERE句にfuge_colmun5 = 2を指定してみる
fuge_colmun5はindexあり
対象件数は3000件ほど
結果
テーブルロックがかかった

想定原因
fuge_colmun5はカーディナリティが低い(13パターン)ためindexの効果が薄かった


WHERE句にfuge_colmun6 = 2を指定してみる
fuge_colmun6はindexあり
対象件数は3000件ほど
結果
テーブルロックがかからなかった

想定原因
fuge_colmun6はfuge_colmun5に比較するとカーディナリティが高い(587パターンほど)ため



 否定形の条件のみにしてみるWHERE句にhoge_table1.fuge_colmun3 != 50を指定してみる
fuge_colmun3はindexあり
対象件数は210万件ほど
結果
テーブルロックがかかった

想定原因
そもそもEXPLAIN結果のTYPEがALLになっていたのでフルテーブルスキャンしている
条件に一致する件数が多いため、indexが使用されなかった可能性が高い


WHERE句にhoge_table1.fuge_colmun3 not in (10,1,30,51,2,6,8)を指定してみる
10,1,30,51,2,6,8は、件数が多い順で上から7個のデータ
結果
テーブルロックがかかった

想定原因
対象データが比較的少ないからかな
否定形の条件でも、対象データ件数が少ないとindexが使用されるみたい



 日付カラムを条件に指定してみるWHERE句にupdated_at <= '2019-01-30'を指定してみる(2019-01-30がローカルデータの最古データだった)
updated_atはindexあり
対象件数は190万件ほど
日付カラムなのでカーディナリティは高いはず
範囲指定だとindexの効果弱いか...?
結果
テーブルロックがかかった

想定原因

group by updated_atしてみると、なんとupdated_at = 2019-01-30 17:50:45のデータが190万件存在していた(おそらくテストデータ😇)
検索範囲内でデータの偏りがあり、indexの効果が得られなかった(ただしこれはローカル環境でしか起きない事象)


WHERE句にupdated_at BETWEEN '2019-02-01' and '2024-09-28'を指定してみる
対象件数は32万件ほど
結果
テーブルロックがかからなかった

想定原因
カーディナリティが高いカラムだからかな

updated_at　= 2019-01-30のデータを除いたので対象データの偏りがなくなったため
ちなみにupdated_at >= '2019-02-01'で指定すると最新のデータも対象に含まれてしまい、ネクストキーロックになってしまうのでNG
一部データのupdated_atを更新してupdated_at <= '2019-01-28'で指定してもテーブルロックかからなかった👍
つまり範囲でも値の偏りがなければ問題なさそう




 結論上記の調査を経て、実際に指定していた条件を再度見てみます。

条件は以下
hoge_table1.fuge_colmun2 is not null
hoge_table1.fuge_colmun3 != 50
hoge_table1.deletes_at is null
hoge_table3.fuge_colmun1 = 1
テーブルロックになった原因として以下が挙げられます。
否定形が多い
否定条件に一致するデータ数が少なく、検索対象件数が多くなっている

!= 50のところ

indexを貼っていないカラムを条件に使っている
deletes_atとhoge_table3.fuge_colmun1

上記どれかひとつでも当てはまればテーブルロックになる！というよりは対象を明確に絞れる条件がなかったことが原因のようです。

例えば条件に以下を指定した場合、2つ目の条件でindexの貼られていないカラムが指定されていますが、１つ目の条件が強いのでテーブルロックにならずにすみます。(条件の書く順番を変えても同様)
fuge_colmun6 = 2
hoge_table1.deletes_at is null
いろいろ試してみて上記結論に至りました！

詳しい方、悔しい方、卑しい方、アドバイスあればお願いします！！！

 学んだことEXPLAIN結果だけだとテーブルロックするかどうかまで分からない
実際にやってみてテーブルロックかかるかどうかをローカルorテスト環境で試すべき
ただしデータ件数などでまた変わってくる

ID指定が最強
テーブルロック避けたいなら対象データのIDとってきて、ID指定でINSERT-SELECTすれば安心
ただしSELECTクエリも重いやつは気をつけて


 感想deleted_at、疑ってすまん。

 参考https://zenn.dev/osshy/articles/62c52692b4bc04
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	hoge_table1	range	idx_hoge_table1,idx_fuge_colmun3	idx_fuge_colmun2	5		3916698	2.82	Using index condition; Using where
1	SIMPLE	hoge_table2	eq_ref	PRIMARY,idx_fuge_colmun3	PRIMARY	4	hoge_table1.hoge_table2_id	1	100
1	SIMPLE	hoge_table3	eq_ref	PRIMARY	PRIMARY	4	hoge_table2.hoge_table3_id	1	10	Using where
背景

EXPLAINで調べる

実行クエリのEXPLAINを確認

EXPLAINの結果で気になった点をさらに確認

実際にクエリ実行して、しらみつぶしに調べる

主キー(idカラム)で条件指定してみる

等価検索のみにしてみる

否定形の条件のみにしてみる

日付カラムを条件に指定してみる

結論

学んだこと

感想

参考

Discussion
