MySQL面试记录

1.MySQL 遇到过死锁如何解决的？

（1）查看死锁日志 show engine innodb status; （2）找出死锁Sql （3）分析sql加锁情况 （4）模拟死锁并分析死锁日志 （5）分析死锁结果

2.如何尽可能的避免死锁？

1）合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。

2）调整业务逻辑SQL执行顺序，避免update/delete长时间持有锁的SQL在事务前面。

3）避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率更小。

4）以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。

5）在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。

6）尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。

7）优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。

3.MySQL数据库cpu飙升排查

3.1排查过程

（1）使用top 命令观察，确定是mysqld导致还是其他原因。（2）如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

3.2处理

（1）kill 掉这些线程(同时观察 cpu 使用率是否下降)， （2）进行相应的调整(比如说加索引、改 sql、改内存参数) （3）重新跑这些 SQL。

4.主从复制分了五个步骤

步骤一：主库的更新事件(update、insert、delete)被写到binlog

步骤二：从库发起连接，连接到主库。

步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。

步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log

步骤五：还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

5.主从同步延迟的原因与解决办法

原因：一个服务器开放Ｎ个链接给客户端，这样有会有大量的并发更新操作, 但是从服务器的里面读取binlog的线程仅有一个，当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

主从同步延迟的解决办法：主服务器要负责更新操作，优化参数；选择更好的硬件设备作为slave；把一台从服务器当度作为备份使用，而不提供查询，那边负载下来了， 执行relay log 里面的SQL效率自然就高了。增加从服务器分散读的压力，从而降低服务器负载。

6.分库与分表的设计怎么做？

分库分表方案:

水分方向主要针对的是数据。

水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

垂直方向主要针对的是业务。

垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

7.分库分表可能遇到的问题

事务问题：需要用分布式事务

跨节点Join的问题：解决这一问题可以分两次查询实现

跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。

数据迁移，容量规划，扩容等问题

ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID

跨分片的排序分页问题

8.默认隔离级别是RR，为什么公司会改成RC

Mysql的四个隔离级别：

READ-UNCOMMITTED(读取未提交)： 事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读

READ-COMMITTED(读取已提交)： 事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读

REPEATABLE-READ(可重复读)： 这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

mysql 的默认隔离级别是 Repeatable Reads以及背后的原因：因为mysql在主从复制的过程是通过bin log 进行数据同步的，而mysql早期只有statement这种bin log格式，这种格式下，bin log记录的是SQL语句的原文。当出现事务乱序的时候，就会导致备库在 SQL 回放之后，结果和主库内容不一致。为了解决这个问题，MySQL默认采用了Repetable Read这种隔离级别，因为在RR 中，会在更新数据的时候增加记录锁的同时增加间隙锁。

查看数据库当前的隔离级别命令：select @@tx_isolation;

RR 和 RC 的区别想要搞清楚这个问题，我们需要先弄清楚 RR 和 RC 的区别，分析下各自的优缺点：

一致性读，又称为快照读。快照即当前行数据之前的历史版本。快照读就是使用快照信息显示基于某个时间点的查询结果，而不考虑与此同时运行的其他事务所执行的更改。在MySQL 中，只有READ COMMITTED 和 REPEATABLE READ这两种事务隔离级别才会使用一致性读。在 RC 中，每次读取都会重新生成一个快照，总是读取行的最新版本。在 RR 中，快照会在事务中第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照。在数据库的 RC 这种隔离级别中，还支持"半一致读" ，一条update语句，如果 where 条件匹配到的记录已经加锁，那么InnoDB会返回记录最近提交的版本，由MySQL上层判断此是否需要真的加锁。

锁机制：数据库的锁，在不同的事务隔离级别下，是采用了不同的机制的。在 MySQL 中，有三种类型的锁，分别是Record Lock、Gap Lock和 Next-Key Lock。

Record Lock表示记录锁，锁的是索引记录。Gap Lock是间隙锁，锁的是索引记录之间的间隙。Next-Key Lock是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。他的范围是左开右闭的。

在 RC 中，只会对索引增加Record Lock，不会添加Gap Lock和Next-Key Lock。在 RR 中，为了解决幻读的问题，在支持Record Lock的同时，还支持Gap Lock和Next-Key Lock；

主从同步：在数据主从同步时，不同格式的 binlog 也对事务隔离级别有要求。MySQL的binlog主要支持三种格式，分别是statement、row以及mixed，但是，RC 隔离级别只支持row格式的binlog。如果指定了mixed作为 binlog 格式，那么如果使用RC，服务器会自动使用基于row 格式的日志记录。而 RR 的隔离级别同时支持statement、row以及mixed三种

为什么公司选择使用 RC?

提升并发：RC 在加锁的过程中，是不需要添加Gap Lock和 Next-Key Lock 的，只对要修改的记录添加行级锁就行了。这就使得并发度要比 RR 高很多。另外，因为 RC 还支持"半一致读"，可以大大的减少了更新语句时行锁的冲突；对于不满足更新条件的记录，可以提前释放锁，提升并发度。

减少死锁：因为RR这种事务隔离级别会增加Gap Lock和 Next-Key Lock，这就使得锁的粒度变大，那么就会使得死锁的概率增大。

死锁：一个事务锁住了表A，然后又访问表B；另一个事务锁住了表B，然后企图访问表A；这时就会互相等待对方释放锁，就导致了死锁。

总结：主要在加锁机制、主从同步以及一致性读方面存在一些差异。为了提升并发度和降低死锁发生的概率，会把数据库的隔离级别从默认的 RR 调整成 RC

9.MVCC

一般在数据库管理系统中，实现对数据库的并发访问，数据库就必然涉及到读和写的存在，读写就必然涉及到读写冲突，MVCC在mysql中的innodb引擎实现就是为了更好的解决读写冲突，提高数据库的性能，做到即使有读写冲突的时候，也可以不用加锁的方式，非阻塞方式来实现并发读

并发下读读、读写、写读、写写，引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁

MVCC属于一种悲观锁的实现

当前读和快照读，当前读：像select lock in share mode这是共享锁，select for update , update , insert , delete都是属于排他锁，上面说的采用共享锁和排他锁的这种方式，都是属于当前读，当前读就是读取的记录的最新版本，读取的时候还会保证其他并发事务不会修改当前的记录，会对当前的记录进行加锁，防止修改

快照读：不加锁的正常的select查询都是属于快照读，也就是不加锁的非阻塞读。

当然，快照读的前提是隔离级别不是串行级别，此时便会退化成当前读，之所以出现快照读的情况，是mysql中的innodb引擎基于提高并发性能的考虑，快照读也就是基本多版本的并发控制，来更高效的解决读和写之间的冲突问题

根据业务场景来考虑可以接受的问题，避免了加锁的操作，降低了开销，既然是多版本并发控制，那么就要接受读取到的并不一定是最新版本的历史数据这一场景

MVCC只是一个抽象概念，innodb实现这个靠的是三个隐式字段、undo log日志、Read View来实现的

三个隐式字段，DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

DB_TRX_ID：最近修改事务ID，也会记录创建这条记录和最后一次修改这个记录的事务ID

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，存储在undo log日志中的Rollback segment回滚段中

DB_ROW_ID：这个不是一定有，如果表没有创建主键，innodb会自动以这列为主键，以这一列来创建B+树，产生一个聚簇索引，也就是创建的其余索引的B+树的叶子节点存储的是这个主键

实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag 变了

再说undo log日志，Undo log日志分为两种insert undo log和update undo log

Insert undo log：这种是事务在insert新数据的时候产生的日志，只有在事务回滚的时候需要，所以在事务commit之后可以立即丢弃该日志

Update undo log：这个是在进行update或者delete而产生的日志，这个不仅是事务回滚的时候需要，在快照读的时候也是需要的，也就是innodb的MVCC机制会用到历史的数据，所以不能随便删除，需要等快照读和事务回滚都不涉及到该日志的时候，这个日志才会被相应的线程统一清楚

Read View，事务快照是用来存储数据库的事务运行情况。一个事务快照ReadView的创建过程可以概括为：

m_ids：一个数值列表，用于维护 Read View 生成时刻系统正活跃的事务ID列表

up_limit_id：是m_ids活跃事务ID中的最小的事务ID

low_limit_id：ReadView 生成时刻系统尚未分配的下一个事务ID ，也就是目前已出现过的事务ID 的最大值 + 1

可见性比较算法：当事务执行快照读的时候，对该记录创建一个Read View读视图，用于记录此时的情景，把它比做条件用来判断当前事务可以看到哪个版本的数据，到底是看到最新版本，还是看到指向undo log日志中的历史版本呢

我们来一起看可见性算法的流程

1. 当行记录的事务ID小于当前系统的最小活动id，就是可见的。

if (trx_id < view->up_limit_id) {

return(TRUE);

}

2. 当行记录的事务ID大于当前系统的最大活动id，就是不可见的。

if (trx_id >= view->low_limit_id) {

return(FALSE);

}

3. 当行记录的事务ID在活动范围之中时，判断是否在活动链表中，如果在就不可见，如果不在就是可见的。

这里我也别用那些官方语言给大家解释了，我就举个简单的例子给大家解释

M_ids：一个数值列表，用于维护 Read View 生成时刻系统正活跃的事务ID列表

up_limit_id：是m_ids活跃事务ID中的最小的事务ID

low_limit_id：ReadView 生成时刻系统尚未分配的下一个事务ID ，也就是目前已出现过的事务ID 的最大值 + 1

插入一个记录，事务ID是10，此时版本链是10

执行一个update操作，事务ID是20，此时版本链是20-10，commit

执行一个update操作，事务ID是30，此时版本连是30-20-10，未Commit

执行select，事务ID是40，生成一个ReadView，这是一个镜像，此时可能已经有更多事务操作这条数据了，活跃列表是m_ids是[30]，最小事务up_limit_id也是30，最大事务low_limit_id是41

比较过程：按照这个ReadView的事务链30-20-10进行上述算法的比较，30不合适，因为在活跃事务中，20满足条件，所以此时事务ID为40的读取的就是ID为20更新的数据

事务ID30Commit，事务ID50执行update，链变成了50-30-20-10，未提交

关键：此时事务ID为40的再次执行了select操作，查询了该记录

如果事务隔离级别是已提交读隔离级别，这时候会重新生成一个新的ReadView，那此时ReadView已经变了，活跃列表m_ids是[50]，最小事务up_limit_id也是50，最大事务low_limit_id是51

于是按照上述比较，30便符合条件了，所以此时读出来的版本就是事务ID30的update数据了

如果事务隔离级别是可重复读，此时不会生成新的ReadView，用的还是开始时候生成的，所以还是20符合条件

两种隔离级别：我们上面说了MVCC只在READ COMMITTED 和REPEATABLE READ 两个隔离级别下工作，已提交读和可重复读的区别在于他们生成ReadView的策略不同

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView，而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView

我们根据名字也可以推断，可重复读，如果每次读取的时候生成新的ReadView了，那符合条件的版本很可能就不一样了，所以查出来的也就不一样了，就不符合条件了，于是用的就是同一个ReadView。