mysql - 从 MySQL/MariaDB 表中最近插入的行中选择行

问题描述

我要将传感器数据记录到 MariaDB 表中......基本上，三列：

ts -- 阅读的时间戳
srcID -- TINYINT UNSIGNED 表示传感器报告数据的 ID
值 -- SMALLINT UNSIGNED -- 16 位值

有三个主要用例：

作为传感器读数插入表中的数据……大约 4/分钟/传感器。一般来说，读取传感器后会立即插入记录……如果某些原因导致插入失败超过几分钟，则根本不插入。
获取每个源的最新传感器读数，最多可返回 15 分钟。这大约每 15 秒发生一次，并且需要非常快。
从过去的小时/天/周/月/等中选择长期图表的值。这将是一个相对不常见的情况，并且可能会相当缓慢。

我的大问题...

是否存在按插入时间的时间顺序物理存储行的 MySQL/MariaDB 表类型？（存档，也许？）
有没有办法告诉查询优化器，“执行 {this-query}，但不是从头开始进行全表（或分区）扫描，而是扫描表中的 500 个最新行”？（理论：如果行是按照插入的顺序存储的，那么最新的读数可能实际上不是最后插入的......但几乎可以肯定它会在最后插入的几百行中）。

我可以发誓我记得读过这样的 MySQL 或 MariaDB 存储引擎，它针对“日志记录类型”记录进行了优化，并且工作方式大致如此......但现在我实际上正在寻找，我找不到任何东西。

我知道我可以按“ts”上的范围对表进行分区（实际上，将按“ts”上的范围进行分区），但如果我每周或每月只使用一个分区而不是必须每天管理一个分区（对于在蓝月亮中执行一次的查询来说很慢是一回事......对于每几百毫秒执行一次的查询来说很慢完全是另一回事）。

标签： mysqlmariadb

解决方案

多少个传感器？为了“做数学”，我假设为 100。如果有 100 万个传感器，那么就会存在严重的缩放问题。（无论如何，257会炸毁TINYINT.）

4/分钟/传感器 * 100 个传感器 = 插入 7 行/秒 -INSERT侧面几乎没有问题。

使用 InnoDB。让我们估计 40 字节/行，包括尚未指定的索引。加起来大约 8GB/年。没问题，我想。

您将保留数据多长时间？这无关紧要，因为我将设计其余部分以充分扩展。

听起来您想在过去 15 分钟内读取所有传感器？那将是 6K 行（对于 100 个传感器）？240KB。我们需要担心的只是不要将行分散得太多，以至于它们不能保持缓存 15 分钟。没问题。

只有（？）两种选择PRIMARY KEY：

(ts, srcID)
(srcID, ts)

由于集群，value将在那里。（阅读有关 PK 与 InnoDB 中数据的“聚类”。）

我告诉人们“在指定查询之前你不能设计模式”。所以这里...

SELECT * FROM t WHERE ts >= NOW() - INTERVAL 15 MINUTE;

这要求查询扫描240KB的PRIMARY KEY(ts, ...)数据——非常快、非常高效、没有浪费。这SELECT将只需要几分之一秒。所以，CPU大部分时间都会打鼾。

对于较旧的图表，查询可以“足够快”。“最后一小时”将仅与 15 分钟查询一样慢。对于其他人，我会建议使用汇总表来使天查询几乎与小时查询一样快，从而让 CPU 获得更多的美眠。

汇总表将有

hr -- timestamp truncated to the hour
srcID
avg_value -- the AVG() over the hour
min_value -- if you want it
max_value -- if you want it
num_readings -- COUNT(*) if you want it
PRIMARY KEY(hr, srcID)

就在每小时结束之后，您将添加一组新的行。每日/每周/等图表将从汇总表中获取，而不是从主（“事实”）表中获取。这要小得多，因此要快得多。

或者，您可以在汇总后将主表中的数据扔掉！这将您的磁盘占用空间从 8GB/年减少到远低于 1GB/年。

更多关于汇总表：http: //mysql.rjweb.org/doc.php/summarytables

你的“大”问题：

Q1：没有数据类型或引擎；InnoDB的PRIMARY KEY控制顺序。

如果选择在一段时间后删除数据，PARTITION BY RANGE (TO_DAYS(ts))会导致DROP PARTITION. 更多。目标是不超过 50 个分区——如果你有“太多”的分区，效率会很低。如果您将数据保存 5 年，则可能建议每月分区。

Q2：InnoDB 的 PK 与作为“缓存”的“buffer_pool”一起表示“最新”行将位于 RAM 中等待获取。（也有例外，例如在重新启动或一些重要的维护之后。）即使缓存是“冷的”，“集群”表示将执行从磁盘提取的最小次数。240K=15*16KB 块——在旋转驱动器上可能需要 150 毫秒；在 SSD 上更快。汇总表将其缩小了 10 倍（或其他因素）。

您可能已经阅读过一篇关于 MyISAM 最适合日志记录的旧文章。这是一个老妇人的故事。InnoDB 有所改进；MyISAM 停滞不前，正在被弃用。此外，InnoDB 中的 PK 集群可能使您的主SELECTs运行速度比使用 MyISAM 更快，后者处理索引的方式不同。

mysql - 从 MySQL/MariaDB 表中最近插入的行中选择行

问题描述

解决方案

推荐阅读