数据导入优化

一、一条insert语句插入多行数据

插入数据的时间分布

连接                                30%
向服务器发送查询          20%

解析查询                        20%

插入行                            大概10%

插入索引                        大概10%

结束                                10%

可以看出大多数时间消耗在客户端和服务端通信的时间上

因此当执行大批量数据时，一条sql插入多行数据会远快于，一条sql插入一行数据 

二、关闭自动提交

show GLOBAL VARIABLES LIKE "autocommit"

可以看到MySQL是默认开启的自动提交 

多条sql插入的情况下，大多数时间花在客户端和服务器通信上，关闭自动提交，在结尾一次性提交可以介绍客户端和服务器通信的时间

合并提交 可以在一定程度上减少数据漏盘（数据丢失或未正确写入磁盘）的次数

当然也要预估一下数据，如果是数据行过多的话，也是要拆分成多个sql，否则就会导致一个大事务

三、参数调整

innodb_flush_log_at_trx_commit

show GLOBAL VARIABLES LIKE "innodb_flush_log_at_trx_commit"

innodb_flush_log_at_trx_commit是 MySQL 中一个非常重要的参数，用于控制 InnoDB 存储引擎在事务提交时如何处理日志（Redo Log）的刷新行为。这个参数的设置直接影响到数据库的性能和数据安全性。它的值可以设置为 0、1 或 2

1. 设置为 1（默认值）最安全，但是性能最差

• 行为：每次事务提交时，InnoDB 都会将日志缓冲区中的数据刷新到磁盘（即调用 fsync）。

• 优点：

  • 数据安全性最高：即使系统崩溃，也不会丢失任何已提交事务的数据。

• 缺点：

  • 性能较低：每次提交都会触发磁盘 I/O，增加了事务提交的延迟，尤其是在机械硬盘上。

2. 设置为 0  最快的，但是科可能会丢数据

• 行为：日志缓冲区的内容每秒刷新到磁盘一次，事务提交时不会触发 fsync。

• 优点：

  • 性能最高：减少了磁盘 I/O 操作，事务提交速度更快。

• 缺点：

  • 数据安全性较低：如果系统崩溃（如断电或进程崩溃），可能会丢失最近一秒内的事务数据。

  • 不推荐用于生产环境，除非对数据丢失的容忍度较高。

3. 设置为 2    

• 行为：事务提交时，日志缓冲区的内容会被写入到操作系统的文件系统缓冲区（但不会立即刷新到磁盘），操作系统会定期刷新缓冲区到磁盘。

• 优点：

  • 性能较好：减少了磁盘 I/O 操作，同时比设置为 0 更安全。

• 缺点：

  • 数据安全性中等：如果系统崩溃，可能会丢失最近一次操作系统刷新之前的数据（通常在几秒内）。

sync_binlog
sync_binlog 是 MySQL 中用于控制二进制日志（binlog）同步到磁盘行为的重要参数，其设置直接影响数据库的性能和数据安全性

sync_binlog 参数定义了 MySQL 服务器将 binlog 内容从内存缓冲区同步到磁盘的频率，其取值可以是 0、1 或大于 1 的整数：

• sync_binlog=0：MySQL 不会主动将 binlog 内容同步到磁盘，完全依赖操作系统的文件系统缓存和刷新机制。这种方式性能最好，但存在数据丢失风险，尤其是在系统崩溃时。

• sync_binlog=1：每次事务提交时，MySQL 都会将 binlog 内容同步到磁盘。这种方式提供了最高的数据安全性，但可能会对性能产生一定影响，因为磁盘 I/O 操作相对较慢。

• sync_binlog=N（N>1）：每 N 个事务提交后，MySQL 才会将 binlog 内容同步到磁盘。这种方式是前两者的折中，既考虑了一定的性能，也兼顾了数据的安全性。