引言

本文为社区首发签约文章，14天内制止转载，14天后未获授权制止转载，侵权必究！

从开设《MySQL专栏》至今，前面二十多个大章节中叨叨絮絮了许多内容，看到这儿也意味着本专栏即将挨近尾声，由于前面的每章内容都较为全面，因此每章的篇幅都并不算短，这关于一些想要面试前作稳固复习的小伙伴并不友好，究竟篇幅过长很难将中心常识点提炼出来，因此本章会要点提炼中心内容，将专栏中触及的一切要点凝练成一份 《MySQL面试通关秘籍》。

(二十八)MySQL面试通关秘籍：这次你也可以在简历写上精通MySQL！

由于首要解说MySQL面试的中心常识点，因此不会对某些技能细节进行深入解说，但文中提到的技能点都会附上详细解说的链接，因此当大家看到自己不了解的面试点时，可直接戳进对应的链接中做深入研讨。

一起，为了防止呈现背诵“面试八股文”那样的枯燥感，接下来通篇都会选用对话面试官的方法阐述！当然，看完如若感觉对你面试会有些许协助（现在不必今后迟早会用的），记住点赞、收藏、重视三连支撑一下噢~

一、好戏开场：MySQL底层架构与库表规划

  • 自我慨叹：闭关修炼半载、通晓MySQL的我又回来啦！尽管迄今为止我的面试大写着失利，但这并不妨碍我持续失利！
  • 开幕场景：此刻正挺着啤酒肚迎面向我走来的性感帅哥，正是本次担任考验我的面试官，微风吹过他那一头稠密的秀发，从空气中我嗅到了一丝并非强者的气息，我心里情不自禁的称道：“呵，真弱”！
  • 震动的我：刷~，很快面试官就在我面前缓缓落座！忽然！他朝我轻轻一笑，接着将双手举过头顶，在我一副震动的神色中，从他那儿48码的大头上，渐渐取下了一顶假发帽，露出了在阳光下略带反光的地中海！该死，这气息的压迫感….好强大！
  • 面试官温文尔雅道：候选者早上好呀，请先做个简略的自我介绍。
  • 我：咳咳，好的。我叫竹子，是一位具有三十年开发经历的三十三岁程序员，通晓Java、Golang、Rust、PHP、Scala、C/C++、Spring、Redis....MySQL等技能栈的单词拼写！
  • 面试官：啥？通晓MySQL？好，那我接下来考考你。
  • 我：不是，通晓MySQL单词的拼写……
  • 面试官：先跟我说说你了解中的MySQL底层架构哈。
  • 我心里：靠，难怪当初在流水线做娃娃时，组长非得安排我去装头，本来我不是装逼那块料啊。
  • 面试官：竹子先生，为了有用展开本场面试，请尽快开端你的答复！
  • 我：我个人了解中的MySQL全体架构，自顶向下分为衔接层、服务层、引擎层以及文件层，其作用如下：
    • ①衔接层：首要是指数据库衔接池，会担任处理一切客户端接入的作业，如用户登录、授权、衔接等。
    • ②服务层：这是最重要的一层，一切跨引擎的操作都会放在这儿完结，如SQL解析、成果兼并、履行计划生成等。
    • ③引擎层：这仍旧是MySQL较为重要的一层，服务层首要是拟定履行计划和等候成果，但读写数据的详细操作都需求经过引擎层来完结，引擎层决议着表数据读写方法和存储方法。
    • ④文件层：这是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：哟，你小子给我显摆上了，小样，看我整不整你就完事！
  • 面试官：都给我先简略的说一遍。
  • 我：好的，其实两者大体上并无差异，首要差异在于一些细节上的改动，先说说读句子的履行流程吧。
  • 读句子：
    • ①先将SQL发送给SQL接口，SQL接口会对SQL句子进行哈希处理。
    • ②SQL接口在缓存（QueryCache）中依据哈希值检索数据，假如缓存中有则直接回来数据。
    • ③缓存中未射中时会将SQL交给解析器，解析器会判别SQL句子是否正确：
      • 过错：抛出1064过错码及相关的语法过错信息。
      • 正确：将SQL句子交给优化器处理，进入第④步。
    • ④优化器依据SQL拟定出不同的履行计划，并择选出最优的履行计划。
    • ⑤作业线程依据履行计划，调用存储引擎所供给的API获取数据。
    • ⑥存储引擎依据API调用方的操作，去磁盘中检索数据（索引、表数据….）。
    • ⑦发送磁盘IO后，关于磁盘中契合要求的数据逐条回来给SQL接口。
    • ⑧SQL接口会对一切的成果集进行处理（除掉列、兼并数据….）并回来。
    • 我嘴里一边念叨，一起拿出了纸笔，然后唰唰两下画出了一幅大体的流程图，如下：
    • (二十八)MySQL面试通关秘籍：这次你也可以在简历写上精通MySQL！
  • 写句子：
    • ①先将SQL发送给SQL接口，SQL接口会对SQL句子进行哈希处理。
    • ②在缓存中依据哈希值检索数据，假如缓存中有则将对应表的一切缓存悉数删去。
    • ③经过缓存后会将SQL交给解析器，解析器会判别SQL句子是否正确：
      • 过错：抛出1064过错码及相关的语法过错信息。
      • 正确：将SQL句子交给优化器处理，进入第④步。
    • ④优化器依据SQL拟定出不同的履行计划，并择选出最优的履行计划。
    • ⑤在履行开端之前，先记载一下undo-log日志和redo-log(prepare状况)日志。
    • ⑥在缓冲区中查找是否存在当时要操作的行记载或表数据（内存中）：
      • 存在：
        • ⑦直接对缓冲区中的数据进行写操作。
        • ⑧然后等候后台线程将数据刷写到磁盘。
      • 不存在：
        • ⑦依据履行计划，调用存储引擎的API。
        • ⑧产生磁盘IO，读取磁盘中的数据做写操作。
    • ⑨写操作完结后，记载bin-log日志，一起将redo-log日志中的记载改为commit状况。
    • ⑩将SQL履行耗时及操作成功的成果回来给SQL接口，再由SQL接口回来给客户端。
    • 一起大笔一挥，又在纸上画出了一幅流程图，如下：
    • (二十八)MySQL面试通关秘籍：这次你也可以在简历写上精通MySQL！
  • 面试官心里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会干什么作业么？
  • 我：分状况，假如是依据空表创立索引，会直接依据创立的索引类型、存储引擎、字段类型等信息，在本地的表文件/索引文件中，直接创立一个树结构即可。但假如表中有数据，状况会略微杂乱一些，如下：
    • ①首要依据索引类型，对索引字段的数据进行对应处理：
      • 仅有索引：判别索引字段的每个值是否存在重复值，假如有则抛出过错码和信息。
      • 主键索引：判别主键字段的每个值是否重复、是否有空值，有则抛出过错信息。
      • 全文索引：判别索引字段的数据类型是否为文本，对索引字段的值进行分词处理。
      • 前缀索引：关于索引字段的值进行截取作业，选用指定规模的值作为索引键。
      • 联合索引：关于组成联合索引的多个列进行值拼接，组成多列索引键。
      • ........
    • ②接着依据索引的数据结构，再对索引字段的数据进行处理：
      • B+Tree：对索引字段的值进行排序，依照次序组成B+树结构。
      • Hash：对索引字段的值进行哈希核算，处理相应的哈希抵触，便利后续查找。
      • .......
    • ③依据表的存储引擎、索引字段再进行相应处理：
      • InnoDB主键索引：对.ibd文件中的表数据进行重构，将索引键和行数据调整到一块区域中存储。
      • InnoDB次级索引：由于有聚簇索引，将非聚簇索引的索引值，与行数据对应的聚簇索引键的相关起来。
      • MyISAM：由于表数据在独自的.MYD文件中，因此能够直接以磁盘指针的相关表数据。
    • 经过上述处理后，创立索引就完结啦！
  • 面试官：讲的蛮好，再奖赏你答复一下：依据索引的查找数据进程。
  • 我：这个还算比较简略，面试官你仔细听好咯！
    • ①首要依据查询句子的条件字段，去内存中找到对应的索引根节点。
    • ②经过根节点中记载的叶节点地址，逐步去遍历查找索引树，终究定位到方针数据所在的叶子节点。
    • ③但遍历索引树的进程，选用的是二分查找法，拿到一个叶节点后，假如SQL条件比它大，会去持续读取右边的叶节点，反之则读取左边的叶节点，然后再进行判别。
    • ④运用二分查找法，找到一个方针数据后，这儿会依据不同的索引，来履行不同的操作：
      • InnoDB聚簇索引：直接从索引树中得到行数据，由于行数据和聚簇索引存储在一块。
      • InnoDB次级索引：看是否能够运用索引掩盖机制获取数据，不可则触发回表动作得到数据。
      • MyISAM的索引：依据索引键中记载的磁盘地址，直接去磁盘中读取行数据。
    • ⑤读取到一个方针数据后，假如是依据主键/仅有索引在查询，则会立马停止查找，假如是一般索引则会持续向下遍历。
    • ⑥假如是规模查询操作，会直接依据叶子节点的前后指针，获取其他的索引键数据，然后重复第④步，得到方针行数据。
  • 面试官：能够，再说说写入句子履行时，会对索引产生什么影响呢？
  • 我：好的，但刺进、删去、修改数据时，都会存在纤细不同，我都简略说说吧：
    • 增：
      • 假如索引字段具有自增特性，直接把刺进的字段数据作为索引键，追加到索引树终究一个节点存储。
      • 假如索引字段是无序的，则先对其排序核算（字符串转换为ascii码），核算出一个方位并刺进。
    • 删：
      • 会先依据删去的条件查找索引树，接着去聚簇索引树找到对应的行数据，先删行数据和聚簇索引键，接着再删去其他次级索引的数据。
    • 改：
      • 和删操作相似，先找到数据，再改行数据，再改聚簇索引键，再改次级索引数据。
  • 面试官：嗯嗯，自己有了解过索引的最左匹配准则么？
  • 我：索引最左匹配准则是依据联合索引而言的，比方一个联合索引由A、B、C三个字段组成，那么在写SQL句子时，最好依照索引字段的次序来运用索引，假如写的SQL中不包含榜首个A字段，一般都无法运用这个联合索引查询数据。
  • 我：一起，假如查询句子中运用了A、C字段，但没有运用B字段，也无法彻底运用联合索引。
  • 我：由于MySQL的联合索引会从左往右匹配数据，所以在规划索引时，最好把查询频率高的字段放在前面，这样才干充分运用最左匹配准则查询数据，但MySQL8.0中也推出了一种名为索引跳动式扫描的机制，能够打破联合索引的最左匹配准则查找数据。
  • 面试官：嗯呢，那MySQL索引除开索引掩盖、跳动扫描外，还有其他优化机制吗？
  • 我：还有MySQL5.6中引进的索引下推机制、MRR机制，这两种机制能够在很大程度上削减索引查询的磁盘IO，以及离散性的磁盘IO。
  • 面试官：嗯，聊了这么多理论，考你一些实践性的常识，怎样才干很好的运用索引呢？
  • 我：这个也不难，能够从两个视点出发，一是合理的创立索引，二是编写合理的SQL句子运用索引，先来说说什么是合理的创立索引：
    • 树立索引时需求恪守的准则：
      • ①常常频频用作查询条件的字段应酌情考虑为其创立索引。
      • ②表的主外键或连表字段，有必要树立索引，由于能很大程度进步连表查询的功用。
      • ③树立索引的字段，一般值的差异性要足够高，这样才干进步索引的检索功率。
      • ④树立索引的字段，值不应该过长，假如较长的字段要树立索引，能够挑选前缀索引。
      • ⑤树立联合索引，应当遵从最左前缀准则，将多个字段之间按优先级次序组合。
      • ⑥常常依据规模取值、排序、分组的字段应树立索引，由于索引有序，能加快排序时刻。
      • ⑦关于仅有索引，假如确认不会运用该字段排序，那能够将结构改为Hash结构。
      • ⑧尽量运用联合索引替代单值索引，联合索引比多个单值索引查询功率要高。
    • 不适宜树立索引的一些状况：
      • ❶值常常会增删改的字段，不适宜树立索引，由于每次改动后需保护索引结构。
      • ❷一个字段存在许多的重复值时，不适宜树立索引，比方之前举例的性别字段。
      • ❸索引不能参加核算，因此常常带函数查询的字段，并不适宜树立索引。
      • ❹一张表中的索引数量并不是越多越好，一般操控在3，最多不能超过5。
      • ❺树立联合索引时，必定要考虑优先级，查询频率最高的字段应当放首位。
      • ❻当表的数据较少，不应当树立索引，由于数据量不大时，保护索引反而开支更大。
      • ❼索引的字段值无序时，不引荐树立索引，由于会形成页割裂，尤其是主键索引。
    • 我：一般依照上述这些准则树立索引，避开一些不适宜树立索引的状况，就能规划出一个很不错的索引，接着聊聊写SQL句子时的留意点，首要是防止索引失效即可，索引失效的场景有下面这些状况：
      • 查询中带有OR会导致索引失效。
      • 含糊查询中like以%开头导致索引失效。
      • 字符类型查询时不带引号导致索引失效。
      • 索引字段参加核算导致索引失效。
      • 字段被用于函数核算导致索引失效。
      • 违背最左前缀准则导致索引失效。
      • 不同字段值比照导致索引失效。
      • 反向规模操作导致索引失效。
  • 我：我们在写SQL句子的时候，能够刻意避开这些会导致索引失效的场景即可。
  • 面试官：哦哟，你小子不赖呀，那你终究再给我说说，运用索引的优点和坏处吧。
  • 我：好，凡事有利害，索引也不例外，除开带来了优点之外也带来了影响，如下：
    • 运用索引带来的优势：
      • ①整个数据库中，数据表的查询速度直线进步，数据量越大时作用越明显。
      • ②经过创立仅有索引，能够保证数据表中的数据仅有性，无需额定树立仅有束缚。
      • ③在运用分组和排序时，同样能够显著削减SQL查询的分组和排序的时刻。
      • ④连表查询时，依据主外键字段上树立索引，能够带来十分明显的功用进步。
      • ⑤索引默许是B+Tree有序结构，依据索引字段做规模查询时，功率会明显进步。
      • ⑥从MySQL全体架构而言，削减了查询SQL的履行时刻，进步了数据库全体吞吐量。
    • 运用索引带来的影响：
      • ①树立索引会生成本地磁盘文件，需求额定的空间存储索引数据，磁盘占用率会变高。
      • ②写入数据时，需求额定保护索引结构，增、删、改数据时，都需求额定操作索引。
      • ③写入数据时保护索引需求额定的时刻开支，履行写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中的锁机制呢？
  • 我：能够呀，其实锁的叫法有许多，但本质上就只要同享锁、排他锁这两种，只不过加的粒度不同、机遇不同、方法不同，就演变出了许多叫法，整个体系如下：
    • 以锁粒度的维度区分：
      • ①表锁：
        • 大局锁：加上大局锁之后，整个数据库只能答应读，不答应做任何写操作。
        • 元数据锁 / MDL锁：依据表的元数据加锁，加锁后整张表不答应其他业务操作。
        • 意向锁：这个是InnoDB中为了支撑多粒度的锁，为了兼容行锁、表锁而规划的。
        • 自增锁 / AUTO-INC锁：这个是为了进步自增ID的并发刺进功用而规划的。
      • ②页面锁
      • ③行锁：
        • 记载锁 / Record锁：也便是行锁，一条记载和一行数据是同一个意思。
        • 空隙锁 / Gap锁：InnoDB中处理幻读问题的一种锁机制。
        • 临建锁 / Next-Key锁：空隙锁的升级版，一起具有记载锁+空隙锁的功用。
    • 以互斥性的维度区分：
      • 同享锁 / S锁：不同业务之间不会相互排挤、能够一起获取的锁。
      • 排他锁 / X锁：不同业务之间会相互排挤、一起只能答应一个业务获取的锁。
      • 同享排他锁 / SX锁：MySQL5.7版别中新引进的锁，首要是处理SMO带来的问题。
    • 以操作类型的维度区分：
      • 读锁：查询数据时运用的锁。
      • 写锁：履行刺进、删去、修改、DDL句子时运用的锁。
    • 以加锁方法的维度区分：
      • 显现锁：编写SQL句子时，手动指定加锁的粒度。
      • 隐式锁：履行SQL句子时，依据阻隔等级自动为SQL操作加锁。
    • 以思维的维度区分：
      • 乐观锁：每次履行前以为自己会成功，因此先测验履行，失利时再获取锁。
      • 失望锁：每次履行前都以为自己无法成功，因此会先获取锁，然后再履行。
  • 面试官：那行锁和表锁之间有啥差异呢？
  • 我：首要是粒度不同，表锁是指对一整张表加锁，当加锁后，其他来拜访该表的业务都会被堵塞，而行锁的粒度则小许多，是指针关于一条/多条数据加锁，并不会堵塞操作同一表的业务，而仅仅只会堵塞操作相同行数据的业务。
  • 面试官：嗯呢，那你所说的同享锁和排他锁，两者的差异是啥？
  • 我：同享锁答应多个业务一起持有，而排他锁在同一时刻内只能答应一个业务持有，也便是凡是呈现排他锁的场景，其他业务都需求堵塞等候。
  • 面试官：那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参数操控：
    • innodb_flush_log_at_trx_commit：
      • 0：距离一段时刻，然后再刷写一次日志到磁盘（功用最佳）。
      • 1：每次提交业务时，都刷写一次日志到磁盘（功用最差，最安全，默许战略）。
      • 2：有业务提交的状况下，每距离一秒时刻刷写一次日志到磁盘。
    • sync_binlog：
      • 0：同上述innodb_flush_log_at_trx_commit参数的2。
      • 1：同上述innodb_flush_log_at_trx_commit参数的1，每次提交业务都会刷盘，默许战略。
  • 一起，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会依据三个链表来办理一切缓冲页，一切缓冲页会依据类型不同，别离加入到不同的链表中，每个缓冲页经过操控块中的指针，形成逻辑接连的链表结构：
    • Free List：担任记载闲暇页，为了运用时能更快的找到闲暇缓冲页。
      • 当需求运用缓冲页时，会从闲暇链表中拿出一个闲暇页运用。
      • 当清空一个数据页后，又会将没有数据的缓冲页加入到闲暇链表。
    • Lru List：记载一切现已运用过的缓冲页，为了便利筛选已运用的内存页。
    • Flush List：担任记载一切改动页，为了刷盘时能够更快的找到改动数据页。
      • 当一个缓冲页中的数据产生改动后，会从Lru链表移动到Flush链表中。
      • 当改动页的数据刷写完结后，又会从Flush链表中移回Lru链表。
  • 面试官：那一个磁盘数据被载入内存缓冲页，会一向逗留在内存吗？
  • 我：不会的，由于这样做会导致内存占用无限增加，终究引发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！
  • 面试官：说说MySQL常用的InnoDB、MyISAM两款引擎之间的差异吧。
  • 我：能够，两者之间其实有许许多多的纤细距离，但我就简略从七个大方向总结一下：
    • ①磁盘文件不同：
      • MyISAM引擎的表会生成三个磁盘文件：
        • table_name.frm：该文件中存储表的结构信息。
        • table_name.MYD：该文件中存储表的行数据。
        • table_name.MYI：该文件中存储表的索引数据。
      • 而InnoDB引擎的表只会生成两个磁盘文件：
        • 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引擎。
  • 面试官：好的，答复的蛮不错。

五、戏入高潮：高档特性、功用调优与线上排查

  • 面试官：有用过存储进程吗？能否说说存储进程的优劣势呢？
  • 我：之前在项目中完结一些特别需求时用过，个人了解中，存储进程的优劣势如下：
    • 运用存储进程的优点：
      • 复用性：存储进程被创立后能够被重复调用，表结构产生改动也只需修改存储进程。
      • 灵活性：存储进程中能够用变量、流程操控句子，能够完结杂乱的条件查询和较繁琐的运算。
      • 省资源：调用存储进程只需求传输调用句子和参数，无需传输一条体积较大的SQL句子。
      • 高功用：履行屡次后，会被编译成机器码驻留在线程缓冲区，后续履行时无需重新编译。
      • 安全性：关于不同的存储进程，可设置用户的履行权限，包含内部句子对客户端来说是黑盒操作，减小了SQL被暴露的危险。
    • 运用存储进程带来的问题：
      • CPU开支大：假如其间触及许多逻辑运算作业，会导致MySQL所在的服务器CPU飙升。
      • 内存占用高：屡次履行后的存储进程会被编译成机器码，长期驻留在内存中，占用不小空间。
      • 保护性差：过于杂乱的存储进程阅览性较差，而且不支撑Debug调试，出错时难以排查。
  • 面试官：那我假如想在新增数据之后，再做某个操作，不必AOP切面的话怎样完结呢？
  • 我：能够用MySQL的触发器来完结，insert、delete、update三个操作都能够添加前/后置处理器，作用与AOP切面相似。
  • 面试官：那假如我想守时履行某段SQL句子，不想用守时调度框架怎样办呢？
  • 我：能够用MySQL的守时器，支撑按年、季、月、周、日、时、分、秒、毫秒等精度触发。
  • 面试官：那一般你写SQL时，常常会用那些句子、关键字、和函数呢？
  • 我：......，我给你一个指令大全，您自己看吧。
  • 面试官：前面看你有提到衔接层优化，你对MySQL功用优化这块了解吗？
  • 我：同样略知一二，功用调优是一门大学识，没有通用的计划，调优都要取决于现有环境、业务来谈，MySQL的功用优化能够从五个维度来说：
    • ①衔接层的优化：调整客户端DB衔接池的参数和DB衔接层的参数。
    • ②结构的优化：合理的规划库表结构，表中字段依据业务挑选适宜的数据类型、索引。
    • ③参数/装备优化：调整参数的默许值，依据业务将各类参数调整到适宜的巨细。
    • ④架构优化：引进中心件减轻数据库压力，优化MySQL架构进步可用性。
    • ⑤编码优化：依据库表结构、索引结构优化业务SQL句子，进步索引射中率。
  • 面试官：你前面说的衔接层优化，怎样装备衔接数来着？
  • 我：CPU中心数*2，假如硬盘原料是SSD的，那么还能够再加个一，总的来说便是依据硬件来装备。
  • 面试官：好，那我这个衔接数是配在客户端仍是MySQL呢？
  • 我：假如MySQL只要一个应用程序拜访，在客户端配好最大衔接数就行，假如供给给多个应用拜访，则需求限制一下MySQL的最大衔接数。
  • 面试官：那你再详细说说你提到的结构优化是咋回事呢？
  • 我：首要是表结构、字段结构、索引结构这三方面的规划，我简略的聊一聊：
    • 表结构优化：挑选适宜的引擎、精细化字段数量、挑选适宜的主键、恰当冗余字段和中心表。
    • 字段结构优化：
      • ①在保证足够运用的规模内，挑选最小数据类型，如能用int就别用bigint。
      • ②尽量防止索引字段值为NULL，由于字段空值过多会影响索引功用。
      • ③在条件答应的状况下，尽量运用最简略的类型替代杂乱的类型。
    • 索引结构优化：
      • ①尽量防止创立过多的单列索引，关于多个字段要用索引，能够用联合索引替代。
      • ②关于一个值较长的字段创立索引，能够运用前N个字节，创立前缀索引。
      • ③索引类型必定要挑选合理，如常常做含糊查询的字段，可用全文索引替代一般索引。
      • ④某个字段在业务中无需用作规模查询时，能够经过Hash结构替代B+Tree结构。
  • 面试官：参数优化呢？
  • 我：参数优化不太熟，有专门的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主从集群的数据同步的原理了解么？
  • 我：这块还比较了解，我先给您画个图：
    • (二十八)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，个人更倾向于后者。
  • 面试官：好的，那分库分表之后，应用程序拜访数据库的进程，详细是什么样的呢？
  • 我：我先给你再画张图吧：
    • (二十八)MySQL面试通关秘籍：这次你也可以在简历写上精通MySQL！
    • ①应用程序的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、MyCat两款产品组成的，三者比照方下：
比照项 Sharding-JDBC Sharding-Proxy MyCat
功用开支 较低 较高 高
异构支撑 不支撑 支撑 支撑
网络次数 最少一次 最少两次 最少两次
异构言语 仅支撑Java 支撑异构 支撑异构
数据库支撑 MySQL、PgSQL 恣意数据库 恣意数据库
装备办理 去中心化 中心化 中心化
布置方法 依赖工程 中心件 中心件
业务侵入性 较低 无 无
衔接开支 高 低 低
业务支撑 XA、Base、Local业务 同前者 XA业务
功用丰厚度 多 多 一般
社区活泼性 活泼 活泼 一言难尽
版别迭代性 高 高 极低
多路由键支撑 2 2 1
集群布置 支撑 支撑 支撑
分布式序列 雪花算法 雪花算法 自增序列

八、大戏闭幕：放在终究的结语

  • 面试官：能够呀，你小子果然是真的通晓MySQL！
  • 我：谬赞了！不敢当，不敢当~
  • 面试官：终究问你一下，看过MySQL源码没有？
  • 我：没看过太多。
  • 面试官：啊？这样啊，那你希望薪资是多少呢？
  • 我：你看着给就行！
  • 面试官：嗯，由于你对MySQL源码还不了解，所以给你开三千五，干不干！
  • 我：我..............，干！您看人可真准~，嘿嘿
  • 面试官：好，那你这边最快多久能入职呢？
  • 我一脸严谨，站起身来郑重的道：请现在马上带我去到我的工位！！！

结语

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

一起，无论是作业一段时刻、或作业多年的程序员，亦或是刚从学校结业的应届生，为了能够更好的找到契合志愿的作业，近两年的内卷更为严重，离任失业的开发者在家学技能，在职作业的程序员为了应对随时或许产生的“优化”，也仍然在学习的路上不断前行，也包含了一些结业后没有找到抱负作业的应届生，几乎各本身上都有着学习的影子。

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