Railsで謎のDISTINCTクエリが発行される件

Railsのクエリログに発行した覚えの無いDISTINCTクエリがあったので、原因を調べてみた。

環境

ソフトウェア	バージョン
Ruby	2.7.4
Ruby on Rails	6.0.4.1
MySQL	5.7.35

再現例

ブログの記事を表すarticlesテーブルと、記事に対するコメントを表すcommentsテーブルを例として使う:

1つの記事に対してコメントは何個でもつけられる（articles:comments = 1:多）ので、モデルにリレーションとして定義しておく:

# models/article.rb

class Article < ApplicationRecord
  has_many :comments
end

# models/comments.rb

class Comment < ApplicationRecord
  belongs_to :article
end

そして、Railsコンソール（rails c）から以下を実行する:

# （データ準備1）記事レコードを5つ作成する
5.times { Article.create! }
# （データ準備2）記事レコードそれぞれにコメントレコードを2つ作成する
Article.all.each { |article| 2.times { article.comments.create! } }
# （問題のクエリ）記事レコードを3件、コメントと一緒に取得する
Article.eager_load(:comments).limit(3)

ログ出力を確認すると、以下の2つのクエリが発行されていた（読みやすくするため整形してある）:

-- クエリ1
SELECT DISTINCT `articles`.`id`
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
LIMIT 3
;

-- クエリ2
SELECT
    `articles`.`id` AS t0_r0,
    `articles`.`created_at` AS t0_r1,
    `articles`.`updated_at` AS t0_r2,
    `comments`.`id` AS t1_r0,
    `comments`.`article_id` AS t1_r1,
    `comments`.`created_at` AS t1_r2,
    `comments`.`updated_at` AS t1_r3
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
WHERE `articles`.`id` IN (1, 2, 3)
;

クエリ1の検証

まずはクエリ1の中身を見てみる:

-- クエリ1
SELECT DISTINCT `articles`.`id`
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
LIMIT 3
;

このクエリではユニークな（DISTINCT） articles.id を3件取得している。
commentsとのLEFT OUTER JOINによって複数のコメントと紐づく記事レコードのIDは重複して返されるところだが、
DISTINCTの効果で同じIDの重複は排除され、結果としてはユニークなID3件が返されるようになっている。
よって、クエリ1では取得対象のarticlesレコードのIDのみを取得しているが、その他のarticlesのカラムや紐づくcommentsレコードのカラムは一切取得していないと分かる。

クエリ2の検証

次にクエリ2の中身を見てみる:

-- クエリ2
SELECT
    `articles`.`id` AS t0_r0,
    `articles`.`created_at` AS t0_r1,
    `articles`.`updated_at` AS t0_r2,
    `comments`.`id` AS t1_r0,
    `comments`.`article_id` AS t1_r1,
    `comments`.`created_at` AS t1_r2,
    `comments`.`updated_at` AS t1_r3
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
WHERE `articles`.`id` IN (1, 2, 3)
;

SELECT句の中身は取得対象のカラムそれぞれに名前をつけているだけなので、以下のように * で置き換えても挙動は変わらない:

-- クエリ2
SELECT *
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
WHERE `articles`.`id` IN (1, 2, 3)
;

クエリ1とかなり似ているものの、以下の違いがあることが分かる:

1. articles.id だけでなくarticlesとcommentsの全カラムをSELECTしている
2. LIMITで件数を絞り込む代わりに、取得対象の articles.id を直接指定している

2の articles.id はお察しの通りクエリ1で取得したIDだ。
つまり、以下のコード:

Article.eager_load(:comments).limit(3)

では、見た目とは裏腹に以下の2段階に分けてクエリが発行されているということになる:

1. 取得対象のarticlesレコードのIDを取得する
2. 取得対象のarticlesレコードとcommentsレコードの全カラムを取得する

なぜクエリが2つ必要なのか？

似たようなクエリが2回も発行されていて無駄があるように思えるかもしれないが、よくよく考えると必要不可欠だったりする。
試しにクエリ2でIDを使って絞り込む代わりにLIMITを使うように書き換えてみる:

-- クエリ2（無駄のない版？）
SELECT *
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
LIMIT 3
;

問題は、このクエリだとarticlesレコードが3件取れるとは限らないことだ。
今回の例ではarticlesレコードそれぞれについてcommentsレコード2件が紐づくので、取得結果は以下のようになる:

articles.id	comments.id	含まれる？
1	1	o
1	2	o
2	3	o
2	4	x
3	5	x
3	6	x

articlesレコードはIDが1と2の2件しか取れていない上、IDが2のarticlesレコードについては2件と紐づくはずのcommentsレコードのうち1件しか取れていない。
articles:comments = 1:多 であるため、LEFT OUTER JOINの結果の行数がarticlesレコードの数と一致せず、単純なLIMITだけではarticlesを3件取ってくることができないというわけだ。
逆に、1:1や多:1のリレーション、つまりモデルで has_one または belongs_to を使って定義されているリレーションでは、このような謎のDISTINCTクエリ問題は発生しない。
実際に、以下のコードではクエリは1つしか発行されない:

Comment.eager_load(:article).limit(3)

SELECT *
FROM `comments`
LEFT OUTER JOIN `articles`
ON `articles`.`id` = `comments`.`article_id`
LIMIT 3
;

上述の通り、CommentモデルからArticleモデルへのリレーションは belongs_to :article なので、
commentsレコード1件と紐づくarticlesレコードは1件のみ、つまりLEFT OUTER JOINの結果の行数がcommentsレコードの数と一致するため、1回のクエリで完結できるという違いがある。

謎のDISTINCTクエリが発行される条件をまとめると、次の通りとなる:

• モデルAからモデルBに対してリレーション has_many :bs が定義されている
• A.eager_load(:bs).limit(n) または A.eager_load(:bs).offset(n) のように、モデルBのeager_loadに加えてlimitかoffsetを使っている

limitやoffsetはページネーションには欠かせないので、N+1クエリを防ぐためにeager_loadを使っている方は知らないうちにこの条件に当てはまっているかもしれない。

DISTINCTクエリが出ないようにするには？

理由が分かったところで、このDISTINCTクエリが発行されないようにする方法はあるのか考えてみる。
この記事の例のクエリは必要最小限に簡略化しているため、DISTINCTクエリが発行されたところで元々のクエリコストが些末なので実害はないかと思うが、以下のようにwhereの条件を追加すると:

Article.eager_load(:comments).where(created_at: 1.day.ago..).limit(3)

以下のように2つのクエリ両方に条件が追加されてしまうので、クエリの複雑さによっては無視できないコストを払っているかもしれない:

-- クエリ1
SELECT DISTINCT `articles`.`id`
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
WHERE `articles`.`created_at` >= '2020-12-11 12:53:50.464526'
LIMIT 3
;

-- クエリ2
SELECT *
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
WHERE `articles`.`created_at` >= '2020-12-11 12:53:50.464526'
AND `articles`.`id` IN (1, 2, 3)
;

Railsアプリを開発している方は、ログファイルの中身を DISTINCT で検索するなどして余計なコストを払っていないか調べてみることをオススメする。
コストの評価方法の一つとして、評価対象のクエリの前に EXPLAIN FORMAT=JSON をつけてクエリを実行する方法がある（MySQL限定。その他のDBでも同等の機能はあるかもしれない）:

EXPLAIN FORMAT=JSON
SELECT DISTINCT `articles`.`id`
FROM `articles`
LEFT OUTER JOIN `comments`
ON `comments`.`article_id` = `articles`.`id`
WHERE `articles`.`created_at` >= '2020-12-11 12:53:50.464526'
LIMIT 3
;

結果として取得できるJSONの中で注目すべきは $.query_block.cost_info.query_cost 項目だ:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "8.15"
    },

コストの単位がなにかは不明なものの、大きければ大きいほどコストが高いことには違いないと思う。
例えばarticlesのIDを指定して1レコード取得するクエリのコストは 1.00 、それにcommentsとのJOINを加えたクエリのコストは 3.80 となったので、
上のコスト 8.15 はそれらとほぼ同等 = 大したコストではないと推測できる。
（もちろん、クエリの発行頻度も考慮に入れるとちりも積もれば山となる可能性もあるので、そこも考慮が必要。）
逆に、コストが1,000や10,000のように4桁、5桁となってくると、そのクエリがアプリケーションの応答速度やDBのリソース使用率等に悪影響を与える可能性も捨てきれないだろう。

さて、肝心のDISTINCTクエリが出ないようにする方法だが、「eager_loadをpreloadに変える」というだけの話だったりする:

Article.preload(:comments).where(created_at: 1.day.ago..).limit(3)

おさらいすると、eager_loadはLEFT OUTER JOINを活用することで1回のクエリでリレーションも含めて取得する（今回の例では2回クエリが発行されているが）のに対し、
preloadはリレーションをJOINを使わずに別個のクエリで取得するという違いがある。
よって、このコードを実行した結果のクエリは以下のようになる:

-- クエリ1
SELECT `articles`.*
FROM `articles`
WHERE `articles`.`created_at` >= '2020-12-11 12:53:50.464526'
LIMIT 3
;

-- クエリ2
SELECT `comments`.*
FROM `comments`
WHERE `comments`.`article_id`
IN (1, 2, 3)
;

クエリが2回発行されることは同じなものの、クエリ1のコストは 8.15 から 2.80 に、クエリ2のコストは 7.99 から 8.00 になったので、合計のコストは少なくなっている。
もっとも、この例ではそもそも低コストなので有意な差につながるかは疑わしい。
データの入り方やインデックスの有無、LIMITの件数によっては逆にコストが増えることもあるかもしれないので、 EXPLAIN FORMAT=JSON などを有効活用して比較検証することをオススメする。
とはいえ、書いた覚えのないDISTINCTクエリがログに出てくるのはやや不気味なので、コスト云々はともかくpreloadを使いたいというのが個人的な所感。

eager_loadとpreloadの使い分けについて

一般論として、has_manyのリレーションはeager_loadでなくpreloadを使い、belongs_toやhas_oneのリレーションはeager_loadを使うのが妥当なように思う。
has_manyでeager_loadを使うことの問題は今回扱った謎のDISTINCTクエリ問題に加えて、LEFT OUTER JOINによってデータの重複が発生してしまうという問題もある。
今回の例に沿って、ブログの記事とコメントを取得することを考えてみる。
eager_loadを使う場合、それぞれのarticlesレコードが紐づくcommentsレコードの数だけ重複して繰り返されるので、
仮にブログの記事本文がarticles.contentカラムに含まれる場合、コメントの数だけ同じarticles.contentの値が繰り返されることとなる:

articles.id	articles.content	comments.id
1	ものすごく長い記事本文	1
1	ものすごく長い記事本文	2
1	ものすごく長い記事本文	3

データの重複によって、ネットワークI/Oの量やアプリケーションのメモリ使用量が増えることにつながることを考えると、preloadを使って重複なくデータを取得したほうがリソースの有効活用になると思う。

preloadよりもeager_loadをあえて使う理由としてよく挙げられるのが「クエリの発行数を減らす」ことだが、has_manyのリレーションの場合そもそもeager_loadでも2回クエリが発行されてしまうので回数の差はない。
また、I/Oに時間がかかるのは事実とはいえ、「クエリ2個を1個に減らすことでパフォーマンスが劇的に改善した」という経験は今のところない。
N+1クエリを1クエリに減らすことは間違いなく有意義だが、2、3クエリを1クエリに減らすことが有意義かは検証の余地があると思う。
クエリの発行数だけでなく、コードの可読性や個々のクエリのコストなどのトレードオフを考えるべきなので、「クエリの発行数が増える」というだけでpreloadを敬遠しないでほしい。

というわけで、今までeager_loadだけを使っていた方も、これを機にpreloadにも目を向けてみてはどうだろうか。