当查询结果按 has_many :through 关联排序时应使用哪些索引

Question

这个问题可能很长，但问题其实很简单。我有3种模型：列表，外观和电影。列表通过外观包含许多影片，它们应按连接模型的属性等级排序。那么我应该使用哪些索引呢？这是我的模型目前的样子：

# Models
class Movie < ActiveRecord::Base  
  has_many :appearances, :dependent => :destroy
  has_many :lists, :through => :appearances
end
class Appearance < ActiveRecord::Base
  belongs_to :list
  belongs_to :movie
end
class List < ActiveRecord::Base
  has_many :appearances, :dependent => :destroy
  has_many :movies, :through => :appearances
  def self.find_complete(id)
    List.includes({:appearances => :movie}).where("appearances.rank IS NOT NULL").order("appearances.rank ASC").find(id)
  end
end

这是我已有的索引。我需要复合索引吗？或者只是按等级划分的索引？

# Tables
class CreateAppearances < ActiveRecord::Migration
  def change
    create_table :appearances do |t|
      t.integer :list_id, :null => false
      t.integer :movie_id, :null => false
      t.integer :rank
    end
    add_index :appearances, :list_id
    add_index :appearances, :movie_id
  end
end

最后，有没有办法重构find_complete List方法？喜欢使用default_scope？不要忘记排名可能为空。

Answer 1

ActiveRecord 正在执行两个查询。

SELECT DISTINCT `lists`.id
FROM `lists` LEFT OUTER JOIN `appearances` ON `appearances`.`list_id` = `lists`.`id`
             LEFT OUTER JOIN `movies` ON `movies`.`id` = `appearances`.`movie_id`
WHERE `lists`.`id` = 1 AND (appearances.rank IS NOT NULL)
ORDER BY appearances.rank ASC LIMIT 1

和

SELECT `lists`.`id` AS t0_r0, `lists`.`name` AS t0_r1, `lists`.`created_at` AS t0_r2, `lists`.`updated_at` AS t0_r3, `appearances`.`id` AS t1_r0, `appearances`.`list_id` AS t1_r1, `appearances`.`movie_id` AS t1_r2, `appearances`.`rank` AS t1_r3, `appearances`.`created_at` AS t1_r4, `appearances`.`updated_at` AS t1_r5, `movies`.`id` AS t2_r0, `movies`.`name` AS t2_r1, `movies`.`created_at` AS t2_r2, `movies`.`updated_at` AS t2_r3
FROM `lists` LEFT OUTER JOIN `appearances` ON `appearances`.`list_id` = `lists`.`id`
             LEFT OUTER JOIN `movies` ON `movies`.`id` = `appearances`.`movie_id`
WHERE `lists`.`id` = 1 AND `lists`.`id` IN (1) AND (appearances.rank IS NOT NULL)
ORDER BY appearances.rank ASC

在这两种情况下，您都需要一个同时具有 lists.id 和外观.rank的索引。 某些数据库将在多个表上创建索引。 我也不知道你的数据库是否足够聪明，可以在appearances.list_id和 appearances.rank 上使用索引，但这值得一试。

add_index :appearances, [:list_id, :rank]

如果这没有帮助，至少索引 ：rank，这样数据库可能能够避免排序。

Answer 2

你必须看看Rails将针对你的特定用例输出哪些查询。

我最好的猜测是在外键列上添加索引，然后在应用程序完成并加载数据后使用探查器查看特定查询是否表现不佳并修复它们！

永远不要尝试超越您的数据库，只需测量和修复 - 其他任何事情很可能会花费比节省更多的性能。

你在这里问的是一种过早的优化形式，也有点猜测，考虑到 Rails3 Arel 将如何延迟执行，以便实际需要的索引在很大程度上取决于如何使用这种关联以及如何遍历它。

Answer 3

重新检查您的关联。如果

列表通过外观包含许多电影

class List < ActiveRecord::Base
 has_many :appearances, :dependent => :destroy
 has_many :movies, :through => :appearances
end
class Appearance < ActiveRecord::Base
 belongs_to :list, :foreign_key => list_id
 has_many :movies
end
class Movie < ActiveRecord::Base  
 belongs_to :appearance, :foreign_key => appearance_id, :dependent => :destroy  
end

至于find_complete方法，我不明白为什么要把它变成一个范围。可能必须做得更短一点，因为您已经在列表模型中并且看起来很奇怪地调用 List.include....在自身内部。

看不到该movie_id索引的必要性

class CreateAppearances < ActiveRecord::Migration
  def change
    create_table :appearances do |t|
      t.integer :list_id, :null => false
      t.integer :movie_id, :null => false
      t.integer :rank
    end
  add_index :appearances, :list_id
 end
end

并且还需要电影表的appearance_id索引。

我希望我没有错过任何东西