H5W3
当前位置:H5W3 > java > 正文

【Java】2021-Java后端工程师面试指南-(MySQL)

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

bin392328206发布于 今天 03:50

前言

Tips

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

上面的是脑图地址

叨絮

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

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

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

  • 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吧

日常求赞

面试mysqljava
阅读 37发布于 今天 03:50
本作品系原创,采用《署名-非商业性使用-禁止演绎 4.0 国际》许可协议

面试系列
2021面试宝典系列
avatar

bin392328206

是非审之于己,毁誉听之于人,得失安之于数,成败归之于零,

18 声望
2 粉丝

0 条评论
得票时间

avatar

bin392328206

是非审之于己,毁誉听之于人,得失安之于数,成败归之于零,

18 声望
2 粉丝

宣传栏

前言

Tips

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

上面的是脑图地址

叨絮

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

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

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

  • 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吧

日常求赞

本文地址:H5W3 » 【Java】2021-Java后端工程师面试指南-(MySQL)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址