MySQL复合索引中的键的高性能排序（WRT Rails多态关联和STI） [英] performant ordering of keys in a MySQL compound index (WRT Rails Polymorphic associations and STI)

问题描述

之前，我曾询问关于多态外键复合索引的问题在ActiveRecord中。我的问题的基础是我的理解，索引应该基于列的基数，并且在Rails的STI类型和多态_type列上通常具有相当低的基数。

Previously, I asked this question about compound indexes on polymorphic foreign keys in ActiveRecord. The basis of my question was my understanding that indexes should be based on the cardinality of your column, and there's generally pretty low cardinality on Rails's STI type and polymorphic _type columns.

接受我的问题的答案是正确的 - 将高基数_id列和低基数_type列索引是有价值的，因为它们在一起它们有一个高基数 - 我的下一个问题是：你应该如何订购复合索引？

Accepting that the answer to my question is right -- that's there's value to indexing both the high cardinality _id columns and the low cardinality _type columns, because they together they have a high cardinality -- my next question is: how should you order your compound indexes?

[owner_id，owner_type]的索引首先将字段置于更高的基数，而[ owner_type，owner_id]将具有更高基数的字段放在第二位。使用前一个键的查询是否比使用后一个键的查询更高效，或者它们是否同样高效？

An index of [owner_id, owner_type] places the field with higher cardinality first, while [owner_type, owner_id] places the field with higher cardinality second. Is a query using the former key more performant than a query using the latter key, or are they equally performant?

我问，因为这对我如何为服务STI模型的表订购复合键有特别的影响。 STI Rails查找器几乎总是查询类型列 - 这也是一个通常低基数的列。因此，比其他索引更频繁地查询类型列。如果更频繁地查询类型列，那么使用类型前导索引可能是有意义的，因为较少特定的查询可以利用索引的第一部分来产生性能提升。但是，对于高度特定的查询而言，我不会因为性能的损害而小费。利用索引的高基数部分。

I ask because this has particular bearing on how I would order the compound keys for tables serving STI models. STI Rails finders almost always query on the type column -- which again is a column of generally low cardinality. The type column is therefore queried much more often than other indexes. If the type column is queried much more often, then maybe it makes sense to use the type-leading index, because less specific queries could take advantage of the first part of the index yielding a performance-boost. However, I wouldn't smaller perk to come at the detriment of performance to highly-specific queries. that take advantage of the higher-cardinality portion of the index.

推荐答案

从我自己的研究中（但我不是专家DBA）我已经了解到在决定复合键索引的顺序时需要考虑两件事。

From my own research (but I'm no expert DBA) I've learned that there's two thing to consider when deciding the order of a compound key index.

首先，关于列的基数，索引通常在搜索时更好具有高基数的列。所以我倾向于在索引中首先放置具有最高基数的列。作为参考，有一篇题为 MySQL查询优化的文章说：

First, concerning the cardinality of columns, index generally are better at searching columns with high cardinality. So I would be inclined to place the column with the highest cardinality first in the index. For reference, there's an article titled MySQL Query Optimization that says:

索引最适用于相对于表中行数具有高基数的列（即具有许多唯一值的列很少重复。）

Indexes work best for columns that have a high cardinality relative to the number of rows in the table (that is, columns that have many unique values and few duplicates).

在你的情况下， _id 列会清楚更符合定义，因此它们更适合作为密钥的前缀。

In your case, the _id columns would clearly fit better that definition, thus they're a better candidate for being a prefix of the key.

另一件需要考虑的事情是这些索引的可重用性。大多数（如果不是全部）数据库系统允许重用复合键的前缀。例如，（owner_id，owner_type）上的复合键也可以用于 owner_id 上的查询，但不能用于 owner_type 。

Another thing to consider would be the reusability of these indexes. Most (if not all) database systems allow a prefix of a compound key to be reused. For example, a compound key on (owner_id, owner_type) could also be used by queries on owner_id but not on owner_type.

因此，根据您在问题中的解释，使用两个索引可能会更好：上的复合键索引（owner_id，owner_type）和另一个（owner_type）。

So from what you explained in your question you might be better off with two indexes: a compound key index on (owner_id, owner_type) and a another on (owner_type).

最后，它真的归结为你的数据集和查询。尝试使用不同复合键排序的多个场景，基准测试，以了解什么是最佳解决方案。此外，请不要忘记索引会对您的表格造成写入惩罚。

Finally, it really all comes down to your dataset and queries. Try out multiple scenarios, benchmarks using different compound key ordering to see what is the most optimal solution. Also, don't forget that indexes incur a write penalty on your tables.

更新：还有另一个相当受欢迎的关于复合键的SO问题索引那里：

Update: There's also another rather popular SO question about compound key index there:

我应该何时使用综合指数？

这篇关于MySQL复合索引中的键的高性能排序（WRT Rails多态关联和STI）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！