Поиск в исторической таблице строки в метке времени, так медленно

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)
   )

С 2.500 строк в changes и 55.000 строк historyзапрос занимает около 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)
• ...

Я протестировал в самой большой MySQL RDS, доступной в AWS, с теми же результатами, поэтому не является аппаратной проблемой.

Я действительно потерян. Что мне не хватает?

Благодарю.

Answer 1

Может быть, вы можете попробовать многопоточность, чтобы прочитать ваши данные. Ответ должен быть там от другого вопроса.