我在 Linux (Debian) 上執行 MariaDB 10.2.13。

我正在設置一個 InnoDB 表，它將記錄大量數據，但我只會保留最後一小時的行。因此，行數將保持不變。

我應該期待這張桌子：

• 許多插入
• 許多更新
• 不時進行一些 DELETE（行 > 1 小時）

例子：

表僅使用固定長度欄位和一些索引定義。

CREATE TABLE `tbl_log` (
 `ip` int(4) unsigned NOT NULL,
 `date` datetime NOT NULL,
 `external_id` smallint(6) unsigned NOT NULL,
 `counter` smallint(6) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`ip`,`external_id`),
 KEY `external_id` (`external_id`),
 KEY `counter` (`counter`),
 KEY `date_idx` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入（和更新，通過on duplicate key）可能看起來像這樣（ip和external_id會有所不同）：

INSERT INTO tbl_log
 SET ip = INET_ATON('192.168.1.1'),
     date = now(),
     external_id = 123,
     counter = 0
 ON DUPLICATE KEY UPDATE counter=counter+1;

最後，刪除舊行將通過查詢完成：

DELETE FROM tbl_log WHERE date < DATE_SUB(NOW(), INTERVAL 1 HOUR);

問題：

這樣的表會隨著時間的推移而產生碎片嗎？

如果是這樣，我想我應該對其進行碎片整理。如有必要，我計劃在刪除後立即執行OPTIMIZE TABLE tbl_log;（使用選項）…innodb-defragment=1

我出於各種原因說“不”。

• 碎片會在 之後立即再次出現OPTIMIZE，所以為什麼要麻煩。
• 碎片化沒什麼大不了的。性能問題很小。BTree 的“深度”很少因碎片而改變。
• InnoDB 傾向於通過組合不到半滿的相鄰塊來消除碎片。所以，再一次，為什麼要麻煩做一個OPTIMIZE.
• 您的問題詢問“固定長度”欄位 - 僅適用於 MyISAM，它具有完全不同的表結構。

DELETE需要很長時間嗎？它是伺服器中其他活動的負擔嗎？如果是這樣，有一個比OPTIMIZE…分區更好的解決方案。

我會考慮每隔 5 分鐘進行一次分區。DROP PARTITION每5分鐘做一次。它將比大型DELETE. 更多討論：http: //mysql.rjweb.org/doc.php/partitionmaint

你現在多久做一次DELETE？即使您不使用PARTITIONing，也要編寫一個不斷重複DELETE. 與任何使用cron.

附帶問題：

索引counter是一個相當大的負擔。每次counter更新 a​​ 時，都需要刪除舊的索引條目，並需要在其他地方添加新條目。

為什麼要刪除date？您可能會在一秒鐘內碰到計數器，然後在下一秒鐘刪除該行。

我會說是的，有四（4）個主要原因

1. 頁面邊界/索引頁面
2. 索引佈局
3. 指數統計
4. 批量 DML

頁面邊界/索引頁面

根據關於 InnoDB 表限制的 MySQL 文件

對於 4KB、8KB、16KB 和 32KB 頁面大小，除了可變長度列（VARBINARY、VARCHAR、BLOB 和 TEXT）外，最大行長度略小於頁面的一半。例如，預設innodb_page_size 16KB 的最大行長度約為 8000 字節。對於 64KB 的 InnoDB 頁面大小，最大行長度約為 16000 字節。LONGBLOB 和 LONGTEXT 列必須小於 4GB，總行長（包括 BLOB 和 TEXT 列）必須小於 4GB。

您的表每行使用 INT UNSIGNED（4 個字節）、DATETIME（8 個字節）、2 個 SMALLINT（4 個字節）。每行總共有 16 個字節。這意味著您可以將 1000 行放入單個 InnoDB 頁面中，但會有非常小的碎片。使用預設的innodb_page_size（16K 或 16384），一個頁面會有 384 個字節的碎片。它最有可能用於映射一頁內的數據。

執行大量 INSERT、DELETE 和 UPDATE 將生成許多頁面，其中包含標記為未使用的整行的空白空間。這是對數據頁的 384 字節區域的補充。

索引佈局

您將生成許多索引頁面

• 你有一個 6 字節的 PRIMARY KEY
• 每個二級索引都帶有 PRIMARY KEY 的副本
• KEY external_id (external_id),將是 8 個字節（SMALLINT + PRIMARY KEY）
• KEY counter (counter),將是 8 個字節（SMALLINT + PRIMARY KEY）
• KEY date_idx (date)將是 14 個字節（日期 + 主鍵）
• 36 字節用於索引單行
• 索引表示為 BTREE，因此您正在查看 O(n log n) 空間使用率。如果考慮到非葉子頁面拆分，許多索引頁面將有 50% 為空（毫無疑問）。

指數統計

執行 INSERT、DELETE 和 UPDATE 會扭曲索引統計資訊，因為索引基數會經常更改。你需要執行ANALYZE TABLE tbl_log;. 執行OPTIMIZE TABLE tbl_log;將對錶進行碎片整理並ANALYZE TABLE tbl_log;作為最後一步執行（請參閱我的文章Feb 27, 2013：What is the difference between optimize table and analyze table in mysql）

散裝 DML

您將按小時執行的 DELETE 查詢是批量操作。許多行需要大量清理才能跟上已刪除的標記行。

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