MySQL的存储引擎InnoDB支持事物管理，所有SQL执行都要commit才有效，当然啦，你可以通过设置全局参数autocommit来自动提交；

MyISAM不支持事物管理，采用的是自动提交；

下面来测试下MySQL中autocommit对存储引擎InnoDB和MyISAM的性能影响

一，准备工作

1，新建测试表格；

create table `test_table`( `number` int(11) ) ENGINE=InnoDB

2，新建测试方法；

delimiter //
create procedure `test_function`(max_num int(11))
begin
	declare i int default 0;
	declare rand_num int;
	while i < max_num do
		select cast(rand()*100 as unsigned) into rand_num;
		insert into test_table(`number`) values(rand_num);
		set i = i +1;
	end while;
end 

二，测试InnoDB引擎

1，查看表格存储引擎，这里是InnoDB

mysql> show table status from test where name='test_table';
+------------+--------+---------+------------+------+----------------+-------------+-----------------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+
| test_table | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+
1 row in set (0.00 sec)

2，查看自动提交参数，这里先开启成自动提交

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

3，清空表格开始测试，插入500条数据，用时14秒多

mysql> truncate table test_table;
Query OK, 0 rows affected (0.25 sec)
mysql> call test_function(500);
Query OK, 1 row affected (14.29 sec)

4，关闭自动提交

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

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

5，再次清空表格进行测试，插入500条数据，同时commit提交数据，共用时不到1秒钟

mysql> truncate table test_table;
Query OK, 0 rows affected (0.25 sec)

mysql> call test_function(500);
Query OK, 1 row affected (0.15 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql>

三，测试MyISAM引擎
1，查看存储引擎，这里是MyISAM

mysql> show table status from test where name='test_table';
+------------+--------+---------+------------+------+----------------+-------------+-
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
+------------+--------+---------+------------+------+----------------+-------------+-
| test_table | MyISAM |      10 | Fixed      |  500 |              7 |        3500 |
+------------+--------+---------+------------+------+----------------+-------------+-
1 row in set (0.00 sec)

2，查看自动提交参数，这里先开启自动提交

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

3，清空表格进行测试，插入500条数据，用时0.05秒

mysql> truncate table test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> call test_function(500);
Query OK, 1 row affected (0.05 sec)

4，关闭自动提交

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

5,再次清空表格进行测试，插入500条数据，用时0.03秒；

mysql> truncate table test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> call test_function(500);
Query OK, 1 row affected (0.03 sec)

mysql>

四，结论：
由测试结果可知，
1，MySQL存储引擎InnoDB开启autocommit后性能会大幅下降，因此建议关闭自动提交功能，在执行完所有SQL之后再commit即可。
2，MyISAM本身采用自动提交机制，所以autocommit设置对其性能影响不大；
3，InnoDB关闭autocommit后，与MyISAM性能基本相当；