MySQL:`...之间的区别... AIND INDEX(a); ... ADD INDEX(b);`和`... ADD INDEX(a，b);`? [英] MySQL: Difference between `... ADD INDEX(a); ... ADD INDEX(b);` and `... ADD INDEX(a,b);`?

问题描述

有人可以告诉我这两者之间有什么区别吗?

Can someone tell me what is the difference between these two:

ALTER TABLE x1 ADD INDEX(a);
ALTER TABLE x1 ADD INDEX(b);

AND

ALTER TABLE x1 ADD INDEX(a,b);

我知道这可以归结为最基本的内容，但有时我发现前者似乎要比后者快一些.仅仅是我的感觉还是有实际的原因?

I know this goes down to the very basics but sometimes I find the former seems to be a little faster than the latter. Is it just my feeling or is there some actual reason for it?

推荐答案

使用第二个选项，以下查询将不使用索引:

With your second option, the following query will not use the index:

SELECT * FROM x1 WHERE b = 'something';

在索引定义中列出列的顺序很重要.可以仅使用第一个索引列来检索一组行标识符.但是，仅在第二或更大索引列中检索行标识符集是不可能的(在大多数数据库上).

The order in which columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.

来源:维基百科-数据库索引:列排序

这篇关于MySQL:`...之间的区别... AIND INDEX(a); ... ADD INDEX(b);`和`... ADD INDEX(a，b);`?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！