我有一个简单的Message表,带有2个索引：

mysql> show keys from Message;
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Message |          0 | PRIMARY   |            1 | id             | A         |     5643295 |     NULL | NULL   |      | BTREE      |         |
| Message |          1 | timestamp |            1 | startTimestamp | A         |     5643295 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

通过查询发出订单时,将检查大量行：

mysql> explain SELECT * from Message ORDER BY startTimestamp LIMIT 0,20;
+----+-------------+---------+-------+---------------+-----------+---------+------+---------+-------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows    | Extra |
+----+-------------+---------+-------+---------------+-----------+---------+------+---------+-------+
|  1 | SIMPLE      | Message | index | NULL          | timestamp | 8       | NULL | 5643592 |       |
+----+-------------+---------+-------+---------------+-----------+---------+------+---------+-------+

总行数为：

mysql> select count(*) from Message;
+----------+
| count(*) |
+----------+
|  5837363 |
+----------+

此查询涉及96.7％的行.索引是BTREE,据我所知,它应该只抽取前20行并将其返回.就目前而言,它使用索引来访问表的几乎所有行,这可能比全表扫描要慢.

我是否误以为它应该只使用索引选择前20行并将其返回？

MySQL服务器版本为5.0.45,表类型为InnoDB.

解决方法:

在EXPLAIN中,MySQL估计扫描到的行数为5645592,但它使用的索引正确(按列排序：时间戳),并且限制为20行,因此请不要担心它做的是正确的事情,它将在20时停止行已发送.您能给我们查询执行时间吗？