时间戳列上的MySQL索引不用于大日期范围

前端之家收集整理的这篇文章主要介绍了时间戳列上的MySQL索引不用于大日期范围前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我有桌子

+-------------------+----------------+------+-----+---------------------+-----------------------------+
| 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;

更新=====>

根据之间的评论也失败了.

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)

最佳答案
MysqL优化器试图做最快的事情 – 它认为使用索引将花费比执行表扫描更长或更长的时间,它放弃了可用的索引.这就是你在例子中看到的情况:范围很小(1天),索引会更快;在范围很大的情况下,您可能会更多地打击表,也可以直接扫描表(请记住,使用索引包括搜索索引然后从表中获取索引记录 – 两组寻求).

如果您认为自己比优化器更清楚(它并不完美),请使用提示(http://dev.mysql.com/doc/refman/5.5/en/index-hints.html).

原文链接:https://www.f2er.com/mysql/433747.html

猜你在找的MySQL相关文章