面試官：MySQL一次到底插入多少條資料合適啊？

來源：Kfaino 連結：juejin.cn/post/7295675926126592034

前言

在網際網路時代，我們的每一個動作，無論是瀏覽網頁、分享動態、點贊、購物或者搜尋資訊，都會在背後產生資料。這些資料，根據其用途和重要性，可能會被儲存到不同的地方，其中最常見的儲存載體就是——資料庫。

不過，資料庫並非一成不變的。根據應用場景和資料特性，我們有關係型資料庫如MySQL，也有非關係型資料庫，例如Redis。比如說，當你在社交網路上點贊一條動態時，為了快速響應，可能是一個基於記憶體的資料庫如Redis首先記錄這一動作，而後臺可能會週期性地同步這些動作到持久化的儲存系統中。

那麼，當面對巨大的資料流入時，我們如何高效、穩定地將這些資料儲存到資料庫中呢？“我每次應該插入多少資料才最合適？” 這個問題，儘管看似簡單，但涉及到的策略和技術都頗為豐富。

所以，本文的目的，就是帶領大家一同探索這個話題。不論你是初涉資料庫的新手，還是有經驗的開發者，我都希望你能從這篇文章中獲得有價值的資訊。那麼，不再贅述，我們現在就開始吧！

說在開頭

在開始討論這個話提前，我們先看面試場景中的對話：

面試官: 在你之前的工作經驗中，當你們需要向資料庫中插入大量資料時，你們是如何操作的？

候選者: 噢，我們使用批次插入來最佳化效能。

面試官: 很好。那你們每次批次插入大約多少條資料？

候選者: 通常我們每次批次插入超過2000萬條資料。

面試官: 2000萬條？你確定每次都插入這麼多資料？不擔心資源過載或事務延遲等問題嗎？

候選者: 我這系統插入2000w條資料沒問題啊！不信你可以回訪我們Leader

面試官: 但是，你有沒有考慮過為什麼2000w條資料可以？2000w條資料是基於什麼方式算出來的？

候選者: 是不是資料量？

面試官: 資料量只是其中一個因素。但2000萬條資料對於不同的資料庫配置、硬體環境、甚至資料本身的複雜性來說，可能有不同的影響。只是簡單地說“我們的系統可以處理”並不足以說明問題。真正的關鍵是，你知道為什麼你的系統可以處理這麼大的資料量嗎？或者說，你們是怎麼確定2000萬是一個合適的數字的？

候選者: 呃…這個…我不太清楚，是我們之前的一位資深工程師定的。

面試官: 這就是問題所在。我們在工作中不僅要知道如何做，還要知道為什麼這麼做。只有瞭解背後的原理和策略，我們才能更好地最佳化和應對各種問題。

候選者: 明白了，我以後會注意這個問題。

面試官: 很好，對於這個問題你可以回去深入研究一下。你先回去等通知。

從上面的對話中，我們可以看到一個很現實的問題：很多人可能知道批次插入可以提高效能，但真正瞭解背後原因的卻不多。而一個優秀的工程師，應該不僅僅滿足於“這樣做可以工作”，而是要探求背後的“為什麼”。

所以，為了不讓你們變成上面的候選者，在這篇文章中，我們將深入探討資料庫插入的各種策略、技術以及背後的原理。不過在此之前，我們還是得先了解一些基礎。

資料庫插入操作的基礎知識

插入資料是資料庫操作中的基礎。但是，我們程式設計師將面臨隨之而來的問題：如何快速有效地插入資料，並保持資料庫效能？當你向資料庫中插入資料時，這些資料直接儲存到硬碟上嗎？

1.1 插入資料的原理

深入瞭解插入資料時背後發生的事情是最佳化資料庫效能的關鍵。

1.1.1 寫入快取與磁碟同步

當資料被寫入資料庫時，它首先應該被寫入快取中，而不是緩慢的磁碟中。然後後臺執行緒在適當的時間點將資料同步到磁碟上。

這樣做的主要原因有以下幾點：

速度差異： RAM（隨機存取儲存器）的速度遠遠快於磁碟。RAM對資料的讀寫幾乎是瞬時的。而磁碟，無論是傳統的機械硬碟還是現代的固態硬碟，其讀寫速度都遠慢於 RAM。
磁碟 I/O 的成本： 每次進行磁碟 I/O操作都有一定的開銷。如果資料庫頻繁地進行小批次的磁碟寫入，這會導致大量的 I/O 開銷，得不償失哇。
合併寫入： 首先將資料寫入 RAM，在資料庫可以把資料同步到磁碟之前，累積多個寫入操作。最後一次性將大量資料寫入磁碟，從而減少 I/O 操作的次數和開銷。

總結: 總的來說嘛，為了最大化效能，資料庫首先將資料寫入快取，並在適當的時間點將這些資料同步到磁碟。這種策略不僅加速了寫入操作，還有效地減少了磁碟 I/O，提高資料庫效能。

那髒頁還沒有來得及刷入到磁碟時，MySQL 宕機了，資料不就莫得了？

這我懂！InnoDB 在進行更新操作時採用了 Write Ahead Log（先寫日誌）策略。這意味著在資料被寫入磁碟之前，相關的操作會首先被記錄到 redo log 日誌中。這種策略賦予了 MySQL 在系統崩潰後的恢復能力。

1.1.2 事務日誌與資料持久化

為了確保資料的完整性，資料庫首先將插入操作寫入事務日誌。只有當資料被安全地寫入日誌後，它才被移動到實際的資料表中。

那為什麼資料庫要用“頁”來儲存資料呢？

我畫個圖，你看下

沒看出來啊，你這基礎還不錯。

謝謝，我接著往下說：

1.2 資料儲存單位：頁

作業系統為了管理物理記憶體和虛擬記憶體，使用一個稱為“頁”的結構來管理，說白了其實就是一塊固定的連續記憶體空間而已。這些頁有固定的大小，如 4KB、8KB 或 16KB。這個大小一般是塊的整數倍。

使用頁進行儲存有多種優勢，如減少磁碟I/O、高效的空間管理以及快取最佳化。瞭解你的資料庫頁的大小可以幫助你最佳化插入操作和空間管理！

那麼，單條資料插入和批次資料插入在速度和效率上有什麼不同呢？

1.3 單條資料與批次資料插入的差異

1.3.1 速度和效率比較

知識點：我們的業務系統的CUD操作，每次都要伴隨著事務開銷。如果你在應用中執行單條插入，插入了1000次資料，那麼你就有1000次事務開銷。而批次插入可以將這些資料在一個事務中插入，大大減少了總的事務開銷。

單條插入雖然簡單明瞭，但在大量資料插入時，其效能上的缺陷會逐漸顯現。與之相對，批次插入可以顯著提高效能，但它也引入了其它問題，資料的驗證和錯誤處理變得更為複雜。（魚與熊掌不可兼得）

1.3.2 對資料庫效能的影響

小貼士：批次插入可以減少磁碟I/O次數，從而提高效能。但是，如果一次插入的資料量過大，它可能會暫時阻塞其他操作，影響資料庫的響應時間。

為了達到最佳效能，您可能需要根據實際情況調整批次插入的資料量。過少的資料可能導致效能最佳化不足，而過多的資料可能導致資料庫響應時間增加。

資料庫的鎖機制和併發控制策略在插入操作中起到關鍵作用。如果多個程序或執行緒試圖同時插入資料，可能會發生鎖爭用，進而影響效能。我們又該如何最佳化這些機制進一步提高批次插入的效能呢？

如何決定合適的插入資料量？

為了實現資料庫的最大效能，確定合適的插入資料量至關重要。但這並不是一項簡單的任務，需要考慮多種因素。

很好啊，能考慮這個說明你有在思考了，那當你決定插入一大批資料時，你通常是如何選擇具體的數量的？

2.1 考慮硬體和系統資源

在考慮合適的插入資料量時，首先需要考慮的是硬體和系統的限制。

磁碟I/O:

磁碟I/O是插入資料時的主要瓶頸之一。過多的插入操作會導致磁碟I/O飽和，降低系統的響應時間。

最佳化建議：監控磁碟I/O使用情況，確保在高插入量時不超過其峰值。

記憶體使用:

大量的插入操作可能會增加RAM的使用量。如果記憶體使用接近或達到了系統限制，可能會導致效能下降，甚至導致系統崩潰。

小貼士：定期檢查系統的記憶體使用情況，確保有足夠的可用資源來處理大量的插入操作。

2.2 資料庫的內部機制

資料庫本身也有一些內部機制，這些機制在決定插入資料量時也應該考慮。

事務大小

資料庫事務的大小直接影響其效能。較大的事務可能會導致長時間的鎖定，從而影響其他查詢的效能。

小貼士：找到合適的事務大小平衡點是提高插入效能的關鍵。太小的事務可能會增加總的事務數量，而太大的事務可能會導致系統資源的飽和。

鎖策略

考慮到資料庫的鎖策略也很重要。過多的鎖爭用可能會導致效能下降。

深入探討：最佳化資料庫的鎖策略和併發控制可以進一步提高插入效能。

？你先別管事務和鎖的問題，你是透過監控這些硬體效能去調整合適的插入量，那生產怎麼辦？沒有可以估算的大小？我不是很滿意你這個回答，你思考思考再回答，我出去接個水。

這…..(拿起手機google)….

2.3 估算插入量

為了進行這個估算，我們首先要確定一條記錄的結構。假設我們有以下的記錄結構：

整型欄位 (int): 4 位元組
變長字元欄位 (varchar): 假設平均長度為 50 位元組，最大長度為 255 位元組
日期欄位 (date): 3 位元組
浮點數字段 (float): 4 位元組

基於上述的結構，一條記錄的平均大小可以估算為：

為了考慮到某些記錄可能使用 varchar 的最大長度，我們也可以計算最大記錄大小：

記憶體分析：

假設給定 8G 記憶體，並且預留 20% 的空間，我們可以使用的記憶體為：

由此，我們可以儲存的最大記錄數為：

硬碟分析：

考慮 512G 硬碟，我們可以儲存的最大記錄數為：

差不多就這樣（為自己的計算沾沾自喜中）

可以啊，功底不錯，雖然有點瑕疵（刮目相看，這輪面試差不多就讓你過了）

實際應用中的策略與建議：結合MyBatis

上面說的都是理論，你專案中一般怎麼使用批次插入的？

我想下啊，大概就這些點：

使用<foreach>標籤進行批次插入

在MyBatis的對映檔案中，通常使用<foreach>標籤來進行批次插入。

<insert id=

'insertMultiple'

parameterType=

'list'

INSERT INTO tableName (column1, column2, …)

VALUES

<foreach collection=

'list'

item=

'record'

separator=

','

(

#{record.column1}, #{record.column2}, …)

</foreach>

</insert>

ExecutorType.BATCH

Mybatis Plus也有相關的批次插入的方法。不過你也可以設定ExecutorType為BATCH來開啟批處理模式。這樣，所有的SQL語句都會被積累，直到手動提交或關閉會話。

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

使用BATCH模式時，MyBatis允許你設定一個batchSize。累積到多少數量的SQL語句時，MyBatis就會會將它們批次執行。合理設定batchSize可以避免OOM（Out of Memory）問題。一幫情況下，我們專案組就是用這個辦法，怕有些新手程式設計師批次單條插入，導致效能緩慢。