EXISTS と INNER JOIN のパフォーマンスについて

EXISTSをINNER JOINに置き換えるとパフォーマンスが上がるという主張をよく見かけますが、ちょっと怪しい記事が多かったので調べました。

環境

他のDBでは確認していないのでMySQLに限定した話題です。

usersテーブルは複数のroom_usersを持ちます、レコード数は100万程度で試しています。

sh-4.4# mysql --version
mysql  Ver 8.0.30 for Linux on aarch64 (MySQL Community Server - GPL)

erDiagram

users {
  id integer PK
}

room_users {
  id integer PK
  user_id integer FK
}

users ||--o{ room_users : has_many

事前調査

調べていくと冒頭の主張とは異なるものの確度が高そうな情報を見つけた。

DBサーバーの種類に言及はなかったがStack Overflow[1][2]とQuora[3]の質問の回答をまとめると

uniqueなカラムについてINNER JOINする場合はEXISTSとパフォーマンスは変わらない
DISTINCTが適用されるレコードに対してINNER JOINする場合はEXISTSのほうがパフォーマンスが良い

INNER JOINとEXISTSはパフォーマンスは変わらず、重複削除の必要があるDISTINCT + INNER JOINになるとパフォーマンスが悪くなるとのこと。

実行計画

EXISTS, INNER JOIN, DISTINCT + INNER JOINそれぞれについて実行計画を出力して比較する。

EXISTS

select `users`.* from `users` where `users`.`id` != 1 and exists (select * from `room_users` where `users`.`id` = `room_users`.`user_id` and `room_users`.`room_id` in (10,11,12,13,14)) order by `users`.`updated_at` desc limit 30;

INNER JOIN

select `users`.* from `users` inner join `room_users` on `room_users`.`user_id` = `users`.`id` and `room_users`.`room_id` in (10,11,12,13,14) where `users`.`id` != 1 order by `users`.`updated_at` desc limit 30;

DISTINCT + INNER JOIN

select distinct `users`.* from `users` inner join `room_users` on `room_users`.`user_id` = `users`.`id` and `room_users`.`room_id` in (10,11,12,13,14) where `users`.`id` != 1 order by `users`.`updated_at` desc limit 30;

Optimizer Trace

 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

出力された join_preparation で最終的に展開されるクエリは以下のようになる、semi joinは重複を取り除いた結果を結合するというもので、結合後に重複を取り除くDISTINCT + INNER JOINよりも効率が良いとのこと[4]

長くなるので省略するが considered_execution_plans に表示されるEXISTSとINNER JOINの実行計画は同じものだった、EXISTSとINNER JOINのパフォーマンスの差は重複を取り除くタイミングによることになる。

EXISTS

 select `users`.`id` AS `id`... from `users` semi join (`room_users`) where ((`users`.`id` <> 1) and (`room_users`.`room_id` in (10,11,12,13,14)) and (`users`.`id` = `room_users`.`user_id`)) order by `users`.`updated_at` desc limit 30

INNER JOIN

select `users`.`id` AS `id`... from `users` join `room_users` where ((`users`.`id` <> 1) and (`room_users`.`user_id` = `users`.`id`) and (`room_users`.`room_id` in (10,11,12,13,14))) order by `users`.`updated_at` desc limit 30

DISTINCT + INNER JOIN

select distinct `users`.`id` AS `id`... from `users` join `room_users` where ((`users`.`id` <> 1) and (`room_users`.`user_id` = `users`.`id`) and (`room_users`.`room_id` in (10,11,12,13,14))) order by `users`.`updated_at` desc limit 30