MySQL中多列索引的效率

If I have a MyISAM table with a 3-column index, something like

如果我有一个带有3列索引的MyISAM表，就像

create table t (
  a int,
  b int,
  c int,
  index abc (a, b, c)
) engine=MyISAM;

the question is, can the following query fully utilize the index:

问题是，以下查询可以充分利用索引：

select * from t where a=1 and c=2;

in other words, considering that an index is a b-tree, can MySQL skip the column in the middle and still do a quick search on first and last columns?

换句话说，考虑到索引是一个b树，MySQL可以跳过中间的列并仍然在第一列和最后一列快速搜索吗？

EXPLAIN does seem to be showing that the index will be used, however, the Extra says: Using where; Using index and I have no idea what this really means.

EXPLAIN似乎表明将使用索引，但Extra说：使用where;使用索引，我不知道这意味着什么。

2 个解决方案

#1

The answer is "no".

答案是不”。

The MySQL documentation is quite clear on how indexes are used:

MySQL文档非常清楚如何使用索引：

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. 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). (http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html.)

如果表具有多列索引，则优化程序可以使用索引的任何最左前缀来查找行。例如，如果在（col1，col2，col3）上有三列索引，则在（col1），（col1，col2）和（col1，col2，col3）上编制索引搜索功能。（http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html。）

What happens is that the index gets used for "a=1". All records that match are loaded, to see if "c=2" is true. The filter ends up using a combination of indexes and explicit record filtering.

会发生什么是索引用于“a = 1”。加载所有匹配的记录，以查看“c = 2”是否为真。过滤器最终使用索引和显式记录过滤的组合。

By the way, if you want to handle all combinations of two columns, you need several indexes:

顺便说一下，如果要处理两列的所有组合，则需要多个索引：

(a, b, c)
（a，b，c）
(b, a, c)
（b，a，c）
(c, b, a)
（c，b，a）

#2

Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.

即使您正在为WHERE子句的所有部分使用索引，您也可以看到如果列可以为NULL则使用where。

As per MySQL documentation, the above statement clarifies that the column in your table could be null and hence it says using where as well though it has covering index for fields in where clause.

根据MySQL文档，上面的语句澄清了表中的列可能为null，因此它表示虽然它具有where子句中字段的覆盖索引，但仍然使用where。

http://dev.mysql.com/doc/refman/5.1/en/explain-output.html#explain-extra-information

#1