MySQL:仅当我仅选择主键时才使用索引

Question

我在 MySQL 中有这个表:

Table: city
Columns: 
  ID int(11) AI PK 
  Name char(35) 
  CountryCode char(3) 
  District char(20) 
  Population int(11)

我想在按 CountryCode 排序时使用索引，所以我在此列上创建了索引:

Index: CountryCode
Definition:
  Type BTREE 
  Unique No 
  Columns CountryCode

为什么 EXPLAIN EXTENDED SELECT * FROM city ORDER BY CountryCode; 不使用索引:

| id: 1 
| select type: SIMPLE 
| table: city 
| partitions: NULL
| type: ALL 
| possible_keys: NULL
| key: NULL
| key_len: NULL
| ref: NULL 
| rows: 4188 
| filtered: 100.00 
| Extra: Using filesort

和EXPLAIN EXTENDED SELECT id FROM world.city ORDER BY CountryCode;使用索引:

| id: 1
| select type: SIMPLE
| table: city
| partitions: NULL
| type: index
| possible_keys: NULL
| key: CountryCode
| key_len: 3
| ref: null
| rows: 4188
| filtered: 100.00
| Extra: Using index

如何更改此行为 - 即在第一个示例中添加索引用法？

Answer 1

没问题。第一个查询通过不使用索引运行更快。

首先，让我们剖析第二个查询。显然你正在使用 InnoDB。这意味着 INDEX(country_code) 实际上是 INDEX(country_code, id)。也就是说，PRIMARY KEY 隐式附加到任何辅助键上。第二个查询只需要 country_code 和 id，因此它可以完全在索引中执行，如 Using index 所示。它按顺序读取索引，并提供结果。非常有效。

现在，让我们看一下第一个查询。这次您要求的是 *，而不仅仅是 id。其余字段不在索引中。如果它要使用索引(并且您可以使用 ... FROM city FORCE INDEX(CountryCode) ... 进行测试)，它必须这样做:

1. 扫描索引 - 这会按顺序提供 ids，但不会提供 * 的其余部分。
2. 对于每个 id，获取 * 其余部分的数据。这代价高昂，尤其是在无法完全缓存表的情况下。

如果相反，它进行了“表扫描”(这就是您所看到的)，则代码的工作方式如下:

1. 将整个表读入一个 tmp 表。 (这将是 MEMORY 或 MyISAM，具体取决于几个条件。)
2. 对该 tmp 表进行排序(在磁盘上的 RAM 中)。

表扫描 + 排序可能会更快。

其他说明

如果您的城市在美国，新墨西哥州的“Los Ranchos de Albuquerque”对于该字段来说太长了。对于世界，请考虑 KZ 的 87 个字符的“Imeni 50-letiya (Pyat'desyatiletiya) Kazakhskoy Sovetskoy Sotsialisticheskoy Respubliki”。

不要对可变长度字符串(城市、地区)使用CHAR；使用 VARCHAR；它将在空间上更有效率，因此在速度上也会更有效率。

使用CHAR(3)作为固定长度的country_code，但一定要指定CHARACTER SET ascii。如果使用默认的 utf8，它将占用 9 个字节，而不是 3 个字节。