共计 3144 个字符，预计需要花费 8 分钟才能阅读完成。
摘要 ：大家提到 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 大批量写入之性能优化》，原文作者：浮尘。
点击关注，第一工夫理解华为云陈腐技术~
