SELECTで最適化されるサブクエリ
MySQLのサブクエリは他のRDBMSに比べて性能の最適化面で劣っている印象だが、単純なSELECT文に関しては最適化され、問題なくINDEXが使われるようになっている。
親子関係のあるテーブル（親テーブル x, 子テーブル y）があるとする。
xを特定の条件でフィルタして、それにマッチするyのデータを取得するSQLをサブクエリとJOINで書いてexplainしてみても、同じ結果になった。
-- サブクエリ
select * from y where x_id in (select id from x where 略);
-- JOIN
select y.* from y join x on y.x_id = x.id where 略;DELETEで最適化されないサブクエリ
先ほど最適化されたSELECT文と同等のDELETE文でも最適化されると思ったら、DELETE文では最適化されなかった。
delete from y where x_id in (select id from x where 略);このSQLではyが全件走査されてしまった。
DELETEでもINDEXのためにサブクエリではなくMultiple-Table Syntaxを使う
DELETEでも最適化するためにJOINを使う。Multiple-Table Syntaxというようだ。
https://dev.mysql.com/doc/refman/8.0/en/delete.htmlの「Multiple-Table Syntax」に構文が、「Multi-Table Deletes」に詳しい説明が、記載されている。
今回の例でいうと、以下のSQLにすれば、INDEXを利用したDELETEが実行される。
delete y from y join x on y.x_id = x.id where 略;ON DELETE CASCADEがない場合に便利なMulti-Table Deletes
Multi-Table Deletesというだけあって複数のテーブルを削除できるので、y, xと両方のテーブルを指定するとことで、一度に両方のテーブルから削除できる。
delete y, x from y join x on y.x_id = x.id where 略;べき論で言えばy.x_idには外部キー制約があるべきで、x, yの両テーブルを消したいのが要件であれば、ON DELETE CASCADEにして親を消した時点で子も自動で削除されるようにした方がいい。
ただ実際の開発では外部キー制約は論理設計までで実装上は制約を付与しない場合も多い。そのような場合にもMulti-Table Deletesが役立つだろう。