Update MySQL面试题.md

lvminghui · web-flow · commit 47d4c6bb70cf · 2020-03-07T17:06:13.000+08:00
diff --git a/docs/MySQL面试题.md b/docs/MySQL面试题.md
@@ -12,15 +12,15 @@
 - 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
 - 第三范式：任何非主属性不依赖于其它非主属性。
 
-## 说一下 ACID 是什么？
+## 事务的基本要素 ACID⭐
 
-Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
+Atomicity（原子性）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行 
 
-Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
+Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
 
-Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
+Isolation（隔离性）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。 事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
 
-Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
+Durability（持久性）：事务处理结束后，对数据的修改是永久的。
 
 ## char 和 varchar 的区别是什么？
 
@@ -32,7 +32,23 @@ varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一
 
 所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。
 
-## 说一下数据库的事务隔离？
+## where与having的区别
+
+* 用的地方不一样
+
+  where可以用于select、update、delete和insert into values(select * from table where ..)语句中。
+
+  having只能用于select语句中
+
+* 执行的顺序不一样
+
+  where的搜索条件是在执行语句进行分组之前应用
+
+  having的搜索条件是在分组条件后执行的
+
+  即如果where和having一起用时，where会先执行，having后执行
+
+## 数据库的事务隔离/事务隔离级别⭐
 
 可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
 
@@ -44,6 +60,8 @@ REPEATABLE-READ：可重复读，mysql默认级别，保证多次读取同一个
 
 SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。
 
+在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。 **通过索引加锁，间隙锁，next key lock解决幻了读的问题。** 
+
 **补充：**
 
 脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。
@@ -52,23 +70,77 @@ SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级
 
 幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。
 
-## MySQL逻辑架构和执行流程？
+### 如何解决事务的并发问题⭐
+
+**1. 版本检查**
+
+在数据库中保留“版本”字段，跟随数据同时读写，以此判断数据版本。版本可能是时间戳或状态字段。
+
+下例中的 WHERE 子句就实现了简单的版本检查：
+
+```
+UPDATE table SET status = 1 WHERE id=1 AND status = 0;
+```
+
+版本检查能够作为“乐观锁”，解决更新丢失的问题。
+
+**2.1 共享锁与排它锁**
+
+共享锁（Shared locks, S-locks）
+
+基本锁类型之一。加共享锁的对象只允许被当前事务和其他事务读。也称读锁。
+
+能给未加锁和添加了S锁的对象添加S锁。对象可以接受添加多把S锁。
+
+排它锁（Exclusive locks, X-locks）
+
+基本锁类型之一。加排它锁的对象只允许被当前事务读和写。也称独占锁，写锁。
+
+只能给未加锁的对象添加X锁。对象只能接受一把X锁。加X锁的对象不能再加任何锁。
+
+更新锁（Update locks, U-locks）
+
+锁类型之一。引入它是因为多数数据库在实现加X锁时是执行了如下流程：先加S锁，添加成功后尝试更换为X锁。这时如果有两个事务同时加了S锁，尝试换X锁，就会发生死锁。因此增加U锁，**U锁代表有更新意向，只允许有一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。**
+
+**悲观锁与乐观锁**
+
+这两种锁的说法，主要是对“是否真正在数据库层面加锁”进行讨论。
+
+悲观锁（Pessimistic Locking）
+
+悲观锁假定当前事务操纵数据资源时，肯定还会有其他事务同时访问该数据资源，为了避免当前事务的操作受到干扰，先锁定资源。**悲观锁需使用数据库的锁机制实现**，如使用行级排他锁或表级排它锁。
+
+乐观锁（Optimistic Locking）
+
+乐观锁假定当前事务操纵数据资源时，不会有其他事务同时访问该数据资源，因此不在数据库层次上的锁定。**乐观锁使用由程序逻辑控制的技术**来避免可能出现的并发问题。
+
+唯一能够同时保持高并发和高可伸缩性的方法就是使用带版本检查的乐观锁。
+
+乐观锁不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。
+
+## 多版本并发控制(MVCC)⭐
 
-<div align="center"> <img src="https://github.com/lvminghui/Java-Notes/blob/master/docs/imgs/mysql逻辑架构.png"/> </div><br>
+ (Multi-Version Concurrency Control)  
 
-## 说一下 mysql 常用的引擎？
+可以认为MVCC是行级锁的一个变种，innoDB采用了乐观锁的策略，在每行记录后保存两个隐藏列来实现，这两个列保存了行的版本号信息，每开启一个新事务，版本号自动更新，事务开始时刻的版本号作为事务的版本号。用来和查询到的记录所带的版本号进行比较来判断。
+
+## MySQL逻辑架构和执行流程？⭐
+
+![1](C:\Users\吕明辉\Desktop\github笔记\MySQL\1.png)
+
+## 说一下 mysql 常用的引擎？⭐
 
 **InnoDB 引擎**：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count(*) from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
 
 **MyISAM 引擎**：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。
 
-## Myisam和InnoDB的区别
+## Myisam和InnoDB的区别⭐
 
 * **是否支持行级锁** : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁，适合高并发操作。 
 * **是否支持外键**： MyISAM不支持，而InnoDB支持 
 * **是否支持事务**：MyISAM不支持，而InnoDB支持 
 * **缓存**：MyISAM只缓存索引，InnoDB缓存索引和真实数据，所以对内存要求高
-*  **崩溃恢复**：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。 
+* **崩溃恢复**：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。 
 
 ## mysql 索引是怎么实现的？
 
@@ -79,23 +151,64 @@ SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级
 1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而**在实际应用中却是B+树的性能要好些**。
 
-原因：
+## 为什么选择B+树作为索引结构⭐
 
 * 因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些。
 * B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）。
-*  B+树的查询效率更加稳定，每次查询的效率一样。
+* B+树的查询效率更加稳定，每次查询的效率一样。
+
+### Hash索引
+
+Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描 
+
+## B+树的叶子节点都可以存哪些东西⭐
+
+ 可能存储的是整行数据，也有可能是主键的值 
 
 ## 什么样的信息能成为索引
 
 主键，唯一键，普通键都可，只要能让数据有一定区分性的字段。
 
-## 说一下乐观锁和悲观锁？
+### 聚簇索引⭐
+
+聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
+
+非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行
+
+**聚簇索引具有唯一性**， 聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引 。
+
+ **聚簇索引默认是主键**，如果表中没有定义主键，InnoDB 会选择一个**唯一的非空索引**代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键**来作为聚簇索引。 
+
+聚簇索引和非聚簇索引类似查字典时直接根据经验查字的大概位置和先去查偏旁部首再去翻页查询类似。
+
+MyISAM没有聚簇索引，都是二级索引。
+
+#### 优点
+
+* 把相关数据保存在一起，减少了磁盘I/O。
+* 数据访问更快。
+
+### 覆盖索引⭐
+
+指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
+
+## 说一下乐观锁和悲观锁？⭐
 
 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
 
+数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。
+
+（如SVN、GIT提交代码就是这样的） 
+
 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。
 
-数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。
+一般是 where id=XX for update 来实现 （一般银行转账、工单审批） 
+
+**优缺点：**
+
+乐观锁：性能高、重试失败成本不高建议乐观
+
+悲观锁：性能低，但安全，失败成功高建议悲观，使用不当有死锁风险
 
  ## mysql 问题排查都有哪些手段？
 
@@ -136,17 +249,39 @@ order by，group by或者关联查询是否使用了索引。
 如果同时出现using where，表明索引被用来执行索引键值的查找;
 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。 
 
+## 查询在什么时候不走（预期中的）索引⭐
+
+1. 模糊查询 %like
+2. 索引列参与计算,使用了函数
+3. 非最左前缀顺序
+4. where对null判断
+5. where不等于
+6. or操作有至少一个字段没有索引
+7. 需要回表的查询结果集过大（超过配置的范围）
+
+## sql 优化可以从哪些方面考虑？
+
+主要是从怎么**合理创建索引 合理使用索引以防止索引失效 合理创建表字段**这3个方面入手
+
+* **合理创建索引：** 
+  * 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 
+
+* **防止索引失效：**
+  * 创建多列索引时遵循**最佳左前缀法则** ；
+  * 避免使用 select *，列出需要查询的字段；
+  * 不在索引列上和where后做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描；
+  * mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
+  * like以通配符开头(’%abc…’) ，索引失效会变成全表扫描的操作 ；
+  * is not null 也无法使用索引
+* **合理创建表字段：** 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库
+* 使用 LIMIT 语句来限制返回的数据或者垂直分割分表；
+
+## **批量往mysql导入1000万数据有什么方法？**
+
+* 减少IO次数
 
+* SQL写法优化，一条SQL语句插入多条数据   
 
-## 如何做 mysql 的性能优化？
+* 合理设置批量大小
 
-- 避免使用 select *，列出需要查询的字段；
--  使用 LIMIT 语句来限制返回的数据;
-- 垂直分割分表；
-- 选择正确的存储引擎；
-- 为搜索字段创建索引；
-- 创建多列索引时遵循**最佳左前缀法则** ；
-- 不在索引列上和where后做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描；
-- like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作 ；
-- mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
-- is not null 也无法使用索引,但是is null是可以使用索引的 ；
+* 尽量顺序插入， 减少索引的维护压力 
