插入到沒有自動增量主鍵的大型 MySQL 表中非常慢

Question

我最近注意到完成簡單 INSERT 語句所需的時間差異顯著增加。雖然這些語句平均需要大約 11 毫秒，但有時可能需要 10-30 秒，我甚至注意到它們的執行時間超過 5 分鐘。

MySQL版本是8.0.24，運行在Windows Server 2016上。據我所知，伺服器的資源從未過載。伺服器有充足的 CPU 開銷可供使用，並為其分配了 32GB 的 RAM。

這是我正在使用的表：

CREATE TABLE `saved_segment` (
  `recording_id` bigint unsigned NOT NULL,
  `index` bigint unsigned NOT NULL,
  `start_filetime` bigint unsigned NOT NULL,
  `end_filetime` bigint unsigned NOT NULL,
  `offset_and_size` bigint unsigned NOT NULL DEFAULT '18446744073709551615',
  `storage_id` tinyint unsigned NOT NULL,
  PRIMARY KEY (`recording_id`,`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

該表沒有其他索引或外鍵，也不用作任何其他表中外鍵的引用。整個表格大小約 20GB，行數約 281M，我覺得不算太大。

此表幾乎完全以唯讀方式使用，每秒讀取次數高達 1000 次。所有這些讀取都發生在簡單的 SELECT 查詢中，而不是在複雜的事務中，並且它們有效地利用了主鍵索引。對此表的並發寫入（如果有的話）非常少。這樣做是有意為之，目的是試圖弄清楚它是否有助於緩慢插入，但事實並非如此。在此之前，始終有最多 10 個並發插入在進行。永遠不會在此表上執行 UPDATE 或 DELETE 語句。

我遇到問題的查詢都是這樣建構的。它們從未出現在交易中。雖然根據聚集主鍵插入絕對不是僅追加的，但查詢幾乎總是將 1 到 20 個相鄰行插入到表中：

INSERT IGNORE INTO saved_segment
(recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) VALUES
(19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10),
(19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10),
(19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10),
(19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10),
(19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10),
(19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10),
(19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10),
(19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10),
(19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10),
(19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)

這是上述查詢的 EXPLAIN 語句的輸出：

id	選擇類型	表	分區	類型	可能的鍵	鍵	key_len	參考	行	已過濾	額外
1	插入	已儲存的段	空	全部	空	空	空	空	空	空	空

這些問題是相對較新的問題，並且在桌子大約小兩倍時並不明顯。

我嘗試減少表中的並發插入數量，從大約 10 個減少到 1 個。我還刪除了某些列上的外鍵 (recording_id)，以進一步加快插入速度。 分析表 和模式分析沒有產生任何可操作的資訊。

我想到的解決方案是刪除聚集主鍵，在 (recording_id, index) 列上新增自動增量主鍵和常規索引。在我看來，這將有助於使插入“僅附加”。我願意接受任何和所有建議，提前致謝！

編輯： 我將解決評論和答案中提出的一些觀點和問題：

• autocommit 設定為 ON
• innodb_buffer_pool_size21474836480，innodb_buffer_pool_chunk_size>，innodb_buffer_pool_chunk_size> / li>
• 一則評論提出了對讀取所使用的讀鎖與寫入所使用的排它鎖之間的爭用的擔憂。該表的使用有點像緩存，我不需要讀取來始終反映表的最新狀態，如果這意味著性能的提高。然而，即使在伺服器崩潰和硬體故障的情況下，該表也應該保持耐用。這可以透過更寬鬆的事務隔離等級來實現嗎？
• 架構絕對可以優化； recording_id 可以是4 個位元組整數，end_filetime 可以改為經過的值，並且start_filetimetime< ; 也可能更小。恐怕這些變更只會將問題推遲一段時間，直到表的大小增加以補償節省的空間。
• 對錶的插入始終是連續的 在表上執行的 SELECT 如下所示：

SELECT TRUE
FROM saved_segment
WHERE recording_id = ? AND `index` = ?

SELECT index, start_filetime, end_filetime, offset_and_size, storage_id
FROM saved_segment
WHERE recording_id = ? AND
        start_filetime >= ? AND
        start_filetime <= ?
ORDER BY `index` ASC

第二種類型的查詢肯定可以透過索引來改進，但我擔心這會進一步降低 INSERT 效能。

我忘記提及的另一件事是存在一個與此非常相似的表。它的查詢和插入方式完全相同，但可能會進一步導致 IO 飢餓。

編輯2： SHOW TABLE STATUS 表saved_segment 的結果，以及一個非常相似的表saved_screenshot （表在saved_screenshot （該表在 欄位上有一個附加索引）。

姓名	引擎	版本	行格式	行	平均行長	資料長度	最大資料長度	Index_length	無資料	自動增量	創建時間	更新時間	檢查時間	整理	校驗與	建立選項	評論
已儲存的螢幕截圖	InnoDB	10	動態	483430208	61	29780606976	0	21380464640	6291456	空	「2021-10-21 01:03:21」	「2022-11-07 16:51:45」	空	utf8mb4_0900_ai_ci	空
已儲存的段	InnoDB	10	動態	281861164	73	20802699264	0	0	4194304	空	「2022-11-02 09:03:05」	「2022-11-07 16:51:22」	空	utf8mb4_0900_ai_ci	空

Answer 1

我會帶著這個答案冒險。

假設

• innodb_buffer_pool_size 的值略小於 20MB，且
• #每秒 1K 個選擇隨機到達表格的各個部分，然後

系統最近變得受 I/O 限制，因為下一個 Select 所需的「下一個」區塊越來越經常不會緩存在 buffer_pool 中。

簡單的解決方案是獲取更多 RAM 並提高該可調參數的設定。但表格只會成長到您購買的下一個限制。

相反，這裡有一些部分解決方案。

• 如果數字不太大，前兩列可能是INT UNSIGNED（4 個位元組而不是8），甚至可能是 MEDIUMINT UNSIGNED（3 個位元組）） 。注意 ALTER TABLE 會長時間鎖定表。
• 這些開始和結束時間看起來像是帶有小數秒的時間戳，且始終為「.000」。 DATETIME 和 TIMESTAMP 佔用 5 個位元組（而不是 8 個位元組）。
• 您的範例顯示經過時間為 0。如果 (end-start) 通常非常小，那麼儲存經過時間而不是結束時間會進一步縮小資料。 （但是使用結束時間會讓事情變得混亂）。
• 您提供的範例資料看起來「連續」。這與自動增量的效率差不多。這是常態嗎？如果不是，INSERT 可能是 I/O 抖動的一部分。
• 您建議添加人工智慧以及二級索引，這使得插入的工作量增加了一倍；所以我不推薦它。

更多

是的，情況就是這樣。

將其作為 INDEX，或者更好的是，作為 PRIMARY KEY 的開頭，可以為您的兩個查詢提供最佳幫助：

(recording_id, index)

回覆:

SELECT  TRUE
FROM    saved_segment
WHERE   recording_id = ? AND `index` = ?

如果它用於控制其他一些 SQL，請考慮將其添加到其他 SQL 中：

... EXISTS ( SELECT 1
        FROM    saved_segment
        WHERE   recording_id = ? AND `index` = ? ) ...

該查詢（無論哪種形式）都需要您已有的內容

PRIMARY KEY(recording_id, index)

您的其他查詢需求

INDEX(recording_id, start_filetime)

所以，加入索引，或...

更好...這個組合對於兩者來說都更好SELECT：

PRIMARY KEY(recording_id, start_filetime, index).
INDEX(recording_id, index)

有了這個組合，

• 單行存在性檢查將「使用索引」執行，因為它是「覆蓋」。
• 另一個查詢將找到在 PK 上聚集在一起的所有相關行。
• （PK 有這3 列，因為它需要是唯一的。它們按這個順序排列有利於您的第二個查詢。而且它是PK，而不僅僅是一個INDEX，因此它不需要在索引的BTree 之間反彈和數據的BTree。）
• 「叢集」可以透過減少此類查詢所需的磁碟區塊數量來提高效能。這會減少 buffer_pool 中的“顛簸”，從而減少增加 RAM 的需要。
• 我的索引建議大部分與我的資料類型建議正交。