了解 MySQL 查询中的多列索引

2021-12-21 00:00:00 indexing mysql

这是查询:

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

单列索引

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

相关文章