MySQL面试复习笔记（三）

1、next-key lock 临键锁是什么？

next-key lock是间隙锁和行锁的结合，主要作用是防止幻读。

加锁规则如下：

1⃣️加锁的对象是索引，比如加锁的索引是A，那么另一个线程更新同一行记录的话，不一定会受到影响，取决于是否走同一个索引

2⃣️加锁的区间是前开后闭的

3⃣️访问到的地方才会加锁，比如等值查询，select * from t1 where id = 1; id=2的数据是不加锁的，加锁范围是(-∞,1]

4⃣️等值查询如果找到对应的行，如果该索引是唯一索引，会退化为行锁，就是只锁住一行

5⃣️等值查询如果如果找不到对应的行，会退化为间隙锁，就是只锁住两行之间存在的间隙

6⃣️加锁顺序是先加间隙锁再加行锁

7⃣️唯一索引上的范围查询，会锁住第一个不符合条件的行

注意next-key lock也是会出现死锁的，这个原因之一是间隙锁是共享锁，如果两个线程分别给同一个区间加间隙锁，但是又分别向这个区间插入数据，就会出现死锁。

2、幻读、脏读、不可重复读

这三种情况一般和事务的隔离级别有关的。

未提交读隔离级别下，一个事务中可以读取到其他事务中已经修改但还未提交的值，这个时候就会产生脏读；

已提交读隔离级别下，一个事务中可以读取到其他已经提交的事务的修改内容，这个时候会产生不可重复读，比如事务A正在访问id=1的记录，这个时候一个字段d=1，由于没有主动加锁，事务B对id=1的记录进行了修改，此时d改为=2，事务A再查询一次id=1的记录，发现d=2，和一开始查询的值不一致，这就是不可重复读；

一般不可重复读有两种解决办法，一个是通过给需要重复读的行加锁，一个是通过mvcc。

如果不在当前读的场景中，mvcc其实已经解决了幻读的问题，但是如果事务出现了当前读这种情况的话，还是会存在当前读这种问题的，这个时候就要靠锁来解决，比如间隙锁、行锁、表锁。

3、什么情况下，索引会失效？

①查询条件没有用到索引字段；

②在索引列上加函数运算(8.0以上增加了函数索引，可以解决)；

③多字段索引没有根据最左匹配原则来规划查询条件，比如索引idx_a_b_c，查询条件是 where b=1,c=1；

④隐式转换，比如字段A是varchar类型，查询条件是 where a=1

⑤使用不等于，或者not进行查询，由于索引检索效率比较低，mysql就不会走索引；

⑥like通配符在前面，比如 like '%abc'

⑦or的两边没有全部使用索引字段

⑧join的联接顺序也有可能影响索引

具体是否走索引，可以通过explain命令对语句进行分析。

4、mysql两阶段提交

mysql将日志提交和日志写入分成两个阶段，来保证redolog和binlog写入的数据一致性。

第一阶段，prepare阶段，这个阶段会把事务操作记录到redolog中，并且标记为prepare状态；

第二阶段，commit阶段，这个阶段会在事务提交的时候写入到binlog，并且在redolog里面把事务操作标记为commit。

如果在prepare阶段之前，系统崩溃了，这个时候在redolog和binlog里面是没有数据的，说明事务还没有提交成功就崩溃了，这个时候是满足数据一致性的；

如果在prepare阶段之后，系统崩溃了，这个时候redolog里面是有数据的，但是状态还是prepare，这个时候去拿事务ID到binlog里面查找是找不到的，所以在系统恢复的时候把这一段未成功执行完的事务回滚即可；

如果在commit之后，系统崩溃了，这个时候redolog的状态如果还是prepare，就用事务id查找binlog，如果发现找到，说明当时没有来得及改为commit，但是事务已经提交成功了。

5、如何进行join查询优化？

首先要了解join查询慢的原因，join的时候会根据具体条件，选择使用哪一种算法去生成结果集。一般有三种算法，一种是简单嵌套循环连接（SNL），第二种是索引嵌套循环连接（INL），第三种是块嵌套循环连接（BNL）。

SNL是一种比较简单粗暴的连接算法，原理相当于两个for循环嵌套在一起，比如T1.id=t2.id，第一步会遍历一遍t1.id，然后第二步逐个t1.id拿去t2.id中去遍历，找到符合条件的数据再返回，这个算法的效率很低，但是简单粗暴；

其实mysql中一般不会用到SNL，因为在join条件中，如果on使用的字段用到了索引，这个时候，就会走索引嵌套循环连接算法，这个算法优化了SNL中第二步，将遍历全表变成按照索引进行二分查找，效率会更高。

当on使用的字段用不到索引的时候，会走BNL算法，就是会把驱动表中的值，缓存到join buffer中，然后批量的用join buffer的值去被驱动表中获取符合条件的数据。

根据上述原理，优化join查询的方向就是减少循环。

对于外层循环，可以用小表驱动大表，这里的小表是小结果集的表，大表是大结果集的表；

对于外层循环，就是在条件中尽量使用到索引。