引言

从开设《MySQL专栏》至今，前面二十多个大章节中叨叨絮絮了许多内容，看到这里也意味着本专栏即将接近尾声，由于前面的每章内容都较为全面，因此每章的篇幅都并不算短，这对于一些想要面试前作巩固复习的小伙伴并不友好，毕竟篇幅过长很难将核心知识点提炼出来，因此本章会重点提炼核心内容，将专栏中涉及的所有重点凝练成一份 《MySQL面试通关秘籍》 。
面试 由于主要讲解MySQL面试的核心知识点，因此不会对某些技术细节进行深入讲解，但文中提到的技术点都会附上具体讲解的链接，因而当大家看到自己不理解的面试点时，可直接戳进对应的链接中做深入研究。

同时，为了避免出现背诵"面试八股文"那样的枯燥感，接下来通篇都会采用对话面试官的形式阐述！当然，看完如若感觉对你面试会有些许帮助（现在不用以后迟早会用的），记得点赞、收藏、关注三连支持一下噢~

PS：个人编写的《技术人求职指南》小册已完结，其中从技术总结开始，到制定期望、技术突击、简历优化、面试准备、面试技巧、谈薪技巧、面试复盘、选Offer方法、新人入职、进阶提升、职业规划、技术管理、涨薪跳槽、仲裁赔偿、副业兼职......，为大家打造了一套"从求职到跳槽"的一条龙服务，同时也为诸位准备了七折优惠码：3DoleNaE，近期需要找工作的小伙伴可以复制链接了解详情：https://s.juejin.cn/ds/USoa2R3/

一、好戏开场：MySQL底层架构与库表设计

• • 自我感慨 ：闭关修炼半载、精通MySQL的我又回来啦！虽然迄今为止我的面试大写着失败，但这并不妨碍我继续失败！

• • 开幕场景：此时正挺着啤酒肚迎面向我走来的性感帅哥，正是本次负责考验我的面试官，微风吹过他那一头浓密的秀发，从空气中我嗅到了一丝并非强者的气息，我内心不由自主的称道："呵，真弱"！

• • 震惊的我 ：刷~，很快面试官就在我面前缓缓落座！突然！他朝我微微一笑，接着将双手举过头顶，在我一副震惊的神色中，从他那儿48码的大头上，渐渐取下了一顶假发帽，露出了在阳光下略带反光的地中海！该死，这气息的压迫感....好强大！

• • 面试官温文尔雅道：候选者早上好呀，请先做个简单的自我介绍。

• • 我 ：咳咳，好的。我叫竹子，是一位具有三十年开发经验的三十三岁程序员，精通Java、Golang、Rust、PHP、Scala、C/C++、Spring、Redis....MySQL等技术栈的单词拼写！

• • 面试官 ：啥？精通MySQL？好，那我接下来考考你。

• • 我 ：不是，精通MySQL单词的拼写......

• • 面试官 ：先跟我说说你理解中的MySQL底层架构哈。

• • 我内心：靠，难怪当初在流水线做娃娃时，组长非得安排我去装头，原来我不是装逼那块料啊。

• • 面试官：竹子先生，为了有效开展本场面试，请尽快开始你的回答！

• • 我 ：我个人理解中的MySQL整体架构，自顶向下分为连接层、服务层、引擎层以及文件层，其作用如下：

  • • ①连接层：主要是指数据库连接池，会负责处理所有客户端接入的工作，如用户登录、授权、连接等。

  • • ②服务层：这是最重要的一层，所有跨引擎的操作都会放在这里完成，如SQL解析、结果合并、执行计划生成等。

  • • ③引擎层：这依旧是MySQL较为重要的一层，服务层主要是制定执行计划和等待结果，但读写数据的具体操作都需要通过引擎层来完成，引擎层决定着表数据读写方式和存储方式。

  • • ④文件层：这是MySQL的基础层，对上层服务提供最基础的文件服务，如日志、数据、索引等文件的支持。

  • • 顺手画了个流程图，并甩给了面试官，如下：

  • •

    MySQL整体架构

• • 面试官 ：嗯，回答的还算不错，你具体说说客户端是怎么和MySQL服务建立连接的呢？

• • 我 ：这个会比较复杂一些，客户端与MySQL建立连接时，会先经过TCP/IP的三次握手过程，如果采用了加密连接的方式，还会经过SSL的握手过程，握手完成后MySQL和客户端会建立session连接。

• • 我 ：接着MySQL会查询自身的mysql.user表，来验证客户端的用户名和密码，如果有误则会报错。

• • 我：在都正确的情况下，首先会根据登录的用户名，对客户端连接进行授权，完成后即表示连接建立成功，后续的交互会采用半全工模式通信，也就是同一时刻内，单方要么只能发送数据，要么只能接受数据。

• • 面试官：客户端获取到的数据库连接本质是什么？每个连接用完后会立马被丢弃吗？

• • 我 ：数据库连接的本质是一条条线程，比如当一个客户端和MySQL成功建立连接之后，MySQL会先保存客户端的网络连接信息，即session会话信息。

• • 我 ：然后为了维护与客户端之间的连接，在内部都会开启一条条的线程绑定对应的会话信息，以此来维护现有的连接，当客户端发来一条SQL语句时，维护对应连接的线程则会去执行，执行过程中也会由对应的线程处理结果集并返回。

• • 我 ：当执行完客户端的SQL语句后，MySQL默认会将连接维护八小时，在这八小时内不会销毁，除非客户端主动发送了quit指令，这时MySQL才会主动销毁连接，但这里的销毁也并非真正意义上的销毁。

• • 我 ：因为线程在任何系统中都属于珍贵资源，频繁创建和销毁的代价比较高，当客户端主动退出连接后，MySQL只会将对应线程绑定的会话信息清空，然后将"空闲"的线程放入自身的连接池当中，以备下次客户端连接时使用。

• • 面试官：可以啊，你小子挺细，那接着说说解析器和优化器的作用。

• • 我 ：解析器和优化器一般是所有语言都具备的组件，前者主要用来词义、语义分析和语法树生成，说人话就是检测SQL语法是否正确。

• • 我 ：优化器主要会对解析器生成的语法树，选出一套SQL执行的最优方案，如选择合适的索引、选择合适的join方式等，对于优化器最终选择的执行计划可以通过explain工具来查看。

• • 面试官 ：嗯，基础还算扎实，那你再说说MySQL执行是如何执行一条SQL语句的呢？

• • 我：这要看具体情况，毕竟写语句和读语句的执行流程会存在些许差异，请问具体是哪个呢？

• • 面试官内心OS：哟，你小子给我显摆上了，小样，看我整不整你就完事！

• • 面试官：都给我先简单的说一遍。

• • 我：好的，其实两者大体上并无差异，主要区别在于一些细节上的变化，先说说读语句的执行流程吧。

• • 读语句：

  • • 错误：抛出1064错误码及相关的语法错误信息。

  • • 正确：将SQL语句交给优化器处理，进入第④步。

  • • ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。

  • • ②SQL接口在缓存（QueryCache）中根据哈希值检索数据，如果缓存中有则直接返回数据。

  • • ③缓存中未命中时会将SQL交给解析器，解析器会判断SQL语句是否正确：

  • • ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。

  • • ⑤工作线程根据执行计划，调用存储引擎所提供的API获取数据。

  • • ⑥存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据....）。

  • • ⑦发送磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。

  • • ⑧SQL接口会对所有的结果集进行处理（剔除列、合并数据....）并返回。

  • • 我嘴里一边念叨，同时拿出了纸笔，然后唰唰两下画出了一幅大体的流程图，如下：

  • •

    SQL执行过程

• • 写语句：

  • • 存在：

  • • 不存在：

  • • ⑦直接对缓冲区中的数据进行写操作。

  • • ⑧然后等待后台线程将数据刷写到磁盘。

  • • ⑦根据执行计划，调用存储引擎的API。

  • • ⑧发生磁盘IO，读取磁盘中的数据做写操作。

  • • 错误：抛出1064错误码及相关的语法错误信息。

  • • 正确：将SQL语句交给优化器处理，进入第④步。

  • • ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。

  • • ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。

  • • ③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：

  • • ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。

  • • ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。

  • • ⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：

  • • ⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。

  • • ⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。

  • • 同时大笔一挥，又在纸上画出了一幅流程图，如下：

  • •

    写SQL执行流程

• • 面试官内心OS：就知道你小子会显摆，说的越详细坑就越多，你小子等着吧，嘿嘿嘿.....

• • 面试官：听你刚刚说读语句的执行流程时，似乎提到了一个叫做查询缓存的东西，你确定这玩意一定在吗？

• • 我：不用怀疑，我敢说！它....不一定在。

• • 面试官：哦！？什么情况下不在呢？

• • 我 ：手动关闭的情况下不会在，8.0之后的版本中想开也开不了，因为被移除了。

• • 面试官：那你说说官方为什么要移除呢？缓存不是能很好的提升查询性能吗？

• • 我 ：缓存的确能够很好的提升查询性能，但MySQL的查询缓存就一言难尽，有多方面原因吧，如下：

  • • ①缓存命中率低：几乎大部分SQL都无法从查询缓存中获得数据。

  • • ②占用内存高：将大量查询结果放入到内存中，会占用至少几百MB的内存。

  • • ③增加查询步骤：查询表之前会先查一次缓存，查询后会将结果放入缓存，额外多几步开销。

  • • ④缓存维护成本不小，需要LRU算法淘汰缓存，同时每次更新/插入/删除数据时，都要清空缓存中对应的数据。

  • • ⑤查询缓存是专门为MyISAM引擎设计的，而InnoDB构建的缓冲区完全具备查询缓存的作用。

  • • ⑥同时项目中一般都会用Redis做业务缓存，能来到MySQL查询的语句十有八九是要走磁盘的，因此查询缓存的存在，反而弊大于利。

• • 面试官：嗯，回答的不错，那说说这个查询缓存和你前面提到的缓冲，两者有什么区别呢？

• • 我：查询缓存只能给读语句使用，而缓冲读写语句都能用（缓冲区的知识后面聊）。

• • 面试官 ：你刚刚说的是SQL执行流程，那你能不能跟我说一下SQL执行之前会发生什么呢？

• • 我 ：当然可以，程序上线后，任何一条SQL语句的诞生，都源自于平台用户的操作，用户发送的请求最终会转变为一条条具体的SQL语句。

• • 我 ：生成SQL之后接着会去配置好的数据库连接池，如Druid中获取一个数据库连接，然后发给MySQL执行，但执行前还会先判断当前连接的用户，是否具备SQL要操作的表权限。

• • 面试官 ：不错，那你刚刚提到的Druid这类连接池，和MySQL自己维护的连接池，会不会冲突呢？

• • 我：不会呀，虽然两个都叫连接池，但一个是位于客户端，一个是位于服务端，两者的区别在于：

  • • 客户端连接池：减少多次创建数据库连接时，频繁出现的TCP三次握手、四次挥手、SSL握手等过程。

  • • 服务端连接池：减少多次创建数据库连接时，频繁创建和销毁工作线程造成的资源开销。

• • 我 ：同时这两个连接池都能带来不小的速度提升呢，前者避免了等待网络握手的时间，后者避免了等待线程创建的时间，如果没有这些连接池，每次SQL执行时，光网络握手和创建线程就需要耗费不少时间。

• • 面试官：嗯嗯，那在高并发情况下，是不是把客户端连接池的最大连接数，调的越大越好呢？

• • 我 ：理论上是的，因为连接数越大，代表同一时间可以执行更多的SQL语句，也就意味着同一时间可以处理更多的用户请求，但理想很丰满，现实很骨感，由于硬件配置的原因，这种做法是不行的。

• • 面试官：此话怎讲呐？谈谈你的看法。

• • 我 ：因为一个数据库连接，本质上对端都需要各自开启一条线程维护，将最大连接数配置成100，这也意味着应用程序和MySQL各自都需要开启100条线程维护这些连接。

• • 我 ：如果一台八核的机器，因为只有八个核心，无法在同一时刻内支持这么多线程执行，所以OS只能频繁的在每条线程之间切换CPU资源，确保每条线程能够正常运转。

• • 我 ：这最终会导致：每条线程等待CPU资源的总时长，反而会超出实际执行SQL的时间，所以根据机器的硬件来配置最大线程数，这才是最合理的方案，目前业界主流的配置计算公式为：CPU核心数*2，如果硬盘材质是SSD的，那么还可以再加个一，这属于最佳配置。

• • 面试官 ：可以嘛，看样子你还懂性能调优呀，这都直接给我聊到连接层调优来了。

• • 面试官 ：你再跟我说说，MySQL一条线程执行完成后，它是如何知道自己该向谁返回数据的？

• • 我 ：这倒不难，之前不是说过数据库连接对应的工作线程，自身会绑定客户端的会话信息嘛？这个会话信息就包含了客户端的IP地址、端口等信息，当一条线程执行完成后，只需要根据这个地址去封装数据报文就好啦，如果要返回的结果集比较大，MySQL会把一个大的数据包拆分成多个小的数据报文分批返回。

• • 面试官：嗯呢，这块就此打住，有了解过数据库的三范式吗？它是做什么用的呢？

• • 我：了解过啊，三范式主要是在设计库表结构时，需要遵循的一些原理原则：

  • • 第一范式：要求一张表的每个字段，设计时都必须具备原子性，即单个列只表示一个值，不可再分。

  • • 第二范式：要求一张表的所有字段，都必须依赖于主键，也就是一张表只能存同一个业务属性的字段。

  • • 第三范式：要求表中每一列数据不能与主键之外的字段有直接关系，也就是表中只允许一个主属性存在。

  • • 除开上述基本的三范式外，还有一些用的比较少的巴斯-科德范式/3.5范式、第四范式、第五范式。

• • 面试官：那在设计库表结构的时候，一定要遵循这些范式原则去设计吗？

• • 我 ：不需要，范式只是设计库表的方法论，但如若业务需要或性能需要，不遵循范式设计也可以，这种不遵循范式设计的手段则被称之为反范式设计。

• • 面试官：小伙子，很不错嘛！

• • 我微微一抬手道：咳咳，低调低调，基操勿六~

• • 面试官：........

• • 我：咳咳，您接着问~

二、小试牛刀：细聊MySQL索引机制

• • 面试官 ：你知道MySQL是如何从磁盘中按条件读取数据的吗？

• • 我 ：这个很简单，MySQL会默认会触发磁盘IO来读取表数据，但InnoDB引擎读取时，会利用局部性原理，也就是预读思想，一次IO会读取16KB磁盘数据放入内存，接着和SQL语句的条件做对比，符合条件的留在内存，其他的丢弃，然后继续去磁盘中读其他的表数据，直到把整张表的数据文件都找一次后，最后才会把符合条件的数据返回，这个过程也被称作全表扫描。

• • 面试官：你这小嘴叭叭太多了，听的脑瓜疼，麻烦给我讲简单点。

• • 我：.....，相当于小学读书，在字典中找一个汉字，是靠一页页的翻，最终找到需要的目标汉字。

• • 面试官：哦，那怎么才能快一点呢？

• • 我：字典不是有那个目录索引页么，通过音节、偏旁等方式查找就行。

• • 面试官 ：咳，我是问MySQL查数据，怎么才能更快一点。

• • 我 ：同样的思想，书籍有目录，MySQL中也有索引，我们可以在经常查询的字段上创建索引，查询时就能直接走索引查找了。

• • 面试官 ：那MySQL中有哪些索引呢？

• • 我：这要看以啥维度来分，不同维度可以划分为不同的索引叫法，比如：

  • • 以数据结构来分：Hash索引、B+Tree索引、R-Tree索引、T-Tree索引。

  • • 以字段数量来分：单列索引（由单个字段组成）、联合索引（由多个字段组成）、前缀索引（由单/多个字段的前面一部分组成）。

  • • 以功能逻辑来分：普通索引、唯一索引、主键索引、全文索引、空间索引。

  • • 以存储方式来分：聚簇索引、非聚簇索引。

• • 面试官 ：小伙子就是不一样，整这么细~，如果我线上业务经常使用like模糊查询，你有好办法优化不？

• • 我 ：很简单呀，可以使用ES这类搜索引擎来完成模糊查询工作，如果不想用，则可在对应字段上建立全文索引，全文索引会比like查询的效率更高，并且支持全模糊、左模糊查询走索引。

• • 面试官 ：你知道MySQL索引的底层是什么数据结构么？

• • 我 ：这要根据具体的存储引擎来决定，常用引擎一般支持Hash、B+Tree两种结构，通常是B+树。

• • 面试官 ：嗯嗯，那为什么MySQL不选择二叉平衡树、红黑树、B树等结构呢？

• • 我：您所提到的这些数据结构都属于树结构，选择这些树结构作为索引的底层实现，在数据量较大的情况下，尤其是索引字段具备顺序递增特性时，索引树的高度会呈直线型增长，也就是树高会变得很大。

• • 我 ：而走索引查询时，一层树高就需要触发一次磁盘IO，索引树的树高决定着磁盘IO的次数，磁盘IO的次数越多，意味着查询耗时、资源开销会更大，所以您所提及到的这些树结构，并不适合作为索引结构的实现。

• • 面试官 ：我提到的前两个树结构的确如此，但为何B树结构也不合适呢？它单个叶子节点不是会存储多个数据吗？

• • 我 ：没错，但关系型数据库经常会执行一些范围查询操作，而普通的B树结构，各个叶子节点之间没有指针连接，所以对于范围查询支持不友好。

• • 我 ：而B+树则不同，每个叶子节点都会有一根指向下个节点的指针，范围查询时可以基于这些指针快捷查找。

• • 我 ：不过值得一提的是：MySQL也并未选择传统的B+Tree结构来实现索引，而是又对其进行了改良，毕竟B+树只有指向下个节点的指针，所以只支持正向范围查询，而不支持反向范围查询。

• • 我 ：因此MySQL在传统的B+Tree结构中，又在每个节点中加了一个指向上个节点的指针，这样做之后也支持反向范围查询。

• • 面试官 ：你的意思是MySQL索引用了变种B+Tree咯？再问一下你们项目一般选什么字段作为主键？

• • 我 ：通常会选一个数值类型、且具备顺序递增特性的字段作为主键，如果表中没有符合条件的字段，则通常会额外设计一个跟业务无关的ID字段作为主键。

• • 面试官：哦？为什么宁愿额外设计也不从表中选择其他字段呢？

• • 我：这主要是为了维护索引的树结构，如果选择值无序的字段作为索引键，这绝对会造成索引树频繁的发生分裂，从而导致索引的性能下降。

• • 面试官：嗯哼？为什么索引树分裂会导致性能下降呢？而顺序自增又能维护树结构呢？

• • 我内心 ：......，希望你除开嘴上一口一个B树的问之外，心里最好也要有个B树。

• • 我：因为当一个叶子节点存满后，此时又新增一个新的值，也要插入到这个节点中，那么该节点中的最后一个数据只能往后面的节点移动，而后面的节点又需要继续往后移动，最终才能给新增的值腾出位置。

• • 我：因为这个过程索引树的结构在发生变更，所以会加锁防止其他事务读到不对的数据。而挪动数据、加锁阻塞都需要时间，因此树分裂会导致索引下降。

• • 我：但如果选择按序递增的字段就不会有这个问题，毕竟每次新增的值，都会直接放到最后面去插入，并不会导致树结构发生分裂。

• • 面试官 ：不错不错，你再跟我说说聚簇索引和非聚簇索引的区别。

• • 我：聚簇索引是物理空间+逻辑上的连续，索引数据和表数据会放在磁盘的同一块位置上存储；而非聚簇索引则是单纯逻辑上的连续，索引数据和表数据是分开的，通过地址指针的形式指向数据。

• • 我 ：同时InnoDB引擎的非聚簇索引和传统的非聚簇索引不同，例如MyISAM引擎中的非聚簇索引，索引值存储的是行数据的磁盘地址。

• • 我 ：而InnoDB的非聚簇索引的索引值，因为表数据和聚簇索引键存储在一起，存储的则是对应行数据的聚簇索引键。

• • 面试官：你既然都聊到了这个，一定知道啥是回表问题吧？

• • 我 ：知道的，回表查询指需要经过两次完整的查询过程后，才能够读取到目标数据，这也是InnoDB引擎独有的坏毛病，基于非聚簇索引/次级索引查找数据时，从索引中查找索引值后，会接着再通过查到的聚簇索引键再查一次聚簇索引，从而得到最终需要的行数据。

• • 面试官：嗯嗯，那有什么好的办法减少回表查询吗？

• • 我 ：有的，尽量创建联合索引来代替单列索引，再结合查询数据时不要用*来表示所有字段，这样可以重复利用索引覆盖机制来获取数据，从而减少回表查询的次数。

• • 面试官：你提到的这个索引覆盖机制，可以展开讲讲吗？

• • 我 ：这个是MySQL的一种优化手段，假设通过name、sex、age三个字段建立了一个联合索引，当基于联合索引查询时只需要返回name、age，因为这两个字段值在联合索引中都包含了，那就可以直接从索引键中读取数据返回。

• • 我 ：但如果使用*时，因为联合索引中不具备完整数据，所以只能触发回表动作得到完整的行数据。

• • 面试官 ：那你知道创建一个索引之后，MySQL会干什么工作么？

• • 我：分情况，如果是基于空表创建索引，会直接根据创建的索引类型、存储引擎、字段类型等信息，在本地的表文件/索引文件中，直接创建一个树结构即可。但如果表中有数据，情况会略微复杂一些，如下：

  • • InnoDB主键索引：对.ibd文件中的表数据进行重构，将索引键和行数据调整到一块区域中存储。

  • • InnoDB次级索引：因为有聚簇索引，将非聚簇索引的索引值，与行数据对应的聚簇索引键的关联起来。

  • • MyISAM：由于表数据在单独的.MYD文件中，因此可以直接以磁盘指针的关联表数据。

  • • B+Tree：对索引字段的值进行排序，按照顺序组成B+树结构。

  • • Hash：对索引字段的值进行哈希计算，处理相应的哈希冲突，方便后续查找。

  • • .......

  • • 唯一索引：判断索引字段的每个值是否存在重复值，如果有则抛出错误码和信息。

  • • 主键索引：判断主键字段的每个值是否重复、是否有空值，有则抛出错误信息。

  • • 全文索引：判断索引字段的数据类型是否为文本，对索引字段的值进行分词处理。

  • • 前缀索引：对于索引字段的值进行截取工作，选用指定范围的值作为索引键。

  • • 联合索引：对于组成联合索引的多个列进行值拼接，组成多列索引键。

  • • ........

  • • ①首先根据索引类型，对索引字段的数据进行对应处理：

  • • ②接着根据索引的数据结构，再对索引字段的数据进行处理：

  • • ③根据表的存储引擎、索引字段再进行相应处理：

  • • 经过上述处理后，创建索引就完成啦！

• • 面试官 ：讲的蛮好，再奖励你回答一下：基于索引的查找数据过程。

• • 我：这个还算比较简单，面试官你认真听好咯！

  • • InnoDB聚簇索引：直接从索引树中得到行数据，因为行数据和聚簇索引存储在一块。

  • • InnoDB次级索引：看是否能够使用索引覆盖机制获取数据，不行则触发回表动作得到数据。

  • • MyISAM的索引：根据索引键中记录的磁盘地址，直接去磁盘中读取行数据。

  • • ①首先根据查询语句的条件字段，去内存中找到对应的索引根节点。

  • • ②通过根节点中记录的叶节点地址，逐步去遍历查找索引树，最终定位到目标数据所在的叶子节点。

  • • ③但遍历索引树的过程，采用的是二分查找法，拿到一个叶节点后，如果SQL条件比它大，会去继续读取右边的叶节点，反之则读取左边的叶节点，然后再进行判断。

  • • ④使用二分查找法，找到一个目标数据后，这里会根据不同的索引，来执行不同的操作：

  • • ⑤读取到一个目标数据后，如果是基于主键/唯一索引在查询，则会立马停止查找，如果是普通索引则会继续向下遍历。

  • • ⑥如果是范围查询操作，会直接根据叶子节点的前后指针，获取其他的索引键数据，然后重复第④步，得到目标行数据。

• • 面试官 ：可以，再说说写入语句执行时，会对索引产生什么影响呢？

• • 我：好的，但插入、删除、修改数据时，都会存在细微不同，我都简单说说吧：

  • • 和删操作类似，先找到数据，再改行数据，再改聚簇索引键，再改次级索引数据。

  • • 会先根据删除的条件查找索引树，接着去聚簇索引树找到对应的行数据，先删其他次级索引的数据，接着再删行数据和聚簇索引键。

  • • 如果索引字段具备自增特性，直接把插入的字段数据作为索引键，追加到索引树最后一个节点存储。

  • • 如果索引字段是无序的，则先对其排序计算（字符串转换为ascii码），计算出一个位置并插入。

  • • 增：

  • • 删：

  • • 改：

• • 面试官：嗯嗯，自己有了解过索引的最左匹配原则么？

• • 我 ：索引最左匹配原则是基于联合索引而言的，好比一个联合索引由A、B、C三个字段组成，那么在写SQL语句时，最好按照索引字段的顺序来使用索引，如果写的SQL中不包含第一个A字段，一般都无法使用这个联合索引查询数据。

• • 我 ：同时，如果查询语句中使用了A、C字段，但没有使用B字段，也无法完全利用联合索引。

• • 我 ：因为MySQL的联合索引会从左往右匹配数据，所以在设计索引时，最好把查询频率高的字段放在前面，这样才能充分利用最左匹配原则查询数据，但MySQL8.0中也推出了一种名为索引跳跃式扫描的机制，可以打破联合索引的最左匹配原则查找数据。

• • 面试官 ：嗯呢，那MySQL索引除开索引覆盖、跳跃扫描外，还有别的优化机制吗？

• • 我 ：还有MySQL5.6中引入的索引下推机制、MRR机制，这两种机制能够在很大程度上减少索引查询的磁盘IO，以及离散性的磁盘IO。

• • 面试官：嗯，聊了这么多理论，考你一些实践性的知识，怎么才能很好的使用索引呢？

• • 我 ：这个也不难，可以从两个角度出发，一是合理的创建索引，二是编写合理的SQL语句使用索引，先来说说什么是合理的创建索引：

  • • ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。

  • • ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。

  • • ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。

  • • ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。

  • • ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

  • • ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。

  • • ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

  • • ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。

  • • ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。

  • • ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。

  • • ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。

  • • ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。

  • • ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。

  • • ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。

  • • ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

  • • 建立索引时需要遵守的原则：

  • • 不适合建立索引的一些情况：

• •

  • • 查询中带有OR会导致索引失效。

  • • 模糊查询中like以%开头导致索引失效。

  • • 字符类型查询时不带引号导致索引失效。

  • • 索引字段参与计算导致索引失效。

  • • 字段被用于函数计算导致索引失效。

  • • 违背最左前缀原则导致索引失效。

  • • 不同字段值对比导致索引失效。

  • • 反向范围操作导致索引失效。

  • • 我 ：一般按照上述这些原则建立索引，避开一些不适合建立索引的情况，就能设计出一个很不错的索引，接着聊聊写SQL语句时的注意点，主要是避免索引失效即可，索引失效的场景有下面这些情况：

• • 我 ：咱们在写SQL语句的时候，可以刻意避开这些会导致索引失效的场景即可。

• • 面试官：哦哟，你小子不赖呀，那你最后再给我说说，使用索引的好处和坏处吧。

• • 我：好，凡事有利弊，索引也不例外，除开带来了好处之外也带来了影响，如下：

  • • ①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。

  • • ②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。

  • • ③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。

  • • ①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。

  • • ②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。

  • • ③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。

  • • ④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。

  • • ⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。

  • • ⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。

  • • 使用索引带来的优势：

  • • 使用索引带来的影响：

• • 面试官：OK，那咱们这块就此打住，嘿嘿，接下来我再问你一点特殊的东西~

三、崭露头角：详谈MySQL事务与锁机制

• • 我：咔！打住！你想要干吗？我卖艺不卖身！

• • 面试官：额.....，我就想问问你懂不懂事务与锁机制。

• • 我：啊？阿，那我白高兴了.....

• • 面试官：？！？？

• • 我：嘿嘿，没事，您接着问。

• • 面试官 ：那先跟我说说什么为什么需要事务机制，以及事务的ACID原则吧。

• • 我 ：需要事务机制的道理很简单，比如目前有一个转账业务，整个业务由减A账户余额、加B账户余额这两个操作组成，假设现在扣完A的余额后，结果程序执行时抛Bug了，但此时B的余额还没有增加，这最终会造成A账户的钱平白无故消失了！所以也正因如此，才需要事务机制来确保一组操作的数据一致性。

• • 我 ：而所谓的ACID原则，则是数据库事务机制要满足的四个特性：

  • • A/Atomicity：原子性，指组成一个事务的一组SQL要么全部执行成功，要么全部执行失败。

  • • C/Consistency：一致性，指任何一个事务发生的前后，库中的数据变化必须一致。

  • • I/Isolation：独立性/隔离性，指同时存在多个并发事务时，各个事务之间执行的操作不会相互影响。

  • • D/Durability：持久性，指一个事务但凡提交之后，就必须确保事务变更过的数据永远不会丢失。

• • 面试官：嗯呢，那你再跟我说说事务的隔离级别。

• • 我 ：MySQL的事务隔离级别有四个，每个级别分别能够解决不同的问题，如下：

  • • ①读未提交/RU：处于该隔离级别的数据库，脏读、不可重复读、幻读问题都有可能发生。

  • • ②读已提交/RC：该级别中解决了脏读问题，不可重复读、幻读问题依旧存在。

  • • ③可重复读/RR：该级别中解决了脏读、不可重复读问题，幻读问题依旧存在。

  • • ④序列化/Serializable：该级别中解决了脏读、不可重复读、幻读问题都不存在。

• • 面试官 ：等等，你所说的脏读、幻读、不可重复读问题是什么意思呢？

• • 我：这是指并发事务执行过程中，可能会碰到的一些问题，我展开说说吧。

  • • 脏读问题：指一个事务读到了其他事务还未提交的数据，其他事务可能会回滚这些数据。

  • • 不可重复读问题：指在一个事务中，多次读取同一数据，先后读取到的数据不一致。

  • • 幻读问题：指一个事务中，批量变更了某类数据，变更完成后再次查询，表中依旧存在变更前的数据，就好比发生了幻觉一样。

• • 面试官 ：那你知道MySQL的事务机制是怎么实现的吗？

• • 我 ：首先纠正一下你的问题，MySQL-Server本身没有提供事务机制，事务机制是InnoDB引擎独有的特性，而事务机制是基于Undo-log日志实现的，InnoDB默认会开启事务的自动提交，将每条SQL都视作一个单独的事务，而通过begin开启事务后，需要手动提交后才能生效，可以将多条SQL语句组成一个事务。

• • 我 ：之前咱们在聊写入语句的执行流程时，说过写入语句执行时会记录Undo-log日志，更新数据前，会把原本的老数据放到Undo-log日志中，然后在表的数据行上记录一个回滚指针，这个指针会指向Undo-log中的旧数据。

• • 我 ：当事务需要回滚时，InnoDB会直接根据回滚指针的地址，找到原本的老数据，然后直接复制过来，将变更过的新数据覆盖掉。

• • 面试官 ：OK，那你能不能简单说一下MySQL中的锁机制呢？

• • 我：可以呀，其实锁的叫法有很多，但本质上就只有共享锁、排他锁这两种，只不过加的粒度不同、时机不同、方式不同，就演变出了很多叫法，整个体系如下：

  • • 乐观锁：每次执行前认为自己会成功，因此先尝试执行，失败时再获取锁。

  • • 悲观锁：每次执行前都认为自己无法成功，因此会先获取锁，然后再执行。

  • • 显示锁：编写SQL语句时，手动指定加锁的粒度。

  • • 隐式锁：执行SQL语句时，根据隔离级别自动为SQL操作加锁。

  • • 读锁：查询数据时使用的锁。

  • • 写锁：执行插入、删除、修改、DDL语句时使用的锁。

  • • 共享锁 / S锁：不同事务之间不会相互排斥、可以同时获取的锁。

  • • 排他锁 / X锁：不同事务之间会相互排斥、同时只能允许一个事务获取的锁。

  • • 共享排他锁 / SX锁：MySQL5.7版本中新引入的锁，主要是解决SMO带来的问题。

  • • ①表锁：

  • • ②页面锁

  • • ③行锁：

  • • 全局锁：加上全局锁之后，整个数据库只能允许读，不允许做任何写操作。

  • • 元数据锁 / MDL锁：基于表的元数据加锁，加锁后整张表不允许其他事务操作。

  • • 意向锁：这个是InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的。

  • • 自增锁 / AUTO-INC锁：这个是为了提升自增ID的并发插入性能而设计的。

  • • 记录锁 / Record锁：也就是行锁，一条记录和一行数据是同一个意思。

  • • 间隙锁 / Gap锁：InnoDB中解决幻读问题的一种锁机制。

  • • 临建锁 / Next-Key锁：间隙锁的升级版，同时具备记录锁+间隙锁的功能。

  • • 以锁粒度的维度划分：

  • • 以互斥性的维度划分：

  • • 以操作类型的维度划分：

  • • 以加锁方式的维度划分：

  • • 以思想的维度划分：

• • 面试官：那行锁和表锁之间有啥区别呢？

• • 我：主要是粒度不同，表锁是指对一整张表加锁，当加锁后，其他来访问该表的事务都会被阻塞，而行锁的粒度则小很多，是指针对于一条/多条数据加锁，并不会阻塞操作同一表的事务，而仅仅只会阻塞操作相同行数据的事务。

• • 面试官：嗯呢，那你所说的共享锁和排他锁，两者的区别是啥？

• • 我：共享锁允许多个事务一起持有，而排他锁在同一时间内只能允许一个事务持有，也就是但凡出现排他锁的场景，其他事务都需要阻塞等待。

• • 面试官 ：那MySQL的表锁、行锁有哪些呢？

• • 我 ：表锁有元数据锁、意向锁、自增锁、全局锁这四种，行锁有记录锁、间隙锁、临键锁、插入意向锁这四类，行锁在MySQL中是InnoDB引擎独有的，并且InnoDB的行锁和表锁之间，是相互兼容的。

• • 面试官：你说到的记录锁、间隙锁、临键锁这三种行锁有什么区别呢？

• • 我 ：记录锁是指对一条数据上锁、间隙锁是指对一条数据和下一条数据之间的空隙上锁、临键锁则是前两者的结合体，InnoDB的行锁默认就是临键锁类型，这三种锁都属于InnoDB的行锁算法，InnoDB会根据情况来选择不同的行锁算法获取锁。

• • 面试官 ：好的，但你说了这么多锁，可是我们在用MySQL的时候似乎没有使用呀？

• • 我 ：对的，我们不会主动去使用锁，这些都是MySQL在执行语句时，自动根据情况来加的锁，因此也被称之为隐式锁，但我们也可以在SQL语句中，通过for update、for share这种语法手动加锁。

• • 面试官：那请问隐式锁、或手动加锁后，什么时候会释放锁呢？

• • 我 ：几乎所有释放锁的工作都是MySQL自动完成的，但不同事务隔离级别中，释放锁的时机也不同，如果目前是读未提交级别，MySQL执行完一条语句后就会立马释放锁。如果是其他级别中，基本上都需要等待持有锁的事务结束（commit/rollback）后才会释放。

• • 面试官 ：那你前面提到过一句，MySQL5.7中引入了一种共享排他锁，这是干嘛的？

• • 我 ：因为索引树的结构会发生变更，比如一个无序数据插入时，就会导致树节点的分裂，这时需要挪动树中的一些节点位置，为了防止其他事务再次破坏树结构、或从索引树中读到不对的数据，所以会对整棵树上锁，这个问题被称为SMO问题，共享排他锁主要就是用来解决SMO问题。

• • 面试官 ：嗯呢，MVCC机制有了解过吗？

• • 我 ：有的，但我先给你讲个故事吧，比如拿一个新闻网站举例说明，首先小编发布了一则新闻报道，等待审核通过后，A、B、C用户看到后开始阅读这篇新闻。

• • 我 ：但小编突然发现文中有错别字，就更新了一次新闻，因此这则新闻又进入了审核状态，但此时A、B、C用户正在看新闻呀！肯定不能直接给它们显示一个审核中的状态，所以就会采用多版本方案，新版本进入审核状态，而用户则读老版本的新闻。

• • 我 ：而MVCC机制翻译过来也就是多版本并发控制技术，是InnoDB中用来解决读-写事务并发冲突问题的，对于多事务并发执行的情况下，InnoDB引擎的表在更新某条数据时，并不会阻塞尝试读取这条数据的事务，而是会让读数据的事务去拿更新前的数据记录，和前面我给您的举例类似，从而实现了读写事务并发执行。

• • 面试官 ：说的倒是蛮清晰的，那跟我说说MVCC机制是怎么实现的呢？

• • 我 ：MVCC机制是通过Undo-log日志的版本链、数据表上的隐藏字段、以及ReadView读视图实现的，简单来说就是：写操作会直接对表数据进行变更，而读操作会根据回滚指针，去找到Undo-log中的旧数据读取。

• • 面试官 ：嗯，你有了解过MySQL锁机制的底层实现吗？

• • 我 ：了解过的，MySQL锁机制是基于事务实现的，一个事务尝试获取锁时，就会在内存中生成一个锁结构，锁结构中会记录着当前事务，要加锁的数据地址，会精确到表空间、数据段、数据页、行数的信息。同时锁结构中有一个is_waiting信息，为0表示当前锁结构对应事务持有着锁，而为1表示当前锁结构对应的事务在阻塞等待获取锁。

• • 我 ：一个事务尝试获取锁时，会根据要上锁的数据位置，去内存中看看是否已有对应数据位置的锁结构，如果有就代表自己要获取的锁，已经被其他事务占有了，这时还要去具体看一下锁的比特位，看一下自己要获取的行锁，具体有没有被加锁，如果没有，当前事务直接获取锁执行，如果有，当前事务阻塞等待，对应锁结构中的is_waiting=1。

• • 面试官 ：嗯呢，那你有了解过事务隔离机制的底层实现吗？

• • 我 ：这块也略懂一些，每个隔离级别都是基于锁和MVCC机制实现的，如下：

  • • ①读未提交/RU：写操作加排他锁，读操作不加锁。

  • • ②读已提交/RC：写操作加排他锁，读操作使用MVCC，但每次select都生成读视图。

  • • ③可重复读/RR：写操作加排他锁，读操作依旧采用MVCC机制，但一次事务中只生成一个读视图。

  • • ④序列化/Serializable：所有写操作加临键锁（具备互斥特性），所有读操作加共享锁。

• • 面试官：可以，先考你到这里，时间不早了，我带你去吃个午饭回来接着聊~

• • 我：好的，开我车去吧（一边说一边从口袋里慢慢掏出了兰博基尼的车钥匙）。

• • 面试官 ：........

因为今晚临时有事，所以得出去一趟，目前这章大概有1.2W字，全部写完预计会有3~4W字左右，我忙完之后会回来继续更，诸位对后续内容感兴趣，可点个关注或收藏，耐心等待一小会儿时间~（时间也不会太久，大概明后天左右会彻底更完！本章属于整个MySQL专栏的总结篇，所以写起来速度会比较快，抛开事实不谈的情况下，日更十万字简直不在话下，哈哈哈）

四、大展身手：对线MySQL日志、内存与存储引擎

• • 面试官：怎么样？我们公司楼下的炒饭味道还不错吧？

• • 我：额.....，如果味道不难吃的话，应该还挺好吃的。

• • 我：咱们不耽误时间了，接下来咱们聊什么呢？

• • 面试官 ：对MySQL的日志熟悉么？

• • 我 ：略懂略懂，MySQL中的日志种类不少，但常用的主要有六种：

  • • ①Undo-log撤销日志：当有操作变更数据前，都会把老数据放入该日志中。

  • • ②Redo-log重做日志：该日志记录着InnoDB所有表的变更语句，也可用来做灾难恢复。

  • • ③Bin-log变更日志：这里面记录着所有对数据库会产生变更的语句。

  • • ④Error-log错误日志：记录着MySQL启动、运行期间所有的报错、警告信息。

  • • ⑤Slow-log慢查询日志：记录着所有执行时长超出指定阈值的查询语句。

  • • ⑥Relay-log中继日志：主从集群中，丛节点用于存储主节点Bin-log数据的日志。

• • 面试官 ：你跟我说说，为什么有了Bin-log日志还需要Redo-log日志呢？

• • 我 ：这两个日志都会记录数据库的写操作，但Redo-log是InnoDB引擎独有的日志，主要功能在于做灾难恢复，每条写入语句在执行前，都会先记录一条prepare状态的日志，然后再执行SQL语句，执行完成后会记录bin-log日志，接着再把Redo-log日志的状态从prepare改为commit。如果一个事务提交后，数据在内存中还未刷盘，此时MySQL宕机了，后续重启时也可以根据Redo-log来恢复数据。

• • 面试官：嗯呢，那你跟我说说这两个日志有啥区别呢？

• • 我 ：对于Redo-log、Bin-log两者的区别，主要可以从四个维度上来说：

  • • ①生效范围不同，Redo-log是InnoDB专享的，Bin-log是所有引擎通用的。

  • • ②写入方式不同，Redo-log是用两个文件循环写，而Bin-log是不断创建新文件追加写。

  • • ③文件格式不同，Redo-log中记录的都是变更后的数据，而Bin-log会记录变更SQL语句。

  • • ④使用场景不同，Redo-log主要实现故障情况下的数据恢复，Bin-log则用于数据灾备、同步。

• • 面试官：那日志是直接写磁盘的吗？

• • 我 ：不是的，MySQL、InnoDB专门在内存中设计了日志缓冲区，不同日志有不同的缓冲区，日志也是先写内存，然后由后台线程来完成刷盘。

• • 面试官 ：嗯呢，你对Redo-log、Bin-log日志的刷盘机制了解过么？

• • 我 ：redo-log日志的刷盘策略由innodb_flush_log_at_trx_commit参数控制，而bin-log日志的刷盘策略则可以通过sync_binlog参数控制：

  • • 0：同上述innodb_flush_log_at_trx_commit参数的2。

  • • 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交事务都会刷盘，默认策略。

  • • 0：间隔一段时间，然后再刷写一次日志到磁盘（性能最佳）。

  • • 1：每次提交事务时，都刷写一次日志到磁盘（性能最差，最安全，默认策略）。

  • • 2：有事务提交的情况下，每间隔一秒时间刷写一次日志到磁盘。

  • • innodb_flush_log_at_trx_commit：

  • • sync_binlog：

• • 同时，Redo-log是通过两个日志来循环写，而Bin-log是通过多个日志文件来追加写。

• • 面试官 ：再问一下，Bin-log日志格式有哪些呢？

• • 我：总共有三种格式，如下：

  • • Statment：记录每一条会对数据库产生变更操作的SQL语句（默认格式）。

  • • Row：记录具体出现变更的数据（也会包含数据所在的分区以及所位于的数据页）。

  • • Mixed：Statment、Row的结合版，可复制的记录SQL语句，不可复制的记录具体数据。

• • 面试官：如果你在线上因为不小心，误删了大量表数据或库数据，你会怎么做呢？

• • 我：这不用说啊，肯定是卷铺盖跑路！

• • 面试官：如果不能跑路呢？

• • 我 ：那就硬着头皮上，由于bin-log日志是按顺序追加写，一个日志文件满了之后，会创建一个新的日志文件来存放记录，在本地会呈现bin-log.0001、bin-log.0002、bin-log.000x.....这种形式，所以咱们只需要找到误删命令执行前的日志文件，然后通过日志来恢复数据即可。

• • 面试官 ：那你有了解过Redo-log日志的两阶段提交嘛？

• • 我 ：有的，这个主要是因为单阶段提交，如果放在bin-log前写入日志，可能会导致主从集群数据同步不一致，但如果放在bin-log后写入日志，则无法实现灾难恢复，所以被设计成了在bin-log前后都写入一次。

• • 面试官：嗯呢，那你们一般在线上环境中，如何定位哪些执行比较缓慢的查询语句呢？

• • 我 ：会开启慢查询日志，通过long_query_time参数指定时间阈值，MySQL会自动将超出阈值的查询语句记录进去。

• • 面试官 ：好，众人都说MySQL是基于磁盘工作的，你对此怎么看呢？

• • 我：我觉得它们说的没有错！

• • 面试官：......，有没有一些别的见解呢？

• • 我 ：那我简单发表一下我的看法吧，MySQL在设计的时候的确是基于磁盘工作，但因为MySQL的存储引擎支持可拔插式，所以如果库使用的是InnoDB引擎，这时情况就不同了。

• • 我 ：InnoDB会在内存中构建出一个BufferPool缓冲区，只要为其分配的内存足够大，InnoDB基本上会把所有操作都放在内存中完成。

• • 面试官 ：何出此言呐？能否跟我细致聊聊这BufferPool缓冲区其中的门道？

• • 我 ：可以的，InnoDB构建出的缓冲区，会把内存划分为一个个的「页」，每个页的默认大小为16KB，以页作为内存和磁盘交互的基本单位，这些缓冲页会分为三种：

  • • 空闲页：指没有被使用过的内存缓冲页。

  • • 数据页：指已经被使用、用于存放磁盘表数据、索引数据以及其他各类的缓冲页。

  • • 变更页（标记页、脏页）：指页中数据发生过变更、还未被后台线程刷写到磁盘的缓冲页。

• • 我 ：同时为了便于管理所有缓冲页，每个缓冲页都会有一个控制块与其对应，其中记录着磁盘数据的表空间、页号、缓冲页地址、链表节点指针等信息，InnoDB可以基于控制块去管理每一块缓冲页。

• • 面试官 ：那InnoDB具体会如何管理缓冲页呢？

• • 我 ：InnoDB会基于三个链表来管理所有缓冲页，所有缓冲页会根据类型不同，分别加入到不同的链表中，每个缓冲页通过控制块中的指针，形成逻辑连续的链表结构：

  • • 当一个缓冲页中的数据发生变更后，会从Lru链表移动到Flush链表中。

  • • 当变更页的数据刷写完成后，又会从Flush链表中移回Lru链表。

  • • 当需要使用缓冲页时，会从空闲链表中拿出一个空闲页使用。

  • • 当清空一个数据页后，又会将没有数据的缓冲页加入到空闲链表。

  • • Free List：负责记录空闲页，为了使用时能更快的找到空闲缓冲页。

  • • Lru List：记录所有已经使用过的缓冲页，为了方便淘汰已使用的内存页。

  • • Flush List：负责记录所有变更页，为了刷盘时能够更快的找到变更数据页。

• • 面试官：那一个磁盘数据被载入内存缓冲页，会一直停留在内存吗？

• • 我 ：不会的，因为这样做会导致内存占用无限增长，最终引发OOM内存溢出，所以有些数据页会被淘汰出内存。

• • 面试官 ：那内存中的数据页是如何被淘汰出去的呢？

• • 我 ：这就需要用到前面所说的Lru链表了，InnoDB会采用末尾淘汰机制，这正如大部分企业中推行的KPI绩效机制类似，每个员工都会有KPI绩效，到了年底时会淘汰一部分绩效较低的员工，来年后再招聘新员工，吸收新鲜血液入职。

• • 我 ：而InnoDB中也类似，所有使用过的数据页都会加入Lru链表中，但每当一个数据页被访问后，都会将其移动到链表的最前面，这样就能够保证热度较高的数据页长久留在内存中，及时淘汰掉那些热度较低的数据页。

• • 面试官 ：那如果在缓冲区内存较大的情况下，如果数据页比较多，对于一些只使用过1~2次的数据，岂不是需要很久才能被淘汰吗？

• • 我 ：对的，所以InnoDB把Lru链表分为了young、old两个区域，默认比例为63:37：

  • • young区域：存放经常被访问的热点数据页。

  • • old区域：存放刚从磁盘中加在的数据页。

• • 我 ：LRU链表被划分为两个区域后，从磁盘中预读的数据页会加入到old区域的头部，当这个数据页被真正访问时，才会将其插入young区的头部。

• • 我 ：如果预读的这页在后续一直没有被访问，就会从old区域移除，从而不会影响young区域中的热点数据。

• • 面试官：那假设此时有一个查询操作，对一张千万级别的表走了全表查询，内存中的热点数据页全部被换出去了怎么办呢？

• • 我 ：这也不用担心，前面说过首次载入内存的数据页，会先放到old区域，而想要从old移到young区域，这是有晋升限制的。

• • 我 ：所谓的晋升限制，就是加了一个停留时间的限制，如果一个数据页想从old晋升到young区，必须要在old区中存活一定时间，这个时间默认为1000ms。

• • 我 ：结合前面old进入young区的条件，数据页想从Old转到Young得满足两个条件：

  • • ①在old区中停留的时间超过了1000ms。

  • • ②在old区中，一秒后有线程再次访问了这个数据页。

• • 我：通过这两个条件限制，就能够有效避免你前面所说的缓冲池污染问题，也就是大查询操作替换掉热点数据页的问题。

• • 面试官 ：很不错嘛，那你跟我说说InnoDB内部的执行过程吧。

• • 我 ：这个不难，InnoDB在处理读写语句时也会有细微差距：

  • • 读语句：首先会根据SQL语句，将目标数据从磁盘载入内存，经过条件筛选后返回。

  • • 写语句：首先会将语句要操作的数据页载入到内存，然后更改内存数据页，由后台线程去把变更数据刷写到磁盘。

• • 面试官 ：嗯呢，如果是insert新增操作呢？之前磁盘没有数据呀，如何处理呢？

• • 我 ：这也不用担心，InnoDB有一个插入缓冲区，5.6之后叫做写入缓冲区，专门用来处理新增操作，insert的数据会被放到这个缓冲区中，然后由后台线程完成刷盘工作。

• • 面试官 ：嗯呢，那你有了解过InnoDB的自适应哈希索引吗？

• • 我 ：这个很简单，因为Hash结构是所有数据类型中最快的，所以InnoDB会在运行期间，统计出一些经常走索引查询的热点数据，然后针对这些热点索引数据，去为其建立哈希索引，以此提升查询性能。

• • 面试官 ：那你最后给我总结一下，MySQL内存中有什么？

• • 我：......，您直接看图吧。

  • •

    MySQL内存结构

• • 面试官 ：说说MySQL常用的InnoDB、MyISAM两款引擎之间的区别吧。

• • 我：可以，两者之间其实有许许多多的细微差距，但我就简单从七个大方向总结一下：

  • • MyISAM引擎的表会生成三个磁盘文件：

  • • 而InnoDB引擎的表只会生成两个磁盘文件：

  • • table_name.frm：该文件中存储表的结构信息。

  • • table_name.MYD：该文件中存储表的行数据。

  • • table_name.MYI：该文件中存储表的索引数据。

  • • table_name.frm：该文件中存储表的结构信息。

  • • table_name.ibd：该文件中存储表的行数据和索引数据。

  • • ①磁盘文件不同：

  • • ②InnoDB支持聚簇索引，而MyISAM只支持非聚簇索引，因为它索引数据和表数据是分开存储的。

  • • ③InnoDB基于Undo-log日志实现了事务机制，但MyISAM没有，所以不支持事务。

  • • ④InnoDB基于Redo-log日志实现了故障恢复机制，但MyISAM则只能依靠Bin-log，因此会有丢失数据的风险。

  • • ⑤InnoDB可以基于聚簇索引实现行锁，同时还兼容表锁，但MyISAM仅支持表锁。

  • • ⑥InnoDB因为支持行锁以及MVCC机制，所以并发场景下的性能会远超MyISAM引擎。

  • • ⑦InnoDB由于设计了BufferPool缓冲池，所有内存利用度会远超MyISAM引擎。

• • 面试官：好的，回答的蛮不错。

五、戏入高潮：高级特性、性能调优与线上排查

• • 面试官 ：有用过存储过程吗？能否说说存储过程的优劣势呢？

• • 我：之前在项目中实现一些特殊需求时用过，个人理解中，存储过程的优劣势如下：

  • • CPU开销大：如果其中涉及大量逻辑运算工作，会导致MySQL所在的服务器CPU飙升。

  • • 内存占用高：多次执行后的存储过程会被编译成机器码，长期驻留在内存中，占用不小空间。

  • • 维护性差：过于复杂的存储过程阅读性较差，并且不支持Debug调试，出错时难以排查。

  • • 复用性：存储过程被创建后可以被反复调用，表结构发生变更也只需修改存储过程。

  • • 灵活性：存储过程中可以用变量、流程控制语句，可以完成复杂的条件查询和较繁琐的运算。

  • • 省资源：调用存储过程只需要传输调用语句和参数，无需传输一条体积较大的SQL语句。

  • • 高性能：执行多次后，会被编译成机器码驻留在线程缓冲区，后续执行时无需重新编译。

  • • 安全性：对于不同的存储过程，可设置用户的执行权限，包括内部语句对客户端来说是黑盒操作，减小了SQL被暴露的风险。

  • • 使用存储过程的好处：

  • • 使用存储过程带来的问题：

• • 面试官 ：那我如果想在新增数据之后，再做某个操作，不用AOP切面的话怎么实现呢？

• • 我 ：可以用MySQL的触发器来完成，insert、delete、update三个操作都可以添加前/后置处理器，效果与AOP切面类似。

• • 面试官 ：那如果我想定时执行某段SQL语句，不想用定时调度框架怎么办呢？

• • 我 ：可以用MySQL的定时器，支持按年、季、月、周、日、时、分、秒、毫秒等精度触发。

• • 面试官 ：那一般你写SQL时，经常会用那些语句、关键字、和函数呢？

• • 我 ：......，我给你一个命令大全，您自己看吧。

• • 面试官 ：前面看你有提到连接层优化，你对MySQL性能优化这块熟悉吗？

• • 我 ：同样略知一二，性能调优是一门大学问，没有通用的方案，调优都要取决于现有环境、业务来谈，MySQL的性能优化可以从五个维度来说：

  • • ①连接层的优化：调整客户端DB连接池的参数和DB连接层的参数。

  • • ②结构的优化：合理的设计库表结构，表中字段根据业务选择合适的数据类型、索引。

  • • ③参数/配置优化：调整参数的默认值，根据业务将各类参数调整到合适的大小。

  • • ④架构优化：引入中间件减轻数据库压力，优化MySQL架构提高可用性。

  • • ⑤编码优化：根据库表结构、索引结构优化业务SQL语句，提高索引命中率。

• • 面试官 ：你前面说的连接层优化，怎么配置连接数来着？

• • 我 ：CPU核心数*2，如果硬盘材质是SSD的，那么还可以再加个一，总的来说就是根据硬件来配置。

• • 面试官 ：好，那我这个连接数是配在客户端还是MySQL呢？

• • 我 ：如果MySQL只有一个应用程序访问，在客户端配好最大连接数就行，如果提供给多个应用访问，则需要限制一下MySQL的最大连接数。

• • 面试官 ：那你再详细说说你提到的结构优化是咋回事呢？

• • 我：主要是表结构、字段结构、索引结构这三方面的设计，我简单的聊一聊：

  • • ①尽量避免创建过多的单列索引，对于多个字段要用索引，可以用联合索引代替。

  • • ②对于一个值较长的字段创建索引，可以使用前N个字节，创建前缀索引。

  • • ③索引类型一定要选择合理，如经常做模糊查询的字段，可用全文索引代替普通索引。

  • • ④某个字段在业务中无需用作范围查询时，可以通过Hash结构代替B+Tree结构。

  • • ①在保证足够使用的范围内，选择最小数据类型，如能用int就别用bigint。

  • • ②尽量避免索引字段值为NULL，因为字段空值过多会影响索引性能。

  • • ③在条件允许的情况下，尽量使用最简单的类型代替复杂的类型。

  • • 表结构优化：选择合适的引擎、精细化字段数量、选择合适的主键、适当冗余字段和中间表。

  • • 字段结构优化：

  • • 索引结构优化：

• • 面试官 ：参数优化呢？

• • 我 ：参数优化不太熟，有专门的DBA负责，个人一般就是调大缓冲区、线程缓冲区这些。

• • 面试官 ：架构优化呢？

• • 我：这块还比较熟悉，主要分为引入第三方技术栈调整业务架构，以及调整数据库的部署架构：

  • • 读写分离架构：搭建主从集群，主机负责处理外部写请求，从机负责处理外部读请求。

  • • 多主多写架构：搭建多主集群，集群内任意节点，都具备独立处理读写请求的能力。

  • • 垂直分库架构：对整库按照业务进行拆分，不同业务属性的流量分发到不同库处理。

  • • 水平分库架构：在垂直分库基础上，再对核心业务库做横向拓展，提升核心库的吞吐。

  • • 引入Redis做缓存，来减少落入数据库中的读请求，分担大部分读压力。

  • • 引入MQ做削峰，来将并发情况下的写压力，平缓到数据库可承载的级别。

  • • 引入第三方技术栈调整业务架构：

  • • 调整数据库的部署架构：

• • 面试官 ：回答的都挺不错的，再聊聊SQL优化吧。

• • 我 ：所谓的SQL优化，核心就是减小查询的数据量、提升SQL的索引命中率，在写SQL的时候刻意注意下述一些原则即可：

  • • ①查询时尽量不要使用*：因为用*会导致网络开销变大，并且无法利用索引覆盖机制。

  • • ②连表查询时尽量不要关联太多表：关联的表数量过多时，会导致扫描的数据量呈直线性增长。

  • • ③多表查询时一定要以小驱大：用小表匹配大表能有效减少循环过程，从而提升数据的检索效率。

  • • ④不要使用like左模糊和全模糊查询：like查询以%号开头会导致索引失效，从而走全表查询。

  • • ⑤查询时尽量不要对字段做空值判断：如果字段存在过多的空值，也有可能导致索引失效。

  • • ⑥不要在条件查询=前对字段做任何运算：在where字句的=号前做运算，也会导致索引失效。

  • • ⑦!=、!<>、not in、not like、or...要慎用：这些反范围查询的操作也有可能会导致索引失效。

  • • ⑧必要情况下可以强制指定索引：过于复杂的查询语句中，优化器可能无法选择最佳索引执行。

  • • ⑨避免频繁创建、销毁临时表：临时表对资源开销极大，内存、磁盘资源消耗较高。

  • • ⑩尽量将大事务拆分为小事务执行：大事务会长期占用锁，造成其他获取锁的事务阻塞等待。

  • • ⑪从业务设计层面减少大量数据返回：结果集的数据量较大时，网络带宽会成为新的瓶颈。

  • • ⑫尽量避免深分页的情况出现：MySQL的limit关键字，处理深分页会把前面的数据都查一次。

  • • ⑬SQL要写完整，不要使用缩写法：缩写的语法会在底层做一次转换，完整的语句则省略此过程。

  • • ⑭使用联合索引请确保字段的顺序性：虽然有跳跃扫描机制，但该机制的底层开销依旧不小。

  • • ⑮某些操作可以批量化完成：将操作批处理可节省传输消耗，减少SQL解析、优化次数。

  • • ⑯明确返回单条数据时可使用limit 1：使用此语句在匹配到一条数据后就会停止扫描。

• • 我 ：一般在写SQL时注意上述十六条原则，通常写出的语句，其效率都不会太差。

• • 面试官 ：一般你们线上的SQL执行标准是多久呢？

• • 我 ：普通语句要控制在200ms以内，超出这个时间，就会导致客户端长时间无响应。

• • 面试官：嗯呢，当线上有些语句执行较为缓慢时，你会如何定位呢？

• • 我 ：部署MySQL前会开启慢查询日志，直接查看慢查询日志即可。

• • 面试官 ：那定位到慢查询语句后，你会怎么排查问题呢？

• • 我 ：定位到具体的慢查询日志后，接着会去通过explain工具分析语句，到底是没走索引、还是由于扫描的数据量较大，然后对症下药解决问题。

• • 面试官 ：你在线上有碰到过客户端连接异常的情况么？连接异常会有哪些原因导致呢？

• • 我：连接异常这种情况比较少见，一般出现的原因如下：

  • • ①总体的现有连接数，超出了MySQL中的最大连接数，此时再出现新连接时就会出异常。

  • • ②客户端数据库连接池与MySQL版本不匹配，或超时时间过小，也可能导致出现连接中断。

  • • ③MySQL、Java程序所部署的机器不位于同一个网段，两台机器之间网络存在通信故障。

  • • ④部署MySQL的机器资源耗尽，如CPU、硬盘过高，导致MySQL没有资源分配给新连接。

• • 面试官：对于每种情况出现时，该怎么解决呢？

• • 我：前面两种比较简单，检查一下相关的配置，然后调整一下配置项即可，如果是网络问题，排查的思路如下：

  • • 检测防火墙与安全组的端口是否开放，或与外网机器是否做了端口映射。

  • • 检查MySQL的机器白名单，及登录的用户IP限制，可能是IP不在白名单范围内。

  • • 如果整个系统各节点部署的网段不同，检查各网段之间交换机的连接超时时间是多少。

  • • 检查不同网段之间的带宽大小，及具体的带宽使用情况，有时会因带宽占满出现问题。

  • • 如果用了MyCat这类代理中间件，记得检查中间件的白名单、超时时间等配置。

• • 我：如果是机器资源被耗尽，那首先需要找到资源消耗较高的语句，然后对语句进行优化后才能解决连接异常。

• • 面试官 ：有在线上排查过MySQL死锁问题么？

• • 我 ：有的，虽然MySQL会有死锁检测机制主动解除已发生的死锁，但无法彻底根治死锁，想要根治必须要先找到频繁触发死锁的事务，步骤如下：

  • • ①先通过SHOW ENGINE INNODB STATUS\G命令，查询InnoDB的运行时日志。

  • • ②找到日志中的LATEST DETECTED DEADLOCK区域，这其中会记录发生过的死锁。

  • • ③查看日志中触发死锁次数最多的语句，分析SQL为何会产生死锁，然后调整即可。

• • 面试官 ：那服务器CPU占用100%该怎么排查呢？

• • 我：这个很简单，属于一种比较常见的线上问题，排查思路如下：

  • • ①先找到CPU过高的服务器。

  • • ②然后在其中定位到具体的进程。

  • • ③再定位到进程中具体的线程。

  • • ④再查看线程正在执行的代码逻辑。

  • • ⑤最后从代码层面着手优化掉即可。

• • 面试官 ：MySQL磁盘利用率达到100%呢，什么情况下会出现，出现后如何解决？

• • 我 ：MySQL磁盘利用率达到100%的原因，通常是因为磁盘占用过高导致，占用过高的情况有很多种，如下：

  • • ①突然大量变更库中数据，需要执行大量写入操作，如主从数据同步时就会出现此问题。

  • • ②MySQL整体并发过高，磁盘I/O频率跟不上，比如是机械硬盘材质，读写速率过慢。

  • • ③内存中的BufferPool缓冲池过小，大量读写操作落入磁盘处理，导致磁盘利用率过高。

  • • ④频繁使用临时表，导致内存无法存下临时表数据，因而转到磁盘存储，导致磁盘飙升。

  • • ⑤执行某些语句从磁盘加载海量数据，如多表联查时，每张表数据较大，导致IO打满。

  • • ⑥日志刷盘频率过高，这条是①、②的附带情况，毕竟日志的刷盘频率，跟整体并发挂钩。

• • 我：解决方案如下：

  • • ①如果磁盘不是SSD材质，请先将磁盘升级成固态硬盘，MySQL对SSD硬盘有特殊优化。

  • • ②在项目中记得引入Redis降低读压力，引入MQ对写操作做流量削峰。

  • • ③调大内存中BufferPool缓冲池的大小，最好设置成机器内存的70~75%左右。

  • • ④撰写SQL语句时尽量减少多张大表联查，不要频繁的使用和销毁临时表。

• • 面试官：很不错，你小子和我胃口！

六、愈演愈热：论述表分区、新特性与高可用集群

• • 面试官：如果一张表的数据较大，但历史数据又很少使用，你会如何处理呢？

• • 我 ：做表分区，可以对数据按时间或者按数据量级分区，将不同范围的数据划分到不同分区中存储。

• • 面试官 ：嗯呢，对MySQL各版本的新特性有了解过吗？

• • 我 ：版本太多了没完全了解，但研究过MySQL5.6、5.7、8.0这三个重量级版本。

• • 面试官 ：那你先说说MySQL5.6有什么重要的特性呢？

• • 我 ：MySQL5.6属于一个里程碑式的版本，在这个版本中性能改善很大，重点有六个改进：

  • • ①支持Read-Only只读事务：提升MVCC机制读取数据的速度。

  • • ②InnoDB缓冲池增强：分配单独刷盘线程、热点数据支持写盘，下次重启后可支持预热。

  • • ③新增performance_schema库监控全局资源：统计网络、磁盘IO、锁资源、SQL语句...信息。

  • • ④引入ICP索引下推机制：将筛选数据的工作从服务层下移到引擎层，减少查询的磁盘IO次数。

  • • ⑤引入MRR机制：减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

  • • ⑥主从复制技术优化：支持GTID复制、无损复制、延时复制、并行复制技术。

• • 面试官 ：嗯呢，再接着给我说说MySQL5.7中的特性吧。

• • 我 ：5.7版本中更多是在改善5.6中的问题，因为优化太多，所以有很多细节需要改进，对于新特性就两个较为重要的：

  • • ①引入共享排他锁：解决SMO问题发生时，锁住整颗B+树（表锁）影响并发性能。

  • • ②内置数据类型中支持Json：MySQL表结构支持Json格式，无需将其转换为字符串再进行存储。

• • 面试官 ：好，最后说说MySQL8.0的新特性吧。

• • 我 ：MySQL8.0是改进较大的一个版本，其中发生的变更比较多，主要也有七点：

  • • ①移除了查询缓存：因为查询缓存带来的弊端大过优势，而且和缓冲池存储功能重复。

  • • ②优化了锁机制：优化共享锁的写法、支持非阻塞式获取锁。

  • • ③在线修改的系统参数支持持久化：之前运行期改过的参数重启后会失效，8.0中会持久化到本地。

  • • ④优化多表连接：引入哈希连接算法、支持隐式转换为反连接查询。

  • • ⑤增强索引机制：引入索引跳跃扫描机制、支持隐藏索引、降序索引、函数索引。

  • • ⑥支持CTE通用表表达式：进一步提升了SQL编程的灵活性。

  • • ⑦支持窗口函数：进一步丰富了MySQL的特性支持。

• • 面试官：你说提到的这个哈希连接是什么东西？我怎么没用过呢？

• • 我 ：这个是连表查询的一种连接算法，之前版本中的联表查询都采用Nest-Loop-Join嵌套循环连接算法，而8.0中，在适当情况下会选择Hash-Join算法提升查询性能。

• • 面试官 ：你说的Nest-Loop-Join嵌套循环连接算法，自己有深入了解过吗？

• • 我：有的，在这种联表查询算法中，会分为驱动表和被驱动表，驱动表则是作为条件的表，被驱动表则是需要去检索数据的表。

• • 我 ：Nest-Loop-Join算法执行时，会通过循环嵌套的模式工作，外层循环遍历驱动表的数据，内层循环遍历被驱动表的数据，然后再进行目标数据的检索，最终得到目标数据。

• • 面试官 ：嗯呢，那为什么需要引入Hash-join算法呢？

• • 我 ：因为Nest-Loop-Join算法执行时，因为采用的是循环嵌套，所以性能方面并不高。

• • 面试官 ：那你说的Hash-Join算法效率很高么？

• • 我 ：在用=符号做等值连接查询时的确如此，在哈希算法中会分为构建表和探测表，构建表则是作为条件的表，探测表是需要检索数据的表。

• • 我 ：首先MySQL会对构建表的每行数据生成哈希值，然后最终得到一张哈希表，接着只需要循环探测表的数据，将每条数据计算出哈希值，然后去哈希表中匹配即可。

• • 面试官：好的。

• • 面试官：如果你线上单台节点抗不住并发，你会如何处理呢？

• • 我：首先我会考虑搭建主从集群来解决并发问题。

• • 面试官：那你说说使用主从集群有啥好处？

• • 我：使用主从复制方案，可以进一步提升数据库的可用性和性能，主要有四大好处：

  • • ①在主机宕机或故障的情况下，从节点能自动升级成主机，从而继续对外提供服务。

  • • ②提供数据备份的功能，当主节点的数据发生损坏时，从节点中依旧保存着完整数据。

  • • ③可以实现读写分离，主节点负责处理写请求，从节点处理读请求，进一步提升性能。

  • • ④可以实现多主多写，数据库系统可以由多个节点组成，共同对外提供读写处理的能力。

• • 面试官：那你认为主从集群这种方案，有没有啥问题呢？

• • 我：有的，主要有三个问题：

  • • ①硬伤：木桶效应，主从集群中所有节点的容量，受限于容量最低的那台服务器。

  • • ②数据一致性问题：同步数据的过程是基于网络传输完成的，所以存在数据延迟。

  • • ③脑裂问题：从机会通过心跳机制来判断主机是否存活，网络故障情况下会产生多主。

• • 面试官 ：嗯呢，对MySQL主从集群的数据同步的原理熟悉么？

• • 我：这块还比较熟悉，我先给您画个图：

  • •

    主从复制原理

  • • ①客户端将写入数据的需求交给主节点，主节点先向自身写入数据。

  • • ②数据写入完成后，紧接着会再去记录一份Bin-log二进制日志。

  • • ③配置主从架构后，主节点上会创建一条专门监听Bin-log日志的log dump线程。

  • • ④当log dump线程监听到日志发生变更时，会通知从节点来拉取数据。

  • • ⑤从机会有专门的I/O线程等待主节点的通知，当收到通知时会去请求一定范围的数据。

  • • ⑥当从机在主机上请求到一定数据后，接着会将得到的数据写入到relay-log中继日志。

  • • ⑦从机上也会有专门负责监听relay-log变更的SQL线程，当日志出现变更会开始工作。

  • • ⑧监听到变更后，接着会从中读取日志记录，然后解析日志并将数据写入到自身磁盘中。

• • 面试官 ：一般搭建主从集群时，有几种架构可选呢？

• • 我：有一主一从/多从、双主/多主、多主一从、级联复制这四种架构可选：

  • • ①一主一从/多从：可以基于此结构的集群实现读写分离，适用于读大于写的场景。

  • • ②双主/多主：各节点间互为主从，各节点都具备处理读写的能力，适用于读写参半的场景。

  • • ③多主一从：一个从节点同步多个主节点的数据，适用于写大于读的场景。

  • • ④级联复制：方案①的改良版，一个节点同步主机数据，其他节点同步该节点的数据。

• • 面试官 ：MySQL主从同步数据时，是主节点推送还是从节点拉取？

• • 我：主推+从拉的结合方案，当主节点数据发生变更时，主机会通知从机，然后从机来拉取数据。

• • 面试官 ：嗯呢，那你能跟我说说，主从同步复制数据时，有哪些方式吗？

• • 我 ：MySQL中主从之间的数据复制，支持四种同步方式：

  • • ①同步复制：主机收到客户端写入请求后，需要等待所有从机都写入完成后再返回。

  • • ②异步复制：主机收到客户端写入请求后，自身写入数据就立即返回。

  • • ③半同步复制：主机收到客户端写入请求后，自身写入成功+一个从节点写入成功就返回。

  • • ④无损复制：第③种的增强版，在未收到从机写入成功的ACK之前，不会提交事务。

• • 我 ：从性能来说① < ④ < ③ < ②，从数据一致性来说② < ③ < ④ < ①。

• • 面试官 ：那你在说5.6特性时，提到的延迟复制、GTID复制、并行复制是啥意思？

• • 我：这是对于主从复制的三种增强机制：

  • • 延迟复制：支持从机同步数据后，并不立刻执行写入操作，而是等待指定时间后再写入。

  • • GTID复制：主从的同步点依靠全局事务ID来实现，开启后无需人工指定数据同步点。

  • • 并行复制：指主节点上怎么并发写入数据的，从节点也开启相同数量的线程写入数据。

• • 面试官 ：那你们线上主从库之间同步存在延迟，数据一致性问题如何解决的呢？

• • 我：前面讲过，主从数据是依靠网络来同步的，所以有延迟很正常，处理一致性问题共有五种方案：

  • • ①更改业务逻辑：当用户变更数据后，先显示「审核中」的状态，同步到从库后再让查询。

  • • ②更改同步方式：将复制数据的方式改为同步复制，这样能够保障主从数据的严格一致性。

  • • ③调整部署架构：将部署结构升级到分库分表，按业务对数据进行分片，每次读对应数据。

  • • ④引入中间件：通过Canal来监控主机的Bin-log日志，一发生变化就立马同步数据。

  • • ⑤调整分发规则：对于一致性较为敏感的数据，强制改写并路由到主节点上读取数据。

• • 面试官：嗯呢，那你自己做过主从集群的搭建么，具体过程能跟我说一下吗？

• • 我 ：......，这个主要是改配置文件，我给你一个搭建教程看吧。

七、戏至巅峰：共探分库分表的奥妙

• • 面试官：如果你的业务规模，使用主从集群无法解决并发压力怎么办呢？

• • 我：会直接选用分库分表方案。

• • 面试官 ：嗯呢，那你跟我说说为什么要分库分表呢？

• • 我：并发请求过高、数据查询较慢、单表/单库数据量过大、数据库出现性能瓶颈。

• • 面试官 ：那分库分表究竟是怎么回事，如何拆分呢？

• • 我：分库、分表、分库分表这是三个概念：

  • • 分库：不对库内的表做拆分，所有节点的表结构完全一致，主从集群就是这种形式。

  • • 分表：不分库，而是在单库内对一张大表做拆分。

  • • 分库分表：先拆分库，再对库中的表做拆分。

• • 我：分库分表主要有两种拆分方式，水平拆分和垂直拆分，好比一个西瓜，横着切叫水平，竖着切叫垂直：

  • • 垂直分表：将一张字段过多的表，拆分成多张字段更精细化的小表。

  • • 水平分表：将一张数据量过大的表，按时间或数据量等方式，拆分为多张数据量较小的表。

  • • 垂直分库：对一个压力较高的大库，按业务属性来分成不同的业务库。

  • • 水平分库：对一个压力较高的核心业务库，再对其做水平拓展，分成多个核心业务库。

• • 面试官：那分库分表能够带来什么好处呢？

• • 我：分库分表能够让数据存储层真正达到高可用、高性能、高拓展的三高水准：

  • • ①能够得到最大的性能收益，吞吐量会随机器数量呈直线性增长。

  • • ②能够最大程度上保障存储层的高可用，任意节点宕机都不会影响整体业务的运转。

  • • ③具备很强的容错率，当一个库存在问题需要重构时，无需将所有业务停机更新。

  • • ④具备高稳定性，分库+配备完善的监控重启策略后，能确保线上无需人工介入管理。

• • 面试官：对于一张日均数据量增长超百万的表，你会如何处理呢？

• • 我：会采用水平分表策略，按几天为一张表做拆分，划分为一张张的小表。

• • 面试官：具体如何做的呢？

• • 我 ：用存储过程+定时器自动按日期创建表，再将业务层的语句做改写，具体可以看这个。

• • 面试官 ：那分库分表之后，SQL语句该如何执行呢？

• • 我：这需要配置好路由键和分片算法，只要根据配置好的分片规则，定位到数据表即可。

• • 面试官 ：那水平拆分出多个数据表之后，如何做联表查询呢？

• • 我：联表查询有多种方案来实现，但性能肯定比不上传统的单库查询，方案如下：

  • • ①如果分表数量是固定的，直接对所有表进行连接查询，但这样性能开销较大。

  • • ②如果表数量会随时间不断变多，那就先根据分表规则，去确定要连接哪张表后再查询。

  • • ③如果每次连表只需要从中获取1~3个字段，可直接在另一张表中设计冗余字段。

• • 面试官 ：那水平分表后，如何使用count()、sum()...这类聚合函数呢？

• • 我：有三种方案，可以靠中间件、中间表、数据聚合解决：

  • • ①提前聚合好数据放入第三方中间件中，然后依赖于第三方中间件完成，如ES、Redis。

  • • ②定期跑脚本查询出一些常用的聚合数据，然后建立中间表，每次查询从中间表中读取。

  • • ③首先从所有表中统计出各自的数据，然后在Java中作聚合操作，从而得到出最终数据。

• • 面试官 ：那垂直分库之后的跨库Join问题该怎么解决呢？

• • 我：不同业务表之间需要做关联查询，这种情况很常见，解决方案有四种：

  • • ①在不同库的表中冗余字段，把常用的字段放到需要要数据的表中，避免跨库连表。

  • • ②选择同步数据，通过全局表/广播表等方式，将需要的表数据直接完全同步到相应库中。

  • • ③在设计库表拆分时配置绑定表，具备主外键的表放在一个库，保证数据落到同一数据库。

  • • ④业务系统中组装数据，调用相应服务的API接口获取数据，然后在程序中组装后返回。

• • 面试官 ：那垂直分库之后的分布式事务问题，又该如何解决呢？

• • 我 ：目前业内的主流方案是使用Seata框架，内部提供了多种模式支持，思想如下：

  • • ①Best Efforts 1PC模式。

  • • ②XA 2PC、3PC模式。

  • • ③TTC事务补偿模式。

  • • ④MQ最终一致性事务模式。

• • 面试官：如果垂直分库之后，某些核心库的访问压力依旧比较大怎么办？

• • 我：对核心库再做水平拆分，将核心库再横向拓展出多个节点。

• • 面试官 ：嗯呢，那水平分库之后，如果做数据分页呢？

• • 我：处理分页问题依旧有三种方案：

  • • ①常用的分页数据提前聚合到ES或中间表，运行期间跑按时更新其中的分页数据。

  • • ②搭建大数据中台，将所有子库数据汇聚到其中，后续的分页数据直接从中获取。

  • • ③先从所有子库中先拿到目标页的数据，然后在Service层再做过滤处理。

• • 面试官 ：那水平分库之后如何保障主键字段的唯一性？

• • 我：可以设置自增步长、使用分布式序列算法、或设计第三方主键生成器：

  • • ①通过设置自增机制的起始值和步长，来控制不同节点的ID交叉增长，保证唯一性。

  • • ②在业务系统中，利用特殊算法生成有序的分布式ID，比如Snowflake雪花算法等。

  • • ③设计ID生成器，如使用Redis的incr命令、或创建独立的库专门做自增ID工作。

• • 面试官 ：那水平分库之后数据该具体落入哪个库中呢？

• • 我：这依旧要根据配置好的分片规则来决定，如：

  • • ①随机分片：随机分发数据，但查询时需要读取全部节点才能拿取数据，一般不用。

  • • ②连续分片：每个节点负责存储一个范围内的数据，如DB1:1~500W、DB2:500~1000W....。

  • • ③取模分片：通过数值ID或哈希值与节点数量做取模运算，最终得到数据落入的节点。

  • • ④一致性哈希：根据某个具备唯一特性的字段值计算哈希值，再通过哈希值做取模分片。

  • • ..........

• • 面试官 ：那如果水平分库后，系统再次出现瓶颈时，你会如何扩容呢？

• • 我：如果是对单个业务库做水平分库，采用异步双写法，如果是对水平分库的业务库再扩容，则采用水平双倍扩容法。

• • 面试官 ：那如果水平分库后如何做多维度查询？

• • 我：设计多个列组成路由键，或者为多个列规划多个水平库集群，也可以维护二级索引。

• • 面试官：那你在做分库分表的时候，会考虑哪些技术栈？

• • 我 ：MyCat和Apache-Sharding-Sphere，个人更倾向于后者。

• • 面试官：好的，那分库分表之后，应用程序访问数据库的过程，具体是什么样的呢？

• • 我：我先给你再画张图吧：

  • •

    分库分表工作流程

  • • ①应用程序的SQL不需要改变，只需要配置好分片策略中的逻辑表后就可以了。

  • • ②首先会根据配置好的路由键以及分片算法，去定位到最终的数据节点（数据表）。

  • • ③改写原本的逻辑SQL语句，然后发往具体的数据节点执行。

• • 面试官 ：那你知道Apache-Sharding-Sphere的工作原理嘛？

• • 我：我简单说一下大体流程哈，如下：

  • • 配置加载：在程序启动时，会读取用户的配置好的数据源、数据节点、分片规则等信息。

  • • SQL解析：SQL执行时，会先根据配置的数据源来调用对应的解析器，然后对语句进行拆解。

  • • SQL路由：拆解SQL后会从中得到路由键的值，接着会根据分片算法选择单或多个数据节点。

  • • SQL改写：选择了目标数据节点后，接着会改写、优化用户的逻辑SQL，指向真实的库、表。

  • • SQL执行：对于要在多个数据节点上执行的语句，内部开启多线程执行器异步执行每条SQL。

  • • 结果归并：持续收集每条线程执行完成后返回的结果集，最终将所有线程的结果集合并。

  • • 结果处理：如果SQL中使用了order by、max()、count()...等操作，对结果处理后再返回。

• • 面试官 ：MyCat和Apache-Sharding-Sphere有啥区别呢？

• • 我 ：Sharding-Sphere是由Sharding-Porxy、JDBC两款产品组成的，三者对比如下：

| 对比项 | Sharding-JDBC | Sharding-Proxy | MyCat | |--------|-----------------|----------------|-------| | 性能开销 | 较低 | 较高 | 高 | | 异构支持 | 不支持 | 支持 | 支持 | | 网络次数 | 最少一次 | 最少两次 | 最少两次 | | 异构语言 | 仅支持Java | 支持异构 | 支持异构 | | 数据库支持 | MySQL、PgSQL | 任意数据库 | 任意数据库 | | 配置管理 | 去中心化 | 中心化 | 中心化 | | 部署方式 | 依赖工程 | 中间件 | 中间件 | | 业务侵入性 | 较低 | 无 | 无 | | 连接开销 | 高 | 低 | 低 | | 事务支持 | XA、Base、Local事务 | 同前者 | XA事务 | | 功能丰富度 | 多 | 多 | 一般 | | 社区活跃性 | 活跃 | 活跃 | 一言难尽 | | 版本迭代性 | 高 | 高 | 极低 | | 多路由键支持 | 2 | 2 | 1 | | 集群部署 | 支持 | 支持 | 支持 | | 分布式序列 | 雪花算法 | 雪花算法 | 自增序列 |

八、大戏落幕：放在最后的结语

• • 面试官 ：可以呀，你小子果然是真的精通MySQL！

• • 我：谬赞了！不敢当，不敢当~

• • 面试官 ：最后问你一下，看过MySQL源码没有？

• • 我：没看过太多。

• • 面试官：啊？这样啊，那你期望薪资是多少呢？

• • 我：你看着给就行！

• • 面试官 ：嗯，因为你对MySQL源码还不熟悉，所以给你开三千五，干不干！

• • 我 ：我..............，干！您看人可真准~，嘿嘿

• • 面试官：好，那你这边最快多久能入职呢？

• • 我一脸严谨，站起身来郑重的道：请现在立刻带我去到我的工位！！！

结语

疫情当下，这让原本很多一年一跳一涨薪的开发者，从此进入了互联网寒冬，企业缩招、停招、裁员等情况屡见不鲜，虽然相较于其他传统行业而言，IT开发行业受影响范围小很多，但依旧造成了一系列的连锁反应，随着应届毕业生越来越多，这也让诸多岗位的要求越来越高，但薪资反而越来越低。

同时，无论是工作一段时间、或工作多年的程序员，亦或是刚从校园毕业的应届生，为了能够更好的找到符合意愿的工作，近两年的内卷更为严重，离职待业的开发者在家学技术，在职工作的程序员为了应对随时可能发生的"优化"，也仍然在学习的路上不断前行，也包括了一些毕业后没有找到理想工作的应届生，几乎各自身上都有着学习的影子。

但许许多多在学习路途上"埋头苦干"的小伙伴，基本上都只是为了应付面试而在学习，诸多时候为了使自己面试造火箭的能力更上一层楼，而这章关于MySQL面试的文章，也真心希望能够帮助到每一位准备或正在面试的后端小伙伴，助力于每一位求职者拿到心满意足的Offer，我与诸君共勉之！