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

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

總的來說MYSQL數(shù)據(jù)庫寫入性能主要受限于數(shù)據(jù)庫自身的配置，以及操作系統(tǒng)的性能，磁盤IO的性能。主要的優(yōu)化手段包括以下幾點：

1、調(diào)整數(shù)據(jù)庫參數(shù)

（1） innodb_flush_log_at_trx_commit

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

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

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

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

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

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

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

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

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

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

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

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

（2) temp_table_size,heap_table_size

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

要根據(jù)實際業(yè)務情況設置大于需要寫入的數(shù)據(jù)量占用空間大小才行。

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

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

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

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

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

（5) innodb_log_file_size,innodb_log_files_in_group,innodb_log_buffer_size

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

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

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

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

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

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

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

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

（7) innodb_thread_concurrency=16

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

（8) write_buffer_size

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

（9) innodb_buffer_pool_instance

默認為1，主要設置內(nèi)存緩沖池的個數(shù)，簡單一點來說，是控制并發(fā)讀寫innodb_buffer_pool的個數(shù)。

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

（10) bin_log

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

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

包括如下方法：

（1)：數(shù)據(jù)庫系統(tǒng)架構優(yōu)化

a：做主從復制；

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

通過這種架構優(yōu)化，分散業(yè)務系統(tǒng)的并發(fā)讀寫IO從一臺服務器到多臺服務器，同樣能提高單臺數(shù)據(jù)庫的寫入速度。

b：做讀寫分離

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

（2)：硬件優(yōu)化

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

b：采用固態(tài)硬盤SSD

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

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

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

最新評論