我正在对查询性能问题进行故障排除.这是来自explain的预期查询计划：

MysqL> explain select * from table1 where tdcol between '2010-04-13 00:00' and '2010-04-14 03:16';
+----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table              | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | table1             | range | tdcol         | tdcol        | 8       | NULL | 5437848 | Using where | 
+----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

这是有道理的,因为使用了名为tdcol(KEY tdcol(tdcol))的索引,因此应从该查询中选择大约5M行.

但是,如果我仅查询一分钟的数据,我们将得到以下查询计划：

MysqL> explain select * from table1 where tdcol between '2010-04-13 00:00' and '2010-04-14 03:17';
+----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | table1             | ALL  | tdcol         | NULL | NULL    | NULL | 381601300 | Using where | 
+----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

优化器认为扫描会更好,但是要检查的行数却多了70倍,因此我很难相信表扫描会更好.

同样,“ USE KEY tdcol”语法不会更改查询计划.

在此先感谢您的帮助,我们非常乐意提供更多信息/答案问题.