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.

考虑下表(会变大):

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

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 receiver_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屋！