优化恢复数据速度

概述

使用默认配置的情况，使用 navicat 恢复 mysql5.7 的数据库速度非常慢，按照本文的介绍优化参数后可以提升速度

方案

mysql

调整 mysql 的参数

配置参数	推荐调整值	作用与说明
innodb_buffer_pool_size	物理内存的 50% - 80%	InnoDB数据和索引的缓存区，这是最核心的参数。设置越大，读写数据越快。
innodb_log_file_size	1GB - 2GB	InnoDB的日志文件大小。适当增大可减少日志文件的切换频率，提升写入性能。
max_allowed_packet	256M 或 512M	限制MySQL接收数据包的大小。恢复大SQL文件时，至少设为256M以防中断。
innodb_flush_log_at_trx_commit	0 或 2	临时设置为0或2，避免每次事务提交都刷盘，可极大提升写入速度。恢复完成后务必改回默认值1以确保数据安全。
sync_binlog	0 或 1000	控制二进制日志同步磁盘的频率。临时调大可以减少磁盘I/O。同样建议恢复后还原为默认值1。

优化sql脚本

导入前的SQL准备（在SQL文件开头执行）

在恢复的SQL文件最开头，加入以下命令，让MySQL在导入时跳过一些非必要检查，可以显著提速：

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET autocommit=0;                 -- 关闭自动提交，改为手动提交
SET unique_checks=0;              -- 关闭唯一键检查
SET foreign_key_checks=0;         -- 关闭外键检查

在文件末尾，再通过COMMIT;一次性提交所有数据，并将参数恢复：

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;
SET autocommit=1;