MySQL SELECT 日期之間的第 n 行（時間間隔）

我有一個伺服器，它每秒記錄各種感測器數據並將其插入 MySQL 表中。要在儀表板上查看感測器數據，我有一個圖表，該圖表每秒也會更新實時數據。該數據庫以超過 250,000 行/24 小時（3 個感測器+）的速度增長。

載入儀表板時，我想顯示歷史數據。至少 24 小時，越多越好（72+ 理想）；我的測試都是 24 小時完成的。

數據量很大（86400 行），所以我只選擇了歷史數據的一個子集，可以通過縮放進行細化。我選擇了 5 分鐘或 300 秒的間隔：

SELECT * FROM `data` WHERE `sensorId` = 0
AND `unixDate` >= (1458829800 - 86400)
AND `unixDate` < 1458829800
AND NOT `unixDate` % 300 -- left out for all data
ORDER BY `id` DESC

這個查詢當然慢得驚人，在我正在執行的該死的小機器上（樹莓派）它需要 10 多秒！

查詢所有 86400 行大約需要不到一秒鐘的時間。

我也可以在重型機器上安裝一個單獨的數據庫伺服器，但是我想在我的伺服器環境啟動並執行之前完成這個項目，我也不想在我的網路上發送這麼多數據每一秒……

我想知道兩件事：是否有更快的按時間間隔查詢數據的方法，以及這種工作是否有完全更好的解決方案（時間序列數據庫，這對我來說似乎有點矯枉過正）？

您應該創建一個匯總表來儲存此匯總數據。

5 分鐘的摘要每天需要額外的 288 行 (24 * 60 / 5)；30 分鐘摘要 - 每天 48 行 (24 * 60 / 30)；每小時摘要 - 每天 24 行。總的來說，您正在考慮每天儲存額外的 360 行 (288 + 48 + 60)，這對於大幅提升性能來說只是很小的成本 (360 / 86400 ~= 0.4167%)。

嘗試這樣的事情：

CREATE TABLE data_summary (
   granularity_mins INT NOT NULL,
   unixDate BIGINT NOT NULL,
   sensorId INT NOT NULL,
   sensor_min_value DECIMAL NOT NULL,
   sensor_max_value DECIMAL NOT NULL,
   sensor_ave_value DECIMAL NOT NULL,
   sensor_readings_count INT NOT NULL,
   PRIMARY KEY(granularity_mins, sensorId, unixDate)
)

然後，您可以使用這樣的 SELECT 獲得 5 分鐘摘要：

SELECT *
FROM data_summary
WHERE granularity_mins = 5
   AND unixDate > 1458829800 - 86400
   AND unixDate < 1458829800
   AND sensorId = 0

除了@Serge 所說的…

對於第一個查詢…如果您的典型查詢是針對單個感測器，則使用 InnoDB 並擁有PRIMARY KEY(sensorId, unixDate). 這樣，每秒選擇一次將獲得連續（聚集）的記錄，而不是跳過其他感測器的記錄。

擺脫代理人id；我PRIMARY KEY建議會做得很好，加上節省空間和時間。而且，無論如何，你想要ORDER BY unixDate，而不是BY id。

選擇合理的數據類型，以減少記錄，避免 I/O。例如，unixDate應該是 4-byte INT，而不是 8-byte BIGINT。

是的，匯總表是適用於任何數據倉庫應用程序的方式。

你需要什麼粒度？假設您不需要任何小於 1 分鐘的內容。1 分鐘的匯總表將是“事實”表大小的近 1/60，並且每隔 5 分鐘（或 1 小時或其他時間）掃描它的速度仍將是現在的近 60 倍. 在表中包含多個粒度之前，請先嘗試一下。

數學……如果您的數據總是均勻分佈（每秒），那麼“平均值的平均值”在數學上是正確的。如果間隔不均勻，則總結SUM和COUNT併計算平均值 = SUM(sums)/SUM(counts)。

引用自：https://dba.stackexchange.com/questions/133343