在ActiveRecord的多列索引 [英] Index for multiple columns in ActiveRecord
问题描述
在ActiveRecord的有两种方法来声明索引多列：
add_index：分类，[：种：家族：trivial_names]
add_index：分类，：物种 add_index：分类，：家庭 add_index：分类，：trivial_names
有没有第一种方法，第二个有什么区别？如果是的话，我应该使用第一和当第二？
您是一个比较综合指数与一组独立的指数。他们只是不同的。
认为它是这样的：一个复合索引为您提供了一组嵌套领域的快速查询的第一场，接着在短短已选中的第一个记录的快速查询第二个字段的的字段的，接着迅速查找第三场的 - 再次，只有在由previous选择两个指数的记录
让我们举一个例子。你的数据库引擎将不会超过20步之内百万的记录找到一个独特的价值（如果没有记错）如果您正在使用一个索引。这是真实的，不管你用的是复合或独立的指数使用 - 但只适用于第一个字段（在你的榜样种虽然我还以为你想要系列，种类，然后通用名称）。
现在，让我们说，有10万符合条件的记录为这第一个字段的值。如果只有单一的指数，然后将这些记录中的任何查询将采取100000步骤：一个是由第一个索引检索到的每个记录。这是因为第二索引将不被使用（在大多数数据库 - 这是一个比特的简化的），并强力匹配必须使用。
如果你有一个的综合指数的那么您的搜索速度更快，因为你的第二个字段搜索将有一个索引的在的第一组值。在这种情况下，你需要不超过17个步骤让你的第一个匹配值的2场10万的比赛中现场1（日志100000基地2）。
这样：找到一个独特的记录了100万条记录用3嵌套字段，其中第一检索10万第二检索万= 20 + 17 + 14 = 51步骤的综合指数数据库所需的步骤。
与刚独立指数在相同的条件下需要采取措施= 20 + 100000 +万= 110020步骤
大的区别，是吧？
现在，的不的想怎么样把综合指数无处不在。首先，它们很昂贵的插入和更新。其次，他们只带了来承担，如果你是真正的跨嵌套数据搜索（再比如，我用它们在给定日期范围内提取数据用于登录的客户端时）。此外，他们是不值得的，如果你正在使用相对较小的数据集。
最后，检查数据库文档。数据库在部署索引这些天，数据库101的情况我上面描述不得担任一些（虽然我总是发展为如果它只是让我知道我得到）的能力成长极其复杂的。
In ActiveRecord there are two ways to declare indexes for multiple columns:
add_index :classifications, [:species, :family, :trivial_names]
add_index :classifications, :species add_index :classifications, :family add_index :classifications, :trivial_names
Is there any difference between the first approach and the second one? If so, when should I use the first and when the second?
You are comparing a composite index with a set of independent indices. They are just different.
Think of it this way: a compound index gives you rapid look-up of the first field in a nested set of fields followed by rapid look-up of the second field within ONLY the records already selected by the first field, followed by rapid look-up of the third field - again, only within the records selected by the previous two indices.
Lets take an example. Your database engine will take no more than 20 steps to locate a unique value within 1,000,000 records (if memory serves) if you are using an index. This is true whether you are using using a composite or and independent index - but ONLY for the first field ("species" in your example although I'd think you'd want Family, Species, and then Common Name).
Now, let's say that there are 100,000 matching records for this first field value. If you have only single indices, then any lookup within these records will take 100,000 steps: one for each record retrieved by the first index. This is because the second index will not be used (in most databases - this is a bit of a simplification) and a brute force match must be used.
If you have a composite index then your search is much faster because your second field search will have an index within the first set of values. In this case you'll need no more than 17 steps to get to your first matching value on field 2 within the 100,000 matches on field 1 (log base 2 of 100,000).
So: steps needed to find a unique record out of a database of 1,000,000 records using a composite index on 3 nested fields where the first retrieves 100,000 and the second retrieves 10,000 = 20 + 17 + 14 = 51 steps.
Steps needed under the same conditions with just independent indices = 20 + 100,000 + 10,000 = 110,020 steps.
Big difference, eh?
Now, don't go nuts putting composite indices everywhere. First, they are expensive on inserts and updates. Second, they are only brought to bear if you are truly searching across nested data (for another example, I use them when pulling data for logins for a client over a given date range). Also, they are not worth it if you are working with relatively small data sets.
Finally, check your database documentation. Databases have grown extremely sophisticated in the ability to deploy indices these days and the Database 101 scenario I described above may not hold for some (although I always develop as if it does just so I know what I am getting).
这篇关于在ActiveRecord的多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
