(二十五)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:哟,你小子给我显摆上了,小样,看我整不整你就完事!

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

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

读语句:

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

正确:将SQL语句交给优化器处理,进入第④步。

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

②SQL接口在缓存(QueryCache)中根据哈希值检索数据,如果缓存中有则直接返回数据。

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

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

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

⑥存储引擎根据API调用方的操作,去磁盘中检索数据(索引、表数据....)。

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

⑧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执行流程

写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内存结构

面试官:说说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:1500W、DB2:5001000W....。

③取模分片:通过数值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两款产品组成的,三者对比如下:

分库存库对比

分库存库对比

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

面试官:可以呀,你小子果然是真的精通MySQL!

我:谬赞了!不敢当,不敢当~

面试官:最后问你一下,看过MySQL源码没有?

我:没看过太多。

面试官:啊?这样啊,那你期望薪资是多少呢?

我:你看着给就行!

面试官:嗯,因为你对MySQL源码还不熟悉,所以给你开三千五,干不干!

我:我..............,干!您看人可真准~,嘿嘿

面试官:好,那你这边最快多久能入职呢?

我一脸严谨,站起身来郑重的道:请现在立刻带我去到我的工位!!!

结语

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

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

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

关于我
loading