優化了MYSQL大量寫入問題，老板獎勵了1000塊給我

摘要：大家提到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大批量寫入之性能優化》，原文作者： 浮塵 ，

點擊關注，第一時間了解華為云新鮮技術~