百G级别sql文件导入参数优化_GZHarryAnonymous的博客-CSDN博客

创建你的数据库

CREATE DATABASE yourDataBaseName;
1

配置MySql日志何时写入硬盘的参数

• innodb_flush_log_at_trx_commit = 0 log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。 当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
• innodb_flush_log_at_trx_commit = 1 每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。 当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。
• innodb_flush_log_at_trx_commit = 2 每次事务提交时mysql都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。 当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

show variables like "innodb_flush_log_at_trx_commit";
set GLOBAL innodb_flush_log_at_trx_commit=OFF;
show variables like "innodb_flush_log_at_trx_commit";
123

InnoDB表引擎下关闭mysql自动事务提交

• Mysql的InnoDB存储引擎支持事务处理。MySQL默认的数据提交操作模式是自动提交模式（autocommit），除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。InnoDB表引擎下关闭mysql自动事务提交可以大大提高数据插入的效率。
• 如果需要插入1000条数据，默认情况下，mysql会自动发起（提交）1000次的数据写入请求。如果把autocommit关闭掉，通过程序来控制，只要一次commit就可以搞定，会大大提高速率。

show variables like "autocommit";
set autocommit=OFF;
show variables like "autocommit";
123

Server接受的数据包大小

• MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。

show variables like "max_allowed_packet";
set GLOBAL max_allowed_packet=4*1024*1024*10;
show variables like "max_allowed_packet";
123

动态关闭当前会话日志记录

• 在 mysql 启动时，通过命令行或配置文件决定是否开启 binlog，而 log_bin 变量仅仅是报告当前 binlog 系统的状态（打开与否）。若要关闭 binlog，可以通过修改 sql_log_bin ，也可以修改 log_bin，然后重启 mysql，后者更彻底，缺点就是需要重启。
• sql_log_bin 是一个动态变量，修改该变量时，可以只对当前会话（Session）生效，也可以是全局的（Global）。当全局修改该变量的时候，只会对新的会话生效 （意味着对当前会话不会生效），因此一般全局修改该变量后，都要把原来的所有连接 kill 掉。
• 当原数据库的时候，如果不关闭二进制日志，那么还原的过程仍然会记录在二进制日志里面，不仅浪费资源，还增加了磁盘的容量，也没有必要（特别是利用二进制还原数据库的时候）所以一般还原的时候会选择关闭二进制日志，可以通过修改配置文件，重启关闭二进制日志。也可以动态命令关闭sql_log_bin，然后导入数据库。

show variables like "sql_log_bin";
set sql_log_bin=OFF;
show variables like "sql_log_bin";
123

缓冲池字节大小

• MyISAM使用操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。
• innodb_buffer_pool_size参数表示缓冲池字节大小，InnoDB缓存表和索引数据的内存区域。mysql默认的值是128M。
• 专用mysql服务器设置的大小： 操作系统内存的70%-80%最佳。

SELECT @@innodb_buffer_pool_size;
SET GLOBAL innodb_buffer_pool_size=5*1024*1024*1024;
SELECT @@innodb_buffer_pool_size;
123

CPU多核的利用

show variables like "INNODB_WRITE_IO_THREADS";
show variables like "innodb_read_io_threads";