2021-Java后端工程师面试指南-(MySQL）

前言

文本已收录至我的GitHub仓库，欢迎Star：https://github.com/bin392328206/six-finger

种一棵树最好的时间是十年前，其次是现在

Tips

面试指南系列，很多情况下不会去深挖细节，是小六六以被面试者的角色去回顾知识的一种方式，所以我默认大部分的东西，作为面试官的你，肯定是懂的。

https://www.processon.com/view/link/600ed9e9637689349038b0e4

上面的是脑图地址

叨絮

可能大家觉得有点老生常谈了，确实也是。面试题，面试宝典，随便一搜，根本看不完，也看不过来，那我写这个的意义又何在呢？其实嘛我写这个的有以下的目的

• 第一就是通过一个体系的复习，让自己前面的写的文章再重新的过一遍，总结升华嘛

• 第二就是通过写文章帮助大家建立一个复习体系，我会将大部分会问的的知识点以点带面的形式给大家做一个导论

然后下面是前面的文章汇总

• 2021-Java后端工程师面试指南-(引言)

• 2021-Java后端工程师面试指南-(Java基础篇）

• 2021-Java后端工程师面试指南-(并发-多线程)

• 2021-Java后端工程师面试指南-(JVM）

今天大家一起来复习复习MySQL吧

聊聊MySql的结构吧

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服 务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都 在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、 Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成 为了默认存储引擎。

聊聊InnoDB和MyISAM的区别吧

• 第一个也是最重要的一个 InnoDB支持事务，MyISAM不支持

• 在MySQL中，表级锁有两种模式：表共享读锁，表独占写锁。也就是说对于MyISAM引擎的表，多个用户可以对同一个表发起读的请求，但是如果一个用户对表进行写操作，那么则会阻塞其他用户对这个表的读和写。InnoDB引擎的表是通过索引项来加锁实现的，即只有通过索引条件检索数据的时候，InnoDB才会使用行级锁，否则也会使用表级锁。

• InnoDB聚集索引，MyISAM 非聚集索引

• 企业级生成环境强制用InnoDB，所以下面的面试题都是基于InnoDB。

说说一个查询SQL的执行过程

• 连接器：首先肯定和mysql建立连接的过程

• 查询缓存:在8以前，mysql会把相同的sql，缓存起来，但是因为发现效率不是那么好，8之后删除了

• 分析器: 如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此 需要对 SQL 语句做解析

• 优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引

• 执行器：MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶 段，开始执行语句

• 返回数据给到客户端

说说一条SQL的插入流程

update T set c=c+1 where ID=2;

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内 存，然后再返回。

• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的 一行数据，再调用引擎接口写入这行新数据。

• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务

• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状 态，更新完成。

说说Buffer Pool吧

• 它是mysql 一个非常重要的内存组件，因为是在内存中操作的，所以速度比较快

• 建议设置合理的buffer pool的大小，如果大小在内存的百分60合适

• 要明确的是pool的结构是一页一页的

• 如果内存够大，可以多设计几个pool

Buffer Pool脏数据页到底为什么会脏

• 是因为我们新增 更新 删除操作的时候只是对内存进行操作，和对我们redo log日志进行操作，所以呢就会有脏数据

• 在buffer pool里面 有一个维护脏数据页的双向链表，用来明确哪个数据页需要刷

• 然后还有就是lru链表，就是假设我们的pool满了，那么我们肯定要把一些数据删除，就是lru算法了（基于冷热数据分离的思想的lru）

说说InnoDB页

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

说说InnoDB行格式是怎么样的

就是我们mysql里面一行的数据，再innodb里面分为了2个部分

• 一个是我们原始的数据，真实的数据，也就是列的值

• 还有一个额外的数据 一个是变长字段的列表，一个是NUll值，还有一个是记录头信息

聊聊整个磁盘的存储的结构

首先是InnoDB的页存储结构，我们知道最大的结构是表，表里面可以分为很多个区，每个区里面又有很多的页 多个不同的页组成的是一个双向链表，而每个页里面的数据行会按主键的大小组成一个单向链表，并且每4到8个数据组成一个槽，每个槽存储在pageDirectoy里面 ，当我们要查询页的行数据的时候，可以先定位到页，然后用2分法定位到槽，然后遍历槽，来定位到当前行的数据。

聊聊索引吧

首先哈 索引的本质是什么呢？其实索引就是一直加快磁盘查询速度的一些数据结构，因为我们磁盘i/o的性能比较慢，索引可以加快我们的查询速度。

聊聊有哪些数据结构适合做索引结构的，优缺点是什么

• Hash索引：hash表，我相信大家都很熟悉了，他的优点查询速度快，但是他不支持范围查询，哈希表这种结构适用于只有等值查询的场景

• 二叉树：如果数据多了，树高会很高，查询的成本就会随着树高的增加而增加。

• B树：B树已经是不错的一个索引结构了，但是他的子节点也存储数据，所以还是不能控制数高，因为树的高度，其实就是代表我们的io

• B+树：其实很简单，我们看一下上面的数据结构，最开始的Hash不支持范围查询，二叉树树高很高，只有B树跟B+有的一比。B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

你可以说说InnoDB 的索引模型吗？

• 主键索引，在 InnoDB 里，主键索引也被称为聚簇索引

• 普通索引，就是我们一般的索引

• 唯一索引，具体排他性的索引

• 组合索，可以多个列的索引

说说怎么从磁盘上加载数据，也就是查询的执行方式

MySQL的查询的执行方式大致分为下边两种：

• 使用全表扫描进行查询

• 使用索引进行查询

  • 针对主键或唯一二级索引的等值查询

  • 针对普通二级索引的等值查询

  • 针对索引列的范围查询

  • 直接扫描整个索引

磁盘访问方式的分类

• const：通过主键或者唯一二级索引列与常数的等值比较来定位一条记录

• ref：对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法

• range：类似于范围查询的方式

• index：这个是什么意思呢？就是比如我们的where条件不符合查询的索引，但是查询的条件在一个组合索引中，那我们遍历索引数，比遍历数据数要快。

• all:最直接的查询执行方式就是全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引.

说说常见的sql需要注意到的点，也就是sql优化

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

• 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要

• 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段

• 尽量避免大事务操作，提高系统并发能力

• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

• 最左原则，是设计组合索引的原则。

• 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

说说EXPLAIN关键字吧

小六六挑选几个有参考价值的列来说说。

• id 每个单查询都有，id越大越先执行,id相同表示加载表的顺序是从上到下。

• type ：这个字段就是我们前面说的查询的分类了 重点关注

• possible_keys  可能的索引

• key 实际用到的索引 重点关注

• key_len 实际使用的索引长度

• rows 预估要读取的行数 重点关注

• Extra 额外的信息 比如看是否用到回表 Using index,或者是否用到了临时表之类的

说说count(字段) count(主键 id) count(1) count(*)

• count(主键 id) ，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加

• count(1) ，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

• count(字段)，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

• count( ) ，并不会把全部字段取出来，而是专门做了优化，不取值。count( ) 肯定不是 null，按行累加

• 按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count( )，所以我建议你，尽量使用 count( )。

事务

说说mysql的事务吧

ACID 这个肯定得背的

• 原子性(A)：事务是最小单位，不可再分

• 一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败

• 隔离性(I)：事务A和事务B之间具有隔离性

• 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

聊聊它的隔离级别吧

• 读未提交 会发生脏读

• 读已提交 会发生 不可重复读

• 可重复读 会发生 幻读

• 串行化，没有问题

说说sping默认的事务传播级别

• Spring中事务的默认实现使用的是AOP，也就是代理的方式，如果大家在使用代码测试时，同一个Service类中的方法相互调用需要使用注入的对象来调用，不要直接使用this.方法名来调用，this.方法名调用是对象内部方法调用，不会通过Spring代理，也就是事务不会起作用

• REQUIRED(Spring默认的事务传播类型)，如果当前没有事务，则自己新建一个事务，如果当前存在事务，则加入这个事务，这个我们一般用的最多

• SUPPORTS 当前存在事务，则加入当前事务，如果当前没有事务，就以非事务方法执行

• MANDATORY 当前存在事务，则加入当前事务，如果当前事务不存在，则抛出异常。

• REQUIRES_NEW 创建一个新事务，如果存在当前事务，则挂起该事务。

• NOT_SUPPORTED 始终以非事务方式执行,如果当前存在事务，则挂起当前事务

说说MVCC呗，谈谈你自己的看法

• 在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

• 在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：trx_id和roll_pointer

• mvcc通过排它锁的形式来修改数据

• 修改之前会把数据放到undolog日志，如果事务提交，那就条件到数据里面，如果事务回滚，则放弃这个事务链

• 读已提交和可重复读的MVcc的区别就是 再这个事务级别下，一个事务操作里面每次查询都会生成一个新的视图，更新自己最小事务id和最大事务id,然后可重复读不会，它只会在事务开始的时候生成一个一致性视图。

Mysql的主从架构聊聊

说说什么是mysql主从复制？

主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库上，然后在从数据库上对这些日志进行重新执行，从而使从数据库和主数据库的数据保持一致。

那你聊聊主从复制的原理

• MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中；

• 主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性；

• MySql通过三个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑着从库上；

• 当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，如下图所示。

聊聊Mysql的分库分表吧

首先来说说分库分表的各种类型吧

• 垂直分表：这个就是我们说的把大表变成小表，也就是分字段

• 水平分表，就是说我们把数据分到多个表里面

• 按月分表，也就是这些数据不会变了，然后按时间分。查询的时候不能跨月查询

• 分库的话，一般现在一个库就是一个服务（按业务分库），这样分，或者是多个库一个服务（按表分库）

说说常用的分库分表中间件

• mycat：阿里开源的，但是目前生态不那么好了，

• Sharding Sphere 这个很好，融合了Sharding-JDBC、Sharding-Proxy、Sharding-Sidecar 文档齐全

• 其实分库分表你不用中间件自己也能做，就是他们也是代理的模式帮你去聚合查询，如果你有5个库，那你要查排序，是不是每个库都要查出来，最后总的合起来排序这样。分页这些都是，实现起来还是很麻烦

• ShardingSphere-JDBC  在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

• ShardingSphere-Proxy 是 Apache ShardingSphere 的第二个产品。它定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。

说说如何满足“跨越多个水平切分数据库，且分库依据与排序依据为不同属性，并需要进行分页”的查询需求

• 服务层通过uid取模将数据分布到两个库上去之后，每个数据库都失去了全局视野，数据按照time局部排序之后由于不清楚到底是哪种情况，所以必须每个库都返回3页数据

• 业务折衷法-禁止跳页查询 用正常的方法取得第一页数据，并得到第一页记录的time_max

结束

Mysql就这些吧，也不是很全，分库分表有很多实战，但是我们在公司用的hbase,所以对于这块涉及没有那么多，接下来Redis吧

日常求赞

好了各位，以上就是这篇文章的全部内容了，能看到这里的人呀，都是 真粉 。

创作不易，各位的支持和认可，就是我创作的最大动力，我们下篇文章见

微信 搜 "六脉神剑的程序人生" 回复888 有我找的许多的资料送给大家