关于mysql面试和工作中经常用到的一些知识

关于mysql面试和工作中经常用到的一些知识

问题1：请说一下mysql分别有那些事务隔离级别？

回答：问到mysql的事务隔离级别，那肯定是在Innodb的存储引擎下。

事务的隔离级别有 读未提交（read-uncommitted） 读已提交（read-committed） 可重复读（repeatable-read） 序列化（serializable）

 

问题2：这些事务隔离级别分别解决哪些问题？

回答： 读已提交（read-committed）解决的是  脏读 问题 

例如 现在有两个客户端 A 和 B ，A 在一个事务中，先是插入 或者 修改 表中的信息，A 未提交事务 。此时 B 去读表中的数据就会读取到A未提交的数据。

若 A 因为某些原因 事务回归了，则之前 B 读取到的数据就是 脏数据。

            可重复读（repeatable-read） 解决的是 不可重复读 问题 （这个非常好记，哈哈）

例如  现在有两个客户端 A 和 B ，A在一个事务中，先是读取表中的数据。此时 B 修改了表中的数据 并提交了事务。之后 A 在之前的事务中再读取一次

数据 会发现 A 前后两次读取的数据不同，即再同一个事务中两次查询的结果不一致

           序列化（serializable） 解决的是 幻读 问题

例如 现在有两个客户端 A 和 B ，A在一个事务中，先是读取表中的数据。此时 B 删除 或者 插入了一条数据 并提交了事务。之后 A 在之前的事务中再读取

一次数据 会发现 A 前后两次读取的数据不同，即再同一个事务中两次查询的结果不一致 

        注意:可能有人会把 不可重复读 和 幻读 搞混 。不可重复读 主要说的是 update 之后产生的不一致， 而 幻读 主要说的是 insert 之后产生的不一致

         

       有的面试官会说 mysql 可重复读（repeatable-read）的隔离级别里也解决的幻读 （简直就是误人子弟，后面会讲到）

 

问题3：mysql里的可重复读（repeatable-read）是如何实现的

回答：mysql 在可重复读（repeatable-read）的事务隔离级别下 使用了 MVCC（Multi-Version Concurrency Control）的技术——多版本并发控制。

在 RR 隔离级别下 每次一个事务在执行第一个语句的时候 就会生成一个版本 记录到 undolog 里，在事务中的查询都会根据版本号去查询 数据，也就

是说每个事务 读取的数据都是一份快照数据。所以两次读取的数据都是一样的。由此实现了 可重复读。

PS：有的面试官所说的 RR级别解决 幻读 也是基于 这个 MVCC技术，但实际上这是错误的。（要真能完全解决幻读，那serializable级别不就失去

意义了么，╮(╯▽╰)╭ ）

接下来讲的也是借鉴了网上的大神的讲解 

       

session A	session B
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)	mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)
	mysql> insert into test(id,n) values(3,3); Query OK, 1 row affected (0.00 sec)
	mysql> commit; Query OK, 0 rows affected (0.00 sec)
mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)
惊讶的发现，影响了3条数据 mysql> update test set n = 4; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0
在本事务没有执行过插入时，突然多出了一条id=3的数据。这就是幻读 mysql> select * from test; +----+------+ | id | n | +----+------+ | 1 | 4 | | 2 | 4 | | 3 | 4 | +----+------+ 3 rows in set (0.00 sec)

以上摘自 https://blog.csdn.net/u010841296/article/details/89041198

总结一下：如果单纯A事务只进行2次查询的话，确实不会出现幻读，但如果A事务在这2次查询之间进行了 update 或者 delete 操作，则会直接操作表中

的数据，而非版本快照。所以第二次查询就是会查询到 第一次查询没有查询到，但是 update 修改到的数据（delete 删除完当然是不可能查询到的，但是

根据返回的执行结果可以指导，影响了多少row）。

如果想避免出现这种情况，可以在查询语句后面部上一段sql “for update”，这就实现了在查询的时候也对查询出来的结果加了写锁。

问题4：undolog、redolog和binlog的区别

PS：其实这个问题是我自己想的，目前还没有见过面试官问这个，但是多了解一些也是好的

回答：1.undolog是mysql 用来实现事务的一致性的，每次有新的事务，undolog里都会生成一个数据的版本，为了方便事务的回滚 ，也是实现MVCC的基础。

           2.redolog是mysql用来保证数据的持久性，每次有数据操作，Innodb都会先往redolog里插入一个记录。然后mysql 会定时的讲redolog里的操作同步到

           物理硬盘上。不管事务是否提交都会记录下来。当数据库重启后，会根据redolog里的信息，把数据恢复。redo log日志的大小是固定的，即记录满了

           以后就从头循环写

           3.binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有

     crash-safe能力的，

          一条更新语句执行的顺序：

                 update T set c=c+1 where ID=2;

                ①执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

                ②执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

                ③引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

                ④执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

                ⑤执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

以上部分摘自https://blog.csdn.net/u010002184/article/details/88526708

          有的人可能会有疑问（就当有疑问吧 ，嘿嘿），为什么要redolog这个日志？ 结合上面的知识点总结下来：首先mysql进行读或者修改数据的时候也都是在内存中操作，因为频繁的从硬盘中读取数据是非常耗时的，为了减少IO上的耗时，所以在第一次读取或修改某行数据的时候，数据库就会将数据读取到内存中。同时会在redolog里记录操作。数据库会有专门的程序去定时的将redolog里的操作刷到硬盘中。另一方面就是，如果mysql重启了，也可以根据redolog来进行数据的还原。

问题4：在Innodb下 数据的索引是什么结构的？

回答：B+Tree 

非主键索引，会根据所选的列创建一个B+树(B+tree)结构的树状索引，其叶子节点上存的是对应的主键。主键索引也是B+树(B-tree)，只是主键索引的叶子节点上，存的就是数据。数据文件本身就是主键索引。所以每次走非主键索引的时候，最后都要在走一边主键索引。

PS:索引不用B-tree 而要用B+tree呢？这是因为索引本身也是存储在磁盘中的，由于B-tree的每个节点上除了有索引的key 还有对应的data，所以在走索引的时候，每个节点存的key就会少。又要减少IO所以 每个节点存的值就少，在加上data，所以每个节点的key就会少，可能就会影响B-tree的深度。所以B+tree在此之上进行了改良，B+tree只有叶子节点上有值。另外B+树相邻的叶子节点上相互都有指针指向对方，方便范围查询。

问题5：联合索引什么时候失效？

回答：一般情况下，面试官会举个例子问了能不能走联合索引，只要记住 最左匹配原则 就清楚了

当然也有例外，如果联合索引中存在主键，mysql会优先走主键索引。例如：用户姓名和用户主键id的联合索引index_personName_id ，mysql会优先走id的主键索引。

问题6：如何使用共享锁（读锁）和排他锁（写锁）

回答：在Innodb存储引擎下，在select语句最末尾加 lock in share mode；（其实我用lock tables person read 也可以，person 是表名）。

在Innodb的RR事务级别下，如果不声明共享锁，mysql 是不会加锁的。而且只要不加锁，读任何数据都是可以的（这个是由于RR下的MVCC机制，上文有提到）

问题7：mysql的行锁是如何实现的

回答：行锁是Innodb下使用的，MyIsam下都是表锁。这是由于Innodb为了支持事务，所以要降低锁的粒度，但是行锁的缺点就是，锁的开销比大。当语句有where条件，且where条件有建索引时，mysql大部分时候都会加行锁。为什么说时大部分时候呢？因为有的时候，及时有建索引，但是mysql没有用到这个索引的话，此时行锁会升级成表锁。所以在处理sql锁问题的时候 ，尽量用explain 分析一下sql语句的执行过程，看看到底走没走索引。行锁最终都是加在主键索引上的，所以若两个sql的where条件不同，但是查询到的行是同一行的，后面的sql也会进行等待。