2024年java面试准备--mysql(4)

此篇是本人在准备java开发岗位时准备的一些关于mysql的优化和一些面试需要特别注意的地方，还有诸多面试知识点在主页，欢迎大家查看，互相交流学习~~ 第一部分链接 第二部分链接 第三部分链接 这是最终部分了

​

集群

1、主从复制过程

MySQl主从复制：

• 原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
• 过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
• 优点：

- 作为备用数据库，并且不影响业务
- 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

binlog记录格式： statement、row、mixed

基于语句statement的复制、基于行row的复制、基于语句和行（mix）的复制。其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

2、数据一致性问题

"主从复制有延时"，这个延时期间读取从库，可能读到不一致的数据。

缓存记录写key法：

在cache里记录哪些记录发生过的写请求，来路由读主库还是读从库

异步复制：

在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

半同步复制：

当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，通过一份在主库的Binlog，另一份在其中一个从库的Relay-log，可以保证了数据的安全性和一致性。

全同步复制：

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

3、集群架构

Keepalived + VIP + MySQL 主从/双主

当写节点 Master db1 出现故障时，由 MMM Monitor 或 Keepalived 触发切换脚本，将 VIP 漂移到可用的 Master db2 上。当出现网络抖动或网络分区时，MMM Monitor 会误判，严重时来回切换写 VIP 导致集群双写，当数据复制延迟时，应用程序会出现数据错乱或数据冲突的故障。有效避免单点失效的架构就是采用共享存储，单点故障切换可以通过分布式哨兵系统监控。

架构选型： MMM 集群 -> MHA集群 -> MHA+Arksentinel。

4、故障转移和恢复

转移方式及恢复方法

1. 虚拟IP或DNS服务 （Keepalived +VIP/DNS  和 MMM 架构）

问题：在虚拟 IP 运维过程中，刷新ARP过程中有时会出现一个 VIP 绑定在多台服务器同时提供连接的问题。这也是为什么要避免使用 Keepalived+VIP 和 MMM 架构的原因之一，因为它处理不了这类问题而导致集群多点写入。

2. 提升备库为主库（MHA、QMHA）

尝试将原 Master 设置 read_only 为 on，避免集群多点写入。借助 binlog server 保留 Master 的 Binlog；当出现数据延迟时，再提升 Slave 为新 Master 之前需要进行数据补齐，否则会丢失数据。

锁

分类：

MySQL中的锁，按照锁的粒度分，分为以下三类:

1. 全局锁:锁定数据库中的所有表。
2. 表级锁:每次操作锁住整张表。
3. 行级锁:每次操作锁住对应的行数据。

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类:

1. 表锁 对于表锁，分为两类: 1.表共享读锁( read lock)：读锁不会阻塞其他客户端的读，但是会阻塞写 2.表独占写锁（write lock )：写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写 语法:

1. 加锁:lock tables表名... read/write。
2. 释放锁: unlock tables /客户端断开连接。

1. 意向锁

1．意向共享锁（lS）:与表锁共享锁(read）兼容，与表锁排它锁（write)互斥。

2．意向排他锁（IX)∶与表锁共享锁( read)及排它锁(write)都互斥。意向锁之间不会互斥。

行级锁：

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在lnnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

1. 行锁(Record Lock)∶锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。 lnnoDB实现了以下两种类型的行锁: 1．共享锁(S）∶允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。 2．排他锁（X)∶允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。 默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

1.  针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

2.  InnoDB的行锁是针对于索引加的锁，不通过g引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

1. 间隙锁(Gap Lock):锁定索引记录间隙（(不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
2. 临键锁(Next-Key Lock)∶行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。 默认情况下，InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。 1．索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。 2． 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。 3．索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

其他问题

MySQL有哪些锁

基于粒度：

• 表级锁：对整张表加锁，粒度大并发小
• 行级锁：对行加锁，粒度小并发大
• 间隙锁：间隙锁，锁住表的一个区间，间隙锁之间不会冲突只在可重复读下才生效，解决了幻读

基于属性：

• 共享锁：又称读锁，一个事务为表加了读锁，其它事务只能加读锁，不能加写锁
• 排他锁：又称写锁，一个事务加写锁之后，其他事务不能再加任何锁，避免脏读问题

$和

#占位符的特点

1. MyBatis处理 #{ } 占位符，使用的 JDBC 对象是PreparedStatement 对象，执行sql语句的效率更高。
2. 使用PreparedStatement 对象，能够避免 sql 注入，使得sql语句的执行更加安全。
3. #{ } 常常作为列值使用，位于sql语句中等号的右侧；#{ } 位置的值与数据类型是相关的。

$占位符的特点

1. MyBatis处理 ${ } 占位符，使用的 JDBC 对象是 Statement 对象，执行sql语句的效率相对于 #{ } 占位符要更低。
2. ${ } 占位符的值，使用的是字符串连接的方式，有 sql 注入的风险，同时也存在代码安全的问题。
3. ${ } 占位符中的数据是原模原样的，不会区分数据类型。
4. 占位符常用作表名或列名，这里推荐在能保证数据安全的情况下使用{ } 占位符常用作表名或列名，这里推荐在能保证数据安全的情况下使用 占位符常用作表名或列名，这里推荐在能保证数据安全的情况下使用{ }。

数据库三范式具体是什么

第—范式:每个列都不可以再拆分。

第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。

第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。

Mysql内连接、左连接、右连接的区别

内连接取量表交集部分，左连接取左表全部右表匹部分，右连接取右表全部坐表匹部分

where和having的区别？

where是约束声明，having是过滤声明，where早于having执行，并且where不可以使用聚合函数，having可以

char和varchar的区别

char是不可变的，最大长度为255，varchar是可变的字符串，最大长度为2^16

InnoDB 什么情况下会产生死锁

事务1已经获取数据A的写锁，想要去获取数据B的写锁，然后事务2获取了B的写锁，想要去获取A的写锁，相互等待形成死锁。 mysql解决死锁的机制有两个：1.等待， 直到超时 2.发起死锁检测，主动回滚一条事务 死锁检测的原理是构建一个以事务为顶点、 锁为边的有向图， 判断有向图是否存在环， 存在即有死锁。

MySQL 删除自增 id，随后重启 MySQL 服务，再插入数据，自增 id 会从几开始？

innodb 引擎：

MySQL8.0前，下次自增会取表中最大 id + 1。原理是最大id会记录在内存中，重启之后会重新读取表中最大的id

MySQL8.0后，仍从删除数据 id 后算起。原理是它将最大id记录在redolog里了

myisam：

自增的 id 都从删除数据 id 后算起。原理是它将最大id记录到数据文件里了

MySQL插入百万级的数据如何优化？

（1）一次sql插入多条数据，可以减少写redolog日志和binlog日志的io次数（sql是有长度限制的，但可以调整）

（2）保证数据按照索引进行有序插入

（3）可以分表后多线程插入

Mybatis 中一级缓存与二级缓存

1. MyBatis的缓存分为一级缓存和 二级缓存。

一级缓存是SqlSession级别的缓存，默认开启。

二级缓存是NameSpace级别(Mapper)的缓存，多个SqlSession可以共享，使用时需要进行配置开启。

1. 缓存的查找顺序：二级缓存 => 一级缓存 => 数据库

简述Mybatis的动态SQL，列出常用的6个标签及作用

动态SQL是MyBatis的强大特性之一 基于功能强大的OGNL表达式。

动态SQL主要是来解决查询条件不确定的情况，在程序运行期间，根据提交的条件动态的完成查询

常用的标签:

<if> : 进行条件的判断

<where>：在<if>判断后的SQL语句前面添加WHERE关键字，并处理SQL语句开始位置的AND 或者OR的问题

<trim>：可以在SQL语句前后进行添加指定字符 或者去掉指定字符.

<set>: 主要用于修改操作时出现的逗号问题

<choose> <when> <otherwise>：类似于java中的switch语句.在所有的条件中选择其一

<foreach>：迭代操作，批量操作

Select 语句完整的执行顺序

（1）from 子句组装来自不同数据源的数据；

（2）where 子句基于指定的条件对记录行进行筛选；

（3）group by 子句将数据划分为多个分组；

（4）使用聚集函数进行计算；

（5）使用 having 子句筛选分组；

（6）计算所有的表达式；

（7）select 的字段；

（8）使用order by 对结果集进行排序。

如何保证接口的幂等性

1. 根据状态机很多时候业务表是有状态的，比如订单表中有：1-下单、2-已支付、3-完成、4-撤销等状态。如果这些状态的值是有规律的，按照业务节点正好是从小到大，我们就能通过它来保证接口的幂等性。假如id=123的订单状态是已支付，现在要变成完成状态。update order set status=3 where id=123 and status=2;第一次请求时，该订单的状态是已支付，值是2，所以该update语句可以正常更新数据，sql执行结果的影响行数是1，订单状态变成了3。后面有相同的请求过来，再执行相同的sql时，由于订单状态变成了3，再用status=2作为条件，无法查询出需要更新的数据，所以最终sql执行结果的影响行数是0，即不会真正的更新数据。但为了保证接口幂等性，影响行数是0时，接口也可以直接返回成功。 具体步骤： 1 用户通过浏览器发起请求，服务端收集数据。 2 根据id和当前状态作为条件，更新成下一个状态 3 判断操作影响行数，如果影响了1行，说明当前操作成功，可以进行其他数据操作。 4 如果影响了0行，说明是重复请求，直接返回成功。
2. 获取token 除了上述方案之外，还有最后一种使用token的方案。该方案跟之前的所有方案都有点不一样，需要两次请求才能完成一次业务操作。 第一次请求获取token 第二次请求带着这个token，完成业务操作。 具体步骤： 1 用户访问页面时，浏览器自动发起获取token请求。 2 服务端生成token，保存到redis中，然后返回给浏览器。 3 用户通过浏览器发起请求时，携带该token。 4 在redis中查询该token是否存在，如果不存在，说明是第一次请求，做则后续的数据操作。 5 如果存在，说明是重复请求，则直接返回成功。 6 在redis中token会在过期时间之后，被自动删除。**