ruby-on-rails - 索引页的查询优化

Question

我有一个系统模型和一个报告模型。 System has_many reports 和 Report belongs_to system。每个每日报告由每个系统的 175 条记录组成。

我需要在我的 system#index 页面上进行查询，该页面应列出在最近创建报告时过滤的所有系统。这是我的第一次尝试。

@systems = System.joins('LEFT JOIN reports ON reports.system_id = systems.id').group('systems.id').order('MAX(reports.created_at) ASC')

这列出了带有报告(系统负载(2.1 毫秒))的系统，但按 system_id 而不是报告 created_at 排序。

第二次尝试

@systems = System.joins(:reports).where("reports.created_at = (SELECT MAX(created_at) FROM reports p group by system_id having p.system_id = reports.system_id)").order('reports.created_at DESC')  

尽管在 report.created_at 上有一个索引，但这个查询确实很慢(System Load (546.2ms))。

第三次尝试

@systems = System.joins(:reports).where("reports.id = (SELECT MAX(id) FROM reports p group by system_id having p.system_id = reports.system_id)").order('reports.id DESC')

也完成了工作，比第二次尝试(系统负载(468.3 毫秒))稍快，但仍然不够快。

有什么建议吗？

编辑 03032017

我在一个小的测试数据集上做了数字

旧查询

SELECT s.* FROM systems s
JOIN reports r ON r.system_id = s.id
WHERE r.created_at = (
  SELECT MAX(created_at)
  FROM reports p
  group by p.system_id
  having p.system_id = r.system_id)
ORDER BY r.id DESC

Time: 622.683 ms

Philip Couling 解决方案(干净，仅返回带有报告的系统)

SELECT systems.*
FROM systems
JOIN (
  SELECT reports.system_id
    , MAX(reports.created_at) created
  FROM reports
  GROUP BY reports.system_id
) AS r_date ON systems.id = r_date.system_id
ORDER BY r_date.created;

Time: 1.434 ms

BookofGreg 解决方案(会给我所有系统，报告或没有报告)

select systems.* from systems order by updated_at;

Time: 0.253 ms

我无法使用 systemjack 的解决方案。

最快的解决方案:bookofgreg

最干净的解决方案:philip couling

感谢您的输入。

Answer 1

(reports.system_id, reports.created_at) 上的索引可能使这项工作高效:

@systems = System.joins(:reports).where("reports.created_at = (SELECT MAX(created_at) FROM reports p where p.system_id = reports.system_id) system_id)").order('reports.created_at DESC') 

---

或者……

你的第二段代码:

System.joins(:reports).where("reports.id = (SELECT MAX(id) FROM reports p group by system_id having p.system_id = reports.system_id)").order('reports.id DESC')

扩展为:

   SELECT system.*
     JOIN reports ON system.id = reports.system_id
    WHERE reports.created_at = (
                           SELECT MAX(created_at) 
                             FROM reports p 
                         group by p.system_id 
                           having p.system_id = reports.system_id)
                       )
 ORDER BY reports.id DESC

请注意它必须两次查看报告。此外，因为您包含 p.system_id = reports.system_id)，嵌套查询将针对每个系统记录调用一次。

理想情况下，您希望获得 system_id 和报告日期的列表:所以……

    SELECT reports.system_id
         , MAX(reports.created_at) created
      FROM reports
  GROUP BY reports.system_id

然后加入:

  SELECT systems.* 
    FROM systems
    JOIN (
           SELECT reports.system_id
                , MAX(reports.created_at) created
             FROM reports
         GROUP BY reports.system_id
         ) AS r_date ON systems.id = r_date.systems_id
ORDER BY r_date.created