时间戳列上的MySQL索引在大日期范围内未被使用

Question

我有一个表格，格式如下：

+-------------------+----------------+------+-----+---------------------+-----------------------------+
| Field             | Type           | Null | Key | Default             | Extra                       |
+-------------------+----------------+------+-----+---------------------+-----------------------------+
| id                | bigint(20)     | NO   | PRI | NULL                | auto_increment              |
| runtime_id        | bigint(20)     | NO   | MUL | NULL                |                             |
| place_id          | bigint(20)     | NO   | MUL | NULL                |                             |
| amended_timestamp | varchar(50)    | YES  |     | NULL                |                             |
| applicable_at     | timestamp      | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| schedule_time     | timestamp      | NO   | MUL | 0000-00-00 00:00:00 |                             |
| quality_indicator | varchar(10)    | NO   |     | NULL                |                             |
| flow_rate         | decimal(15,10) | NO   |     | NULL                |                             |
+-------------------+----------------+------+-----+---------------------+-----------------------------+

我在schedule_time字段上创建了索引

create index table_index on table(schedule_time asc);

当前表格有2121552+条记录。

我不明白的是当我解释时。

explain select runtime_id from table where schedule_time >= now() - INTERVAL 1 DAY;
+----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
| id | select_type | table    | type  | possible_keys                | key                          | key_len | ref  | rows  | Extra       |
+----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | table    | range | table_index                  | table_index                  | 4       | NULL | 38088 | Using where |
+----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

上面的索引被使用了，但下面的索引没有被使用。

mysql> explain select runtime_id from table where schedule_time >= now() - INTERVAL 30 DAY;
+----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys                | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table    | ALL  | table_index                  | NULL | NULL    | NULL | 2118107 | Using where |
+----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

如果有人能指出这里有什么问题，我会非常感激，因为数据每12分钟更新一次，随着时间的推移，查询30天或60天的数据速度会变得非常缓慢。

我计划使用它的最终查询如下：

select avg(flow_rate),c.group from table a ,(select runtime_id from table where schedule_time >= now() - INTERVAL 1 DAY group by schedule_time ) b,place c  where a.runtime_id = b.runtime_id and a.place_id = c.id group by c.group;

更新 =====>

根据评论，"between fails too" 也会失败。

mysql> explain select runtime_id from table where schedule_time between '2013-07-17 12:48:00' and '2013-08-17 12:48:00';
+----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys                | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table    | ALL  | table_index                  | NULL | NULL    | NULL | 2118431 | Using where |
+----+-------------+----------+------+------------------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain select runtime_id from table where schedule_time between '2013-08-16 12:48:00' and '2013-08-17 12:48:00';
+----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
| id | select_type | table    | type  | possible_keys                | key                          | key_len | ref  | rows  | Extra       |
+----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | table    | range | table_index                  | table_index                  | 4       | NULL | 38770 | Using where |
+----+-------------+----------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

更新2 =======>

mysql> select count(*) from table where schedule_time between '2013-08-16 12:48:00' and '2013-08-17 12:48:00';
+----------+
| count(*) |
+----------+
|    19440 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from table where schedule_time between '2013-07-17 12:48:00' and '2013-08-17 12:48:00';
+----------+
| count(*) |
+----------+
|   597132 |
+----------+
1 row in set (0.00 sec)

服务器版本：5.5.24-0ubuntu0.12.04.1（Ubuntu）

- baba.kabira

---

类似的问题在这里 - 思路是使用'between'语句：https://dev59.com/6HI-5IYBdhLWcg3wCztn - FreudianSlip

我无法重现那种行为（没有尝试太多，不过...）只是一个猜测：运行 ANALYZE TABLE my_table 是否会改善情况？ - Sylvain Leroux

如果EXPLAIN在列type中报告ALL作为连接类型，那么这确实令人困惑...作为可能的解决方法，你可以尝试：SET @d = now() - INTERVAL 30 DAY; SELECT .... where schedule_time >= @d - Sylvain Leroux

我发现了这个链接http://bugs.mysql.com/bug.php?id=64998，我猜测mysql版本5.5.24中存在一个bug。 - baba.kabira

600 k / 2100 k = 整个表的28％，我认为优化器可能会做出一个好决定，它更喜欢全表扫描而不是索引，在这种情况下，整个表的顺序扫描可能比使用索引的随机访问更快，您可以尝试使用“force index hint” - http://dev.mysql.com/doc/refman/5.6/en/index-hints.html，这应该强制MySql使用该索引，然后将查询时间与FTS和INDEX进行比较。 - krokodilko

Answer 1

MySQL优化器尝试做最快的事情。当它认为使用索引所需的时间与执行表扫描相等或更长时，它会放弃可用的索引。

这就是你在示例中看到的情况：

- 当范围很小（1天）时，索引会更快； - 当范围很大时，你将访问表格的更多部分，最好直接扫描整个表格（请记住，使用索引需要搜索索引，然后从表格中获取索引记录 - 两组查找）。

如果你认为自己比优化器更懂（它不完美），使用INDEX提示：

USE INDEX（index_list）提示告诉MySQL仅使用一个指定的索引来查找表中的行。替代语法IGNORE INDEX（index_list）告诉MySQL不要使用某些特定的索引。如果EXPLAIN显示MySQL正在使用可能索引列表中的错误索引，则这些提示非常有用。

- D Mac