更新时间:2023-02-02 22:15:15
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 。并且,它可以(部分)用于以下条件:
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 = ?