隨著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環境下進行測試。

點選(此處)摺疊或開啟

1. mysql> status
   
2. --------------
   
3. mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)
   
4. 
   
5. Connection id: 2
   
6. Current database:
   
7. Current user: root@localhost
   
8. SSL: Not in use
   
9. Using delimiter: ;
   
10. Server version: 5.7.16 MySQL Community Server (GPL)
    
11. Protocol version: 10
    
12. Connection: localhost via TCP/IP
    
13. Server characterset: latin1
    
14. Db characterset: latin1
    
15. Client characterset: utf8
    
16. Conn. characterset: utf8
    
17. TCP port: 3306
    
18. Uptime: 9 min 5 sec
    
19. 
    
20. Threads: 1 Questions: 7 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.012
    
21. --------------
    
22. 
    
23. mysql>

2.透過set來關閉autocommit，重啟後恢復預設值

首先，我們透過set命令來修改autocommit引數。

點選(此處)摺疊或開啟

1. mysql>
   
2. mysql> show global variables like '%commit%';
   
3. +-----------------------------------------+-------+
   
4. | Variable_name | Value |
   
5. +-----------------------------------------+-------+
   
6. | autocommit | ON |
   
7. | binlog_group_commit_sync_delay | 0 |
   
8. | binlog_group_commit_sync_no_delay_count | 0 |
   
9. | binlog_order_commits | ON |
   
10. | innodb_api_bk_commit_interval | 5 |
    
11. | innodb_commit_concurrency | 0 |
    
12. | innodb_flush_log_at_trx_commit | 1 |
    
13. | slave_preserve_commit_order | OFF |
    
14. +-----------------------------------------+-------+
    
15. 8 rows in set, 1 warning (0.00 sec)
    
16. 
    
17. mysql> set autocommit=0;
    
18. Query OK, 0 rows affected (0.00 sec)
    
19. mysql>
    
20. mysql> set global autocommit=0;
    
21. Query OK, 0 rows affected (0.00 sec)
    
22. 
    
23. mysql> show global variables like '%commit%';
    
24. +-----------------------------------------+-------+
    
25. | Variable_name | Value |
    
26. +-----------------------------------------+-------+
    
27. | autocommit | OFF |
    
28. | binlog_group_commit_sync_delay | 0 |
    
29. | binlog_group_commit_sync_no_delay_count | 0 |
    
30. | binlog_order_commits | ON |
    
31. | innodb_api_bk_commit_interval | 5 |
    
32. | innodb_commit_concurrency | 0 |
    
33. | innodb_flush_log_at_trx_commit | 1 |
    
34. | slave_preserve_commit_order | OFF |
    
35. +-----------------------------------------+-------+
    
36. 8 rows in set, 1 warning (0.02 sec)
    
37. 
    
38. mysql>

接下來，我們重啟資料庫，發現autocommit引數已經恢復預設值。

點選(此處)摺疊或開啟

1. E:\mysql-5.7.16-winx64\bin>net stop mysql
   
2. MySQL 服務正在停止.
   
3. MySQL 服務已成功停止。
   
4. 
   
5. 
   
6. E:\mysql-5.7.16-winx64\bin>net start mysql
   
7. MySQL 服務正在啟動 .
   
8. MySQL 服務已經啟動成功。
   
9. 
   
10. 
    
11. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
    
12. mysql: [Warning] Using a password on the command line interface can be insecure.
    
13. Welcome to the MySQL monitor. Commands end with ; or \g.
    
14. Your MySQL connection id is 2
    
15. Server version: 5.7.16 MySQL Community Server (GPL)
    
16. 
    
17. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
18. 
    
19. Oracle is a registered trademark of Oracle Corporation and/or its
    
20. affiliates. Other names may be trademarks of their respective
    
21. owners.
    
22. 
    
23. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
24. 
    
25. mysql> 
26. mysql> show global variables like '%commit%';
    
27. +-----------------------------------------+-------+
    
28. | Variable_name | Value |
    
29. +-----------------------------------------+-------+
    
30. | autocommit | ON |
    
31. | binlog_group_commit_sync_delay | 0 |
    
32. | binlog_group_commit_sync_no_delay_count | 0 |
    
33. | binlog_order_commits | ON |
    
34. | innodb_api_bk_commit_interval | 5 |
    
35. | innodb_commit_concurrency | 0 |
    
36. | innodb_flush_log_at_trx_commit | 1 |
    
37. | slave_preserve_commit_order | OFF |
    
38. +-----------------------------------------+-------+
    
39. 8 rows in set, 1 warning (0.00 sec)
    
40. 
    
41. mysql>

3.修改mysql的配置檔案my.ini

我們找到mysql的配置檔案my.ini，在裡面新增一行記錄“autocommit=0”。

點選(此處)摺疊或開啟

1. [mysql]
   
2. default-character-set=utf8
   
3. [mysqld]
   
4. max_connections=200
   
5. default-storage-engine=INNODB
   
6. basedir =E:\mysql-5.7.16-winx64\bin
   
7. datadir =E:\mysql-5.7.16-winx64\data
   
8. port = 3306
   
9. autocommit=0

然後重新啟動資料庫，確認autocommit引數是否為OFF。

點選(此處)摺疊或開啟

1. E:\mysql-5.7.16-winx64\bin>net stop mysql
   
2. MySQL 服務正在停止.
   
3. MySQL 服務已成功停止。
   
4. 
   
5. 
   
6. E:\mysql-5.7.16-winx64\bin>net start mysql
   
7. MySQL 服務正在啟動 .
   
8. MySQL 服務已經啟動成功。
   
9. 
   
10. 
    
11. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
    
12. mysql: [Warning] Using a password on the command line interface can be insecure.
    
13. Welcome to the MySQL monitor. Commands end with ; or \g.
    
14. Your MySQL connection id is 2
    
15. Server version: 5.7.16 MySQL Community Server (GPL)
    
16. 
    
17. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
18. 
    
19. Oracle is a registered trademark of Oracle Corporation and/or its
    
20. affiliates. Other names may be trademarks of their respective
    
21. owners.
    
22. 
    
23. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
24. 
    
25. mysql> show global variables like '%commit%';
    
26. +-----------------------------------------+-------+
    
27. | Variable_name | Value |
    
28. +-----------------------------------------+-------+
    
29. | autocommit | OFF |
    
30. | binlog_group_commit_sync_delay | 0 |
    
31. | binlog_group_commit_sync_no_delay_count | 0 |
    
32. | binlog_order_commits | ON |
    
33. | innodb_api_bk_commit_interval | 5 |
    
34. | innodb_commit_concurrency | 0 |
    
35. | innodb_flush_log_at_trx_commit | 1 |
    
36. | slave_preserve_commit_order | OFF |
    
37. +-----------------------------------------+-------+
    
38. 8 rows in set, 1 warning (0.01 sec)
    
39. 
    
40. mysql>
    
41. mysql>

我們看到autocommit引數為OFF，目標達成。

4.資料插入速度提升十倍

~~~~~~~ the end~~~~~~~~~
hoegh
2016.11.01