mysql - 为什么索引不使用 order by(外键)

Question

当我将 id(主键)与 order by 子句一起使用时，它使用名为 PRIMARY 的索引，但是当我将国家/地区代码(外键)与 order by 子句一起使用时，它不使用索引。我的输出如下。

mysql> SHOW CREATE TABLE City;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| City  | CREATE TABLE `City` (
|       | `ID` int(11) NOT NULL AUTO_INCREMENT,
|       | `Name` char(35) NOT NULL DEFAULT '',
|       | `CountryCode` char(3) NOT NULL DEFAULT '',
|       | `District` char(20) NOT NULL DEFAULT '',
|       | `Population` int(11) NOT NULL DEFAULT '0',
|       |  PRIMARY KEY (`ID`),
|       |  KEY `CountryCode` (`CountryCode`),
|       |  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
|       |  ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


mysql> EXPLAIN SELECT * FROM City ORDER BY ID;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | City  | index | NULL          | PRIMARY | 4       | NULL | 4321 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City ORDER BY COUNTRYCODE;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | City  | ALL  | NULL          | NULL | NULL    | NULL | 4321 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Answer 1

在 innodb 中，主键被称为“聚集索引”。

这意味着行根据 PK 值进行物理排序。

由于行是自然排序的，因此以 ASC 或 DESC 排序读取它们很便宜。

另一个故事是当您按另一列排序时。

要使用该 mysql 就必须读取索引页和数据页，这会显着增加 IO。因此 mysql 决定在内存中排序(因为根据其启发式内存排序比增加 IO 更快)。如果你想看到 mysql 使用该索引进行排序，你需要:

1. 将总行数增加到几十万
2. 仅选择一小部分，例如LIMIT 10

那么 mysql可能决定使用索引。