面试官：MySQL索引底层数据结构原理与性能调优，你能回答多少？

哈喽！大家好，我是小奇，一位不靠谱的程序员
小奇打算以轻松幽默的对话方式来分享一些技术，如果你觉得通过小奇的文章学到了东西，那就给小奇一个赞吧
文章持续更新，可以微信搜索【小奇JAVA面试】第一时间阅读，回复【资料】更有我为大家准备的福利哟！

前言

作为一名Java程序员，MySQL底层的一些原理是我们不必学会就可以搬砖工作的一种技能点，但是小奇为什么还要讲一下呢？难道就是为了浪费大家1分钟的宝贵时间，一个人1分钟，50万人就是1年，5000万人就是100年，赚了，小奇以一己之力成功搞挂一个人（血赚）。

当然不是，并且小奇的文章也没有那么多人看，最多也就浪费个肾吧。

学习MySQL底层原理是因为面试官要问啊！，所以我们就要学，什么？不实用的你不学？那邻居小奇可要使劲学啦，到时候面试官只要小奇不要你。

至于你问为什么面试官要问MySQL底层原理呢，这个。。。我把这次机会留给你，下次你面试的时候面试官问：“讲一下MySQL底层原理”。你：“面试官你好，请问为什么你要问MySQL底层原理呢，你给我台电脑，我五分钟给你搭建好图书管理系统他不香吗，咱们键盘上见真章”。这时面试官就会告诉你答案，你就可以把答案打在评论区，让小奇以及众多小伙伴一起知道一下到底为什么要问？

面试

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

一位英俊潇洒，眼神犀利的面试官走了进来，看到他那犀利、仿佛能看穿一切的眼神 ，我在想要不然一会就不要20k了，要8k得了，这个面试官一看就不好糊弄啊，但是我想起来我来之前刚看了小奇的趣学编程系列，我已经完全学会了小奇的精髓，我顿时就来了底气，决定一会要30k，不给就学小奇赖着不走（哈哈）

面试官：小奇是吧，带简历了吗？

我：没带，现在彩印两块一张，我简历五张，每次面试都要花费十块，我朋友说了还没工作就先让你掏钱的工作不要去。

面试官：。。。那你靠什么来征服我，让我录用你

我：气质？

（此时面试官并没有叫保安，而是从门后拿出了恭候我多时的棍子，我瞬间怂了）

（我只好从我的双肩包中拿出了我从上午没有面试通过的其他公司面试官手中要回的简历，上午的情形是这样的，上午的面试官：今天的面试就到这吧，回去等通知吧！我：面试官你好，如果贵公司不打算录取我的话，能不能把我的纸质简历还给我，我下午还有一家面试。上午的面试官：我说你的简历怎么皱皱巴巴，原来你一直在循环利用啊！这个症状出现多久了？我：半拉月了。。。）

（当我把皱皱巴巴的简历交给面试官后，这场面试才得以继续进行。。。）

MySQL索引底层数据结构

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

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

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

（我的笑容逐渐自信。。。）

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

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

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

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

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

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

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

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

我：（很明显啊！B+比B多一个+啊，年底了能拿A+的谁爱拿A呢，这一题过。。。）
我们先来看一下B树的一个数据结构

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

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

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

如果用B+树则距离为1就可以搞定（当然B+树一层不止7个节点，节点数量取决于一页数据能存放多少个节点）

面试官：嗯，每一个节点都有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那么这个索引还有效果吗？

我：有效果。

面试官：为什么呢？

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

面试官：那如果我查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就可以来查看

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

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

我：额。。。面试官这个我的纸质简历可以给我吗，可以不往我的简历上写写画画吗，我明天的面试还要用。

面试官：还面啥别的公司啊，就来我这吧，条件随便开

我：那就100k吧（此时面试官又拿起了他准备好的棍子）

面试官：你要是不来就给我推荐一下，让别人来我这面试一下

我：你先好好学习一下MySQL吧，今天幸亏只是我来了，如果是小奇的忠实读者来了，你将会被虐的很惨的。（我转身留下了帅气的背影，而面试官落寞无神的呆呆的坐在那里，仿佛一个亿离他而去。。。）

总结

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

本文参与了 SegmentFault 思否征文「如何“反杀”面试官？」，欢迎正在阅读的你也加入。