公眾號 MySQL學習 
---
RR隔離級別，簡單記錄如下

#####1、session 1開啟read view
```
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table testin(id int);
Query OK, 0 rows affected (0.11 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from testin;
Empty set (0.01 sec)
```

#####2、session 2 不斷插入資料

```
mysql> insert into testin select * from testin;
Query OK, 65536 rows affected (14.11 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> select count(*) from testin;
+----------+
| count(*) |
+----------+
|   131072 |
+----------+
1 row in set (6.82 sec)

mysql> insert into testin select * from testin;
Query OK, 131072 rows affected (27.86 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> select count(*) from testin;
+----------+
| count(*) |
+----------+
|   262144 |
+----------+
1 row in set (13.46 sec)

mysql> insert into testin select * from testin;
Query OK, 262144 rows affected (55.49 sec)
Records: 262144  Duplicates: 0  Warnings: 0
```

#####3、session 1查詢時間不斷遞增

```
mysql> select count(*) from testin;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.40 sec)

mysql> select count(*) from testin;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.41 sec)

mysql> select count(*) from testin;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.40 sec)

mysql> select *from testin;
Empty set (0.41 sec)

mysql> select *from testin;
Empty set (0.98 sec)

mysql> select *from testin;
Empty set (2.72 sec)

mysql> select *from testin;
Empty set (2.97 sec)
```

應該是在read view的作用下做了很多跳過處理，當然RC 會直觀的觀察到資料的變化，但是RR則會在可見性的判斷下跳過這些行。
慢查詢由於innodb層mysql層沒有互動因此看不到掃描了行。但是實際上是做了行掃描的。
```
# Time: 2020-12-16T21:23:34.622196-05:00
# User@Host: root[root] @ localhost []  Id:     7
# Schema: test  Last_errno: 0  Killed: 0
# Query_time: 2.966895  Lock_time: 0.000124  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 62
SET timestamp=1608171814;
select *from testin;
```

MySQL:RR模式下insert也可能導致查詢慢

相關文章