mysql复合索引是否会使其他索引完全冗余？ [英] Do mysql composite indexes make some other indexes completely redundant?

问题描述

如果我在（a，b）上有一个复合索引，我理解只关注'a'的查询仍将使用复合索引（但不是与'b'有关的查询）

If I have an a composite index on (a, b) I understand that queries only concerned with 'a' will still use the composite index (but not queries concerned with 'b')

我的问题是，如果我有（a，b）索引，是否有任何正当理由在'a'上设置单列索引？我所读到的似乎含糊不清的是（a，b）指数是否完全替代a，或仅仅是优于无指数。

My question is whether there is any valid reason to have a single-column index on 'a' if I have the (a, b) index? What I've read has seemed vague as to whether the (a,b) index was a complete substitute for a, or merely a "better than nothing" index.

这假设我通过a和a，b进行过滤。我有一个表太多的索引，这会损害写入性能，并希望在删除索引之前仔细检查，我只是相当确定没有做任何好事。

This assumes that I do filtering by both a and a,b. I have a table with way too many indexes that is hurting write performance and want to double check before dropping indexes that I'm only fairly sure are not doing any good.

此外，这个答案是否会根据我使用InnoDb还是MyISAM而改变？有关的表是MyISAM，但我们的大多数表都是InnoDb。

Also, does this answer change depending on whether I am using InnoDb or MyISAM? The table concerned is MyISAM, but most of our tables are InnoDb.

推荐答案

您的（a，b）索引也将处理查询仅涉及'a'并且不需要（a）单独的索引。

Your (a,b) index will also handle queries involving only 'a' and there is no need for an index on (a) alone.

来自文档：

如果该表有一个多列
索引，优化器可以将
索引的任何最左前缀用于
查找行。

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows.

例如，如果您在（col1，col2，
col3）上有一个
三列索引，则已将索引编入索引
上的功能（col1），（col1，col2），
和（col1，col2，col3）。

For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

这篇关于mysql复合索引是否会使其他索引完全冗余？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！