优化很难查询（可能与squeel） [英] Optimize difficult query (possibly with squeel)

问题描述

有这样code（使用PublicActivity宝石和放大器; Squeel）

 高清指数
    @activities = Activity.limit（20）.order {created_at.desc}
    @One = @ activities.where {trackable_type =='邮'}包括（可跟踪：[：作者：项目]）
    。@two = @ activities.where {trackable_type =='项目'}包括可跟踪：[：所有者]
    @activities = @One + @two
  结束
 

不过，它创建 8 SQL请求：

 选择活动。* FROM活动WHERE活动。trackable_type=邮报ORDER BY活动。created_atDESC LIMIT 20

      选择上岗。* FROM上岗WHERE上岗。IDIN（800，799，798，797，796，795，794，793，792，791，790，789，788，787，786， 785，784，783，782，781）

      选择用户。*从用户WHERE用户，IDIN（880，879，878，877，876，875，874，873，872，871，869，868，867，866，865， 864，863，862，861，860）

      选择项目。*从项目WHERE项目，IDIN（80，79）

      选择活动。* FROM活动WHERE活动。trackable_type=项目ORDER BY活动。created_atDESC LIMIT 20

      选择项目。* FROM项目WHERE项目，IDIN（80，79，78，77，76，75，74，73，72，71，70，69，68，67，66， 65，64，63，62，61）

     选择用户。*从用户WHERE用户，IDIN（870，859，848，837，826，815，804，793，782，771，760，749，738，727，716， 705，694，683，672，661）
 

1. activites要求未加入
2. 在一些用户（后业主和项目业主）将被加载两次
3. 在一些项目被载入两次
4. @activities是数组。导轨关系合并方法（除了 + ）不与code以上的工作。

任何想法，以优化呢？

解决方案

在简单地说，你不能进一步优化任何不使用SQL。这是Rails的业务的方式。它不允许访问加入其中该查询所构成的AR模型外域。因此，为了得到其他的表中的值，它在每一个查询。

也不允许 UNION 或花哨的，其中提供解决问题的其他方面的条件。

好消息是，这些查询都是有效率的（因为trackable_type有标号）。如果结果的大小是任何实质性（比如几十行），在I / O时间将主导7个简单的查询副轻微的额外开销1复杂。

即使使用SQL，这将是很难得到你想要在一个查询所有连接的结果。 （这是可以做到的，但结果将是一个哈希值，而不是一个AR实例。所以依赖code将是丑陋的。）一查询，每个表是有线pretty的深入到活动记录。

@ Mr.Yoshi的解决方案是一个很好的妥协使用最小SQL但它不会让你有选择地加载任何作者或项目 + 所有者根据 trackable_type 字段。

修改

以上是正确的Rails的3.轨道4为@CMW说，在 eager_load 方法做同样的包括使用外部连接，而不是单独的查询。这就是为什么我喜欢这样！我总是学到一些东西。

There is such code(using PublicActivity gem & Squeel)

  def index
    @activities = Activity.limit(20).order { created_at.desc }
    @one = @activities.where{trackable_type == 'Post'}.includes(trackable: [:author, :project])
    @two = @activities.where{trackable_type == 'Project'}.includes trackable: [:owner]
    @activities = @one + @two
  end

But it creates 8 SQL requests:

 SELECT "activities".* FROM "activities" WHERE "activities"."trackable_type" = 'Post' ORDER BY "activities"."created_at" DESC LIMIT 20

      SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (800, 799, 798, 797, 796, 795, 794, 793, 792, 791, 790, 789, 788, 787, 786, 785, 784, 783, 782, 781)

      SELECT "users".* FROM "users" WHERE "users"."id" IN (880, 879, 878, 877, 876, 875, 874, 873, 872, 871, 869, 868, 867, 866, 865, 864, 863, 862, 861, 860)

      SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (80, 79)

      SELECT "activities".* FROM "activities" WHERE "activities"."trackable_type" = 'Project' ORDER BY "activities"."created_at" DESC LIMIT 20

      SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (80, 79, 78, 77, 76, 75, 74, 73, 72, 71, 70, 69, 68, 67, 66, 65, 64, 63, 62, 61)

     SELECT "users".* FROM "users" WHERE "users"."id" IN (870, 859, 848, 837, 826, 815, 804, 793, 782, 771, 760, 749, 738, 727, 716, 705, 694, 683, 672, 661)

1. activites request are not joined
2. some users (post owner and project owner) are loaded twice
3. some projects are loaded twice
4. @activities is Array. Rails relations merge methods(except +) don't work with the code above.

Any ideas to optimize it?

解决方案

In a nutshell, you can't optimize any further without using SQL. This is the way Rails does business. It doesn't allow access to join fields outside the AR model where the query is posed. Therefore to get values in other tables, it does a query on each one.

It also doesn't allow UNION or fancy WHERE conditions that provide other ways of solving the problem.

The good news is that these queries are all efficient ones (given that trackable_type is indexed). If the size of the results is anything substantial (say a few dozen rows), the i/o time will dominate the slight additional overhead of 7 simple queries vice 1 complex one.

Even using SQL, it will be difficult to get all the join results you want in one query. (It can be done, but the result will be a hash rather than an AR instance. So dependent code will be ugly.) The one-query-per-table is wired pretty deeply into Active Record.

@Mr.Yoshi's solution is a good compromise using minimal SQL except it doesn't let you selectively load either author or project+owner based on the trackable_type field.

Edit

The above is all correct for Rails 3. For Rails 4 as @CMW says, the eager_load method will do the same as includes using an outer join instead of separate queries. This is why I love SO! I always learn something.

这篇关于优化很难查询（可能与squeel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！