У меня есть две таблицы: markets (27 записей) и histories (~ 1,75 млн записей, ~ 67 тыс. на рынок).

Мне нужно получить каждый рынок с его последней histories записью. Решения, которые я пробовал, работают, но невероятно медленно.

Таблицы DDL

CREATE TABLE `markets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `base_asset_id` int unsigned NOT NULL,
  `quote_asset_id` int unsigned NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `markets_base_asset_id_quote_asset_id_unique` (`base_asset_id`,`quote_asset_id`),
  KEY `markets_base_asset_id_index` (`base_asset_id`),
  KEY `markets_quote_asset_id_index` (`quote_asset_id`),
  CONSTRAINT `markets_base_asset_id_foreign` FOREIGN KEY (`base_asset_id`) REFERENCES `assets` (`id`),
  CONSTRAINT `markets_quote_asset_id_foreign` FOREIGN KEY (`quote_asset_id`) REFERENCES `assets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `histories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `market_id` int unsigned NOT NULL,
  `timeframe` enum('1_m','5_m','15_m','30_m','1_H','4_H','6_H','12_H','1_D','1_W','1_M') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `time` int unsigned NOT NULL,
  `is_source` tinyint(1) NOT NULL DEFAULT '0',
  `is_final` tinyint(1) NOT NULL DEFAULT '0',
  `open` decimal(36,18) NOT NULL,
  `high` decimal(36,18) NOT NULL,
  `low` decimal(36,18) NOT NULL,
  `close` decimal(36,18) DEFAULT NULL,
  `volume` decimal(36,18) NOT NULL,
  `ohlc_avg` decimal(36,18) DEFAULT NULL,
  `hl_avg` decimal(36,18) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `histories_market_id_timeframe_time_unique` (`market_id`,`timeframe`,`time`),
  KEY `histories_market_id_index` (`market_id`),
  KEY `histories_timeframe_index` (`timeframe`),
  KEY `histories_time_index` (`time`) USING BTREE,
  CONSTRAINT `histories_market_id_foreign` FOREIGN KEY (`market_id`) REFERENCES `markets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2334503 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Что я пробовал

1 - Некоррелированный подзапрос

Я начал с этого решения, так как использовал его в другой раз, это занимает ~ 7,5 с:

SELECT
    m.*,
    h.time,
    h.close
FROM
    markets m
LEFT JOIN (
    SELECT
        market_id,
        MAX(`time`) AS `time`
    FROM
        histories h
    WHERE
        h.is_final = 1
    GROUP BY
        market_id
) latest_history
    ON latest_history.market_id = m.id
LEFT JOIN histories h
    ON h.market_id = m.id
    and h.`time` = latest_history.time;

EXPLAIN результат:

+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                            | key                       | key_len | ref                 | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
|  1 | PRIMARY     | m          | NULL       | ALL   | NULL                                                                                     | NULL                      | NULL    | NULL                |      27 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>                                                                              | <auto_key0>               | 4       | db_name.m.id        |    1745 |   100.00 | Using index |
|  1 | PRIMARY     | h          | NULL       | ref   | histories_market_id_timeframe_time_unique,histories_market_id_index,histories_time_index | histories_time_index      | 4       | latest_history.time |      26 |   100.00 | Using where |
|  2 | DERIVED     | h          | NULL       | index | histories_market_id_timeframe_time_unique,histories_market_id_index                      | histories_market_id_index | 4       | NULL                | 1744647 |    10.00 | Using where |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+

2 - Использование WITH

Я попытался запустить подзапрос с помощью WITH, но без каких-либо улучшений, все еще ~ 7,5 с:

WITH latest_history AS (
    SELECT
        market_id,
        MAX(h.`time`) AS `time`
    FROM
        histories h
    WHERE
        h.is_final = 1
    GROUP BY
        market_id
)
SELECT
    m.*,
    h.time,
    h.close
FROM
    markets m
LEFT JOIN latest_history
    ON latest_history.market_id = m.id
LEFT JOIN histories h
    ON h.market_id = m.id
    AND h.`time` = latest_history.time;

EXPLAIN результат (идентичен предыдущему)

+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                            | key                       | key_len | ref                 | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+
|  1 | PRIMARY     | m          | NULL       | ALL   | NULL                                                                                     | NULL                      | NULL    | NULL                |      27 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>                                                                              | <auto_key0>               | 4       | db_name.m.id        |    1745 |   100.00 | Using index |
|  1 | PRIMARY     | h          | NULL       | ref   | histories_market_id_timeframe_time_unique,histories_market_id_index,histories_time_index | histories_time_index      | 4       | latest_history.time |      26 |   100.00 | Using where |
|  2 | DERIVED     | h          | NULL       | index | histories_market_id_timeframe_time_unique,histories_market_id_index                      | histories_market_id_index | 4       | NULL                | 1744647 |    10.00 | Using where |
+----+-------------+------------+------------+-------+------------------------------------------------------------------------------------------+---------------------------+---------+---------------------+---------+----------+-------------+

3 - Использование WITH и оконных функций

Затем я обновился с 5.7 до 8.0.22, чтобы попробовать этот другой предложенный метод, который занимает еще больше: ~ 11 с.

WITH latest_history AS (
    SELECT
        h.*,
        ROW_NUMBER() OVER (PARTITION BY market_id ORDER BY id DESC) AS rn
    FROM
        histories AS h
    where
        h.is_final = 1
)
SELECT
    m.*,
    latest_history.time,
    latest_history.close
FROM
    markets m
LEFT JOIN latest_history
    ON latest_history.market_id = m.id
    AND latest_history.rn = 1;

EXPLAIN результат:

+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+---------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref                | rows    | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+---------+----------+-----------------------------+
|  1 | PRIMARY     | m          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL               |      27 |   100.00 | NULL                        |
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 12      | db_name.m.id,const |    1744 |   100.00 | NULL                        |
|  2 | DERIVED     | h          | NULL       | ALL  | NULL          | NULL        | NULL    | NULL               | 1744647 |    10.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+---------+----------+-----------------------------+

Дополнительная информация

Затем я увидел, что один только подзапрос (MAX и GROUP BY), необходимый в решениях 1 и 2, занимает ~ 7,5 с!

Поэтому я считаю, что должно быть что-то принципиально неправильное в histories структуре/индексах, а не в том, как я присоединяюсь markets к этому. Чтобы было ясно, я имею в виду:

SELECT
    market_id,
    MAX(h.`time`) AS `time`
FROM
    histories h
WHERE
    h.is_final = 1
GROUP BY
    market_id;

EXPLAIN результат:

+----+-------------+-------+------------+-------+---------------------------------------------------------------------+---------------------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                                                       | key                       | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------------------------------------------------------+---------------------------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | h     | NULL       | index | histories_market_id_timeframe_time_unique,histories_market_id_index | histories_market_id_index | 4       | NULL | 1744647 |    10.00 | Using where |
+----+-------------+-------+------------+-------+---------------------------------------------------------------------+---------------------------+---------+------+---------+----------+-------------+

time — это int, представляющий временную метку Unix, id можно использовать, но это не повышает производительность.

Конечно, я пытался просмотреть другие вопросы/сообщения, касающиеся проблемы с производительностью в этой группе, но я все еще не могу это исправить.