优化了 MYSQL 大量写入问题，老板奖励了 1000 块给我

摘要：大家提到 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 大批量写入之性能优化》，原文作者： 浮尘 。

点击关注，第一时间了解华为云新鲜技术~