面试官：MySQL性能调优，你能提高多少效率？

青旅半醒 2024-03-01 03:32 61阅读 0赞

> 哈喽！大家好，我是奇哥，一位专门给面试官添堵的职业面试员  
> 文章持续更新，可以微信搜索【小奇JAVA面试】第一时间阅读，回复【资料】更有我为大家准备的福利哟！

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

*  面试
 *  MySQL索引底层数据结构
 *  MySQL索引优化
 *  MySQL索引覆盖
 *  MySQL索引下推
 *  MySQL索引失效
 *  EXPLAIN
 *  MySQL事务隔离级别
 *  MVCC机制
 *  总结

## 面试 ##

> 在一个晴朗的周日，我来到了一个陌生的园区（别问为什么是周日，问就是997，不过为了填饱肚子的打工人，只能明知山有虎、偏向虎山行），坐在陌生的会议室，等待HR小姐姐去叫面试官，此时我的心情和各位小伙伴一样五味杂陈，**担心面试官问的会不会很难？问到我的知识盲区我该怎么办？一会自我介绍的时候要不要吹一下我和奇哥的关系？**

> 突然门吱吱作响，伴随着办公室门的打开，我看到了一个胡子拉碴，睡眼朦胧的中年男子，这特么一看就没少加班啊！我还是给他添完堵就撤吧。

## MySQL索引底层数据结构 ##

**面试官：我看你简历上写的精通MySQL？（哼，面试官轻蔑的一笑）**

（看着面试官轻蔑的笑容，我忍不住拿出了我的MySQL入门书籍推给了他）

我：这本书我倒背如流，你随便提问，答不上来算我输，答上来你就要为你的轻蔑向我道歉。

![请添加图片描述][98dba9467735456ea94d044241de6005.png]  
（我的笑容逐渐自信。。。）  
![请添加图片描述][5c2362bb3cf44e74a21afb2bcc241f27.gif]

（此时面试官笑的更大声了，完全不在意我就坐在对面）

**面试官：哈哈哈、你这本书都写了MySQL入门了，你还敢说你精通MySQL，我随便问你一个问题就把你问住了，因为我问的问题都是你这本书上没有的**

我：那你问吧，是骡子是马咱拉出来溜溜。

**面试官：好，小伙子还挺硬气，那你说说MySQL索引的底层数据结构吧**

我：MySQL索引的底层数据结构是B+树数据结构（这有何难。。。）

**面试官：完了？详细介绍一下B+树的数据结构是什么样子的，不然我怎么知道你真懂假懂**

我：B+树有三个特性  
1、B+树是一个平衡多叉树，与平衡二叉树的每一个节点下面最多有两个子节点相比B+树每一个节点下面有多个子节点。  
2、B+树叶子节点（也就是最下面一层的没有子节点的节点）有一个双向链表，左右是为了方便范围查找（假如我找前100条数据，那么我找到第一条叶子节点的数据就可以从叶子节点直接向后取100个数据即可，不用再从根节点向下寻找）  
3、B+树的叶子节点有data数据（就是数据库中这一条所有的字段数据），非叶子节点只有索引数据。  
![在这里插入图片描述][2a88693189e1465db0d67f3a02cbab65.png]

**面试官：嗯，那你说一下B树和B+树的区别，为什么MySQL底层使用B+树而不使用B树呢**

我：（很明显啊！B+比B多一个+啊，年底了能拿A+的谁爱拿A呢，这一题过。。。）  
我们先来看一下B树的一个数据结构  
![在这里插入图片描述][c8702a932b8a429995013911fe32ae77.png]

很明显B树与B+树有两个地方不同，一个是叶子节点的双向链表，一个是B树不是只有叶子节点有data数据，而是所有的节点都有data数据。

**面试官：嗯。那为什么不用二叉树作为索引的底层结构而用B+树呢**

我：因为二叉树的特性造成根节点距离叶子节点的路径太长，假如一个7个节点的数据二叉树从根节点到叶子节点的距离为三。

![在这里插入图片描述][5a3092d2fa36489b83db074870b2d472.png]  
如果用B+树则距离为1就可以搞定（当然B+树一层不止7个节点，节点数量取决于一页数据能存放多少个节点）

![在这里插入图片描述][d19fcf6fa78e4db49efcfdb673bcafde.png]

**面试官：嗯，每一个节点都有data数据不是更好吗，不需要到达叶子节点就可以获取数据返回了，为什么B+树还要把其他节点的data数据去掉，只留叶子节点的data数据呢**

我：因为这里涉及到计算机中的IO操作，计算机**IO一次**只能拿一数据页的数据（姑且认为大小为64KB吧），如果每一个节点都有data数据，那么计算机IO一次可能只够拿一个节点出来，这样，可能IO一百次才能找到结果，如果其他节点不存储data数据，那么这个索引**占用空间就少**，IO一个可以拿出多个节点来，这样IO的次数就大大降低了，IO一次是比较**耗费性能**的，所以使用B+树就提高了性能。

**面试官：可以啊小伙子，有点东西，平时都怎么学习呀，回答的这么全面**

我：平时都是看看奇哥的《趣学编程》系列文章，文章简答又有趣，利用闲暇时间就慢慢得到了升华（此时真想给奇哥的文章点个赞，**拒绝白嫖**哦，不点赞就很坏~~）

**面试官：嗯。那你简单说一下聚集索引和非聚集索引是什么意思**

我：那我就由浅入深的简单说一下吧。

**聚集索引**：首先所谓聚集的含义是索引与data数据是否**相邻**，就是我找到索引以后在它**附近**就可以找到想要的data数据这就是聚集索引。

**非聚集索引**：非聚集索引就是我找到索引后，在它的附近找不到data数据。

这里我们根据之前的图想一下，之前的图叶子节点下面紧挨着就是data数据，这里肯定是聚集索引啊，那么什么情况下是非聚集索引呢。

在索引的字段是**非主键**的时候就是非聚集索引。

这里我来举个例子，如果一个student表中有主键id，姓名name，年龄age，住址add。这个时候我们给name字段建立一个索引，给add字段建立了一个索引，那么这个时候是不是有**两个**B+树的索引结构，那么意味着这两个索引结构的叶子节点都需要有data数据，那岂不是需要将name索引中的data数据**复制**一份出来给add索引。

那假如有100个字段都建立了索引，岂不是data要复制100遍。这个时候我就想了一个办法（这个办法不是我想的。。。我快要想出来了，被别人提前答出来了）能不能**只让一个**索引的子节点有data数据，其他索引的子节点**没有data数据**而是放有data数据的索引的地址呢。

这个时候就让哪个索引作为唯一拥有data数据的索引呢，这里很明显可以用**主键**嘛，因为主键正好是唯一的，其他字段都可以为多个，所以主键所建立的索引就是拥有data数据的聚集索引，而其他非主键字段建立的索引就是非聚集索引。

## MySQL索引优化 ##

**面试官：非常棒，看来奇哥的文章真不错呀，接下来讲一讲MySQL语句怎么写可以提高性能呢**

我：当然是建立索引啦，建立了索引犹如给书加上了目录，如鱼得水、如虎添翼、如。。。

**面试官：那索引是不是建立的越多越好呢**

我：当然不是。。。

**面试官：为什么？**

我：（因为一般面试官用这种口气问问题就是给你下套呢，答肯定不是就对了。。。机智如我）

因为每一个索引就是给索引字段建立一个**索引结构**，假如现在插入一条数据，那么这条数据也需要将字段建立到索引结构当中，就需要调整索引结构了，如果建立了100个索引，那么插入1条数据需要调整100个索引结构（数据库：我去你。的，瞎鸡。搞），那么性能就可想而知了。

但是不建立索引又不行，必须还得建立，那么应该怎么建立呢，就是把经常要用到的查询条件的字段建立一个联合索引，这样用一个索引树可以将多个字段建立了索引。

**面试官：嗯。说到了联合索引，如果我建立的联合索引是A、B、C这三个字段，那么我查询的时候条件是A、B那么这个索引还有效果吗？**

我：有效果。

**面试官：为什么呢？**  
![请添加图片描述][6b174ae783344418b2f0776f10ec88e7.png]

我：因为最左前缀原则，假如当我们给姓名、年龄、性别三个字段建立了索引，那么从左边先开始的字段才可以索引有效果。

![在这里插入图片描述][559d634b5d5f43aa9491eca074995305.png]  
**面试官：那如果我查A和C呢？**

我：那么只有A有索引效果，查询的字段从索引的最左边开始向右查找，如果中间断了，那么后面的索引字段就失去效果了。

## MySQL索引覆盖 ##

**面试官：嗯。讲一下MySQL的索引覆盖是怎么回事吧**

我：所谓索引覆盖就是用索引字段来覆盖要查询的字段。

假如我们要查询两个字段，name和age，我们的sql语句为

select name,age from student where name=‘张三’ and age=20

假如我们这个时候只有name建立了索引，这个时候我们需要在索引中找到name等于张三的这些数据，并回表（就是从普通索引中找不全所要查询的所有字段，那么需要回表再去主键聚簇索引中寻找，因为聚簇索引中有全量的data数据）。

这个时候我们可以看到我们需要查询的字段只有name，age两个字段，这个时候我们可以将name，age这两个字段做一个**联合索引**，这个时候我们直接通过联合索引就可以找到所要**查询出的字段**了。

请注意如果是 select name,age,add from student where name=‘张三’ and age=20；这个时候由于刚刚的联合索引只有name，age两个字段，没有add字段，所以这种情况又要**回表**查询，这种情况就没有索引覆盖了。

所以我们sql语句要尽可能的**查询出少量**的字段，就是用哪个字段就查询哪个字段，更要避免select \* 的这种情况。

## MySQL索引下推 ##

**面试官：嗯。非常不错，那你再说一下什么是索引下推吧**

我：（我特么。。。精通MySQL就这么被问啊，早知道不写精通MySQL了，累死我了。。。可以来个赞给我续续命吗家人们）

首先索引下推是MySQL5.6版本引入的一种优化手段，说白了就是优化了一下，具体优化后有了哪些效果呢，切听帅气的奇哥给你娓娓道来。

关键点：  
1、第一个字段为非等值字段。  
2、查询的字段建立了联合索引。  
前提：  
name和age建立了联合索引

例如 select name,age from student where name like ‘%李\*%’ and age=20；

这个时候如果在5.6之前，我们会在联合索引中先找到所有name为李开头的数据id（主键），然后再去主键索引（聚集索引）中找age为20的数据的id拿回来，然后将最后合并的数据根据id再去聚集索引中找，这样其实是两次回表查询。

而在5.6之后，我们在联合索引中就直接将name为李开头的和age等于20的数据id筛选出来了，然后再去聚集索引中查询，这样就只进行了一次回表查询。

总结：5.6之前如果查询字段为非等值字段，那么后面的查询条件就回去聚集索引中进行判断，5.6之后非等值字段后面的查询条件在当前非聚集索引中也可以进行判断。

## MySQL索引失效 ##

**面试官：嗯。非常不错，那你能说一下索引在什么情况下会失效吗？**

我：在特么没有建立索引的情况下会失效。。。

**面试官：嗯。你等我找一下棍子**

我：额，在如下这几种情况下会失效。

1、在使用不等于！= 或者<> 这样的会失效。  
2、在使用不包含 not in ， 不存在 not exists 这样的会失效。  
3、在使用空 is null，不为空 is not null 这样的会失效。  
4、在使用小于 <、大于 >、<=、 >= 这些的时候，mysql优化器会根据索引比例、表的数据量大小等因素来决定走不走索引。

## EXPLAIN ##

**面试官：嗯。那我写了一条sql，我怎么知道这条sql有没有走索引呢**

我：使用explain解释器来查看，在sql语句前面加上explain就可以来查看

![在这里插入图片描述][5e667d52f67646fd981aa6411cfe64f3.png]  
explain中有多列，我们直接来看type这一列，这一列表示**访问类型**，即MySQL决定以哪种形式来查找表中的行，是根据索引还是全表扫描，表示查找数据行记录的大概范围。

type中的数据类型从优到差依次为：

**system > const > eq\_ref > ref > range > index >ALL**

当我们写了一条sql语句发现他的type是ALL的时候我们就要考虑一下怎么优化一下了，因为ALL是最差的，我们就需要琢磨一下怎么优化，当然优化到system是最好的，但是一般不会优化到这种程度，你只要前进一小步对于整个系统来说就是文明一大步。。。

## MySQL事务隔离级别 ##

**面试官：可以可以，回答的不错，不过你是精通MySQL，那我必须得全面的问你MySQL的相关知识，你再坚持坚持，和我再大战个三百回合**

我：（我特么谢谢您。。。）

**面试官：数据库有哪些事务隔离级别，MySQL使用了哪种级别呢？**

我：数据库有四种事务隔离级别

> 读未提交  
> 读已提交  
> 可重复读  
> 可串行化

MySQL默认是可重复读事务隔离级别

**面试官：嗯。那MySQL有哪些锁呢？**

我：从颗粒度来分，MySQL有表锁和行锁。

表锁：每次操作锁住整张表，开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突的概率最低。

行锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

## MVCC机制 ##

**面试官：嗯。那你知道MVCC机制吗，他的原理是什么？**

我：（这特么好难讲明白的。。。）那我就简单的说一下。

MVCC（Multi-Version Concurrency Control）是多版本并发控制，是在多个事务情况下可以保证每个事物之间相互隔离，MVCC机制适用于读已提交和可重复读这两个事务隔离级别。

MVCC机制中有最重要的两部分：

undo日志版本链：在一行数据被多个事务依次修改过后，每次的修改记录都会保存到undo日志版本链中，用于回滚操作。

一致性视图：read-view：每一个事务开启后，执行任何查询sql时就会生成当前事务的一致性视图。这个视图是由查询的时候所有未提交的事务id数组和已创建的最大事务id组成。

总结：MySQL通过是可重复读的，所以当一个事务执行第一个查询语句的时候就生成了一个一致性视图，当第二个事务修改了这条数据够，第一个事务查询的还是原来的数据，因为第一个事务当时查询的时候生成了一致性视图，这个一致性视图对于第一个事务来说没有变化，所以查询出来的数据也没有变化。

**面试官：小伙子真厉害啊，我这边没有什么要问的了，你还有什么问题要问（面试官两眼放光）**

我：咱们公司分配女朋友吗？

未完待续。。。

## 总结 ##

MySQL是及基础又重要的一项技术，所以大家要收藏后认真反复的去学习，如果觉得我的文章还不错的话就点个赞吧，另外可以微信搜索【小奇JAVA面试】阅读更多的好文章，获取我为大家准备的资料。

[98dba9467735456ea94d044241de6005.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/1c29d11dd876484ab7a946d4ecefd8b5.png
[5c2362bb3cf44e74a21afb2bcc241f27.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/38abceac09d04fedb0d5e26ec92742a3.png
[2a88693189e1465db0d67f3a02cbab65.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/246c155b96f748e98248a9524d7480ef.png
[c8702a932b8a429995013911fe32ae77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/6dc5e7f8b3384f51886e7f424baa0453.png
[5a3092d2fa36489b83db074870b2d472.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/4be7323d385a4bc29a1f84878bc22137.png
[d19fcf6fa78e4db49efcfdb673bcafde.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/61cb962c3b8446ba945299aba960a4c9.png
[6b174ae783344418b2f0776f10ec88e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/80ddc30c91654ab6a97a6686f66fc523.png
[559d634b5d5f43aa9491eca074995305.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/14d56539bd2c4f018053288cd8f2c1a1.png
[5e667d52f67646fd981aa6411cfe64f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/01/99ba35fb69b54046bb09d3b5a7212495.png