《高性能mysql》札记第一波，《高性能mysql》

《高性能mysql》札记第一波，《高性能mysql》

作者：晴落花香

博客：http://blog.csdn.net/qing_gee

前言：本篇文章是阅读《高性能mysql》后一些心得。项目中最近一直在进行spring和mysql之间的性能优化，由于对两者之间的关系了解甚微，导致需要查阅大量的资料，并且反复的回看，记录在博客中有几个好处：1.让更多的人关注和了解，从而获取更多的帮助；2.每次不必再来回翻看书籍，直接在博客上查阅。

理论

实践

事务1	事务2
mysql> show variables like 'tx_isolation'; +---------------+----------------+ | Variable_name | Value          | +---------------+----------------+ | tx_isolation  | READ-COMMITTED | +---------------+----------------+ 1 row in set mysql> start transaction; Query OK, 0 rows affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | +----+-------+ 2 rows in set	1
2	mysql> start transaction; Query OK, 0 rows affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | +----+-------+ 2 rows in set mysql> insert into test values(3,3); Query OK, 1 row affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set
mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | +----+-------+ 2 rows in set	3
4	mysql> commit; Query OK, 0 rows affected
mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set mysql> commit; Query OK, 0 rows affected	5

通过上面的顺序执行，可以看出来“一个事务开始时，只能看到已经提交的事务所作的修改”，并且“一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的”，然后在一个事务内“两次执行同样的查询，可能会得到不一样的结果”。

事务1	事务2
mysql> show variables like 'tx_isolation'; +---------------+----------------+ | Variable_name | Value          | +---------------+----------------+ | tx_isolation  | READ-COMMITTED | +---------------+----------------+ 1 row in set mysql> start transaction; Query OK, 0 rows affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set	1
2	mysql> start transaction; Query OK, 0 rows affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set mysql> update test set value = 11 where id = 1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |    11 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set
mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set	3
4	mysql> commit; Query OK, 0 rows affected
mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |    11 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set mysql> commit; Query OK, 0 rows affected	5
mysql> show variables like 'tx_isolation'; +---------------+----------------+ | Variable_name | Value          | +---------------+----------------+ | tx_isolation  | READ-COMMITTED | +---------------+----------------+ 1 row in set mysql> start transaction; Query OK, 0 rows affected mysql> select value from test where id = 1; +-------+ | value | +-------+ |    11 | +-------+ 1 row in set	6
7	mysql> start transaction; Query OK, 0 rows affected mysql> update test set value = 1 where id = 1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select value from test where id = 1; +-------+ | value | +-------+ |     1 | +-------+ 1 row in set
mysql> select value from test where id = 1; +-------+ | value | +-------+ |    11 | +-------+ 1 row in set	8
9	mysql> commit; Query OK, 0 rows affected

mysql> select value from test where id = 1; +-------+ | value | +-------+ |     1 | +-------+ 1 row in set mysql> commit; Query OK, 0 rows affected

10

从以上步骤可以看出来，使用READ-COMMITTED的时候，一个事务内部，其查询结果会受到其他事务的update和insert影响。

首先我们先看一下命令行模式下，怎么修改事务隔离级别（以下是把可重复读改成提交读，注意如果按照我的测试顺序，请注意修改）

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> 

貌似隔离级别没有变？关掉当前命令行，重新打开一个命令行

mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql> 

事务隔离级别修改完毕后，我们开始测试

事务1	事务2
mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | REPEATABLE-READ | +---------------+-----------------+ 1 row in set mysql> start transaction; Query OK, 0 rows affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set	1
2	mysql> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | REPEATABLE-READ | +---------------+-----------------+ 1 row in set mysql> start transaction; Query OK, 0 rows affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set mysql> insert into test values(4,4); Query OK, 1 row affected mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | |  4 |     4 | +----+-------+ 4 rows in set
mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set	3
4	mysql> commit; Query OK, 0 rows affected
mysql> select * from test; +----+-------+ | id | value | +----+-------+ |  1 |     1 | |  2 |     2 | |  3 |     3 | +----+-------+ 3 rows in set mysql> commit; Query OK, 0 rows affected	5

从以上结果可以看得出来“该级别保证了同一个事务中，多次读取同样的结果是一致的”。这里并没有出现幻行，因为我的数据库引擎为innodb，书中说这种引擎可以解决幻读的问题。

• 隔离级别为可重复读，测试update情况

事务1	事务2
mysql> start transaction; Query OK, 0 rows affected mysql> select value from test where id=1; +-------+ | value | +-------+ |     1 | +-------+ 1 row in set
	mysql> start transaction; Query OK, 0 rows affected mysql> select value from test where id =1; +-------+ | value | +-------+ |     1 | +-------+ 1 row in set mysql> update test set value=11 where id=1; Query OK, 1 row affected Rows matched: 1  Changed: 1  Warnings: 0 mysql> select value from test where id =1; +-------+ | value | +-------+ |    11 | +-------+ 1 row in set
mysql> select value from test where id=1; +-------+ | value | +-------+ |     1 | +-------+ 1 row in set
	mysql> commit; Query OK, 0 rows affected
mysql> select value from test where id=1; +-------+ | value | +-------+ |     1 | +-------+ 1 row in set mysql> commit; Query OK, 0 rows affected

update的时候同样符合预期结果。

总结：总体研究了不少时间，但是还没有彻底挖清楚这个参数对性能的影响，但是以上这些知识点也非常的重要，再次恳请有这方面经验的人给出帮助，非常感谢。