mysql+查询慢+插入快,MySQL:RR模式下insert也可能导致查询慢

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;