索引不按顺序使用的原因(外键)

Question

当我使用id (主键)和order子句时，它使用了名为PRIMARY的索引，但是当我使用countrycode (外键)和order子句时，它不使用索引。我的产量低于。

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比较便宜。

另一个故事是当你order by另一个专栏的时候。

要使用它，mysql必须同时读取索引页和数据页，这大大增加了IO。因此mysql决定在内存中对其进行排序(因为根据其启发式算法，内存排序比增加的IO更快)。如果希望看到mysql使用该索引进行排序，则需要：

1. 将行总数增加到几十万行
2. 只选择一个小子集，如LIMIT 10

然后mysql可能决定使用索引。