使用 MariaDB 數據庫進行數據輪換

我們有一台小型本地電腦，用作臨時數據庫備份，當我們失去與主雲伺服器的連接或必須對此伺服器進行維護時，自動從感測器恢復數據。

由於可用於數據的磁碟空間小（32go），我們的想法是保留最近 3 個月的感測器數據並刪除最舊的條目。

下面是數據庫的描述：

CREATE TABLE IF NOT EXISTS `machine` (
 `Nom` varchar(50) NOT NULL,
 `Site` smallint(5) unsigned NOT NULL,
 `Emplacement` smallint(5) unsigned DEFAULT NULL,
 `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Nom` (`Nom`),
 KEY `FK__site` (`Site`),
 CONSTRAINT `FK__site` FOREIGN KEY (`Site`) REFERENCES `site` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `mesure` (
 `Machine` smallint(5) unsigned NOT NULL,
 `Date` datetime NOT NULL,
 `Valeur` decimal(13,2) NOT NULL,
 PRIMARY KEY (`Machine`,`Date`),
 KEY `Date` (`Date`) USING BTREE,
 CONSTRAINT `FK__machine` FOREIGN KEY (`Machine`) REFERENCES `machine` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `site` (
 `Nom` varchar(50) NOT NULL,
 `ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

第一次我認為定期刪除就足夠了，但我發現innoDB不會向作業系統釋放空間磁碟，現在磁碟分區已滿（4個月填充）。而且，這些刪除有點慢。

經過一些研究，我發現分區可以解決我的問題，但似乎不可能在表內使用外鍵進行分區。

嘗試了此程式碼，僅用於測試目的：

ALTER TABLE mesure
PARTITION BY RANGE(UNIX_TIMESTAMP(Date))
(
   PARTITION START VALUES LESS THAN (UNIX_TIMESTAMP("2021-10-01 00:00:00")),
   PARTITION MONTH1 VALUES LESS THAN (UNIX_TIMESTAMP("2021-11-01 00:00:00")),
   PARTITION MONTH2 VALUES LESS THAN (UNIX_TIMESTAMP("2021-12-01 00:00:00")),
   PARTITION MONTH3 VALUES LESS THAN (UNIX_TIMESTAMP("2022-01-01 00:00:00")),
   PARTITION END VALUES LESS THAN MAXVALUE
   
); 

結果是：/* Erreur SQL (1217) : Cannot delete or update a parent row: a foreign key constraint fails */

我現在用完了解決方案，以使該解決方案在我們的硬體上可行。

關於如何管理這個的任何想法？

建議每週分區，否則你需要持有近 4 個月的價值才能丟棄最舊的。更多關於時間序列分區的討論： 分區

一旦你調試了程式碼，就沒有真正的需要了FOREIGN KEYs，所以不用它們。（然後根據需要添加合適的索引。）

decimal(13,2)佔用 6 個字節；考慮FLOAT，這需要 4。（但在決定之前先查看範圍和舍入問題。）

你能改變任何一個SMALLINTs（2字節) to TINYINT`（1字節）嗎？

這個問題的另一種解決方案可能是實現一個環形緩衝區。從版本 10.3 開始，這在 MariaDB 中是可能的。環形緩衝區的想法最初是在Federico Razzoli的舊部落格上描述的，該部落格不再可用。

• 您的measure表需要一個非複合主鍵，例如ID int unsigned.
• 您將需要使用您選擇的maxvalue.
• 寫入表時，使用REPLACE而不是INSERT. 此外，使用該序列獲取下一個可用 ID。

換句話說，是這樣的：

CREATE SEQUENCE measure_seq
 START WITH 1
 INCREMENT BY 1
 MINVALUE=1
 MAXVALUE=10000000
 CYCLE;

然後像這樣寫數據：

REPLACE INTO measure (ID, ...) 
 VALUES (NEXTVAL(measure_seq), ...);

這樣，該表的行數將永遠不會超過您選擇的最大值，因為當它達到該限制時，它將用新值覆蓋舊值。此外，您不必擔心管理分區。

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