时间戳

首页 > 解决方案 > 为什么我会收到这么多对我查询不多的表的 SQL 调用？

sql - 为什么我会收到这么多对我查询不多的表的 SQL 调用？

问题描述

我注意到我的editions表收到了多个 SQL 调用，即使我只在控制器中加载了一次。对我来说更奇怪的是，edition即使我正在加载show单个版本的页面，每次调用都是不同的。

控制台输出在这里：

Started GET "/editions/48" for 127.0.0.1 at 2020-03-07 08:37:53 +0530
Processing by EditionsController#show as HTML
  Parameters: {"id"=>"48"}
  Edition Load (0.5ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 48], ["LIMIT", 1]]
  User Load (0.8ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Rendering editions/show.html.erb within layouts/application
  Product Load (0.4ms)  SELECT  "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Rendered shared/_arrow.html.erb (0.3ms)
  Edition Load (0.6ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date < '2018-02-18') ORDER BY "editions"."date" DESC LIMIT $1  [["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date < '2018-02-18') ORDER BY "editions"."date" DESC LIMIT $1  [["LIMIT", 1]]
  Edition Load (0.5ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date > '2018-02-18') ORDER BY "editions"."date" ASC LIMIT $1  [["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE (product_id = 1 AND date > '2018-02-18') ORDER BY "editions"."date" ASC LIMIT $1  [["LIMIT", 1]]
  Rendered editions/_admin_controls.html.erb (0.9ms)
  Section Load (0.8ms)  SELECT DISTINCT "sections".* FROM "sections" INNER JOIN "contents" "contents_sections" ON "contents_sections"."section_id" = "sections"."id" INNER JOIN "contents" ON "sections"."id" = "contents"."section_id" WHERE "contents"."edition_id" = $1 AND "contents"."top_story" = $2 ORDER BY "sections"."position" ASC  [["edition_id", 48], ["top_story", "f"]]
  Content Load (0.6ms)  SELECT "contents".* FROM "contents" WHERE "contents"."edition_id" = $1 AND "contents"."top_story" = $2  [["edition_id", 48], ["top_story", "t"]]
  Content Load (0.6ms)  SELECT "contents".* FROM "contents" WHERE "contents"."edition_id" = $1 AND "contents"."top_story" = $2 ORDER BY "contents"."position" ASC  [["edition_id", 48], ["top_story", "t"]]
  Rendered contents/_admin_controls.html.erb (0.5ms)
  Source Load (0.4ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 5], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.0ms)
  Rendered contents/_admin_controls.html.erb (0.3ms)
  Source Load (0.4ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (6.9ms)
  Rendered contents/_admin_controls.html.erb (0.3ms)
  CACHE Source Load (0.0ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.9ms)
  Rendered contents/_admin_controls.html.erb (0.3ms)
  CACHE Source Load (0.0ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.0ms)
  Content Load (0.6ms)  SELECT "contents".* FROM "contents" WHERE "contents"."section_id" = $1 ORDER BY "contents"."position" ASC  [["section_id", 2]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 40], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 22], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 14], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 41], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 14], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 38], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 34], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 36], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 36], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 27], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 48], ["LIMIT", 1]]
  Rendered contents/_admin_controls.html.erb (0.4ms)
  Source Load (0.4ms)  SELECT  "sources".* FROM "sources" WHERE "sources"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Rendered shared/_content.html.erb (7.4ms)
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 24], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 13], ["LIMIT", 1]]
  Edition Load (0.2ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 20], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 37], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 34], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 23], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 24], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 45], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 36], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 15], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 24], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 37], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 40], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 47], ["LIMIT", 1]]
  CACHE Edition Load (0.0ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 14], ["LIMIT", 1]]
  Edition Load (0.4ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 10], ["LIMIT", 1]]
  Edition Load (0.3ms)  SELECT  "editions".* FROM "editions" WHERE "editions"."id" = $1 LIMIT $2  [["id", 29], ["LIMIT", 1]]
  Rendered editions/_edition_sections.html.erb (142.3ms)
  Rendered editions/show.html.erb within layouts/application (246.2ms)
  Rendered shared/_nav_searchbar.html.erb (0.8ms)
  Rendered shared/_nav.html.erb (4.1ms)
  Visit Load (0.5ms)  SELECT  "visits".* FROM "visits" WHERE "visits"."visit_token" = $1 ORDER BY "visits"."id" ASC LIMIT $2  [["visit_token", "40979cb1-0c92-486a-a274-c937560c03b1"], ["LIMIT", 1]]
   (0.2ms)  BEGIN
  Visit Load (0.3ms)  SELECT  "visits".* FROM "visits" WHERE "visits"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  SQL (0.4ms)  INSERT INTO "ahoy_events" ("visit_id", "user_id", "name", "properties", "time") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["visit_id", 1], ["user_id", 1], ["name", "Load edition"], ["properties", "{\"controller\":\"editions\",\"action\":\"show\",\"id\":\"48\"}"], ["time", "2020-03-07 03:07:53.513209"]]
   (0.9ms)  COMMIT
Completed 200 OK in 400ms (Views: 367.2ms | ActiveRecord: 16.8ms)

我的其余代码可以在这里找到

我最好的猜测是show视图中的一些逻辑正在创建对数据库的更多查询，但我不确定在哪里或如何优化它（我是 SQL 和 Rails 的初学者）。这实际上已成为我的生产站点上的一个更大的问题，它导致 Heroku 发出错误说R14 - Memory Quota Exceeded

有什么建议吗？

标签： sqlruby-on-railspostgresqloptimization

解决方案

这称为N+1 问题。

在_editions_sections.html.erb你有：

if (content.edition == @edition) && (content.top_story == false)

这样做是针对它加载的循环中的每个内容edition与@edition.

您可以通过两种方式解决它：

  • 1（不是通常的解决方案）

    if (content.edition_id == @edition.id) && (content.top_story == false)

    这将只检查edition_id列而不加载edition.

  • 2 使用includes

    在 中edition.rb，sections_with_regular_content看起来像这样：

    def sections_with_regular_content
  sections.includes(contents: :edition).where(contents: { top_story: false })
end

includes有效地找出所有需要的 id 并将它们加载到单个查询中或通过连接加载。

推荐阅读