824   MySQL

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性能基本相当;




Leave a Reply

Your email address will not be published. Required fields are marked *