MySQL 中的两个单列索引与一个两列索引？

Two single-column indexes vs one two-column index in MySQL?

提问by Tom

I'm faced with the following and I'm not sure what's best practice.

我面临以下问题，我不确定什么是最佳实践。

Consider the following table (which will get large):

考虑下表（会变大）：

id PK | giver_id FK | recipient_id FK | date

身PK | Giver_id FK | 收件人_id FK | 日期

I'm using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, I'll also be doing lots of queries where I need to match a particular combination of:

我正在使用 InnoDB，据我了解，它会自动为两个外键列创建索引。但是，我还将进行大量查询，需要匹配以下特定组合：

SELECT...WHERE giver_id = x AND recipient_id = t.

SELECT...WHERE giver_id = x AND recipient_id = t.

Each such combination will be unique in the table.

每个这样的组合在表中都是唯一的。

Is there any benefit from adding an two-column index over these columns, or would the two individual indexes in theory be sufficient / the same?

在这些列上添加两列索引是否有任何好处，或者理论上两个单独的索引是否足够/相同？

回答by Mark Byers

If you have two single column indexes, only one of them will be used in your example.

如果您有两个单列索引，则在您的示例中将只使用其中之一。

If you have an index with two columns, the query might be faster (you should measure). A two column index can also be used as a single column index, but only for the column listed first.

如果您有一个包含两列的索引，查询可能会更快（您应该测量）。两列索引也可以用作单列索引，但仅适用于首先列出的列。

Sometimes it can be useful to have an index on (A,B) and another index on (B). This makes queries using either or both of the columns fast, but of course uses also more disk space.

有时在 (A,B) 上有一个索引而在 (B) 上有另一个索引会很有用。这使得使用任一列或两列的查询速度更快，但当然也会使用更多的磁盘空间。

When choosing the indexes, you also need to consider the effect on inserting, deleting and updating. More indexes = slower updates.

在选择索引时，还需要考虑对插入、删除和更新的影响。更多索引 = 更慢的更新。

回答by OMG Ponies

A covering index like:

覆盖索引如：

ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

...would mean that the index could be used if a query referred to giver_id, or a combination of giver_idand recipient_id. Mind that index criteria is leftmost based - a query referring to only recipient_idwould not be able to use the covering index in the statement I provided.

...将意味着如果查询中提到的索引可以使用giver_id，或组合giver_id和recipient_id。请注意，索引条件是基于最左侧的 - 仅引用的查询recipient_id将无法使用我提供的语句中的覆盖索引。

Additionally, MySQL can only use one index per SELECT so a covering index would be the best means of optimizing your queries.

此外，MySQL 每个 SELECT 只能使用一个索引，因此覆盖索引将是优化查询的最佳方法。

回答by Mark Wilkins

If one of the foreign key indexes is already very selective, then the database engine should use that one for the query you specified. Most database engines use some kind of heuristic to be able to choose the optimal index in that situation. If neither index is highly selective by itself, it probably does make sense to add the index built on both keys since you say you will use that type of query a lot.

如果其中一个外键索引已经非常有选择性，那么数据库引擎应该为您指定的查询使用该索引。大多数数据库引擎使用某种启发式方法来选择这种情况下的最佳索引。如果这两个索引本身都不是高度选择性的，那么添加建立在两个键上的索引可能是有意义的，因为您说您将经常使用这种类型的查询。

Another thing to consider is if you can eliminate the PK field in this table and define the primary key index on the giver_idand recipient_idfields. You said that the combination is unique, so that would possibly work (given a lot of other conditions that only you can answer). Typically, though, I think the added complexity that adds is not worth the hassle.

另一件要考虑的事情是，是否可以消除此表中的 PK 字段并在giver_id和recipient_id字段上定义主键索引。你说这个组合是独一无二的，所以这可能会起作用（考虑到很多只有你能回答的其他条件）。但是，通常情况下，我认为增加的复杂性不值得麻烦。

回答by Andrew

Another thing to consider is that the performance characteristics of both approaches will be based on the size and cardinality of the dataset. You may find that the 2-column index only becomes noticing more performant at a certain dataset size threshold, or the exact opposite. Nothing can substitute for performance metrics for your exact scenario.

另一件需要考虑的事情是，这两种方法的性能特征将基于数据集的大小和基数。您可能会发现 2 列索引只会在特定的数据集大小阈值下表现出更高的性能，或者正好相反。对于您的具体场景，没有什么可以替代性能指标。