优化MySQL SELECT语句中TIMESTAMP字段的WHERE条件

举一个简单的例子,这种类型的查询将经常运行：

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

对于人口密集的表,此查询通常需要大约7秒.它有大约3500万行,MySQL上的MyISAM在Amazon RDS上运行(db.m3.xlarge).

删除WHERE子句使查询只需4秒,添加第二个子句(time_off> XXX)会增加1.5秒,使查询时间延长到8.5秒.

因为我知道这些类型的查询通常都会完成,所以我希望优化它们以便它们更快,理想情况下低于5秒.

我开始在time_on上添加一个索引,虽然这大大加快了WHERE“=”查询,但它对“>”没有影响.查询.有没有办法创建一个加速WHERE“>”的索引或“<”疑问？ 或者如果对此类查询的性能有任何其他建议,请告诉我. 注意：我使用“diff_ms”字段作为非规范化步骤(它等于time_off – time_on),它将聚合的性能提高了大约30％-40％. 我用这个命令创建索引：

ALTER TABLE writetest_table ADD INDEX time_on (time_on) USING BTREE;

在原始查询上运行“解释”(使用“time_on>”)表示time_on是“possible_key”,select_type是“SIMPLE”. “额外”列显示“使用位置”,“类型”为“全部”.添加索引后,表中显示“time_on”是“MUL”键类型,这似乎是正确的,因为同一时间可以出现两次.

这是表模式：

CREATE TABLE `writetest_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sessionID` int(11) DEFAULT NULL,
  `time_on` timestamp NULL DEFAULT NULL,
  `time_off` timestamp NULL DEFAULT NULL,
  `diff_ms` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time_on` (`time_on`)
) ENGINE=MyISAM AUTO_INCREMENT=50410902 DEFAULT CHARSET=latin1;

更新：我根据ypercube的响应创建了以下索引,但这会将第一个查询的查询时间增加到大约17秒！

ALTER TABLE writetest_table  ADD INDEX time_on__diff_ms__ix (time_on, diff_ms) ;

更新2：EXPLAIN输出

mysql> explain select sum(diff_ms) from writetest_table where time_on > '2015-07-13 15:11:56';
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
| id | select_type | table               | type  | possible_keys        | key                  | key_len | ref  | rows     | Extra                    |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | writetest_table_old | index | time_on__diff_ms__ix | time_on__diff_ms__ix | 10      | NULL | 35831102 | Using where; Using index |
+----+-------------+---------------------+-------+----------------------+----------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

更新3：请求查询的结果

mysql> SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;
+---------------------+
| time_on             |
+---------------------+
| 2015-07-13 15:11:56 |
+---------------------+
1 row in set (0.01 sec)

当我要你跑的时候

SELECT time_on FROM writetest_table ORDER BY time_on LIMIT 1;

你说这是你在WHERE子句中的2015-07-13 15:11:56

当您执行查询时

select sum(diff_ms) from writetest_table;

它执行了3580万行的全表扫描.

当您执行查询时

select sum(diff_ms) from writetest_table where time_on > ("2015-07-13 15:11:56");

它执行了3580万行的完整索引扫描.

没有WHERE子句的查询更快是完全有道理的.为什么？

表扫描在一次线性传递中读取3580万行.

WHERE查询的EXPLAIN也出现了3580万行.索引扫描会有一点不同.虽然BTREE保持按键的顺序,但是进行范围扫描很可怕.在您的特定情况下,您正在执行最差可能范围扫描,该扫描将具有与表中的行相同数量的BTREE条目. MySQL必须遍历BTREE页面(至少跨越叶子节点)才能读取值.此外,time_on列必须按照索引指定的顺序进行比较.因此,也必须遍历非叶BTREE节点.

请看我在BTREE上的帖子

> 2013年8月6日：In MySQL if column X has unique values what’s the difference between UNIQUE index and B-Tree index
> 2012年6月28日：Benefits of BTREE in MySQL

如果查询是截至今天午夜

select sum(diff_ms) from writetest_table where time_on >= ("2015-07-14 00:00:00");

甚至今天中午

select sum(diff_ms) from writetest_table where time_on >= ("2015-07-14 12:00:00");

它应该花更少的时间.

故事的道德：不要使用WHERE子句执行有序范围扫描,该扫描等于目标表中的行数.