《高效能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 setmysql> set global transaction isolation level read committed;Query OK, 0 rows affectedmysql> show variables like 'tx_isolation';+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+1 row in setmysql> 

貌似隔離等級沒有變？ 關掉當前命令列，重新開啟一個命令列

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

交易隔離等級修改完畢後，我們開始測試

事務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的時候同樣符合預期結果。

總結：總體研究了不少時間，但是還沒有徹底挖清楚這個參數對效能的影響，但是以上這些知識點也非常的重要，再次懇請有這方面經驗的人給出協助，非常感謝。