MySQL:在时间序列中的某个日期之前查询最后值的最佳实践

Question

我在MySQL中有下表：

CREATE TABLE `history` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `code` CHAR(32) NOT NULL,
    `value` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `timestamp_code` (`timestamp`, `code`),
    INDEX `code` (`code`),
    INDEX `timestamp` (`timestamp`)
   ) COLLATE='utf8_general_ci' ENGINE=InnoDB;

我想知道什么是最佳做法，才能最有效地获取某一组代码在某一日期之前的最后可用值？

到目前为止，我提出了以下查询：

SELECT h.* FROM history h
JOIN (
    SELECT code, MAX(timestamp) as 'last_ts'
    FROM history WHERE
    timestamp < '2015-09-04 13:50:00' AND
    code IN ('119813249', '12087792', '12087797',
    '127012151', '131014335', '131014378',
    '132757371', '15016059', '15016062',
    '150250238', '153462747', '155802712',
    '156974389', '162277696', '166330444',
    '166483001', '167220356', '167264923',
    '167867931', '172283682', '177539478',
    '177583937', '177648754', '177649011',
    '187532416', '189230667', '70273253',
    '70342790', '79342386', '82460282',
    '98693280', '98693380')
    GROUP BY code) last_price
ON last_price.last_ts = h.timestamp
AND last_price.code = h.code

上面的查询工作正常，但随着表中条目数的增加(100,000‘000行)，查询速度会变慢。

您可以下载样本数据来填充表。

Answer 1

根据代码创建索引，时间戳-而不是时间戳，代码。这将让mysql在寻找每个代码的最大时间戳之前对代码进行排序，而且应该更快。使用“解释”来验证是否使用了索引。

如果您创建了该索引-您不应该修改您的查询。