mysql - 在日期字段中保留加入时更新速度问题

Question

我有一个在 MySQL 5.6 中存储时间序列数据点的表，名为 data_points

CREATE TABLE `data_points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time_series_id` int(10) unsigned NOT NULL,
  `logged_at` date NOT NULL,
  `data_value` decimal(20,6) DEFAULT NULL,
  `upload_id` int(10) unsigned NOT NULL,
  `is_latest` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `data_points_time_series_id_index` (`time_series_id`),
  KEY `data_points_logged_at_index` (`logged_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

is_latest 标志指示数据点是否是给定 logged_at 日期和给定 time_series_id 的最新数据点。插入新数据点时，我需要保留旧版本，但将其 is_latest 标志设置为 0。

例如，在 2018-01-01 上，我为 time_series_id 123< 插入值为 1457.2 的第一个数据点。 is_latest 的默认值为 1。

稍后我想修改该数据点值(同时出于时间点分析的原因保留现有行)。因此，我插入了 2018-01-01 的新数据点，time_series_id 123 的值为 44795.778。

我现在需要将旧版本的 is_latest 标志设置为 0。

我有一个更新查询来执行此操作，但执行 data_points 表中约 300 万行的时间约为 400 毫秒...

UPDATE data_points o
LEFT JOIN data_points o2
  ON o2.time_series_id = o.time_series_id
  AND o2.logged_at = o.logged_at
SET o.is_latest = 0
WHERE o.is_latest = 1
  AND o.time_series_id = 123
  AND o.upload_id < o2.upload_id;

我认为问题出在按日期加入时 o2.logged_at = o.logged_at 上的自加入。

是否有更有效的方法来定义哪些 data_points 行应标记为 is_latest = 0？

Answer 1

对于此查询:

UPDATE data_points o LEFT JOIN
       data_points o2
       ON o2.time_series_id = o.time_series_id AND
          o2.logged_at = o.logged_at
    SET o.is_latest = 0
WHERE o.is_latest = 1 AND
      o.time_series_id = 123 AND
      o.upload_id < o2.upload_id;

您需要两个索引:data_points(is_latest, time_series_id) 和 data_points(time_series_id,logging_at, upload_id)。第一个可能已被您的现有索引覆盖。