起こり

仕事で「一対多のリレーションを複数含んだちょっとややこしめのクエリのパフォーマンス改善」を仰せつかった。複数のユーザー情報の一覧を取得し、さらにユーザーごとに紐づけられたいくつかのテーブルから情報を集計しなければならない。そのコードは一覧を一行ずつループして追加のクエリを発行していて、典型的なN+1問題を引き起こしていた。

問題のコード抜粋

-- APIハンドラの先頭で、対象ユーザーを取得
SELECT id,name FROM users WHERE id IN (1001,1002,1003);

-- for _, u := range users { // ユーザーごとのループで詳細情報を追加
    SELECT product,amount FROM purchases WHERE user_id = ?;	-- ? はユーザーID; u.id
    SELECT type,value FROM contacts WHERE user_id = ?;		-- ? はユーザーID

レスポンス例

[
  {
    "id": 1002,
    "name": "はなこ",
    "purchases": [
      {
        "product": "商品564",
        "amount": 298
      },
      {
        "product": "商品194",
        "amount": 1000
      },

      // ...中略
    ],
    "contacts": [
      "ooo-0123-4567",
      "name@example.com"
      // ...中略
    ],
    "family_id": 502,
    "family": [ 1001, 1003 ],

    // ...中略
  },

  // ...ユーザーがたくさん
]

`GROUP_CONCAT()`

ChatGPTに相談したところこんなSQLをお出しされた。

GROUP_CONCAT()で一行に

SELECT
  U.id,
  U.name,
  GROUP_CONCAT(P.product) AS purchases, 
  GROUP_CONCAT(C.value) AS contacts
FROM users AS U
LEFT JOIN purchases AS P
  ON U.id = P.user_id
LEFT JOIN contacts AS C
  ON U.id = C.user_id
WHERE U.id IN (1001,1002,1003) -- 対象ユーザーIDのリスト
GROUP BY U.id;

クエリ結果
id	name	purchases	contacts
1001	たろう	あんパン,あんパン, 牛乳,牛乳	tarou@example.com, 070-XXXX-1234, tarou@example.com, 070-XXXX-1234
1002	はなこ		080-YYYY-9999
1003	ごん	食券,消しゴム,鉛筆

なるほどGROUP_CANCAT()ね。親テーブル一行当たり子テーブルを複数行読まなけれなならないところをグループ化して一行にまとめられるわけか。これは使えそう。
しかし、文字列連結しているだけなので、いにしえのCSVと同じでDBの内容物によっては区切り文字や引用符が問題になるかもしれない。

`JSON_ARRAYAGG()`

もう少しうまくできないか調べてみると、GROUP_CONCAT()と似ているが文字列の代わりにJSONを返すJSON_ARRAYAGG()というのが見つかった。
JSONなら今時の言語やフレームワークで標準的に扱えるであろう。

JSON_ARRAYAGG()でパーズを楽に

SELECT
  U.id,
  U.name,
  JSON_ARRAYAGG(P.product) AS purchases, 
  JSON_ARRAYAGG(C.value) AS contacts
FROM users AS U
LEFT JOIN purchases AS P
  ON U.id = P.user_id
LEFT JOIN contacts AS C
  ON U.id = C.user_id
WHERE U.id IN (1001,1002,1003) -- 対象ユーザーIDのリスト
GROUP BY U.id;

クエリ結果
id	name	purchases	contacts
1001	たろう	["あんパン", "あんパン", "牛乳", "牛乳"]	["tarou@example.com", "070-XXXX-1234", "tarou@example.com", "070-XXXX-1234"]
1002	はなこ	[null]	["080-YYYY-9999"]
1003	ごん	["食券", "消しゴム", "鉛筆"]	[null, null, null]

AGG は Aggregation (集約) のこと

`JSON_OBJECT()`

JSON_ARRAYAGG()は、JSON_OBJECT()と組み合わせることが多いらしい。
今回集約すべき購入履歴はIDや品名だけの配列でなくそれぞれオブジェクトの配列として扱えれば都合がよろしい。

JSON_OBJECT()を組み合わせてみる

SELECT
  U.id,
  U.name,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'product', P.product,
      'amount',  P.amount
    )
  ) AS purchases_json
FROM users AS U
LEFT JOIN purchases AS P
  ON U.id = P.user_id
WHERE U.id = 1001
GROUP BY U.id;

クエリ結果
id	name	purchases_json
1001	たろう	[ { "product": "あんパン", "amount": 50 }, { "product": "牛乳", "amount": 100 } ]

SQLはOracle7のころからやっとるけど、JSON_ARRAYAGG()もJSON_OBJECT()も知らんかった。最新動向をまったくキャッチアップできていない。困ったもんや。

`GROUP_CONCAT(DISTINCT ...)`

ところで結果をよくみると、電話番号が二重になっていたり何か違和感がある。

これはうまくないなと思いつつgeminiに同じ質問をしてみると DISTINCT をつければ良いという。(ChatGPT と gemini は逆だったかもしれない)

DISTINCTで重複を排除

SELECT
  U.id,
  U.name,
  GROUP_CONCAT(DISTINCT P.product) as purchases, 
  GROUP_CONCAT(DISTINCT C.value) AS contacts
FROM users AS U
LEFT JOIN purchases AS P
  ON U.id = P.user_id
LEFT JOIN contacts AS C
  ON U.id = C.user_id
WHERE U.id IN (1001,1002,1003)
GROUP BY U.id;

クエリ結果
id	name	purchases	contacts
1001	たろう	あんパン,牛乳	070-XXXX-1234, tarou@example.com
1002	はなこ	NULL	080-YYYY-9999
1003	ごん	消しゴム,鉛筆,食券	NULL

重複がなくなってそれっぽい結果になったかもしれないが、いまいちセコいごまかしに思える。
それにJSON_ARRAYAGG()ではDISTINCTは使えないようだ。

なんでこんなことに...、ちょっと考えてみよう

どうしてこうなったかというと、グループ化を解いてみれば一目瞭然で、「購入:多」✖︎「連絡先:多」のマトリクスに展開されとるやないかい？
よく考えれば当たり前である。ChatGPTに騙された。

ためしにグループ解除

SELECT
  U.id,
  U.name,
  P.product,
  C.value
FROM users AS U
LEFT JOIN purchases AS P
  ON U.id = P.user_id
LEFT JOIN contacts AS C
  ON U.id = C.user_id
WHERE U.id IN (1001,1002,1003);

id	name	product	value
1001	たろう	あんパン	tarou@example.com
1001	たろう	あんパン	070-XXXX-1234
1001	たろう	牛乳	tarou@example.com
1001	たろう	牛乳	070-XXXX-1234
1002	はなこ	NULL	080-YYYY-9999
1003	ごん	食券	NULL
1003	ごん	消しゴム	NULL
1003	ごん	鉛筆	NULL

つまるところ外部結合してからグループ化するのがおかしいのでは？

完成系

したがって操作順を入れ替え、

グループ化＋ JSON_ARRAYAGG() で一行にまとめてから、
外部結合する

で、良いはず。

SELECT
  U.id,
  contacts_json,
  purchases_json
FROM users AS U
LEFT JOIN (
  SELECT
    user_id,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'product', product,
        'amount',  amount
      )
    ) AS purchases_json
  FROM purchases
  WHERE user_id IN (1001,1002,1003) -- 外部結合する前にフィルタしておいた方が速い
  GROUP BY user_id
) AS P ON P.user_id = U.id
LEFT JOIN (
  SELECT
    user_id,
    JSON_ARRAYAGG(value) AS contacts_json
  FROM contacts
  WHERE user_id IN (1001,1002,1003)
  GROUP BY user_id
) AS C ON C.user_id = U.id
WHERE U.id IN (1001,1002,1003);

id	purchase_json	contact_json
1001	[ { "product": "牛乳", "amount": 100 }, { "product": "あんパン", "amount": 150 } ]	[ "070-XXXX-1234", "tarou@example.com" ]
1002	NULL	[ "080-YYYY-YYYY" ]
1003	[ { "product": "鉛筆", "amount": 80 }, { "product": "消しゴム", "amount": 100 }, { "product": "食券", "amount": 3000 } ]	NULL

これでええ感じ。ユニットテストもパス。
インデックスの効いたキレのいいクエリ一発、ループ内でのクエリ発行がいらなくなり、一対多が一行のJSONにまとめられたことにより、APIレスポンスのために多少の加工が必要になるがそれでも応答時間が数秒以内に収まった。同じ条件で120秒もかかっていたので劇的な改善といえよう。

かくして、ChatGPTを出しぬき、JSON_ARRAYAGG()の有効利用法を見い出した末端エンジニアは辛くも任務を果たしたのであった。

注意: 実物は子テーブルが十数個にサブクエリや自己結合を含んだよりややこしい処理になっていますが話を単純化するため割愛してあります。ユーザー数千から数万人の規模を想定。