多列索引对单列也有用吗？ [英] Multiple-column index would also be useful for single column?

问题描述

我对多个索引组的顺序感到困惑。

I'm confused about the order on multiple indexes groups.

看看这个：

. . . WHERE col1 = ? AND col2 = ? AND col3 = ?

在上面的查询中我必须在上创建一个三列索引（ col1，col2，col3）。现在我想知道，多指数在其他地方有用吗？例如，此查询：

Well in query above I have to create a three-column index on (col1, col2, col3). Now I want to know, that multiple-index is useful elsewhere? For example this query:

. . . WHERE col1 = ? AND col2 = ?

我应该创建另一个双列索引（col1，col2）也足够了吗？

Should I create another two-column index (col1, col2) for this ^ or that three-column index is enough here too?

这个查询怎么样？ ：

. . . WHERE col2 = ? AND col3 = ?

或者这一个：

. . . WHERE col2 = ? AND col1 = ?

他们都需要分离的索引，还是一个多重索引对所有索引都足够了？

Do all of them need separated indexes or one multiple index is enough for all of them?

实际上我正在试图理解索引的顺序是如何工作的？

In fact I'm trying to understand how the order of indexes work?

推荐答案

MySQL在使用方面有很好的文档多列索引。

MySQL has good documentation on the use of multi-column indexes.

首先要注意的是你的其中条件包括相同的条件;类型转换和归类差异也会影响索引的使用。条件也通过 AND 连接。 OR 阻止使用索引。

The first thing to note is that your where conditions consist of equal conditions; also type conversions and collation differences can affect the use of indexes. The conditions are also connected by AND. OR impedes the use of an index.

从索引的角度来看，列的顺序（具有相等性） ） 没关系。所以，对于这些条件：

From the perspective of indexing, the order of the columns (with equality) does not matter. So, for these conditions:

WHERE col1 = ? AND col2 = ? AND col3 = ?

这六个指数中的任何一个都是最优的：（col1，col2，col3） ，（col1，col3，col2），（col2，col1，col3）， （col2，col3，col1），（col3，col1，col2）和（ col3，col2，col1）。它们覆盖其中子句。

Any of these six indexes are optimal: (col1, col2, col3), (col1, col3, col2), (col2, col1, col3), (col2, col3, col1), (col3, col1, col2), and (col3, col2, col1). They "cover" the where clause.

作为旁注：最多只有一个不等式可以最佳地使用索引，这是索引中使用的最后一列。

As a side note: at most one inequality can use an index optimally, and that is the last column used in the index.

其中子句也可以使用较小的索引，例如（col1），或（col2，col3）。在这种情况下，使用索引进行一些过滤，对于其余的，引擎需要在数据页中查找数据以获取所需的字段。

That where clause can also make use of smaller indexes, such as (col1), or (col2, col3). In this case, some filtering is done using the index and for the rest, the engine needs to look up data in the data pages to fetch the needed fields.

索引也可用于其中只使用一部分键的条件 - 但必须从左到右使用键。因此，（col1，col2，col3）的索引可用于以下条件：

An index can also be used for where conditions that use just a subset of the keys -- but the keys have to be used from left to right. So, an index on (col1, col2, col3) can be used for these conditions:

where col1 = ? and col2 = ? and col3 = ?
where col1 = ? and col2 = ?
where col2 = ? and col1 = ?
where col1 = ?

它不会用于没有 col3 <的条件/ code>。并且，它可以（部分）用于以下条件：

It won't be used for conditions that don't have col3. And, it can be used (partially) for a condition such as:

where col1 = ? and col2 = ? and col4 = ?

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