了解MySQL查询中的多列索引

Here is the query:

在这里查询:

SELECT * FROM table WHERE accountid = 1 ORDER BY logindate DESC LIMIT 1

Now if I added an index with multiple columns on the fields:

现在，如果我在字段上添加一个包含多个列的索引:

INDEX(accountid,logindate)

Would MySQL take advantage of this multiple column index? Or would it not use it because one field is in the where clause and the other is in an order statement? Or does it not matter as long as I use the fields in the order of the multiple column index?

MySQL会利用这个多列索引吗?或者它不会因为一个字段在where子句中而另一个字段在order语句中而使用它吗?或者，只要我按照多列索引的顺序使用字段，它就不重要吗?

1 个解决方案

#1

Good question.

好问题。

Indexes work left to right, so your WHERE criteria would use the index. The sort would also utilize the index in this case (execution plan below).

索引在左向右运行，因此您的标准将使用索引。在这种情况下，排序也将使用索引(执行计划如下)。

From the manual:

从手册:

The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause. The following queries use the index to resolve the ORDER BY part:

即使ORDER BY与索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和列的所有额外顺序都是WHERE子句中的常量。以下查询使用索引按部分解析订单:

SELECT * FROM t1
WHERE key_part1=constant
ORDER BY key_part2;

If you had a single column index (accountid), a filesort would be used instead. Therefore, your query does benefit from that index.

如果您只有一个列索引(accountid)，那么将使用一个filesort。因此，查询确实受益于该索引。

---

Two Column Index

create table t1 (
  accountid tinyint,
  logindate date);

create index idx on t1 (accountid, logindate);

insert into t1 values (1, '2012-09-05'), (2, '2012-09-09'), (3, '2012-09-04'), 
    (1, '2012-09-01'), (1, '2012-09-26'), (2, '2012-05-16'), 
    (1, '2012-09-01'), (3, '2012-10-19'), (1, '2012-03-01')

Execution Plan

执行计划

ID  SELECT_TYPE  TABLE  TYPE  POSSIBLE_KEYS  KEY  KEY_LEN  REF   ROWS  FILTERED  EXTRA
1   SIMPLE       t1     ref   idx            idx  2        const 5     100       Using where; Using index

Single Column Index

create table t1 (
  accountid tinyint,
  logindate date);

create index idx on t1 (accountid);

insert into t1 values (1, '2012-09-05'), (2, '2012-09-09'), (3, '2012-09-04'), 
    (1, '2012-09-01'), (1, '2012-09-26'), (2, '2012-05-16'), (1, '2012-09-01'), 
    (3, '2012-10-19'), (1, '2012-03-01')

Execution Plan

执行计划

ID  SELECT_TYPE  TABLE  TYPE   POSSIBLE_KEYS  KEY  KEY_LEN  REF   ROWS  FILTERED  EXTRA
1   SIMPLE       t1     range  idx            idx  2              5     100       Using where; Using filesort