一、好戏开场：MySQL底层架构与库表设计
自我感慨：闭关修炼半载、精通MySQL的我又回来啦！虽然迄今为止我的面试大写着失败，但这并不妨碍我继续失败！
开幕场景：此时正挺着啤酒肚迎面向我走来的性感帅哥，正是本次负责考验我的面试官，微风吹过他那一头浓密的秀发，从空气中我嗅到了一丝并非强者的气息，我内心不由自主的称道：“呵，真弱”！
震惊的我：刷~，很快面试官就在我面前缓缓落座！突然！他朝我微微一笑，接着将双手举过头顶，在我一副震惊的神色中，从他那儿48码的大头上，渐渐取下了一顶假发帽，露出了在阳光下略带反光的地中海！该死，这气息的压迫感....好强大！
面试官温文尔雅道：候选者早上好呀，请先做个简单的自我介绍。
我：咳咳，好的。我叫竹子，是一位具有三十年开发经验的三十三岁程序员，精通Java、Golang、Rust、PHP、Scala、C/C++、Spring、Redis....MySQL等技术栈的单词拼写！
面试官：啥？精通MySQL？好，那我接下来考考你。
我：不是，精通MySQL单词的拼写......
面试官：先跟我说说你理解中的MySQL底层架构哈。
我内心：靠，难怪当初在流水线做娃娃时，组长非得安排我去装头，原来我不是装逼那块料啊。
面试官：竹子先生，为了有效开展本场面试，请尽快开始你的回答！
我：我个人理解中的MySQL整体架构，自顶向下分为连接层、服务层、引擎层以及文件层，其作用如下：
①连接层：主要是指数据库连接池，会负责处理所有客户端接入的工作，如用户登录、授权、连接等。
②服务层：这是最重要的一层，所有跨引擎的操作都会放在这里完成，如SQL解析、结果合并、执行计划生成等。
③引擎层：这依旧是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接口会对所有的结果集进行处理（剔除列、合并数据....）并返回。 我嘴里一边念叨，同时拿出了纸笔，然后唰唰两下画出了一幅大体的流程图，如下：
写语句：
- ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
- ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。
- ③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确： 错误：抛出1064错误码及相关的语法错误信息。 正确：将SQL语句交给优化器处理，进入第④步。
- ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
- ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。
- ⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）： 存在： ⑦直接对缓冲区中的数据进行写操作。 ⑧然后等待后台线程将数据刷写到磁盘。 不存在： ⑦根据执行计划，调用存储引擎的API。 ⑧发生磁盘IO，读取磁盘中的数据做写操作。
- ⑨写操作完成后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。
- ⑩将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会干什么工作么？
我：分情况，如果是基于空表创建索引，会直接根据创建的索引类型、存储引擎、字段类型等信息，在本地的表文件/索引文件中，直接创建一个树结构即可。但如果表中有数据，情况会略微复杂一些，如下：
①首先根据索引类型，对索引字段的数据进行对应处理： 唯一索引：判断索引字段的每个值是否存在重复值，如果有则抛出错误码和信息。 主键索引：判断主键字段的每个值是否重复、是否有空值，有则抛出错误信息。 全文索引：判断索引字段的数据类型是否为文本，对索引字段的值进行分词处理。 前缀索引：对于索引字段的值进行截取工作，选用指定范围的值作为索引键。 联合索引：对于组成联合索引的多个列进行值拼接，组成多列索引键。 ........
②接着根据索引的数据结构，再对索引字段的数据进行处理： B+Tree：对索引字段的值进行排序，按照顺序组成B+树结构。 Hash：对索引字段的值进行哈希计算，处理相应的哈希冲突，方便后续查找。 .......
③根据表的存储引擎、索引字段再进行相应处理： InnoDB主键索引：对.ibd文件中的表数据进行重构，将索引键和行数据调整到一块区域中存储。 InnoDB次级索引：因为有聚簇索引，将非聚簇索引的索引值，与行数据对应的聚簇索引键的关联起来。 MyISAM：由于表数据在单独的.MYD文件中，因此可以直接以磁盘指针的关联表数据。
经过上述处理后，创建索引就完成啦！
三、崭露头角：详谈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：所有写操作加临键锁（具备互斥特性），所有读操作加共享锁。
面试官：可以，先考你到这里，时间不早了，我带你去吃个午饭回来接着聊~
我：好的，开我车去吧（一边说一边从口袋里慢慢掏出了*博兰**基尼的车钥匙）。
面试官：........