MySQL面试题

左手的ㄟ右手 2022-12-15 03:53 197阅读 0赞

最近跟着视频学习了很多关于MySQL相关的知识，今天看到一篇MySQL相关的面试题，发现其中的一些问题自己也回答不好，虽然知识点大部分都知道，但是无法将知识串联起来。最终决定将自己零散的知识体系以面试题的方式梳理一下，试着用回答问题的方式，让自己对知识点的理解更加深入一点。

--------------------

## 索引相关 ##

1、什么是索引？

答：MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。句子主干提取，就可以得到索引的本质：索引是一种数据结构。

2、索引是一个什么数据结构？

答：索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。

3、Hash索引和B+树索引有什么区别？为什么InnoDB采用B+树索引而不使用Hash索引?

答：hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。 B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

*  hash表只能匹配是否相等，不能实现范围查找

*select \* from emp where id > 18;*

*  当需要按照索引进行order by时，hash值没办法支持排序

*select \* from emp order by age desc;*

*  hash索引不支持模糊查询以及多列索引的最左前缀匹配
 *  hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。
 *  hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

造成上述原因是因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点)，天然支持范围。

**因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。**

4、说说InnoDB采用B+树索引而不使用其他树索引？比如二叉平衡搜索树、B树。

答：既然索引是为了提高查询效率，现如今最快的查找算法莫过于时间复杂度为O(logN)二分查找算法了。二分查找随快，但是要求元素必须是有序的。数据库中的元素是不断的累加的上去的，如果要求有序，则每次插入数据就需要给元素排序。如果索引存储在数组的数据结构上，那么数据的插入和删除将是一个灾难。那么有没有一种数据结构是擅长插入和删除操作的呢?没错，就是链表，但是链表上面是做不了二分查找的。那么增养才能做到，使用二分查找，又善于增/删操作呢，如此重任非二叉平衡搜索树莫属了。

*  二叉平衡搜索树

假如我们使用二叉平衡搜索树作为索引的数据结构，搜索一个目标数据的时间复杂度相较于O(n)的算法，已经提升到了O(logN)了。但这还不够，因为索引是存储在磁盘上的，I/O操作是非常耗时间的，一次I/O操作的时间CPU可以执行几十万次指令了，是否还可以继续减少I/O的次数呢?B树。

*  B 树

B 树二叉平衡树的变体，它是多路搜索树，通俗来讲就是叉路变多了，由于叉树变多了，则树的层数就会减少，层数减少则意味着I/O次数的减少。B树将数据存储在节点中，搜索时将节点中的数据取出然后在内存中使用二分查找，找到对应的数据是很快的。哪还有没有可能继续减少I/O次数呢(树的层次减少一层，即可减少一次I/O的次数)，减少I/O，让CPU计算来加快整体的速度。B+树。

*  B+树

操作系统I/O操作的单位为页，根据操作系统的不同，页存储的数据大小不同。如果每个页里面存入更多的数据，意味着I/O的次数减少。B+树将所有数据元素存储在叶子节点上，则非叶子节点上就可以存储更多的指针数据，这样就可以分出更多的叉路，同时意味着树结构的层数更少，则I/O的次数变得更少，整体搜索速度得到了更大的提升。因此，mysql 的 innodb 采用了 B+ 树来作为索引。

5、前面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,请谈谈什么是聚簇索引和覆盖索引？

聚簇索引是指数据和索引是放在一个叶子节点上。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

覆盖索引是指查询的列全都在该组合索引上，举例：我们为emp表建立一个组合索引，index(age,name)。id为主键

*select id,age,name from emp where age = 18 and name = 'dahuang';*

这个时候我们发现所有查询的列均在组合索引上，这个时候就称该索引为覆盖索引。

6、如果一开始创建表的时候没有创建主键，没有唯一值，添加数据之后，又设置了主键。这个时候数据库会有什么样的操作？

答：可以知道，没有创建主键和唯一值，这个时候会隐式的生成一个键来建立聚簇索引。但当我们添加数据后，再设置了主键后，InnoDB会删除隐式的聚簇索引，再将主键设置为聚簇索引。并且，在创建主键索引的时候会比平时多耗费一些时间。

**原因：以创建时间换取查询的时间，尽可能的减少回表，减少IO读取次数以增加查询效率**

7、什么时候需要回表？什么时候不需要回表？

答：聚簇索引和覆盖索引不需要回表，其他情况都需要回表。

8、在建立索引的时候,我们应该有哪些因素要考虑呢?

答：1、建立索引的时候一般要考虑到字段的使用频率，经常作为条件进行查询的字段比较适合。 2、如果需要建立联合索引的话，还需要考虑联合索引中的顺序。 3、此外也要考虑其他方面，比如防止过多的索引对表造成太大的压力。

9、索引的最左匹配原则？

答：最左匹配原则说的是搜索的时候，该 SQL 语句是否可以利用到索引来提升效率。假设我们在数据库中建立了联合索引(name，age，addres)，那么我们在写 SQL 语句的时候，where 条件中 name，age，address 同时他们所在的表达式不是 全模糊/左模糊查询，则该查询可以使用索引，同时不论他们在 SQL 条件中的顺序，因为数据库会将他们优化成合适的顺序。

10、那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢？

答：最常见的有以下几种情况：

*  使用不等于查询
 *  列参与了数学运算或者函数
 *  在字符串like时左边是通配符，类似于'%huang'。
 *  当mysql分析全表扫描比使用索引快的时候不使用索引.
 *  当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引。

## 事务相关 ##

1、什么是事务？

答：事务就是用户定义的一系列数据库操作，这些操作可以视为一个完成的逻辑处理工作单元，要么全部执行，要么全部不执行，是不可分割的工作单元。最常见的理解就是:事务中的操作要么全部成功,要么全部失败。银行转账的例子大家都不陌生吧，哈哈。。。他们要符合ACID特性

2、详细说明一下ACID是什么?

答：

<table> 
 <thead> 
  <tr> 
   <th>英文</th> 
   <th>中文</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Atomicity</td> 
   <td>原子性</td> 
   <td>一个事务不可在分割，要么都执行要么都不执行</td> 
  </tr> 
  <tr> 
   <td>Consistency</td> 
   <td>一致性</td> 
   <td>一个事务的执行会使数据从一个一致状态切换到另一个一致的状态</td> 
  </tr> 
  <tr> 
   <td>Isolation</td> 
   <td>隔离性</td> 
   <td>一个事务的执行不受其他事物的干扰</td> 
  </tr> 
  <tr> 
   <td>Durability</td> 
   <td>持久性</td> 
   <td>一个事务一旦提交，则会永久的改变数据库的数据</td> 
  </tr> 
 </tbody> 
</table>

***隔离性(Isolation): 通常来说,一个事务在完全提交之前，对其他事务是不可见的，但也有例外情况。***

3、同时进行多个事务会发生什么呢?

答：多事务的并发进行一般会造成以下几个问题:

*  脏读（Dirty Read）: A事务读取到了B事务未提交的内容，而B事务后面进行了回滚
 *  不可重复读(NonRepeatable Read): 当设置A事务只能读取B事务已经提交的部分，会造成在A事务内的两次查询，结果竟然不一样，因为在此期间B事务进行了提交操作
 *  幻读(Phantom Read): A事务读取了一个范围的内容，而同时B事务在此期间插入了一条数据

4、怎么解决上面这些问题呢?MySQL的事务隔离级别了解吗?

答：MySQL的四种隔离级别如下:

*读未提交(READ UNCOMMITTED)*

这就是上面所说的例外情况了，这个隔离级别下，其他事务可以看到本事务没有提交的部分修改。因此会造成脏读的问题(读取到了其他事务未提交的部分，而之后该事务进行了回滚)。

*读已提交(READ COMMITTED)*

其他事务只能读取到本事务已经提交的部分。这个隔离级别有不可重复读的问题，在同一个事务内的两次读取，拿到的结果不同，原因是另外一个事务对数据进行了修改。

*可重复读(REPEATABLE READ)*

可重复读隔离级别解决了上面不可重复读的问题，但是仍然有一个新问题，就是幻读，当你读取id> 5 的数据行时，对涉及到的所有行加上了读锁，此时例外一个事务新插入了一条id=8的数据，因为是新插入的，所以不会触发上面的锁的排斥，那么进行本事务进行下一次的查询时会发现有一条id=8的数据，而上次的查询操作并没有获取到，再进行插入就会有主键冲突的问题。

**InnoDB默认使用的是可重复读隔离级别。**

*可串行化(SERIALIZABLE)*

这是最高的隔离级别，可以解决上面提到的所有问题，因为他强制将所以的操作串行执行，这会导致并发性能极速下降，因此在现实场景中基本不使用。

把隔离级别与可能出现的问题整理为一个表格如下：

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>读未提交</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>读已提交</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>可重复读</td> 
   <td>×</td> 
   <td>×</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>可串行化</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
  </tr> 
 </tbody> 
</table>

5、怎么在MySQl中设置隔离级别？

答：set tx\_isolation = '具体的隔离级别'，例如要设置为读已提交的隔离级别。

*set tx\_isolation = 'READ\_COMMITTED';*

6、MySQL都有哪些锁呢？

答：有共享锁和排他锁，又被称为读锁和写锁。

某张表被上了读锁，其他的会话能够查询，单修改会被阻塞。 上了写锁，其他的会话不能进行查询和修改，只能当前会话查询和修改。

欢迎扫码关注我的微信公众号，一起学习进行。

![20201015145101356.jpg][]

[20201015145101356.jpg]: /images/20221123/fe0dab1c1b4a4098a8e0c98468698441.png