MySQL面试题

MVCC快照读的一致性视图

MVCC
Innodb中的事务隔离级别和锁的关系

MVCC是乐观锁，快照读通过创建一致性视图，基于行数据的事务Id和一致性视图对比数据版本判断数据是否可见

只有在读提交和可重复读的隔离级别下才有MVCC，因为读未提交总是读取最新的数据，可串行化会对所有读取的行都加锁
读提交是在每个查询语句开始前创建一致性视图
可重复读是在每个事务开始前创建一致性视图

快照读和当前读

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。
快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
快照读：简单的select操作，属于快照读，不加锁。

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

• update当前读加写锁是因为防止数据丢失
  比如key=1
  事务1 set key=key+1，之后事务2 set key=key+1
  在事务1未提交前事务2则需要等待事务1提交后才能进行当前读，否则事务1没有加写锁，事务1在update后key=2，事务2的当前读只能读到1，这时进行update后key=2，就会将事务1的update覆盖。

• insert当前读加写锁是因为唯一键可能会产生冲突

• delete当前读加写锁是因为防止delete被覆盖，理由与update类似，如果delete未提交但是不加写锁，这时其他事务进行update就会将delete覆盖

InnoDB解决幻读问题

间隙锁和next-key lock

为什么能解决幻读问题？
幻读问题就是针对insert两次查询结果数据量不一致，比如第一次通过id=1查询10条数据，插入id=1一条数据，第二次查询则查询出11条数据，导致前后数据不一致，快照读不会产生幻读问题

只有可重复读（RR）的隔离级别下才会有next-key锁，解决幻读问题，当第一次通过select … from … where c=5 lock in share mode时，加上了读锁，查询了10条数据，因为非唯一索引，等值查询，且查询命中，因此next-key锁范围(0,10)，因此对索引锁范围内进行写时会阻塞，防止第二次查询存在幻读

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

• 原则 1： 加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
• 原则 2： 查找过程中访问到的对象才会加锁。
• 优化 1： 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。（等值查询唯一索引命中退化行锁）
• 优化 2： 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。（等值查询未命中或非唯一索引命中向右遍历退化间隙锁）
• 一个 bug： 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

等值查询：
id为唯一索引
如果id=7，查询未命中，根据优化2退化成间隙锁，加锁范围为(5,10)
如果id=5，查询命中，id为唯一索引，根据优化1退化成行锁，加锁范围为5

c为非唯一索引
如果id=7，查询未命中，根据优化2退化成间隙锁，加锁范围为(5,10)
如果id=5，查询命中，不退化成行锁，根据优化2退化成间隙锁，加锁范围为(0,10)

如果为共享锁的覆盖索引，没有返回表中用到主键索引，因此只锁覆盖索引，不锁主键索引，而排他锁会都锁上

范围查询：
id为唯一索引
如果10<=id<11，根据优化1，id=10退化为行锁，则加锁范围为[10,15]

c为非唯一索引
如果10<=c<11，不退化成行锁，加锁范围为(5,15]

两段锁协议

事务分为两个阶段，前一个阶段是加锁阶段（读写锁），后一个阶段是解锁阶段。在加锁阶段，事务只能加锁，不能解锁，可以操作数据，直到事务提交或回滚释放第一个锁，就进入解锁阶段，这时候不能再进行加锁，只能解锁
不能避免死锁，但是两段锁协议可以保证事务的并发调度是串行化的

Serializable可串行化

这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

Union和Union all

Union和Union all区别

一、区别

1、显示结果不同

union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来。

2、对重复结果的处理不同

union all是直接连接，取到得是所有值，记录可能有重复；union 是取唯一值，记录没有重复。所以union在进行表链接后会筛选掉重复的记录，union all不会去除重复记录。

3、对排序的处理不同

union将会按照字段的顺序进行排序；union all只是简单的将两个结果合并后就返回。从效率上说，union all 要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用union all。

注意事项：

1、union 和 union all都可以将多个结果集合并，而不仅仅是两个，所以可将多个结果集串起来。

2、使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的。但列名则不一定需要相同，oracle会将第一个结果的列名作为结果集的列名。

二、使用方法

1、union：用于对多个select查询结果进行联合。

2、union all：用于对多个select查询结果进行联合。

3、union 和 union all 对select语句的要求：

(1)各个select查询语句中，各个select查询的列数的个数必须相同，不能1个select查询的列数是4列，而另一个select查询的列数是7列。

(2)各个select查询语句中，每个列的数据类型必须相同或相似。不能1个select的第1列是int类型，而另一个select的第1列是nvarchar类型。

SQL生命周期

1、客户端与数据库服务器建立连接

2、数据库进程拿到请求sql

3、分析器进行SQL解析、预处理

4、由优化器生成对应的执行计划

5、MySQL根据执行计划，读取数据到内存，调用存储引擎的API来执行

6、将结果返回给客户端

7、关掉连接，释放资源

InnoDB与MyISAM 聚集索引与非聚集索引

原理剖析：InnoDB与MyISAM 聚集索引与非聚集索引

B树和B+树

MySQL索引-B+树

总参考

硬核！MySQL数据库面经大全