在历史表中搜索时间戳中的一排的速度是如此之慢

Question

我正在使用正在存储从"真实"表中存储每个更改的历史表。

在时间戳列表中检索数据的那一刻，性能是可怕的。

在这里，我的桌子的简化版本。

CREATE TABLE `changes` (
  `ts` datetime DEFAULT NULL
) ENGINE=InnoDB;
CREATE TABLE `history` (
  `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `start_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_ts` timestamp NULL DEFAULT NULL,
  `pk` int(11) DEFAULT NULL,
  `note` mediumtext,
  PRIMARY KEY (`h_id`),
  KEY `history_pk_ts_tsev_IDX` (`pk`,`start_ts`,`end_ts`) USING BTREE
) ENGINE=InnoDB;

这是我正在运行的查询：

SELECT * FROM `changes` AS `c`
JOIN `history` AS `h`
ON (`h`.`pk` = 9999
    AND `c`.`ts` >= `h`.`start_ts`
    AND `c`.`ts` < IFNULL(`h`.`end_ts`, `c`.`ts` + 1)
   )

changes中的2.500行和history中的55.000行，查询大约需要8秒才能获得第一行，大约2分钟才能获得全部。

这张桌子会很快生长，很快就会存储数英里的行。

这是解释结果：

|id |select_type |table |partitions |type |possible_keys          |key |key_len |ref |rows  |filtered|Extra                                              |
|---|------------|------|-----------|-----|-----------------------|----|--------|----|------|--------|---------------------------------------------------|
|1  |SIMPLE      |c     |           |ALL  |                       |    |        |    |2448  |100     |                                                   |
|1  |SIMPLE      |h     |           |ALL  |history_pk_ts_tsev_IDX |    |        |    |54227 |16.66   |Using where; Using join buffer (Block Nested Loop) |

我试图强制索引：

SELECT * FROM `changes` AS `c`
JOIN `history` AS `h` FORCE INDEX (history_pk_ts_tsev_IDX)
ON (`h`.`pk` = 2476
    AND `c`.`ts` >= `h`.`start_ts`
    AND `c`.`ts` < IFNULL(`h`.`end_ts`, `c`.`ts` + 1)
   )

现在，第一行大约需要10秒钟。同样，解释：

|id |select_type |table |partitions |type |possible_keys          |key                    |key_len |ref   |rows  |filtered|Extra                 |
|---|------------|------|-----------|-----|-----------------------|-----------------------|--------|------|------|--------|----------------------|
|1  |SIMPLE      |c     |           |ALL  |                       |                       |        |      |2448  |100     |                      |
|1  |SIMPLE      |h     |           |ref  |history_pk_ts_tsev_IDX |history_pk_ts_tsev_IDX |5       |const |27113 |33.33   |Using index condition |

我试图以任何顺序创建索引，并没有运气：

• （pk，start_ts，end_ts）
• （start_ts，end_ts）
• （end_ts）
• （end_ts，start_ts）
• ...

我已经在AWS中可用的最大MySQL RD中进行了测试，因此结果不是硬件问题。

我真的迷路了。我想念什么？

谢谢。

Answer 1

也许您可以尝试多线程阅读您的数据。答案应该来自另一个问题。