review之数据库篇

数据库篇

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)$
  • 适用场景：适用于等值查询的场景，内存数据的索引
  • 典型实现：Redis，MySQL 的 memory 引擎

• 平衡二叉树索引

  • 查询和更新的时间复杂度都是 $O(lo{g}_{}$