了解MySQL查询中的多个列索引 [英] Understanding multiple column indexes in MySQL 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)

MySQL会采取这个多列索引的优势是什么？或者它不会使用它，因为一个字段在where子句中而另一个在order语句中？或者只要我按照多列索引的顺序使用字段无关紧要？

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?

推荐答案

好问题。

索引从左到右工作，因此您的 WHERE 条件将使用索引。在这种情况下，排序也会使用索引（下面的执行计划）。

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).

来自手册：

该索引甚至可以使用如果 ORDER BY 与索引完全不匹配，只要索引的所有未使用部分和所有额外的 ORDER BY 列是 WHERE 子句中的常量。以下查询使用索引来解析 ORDER BY 部分：

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:

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

如果你有一个列索引（ accountid ），将使用一个文件排序。因此，您的查询确实会从该索引中受益。

If you had a single column index (accountid), a filesort would be used instead. Therefore, your query does benefit from that 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')

执行计划

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')

执行计划

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

这篇关于了解MySQL查询中的多个列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！