数据库相关问题2_读已提交 rc 隔离级别下的可见性分析-CSDN博客

本文链接：https://blog.csdn.net/xiaofangkuailalala/article/details/127474011

题目 01- 完成 ReadView 案例，解释为什么 RR 和 RC 隔离级别下看到查询结果不一致

使用云服务器上的mysql
初始化：
CREATE TABLE tab_user (
id int(11) NOT NULL,
name varchar(100) DEFAULT NULL,
age int(11) NOT NULL,
address varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
Insert into tab_user(id,name,age,address) values (1,‘刘备’,18,‘蜀国’);
在这里插入图片描述

案例 01- 读已提交 RC 隔离级别下的可见性分析

操作步骤

远程登录mysql，三个窗口，共代表三个事务。
在这里插入图片描述
操作步骤
（开头数字代表事务编号）
1set session transaction isolation level read committed;
2set session transaction isolation level read committed;
3set session transaction isolation level read committed;
1BEGIN;
2BEGIN;
3BEGIN;
1UPDATE tab_user SET name = ‘关羽’ WHERE id = 1;
1UPDATE tab_user SET name = ‘张飞’ WHERE id = 1;
2UPDATE tab_user SET name = ‘赵云’ WHERE id = 1;
2UPDATE tab_user SET name = ‘诸葛亮’ WHERE id = 1;
3SELECT * FROM tab_user;
1COMMIT;
3SELECT * FROM tab_user;
2COMMIT;
3SELECT * FROM tab_user;
3COMMIT;

结果（事务3，T6,T8,T10）

在这里插入图片描述

异常结果分析

事务2在执行update语句的时候会被阻塞，直到事务1提交，阻塞才会解除。由于我无意间在事务2执行update语句几分钟后才去执行事务1的commit，导致事务2窗口报错，报错内容是
在这里插入图片描述
通过这个报错了解到事务2并不会无限阻塞下去。然后查阅资料得知InnoDB事务等待一个行级锁的时间最长时间默认值是50秒，超过这个时间就会放弃。(通过show variables命令验证了等待锁的时间确实是50秒）

在这种情况下事务3在t10读到的还是张飞。因为事务2的update操作因为没有获得锁而放弃了更新操作。

再次测试

缩短t5和t7之间的时间，得到了预想的输出。
在这里插入图片描述

案例 02- 可重复读 RR 隔离级别下的可见性分析

INNODB默认的级别是RR，因此该案例不用手动设置隔离级别，其他步骤同上。
结果
在这里插入图片描述
T6,T8,T10结果跟预想一致。另外我在事务3commit之后，又执行了一次查询，结果是诸葛亮。一开始觉得是异常，认为哪里出了错。然后想到commit之后，即使还在同一个窗口，但此时是另一个事务了，之前事务2提交的更新肯定是生效的。

题目 02- 什么是索引？

索引是表中数据有序排列的目录，优点是如果使用的方法得当，在查找的时候可以加快数据访问的速度，在更新的时候可以减小锁的粒度。缺点是维护索引需要额外的磁盘空间。

索引分类

唯一索引和普通索引

按照索引是否唯一分为唯一索引和普通索引。唯一索引包括主键索引，当然可以创建非主键的唯一索引。不过唯一索引可以为空，但主键索引不可以为空。
CREATE UNIQUE INDEX index_name ON table(column_name) ;

聚簇索引和辅助（二级）索引

在innodb引擎中，按照索引节点的内容区分可以分为聚簇索引和辅助（二级）索引，聚簇索引的内容是该行的所有内容，辅助索引节点内容是聚簇索引，因此一般当通过辅助索引查询的时候，需要再去访问聚簇索引，这种操作称为回表。
在myisam引擎中，因为数据和索引是分开存储的，所以不存在聚簇和二级的概念。
如果一张表里建了主键索引，那么主键索引一定是聚簇索引，如果没有显式的建主键，rowid会成为表的聚簇索引，因此在一个表里，聚簇索引有且仅有一个。

覆盖索引

覆盖索引我理解不是索引的一种分类方式，是使用索引的方式，是指索引能够覆盖到要select后面要查的字段。如表t中有主键索引id，有普通索引ab，select b from t where a=‘111’。

单列索引和组合索引

按照索引包含列的个数分为单列索引和组合索引。一般建议选择建组合索引，比如（abc）这个索引相当于建了a，ab，abc三个索引

索引创建的原则是什么？

在区分度高，更新频率不高的列上建索引.
主键索引建议使用自增的长整型，避免使用很长的字段,因为主键要存在辅助索引的叶子节点，主键越长，占用空间越大，那么b+树会越高，io成本越大。

有哪些使用索引的注意事项？

根据最左匹配原则

如何知道 SQL 是否用到了索引？

通过执行计划，在要执行的sql语句前加explain，如 explain select b from t where a=‘111’

索引原理

索引在INNODB里的物理实现是B+树，即叶子节点相连的多路搜索树
其他可能的实现方式：哈希表或者二叉树
哈希表的优点是查询的时间复杂度非常低，但哈希表的设计原则就是为了使数据能够均匀离散分布，和设计索引的初衷-数据的有序分布是相违背的。所以哈希表非常不适合做范围查询
二叉树因为每个节点只有2个子节点，会导致树太高，从而导致IO成本提高。