<p>我最近注意到完成簡單 INSERT 語句所需的時間差異顯著增加。雖然這些語句平均需要大約 11 毫秒，但有時可能需要 10-30 秒，我甚至注意到它們的執行時間超過 5 分鐘。 </p> <p>MySQL版本是<code>8.0.24</code>，運行在Windows Server 2016上。據我所知，伺服器的資源從未過載。伺服器有充足的 CPU 開銷可供使用，並為其分配了 32GB 的 RAM。 </p> <p>這是我正在使用的表：</p> <pre class="brush:php;toolbar:false;">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</pre> <p>該表沒有其他索引或外鍵，也不用作任何其他表中外鍵的引用。整個表格大小約 20GB，行數約 281M，我覺得不算太大。 </p> <p>此表幾乎完全以唯讀方式使用，每秒讀取次數高達 1000 次。所有這些讀取都發生在簡單的 SELECT 查詢中，而不是在複雜的事務中，並且它們有效地利用了主鍵索引。對此表的並發寫入（如果有的話）非常少。這樣做是有意為之，目的是試圖弄清楚它是否有助於緩慢插入，但事實並非如此。在此之前，始終有最多 10 個並發插入在進行。永遠不會在此表上執行 UPDATE 或 DELETE 語句。 </p> <p>我遇到問題的查詢都是這樣建構的。它們從未出現在交易中。雖然根據聚集主鍵插入絕對不是僅追加的，但查詢幾乎總是將 1 到 20 個相鄰行插入到表中：</p> <pre class="brush:php;toolbar:false;">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)</pre> <p>這是上述查詢的 EXPLAIN 語句的輸出：</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>選擇類型</th> <th>表</th> <th>分區</th> <th>類型</th> <th>可能的鍵</th> <th>鍵</th> <th>key_len</th> <th>參考</th> <th>行</th> <th>已過濾</th> <th>額外</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>插入</td> <td>已儲存的段</td> <td>空</td> <td>全部</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> </tr> </tbody> </table> <p>這些問題是相對較新的問題，並且在桌子大約小兩倍時並不明顯。 </p> <p>我嘗試減少表中的並發插入數量，從大約 10 個減少到 1 個。我還刪除了某些列上的外鍵 (<code>recording_id</code>)，以進一步加快插入速度。 <code>分析表</code> 和模式分析沒有產生任何可操作的資訊。</p> <p>我想到的解決方案是刪除聚集主鍵，在 <code>(recording_id, index)</code> 列上新增自動增量主鍵和常規索引。在我看來，這將有助於使插入“僅附加”。我願意接受任何和所有建議，提前致謝！ </p> <p>編輯： 我將解決評論和答案中提出的一些觀點和問題：</p> <ul> <li><code>autocommit</code> 設定為 <code>ON</code></li> <li><code>innodb_buffer_pool_size</code><code>21474836480</code>，<code>innodb_buffer_pool_chunk_size>，<code>innodb_buffer_pool_chunk_size> / li> <li>一則評論提出了對讀取所使用的讀鎖與寫入所使用的排它鎖之間的爭用的擔憂。該表的使用有點像緩存，我不需要讀取來始終反映表的最新狀態，如果這意味著性能的提高。然而，即使在伺服器崩潰和硬體故障的情況下，該表也應該保持耐用。這可以透過更寬鬆的事務隔離等級來實現嗎？ </li> <li>架構絕對可以優化； <code>recording_id</code> 可以是4 個位元組整數，<code>end_filetime</code> 可以改為經過的值，並且<code>start_filetime</code>time< ; 也可能更小。恐怕這些變更只會將問題推遲一段時間，直到表的大小增加以補償節省的空間。 </li> <li>對錶的插入始終是連續的 在表上執行的 SELECT 如下所示：</li> </ul> <pre class="brush:php;toolbar:false;">SELECT TRUE FROM saved_segment WHERE recording_id = ? AND `index` = ?</pre> <pre class="brush:php;toolbar:false;">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</pre> <p>第二種類型的查詢肯定可以透過索引來改進，但我擔心這會進一步降低 INSERT 效能。 </p> <p>我忘記提及的另一件事是存在一個與此非常相似的表。它的查詢和插入方式完全相同，但可能會進一步導致 IO 飢餓。 </p> <p>編輯2： <code>SHOW TABLE STATUS</code> 表<code>saved_segment</code> 的結果，以及一個非常相似的表<code>saved_screenshot</code> （表在<code>saved_screenshot</code> （該表在<code null</code> 欄位上有一個附加索引）。</p> <table class="s-table"> <thead> <tr> <th>姓名</th> <th>引擎</th> <th>版本</th> <th>行格式</th> <th>行</th> <th>平均行長</th> <th>資料長度</th> <th>最大資料長度</th> <th>Index_length</th> <th>無資料</th> <th>自動增量</th> <th>創建時間</th> <th>更新時間</th> <th>檢查時間</th> <th>整理</th> <th>校驗與</th> <th>建立選項</th> <th>評論</th> </tr> </thead> <tbody> <tr> <td>已儲存的螢幕截圖</td> <td>InnoDB</td> <td>10</td> <td>動態</td> <td>483430208</td> <td>61</td> <td>29780606976</td> <td>0</td> <td>21380464640</td> <td>6291456</td> <td>空</td> <td>「2021-10-21 01:03:21」</td> <td>「2022-11-07 16:51:45」</td> <td>空</td> <td>utf8mb4_0900_ai_ci</td> <td>空</td> <td></td> <td></td> </tr> <tr> <td>已儲存的段</td> <td>InnoDB</td> <td>10</td> <td>動態</td> <td>281861164</td> <td>73</td> <td>20802699264</td> <td>0</td> <td>0</td> <td>4194304</td> <td>空</td> <td>「2022-11-02 09:03:05」</td> <td>「2022-11-07 16:51:22」</td> <td>空</td> <td>utf8mb4_0900_ai_ci</td> <td>空</td> <td></td> <td></td> </tr> </tbody> </table></p>

(recording_id, index)

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

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

PRIMARY KEY(recording_id, index)

INDEX(recording_id, start_filetime)

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