Update MySQL面试题.md

lvminghui · web-flow · commit 687fc26463e7 · 2020-03-09T01:28:39.000+08:00
diff --git a/docs/MySQL面试题.md b/docs/MySQL面试题.md
@@ -60,7 +60,11 @@ REPEATABLE-READ：可重复读，mysql默认级别，保证多次读取同一个
 
 SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。
 
-在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。 **通过索引加锁，间隙锁，next key lock解决幻了读的问题。** 
+在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。 **通过next key lock解决了幻读的问题。** 
+
+- Record lock：单个行记录上的锁
+- Gap lock：间隙锁，锁定一个范围，不包括记录本身
+- Next-key lock：record+gap 锁定一个范围，包含记录本身
 
 **补充：**
 
@@ -118,6 +122,24 @@ UPDATE table SET status = 1 WHERE id=1 AND status = 0;
 
 乐观锁不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。
 
+## 说一下乐观锁和悲观锁？⭐
+
+乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
+
+数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。
+
+（如SVN、GIT提交代码就是这样的） 
+
+悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。
+
+一般是 where id=XX for update 来实现 （一般银行转账、工单审批） 
+
+**优缺点：**
+
+乐观锁：性能高、重试失败成本不高建议乐观
+
+悲观锁：性能低，但安全，失败成功高建议悲观，使用不当有死锁风险
+
 ## 多版本并发控制(MVCC)⭐
 
  (Multi-Version Concurrency Control)  
@@ -128,11 +150,15 @@ UPDATE table SET status = 1 WHERE id=1 AND status = 0;
 
 ![1](C:\Users\吕明辉\Desktop\github笔记\MySQL\1.png)
 
-## 说一下 mysql 常用的引擎？⭐
+- 最上层的服务类似其他CS结构，比如连接处理，授权处理。
+- 第二层是Mysql的服务层，包括SQL的解析分析优化，存储过程触发器视图等也在这一层实现。
+- 最后一层是存储引擎的实现。
+
+## 说一下 mysql 常用的引擎？
 
-**InnoDB 引擎**：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
+**InnoDB 引擎**：MySQL 的5.5之后的默认引擎，InnoDB 引擎提供了对数据库事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
 
-**MyISAM 引擎**：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。
+**MyISAM 引擎**：不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。
 
 ## Myisam和InnoDB的区别⭐
 
@@ -157,9 +183,11 @@ UPDATE table SET status = 1 WHERE id=1 AND status = 0;
 * B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）。
 * B+树的查询效率更加稳定，每次查询的效率一样。
 
-### Hash索引
+**Hash索引底层是哈希表**，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，**哈希索引只适用于等值查询的场景**。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描 
 
-Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描 
+- 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。
+- 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。
+- 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。
 
 ## B+树的叶子节点都可以存哪些东西⭐
 
@@ -173,11 +201,11 @@ Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结
 
 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
 
-非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行
+非聚簇索引：将数据存储金和索引分开的结构，索引结构的叶子节点指向了数据的对应行
 
-**聚簇索引具有唯一性**， 聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引 。
+**聚簇索引具有唯一性**， 一个表仅有一个聚簇索引 。
 
- **聚簇索引默认是主键**，如果表中没有定义主键，InnoDB 会选择一个**唯一的非空索引**代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键**来作为聚簇索引。 
+**聚簇索引默认是主键**，如果表中没有定义主键，InnoDB 会选择一个**唯一的非空索引**代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键**来作为聚簇索引。 
 
 聚簇索引和非聚簇索引类似查字典时直接根据经验查字的大概位置和先去查偏旁部首再去翻页查询类似。
 
@@ -192,23 +220,10 @@ MyISAM没有聚簇索引，都是二级索引。
 
 指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
 
-## 说一下乐观锁和悲观锁？⭐
+## 最佳左前缀法则⭐
 
-乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
-
-数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。
-
-（如SVN、GIT提交代码就是这样的） 
-
-悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。
-
-一般是 where id=XX for update 来实现 （一般银行转账、工单审批） 
-
-**优缺点：**
-
-乐观锁：性能高、重试失败成本不高建议乐观
-
-悲观锁：性能低，但安全，失败成功高建议悲观，使用不当有死锁风险
+指的是查询从索引的最左前列开始并且不跳过索引中的列。
+在创建索引的字段中第一个就是最左，每个左边的字段都是后面一个字段的一整个树，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。**要按照顺序命中索引**
 
  ## mysql 问题排查都有哪些手段？
 
@@ -249,7 +264,7 @@ order by，group by或者关联查询是否使用了索引。
 如果同时出现using where，表明索引被用来执行索引键值的查找;
 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。 
 
-## 查询在什么时候不走（预期中的）索引⭐
+##  
 
 1. 模糊查询 %like
 2. 索引列参与计算,使用了函数
@@ -259,22 +274,23 @@ order by，group by或者关联查询是否使用了索引。
 6. or操作有至少一个字段没有索引
 7. 需要回表的查询结果集过大（超过配置的范围）
 
-## sql 优化可以从哪些方面考虑？
+## sql 优化可以从哪些方面考虑？⭐
 
 主要是从怎么**合理创建索引 合理使用索引以防止索引失效 合理创建表字段**这3个方面入手
 
 * **合理创建索引：** 
-  * 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 
+
+* 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 
 
 * **防止索引失效：**
-  * 创建多列索引时遵循**最佳左前缀法则** ；
-  * 避免使用 select *，列出需要查询的字段；
-  * 不在索引列上和where后做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描；
-  * mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
-  * like以通配符开头(’%abc…’) ，索引失效会变成全表扫描的操作 ；
-  * is not null 也无法使用索引
+
+  参考上一个问题的答案
+
 * **合理创建表字段：** 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库
-* 使用 LIMIT 语句来限制返回的数据或者垂直分割分表；
+
+* 不使用 *，使用 LIMIT 语句来限制返回的数据
+
+* 减少交互次数（批量提交） 
 
 ## **批量往mysql导入1000万数据有什么方法？**
 
@@ -285,3 +301,17 @@ order by，group by或者关联查询是否使用了索引。
 * 合理设置批量大小
 
 * 尽量顺序插入， 减少索引的维护压力 
+
+### redolog,undolog,binlog
+
+- undoLog 也就是我们常说的回滚日志文件 主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如"把id='B' 修改为id = 'B2' ，那么undo日志就会用来存放id ='B'的记录”。当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
+- redoLog 是重做日志文件是记录数据修改之后的值，用于持久化到磁盘中。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上。不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性。
+- binlog由Mysql的Server层实现,是逻辑日志,记录的是sql语句的原始逻辑，比如"把id='B' 修改为id = ‘B2’。binlog会写入指定大小的物理文件中,是追加写入的,当前文件写满则会创建新的文件写入。 产生:事务提交的时候,一次性将事务中的sql语句,按照一定的格式记录到binlog中。用于复制和恢复在主从复制中，从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。业务数据不一致或者错了，用binlog恢复。
+
+### binlog和redolog的区别
+
+1. redolog是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层服务层产生的。
+2. 两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。
+3. 两种日志与记录写入磁盘的时间点不同，binlog日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。
+4. binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redolog是循环使用。
+5. binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。
