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

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

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

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

（1） innodb_flush_log_at_trx_commit

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

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

1：在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到日志文件，對(duì)日志文件做到磁盤操作的刷新。

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

有人會(huì)說(shuō)如果改為不是1的值會(huì)不會(huì)不安全呢？ 安全性比較如下：

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

當(dāng)innodb_flush_log_at_trx_commit和sync_binlog 都為 1 時(shí)是最安全的，在mysqld 服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binary log 只有可能丟失最多一個(gè)語(yǔ)句 或者一個(gè)事務(wù)。

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

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

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

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

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

（2) temp_table_size,heap_table_size

這兩個(gè)參數(shù)主要影響臨時(shí)表temporary table 以及內(nèi)存數(shù)據(jù)庫(kù)引擎memory engine表的寫(xiě)入，設(shè)置太小，甚至?xí)霈F(xiàn)table is full的報(bào)錯(cuò)信息.

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

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

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

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

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

讓表空間增長(zhǎng)時(shí)一次盡可能分配更多的表空間，避免在大批量寫(xiě)入時(shí)頻繁的進(jìn)行文件擴(kuò)容

（5) innodb_log_file_size,innodb_log_files_in_group,innodb_log_buffer_size

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

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

當(dāng)然，也不宜設(shè)置過(guò)大。過(guò)大會(huì)導(dǎo)致數(shù)據(jù)庫(kù)異常宕機(jī)時(shí)，數(shù)據(jù)庫(kù)重啟時(shí)會(huì)去讀取日志中未寫(xiě)入數(shù)據(jù)文件的臟數(shù)據(jù)，進(jìn)行redo，恢復(fù)數(shù)據(jù)庫(kù)，太大就會(huì)導(dǎo)致恢復(fù)的時(shí)間變的更長(zhǎng)。當(dāng)恢復(fù)時(shí)間遠(yuǎn)遠(yuǎn)超出用戶的預(yù)期接受的恢復(fù)時(shí)間，必然會(huì)引起用戶的抱怨。

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

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

而假如在寫(xiě)入數(shù)據(jù)量遠(yuǎn)大于讀的場(chǎng)景，或者說(shuō)方便隨便改動(dòng)參數(shù)的場(chǎng)景，可以針對(duì)大批量的數(shù)據(jù)導(dǎo)入，再做調(diào)整，把log_file_size調(diào)整的更大，可以達(dá)到innodb_buffer_pool_size的25%~100%。

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

設(shè)置越大的值，當(dāng)然比設(shè)置小的值的寫(xiě)入性能更好。比如上面的參數(shù)innodb_log_file_size就是參考innodb_buffer_pool_size的大小來(lái)設(shè)置的。

（7) innodb_thread_concurrency=16

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

（8) write_buffer_size

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

（9) innodb_buffer_pool_instance

默認(rèn)為1，主要設(shè)置內(nèi)存緩沖池的個(gè)數(shù)，簡(jiǎn)單一點(diǎn)來(lái)說(shuō)，是控制并發(fā)讀寫(xiě)innodb_buffer_pool的個(gè)數(shù)。

在大批量寫(xiě)入的場(chǎng)景，同樣可以調(diào)大該參數(shù)，也會(huì)帶來(lái)顯著的性能提升。

（10) bin_log

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

2、減少磁盤IO，提高磁盤讀寫(xiě)效率

包括如下方法：

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

a：做主從復(fù)制；

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

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

b：做讀寫(xiě)分離

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

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

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

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

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

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

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

最新評(píng)論