MySQLのSQL書くとき気をつけること
8.0.20以下では、Update/Delete文でサブクエリ使って条件を絞り込まない
事象
MySQL 8.0.20以下で、where条件にサブクエリを指定した場合、先にUpdate/Delete対象のテーブルがAll scanされてしまう。
code: example.sql
delete from 明細 where 明細.請求書 in (select id from 請求書 where 請求書.請求日 = '2021-01-01');
上記のようなdelete文だと先に明細が全件ロックされてしまう
更新/削除対象レコードのあるテーブルが大量のデータを持っている場合、非常に長い時間全行排他ロックがかかってしまう
対処
MySQLの8.0.20以下では、Delete/Update時にサブクエリを使って条件の絞り込みを行わない。代わりに以下のような手段を使う
joinしてDelete/Updateする
サブクエリで指定していた結果を一度変数などに入れておいて、Delete/Update分の条件にしていする。
ユーザ定義変数の評価順序は保証されていない
下記ドキュメントにもある通りMySQLではユーザ定義変数の評価順序は保証されていない
MySQL5.7以下だとWindow関数がないこともあり、代わりにユーザ定義変数を使って同様のことを実現させたくなることがある (そうやるとできるよという記事とかもたまにある) が、動作保証されていないので本番環境などで使うべきではない