AB上的索引是否多余如果ABC上有索引?-IsanindexonA,BredundantifthereisanindexonA,B,C?

28  

It depends, but the answer is often 'Yes, you could drop the index on (A,B)'.

这要看情况，但答案通常是“是的，你可以在(A,B)上删除索引”。

The counter-case (where you would not drop the index on (A,B)) is when the index on (A,B) is a unique index that is enforcing a constraint; then you do not want to drop the index on (A,B). The index on (A,B,C) could also be unique, but the uniqueness is redundant because the (A,B) combination is unique because of the other index.

反例(在A,B上不放索引)是当(A,B)上的索引是一个唯一的索引来执行约束;那么你就不想把索引放在(A,B)上。(A、B、C)上的索引也可能是惟一的，但是惟一性是多余的，因为(A、B)组合由于其他索引而惟一。

But in the absence of such unusual cases (for example, if both (A,B) and (A,B,C) allow duplicate entries), then the (A,B) index is logically redundant. However, if the column C is 'wide' (a CHAR(100) column perhaps), whereas A and B are small (say INTEGER), then the (A,B) index is more efficient than the (A,B,C) index because you can get more information read per page of the (A,B) index. So, even though (A,B) is redundant, it may be worth keeping. You also need to consider the volatility of the table; if the table seldom changes, the extra indexes don't matter much; if the table changes a lot, extra indexes slow up modifications to the table. Whether that's significant is difficult to guess; you probably need to do the performance measurements.

但是如果没有这种不寻常的情况(例如，如果(A、B)和(A、B、C)都允许重复条目)，那么(A、B)索引在逻辑上是冗余的。但是，如果列C是“宽”(可能是CHAR(100)列)，而a和B是小的(比如整数)，那么(a,B)索引比(a,B,C)索引更有效，因为您可以从(a,B)索引的每一页中获得更多的信息。所以，即使(A,B)是冗余的，它也值得保留。您还需要考虑表的波动性;如果表很少更改，那么额外的索引并不重要;如果表变化很大，那么额外的索引会减慢对表的修改。这是否具有重大意义还难以猜测;您可能需要进行性能度量。

11  

The first index covers queries that look up on A , A,B and the second index can be used to cover queries that look up on A , A,B or A,B,C which is clearly a superset of the first case.

第一个索引包含查找A、A、B的查询，第二个索引可以用于查询查找A、A、B或A、B、C的查询，这显然是第一种情况的超集。

If C is very wide however the index on A,B may still be useful as it can satisfy certain queries with fewer reads.

如果C非常宽，但是A上的索引，B可能仍然有用，因为它可以用更少的读取来满足某些查询。

e.g. if C was a char(800) column the following query may benefit significantly from having the narrower index available.

例如，如果C是一个char(800)列，下面的查询可以从更窄的索引中获益良多。

SELECT a,b
FROM YourTable
ORDER BY a,b

7  

Yes, this is a common optimization. Any query that would benefit from the index on A,B can also benefit just as well from the index on A,B,C.

是的，这是一个常见的优化。从A、B上的索引中获益的任何查询也可以从A、B、C上的索引中获益。

In the MySQL community, there's even a tool to search your whole schema for redundant indexes: http://www.percona.com/doc/percona-toolkit/pt-duplicate-key-checker.html

在MySQL社区中，甚至有一个工具可以搜索整个模式中的冗余索引:http://www.percona.com/doc/percona-toolkit/pt-duplicate-key-checker.html

The possible exception case would be if the index on A,B were more compact and used much more frequently, and you wanted to control which index was kept loaded in memory.

可能的例外情况是，如果A、B上的索引更紧凑，使用频率更高，并且希望控制哪些索引被保存在内存中。

4  

Much of what I was thinking was written by Jonathan in previous answer. Uniqueness, faster work, and one other thing I think he missed.

我想的大部分内容都是乔纳森之前的回答。独特性，更快的工作，还有一件事我认为他漏掉了。

If the first index is made A desc, B asc and second A asc, B asc, C asc, then deleting he first index isn't really a way to go, because the second one isn't a superset of the first one, and your query cannot benefit from the second index if ordering is as written in the first one.

desc,如果第一个索引B asc和第二asc,asc B,C asc,然后删除他第一次指数并不是一个真正的路要走,因为第二个不是第一个的超集,而且查询不能受益于第二索引排序是否写在第一个。

In some cases like when you use the first index, you can order by A desc, B asc (of course) and A asc, B desc, but you can also make a query that will use any part of that index, like Order by A desc.

在某些情况下，如使用第一个索引时，可以使用desc、B asc(当然)和A asc、B desc，但也可以使用该索引的任何部分进行查询，比如使用desc的order。

But a query like order by A asc, B asc, will not be 'covered' by the first index.

但是asc、B asc的order这样的查询不会被第一个索引“覆盖”。

So I would add up, you can usually delete the first index, but that depends on your table configuration and your query (and, of course, indexes).

所以我想说的是，您通常可以删除第一个索引，但是这取决于您的表配置和查询(当然还有索引)。

2  

I typically would find this "almost" similar index in table that contains historical data. If column C is a date or integer column, be careful. It is most likely used to satisfy the MAX function as in WHERE tblA.C = MAX(tblB.C), which skips the table altogether and utilize an index only access path.

我通常会在包含历史数据的表中找到这个“几乎”相似的索引。如果C列是日期或整数列，请小心。它很可能被用来满足最大函数，就像tblA。C = MAX(tblB.C)，它完全跳过表，只使用索引访问路径。