数据库篇
文章目录
1. 隔离级别
要求
- 掌握四种隔离级别与相关的错误现象
未提交读
-
读到其它事务未提交的数据(最新的版本)
-
错误现象:有脏读、不可重复读、幻读现象
脏读现象
tx1 | tx2 |
---|---|
set session transaction isolation level read uncommitted; | |
start transaction; | |
select * from account; /两个账户都为 1000/ | |
start transaction; | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /1号账户2000, 2号账户1000/ |
- tx2 未提交的情况下,tx1 仍然读取到了它的更改
提交读(RC)
-
读到其它事务已提交的数据(最新已提交的版本)
-
错误现象:有不可重复读、幻读现象
-
使用场景:希望看到最新的有效值
不可重复度现象
tx1 | tx2 |
---|---|
set session transaction isolation level read committed; | |
start transaction; | |
select * from account; /两个账户都为 1000/ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /1号账户2000, 2号账户1000/ |
- tx1 在同一事务内,两次读取的结果不一致,当然,此时 tx2 的事务已提交
可重复读(RR)
-
在事务范围内,多次读能够保证一致性(快照建立时最新已提交版本)
-
错误现象:有幻读现象,可以用加锁避免
-
使用场景:事务内要求更强的一致性,但看到的未必是最新的有效值
幻读现象
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /存在 1,2 两个账户/ | |
insert into account values(3, 1000); | |
select * from account; /发现还是只有 1,2 两个账户/ | |
insert into account values(3, 5000); /* ERROR 1062 (23000): Duplicate entry ‘3’ for key ‘PRIMARY’ */ |
- tx1 查询时并没有发现 3 号账户,执行插入时却发现主键冲突异常,就好像出现了幻觉一样
加锁避免幻读
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /存在 1,2 两个账户/ | |
select * from account where accountNo=3 for update; | |
insert into account values(3, 1000); /* 阻塞 */ | |
insert into account values(3, 5000); |
- 在 for update 这行语句执行时,虽然此时 3 号账户尚不存在,但 MySQL 在 repeatable read 隔离级别下会用间隙锁,锁住 2 号记录与正无穷大之间的间隙
- 此时 tx2 想插入 3 号记录就不行了,被间隙锁挡住了
串行读
-
在事务范围内,仅有读读可以并发,读写或写写会阻塞其它事务,用这种办法保证更强的一致性
-
错误现象:无
串行读避免幻读
tx1 | tx2 |
---|---|
set session transaction isolation level serializable; | |
start transaction; | |
select * from account; /* 存在 1,2 两个账户 */ | |
insert into account values(3, 1000); /* 阻塞 */ | |
insert into account values(3, 5000); |
- 串行读隔离级别下,普通的 select 也会加共享读锁,其它事务的查询可以并发,但增删改就只能阻塞了
2. 快照读与当前读
要求
- 理解快照读与当前读
- 了解快照产生的时机
当前读
即读取最新提交的数据
- select … for update
- select … lock in share mode
- insert、update、delete,都会按最新提交的数据进行操作
当前读本质上是基于锁的并发读操作
快照读
读取某一个快照建立时(可以理解为某一时间点)的数据,也称为一致性读。快照读主要体现在 select 时,而不同隔离级别下,select 的行为不同
-
在 Serializable 隔离级别下 - 普通 select 也变成当前读,即加共享读锁
-
在 RC 隔离级别下 - 每次 select 都会建立新的快照
-
在 RR 隔离级别下
- 事务启动后,首次 select 会建立快照
- 如果事务启动选择了 with consistent snapshot,事务启动时就建立快照
- 基于旧数据的修改操作,会重新建立快照
快照读本质上读取的是历史数据(原理是回滚段),属于无锁查询
RR 下,快照建立时机 - 第一次 select 时
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /* 此时建立快照,两个账户为 1000 */ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /* 两个账户仍为 1000 */ |
- 快照一旦建立,以后的查询都基于此快照,因此 tx1 中第二次 select 仍然得到 1 号账户余额为 1000
如果 tx2 的 update 先执行
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /* 此时建立快照,1号余额已经为2000 */ |
RR 下,快照建立时机 - 事务启动时
如果希望事务启动时就建立快照,可以添加 with consistent snapshot 选项
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction with consistent snapshot; /* 此时建立快照,两个账户为 1000 */ | |
update account set balance = 2000 where accountNo=1; | |
select * from account; /* 两个账户仍为 1000 */ |
RR 下,快照建立时机 - 修改数据时
tx1 | tx2 |
---|---|
set session transaction isolation level repeatable read; | |
start transaction; | |
select * from account; /* 此时建立快照,两个账户为 1000 */ | |
update account set balance=balance+1000 where accountNo=1; | |
update account set balance=balance+1000 where accountNo=1; | |
select * from account; /* 1号余额为3000 */ |
- tx1 内的修改必须重新建立快照,否则,就会发生丢失更新的问题
3. InnoDB vs MyISAM
要求
- 掌握 InnoDB 与 MyISAM 的主要区别
- 尤其注意它们在索引结构上的区别
InnoDB
-
索引分为聚簇索引与二级索引
- 聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的
- 二级索引:除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据,叶子节点还包含了主键值
-
支持事务
- 通过 undo log 支持事务回滚、当前读(多版本查询)
- 通过 redo log 实现持久性
- 通过两阶段提交实现一致性
- 通过当前读、锁实现隔离性
-
支持行锁、间隙锁
-
支持外键
MyISAM
-
索引只有一种
- 被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的
-
不支持事务,没有 undo log 和 redo log
-
仅支持表锁
-
不支持外键
-
会保存表的总行数
InnoDB 索引特点
聚簇索引:主键值作为索引数据,叶子节点还包含了所有字段数据,索引和数据是存储在一起的
- 主键即 7369、7499、7521 等
二级索引:除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据,叶子节点还包含了主键值
- 上图中 800、950、1100 这些是工资字段的值,根据它们建立了二级索引
- 上图中,如果执行查询
select empno, ename, sal from emp where sal = 800
,这时候可以利用二级索引定位到 800 这个工资,同时还能知道主键值 7369 - 但 select 字句中还出现了 ename 字段,在二级索引中不存在,因此需要根据主键值 7369 查询聚簇索引来获取 ename 的信息,这个过程俗称回表
MyISAM 索引特点
被索引字段值作为索引数据,叶子节点还包含了该记录数据页地址,数据和索引是分开存储的
4. 索引
要求
- 了解常见索引与它们的适用场景,尤其是 B+Tree 索引的特点
- 掌握索引用于排序,以及失效情况
- 掌握索引用于筛选,以及失效情况
- 理解索引条件下推
- 理解二级索引覆盖
索引基础
常见索引
-
哈希索引
- 理想时间复杂度为 O ( 1 ) O(1) O(1)
- 适用场景:适用于等值查询的场景,内存数据的索引
- 典型实现:Redis,MySQL 的 memory 引擎
-
平衡二叉树索引
- 查询和更新的时间复杂度都是 O ( l o g 2 ( n ) ) O(log_2(n)) O(log