摘要：大家提到Mysql的性能優化都是注重于優化sql以及索引來提升查詢性能，大多數產品或者網站面臨的更多的高并發數據讀取問題。然而在大量寫入數據場景該如何優化呢？

今天這里主要給大家介紹，在有大量寫入的場景，進行優化的方案。

總的來說MYSQL數據庫寫入性能主要受限于數據庫自身的配置，以及操作系統的性能，磁盤IO的性能。主要的優化手段包括以下幾點：

1、調整數據庫參數

（1） innodb_flush_log_at_trx_commit

默認為1，這是數據庫的事務提交設置參數，可選值如下：

0: 日志緩沖每秒一次地被寫到日志文件，并且對日志文件做到磁盤操作的刷新，但是在一個事務提交不做任何操作。

1：在每個事務提交時，日志緩沖被寫到日志文件，對日志文件做到磁盤操作的刷新。

2：在每個提交，日志緩沖被寫到文件，但不對日志文件做到磁盤操作的刷新。對日志文件每秒刷新一次。

有人會說如果改為不是1的值會不會不安全呢？ 安全性比較如下：

在 mysql 的手冊中，為了確保事務的持久性和一致性，都是建議將這個參數設置為 1 。出廠默認值是 1，也是最安全的設置。

當innodb_flush_log_at_trx_commit和sync_binlog 都為 1 時是最安全的，在mysqld 服務崩潰或者服務器主機crash的情況下，binary log 只有可能丟失最多一個語句 或者一個事務。

但是這種情況下，會導致頻繁的io操作，因此該模式也是最慢的一種方式。

• 當innodb_flush_log_at_trx_commit設置為0，mysqld進程的崩潰會導致上一秒鐘所有事務數據的丟失。
• 當innodb_flush_log_at_trx_commit設置為2，只有在操作系統崩潰或者系統掉電的情況下，上一秒鐘所有事務數據才可能丟失。

針對同一個表通過c#代碼按照系統業務流程進行批量插入，性能比較如下所示：

• （a.相同條件下：innodb_flush_log_at_trx_commit=0，插入50W行數據所花時間25.08秒;
• （b.相同條件下：innodb_flush_log_at_trx_commit=1，插入50W行數據所花時間17分21.91秒;
• （c.相同條件下：innodb_flush_log_at_trx_commit=2，插入50W行數據所花時間1分0.35秒。

結論：設置為0的情況下，數據寫入是最快的，能迅速提升數據庫的寫入性能， 但有可能丟失上1秒的數據。

（2) temp_table_size,heap_table_size

這兩個參數主要影響臨時表temporary table 以及內存數據庫引擎memory engine表的寫入，設置太小，甚至會出現table is full的報錯信息.

要根據實際業務情況設置大于需要寫入的數據量占用空間大小才行。

（3) max_allowed_packet=256M,net_buffer_length=16M，set autocommit=0

備份和恢復時如果設置好這三個參數,可以讓你的備份恢復速度飛起來哦！

（4) innodb_data_file_path=ibdata1:1G;ibdata2:64M:autoextend

很顯然表空間后面的autoextend就是讓表空間自動擴展，不夠默認情況下只有10M，而在大批量數據寫入的場景，不妨把這個參數調大；

讓表空間增長時一次盡可能分配更多的表空間，避免在大批量寫入時頻繁的進行文件擴容

（5) innodb_log_file_size,innodb_log_files_in_group,innodb_log_buffer_size

設置事務日志的大小，日志組數，以及日志緩存。默認值很小，innodb_log_file_size默認值才幾十M，innodb_log_files_in_group默認為2。

然而在innodb中，數據通常都是先寫緩存，再寫事務日志，再寫入數據文件。設置太小，在大批量數據寫入的場景，必然會導致頻繁的觸發數據庫的檢查點，去把 日志中的數據寫入磁盤數據文件。頻繁的刷新buffer以及切換日志，就會導致大批量寫入數據性能的降低。

當然，也不宜設置過大。過大會導致數據庫異常宕機時，數據庫重啟時會去讀取日志中未寫入數據文件的臟數據，進行redo，恢復數據庫，太大就會導致恢復的時間變的更長。當恢復時間遠遠超出用戶的預期接受的恢復時間，必然會引起用戶的抱怨。

這方面的設置倒可以參考華為云的數據庫默認設置,在華為云2核4G的環境，貌似默認配置的buffer:16M,log_file_size:1G----差不多按照mysql官方建議達到總內存的25%了；而日志組files_in_group則設置為4組。

2核4G這么低的硬件配置，由于參數設置的合理性，已經能抗住每秒數千次，每分鐘8萬多次的讀寫請求了。

而假如在寫入數據量遠大于讀的場景，或者說方便隨便改動參數的場景，可以針對大批量的數據導入，再做調整，把log_file_size調整的更大，可以達到innodb_buffer_pool_size的25%~100%。

（6) innodb_buffer_pool_size設置MySQL Innodb的可用緩存大小。理論上最大可以設置為服務器總內存的80%.

設置越大的值，當然比設置小的值的寫入性能更好。比如上面的參數innodb_log_file_size就是參考innodb_buffer_pool_size的大小來設置的。

（7) innodb_thread_concurrency=16

故名思意，控制并發線程數，理論上線程數越多當然會寫入越快。當然也不能設置過大官方建議是CPU核數的兩倍左右最合適。

（8) write_buffer_size

控制單個會話單次寫入的緩存大小，默認值4K左右，一般可以不用調整。然而在頻繁大批量寫入場景，可以嘗試調整為2M，你會發現寫入速度會有一定的提升。

（9) innodb_buffer_pool_instance

默認為1，主要設置內存緩沖池的個數，簡單一點來說，是控制并發讀寫innodb_buffer_pool的個數。

在大批量寫入的場景，同樣可以調大該參數，也會帶來顯著的性能提升。

（10) bin_log

二進制日志，通常會記錄數據庫的所有增刪改操作。然而在大量導數據，比如數據庫還原的時候不妨臨時關閉bin_log,關掉對二進制日志的寫入，讓數據只寫入數據文件，迅速完成數據恢復，完了再開啟吧。

2、減少磁盤IO，提高磁盤讀寫效率

包括如下方法：

（1)：數據庫系統架構優化

a：做主從復制；

比如部署一個雙主從，雙主從模式部署是為了相互備份，能保證數據安全，不同的業務系統連接不同的數據庫服務器，結合ngnix或者keepalive自動切換的功能實現負載均衡以及故障時自動切換。

通過這種架構優化，分散業務系統的并發讀寫IO從一臺服務器到多臺服務器，同樣能提高單臺數據庫的寫入速度。

b：做讀寫分離

和1中要考慮的問題一樣，可以減輕單臺服務器的磁盤IO，還可以把在服務器上的備份操作移到備服務器，減輕主服務器的IO壓力，從而提升寫入性能。

（2)：硬件優化

a: 在資源有限的情況下，安裝部署的時候，操作系統中應有多個磁盤，把應用程序，數據庫文件，日志文件等分散到不同的磁盤存儲，減輕每個磁盤的IO，從而提升單個磁盤的寫入性能。

b：采用固態硬盤SSD

如果資源足夠可以采用SSD存儲，SSD具有高速寫入的特性，同樣也能顯著提升所有的磁盤IO操作。

當然還有更多的硬件或者軟件優化方法，這里就不一一列舉了。

到此這篇關于MYSQL大量寫入問題優化詳解的文章就介紹到這了,更多相關MYSQL大量寫入內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！