MYSQL⼤量写⼊问题优化详解
摘要：⼤家提到Mysql的性能优化都是注重于优化sql以及索引来提升查询性能，⼤多数产品或者⽹站⾯临的更多的⾼并发数据读取问题。然⽽在⼤量写⼊数据场景该如何优化呢？
今天这⾥主要给⼤家介绍，在有⼤量写⼊的场景，进⾏优化的⽅案。
总的来说MYSQL数据库写⼊性能主要受限于数据库⾃⾝的配置，以及操作系统的性能，磁盘IO的性能。主要的优化⼿段包括以下⼏点：mysql面试题acid
1、调整数据库参数
（1） innodb_flush_log_at_trx_commit
默认为1，这是数据库的事务提交设置参数，可选值如下：
0: ⽇志缓冲每秒⼀次地被写到⽇志⽂件，并且对⽇志⽂件做到磁盘操作的刷新，但是在⼀个事务提交不做任何操作。
1：在每个事务提交时，⽇志缓冲被写到⽇志⽂件，对⽇志⽂件做到磁盘操作的刷新。
tokenization什么意思
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:1
6M,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
access数据库图书管理系统下载控制单个会话单次写⼊的缓存⼤⼩，默认值4K左右，⼀般可以不⽤调整。然⽽在频繁⼤批量写⼊场景，可以尝试调整为2M，你会发现写⼊速度会有⼀定的提升。
（9) innodb_buffer_pool_instance
textbox滚动条位置在最下面默认为1，主要设置内存缓冲池的个数，简单⼀点来说，是控制并发读写innodb_buffer_pool的个数。
在⼤批量写⼊的场景，同样可以调⼤该参数，也会带来显著的性能提升。
（10) bin_log
⼆进制⽇志，通常会记录数据库的所有增删改操作。然⽽在⼤量导数据，⽐如数据库还原的时候不妨临时关闭bin_log，关掉对⼆进制⽇志的写⼊，让数据只写⼊数据⽂件，迅速完成数据恢复，完了再开启吧。
2、减少磁盘IO，提⾼磁盘读写效率
包括如下⽅法：
（1)：数据库系统架构优化
含数字的组词a：做主从复制；
⽐如部署⼀个双主从，双主从模式部署是为了相互备份，能保证数据安全，不同的业务系统连接不同的数据库服务器，结合ngnix或者keepalive⾃动切换的功能实现负载均衡以及故障时⾃动切换。
通过这种架构优化，分散业务系统的并发读写IO从⼀台服务器到多台服务器，同样能提⾼单台数据库的写⼊速度。
b：做读写分离
c语言函数调用的实现和1中要考虑的问题⼀样，可以减轻单台服务器的磁盘IO，还可以把在服务器上的备份操作移到备服务器，减轻主服务器的IO 压⼒，从⽽提升写⼊性能。
（2)：硬件优化
a: 在资源有限的情况下，安装部署的时候，操作系统中应有多个磁盘，把应⽤程序，数据库⽂件，⽇志⽂件等分散到不同的磁盘存储，减轻每个磁盘的IO，从⽽提升单个磁盘的写⼊性能。
b：采⽤固态硬盘SSD
如果资源⾜够可以采⽤SSD存储，SSD具有⾼速写⼊的特性，同样也能显著提升所有的磁盘IO操作。
当然还有更多的硬件或者软件优化⽅法，这⾥就不⼀⼀列举了。
到此这篇关于MYSQL⼤量写⼊问题优化详解的⽂章就介绍到这了，更多相关MYSQL⼤量写⼊内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。