has_many :through 的索引

Question

假设您有两个模型，User和City，由第三个模型CityPermission连接：

class CityPermission < ActiveRecord::Base
  belongs_to :city
  belongs_to :user
end

class City < ActiveRecord::Base
  has_many :city_permissions
  has_many :users, :through => :city_permissions
end

class User < ActiveRecord::Base
  has_many :city_permissions
  has_many :cities, :through => :city_permissions
end

目前，我使用以下迁移代码片段创建联结表和表的索引：

create_table :city_permissions do |t|
      t.integer :user_id, :city_id
      t.other_fields ...
end

add_index(:city_permissions, :user_id)
add_index(:city_permissions, :city_id)

这些索引是最优的吗？这些索引能够快速地在联接表中来回访问，同时也可以快速查找表本身，还是有其他更好的方法？换句话说，如果给定city和user是City和User类的实例变量，这些索引是否能够让city.users、city.city_permissions、user.cities和user.city_permissions都同样表现出色？请注意保留HTML标记。

- jcnnghm

Answer 1

我认为看起来不错。

生成的连接应该只是实体表中的PK ID或连接表中的FK ID上的连接 - 这两者都是索引。

最好查看生成的ActiveRecord SQL并将其与索引进行比较。

根据您所在的数据库，您可以通过Explain计划（或任何现有工具，我在考虑Oracle）运行该SQL。

为了简化您的代码，您可以考虑使用has_and_belongs_to_many。这将让您摆脱CityPermission对象（除非您想在其中存储数据）。

- madlep

---

我认为在这种情况下使用多态关系是很好的选择，以防需要将habtm关系扩展到除城市和用户之外的其他对象。 - JasonOng

他确实提到了他存储了其他信息。 - Tilendor

根据我对Rails 4的了解，由于Rails会自动为所有主键创建索引，因此在外键上添加索引是否有必要？ - Derrick Mar

Answer 2

这是ActiveRecord为user.cities生成的SQL：

SELECT `cities`.* FROM `cities` INNER JOIN city_permissions ON (cities.id = city_permissions.city_id) WHERE (city_permissions.user_id = 1 )

以下是结果的解释：

+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                                                       | key                               | key_len | ref                                             | rows | Extra       |
+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+
|  1 | SIMPLE      | city_permissions | ref    | index_city_permissions_on_user_id,index_city_permissions_on_city_id | index_city_permissions_on_user_id | 5       | const                                           |    1 | Using where |
|  1 | SIMPLE      | cities           | eq_ref | PRIMARY                                                             | PRIMARY                           | 4       | barhopolis_development.city_permissions.city_id |    1 |             |
+----+-------------+------------------+--------+---------------------------------------------------------------------+-----------------------------------+---------+-------------------------------------------------+------+-------------+

这里是ActiveRecord为user.city_permissions生成的SQL：

SELECT * FROM `city_permissions` WHERE (`city_permissions`.user_id = 1)

使用该查询的EXPLAIN结果：

+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+
| id | select_type | table            | type | possible_keys                     | key                               | key_len | ref   | rows | Extra       |
+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | city_permissions | ref  | index_city_permissions_on_user_id | index_city_permissions_on_user_id | 5       | const |    1 | Using where |
+----+-------------+------------------+------+-----------------------------------+-----------------------------------+---------+-------+------+-------------+

看起来确实正常工作。根据MySQL手册：

eq_ref

对于前面表的每个行组合，从该表中读取一行。除了系统和const类型外，这是最好的可能的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE索引时，它被使用。

ref

对于前面表的每个行组合，从该表中读取具有匹配索引值的所有行。如果连接仅使用键的左侧前缀或者键不是PRIMARY KEY或UNIQUE索引（换句话说，如果连接不能基于键值选择单个行），则使用ref。如果使用的键只匹配少数行，则这是一个很好的连接类型。

- jcnnghm