java面试 - mysql

Mysql的InnoDB和MyISAM区别

1.InoDB支持事务，MyISAM不支持
2.InoDB支持行锁，MyISAM支持表锁
3.InoDB支持外键，MyISAM不支持

数据库事务

数据库操作要么都执行，要么都不执行，事务是维护数据一致性的单位，事务结束时数据都能保持一致
事务日志通过重做(redo)日志文件和InnoDB存储的日志缓存来实现的，当一个事务开始时会往InnoDB的日志
缓存中插入事务日志，当事务提交时必须将InnoDB存储引擎的日志写入磁盘。
事务撤销时写入undo日志，事务失败时回滚就是利用undo信息将数据回滚到之前的样子。

原子性(A):事务操作数据库要么都成功，要么都失败
一致性(C):事务开始前后数据库结构的完整性没有破坏
隔离性(I):事务质检相互不影响
持久性(D):事务一旦提交后数据就是永久的

进一步详情查看 https://blog.csdn.net/zxl646801924/article/details/84378057

事务实现原理：

• 事务的原子性是通过 undo log 来实现的
• 事务的持久性性是通过 redo log 来实现的
• 事务的隔离性是通过 (读写锁+MVCC)来实现的

而事务的终极大 boss 一致性是通过原子性，持久性，隔离性来实现的。

原子性，持久性，隔离性折腾半天的目的也是为了保障数据的一致性！

总之，ACID只是个概念，事务最终目的是要保障数据的可靠性，一致性。

悲观锁(行锁)：数据更新的时候加for update
乐观锁(表锁)：使用版本号控制实现
共享锁(读锁): 在同一时段内，多个用户可以读取同一个资源，读取过程中数据不会发生变化
排它锁(写锁)：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其它的读锁或者写锁，
只能由这一个用户来写，其它用户既不能写也不能读

脏读：一个事务读取到了另外一个未提交的事务
不可重复度：事务先后两次读取同一个数据，两次读取的结果不一样。
和脏读的区别在于：脏读读到其它事务未提交的数据，不可重复读是读到其它事务已提交的数据。
幻读：事务按照某个条件先后两次查询数据库，两次查询结果条数不一样
和不可重复的区别是，前者是数据发生了变化，幻读是数据行数发生了变化

Mysql数据的隔离级别

read uncommitted（读未提交）
read committed（读已提交）
repeatable read（可重复读）：InnoDB的默认隔离级别
serializable（串行）

索引类型

主键索引，唯一索引，联合索引，全文索引(主要用来查找文本中的关键字)

索引的原理

使用B+树将数据变成有序的节点，查询的时候通过hash算法可以先定位到具体的某个节点然后再找到对应的数据，方便快速查找

B树和B+树

B树：每个叶子节点都存储key和data，不含关键字信息
B+树：所有的叶子节点包含关键字的信息，以及指向含有这些关键字的指针，

聚集索引和非聚集索引

聚集索引：按照每张表的主键构造一颗B+树，叶节点存放整张表的行数据，告诉InnoDB引擎哪里可以找到相应的数据
非聚集索引：叶⼦节点存储的是主键 id，需要使⽤主键 id 再去聚簇索引中获取表的相关信息，所以执⾏效率没有聚簇索引⾼，⽽这个查询的过程就叫做回表查询。

区别：

1. 执⾏效率：聚簇索引查询速度更快，因为聚簇索引存储的是数据，⽽⾮聚簇索引存储的是主键 ID，需要进⾏回表查询。

2.数量上：聚簇索引⼀个表只能有⼀个，⽽⾮聚簇索引可以有多个。

MVCC

多版本控制器，并发访问数据库时对事物加载内读取的数据，加载到内存中做处理，用来避免写操作阻塞读操作的问题，说白了MVCC就是为了实现读(select)-写冲突不加锁。

一个支持MVCC的数据库在更新某些数据时并非使用新的数据覆盖旧的数据，而是标记旧数据是过时的，同时在其它地方新增一个数据版本。因此，同一份数据有多个存储版本，但只有一个是最新的。假设同一份数据既有读事务访问，又有写事务访问操作，实际上，写事务会新建一个新的数据版本，而读事务访问的是旧的数据版本直到写事务提交后，读事务才会访问新的数据版本。

意义：MVCC在Mysql的InnoDBzhong的实现主要是为了提高数据库的并发性能，用更好的方式无处理读-写冲突，做到即使有读写冲突时也能做到不加锁，非阻塞的并发读。

实现方式：

通过三个隐藏字段，undo日志 ，Read View 来实现的。

三个隐士字段：

DB_ROW_ID 是数据库默认为该行记录生成的唯一隐式主键，默认自增

DB_TRX_ID 是当前操作该记录的事务 ID

DB_ROLL_PTR 是一个回滚指针，用于配合 undo日志，指向上一个旧版本

数据首次插入时只有ROW_ID有值其它为空，下次数据更新时mysql先将数据加锁然后拷贝数据到undo文件中并记录TRX_ID，ROLL_PTR指向上一次数据的ROW_ID，再有数据更新时mysql继续将数据加锁然后拷贝数据到上一次undo文件的前面，ROOL_PTR指向上一次数据的ROW_ID。

MyBatis缓存机制：

Mybatis 提供了查询缓存来缓存数据，以提高查询效率。缓存级别分为一级缓存 和 二级缓存。
1、一级缓存
一级缓存为 SqlSession 级别的缓存，也就是会话级缓存，是基于HashMap的本地缓存，当同一个SqlSession执行两次相同的SQL语句时，第一次执行完后会将数据库中查询到的结果写到缓存，第二次查询时直接从缓存中读取，不经过数据库了。一级缓存默认是开启的。

2、二级缓存
二级缓存为mapper级别的缓存，多个 SqlSession 去操作同一个 Mapper 的 sql 语句，多个 SqlSession 去操作数据库得到数据会存在二级缓存区域，多个 SqlSession 可以共用二级缓存，二级缓存是跨 SqlSession 的。其作用域是 mapper 的同一个 namespace，不同的 sqlSession 两次执行相同 namespace下的 sql 语句且向 sql 中传递参数也相同即最终执行相同的 sql 语句，第一次执行完毕会将数据库中查询的数据写到缓存（内存），第二次会从缓存中获取数据将不再从数据库查询，从而提高查询效率。Mybatis 默认没有开启二级缓存需要在 setting 全局参数中配置开启二级缓存。

详情见：详解SpringBoot整合Mybatis开启本地二级缓存和使用redis开启二级缓存_springboot mybatis缓存_境里婆娑的博客-CSDN博客

Mysql的二阶段提交

MySQL想要准备事务的时候会先写redolog、binlog分成两个阶段。

两阶段提交的第一阶段 （prepare阶段）：写redo-log 并将其标记为prepare状态，紧接着写binlog

两阶段提交的第二阶段（commit阶段）：写 redo-log并将其标记为commit状态。

sql优化

 从三个方面出发：

1.字段数据结构
设置合适的字段类型，长度
2.字段索引
常用查询字段，排序字段上增加索引，避免全表扫描
3.sql语法
尽量避免使用select * 查询，消耗性能大
查询唯一只有一条数据时，使用limit 1，找到数据后就会停止查找
尽量少用order by
where查询条件进行null值判断，避免使用!=或者<>或者or操作
where查询条件避免使用表达式，函数
索引字段尽量避免使用函数
优先使用inner join, 使用left join时左边放数据量小的表
使用联合索引时注意索引的顺序，一般遵循最左匹配原则
尽量不要超过5个以上的表连接

索引失效的几种场景：

1.like以%开头
2.or查询
3.联合索引，不遵循最左匹配原则
4.字段做隐形转换，如字符类型不在引号则不会使用索引
5.字段有数学运算或者函数