由于業(yè)務(wù)原因册踩，遇到了如題所述的業(yè)務(wù)問題，事務(wù)執(zhí)行時間在30s~50s 不等扒接，效果非常不理想
方案1. jdbc批處理
5w+ 數(shù)據(jù)測試恕齐，分別使用了mybatis insert()()（拼接xml）, mybatis的批處理和 jdbc的批處理。
可以看到在jdbc執(zhí)行時間方面是差不多的剃斧，但是在方法執(zhí)行時間上杂拨，批處理要稍微快了一些，但是還是不理想
方案2. 優(yōu)化MySQL 參數(shù)
修改 my.ini
- innodb_buffer_pool_size :
InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
row data. The bigger you set this the less disk I/O is needed to
access data in tables. On a dedicated database server you may set this
parameter up to 80% of the machine physical memory size. Do not set it
too large, though, because competition of the physical memory may
cause paging in the operating system. Note that on 32bit systems you
might be limited to 2-3.5G of user level memory per process, so do not
set it too high.
Innodb的緩沖池會緩存數(shù)據(jù)和索引悯衬，設(shè)置的越大訪問表中的數(shù)據(jù)所需的磁盤I/O就越少。
修改innodb_buffer_pool_size = 512M測試一下效率，這速度簡直感人筋粗！
- innodb_log_buffer_size :
The size of the buffer InnoDB uses for buffering log data. As soon as
it is full, InnoDB will have to flush it to disk. As it is flushed once per second anyway, it does not make sense to have it very large
(even with long transactions).
表示InnoDB寫入到磁盤上的日志文件時使用的緩沖區(qū)的字節(jié)數(shù)策橘，默認(rèn)值為8M。當(dāng)緩沖區(qū)充滿時娜亿，InnoDB將刷新數(shù)據(jù)到磁盤丽已。由于它每秒刷新一次，所以將它設(shè)置得非常大是沒有意義的 (即使是長事務(wù))买决。
- innodb_log_file_size :
Size of each log file in a log group. You should set the combined size
of log files to about 25%-100% of your buffer pool size to avoid
unneeded buffer pool flush activity on log file overwrite. However,
note that a larger logfile size will increase the time needed for the
recovery process.
該值越大沛婴，緩沖池中必要的檢查點(diǎn)刷新活動就會越少，節(jié)省磁盤I/ O督赤。但是越大的日志文件嘁灯，mysql的崩潰恢復(fù)就越慢
- 設(shè)置上述兩個參數(shù)
innodb_log_file_size=64Minnodb_log_buffer_size=16M，效率提升的并不明顯躲舌。
總結(jié)
- 數(shù)據(jù)量大時丑婿，批處理在方法執(zhí)行時間上要比 mybatis xml拼接快一點(diǎn) （批處理只編譯一條SQL，而拼接的方式SQL會很長）
- 性能瓶頸優(yōu)化還是要從數(shù)據(jù)庫下手没卸，目前來看MySQL 大數(shù)據(jù)量時很依賴 innodb_buffer_pool_size （緩沖池）
