將時間序列數據儲存在普通 SQL 數據庫中

我有大約 20 個機器感測器的歷史數據，時間解析度為一秒，儲存在 csv 文件中，需要在進一步數據處理和分析之前將其導入 SQL 數據庫。

數據 tim 導入的代表性模型如下所示：

+---------------------+------------------+------------------+------------------+-----+-------------------+
| timestamp           | name_of_sensor_0 | name_of_sensor_1 | name_of_sensor_2 | ... | name_of_sensor_19 |
+---------------------+------------------+------------------+------------------+-----+-------------------+
| 2019-12-25 05:35:20 | 10               | 11               | 12               | ... | 19                |
+---------------------+------------------+------------------+------------------+-----+-------------------+
| 2019-12-25 05:35:21 | 20               | 21               | 22               | ... | 29                |
+---------------------+------------------+------------------+------------------+-----+-------------------+
| 2019-12-25 05:35:22 | 30               | 31               | 32               | ... | 39                |
+---------------------+------------------+------------------+------------------+-----+-------------------+
| 2019-12-25 05:35:23 | 40               | 41               | 42               | ... | 49                |
+---------------------+------------------+------------------+------------------+-----+-------------------+
| 2019-12-25 05:35:24 | 50               | 51               | 52               | ... | 59                |
+---------------------+------------------+------------------+------------------+-----+-------------------+
| 2019-12-25 05:35:25 | 60               | 61               | 62               | ... | 60                |
+---------------------+------------------+------------------+------------------+-----+-------------------+

對於每個感測器，數據庫中也應該有一些描述性元數據，因為這些元數據可能包含在進一步數據分析期間獲得洞察力所需的重要資訊。每個感測器都有以下元數據：

- designator
- acquisition_channel
- system_code
- unit_code
- sub_system_code
- function_code
- major_counting_number
- minor_counting_number
- measurement_unit
- description

為了結合讀數和元數據，我考慮使用兩個 SQL 表。一個包含上面給出的所有元數據：

SensorTable
- id
- designator
- acquisition_channel
- unit_code
- system_code
- sub_system_code
- function_code
- major_counting_number
- minor_counting_number
- measurement_unit
- description

另一個表格包含每個感測器在給定時間戳的讀數。為了能夠合併兩個表中的數據，我可以JOIN在sensor_idwhich 是外鍵上執行：

ReadingsTable
- id
- timestamp
- sensor_uid
- value

Both table defintions are implemeted using `sqlalchemy` like this:

class Sensor(Base):
   __tablename__ = 'sensors'

   id = Column(Integer, primary_key=True, autoincrement=True)
   designator = Column(String, unique=True, nullable=False)
   acquisition_channel = Column(String)
   unit_code = Column(String)
   system_code = Column(String)
   sub_system_code = Column(String)
   function_code = Column(String)
   major_counting_number = Column(String)
   minor_counting_number = Column(String)
   measurement_unit = Column(String, nullable=False)
   description = Column(String)
   readings = relationship('Reading')


class Reading(Base):
   __tablename__ = 'readings'

   id = Column(Integer, primary_key=True, autoincrement=True)
   timestamp = Column(DateTime, nullable=False)
   sensor_id = Column(Integer, ForeignKey('sensors.id'), nullable=False)
   value = Column(Float, nullable=False)

這個表格設計對我來說很明顯，應該滿足規範化的基本原則。但是，在查看了生成的表行之後，我想知道是否需要（或可以）timestamp進一步規範化列。中的每一行都ReadingsTable包含給定時間戳的感測器讀數。由於所有感測器都在完全相同的時間進行測量，因此我在同一列中得到了很多重複的時間戳。從上面回顧我的數據模型，摘錄ReadingsTable如下：

+-----+---------------------+-----------+-------+
| id  | timestamp           | sensor_id | value |
+-----+---------------------+-----------+-------+
| 60  | 2019-12-25 05:35:22 | 1         | 30    |
+-----+---------------------+-----------+-------+
| 61  | 2019-12-25 05:35:22 | 2         | 31    |
+-----+---------------------+-----------+-------+
| 62  | 2019-12-25 05:35:22 | 3         | 32    |
+-----+---------------------+-----------+-------+
| ... | ...                 | ...       | ...   |
+-----+---------------------+-----------+-------+

timestamp由於每個時間戳的重複條目，我是否需要進一步規範化列？我怎麼能這樣做？我應該對我的數據庫/表設計進行哪些採用？

我看了一下這個答案，它提出了一種非常相似的方法，但仍然沒有解決時間戳列中的重複項。

不要規範化任何“連續”或“數字”值，例如時間戳。

首先，TIMESTAMP每個DATETIME佔用 5 個字節。 INT佔用 4 個字節。所以，這並沒有多少節省。一個 3 字節MEDIUMINT UNSIGNED（0..16M）的節省仍然不足以彌補以下…

更重要的是，如果進行範圍掃描，在“維度”表中包含“範圍”將使查詢效率非常低。

Readings.id可能沒有必要——sensor_id 加上時間戳是唯一的；將組合設為PRIMARY KEYfor Reading。同時，sensor_id應盡可能小。 TINYINT UNSIGNED允許 256 個感測器；夠了嗎？（注意：它會重複readings很多次。）

PS to Akina：添加小數秒後，TIMESTAMP從 4 個字節變為 5 個或更多。更多的是如果你有分數。 DATETIME從 8 個字節的壓縮十進製表示變為只有 5 個（或更多）

索引

Sensors將擁有它id以及您將經常使用的任何其他內容。因為它是一張小桌子；選擇並不重要。

的索引Readings非常重要，因為表的大小和大查詢的可能性。SELECTs在你畫出草圖之前，你無法真正做出決定。但我可以猜到：

PRIMARY KEY(sensor_id, timestamp)
INDEX(timestamp)

PK 允許在單個感測器上有效收集資訊。對於您似乎需要的每秒 20 次插入，它已經足夠高效了。

我不知道二級索引是否有用。

分區

分區的唯一可能用途是如果您打算刪除“舊”數據。我們可以討論PARTITION BY RANGE時間序列。否則，不要使用PARTITIONs.

匯總表

將一個月的數據繪製到第二個。無論數據如何儲存和索引，都需要花費大量時間來獲取幾毫米的行，只是在建構圖形時會丟棄大部分資訊。我們可以進一步討論匯總表。

參考資料（用於分區、匯總表等）：http: //mysql.rjweb.org/

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