两个单列索引与MySQL中的一个两列索引？ [英] Two single-column indexes vs one two-column index in MySQL?

问题描述

我面对以下问题，我不知道什么是最好的做法。

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

id PK | giver_id FK | recipient_id FK | date

我使用InnoDB，根据我的理解，它会自动为两个外键列创建索引。但是，我还会做很多查询，我需要匹配的特定组合：

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:

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?

推荐答案

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

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.

有时，索引可能有用（A， B）和另一个索引（B）。这使得使用一个或两个列的查询快速，但是当然也使用更多的磁盘空间。

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.

在选择索引时，您还需要考虑插入，删除和更新。更多索引=更新速度较慢。

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

这篇关于两个单列索引与MySQL中的一个两列索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！