MYSQL大量写入问题优化详解-木庄网络博客

本文整理自网络，侵删。

摘要：大家提到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操作，因此该模式也是最慢的一种方式。

针对同一个表通过c#代码按照系统业务流程进行批量插入，性能比较如下所示：

结论：设置为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以及切换日志，就会导致大批量写入数据性能的降低。

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。