mysql - 为什么带有 InnoDB 的 MySQL 在键存在时进行表扫描并选择检查 70 倍以上的行？

Question

我正在解决查询性能问题。这是来自解释的预期查询计划:

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)) 的索引，因此应该从此查询中选择大约 500 万行。

但是，如果我再查询一分钟的数据，我们会得到这个查询计划:

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”语法不会更改查询计划。

在此先感谢您的帮助，我非常乐意提供更多信息/回答问题。

Answer 1

500 万个索引探测可能比读取所有 3.5 亿行(顺序磁盘读取)更昂贵(大量随机磁盘读取，可能更复杂的同步)。

这种情况可能是一个异常(exception)，因为时间戳的顺序大概与插入表的顺序相匹配。但是，除非 tdcol 上的索引是“聚集”索引(意味着数据库确保基础表中的顺序与 tdcol 中的顺序匹配)，否则优化器不太可能知道这一点。

在没有顺序相关信息的情况下，假设您想要的 500 万行大致均匀分布在 3.5 亿行中是正确的，因此索引方法将涉及读取大部分或几乎所有的行无论如何，底层行中的页面(在这种情况下，扫描将比索引方法便宜得多，完全读取和顺序读取比随机读取更少)。