Ruby on Railsの木構造Gemのacts_as_treeで、N+1を消し去る

手元動作環境

当然、サービスインしているコードを乗せるわけにはいかないので、手元環境で似たようなコードを書いて動作検証しています。

• ruby 2.7.1p83
• Rails 6.0.3.2
• acts_as_tree (2.9.1)

木構造

古いコードを見ると、アンチパターンが多いですね。今回も同様にアンチパターンの一つである隣接リストが使われていました。さらに、N+1問題が放置され、パフォーマンス的に悪いということで対応することになりました。
今回は、仕様的にたった2階層だったので、隣接リストでもあまり問題がなさそうです。

GitHub

使い方

https://github.com/amerine/acts_as_tree#example 引用

class Category < ActiveRecord::Base
  acts_as_tree order: "name"
end

root      = Category.create("name" => "root")
child1    = root.children.create("name" => "child1")
subchild1 = child1.children.create("name" => "subchild1")

root.parent   # => nil
child1.parent # => root
root.children # => [child1]
root.children.first.children.first # => subchild1

前提

parent_id （あるいは、任意のキー） を木構造の親キーとします。

schema例

  create_table "technologies", force: :cascade do |t|
    t.string "name"
    t.bigint "parent_id"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["parent_id"], name: "index_technologies_on_parent_id"
  end

N+1の消し去り方

modelに追加した acts_at_tree によって、 children メソッドが生えますので、そちらをincludesして関連付け、事前に最小限のクエリ回数にて読み込まれるようにします。

解消コード

# model
class Technology < ApplicationRecord
  acts_as_tree

  def dig
    if children.empty?
      self
    else
      children.map(&:dig)
    end
  end
end

includesを付与してあげます。

# console
Technology
  .where(parent: nil)
  .includes(children: { children: [:children] }))
  .map(&:dig)

response

２階層のコードを抜き出して見ると、このような形になります。

{
  "id":6,
  "name":"AWS",
  "parent_id":null,
  "children":[
    {
      "id":7,
      "name":"コンピューティング",
      "parent_id":6,
      "children":[
        {
          "id":8,
          "name":"EC2",
          "parent_id":7
        },
        {
          "id":9,
          "name":"Elastic Beanstalk",
          "parent_id":7
        },
        {
          "id":10,
          "name":"Lambda",
          "parent_id":7
        }
      ]
    }
  ]
}

発行SQLの比較

includesがない場合の例

60件ぐらいデータを入れてみた
データに応じて、SQLの数が増加していく

SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IS NULL
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 1], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 2], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 3], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 4], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 5], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 6], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 6]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 7], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 7]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 8], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 9], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 10], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 11], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 11]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 12], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id  SELECT 1 AS one F_i  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id  SE= $1  [["parent_id", 14]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 15], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 16], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 16]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 17], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "ent_id", 19], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 20], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 20]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 21], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 22], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 23], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" W" = $1  [["parent_id", 24]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 25], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 26], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 27], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 28], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 29], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIM  SELECT 1 AS one FROM "technologies" WIMIT $2  [["parent_id", 31], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 31]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 32], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 33], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 34], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 34]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 35], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 36], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 37], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 38], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 39], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 39]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 40], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 41], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 42], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 42]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 43], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 44], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 44]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 45], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 46], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 46]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 47], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 48], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 49], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "ent_id", 51], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 51]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 52], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 53], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 54], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 55], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id"   SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id"   SELECT "technologies".* FROM "technolent_id", 56], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 57], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 58], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 59], ["LIMIT", 1]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" = $1  [["parent_id", 59]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["paren  SELECT 1 AS one FROM "ent_id", 61], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 62], ["LIMIT", 1]]
SELECT 1 AS one FROM "technologies" WHERE "technologies"."parent_id" = $1 LIMIT $2  [["parent_id", 63], ["LIMIT", 1]]

N+1問題解消時の例

SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IS NULL
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IN ($1, $2, $3, $4, $5, $6)  [["parent_id", 1], ["parent_id", 6], ["parent_id", 46], ["parent_id", 51], ["parent_id", 55], ["parent_id", 59]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29)  [["parent_id", 2], ["parent_id", 3], ["parent_id", 4], ["parent_id", 5], ["parent_id", 7], ["parent_id", 11], ["parent_id", 14], ["parent_id", 16], ["parent_id", 20], ["parent_id", 24], ["parent_id", 31], ["parent_id", 34], ["parent_id", 39], ["parent_id", 42], ["parent_id", 44], ["parent_id", 47], ["parent_id", 48], ["parent_id", 49], ["parent_id", 50], ["parent_id", 52], ["parent_id", 53], ["parent_id", 54], ["parent_id", 56], ["parent_id", 57], ["parent_id", 58], ["parent_id", 60], ["parent_id", 61], ["parent_id", 62], ["parent_id", 63]]
SELECT "technologies".* FROM "technologies" WHERE "technologies"."parent_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28)  [["parent_id", 8], ["parent_id", 9], ["parent_id", 10], ["parent_id", 12], ["parent_id", 13], ["parent_id", 15], ["parent_id", 17], ["parent_id", 18], ["parent_id", 19], ["parent_id", 21], ["parent_id", 22], ["parent_id", 23], ["parent_id", 25], ["parent_id", 26], ["parent_id", 27], ["parent_id", 28], ["parent_id", 29], ["parent_id", 30], ["parent_id", 32], ["parent_id", 33], ["parent_id", 35], ["parent_id", 36], ["parent_id", 37], ["parent_id", 38], ["parent_id", 40], ["parent_id", 41], ["parent_id", 43], ["parent_id", 45]]