從大型 MySQL 表中獲取大量行

我正在使用一個 IOT 應用程序，我們有一堆設備每分鐘左右向 AWS Aurora MySQL 5.6 (InnoDB) 實例發送讀數。該實例是 db.t2.medium（2 CPU，4GB RAM）大小。SELECT我們通過設備 ID 和感測器類型獲取的查詢已經開始花費越來越長的時間，我猜這是因為我們的實例大小已經超過了。

我們查詢的表有大約 6000 萬行，並且由於我們的功能在圖表上顯示數據的方式，我們獲取所有歷史數據而不是進行分頁。我也懷疑這可能是問題的一部分。一個範例查詢看起來像SELECT * FROM readings WHERE device_id = 1234 and sensor_type = 'pressure' and time >= 1644092837 and time <= 1646684837並返回大約 500K 行，大約需要 5-8 秒。

該readings表有四列 - device_id、sensor_type、時間（Unix 時間戳，儲存為 int）和值。複合索引位於 device_id、sensor_type 和 time。

我的主要問題是——人們是如何處理從已經很大的表中返回大量行的？由於感測器發送數據的頻率，該表只會增長。我考慮過為每個設備設置一個讀數表，但我對可能擁有數千個表並不滿意，尤其是在我們必須添加或編輯列的情況下。

我還想知道人們是如何在 IOT 案例中處理擴展數據庫的，因為我擔心如果我們繼續增加 RAM/增加實例大小，我們的 AWS 賬單將會變得非常昂貴。

（來自評論）

CREATE TABLE readings (
   device_id int(11) unsigned NOT NULL AUTO_INCREMENT, 
   sensor_type char(5) CHARACTER SET ascii NOT NULL DEFAULT '', 
   time int(11) unsigned NOT NULL, 
   value float NOT NULL, 
   PRIMARY KEY (device_id,sensor_type,time)
) ENGINE=InnoDB AUTO_INCREMENT=48025983 DEFAULT CHARSET=latin1

請提供SHOW CREATE TABLE。我懷疑你有PRIMARY KEY(id)，這不是感測器表的最佳選擇。相反，它應該是以下之一：

• 如果您可以保證永遠不會同時從一個感測器讀取兩個讀數（或者如果發生這種情況，您只需丟棄其中一個讀數，例如通過INSERT IGNORE）具有以下內容並擺脫id：

PRIMARY KEY (sensor_id, time)

• 否則會有點亂；如果你需要我可以詳細說明。

這使得您的特定表所需的所有行都“聚集”在一起。這樣，查詢的時間將與返回的行數成正比，而不是與表中的行數成正比。

我建議sensor_id，你似乎有一種 2 列的方式來指定一個獨特的感測器？ (device_id, sensor_type). 嗯，這幾乎一樣好。但是，它會在您巨大的“事實”表中佔用更多空間。因此，您可能會考慮使用Sensors包含列device_id和sensor_type（等）的表。

數據類型很重要。如果你盲目地使用（4 字節）作為 id，你應該為一些不超過 64K 的 idINT切換到（2 字節）。SMALLINT UNSIGNED NOT NULL另見TINYINT和MEDIUMINT。

現在在表變大之前縮小數據類型。它需要的停機時間只會隨著表的變大而增加。

考慮PARTITION BY RANGE(TO_DAYS(time)) 您是否打算刪除“舊”數據。這將使“刪除”（通過DROP PARTITION）大大加快，但不太可能加快其他任何事情。更多討論： 分區

你會在圖形包上扔 500K 行嗎？嗆！如果您只有 1K 點，我懷疑結果圖表的解析度是否會有所不同。為此，建構並維護一個匯總表。它應該包含sensor_id hour（或其他解析度）、count/min/max/sum/… 的度量。您是否使用SUM()或AVG()取決於您如何定義“平均”。如果缺少數據樣本，我正在考慮一天的平均值是多少？

從匯總表中提取會快得多，而不會影響圖形的解析度。

請注意，可以通過 min = min of mins、sum = sum of sum、count = sum of count 等方式匯總數據（如果保留 sum 和 sum-of-squares，即使 stddev 也可以匯總。）

更多：匯總表

我假設您有多種指標（度數、英寸等）？ FLOAT可能是最優的；它需要 4 個字節。有了更多的規律性（或縮放），有些DECIMAL可能就足夠了 2-3 個字節。

不要分成很多表。

Not-yet-ready-for-prime-time： MySQL 中的感測器數據

（對評論的回應）

嗯？您不能將滑鼠放在 500M 中的一點上！另一方面，如果放大需要幾個步驟，請執行以下操作。從匯總表開始；當這種情況發生時，從 Fact 表中獲取。也就是說，在任何階段，都不要提供全部 500M 點。相反，讓使用者在點擊原始圖表時得到一周的平均值。然後，隨著您的放大，變得越來越精緻。

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