如何優化 2.5 億行的 Mysql 數據庫進行批量插入和選擇

我是一名學生，我的任務是為感測器數據設計數據庫。我的大學目前有一個大型數據庫，其中充滿了這些數據，但儲存的很多內容並不是必需的。他們希望我從現有數據庫中提取一些欄位，並將其插入一個新數據庫，該數據庫僅包含“必需品”。我需要從舊行中提取每一行，並每天獲取一次新數據。

• 有 1500 個感測器。
• 他們每分鐘產生一次讀數。
• 每天大約有 210 萬個讀數
• 目前數據庫有大約 2.5 億行。

將執行的查詢通常是為給定時間跨度之間的一組感測器選擇感測器讀數。

最初我對大量數據引入的複雜性很天真，所以我嚴重低估了這項任務所需的時間。因此，以及我無法在家中訪問伺服器的事實，我在這裡尋求幫助和意見。

最初的設計如下所示：

CREATE TABLE IF NOT EXISTS SENSORS (
   ID smallint UNSIGNED NOT NULL AUTO_INCREMENT,
   NAME varchar(500) NOT NULL UNIQUE,
   VALUEFACETS varchar(500) NOT NULL,
   PRIMARY KEY (ID)
); 

CREATE TABLE IF NOT EXISTS READINGS (
   ID int UNSIGNED AUTO_INCREMENT,
   TIMESTAMP int UNSIGNED INDEX NOT NULL,
   VALUE float NOT NULL,
   STATUS int NOT NULL,
   SENSOR_ID smallint UNSIGNED NOT NULL,
   PRIMARY KEY (ID),
   FOREIGN KEY (SENSOR_ID) REFERENCES SENSORS(ID)
);

設計問題

我的第一個問題是我是否應該為讀數保留一個自動遞增的鍵，或者在 TIMESTAMP（UNIX 紀元）和 SENSOR_ID 上使用複合鍵是否更有益？

這個問題既適用於我每天必須插入 210 萬行的事實，也適用於我想針對上述查詢進行優化的事實。

初始批量插入：

經過大量試驗和錯誤並在網上找到指南後，我發現使用 load infile 插入最適合此目的。我編寫了一個腳本，該腳本將同時從舊數據庫中選擇 500 000 行，並將它們（全部 2.5 億）寫入一個 csv 文件，如下所示：

TIMESTAMP,SENSOR_ID,VALUE,STATUS
2604947572,1399,96.434564,1432543

然後我的計劃是使用 GNU sort 對其進行排序，並將其拆分為包含 100 萬行的文件。

在插入這些文件之前，我將刪除 TIMESTAMP 上的索引，並執行以下命令：

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SESSION tx_isolation='READ-UNCOMMITED';
SET sql_log_bin = 0;

插入後，我當然會還原這些更改。

• 這個計劃是否可行？
• 如果我根據 SENSOR_ID 和 TIMESTAMP 而不是 TIMESTAMP 和 SENSOR_ID 對 csv 進行排序，是否可以加快插入速度？
• 在批量插入後重新打開索引後，是否可以每天插入 200 萬行？
• *是否可以使用正常插入語句進行日常插入，還是必須使用 load infile 才能跟上

輸入負載？*

我的.cnf

除了這些之外，每個配置都是預設配置：

 innodb_flush_log_at_trx_commit=2
 innodb_buffer_pool_size=5GB
 innodb_flush_method=O_DIRECT
 innodb_doublewrite = 0

我是否需要為此特定目的進行任何其他優化？

伺服器有 8GB 的​​記憶體。mysqld 版本 8.0.22 Ubuntu 20.04

任何想法、想法或投入將不勝感激。

“感測器”數據集的一般建議：

• 最小化數據類型
• 最小化索引
• 批量插入
• 每秒 25 行速度很快，但不需要更劇烈的步驟

規格：

• STATUS int NOT NULL - 4字節？它可能有什麼價值？（如果可行，將其縮小。）
• 我建議這PRIMARY KEY(sensor_id, timestamp)將是獨特的和足夠的。id然後徹底擺脫。結果是要更新的二級索引減少了。我推薦這個列的順序。但真正的選擇需要基於SELECTs將要執行的操作。
• 將所有 1500 行收集到一個INSERT. 或使用LOAD DATA INFILE（除了這需要更多的磁碟命中）。也就是說，您INSERT每分鐘將擁有一個。單執行緒跟上應該沒有問題。“持續”載入，我認為等到一天結束沒有任何好處。（或者我錯過了什麼？）
• 調試你的程式碼，然後去掉FOREIGN KEY; FK 檢查需要額外的努力。
• 將數百萬行放入表中的初始 LOAD DATA ——這可能會達到一些超時和/或緩衝區限制。在這種情況下，將其分成塊 - 但不是 1M 行；而不是 10K 行。並做單執行緒。如果您嘗試使用多執行緒執行此操作，我會毫不猶豫地考慮可能會遇到的問題。此外，它可能主要受 I/O 限制，因此無法從多執行緒中受益。
• 開autocommit；這樣每個塊都將被送出。否則，重做日誌會變得很大，佔用額外的磁碟空間並減慢速度。
• 預分類數據——這對一些人有幫助。在您的原始模式中，按輔助鍵排序(timestamp)；AUTO_INCREMENT意志會照顧好自己。如果您刪除 auto_inc，則按 排序PRIMARY KEY(sensor_id, timestamp)，如果您接受我的建議。
• 在載入數據時準備好PRIMARY KEY。否則在建構 PK 時需要復製表。
• 如果有輔助鍵，ALTER TABLE .. ADD INDEX ..則在初始載入後。
• 設置看起來不錯。但是，我會留下innodb_doublewrite來——這可以防止罕見但災難性的數據損壞。

與您提供的連結相關的評論：

• 該連結已有 8 年曆史。但其中大部分仍然有效。（但是，我不同意一些細節。）
• 如果您最終需要刪除舊數據，請**立即計劃。請參閱此時間序列，該時間序列通過使用以下方式大大加快了每月刪除舊數據的速度：http PARTITION BY RANGE(): //mysql.rjweb.org/doc.php/partitionmaint 注意：我建議的 PK 是按天分區的正確方法（或週或月）。
• 分區的唯一性能優勢（基於到目前為止所討論的內容）是關於DELETEingvia DROP PARTITION。沒有SELECTs可能跑得更快（或更慢）。
• 如果您使用mysqldump，只需使用預設值。這將產生可管理INSERTs的大量行。除非您來自其他來源，否則不需要 TSV/CSV。
• 我不知道他在 LOAD FILE 和批量 INSERT 以及每塊的行數之間的辯論。也許他的二級索引讓事情變慢了。“更改緩衝區”是二級索引的一種寫入記憶體。一旦它已滿，插入必然會減慢，等待更新被刷新。通過擺脫二級索引，這個問題就消失了。或者，通過ADD INDEX在載入數據後使用，可以推遲成本。

趕上一個破碎的 cron 工作……

如果您有一個 cron 作業根據源中的時間戳擷取“昨天”的數據，則仍然存在 cron 作業失敗或什至不執行的風險（當您的伺服器在應該執行該作業時關閉時）。

使用我推薦的 PK(sensor, ts)，以下是相當有效的：

SELECT sensor, MAX(ts) AS max_ts
   FROM dest_table GROUP BY sensor;

效率來自於跳桌，只打了 1500 個點。

在 cron 作業開始時，（在目標上）執行它以“找出你離開的地方”：

SELECT MAX(max_ts) AS left_off FROM
      ( SELECT sensor, MAX(ts) AS max_ts
           FROM dest_table GROUP BY sensor ) AS x;

然後從源中獲取新行

   WHERE ts > left_off
     AND ts < CURDATE()

通常，left_off將在昨天早上的午夜前不久。打嗝的時候會提前一天。

您還可以使用該子查詢來查看是否有任何感測器離線以及何時離線。

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