ruby-on-rails - rails多态关联中多列索引的顺序

Question

我有一个表，其中包含通过以下迁移生成的多态引用:

def change                  
  add_reference :table_name, :thing, polymorphic: true, index: true
end

当我运行迁移时，它生成了以下内容:

add_index "workflow_engine_task_bases", ["thing_type", "thing_id"], name: "index_workflow_engine_task_bases_on_thing_type_and_thing_id", using: :btree

为什么最左边的列是 thing_type？对我来说，这似乎不是最理想的，因为它不太具体。

Answer 1

这是 commit在 Derek Prior 的 Rails 中，更新 add_reference 以在为多态关联生成索引时在 id 之前使用 type。更改的理由转载如下:

Use type column first in multi-column indexes

add_reference can very helpfully add a multi-column index when you use it to add a polymorphic reference. However, the first column in the index is the id column, which is less than ideal.

The [PostgreSQL docs][1] say:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

The [MySQL docs][2] say:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

In a polymorphic relationship, the type column is much more likely to be useful as the first column in an index than the id column. That is, I'm more likely to query on type without an id than I am to query on id without a type.

[1]: http://www.postgresql.org/docs/9.3/static/indexes-multicolumn.html

[2]: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

我认为在大多数情况下，这个顺序是有道理的。它允许您拥有一个索引，该索引在包括 type 和 id 或仅 type 的查询上表现良好。

话虽如此，您的用例可能会有所不同，具体取决于您使用的数据库、数据集以及您计划运行的查询。最好的办法是在生产转储中分析最常见的用例，并相应地选择索引策略。