mysql - 如何在数据库中存储时间/值数据向量

Question

我正在收集时间/值对向量，在本例中是服务器的 I/O 延迟与时间的关系。我想将它们存储在可按日期、服务器和其他元数据查询的 MySQL 数据库中。

例子:

• 我希望能够查询服务器 Atriedes 在 2007 年 8 月 19 日从下午 1:00 到下午 3:00 的 I/O 延迟。
• 我还希望能够查询服务器 Harkonnen 上 I/O 延迟超过 40 毫秒的时间。
• 我想找出所有在 2007 年 8 月 1 日延迟超过 100 毫秒的服务器。

我应该如何构建我的数据库以轻松实现这一点？

Answer 1

CREATE TABLE t_latency (
       id INT NOT NULL PRIMARY KEY,
       server_id INT NOT NULL,
       ts DATETIME NOT NULL,
       latency FLOAT NOT NULL,
       KEY ix_latency_server_time_latency (server_id, ts, latency),
       KEY ix_latency_server_latency (server_id, latency),
       KEY ix_latency_time (ts)
       )

I want be able to query the I/O latencies from 1:00 PM to 3:00 PM for the server Atriedes on August 19th, 2007

SELECT  *
FROM    t_latency
WHERE   server_id = @id_of_atriedes
        AND ts BETWEEN '2007-08-19 01:00' AND '2007-08-19 03:00'
--  will use ix_latency_server_time_latency

I want to also be able to query the times that the I/O latency on server Harkonnen where the I/O latencies are above 40 ms.

SELECT  *
FROM    t_latency
WHERE   server_id = @id_of_harkonnen
        AND latency > .04
--  will use ix_latency_server_latency

I want to find all the servers that had latencies above 100 ms on August 1st, 2007.

SELECT  DISTINCT server
FROM    t_latency
WHERE   ts >= '2007-08-01 00:00'
        AND ts < '2007-08-02 00:00'
        AND latency > 0.1
--  will use ix_latency_time