了解 MySQL 查询中的多列索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12728832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Understanding multiple column indexes in MySQL query
提问by John
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 语句中？还是只要我按照多列索引的顺序使用字段就可以了？
采纳答案by Kermit
Good question.
好问题。
Indexes work left to right, so your WHEREcriteria would use the index. The sort would also utilize the index in this case (execution plan below).
索引从左到右工作，因此您的WHERE标准将使用索引。在这种情况下，排序也将利用索引（下面的执行计划）。
From the manual:
从手册：
The index can also be used even if the
ORDER BYdoes not match the index exactly, as long as all of the unused portions of the index and all the extraORDER BYcolumns are constants in theWHEREclause. The following queries use the index to resolve theORDER BYpart:SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
即使
ORDER BY索引与索引不完全匹配，也可以使用索引，只要索引的所有未使用部分和所有额外ORDER BY列都是WHERE子句中的常量。以下查询使用索引来解析ORDER BY部分：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)，则将使用文件排序。因此，您的查询确实受益于该索引。
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
