MySQL多版本并发控制

1. MVCC原理 2. 数据库中的“坑”

事务的隔离级别 - 种类、查看、设置、选择、关联

事务隔离级别

隔离级别	描述
read uncommitted	脏读、不可重复读、幻读
read committed	不可重复读、幻读
repeatable read	幻读(MVCC, next-key-lock解决幻读
serializable	最高隔离级别，施加行级锁

隔离级别的设置

SELECT @@global.tx_isolation; # 查看系统隔离级别
 
SELECT @@tx_isolation; # 查看会话隔离级别(v5)
 
SELECT @@transaction_isolation; # 查看会话隔离级别(v8)
 
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read; # 设置当前会话隔离级别
 
mysql> select @@global.tx_isolation 
    -> ;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)
 
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
 

如何选择隔离级别

• 上松、下严
• Spring事务传播属性要了解

当Spring开启事务并设置传播机制，那么会覆盖MySQL已有的事务隔离级别，如果MySQL不支持该隔离级别，Spring事务就不会生效；

• read committed 和 repeatable read 是常态

MySQL事务隔离级别与并发事务

• 读未提交
  

• 读提交
  

• 可重复读
  

• 串行化
  严格按照先后顺序执行数据变更操作，读写加锁！

多版本并发控制原理之MySQL的日志机制

• redo log: 重做日志

  1. redo log是Inno DB存储引擎的日志
  2. 记录的是数据修改之后的值，不管事务是否提交都会记录下来
  3. 先写日志，后写磁盘

• undo log: 回滚日志

  1. 保存事务发生之前的数据版本
  2. 可以用于回滚
  3. 同时可以提供多版本并发控制下的读(MVCC)，即非锁定读

• bin log: 归档日志

  1. 主从复制
  2. 是以二进制形式记录
  3. 用于数据库的基于时间点的还原
  4. 提交事务记录bin log: 定时写盘

多版本并发控制原理分析之隐式字段

• DB_TRX_ID

  1. 6byte
  2. 记录创建这条记录时，最后一次修改该记录的事务ID

• DB_ROLL_PTR

  1. 7byte， 回滚指针
  2. 指向这条记录的上一个版本（存储与rollback segement回滚段里）

• DB_ROW_ID

  1. 6byte，隐含的自增ID（隐藏主键）
  2. 如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

• 创建版本号

• 删除版本号
  

多版本并发控制原理分析之当前读与快照读

• 当前读： select for update；update；insert update
  读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。

• 快照都： select
  不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

多版本并发控制原理分析之读视图

• 读视图: read-view
  List: 生成read-view时候，活跃的事务id集合
  up limit id: list中最小的事务id
  low limit id: 下一个要分配的事务id
  

• 可重复读级别： 在首次快照读时生成读视图；
• 读已提交级别： 在每次快照读时生成读视图；

多版本并发控制原理分析之数据可见性算法

事务使用经验汇总

Spring中事务的传播属性

Propagation ： key属性确定代理应该给哪个方法增加事务行为。这样的属性最重要的部份是传播行为。
有以下选项可供使用：

• PROPAGATION_REQUIRED--支持当前事务，如果当前没有事务，就新建一个事务。这是最常见的选择。
• PROPAGATION_SUPPORTS--支持当前事务，如果当前没有事务，就以非事务方式执行。
• PROPAGATION_MANDATORY--支持当前事务，如果当前没有事务，就抛出异常。
• PROPAGATION_REQUIRES_NEW--新建事务，如果当前存在事务，把当前事务挂起。
• PROPAGATION_NOT_SUPPORTED--以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
• PROPAGATION_NEVER--以非事务方式执行，如果当前存在事务，则抛出异常。

1. Spring中，方法嵌套调用外层读取数据和内存读取数据效果与数据库隔离级别的关系。
2. 可重复度是MySQL默认的隔离级别，但不一定是常用的。乐观锁必不可少。
3. 死锁、逻辑怪圈、并发场景。

索引实战经验

1. 如何查看SQL语句是否走了索引？ 【EXPLAIN语句剖析SQL】
   
2. 联合索引是最佳左前缀原则(有序、不跳）
3. 区分度较低的字段不适合建立索引
4. 联合索引字段的个数不宜太多，充分权衡插入删除操作以及DBA操作表成本
5. 索引组合索引，少用单列索引
6. where、on、group by、order by后面的字段创建索引
7. 创建索引，不代表就走了索引

MySQL常见面试题

1. 表中ABC三个字段创建联合索引，下面的情况是否能走到该索引？

WHERE A = 10； # ✔
WHERE B = 10; # ❌
WHERE A = 10 AND C = 3; # 在A等于某个值的时候理论上是可以走索引而C跳过了B索引所以不一定会起到索引的效果
WHERE B = 7 AND A = 6; # 可以走，虽然B、A的顺序不正确但在MySQL优化器下可以改变其顺序
WHERE A IN (1, 2, 3); # 理论可走
WHERE A > 8 AND A < 100; # 理论可走
 
WHERE A LIKE '%2%' # 不可走
WHERE A LIKE '2%' # 可走
WHERE A * 3 = 90 # 不可走
 

2. MySQL中锁的种类

按照锁的操作权限

• 共享锁(S锁)
• 排他锁(X锁)

3. InnoDB引擎索引结构？B+与B树区别？

4. 可重复读是否解决了幻读问题？

在MySQL的可重复读级别不存在幻读问题是可重复读级别中多版本并发控制中Next-key Lock解决了幻读问题。

5. 解释Next-key Lock? 锁定范围，左开右闭

6. 按照锁的操作范围

• 表级锁(意向锁）
• 行级锁(间隙锁)
• 索引锁(记录所)

7. MySQL里面binLog、undoLog、redoLog是什么

• binLog是MySQL数据库级别的日志 - 类似于数据库的大管家，所有的操作信息都在binLog中记录
• undoLog和redoLog是MySQL InnoDB事务引擎下面的日志
  • undoLog和redoLog是用来保证事务机制的
  • 用来记录事务操作之前的数据和事务操作之后的数据

8. 如何产生死锁？

多个线程抢占一份共享资源，都在等待对方释放资源而进入等待永远不可能发生事件的状态

9. 高并发数据库读写压力大怎么处理?

1. 硬件层

• 构建DB服务器集群
• 构建缓存服务器集群

2. 软件层

• 根据数据热点不同进行分库、分表
• 构建合理索引
• 读写分离

10. 如何保证数据库的一致性？

11. 什么是覆盖索引和回表？

12. left join、inner join、right join、full join，表表查询区别？

• inner join即内连接包含等值连接、非等值连接、自然连接（去掉重复列的等值连接）
  • 内连接只保留两张表完全匹配的结果集。
• outer join包含left join、right join、full join

学生表和选课表
内连接的业务场景：我们想要得到已经选完课的学生。
外连接的业务常见：我们想要得到每个学生选课的情况，无论他有没有选课。