MySQL中使用BETWEEN语句时执行时间过长。

Question

这个查询的执行时间超过了2秒（10,000行数据）。是否有可能对这个查询进行优化？

SELECT id, MIN(ABS(timestamp_a - timestamp_b))
FROM a 
  INNER JOIN b ON ( timestamp_a  between (timestamp_b - 5 * 60) 
              AND (timestmap_b + 5 * 60) )
GROUP BY id

样例结果 (id, 时间戳A, 时间戳B, 差值):

1   1349878538  1349878539  1
2   1349878679  1349878539  2
3   1349878724  1349878539  1
5   1349878836  1349878539  1
6   1349878890  1349878641  1

表格a

CREATE TABLE `a` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`timestamp_a`  bigint(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `a` (`timestamp_a`) USING BTREE 
)

表格 b

CREATE TABLE `b` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`timestamp_b`  bigint(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX `b` (`timestamp_b`) USING BTREE 
)

这两个表之间没有关联 - 我要从表'a'中搜索那些时间戳在表'b'时间戳范围内的记录。

编辑：简单解决方案（运行非常快）：

SELECT id, MIN(ABS(timestamp_a - timestamp_b))
FROM (SELECT id, timestamp, (timestamp - 5 * 60) timestamp_a, (timestamp + 5 * 60) timestamp_b) a
INNER JOIN b ON ( timestamp between timestamp_a AND timestamp_b )
GROUP BY id

- ekstro

---

你能更好地解释一下你的表结构吗？在你的问题中，可能包括两个表的SHOW CREATE TABLE输出。这些表如何相互关联？我认为主要问题在于你非常复杂的JOIN条件，但是如果没有这些信息，很难推荐替代方案。 - Mike Brant

谢谢您的回答-请现在查看我的问题 - ekstro

这并不会对这个查询有明显的帮助，但是你应该将时间戳列设为TIMESTAMP或INT UNSIGNED NOT NULL，BIGINT只是错误的类型。ID列也应该是UNSIGNED。 - G-Nugget

如果速度比规范化更重要...您可以在表“b”中添加两列：timestamp_b_minus和timestamp_b_plus...这两列将包含您正在执行的联接计算的结果。 然后，您可以对这两个列创建索引，这将防止您现在可能看到的表扫描。 - Michael Fredrickson

@Michael 在我的情况下无法添加那些列，但是你的建议可以帮助。 - ekstro

Answer 1

采用Michael所规定的修改时间戳列约定，此查询将以“更快”的查询性能产生原始查询的预期结果：

SELECT a.id, MIN(ABS(a.timestamp_a - tmp_b.timestamp_b))
FROM (SELECT id, timestamp_b, (timestamp_b - 5 * 60) timestamp_b_minus, (timestamp_b + 5 * 60) timestamp_b_plus) tmp_b
INNER JOIN a ON ( a.timestamp_a between tmp_b.timestamp_b_minus AND tmp_b.timestamp_b_plus )
GROUP BY a.id

原始查询遇到性能限制的原因是由于RDBMS被迫对b进行全表扫描，因为在ON子句中使用了公式，需要对a的每一行执行。

尽管“更快”的查询需要对b进行全表扫描以生成“临时”表tmp_b，但它能够使用a.timestamp_a上的索引根据条件从a中提取适当的值：tmp_b.timestamp_b_minus AND tmp_b.timestamp_b_plus

- Drew