[TOC]

可以從如下幾個方面優化MySQL的插入性能。

代碼優化

values 多個

即拼接成一個insert values sql, 例如

INSERT INTO MyTable
( Column1, Column2, Column3 )
VALUES
("John", 123, "Lloyds Office"),
("Jane", 124, "Lloyds Office"),
("Billy", 125, "London Office"),
("Miranda", 126, "Bristol Office");

一個事務

開啟一個事務，批量操作完了才提交事務，而不是，操作一次就提交一次，這樣io太高，插入太慢。

插入欄位盡量少，盡量用默認值

注意事項： max_allowed_packet 默認是1M，如何insert values sql 太大需要上調這個值

關閉 unique_checks

優化效果不是很明顯，下面截圖 選自 《MySQL 資料庫開發、優化與管理維護 第2版》書籍

bulk_insert_buffer_size

這個參數只能對 MyISAM使用，innodb無效

配置優化

innodb_buffer_pool_size 緩衝區配置

什麼是 innodb_buffer_pool_size

MySQL 緩存表數據，索引數據的地方。增加它的值可以減少 磁碟 io ，提升 讀寫性能。 提升讀的原理：因為 buffer_pool_size 設置的比較大， 很多表數據和索引已緩存到 buffer pool , 要查詢的數據在緩存中找到了，就不需要訪問磁碟了。讀性能就得到了提升。 提升寫的原理：因為 buffer_pool_size 設置的比較大， 寫的數據，暫時以臟頁的方式放在內存，然後慢慢落到磁碟，如果buffer_pool_size 太小就沒辦法緩存寫操作，寫一次訪問一次磁碟 ，寫入性能就比較慢。（實際自測增大buffer_pool_size後，並未帶來純寫操作的性能提升， 這塊有待進一步研究）

設置多大的 innodb_buffer_pool_size 合適？

通常將innodb_buffer_pool_size其配置為物理內存的50％到75％

相關參數設置

innodb_buffer_pool_instances

一般將 innodb_buffer_pool_size 值增大後，需要增加配置 innodb_buffer_pool_instances 的值。

innodb_buffer_pool_instances 是 buffer_pool 實例數量，默認為1。增加它的值，可以減少資料庫內部的資源競爭，增加並發處理能力。

如何設置innodb_buffer_pool_instances？ innodb_buffer_pool_instances 的範圍是 1 (the default) up to 64 (the maximum). 可以將 innodb_buffer_pool_instances 的個數設置為 buffer pool size 的 十分之一， 比如 innodb_buffer_pool_size 是 30g ，那 innodb_buffer_pool_instances 就設置為 3；

innodb_buffer_pool_size 注意事項

因為有額外內存的使用，如果指定 innodb_buffer_pool_size 為 12g 實際佔用內存可能是 14g +

事務日誌配置

innodb_log_file_size

默認值 48MB 設置的太小：比如用默認值48MB，當一個日誌文件寫滿後，innodb會自動切換到另外一個日誌文件，而且會觸發資料庫的檢查點（Checkpoint），這會導致innodb緩存臟頁的小批量刷新，會明顯降低innodb的性能。

設置的太大：設置很大以後減少了checkpoint，並且由於redo log是順序I/O，大大提高了I/O性能。但是如果資料庫意外出現了問題，比如意外宕機，那麼需要重放日誌並且恢復已經提交的事務，如果日誌很大，那麼將會導致恢復時間很長。甚至到我們不能接受的程度。

總結： innodb_log_file_size設置得太小無法釋放資料庫性能，設置得太大，會增加宕機後日誌重放恢復的時間。

innodb_log_files_in_group

重做日誌組中的日誌數量，默認值是2 ，一般用默認值也可以。

innodb_log_buffer_size

將日誌寫入磁碟日誌文件前的緩衝大小，默認值 8MB，一般用默認值也可以。

讀寫線程增加

合理增加 innodb_write_io_threads，innodb_read_io_threads 兩個配置的值即可。

實踐比較

環境：centos 7， MySQL 6.7 ， 8G，i5 操作：jmeter 64個並發插入數據，每個並發插入320條數據，每條數據插入前都會隨機查詢一次資料庫。

優化前的配置

innodb_buffer_pool_size = 134217728 //128MB
innodb_buffer_pool_instances = 1

優化後的配置

innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1

性能結果 buffer_pool_size 128MB：耗時58s buffer_pool_size 1GB：耗時172s

結論

buffer pool 緩衝區增加8倍內存， 換來 3 倍 讀性能提升，3 倍 含讀的寫性能提升（含讀的寫，指的是在insert 前，進行了資料庫查詢，將查得的數據賦值給了 insert 欄位）， 對於純寫幾乎沒有性能提升。同理，如果緩衝區增加5.3倍內存，理論可以提升2倍性能提升。

硬體優化

最影響資料庫性能的是磁碟 io，上 ssd 可以大大提升性能，其次是 cpu，內存 。

架構優化

讀寫分離，降低單機io的壓力。

作者：蔣先生66

原文：https://my.oschina.net/anuodog/blog/3002941

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！