MySQL优化之提高插入效率（全局关闭autocommit）

写在前面

近来做mysql的导入时，发现插入速度极慢。5W多条数据要花半个小时，难以忍受。在网上查阅资料，总结出来这个方法。 即 关闭Mysql的自动提交 因为我是qt+mysql 配合使用，所以需要全局关闭。关闭后插入5W多条数据只需要29S

---

MySQL默认的数据提交操作模式是自动提交模式（autocommit）。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。

Mysql的InnoDB存储引擎支持事务处理。InnoDB表引擎下关闭mysql自动事务提交可以大大提高数据插入的效率，这是因为如果需要插入1000条数据，mysql会自动发起（提交）1000次的数据写入请求，如果把autocommit关闭掉，通过程序来控制，只要一次commit就可以搞定，这是会大大提高速率。关闭后如下图

所以说应该将存储引擎改为InnoDB，具体方法不做讲述。
首先查询当前数据库事务提交方式的命令为：
show variables like "autocommit"
查询结果如下（如果是off 就可以关闭这篇文章了）
。

下面说一下具体操作方法

• 更改配置文件
  在MySQL的安装根目录下 找到my.ini .使用记事本打开，在[mysqld] 后面加入**init_connect=‘SET autocommit=off’ ** 如果没有my.ini 请新建一个记事本 把图片的内容输入进去，并更改名字。
  
  
• 重新启动mysql服务
  打开管理员模式的cmd输入

net stop MySQL

net start MySQL

• 创建子权限用户
  这一步是因为 当我们以Super权限(root)登录mysql数据库服务时,show variables like '%autocommit%'查得结果还是:autocommit ON,这是因init_connect(手册中有对其解释)以Super用户登录是不被加载的，这是mysql出于安全考虑，而后用普通用户登录，show variables like ‘%autocommit%’ 显示的结果如下:autocommit OFF。

  所以我们就登陆root 创建一个用户 授予他你想操作数据库的权限。使用该用户登陆MySQL即可。

mysql -uroot -p   //登陆root账户 输入完毕后按两下回车
CREATE USER 'tomato'@'localhost' IDENTIFIED BY '123456'; //三个单引号内容 用户名 端口号 密码
GRANT [ALL] ON [cg].[*] TO ‘tomato’@’localhost’; // 实际输入没有[] 为了方便解释才采用
// 第一个为权限名 all是所有权限  第二个是数据库名  第三个 *代表cg数据库的所有表  

---

避免长事务

将autocommit设置为0 很容易导致长事务的产生。
而长事务会存在很多潜在风险，且会占用磁盘资源。

mysql的事务启动方式有以下几种：

1. 显式启动事务语句，begin start transaction 配套的commit 回滚语句roll back。
2. set autocommit=0.这个命令会将这个线程的自动提交关闭。意味着执行一个select语句的时候，事务就启动了，并且不自动提交。知道主动执行commit或者rollback，或者断开连接。
   使用第二种方式会导致长事务。
   所以建议显式指定begin和commit。

另外 begin 后 输入 commit work and chain 会提交当前事务，开启下一次事务。

可以通过

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

查询持续时间超过60s的事务。

---

到此为止我们就大功告成了，去试试此时插入的速度把。
还有一个记录了mysql优化插入的博客

https://blog.csdn.net/u013707844/article/details/24694823

参考文章：https://blog.csdn.net/rl529014/article/details/79451381
用了人家的图哈哈哈！