Note the first wave of high-performance mysql, high-performance mysql

Note the first wave of high-performance mysql, high-performance mysql

Author: fallen flower fragrance

Blog: http://blog.csdn.net/qing_gee

Preface: This article provides some experiences after reading "High Performance mysql. The project has been in progress recently.Performance Optimization between spring and mysqlBecause I have little knowledge about the relationship between the two, I need to check a lot of information and review it repeatedly. There are several advantages of recording in my blog: 1. let more people pay attention to and understand, so as to get more help; 2. you don't have to go back and forth to read books every time and read them on your blog.

Theory

Practice

Transaction 1	Transaction 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

Through the above sequential execution, we can see that "at the beginning of a transaction, we can only see modifications made by committed transactions", and "A transaction starts until it is committed, any modifications made are invisible to other transactions. "Then," the same query may be executed twice in a transaction and different results may be obtained ".

Transaction 1	Transaction 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

As shown in the preceding steps, When READ-COMMITTED is used, the query results of a transaction are affected by the update and insert operations of other transactions..

First, let's take a look at how Modify transaction isolation level(The following describes how to change the Repeatable read to the committed read. Note that if you follow the test order, modify it)

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> 

Does the isolation level change? Close the current command line and open a new command line.

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

After the transaction isolation level is modified, we start testing.

Transaction 1	Transaction 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

From the above results, we can see that "this level ensures that the same transaction reads the same results multiple times ". Here there is no magic line, because my database engine is innodb, and the book says this engine can solve the phantom read problem.

• The isolation level can be read repeatedly, and the update test is performed.

Transaction 1	Transaction 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

The update operation also meets the expected results.

Summary: The overall research has been a lot of time, but the impact of this parameter on performance has not been fully explored, but the above knowledge points are also very important. I would like to ask people with experience in this area to help, thank you very much.