Windows下关闭MySQL的自动提交 autocommit 性能

发布时间：2022-06-24 14:43 所属栏目：115 来源：互联网

导读：随着MySQL的应用日益广泛，支持事务特性的InnoDB已经成为MySQL的默认存储引擎。和很多关系数据库不同的是，在InnoDB存储引擎中，事务默认是自动提交的，也就是说每条DML语句都会触发commit操作。这一自动提交(autocommit)特性在很多场景下对于性能还是有一定

　　随着MySQL的应用日益广泛，支持事务特性的InnoDB已经成为MySQL的默认存储引擎。和很多关系数据库不同的是，在InnoDB存储引擎中，事务默认是自动提交的，也就是说每条DML语句都会触发commit操作。这一自动提交(autocommit)特性在很多场景下对于性能还是有一定影响的。

　　最近我们尝试将一个Oracle数据库的数据迁移到MySQL数据库，发现导入时间很长。举个例子来说，其中有一个表包含四千多条数据，插入时间竟然超过了100秒。每插入一条数据，数据库就会自动提交一次，也就是说单这一个表MySQL会commit超过4000次，如果我们关闭自动提交功能，通过程序来控制，只要一次commit就可以了。

　　那么，如何关闭MySQL的autocommit特性呢？
　　通常有两种方法：
　　一种是通过set命令修改会话级别或者数据库级别的参数，但是数据库重启后参数会恢复默认值；
　　第二种方法是修改mysql的配置文件my.ini，一劳永逸。

　　1.测试环境 Windows Server 2008 r2+MySQL Community Server (GPL) 5.7.16
　　我是在Windows Server 2008 r2环境下进行测试。
　　点击(此处)折叠或打开

　　mysql> status
　　--------------
　　mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)

　　Connection id: 2
　　Current database:
　　Current user: root@localhost
　　SSL: Not in use
　　Using delimiter: ;
　　Server version: 5.7.16 MySQL Community Server (GPL)
　　Protocol version: 10
　　Connection: localhost via TCP/IP
　　Server characterset: latin1
　　Db characterset: latin1
　　Client characterset: utf8
　　Conn. characterset: utf8
　　TCP port: 3306
　　Uptime: 9 min 5 sec

　　Threads: 1 Questions: 7 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.012
　　--------------

　　mysql>

　　2.通过set来关闭autocommit，重启后恢复默认值
　　首先，我们通过set命令来修改autocommit参数。
　　点击(此处)折叠或打开

　　mysql>
　　mysql> show global variables like '%commit%';
　　+-----------------------------------------+-------+
　　| Variable_name | Value |
　　+-----------------------------------------+-------+
　　| autocommit | ON |
　　| binlog_group_commit_sync_delay | 0 |
　　| binlog_group_commit_sync_no_delay_count | 0 |
　　| binlog_order_commits | ON |
　　| innodb_api_bk_commit_interval | 5 |
　　| innodb_commit_concurrency | 0 |
　　| innodb_flush_log_at_trx_commit | 1 |
　　| slave_preserve_commit_order | OFF |
　　+-----------------------------------------+-------+
　　8 rows in set, 1 warning (0.00 sec)

　　mysql> set autocommit=0;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql>
　　mysql> set global autocommit=0;
　　Query OK, 0 rows affected (0.00 sec)

　　mysql> show global variables like '%commit%';
　　+-----------------------------------------+-------+
　　| Variable_name | Value |
　　+-----------------------------------------+-------+
　　| autocommit | OFF |
　　| binlog_group_commit_sync_delay | 0 |
　　| binlog_group_commit_sync_no_delay_count | 0 |
　　| binlog_order_commits | ON |
　　| innodb_api_bk_commit_interval | 5 |
　　| innodb_commit_concurrency | 0 |
　　| innodb_flush_log_at_trx_commit | 1 |
　　| slave_preserve_commit_order | OFF |
　　+-----------------------------------------+-------+
　　8 rows in set, 1 warning (0.02 sec)

　　mysql>

　　接下来，我们重启数据库，发现autocommit参数已经恢复默认值。
　　点击(此处)折叠或打开

　　E:\mysql-5.7.16-winx64\bin>net stop mysql
　　MySQL 服务正在停止.
　　MySQL 服务已成功停止。

　　E:\mysql-5.7.16-winx64\bin>net start mysql
　　MySQL 服务正在启动 .
　　MySQL 服务已经启动成功。

　　E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
　　mysql: [Warning] Using a password on the command line interface can be insecure.
　　Welcome to the MySQL monitor. Commands end with ; or \g.
　　Your MySQL connection id is 2
　　Server version: 5.7.16 MySQL Community Server (GPL)

　　Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

　　Oracle is a registered trademark of Oracle Corporation and/or its
　　affiliates. Other names may be trademarks of their respective
　　owners.

　　Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

　　mysql>
　　mysql> show global variables like '%commit%';
　　+-----------------------------------------+-------+
　　| Variable_name | Value |
　　+-----------------------------------------+-------+
　　| autocommit | ON |
　　| binlog_group_commit_sync_delay | 0 |
　　| binlog_group_commit_sync_no_delay_count | 0 |
　　| binlog_order_commits | ON |
　　| innodb_api_bk_commit_interval | 5 |
　　| innodb_commit_concurrency | 0 |
　　| innodb_flush_log_at_trx_commit | 1 |
　　| slave_preserve_commit_order | OFF |
　　+-----------------------------------------+-------+
　　8 rows in set, 1 warning (0.00 sec)

　　mysql>
　　3.修改mysql的配置文件my.ini
　　我们找到mysql的配置文件my.ini，在里面添加一行记录“autocommit=0”。
　　点击(此处)折叠或打开

　　[mysql]
　　default-character-set=utf8
　　[mysqld]
　　max_connections=200
　　default-storage-engine=INNODB
　　basedir =E:\mysql-5.7.16-winx64\bin
　　datadir =E:\mysql-5.7.16-winx64\data
　　port = 3306
　　autocommit=0

　　然后重新启动数据库，确认autocommit参数是否为OFF。
　　点击(此处)折叠或打开

　　E:\mysql-5.7.16-winx64\bin>net stop mysql
　　MySQL 服务正在停止.
　　MySQL 服务已成功停止。

　　E:\mysql-5.7.16-winx64\bin>net start mysql
　　MySQL 服务正在启动 .
　　MySQL 服务已经启动成功。

　　E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
　　mysql: [Warning] Using a password on the command line interface can be insecure.
　　Welcome to the MySQL monitor. Commands end with ; or \g.
　　Your MySQL connection id is 2
　　Server version: 5.7.16 MySQL Community Server (GPL)

　　Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

　　Oracle is a registered trademark of Oracle Corporation and/or its
　　affiliates. Other names may be trademarks of their respective
　　owners.

　　Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

　　mysql> show global variables like '%commit%';
　　+-----------------------------------------+-------+
　　| Variable_name | Value |
　　+-----------------------------------------+-------+
　　| autocommit | OFF |
　　| binlog_group_commit_sync_delay | 0 |
　　| binlog_group_commit_sync_no_delay_count | 0 |
　　| binlog_order_commits | ON |
　　| innodb_api_bk_commit_interval | 5 |
　　| innodb_commit_concurrency | 0 |
　　| innodb_flush_log_at_trx_commit | 1 |
　　| slave_preserve_commit_order | OFF |
　　+-----------------------------------------+-------+
　　8 rows in set, 1 warning (0.01 sec)

　　mysql>
　　mysql>
　　我们看到autocommit参数为OFF，目标达成。

　　4.数据插入速度提升十倍
　　以开头提到的那张表为例，4000多条数据，在关闭autocommit参数之前插入时间为101505ms；关闭autocommit参数之后插入时间为8869ms，插入速度提升了十倍还多。

　　但是这个时间其实还是蛮长的，继续提升！

（编辑：ASP站长网）