割と沼にハマりがちな知見を得たのでメモ。
本番環境だと問題なく動いていたバッチに手を加えたのでテスト環境で動作チェックしたら、なぜかロック取り合いで落ちた。何回やり直してもだめ。
そんな時はテストデータのレコード数が少ないことが原因かもしれないです。
レコード数に対して、相対的に多くの要素をIN句に指定するとフルスキャンになってしまうので注意。
例
次のようなテーブルにて、IN句に16個のキーを指定してWhereで絞り込むとします。
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int unsigned | NO | PRI | NULL | |
| value | int unsigned | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
レコード数が60件のとき
mysql> select count(*) from lock_test;
+----------+
| count(*) |
+----------+
| 60 |
+----------+
レコード数が30件のテーブルに対して16個のIN句でWhereをかけるときちんとインデックスが効いている。
問題なし。
mysql> explain select * from lock_test where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | lock_test | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 16 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
テーブルサイズが30件のとき
mysql> select count(*) from lock_test;
+----------+
| count(*) |
+----------+
| 30 |
+----------+
対して、レコード数が30件のテーブルの場合。
mysql> explain select * from lock_test where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | lock_test | NULL | ALL | PRIMARY | NULL | NULL | NULL | 30 | 53.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
60件の時と同様、IN句に16個のキーを指定してWhereをかけたのですが、インデックスによる検索ではなくテーブルスキャンになってしまっています。
ハマりがちなポイント
実はこれ実務でかなりハマりまして。
とある安定して動いているバッチがあるのですが、これに手を加えることになり、最小限のテストデータを用意して開発環境で回してみたわけですよ。
そしたらバッチが落ちる落ちる。
原因はLock wait timeout exceeded.
ロックの取り合いになっていたわけです。
しかし本番では何万、何十万のレコードを処理しているにも関わらずロックの取り合いなんかにならず安定して動いているわけで、なぜ数百レコードのテスト環境だとロックの取り合いになる・・・？
丸一日潰して出た結論が、「テストデータの数が本番データに比べて圧倒的に少ないから」でした。
MySQLくんがご丁寧にチューニングしてくれた結果、「このレコード数に対してこの数のIN句ならばテーブル全部スキャンした方が早いよね」って判断してくれちゃったんですね。
そのおかげでテーブル全体にロックがかかり、他のトランザクションがロック待ちになると。
いい知見が得られました。
同じように「本番だと動いてたのにテストデータだとなぜかロック取り合いで落ちる」って人はこの点に注意してみると良いかもです。