數十億行數據的最佳數據庫和表設計

我正在編寫一個需要儲存和分析大量電氣和溫度數據的應用程序。

基本上，我需要為數万個地點儲存過去幾年和未來許多年的大量每小時用電量測量結果，然後以一種不太複雜的方式分析數據。

我需要（目前）儲存的資訊是位置 ID、時間戳（日期和時間）、溫度和用電量。

關於需要儲存的數據量，這是一個近似值，但大致如下：

20 000 多個位置，每月 720 條記錄（每小時測量，每月大約 720 小時），120 個月（10 年前) 以及未來許多年。簡單的計算產生以下結果：

20 000 個位置 x 720 條記錄 x 120 個月（10 年前）= 1 728 000 000 條記錄。

這些是過去的記錄，每月將導入新記錄，因此每月大約有 20 000 x 720 = 14 400 000 條新記錄。

總地點也將穩步增長。

在所有這些數據上，需要執行以下操作：

1. 檢索特定日期和時間段的數據：日期 01.01.2013 和 01.01.2017 之間以及 07:00 和 13:00 之間特定位置 ID 的所有記錄。
2. 特定日期和時間範圍的簡單數學運算，例如 07:00 和 13:00 之間特定位置 ID 的 5 年的 MIN、MAX 和 AVG 溫度和用電量。

數據將每月寫入一次，但會被數百名使用者（至少）不斷讀取，因此讀取速度更為重要。

我沒有使用 NoSQL 數據庫的經驗，但從我收集到的資訊來看，它們是在這裡使用的最佳解決方案。我讀過最流行的 NoSQL 數據庫，但由於它們完全不同，並且允許非常不同的表架構，所以我無法確定最好使用的數據庫。

我的主要選擇是 Cassandra 和 MongoDB，但由於我對大數據和 NoSQL 的知識非常有限，沒有實際經驗，所以我不太確定。我還讀到 PostreSQL 也能很好地處理如此大量的數據。

我的問題如下：

1. 我應該對如此大量的數據使用 NoSQL 數據庫嗎？如果不能，我可以堅持使用 MySQL 嗎？
2. 我應該使用什麼數據庫？
3. 我應該將日期和時間保存在單獨的索引（如果可能）列中，以便在某些時間和日期期間快速檢索和處理數據，還是可以通過將時間戳保存在單個列中來完成？
4. 時間序列數據建模方法在這里合適嗎？如果不合適，你能給我指點一下如何設計一個好的表格嗎？

謝謝你。

這正是我每天都在做的事情，只是我不使用每小時數據，而是使用 5 分鐘數據。我每天下載大約 2 億條記錄，所以你在這裡說的數量不是問題。5 分鐘的數據大小約為 2 TB，我的天氣數據按位置按小時計算可追溯到 50 年前。那麼，讓我根據我的經驗回答你的問題：

1. 不要為此使用 NoSQL。數據高度結構化，非常適合關係數據庫。
2. 我個人使用 SQL Server 2016，並且在該數據量上應用計算沒有問題。當我開始工作時，它最初位於 PostgreSQL 實例上，它無法處理小型 AWS 實例上的數據量。
3. 我強烈建議提取日期的小時部分並將其與日期本身分開儲存。相信我，從我的錯誤中吸取教訓！
4. 我按列表儲存大部分數據（DATE、TIME、DATAPOINT_ID、VALUE），但這不是人們想要解釋數據的方式。為針對數據和大量數據透視的一些可怕查詢做好準備。不要害怕為太大而無法即時計算的結果集創建非規範化表。

一般提示：我將大部分數據儲存在兩個數據庫之間，第一個是直接的時間序列數據並且是標準化的。我的第二個數據庫非常反規範化，包含預先聚合的數據。與我的系統一樣快，我並沒有忽視使用者甚至不想等待 30 秒來載入報告的事實——即使我個人認為 30 秒來處理 2 TB 的數據非常快。

為了詳細說明為什麼我建議將小時與日期分開儲存，以下是我這樣做的幾個原因：

1. 電氣數據的呈現方式是按小時結束– 因此，01:00 實際上是前一小時的電功率平均值，而 00:00 是 Hour Ending 24。（這很重要，因為您實際上必須搜尋兩個日期才能包含 24 小時值 - 您當天正在尋找加上第二天的第一個標記。）但是，天氣數據實際上是以向前的方式呈現的（實際和預測下一小時）。根據我對這些數據的經驗，消費者希望分析天氣對電價/需求的影響。如果您要使用直接日期比較，您實際上是在比較前一小時的平均價格與下一小時的平均溫度，即使時間戳相同。DATETIME柱子。
2. 表現。我會說，我生成的至少 90% 的報告都是圖表，通常是針對單個日期或一系列日期的時間繪製價格。必須將時間與日期分開可能會降低用於生成報告的查詢速度，具體取決於您要查看的日期範圍。消費者希望看到過去 30 年同比的單一日期並不少見（事實上，對於天氣而言，這需要生成 30 年的正常值）——這可能很慢。當然，您可以優化您的查詢並添加索引，相信我，我有一些我寧願沒有的瘋狂索引，但它使系統執行得更快。
3. 生產率。我討厭不得不多次編寫同一段程式碼。我曾經將日期和時間儲存在同一列中，直到我不得不一遍又一遍地編寫相同的查詢來提取時間部分。過了一會兒，我厭倦了必須這樣做並將其提取到自己的列中。您編寫的程式碼越少，出錯的可能性就越小。此外，不必編寫更少的程式碼意味著您可以更快地獲得報告，沒有人願意整天等待報告。
4. 終端使用者。並非所有最終使用者都是高級使用者（即知道如何編寫 SQL）。將數據以一種可以輕鬆導入 Excel（或其他類似工具）的格式儲存，這將使您成為辦公室中的英雄。如果使用者無法輕鬆訪問或操作數據，他們將不會使用您的系統。相信我，幾年前我設計了完美的系統，但由於這個原因沒有人使用它。數據庫設計不僅僅是遵守一組預定義的規則/指南，它是關於使系統可用。

正如我上面所說，這一切都是基於我的個人經驗，讓我告訴你，我已經經歷了幾年的艱難和大量的重新設計才能達到現在的水平。不要像我做的那樣，從我的錯誤中吸取教訓，並確保在做出有關數據庫的決策時讓系統的最終使用者（或開發人員、報告作者等）參與進來。

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