Select *和查询优化器
我有一个非常简单的MySQL表，有一些非常奇怪的行为。顺便说一句，奇怪的行为正是我想要它做的，但我不想把它投入生产，不知道它为什么要做它做的事情。
不管怎样，我已经创建了一个表，如下所示：
Create table `raceTimes` (
`userID` mediumint(8) unsigned,
`time` time,
primary key (`userID`),
key `idx_time` (`time`)
) engine=InnoDB default charset=utf8;现在，当我执行Select * from raceTimes查询时，我得到如下结果集：
mysql> Select * from raceTimes;
+--------+----------+
| userID | time |
+--------+----------+
| 14 | 12:37:46 |
| 6 | 12:41:11 |
| 5 | 12:48:45 |
| 13 | 12:55:46 |
| 10 | 13:13:37 |
| 9 | 13:40:37 |
| 17 | 15:30:44 |
| 18 | 15:46:58 |
| 3 | 16:16:45 |
| 8 | 16:40:11 |
| 7 | 16:41:11 |
| 4 | 16:48:45 |
| 16 | 20:30:44 |
| 15 | 20:37:44 |
| 1 | 21:00:00 |
| 2 | 21:16:00 |
| 11 | 23:13:37 |
| 20 | 23:14:58 |
| 19 | 23:46:58 |
| 12 | 23:55:46 |
+--------+----------+请注意，结果集是基于时间的顺序，从最低到最高。好的，这正是我想让表做的，因为我想在游戏中用它做排行榜。当我对我的查询运行explain时，我得到这样的结果：
mysql> explain select * from raceTimes;
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | raceTimes | index | NULL | idx_time | 4 | NULL | 20 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+到目前为止，一切都很好。由于对idx_time索引进行了排序(就像索引一样)，因此我将得到一个排序的结果集，为此我点击了一个索引。现在是奇怪的行为了。
据我所知，主键在默认情况下是索引的，在查询表时应该是最快的索引。然而，它并没有被使用。我的猜测是idx_time索引比主键索引小，因为它是时间类型，而不是mediumint(8)类型。但这只是个猜测。
现在，如果我创建了一个与上面创建的表相同的表，但省略了主键，如下所示：
Create table `raceTimes2` (
`userID` mediumint(8) unsigned,
`time` time,
key `idx_time` (`time`)
) engine=InnoDB default charset=utf8;那么结果集就不会在这个go的时间列上排序。即使我告诉它在我的查询中专门使用idx_time索引，这种行为仍然有效。另外，如果我对查询进行解释，我会得到这样的结果：
mysql> explain select * from testTable6 use index(`idx_time`);
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | raceTimes2 | ALL | NULL | NULL | NULL | NULL | 20 | |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+所以我想知道的是幕后发生了什么。为什么看起来，如果我有一个主键和另一个索引，我甚至不需要尝试就可以获得按索引排序的结果集，为什么查询优化器使用其他索引而不是主键索引？
Stack Overflow用户
发布于 2012-07-21 06:55:37
正如戈登所指出的，您不应该依赖于结果集的自然顺序。您得到结果的原因如下：
在第一种情况下，MySQL只使用idx_time索引执行查询，而不打开实际的表。当您使用的所有列都在索引中时，这是可能的( InnoDB表的主键总是附加到每个索引的末尾，因此您的索引实际上是幕后的(time，userID) )。结果是按时间排序的，因为这是time索引中的实际顺序。
在第二种情况下，列userID不是任何索引的一部分，并且MySQL必须执行常规表扫描才能获取结果。本例中的"use index(idx_time)“不执行任何操作，因为没有使用time列的WHERE子句。
编辑：
它仅在有选择的情况下才适用，但如果不能使用在USE INDEX MySQL中指定的索引，则将不使用该表上的任何索引进行搜索(WHERE/ON子句)，并将读取整个表。因此，在使用索引提示时应该非常小心。
此外，在explain中使用type='index‘的行意味着表中的所有行都将被读取，这几乎与type=' all’一样糟糕。
您应该查看有关index hints和explain output的MySQL手册。
https://stackoverflow.com/questions/11587780
复制相似问题
腾讯云开发者
