MySQL常见面试题与答案

分手后的思念是犯贱 2023-10-02 15:29 13阅读 0赞

#### 文章目录 ####

*   *  存储引擎
     *   *  InnoDB的主要特点是什么？
         *  InnoDB与MyISAM的区别？
     *  查询
     *   *  MySQL执行一次SQL需要经过哪些流程？
         *  为什么MySQL 8.0默认关闭了缓存开启？
         *  EXPLAIN如何使用？
     *  索引
     *   *  MySQL索引默认用哪种数据结构？优点是什么？
         *  索引为什么不用平衡二叉树？
         *  如果用Hash索引会有什么问题？
         *  聚簇索引和非聚簇索引的区别？
         *  什么是最左前缀原则？
         *  什么是覆盖索引？
         *  为什么MySQL选择B+Tree索引而MongoDB却选择了B-Tree索引？
     *  事务
     *   *  ACID分别指什么？
         *  事务并发下会有哪些问题？
         *  事务的隔离级别有哪些？MySQL默认是哪种？
         *  写操作是否会阻塞读操作，为什么？
         *  事务回滚的原理是什么？
         *  如何保证的持久性？
     *  锁
     *   *  InnoDB中锁有哪些种类？
         *  什么情况下会发生死锁？
         *  如何避免死锁？
     *  分库分表
     *   *  常见的分片策略有哪些？
         *  订单表如何选择分片键（买家id、商家id）？

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

### 存储引擎 ###

#### InnoDB的主要特点是什么？ ####

*  MySQL5.5版本之后的默认存储引擎；
 *  支持事务；
 *  支持行级锁；
 *  支持MVCC；
 *  支持聚集索引方式存储数据。

#### InnoDB与MyISAM的区别？ ####

<table> 
 <thead> 
  <tr> 
   <th align="left">存储引擎</th> 
   <th align="left">MyISAM</th> 
   <th align="left">InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">存储结构</td> 
   <td align="left">MyISAM在磁盘上存储成三个文件，其中.frm文件存储表定义，.MYD 为数据文件，.MYI 为索引文件。</td> 
   <td align="left">InnoDB是由.frm文件、表空间（分为独立表空间或者共享表空间）和日志文件（redo log）组成。</td> 
  </tr> 
  <tr> 
   <td align="left">存储空间</td> 
   <td align="left">可被压缩，存储空间较小。</td> 
   <td align="left">需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。</td> 
  </tr> 
  <tr> 
   <td align="left">事务支持</td> 
   <td align="left">强调的是性能，执行速度比InnoDB快，但不支持事务。</td> 
   <td align="left">支持事务，具有ACID的特性</td> 
  </tr> 
  <tr> 
   <td align="left">锁</td> 
   <td align="left">只支持表级锁。 如果执行大量的select，MyISAM是更好的选择。但是在增删改的时候需要锁定整个表格，效率会低一些。</td> 
   <td align="left">支持事务和行级锁，是InnoDB的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁只有在WHERE条件命中索引时才是有效的，如果全表扫描会锁定整张表。</td> 
  </tr> 
  <tr> 
   <td align="left">索引</td> 
   <td align="left">MyISAM的索引都属于B+Tree结构的非聚簇索引，索引与数据是分开的，B+Tree的叶子节点上存放的是数据的地址。主键索引和辅助索引没有区别，只是主键索引必须是唯一的。</td> 
   <td align="left">InnoDB的主键是B+Tree结构的聚簇索引，将主键组织到一棵B+Tree中，而行数据就储存在叶子节点上。聚簇索引是按大小排列的，因此对于范围查询的效率很高。InnoDB中除了主键索引其他辅助索引都是非聚簇索引，叶子节点则存储的是主键的值，所以通过辅助索引查询其实需要两个过程，先确定数据的主键，再通过主键进行查询。</td> 
  </tr> 
  <tr> 
   <td align="left">select count(*)</td> 
   <td align="left">MyISAM保存了表的总行数，如果select count(*) from table不加where条件，会直接取出出该值。</td> 
   <td align="left">InnoDB没有保存表的总行数，如果使用select count(*) from table就会遍历整张表。如果加了where条件后，MyISAM和InnoDB处理的方式都一样。</td> 
  </tr> 
  <tr> 
   <td align="left">全文索引</td> 
   <td align="left">支持 FULLTEXT类型的全文索引</td> 
   <td align="left">不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。</td> 
  </tr> 
  <tr> 
   <td align="left">外键</td> 
   <td align="left">不支持</td> 
   <td align="left">支持</td> 
  </tr> 
 </tbody> 
</table>

### 查询 ###

#### MySQL执行一次SQL需要经过哪些流程？ ####

![img][]

注：MySQL8.0已取消查询缓存

#### 为什么MySQL 8.0默认关闭了缓存开启？ ####

1.  在查询之前必须先检查是否命中缓存，浪费计算资源；
2.  如果这个查询可以被缓存，那么执行完成后，MySQL发现查询缓存中没有这个查询，则会将结果存入查询缓存，这会带来额外的系统消耗；
3.  针对表进行写入或更新数据时，将对应表的所有缓存都设置失效；
4.  如果查询缓存很大或者碎片很多时，这个操作可能带来很大的系统消耗；
5.  缓存应该由Redis、MC等中间件去完成（专人干专事）。

#### EXPLAIN如何使用？ ####

[MySQL EXPLAIN属性分析\_学无止境-CSDN博客][MySQL EXPLAIN_-CSDN]

### 索引 ###

#### MySQL索引默认用哪种数据结构？优点是什么？ ####

B+Tree；

优势：

*  磁盘读写能力强。B+Tree非叶节点不保存数据相关信息，只保存关键字和子节点的引用，所以一个节点可以保存更多关键字，一次磁盘加载的关键字更多。
 *  扫表能力更强。B+Tree关键字对应的数据都保存在叶子节点上， 进行全表扫描时，只需要遍历叶子节点，无需遍历整棵树。
 *  遍历、排序、范围搜索的能力更强。B+Tree叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系，所以对于这种依赖顺序或范围的操作更有优势。
 *  检索效率更加稳定。B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。

#### 索引为什么不用平衡二叉树？ ####

*  太高：树的高（深）度决定着它的IO操作次数。显然，当数据量逐渐增加时，平衡二叉树的深度也会显著增加。
 *  太小：每一个节点保存的数据量太小了。没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作。

#### 如果用Hash索引会有什么问题？ ####

*  不适合范围查找、排序等操作

#### 聚簇索引和非聚簇索引的区别？ ####

*  InnoDB的主键是B+Tree结构的聚簇索引，将主键组织到一棵B+Tree中，而行数据就储存在叶子节点上。聚簇索引是按大小排列的，因此对于范围查询的效率很高。
 *  InnoDB中除了主键索引其他辅助索引都是非聚簇索引，叶子节点则存储的是主键的值，所以通过辅助索引查询其实需要两个过程，先确定数据的主键，再通过主键进行查询。

#### 什么是最左前缀原则？ ####

*  对索引中关键字进行匹配时，一定是从左往右依次进行，不能跳过。如果最左边是模糊的，那么将无法命中索引。
 *  例如对于单列索引：a like’%123’；联合索引 (a,b,c)：where b=2 and c=3; 都无法命中索引。
 *  理解了索引结构 B+Tree 的特点之后，就不难理解最左前缀原则了。

#### 什么是覆盖索引？ ####

*  如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用；
 *  即通过索引便可以直接得到想要的内容，这个过程称为覆盖索引。

#### 为什么MySQL选择B+Tree索引而MongoDB却选择了B-Tree索引？ ####

*  B+Tree将行数据保存在叶子节点，而B-Tree的每个节点都保存数据，因此B+Tree的查询时间复杂度固定是logn，而B-Tree查询复杂度最好是 O(1)；
 *  B+Tree更适合范围查找，B-Tree单次查找的平均效率更高；

### 事务 ###

#### ACID分别指什么？ ####

*  原子性（Atomicity）：整个事务所有操作要么全部提交成功，要么全部失败回滚，不可能只成功一部分。
 *  一致性（Consistency）：事务中操作的数据及状态改变是一致的，即写入资料的结果必须完全符合预设的规则，不会因为出现系统意外等原因导致状态的不一致。
 *  隔离性（Isolation）：通常来说，一个事务所做的修改在最终提交以前，对其它事务是不可见的。多个事务之间的操作相互不影响。
 *  持久性（Durability）：事务提交后，事务对数据库的所有更新将被保存到数据库，且无法撤回。一旦一个事务已经提交了，就算服务器崩溃，仍然需要在下次启动的时候结合事务日志自动恢复。

#### 事务并发下会有哪些问题？ ####

*  脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
 *  不可重复读：事务A多次读取同一条数据，事务B在事务A执行的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
 *  幻读：事务A对多次读取某一范围的数据，事务B在事务A执行的过程中插入并提交一条满足A读取范围的数据，使A发现数据变多了，犹如产生幻觉一般。

#### 事务的隔离级别有哪些？MySQL默认是哪种？ ####

<table> 
 <thead> 
  <tr> 
   <th align="left">隔离级别</th> 
   <th align="left">说明</th> 
   <th align="left">脏读</th> 
   <th align="left">不可重复读</th> 
   <th align="left">幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">读未提交（read-uncommitted）</td> 
   <td align="left">性能没有明显优势，同时缺乏其他级别的好处，因此很少使用。</td> 
   <td align="left">是</td> 
   <td align="left">是</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">读已提交（read-committed）</td> 
   <td align="left">大多数数据库的默认级别，只能看到已提交的事务，避免了脏读，但相同查询可能读到不同结果。</td> 
   <td align="left">否</td> 
   <td align="left">是</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">可重复读（repeatable-read）</td> 
   <td align="left">MySQL默认级别，非InnoDB可能产生幻读。</td> 
   <td align="left">否</td> 
   <td align="left">否</td> 
   <td align="left">InnoDB 否</td> 
  </tr> 
  <tr> 
   <td align="left">串行化（serializable）</td> 
   <td align="left">在读取的每行数据上都加锁，强制事务串行执行，会导致大量超时与锁竞争问题，一般很少使用。</td> 
   <td align="left">否</td> 
   <td align="left">否</td> 
   <td align="left">否</td> 
  </tr> 
 </tbody> 
</table>

#### 写操作是否会阻塞读操作，为什么？ ####

*  不会，InnoDB引擎可以通过MVCC（多版本并发控制）实现并发访问下，对事务内正在处理的数据做多版本管理。以实现写操作堵塞的同时，依然可以进行读操作。
 *  MVCC是基于undo log实现的，事务未提交之前，Undo log保存了未提交之前的版本数据，Undo log中的数据可作为数据旧版本快照供其他并发事务进行快照读。

#### 事务回滚的原理是什么？ ####

*  在事务提交前，Undo log保存了未提交之前的版本数据，事务处理过程中如果出现了错误或者用户执行了 ROLLBACK语句，MySQL可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

#### 如何保证的持久性？ ####

*  持久性是通过redo log实现，MySQL会将事务中操作的最新数据备份到redo log；
 *  redo log可以让数据在事务提交时以顺序IO的方式快速持久化到文件，然后再异步的将这部分数据刷到具体的磁盘位置，这样即使刷盘前发生宕机，系统重启后也可以通过redo log来恢复数据。

### 锁 ###

#### InnoDB中锁有哪些种类？ ####

*  表锁 & 行锁；
 *  共享锁 & 排它锁；
 *  意向共享锁 & 意向排它锁：意向锁为表级别的锁，当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁；
 *  自增锁：针对自增列自增长的一个特殊的表级别锁；
 *  记录锁：记录锁锁住的是具体的索引项；
 *  临键锁：临键锁锁住的是记录+区间（左开右闭】，临键锁（左开右闭】可以利用B+Tree从左至右连续的特性来避免幻读；
 *  间隙锁：间隙锁锁住的是数据不存在的区间（左开右开）。

#### 什么情况下会发生死锁？ ####

*  多个并发事务（2个或者以上）；
 *  每个事务都持有锁（或者是已经在等待锁）;
 *  每个事务都需要再继续持有锁；
 *  事务之间产生加锁的循环等待，形成死锁。

#### 如何避免死锁？ ####

*  类似的业务逻辑以固定的顺序访问表和行；
 *  大事务更倾向于死锁，如果业务允许，将大事务拆成小事务；
 *  在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率；
 *  涉及加锁的操作条件要索引。如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）。

### 分库分表 ###

#### 常见的分片策略有哪些？ ####

*  基于时间分片：按年、月、日等，适合存储账单、流水等，容易扩展，缺点是冷热不均）；
 *  基于范围分片：110000、1000020000等；
 *  基于地域分片：北京、上海等；
 *  Hash取模分片：存储与负载更均匀，缺点是不易扩展。

#### 订单表如何选择分片键（买家id、商家id）？ ####

*  可以考虑买家订单存一份、商家订单存一份，分别按不同字段分片（空间换时间）；
 *  如果重复字段多，可以考虑增加关系映射表，如订单表按照买家id分片，买家、商家关系表按照商家id分片。

[img]: https://img-blog.csdnimg.cn/ef5ffe87cc7f465eb8ad576a704ac855.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAS2VlcE1vdmluZysr,size_20,color_FFFFFF,t_70,g_se,x_16
[MySQL EXPLAIN_-CSDN]: https://hujinyang.blog.csdn.net/article/details/103877385