mysql - 在 MySQL 中使用 BETWEEN 执行时间长

Question

此查询的执行时间超过 2 秒(对于 10k 行)。是否可以优化此查询？

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、timestamp_a、timestamp_b、diff):

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

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 被迫对 a 中的每一行执行 b 的全表扫描在 ON 子句中使用。

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