前言

文本已收录至我的GitHub仓库，欢迎Star：github.com/bin39232820…
种一棵树最好的时间是十年前，其次是现在

Tips

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

www.processon.com/view/link/6…

上面的是脑图地址

叨絮

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

第一就是通过一个体系的复习，让自己前面的写的文章再重新的过一遍，总结升华嘛
第二就是通过写文章帮助大家建立一个复习体系，我会将大部分会问的的知识点以点带面的形式给大家做一个导论

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

今天大家一起来复习复习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 有我找的许多的资料送给大家

2021-Java后端工程师面试指南-(MySQL）｜牛气冲天新年征文

前言