介于或大于 > 条件下的 MySQL 查询优化

Question

问题:查询速度慢。

• table1 大约有 5000 行
• table2 大约有 50 000 行
• 时间戳格式为int(11)
• MySQL - 20 秒(带索引)

• PostgreSQL - 0.04 秒(带索引)

  SELECT * 
  FROM table1
    LEFT JOIN table2 
      ON table2_timestamp BETWEEN table1_timestamp - 500 
                              AND table1_timestamp + 500 ;
  

谁能帮我优化这个 MySQL 查询？

解释:

1   SIMPLE  a   index       a   9       2   Using index
1   SIMPLE  b   index   b   b   9       5   Using index

表格:

CREATE TABLE `a` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `table1_timestamp`  bigint(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  INDEX `a` (`table1_timestamp`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;

CREATE TABLE `b` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `table2_timestamp`  bigint(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  INDEX `a` (`table2_timestamp`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;

Answer 1

我想到了几点，但都感觉不太可能。实际上，假设您的示例是准确的表示，您似乎对查询无能为力。

1 :您正在使用 BIGINT，其最大值为 9x10^18(已签名)。 INT 的最大值为 4x10^9(UNSIGNED)，而天数时间戳约为 1.4x10^9(所有值均为近似值)，因此请考虑将两个表中该列的数据类型从 BIGINT 到 INT UNSIGNED 或 DATETIME

2:ROW_FORMAT 是 COMPACT，这可能会导致 BTREE 索引出现问题 (source)。您正在处理 INT 数据类型，因此 FIXED 的 ROW_FORMAT 就足够了，因此请尝试在两个表上更改为 ROW_FORMAT=FIXED

3 : 如果总是期望从 table2 返回 table1 行的行，那么 INNER JOIN 将比 LEFT JOIN 更有效