我有一个表,它在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
上,我插入了第一个值为1457.2
的数据点,用于time_series_id
123
。is_latest
的默认值为1
。
稍后我想修改该数据点值(同时出于时间点分析的原因保留现有行(。因此,我为2018-01-01
插入一个新的数据点,其值为44795.778
表示time_series_id
123
。
我现在需要将is_latest
标志设置为0
旧修订版。
我有一个更新查询来执行此操作,但执行需要 ~400 毫秒,data_points
表中有 ~300 万行......
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
?
对于此查询:
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, logged_at, upload_id)
。 第一个可能已被现有索引覆盖。
INDEX(time_series_id, is_latest, o.upload_id)
但是,我想知道。 当"最大的upload_id"具有相同的含义时,为什么is_latest
标志具有相同的含义? 让我们看看使用is_latest
的查询,看看我们是否可以摆脱该标志。 通过摆脱查询,这将使查询速度提高 100%!