本文章只是对本人在MySQL中八股的不足进行查漏补缺，搭建属于我的知识体系，使用的是适用于本人的语言逻辑，不建议作为八股学习🤫。

之前的回答

讲一下redo log 和bin log undo log

redo log 用于崩溃恢复，保证了事务的持久性
bin log 用于主从复制和数据备份，保证了事务的原子性
undo log 用于事务的回滚和MVCC
redo log 是物理日志，记录的是数据页的修改
- 使用redo log更新数据的流程
1. 数据更新到Buffer pool 同时redo log 来记录修改用于宕机后恢复数据
2. 提交事务(此刻更新数据算是成功)
3. 之后慢慢将redo log里的数据刷盘到磁盘
- 如果数据还没刷盘，宕机后可以使用redo log 进行重做，恢复数据，保证了持久性
bin log 是逻辑日志记录的是SQL语句，主要用于主从复制和数据备份，但它不能像 redo log 一样用于崩溃恢复。
我们提交数据时使用两阶段提交，用于保证redo log 和 bin log 一致
- 先写 redo log 的prepare状态
- 再写 bin log
- 如果完成，则写redo log 的commit状态，如果没完成，则回滚

为什么redo log 不能代替bin log 完成主从复制

redo log 是物理日志，记录的是数据页的修改
跨机器的数据页可能不同，导致无法多节点复用

redo log 是循环写的，它具有固定大小，新的记录会覆盖旧的记录，无法做完整链路

MySQL主从同步原理

Mysql主从同步是基于binlog的异步复制机制
主库记录binlog，从库通过IO线程拉取并写入relay log(中继日志)，再执行SQL实现数据同步

主从同步核心流程
1. 主库写数据，同时记录SQL语句到binlog
2. 从库通过IO线程拉binlog，将日志写入relay log
3. 从库执行relay log的SQl语句，同步数据。
为什么是异步复制
主库提交之后从库不会立刻同步
存在数据延迟
我们可以使用半同步复制模式
主库等从库ACK之后再返回客户端

为什么现在提倡单表查询数据，业务层做耦合

单表查询可以避免联表插查询JOIN的性能问题
联表查询性能比较低,需要看这些表是否有索引
举个例子
- 如果两张表使用JOIN，但是都没加索引
- 假如表A 100行数据表B 1000行数据
- 在内存中会写入1100行数据，然后会进行100*1000次的判断

请你说一下 MySQL 中 InnoDB 和 MyISAM 的区别

InnoDB有拥有事务，可以保证ACID，而MyISAM没有，数据库崩溃会出现错误
InnoDB使用行锁，MyISAM使用表锁
InnoDB可以使用聚簇索引，而MyISAM不行

什么是MySQL的索引

mysql的索引是一种数据结构举个例子的话数据库就相当于一本书而索引就是书内容的目录我们查询一条数据的时候不能直接查询整个数据库而是通过索引去定位我们想要的数据
mysql的索引采用的是b+树的数据结构，不同于二叉树，如果数据依次递增就会退化为链表大大降低性能，而b树虽然在每个节点存储索引和数据，但是面对数据量过大的时候树高还是会很大而b+树的非叶子节点只存储索引和指针，只在叶子节点存储数据，这样我们在遍历非叶子节点的时候就很快，而每个叶子节点都是双向链表，可以方便的进行范围索引查询
常见的索引有聚簇索引和非聚簇索引，我的理解是聚簇索引会根据一个唯一或者主键索引来遍历，叶子节点上存储的是指针和整行数据，非聚簇索引就是使用其他不太重要的属性进行便利，叶子节点存的是主键和指针。当我们select * 时，条件如果用到了非聚簇索引，就会引发回表查询，再根据聚簇索引来查询整行元素性能很差所以我们一般使用联合索引进行查询，尽量不select * 需要我为你讲解联合索引的使用方法吗

InnoDB中行锁和表锁可以同时存在吗

行锁和表锁是可以同时存在的。
在 InnoDB 中，当事务对某一行加锁时，会先在表上加意向锁，再对具体行加行锁。
因此表级锁和行级锁是分层存在的。但如果涉及显式的表锁操作，例如 LOCK TABLE，就需要根据锁的兼容性判断是否冲突

什么是最左前缀原则？

最左前缀原则适用于联合索引我给您讲解一下联合索引的优势
假如我的联合索引时username age address 那么我查询这个索引的时候就可以获得id username age address这四个数据并且不需要回表这就是覆盖索引
至于最左前缀原则我们查询使用联合索引时需要依次需要这些条件
比如where a = 1 ， b = 2 , c = 3 我们的联合索引是a b c 那么就可以正确查询
如果是a = 1 b = 2 那么可以通过前两个进行查询
如果a = 1 c = 3 就只会使用a这一个条件
同样的如果没有a 有b c 那么不会使用任何索引
至于条件的顺序不用非要一致可以先b后a 之后数据库会自动排序
这就是最左前缀原则如果不遵循最左前缀原则就可能会发生回表查询我们通常优化sql会注意sql语句是否准寻他
还需要我说出索引失效的情况吗

MySQL中的事务是什么

事物就是一个程序运行的最小单位
ACID是MySQL事物的四大特性分别是原子性持久性隔离性一致性
在InnoDB中我们使用redo log 来保证持久性假如我们的mysql在内存中读取到了数据，要将其存放到磁盘上
突然mysql崩溃了没有将其存到磁盘上这样就违背了持久性也就是说我们的程序运行成功了但是没有存在数据库中

mysql在内存里引用redo log 我们buffer pool里的数据会先存放到同在内存中的redo log
假如mysql崩溃了 redo log会检测磁盘和自身数据是否一致，如果不一致就会将数据写入磁盘而保证一致性和原子性

我们使用undo log 简单的来说我们的事务如果执行了insert语句还需要执行update语句但是突然报错了
但是insert语句已经执行了这时我们事物的原子性就被破坏了而因为数据库中多了insert语句而程序并没跑通
所以破坏了数据一致性这时我们就需要使用到undo log了 undo log会存放事务中执行的DDL语句的相反语句
比如我们insert了他就存放一个相反的delete 这样在我们程序报错的时候他就会执行相反的语句rollback我们的数据库，保证数据一致性，这样我们的事务里操作数据库的行为也失效了，保证了原子性。至于隔离性我们是通过MVCC进行保证的需要我继续给你讲解MVCC吗

MySQL 为什么使用 B+Tree 作为索引，而不用 Hash 或 BTree？

Hash结构是快但是我们的内存地址不是连续的做不到范围索引
而B+树的每个叶子节点都是双向链表，可以做到范围索引

至于b树他虽然性能比平衡二叉树以及红黑树好，因为他的一个节点不只可以分出两个分支比他俩的树长小很多但是如果数据量很大每个节点上都有数据我们在查询下面的数据时还是性能很差
而B+树的非叶子节点存的都是索引和指针，遍历速度很快再加上因为数据都在叶子节点所以我们平均查询时间会因为树高的相同而比较平衡

什么是MVCC

MVCC是多版本事务控制
在MYSQL中存在很多隔离级别他们是读未提交读已提交可重复读串行化
他会解决我们在数据库中每个事务的隔离性问题
我们多事务并发运行的时候假如我们第一个线程的事务正在修改数据而第二个线程读取到了第一个线程修改的数据结果想读跟读到的不一致这样就形成了脏读
我们的读未提交就会引起脏读因为会读到未提交的事务
而读已提交解决了这个问题他让事务只会读到已经提交的事务的数据

但是假如说我们第一次查询读到了数据而另外一个线程修改了这个数据并且提交了那么我们第二次读取的数据和第一个就不一致这就造成了不可重复读

而RR 可重复读就解决了这个问题我们在第一个查询的时候生成了一个read view 而之后每次查询都复制第一次的read view 这样就保证了每次查询数据一致

但是可重复读还是会产生幻读的问题

而串行化可以解决这个问题他给数据库加了行锁除非解锁要不然其他线程的事务是读取不到数据的摒弃了并行性能就大大降低了

至于MVCC怎么实现是由undo log read view 隐藏字段组成的
我们的表中默认两个隐藏字段一个是该行数据版本号和上一个版本的内存地址
undo log 中存储了数据的所有版本形成了一个版本链表每一个版本都有上一个版本的内存地址
假如我们在RC的情况下每次查询都会生成一个新的readview 他会根据条件来判断读取哪个版本的数据而RR环境下只有一种read view 所以只会读取同一个版本的数据

一条 SQL 在 MySQL 中是如何执行的？

首先是客户端与mysql通过TCP三次握手建立连接
mysql数据库检测用户的密码是否正确从而确认用户是否有权限
然后就是解析器解析用户的SQL语句把他们分解成token 逐token进行解析
解析完了mysql会对sql语句进行优化让他可以符合执行的要求
再通过执行器去执行SQL语句到存储引擎
这些都属于MySQL的Server层，这层里还有一个binlog，MySQL还包括引擎层，也就是InnoDB

1. MySQL执行流程

Client
 ↓
连接器
 ↓
查询缓存(8.0已删除)
 ↓
解析器
 ↓
优化器
 ↓
执行器
 ↓
存储引擎
 ↓
磁盘

客户端使用 TCP/IP 连接方式通过TCP三次握手与数据库左连接
然后数据库校验用户信息，判断是否给用户数据库权限
查询缓存在mysql 8.0 版本被废弃
解析器将用户的SQL语句转化为可处理的内部结构
- 将SQL拆解为token
- 验证token序列是否符合mysql语法规则
- 输出语法树Parse Tree
优化器会对SQL语句进行优化，使其更加符合规范
执行器将会执行SQL语句,如果用户有权限就回去调用引擎的接口

这些都属于MySQL的Server层，这层里还有一个binlog，MySQL还包括引擎层，也就是InnoDB
更新语句执行的流程如下：
- Server层走完之后来到了引擎层，先存入redo log(prepare) -> 再存入bin log ->再给redo log设置commit状态， 双阶段提交保证两个日志相同。

2. MySQL锁体系

MySQL 的锁可以从多个维度分类：

从粒度上可以分为表锁和行锁；
行锁分为记录锁、间隙锁和临键锁；
表锁分为意向锁、自增锁、和元数据锁
从锁的属性上可以分为共享锁（S锁）和排他锁（X锁）；

共享锁(S锁)
SELECT ... LOCK IN SHARE MODE
多个事务可以同时读
排他锁(X锁)
1. SELECT ... FOR UPDATE
2. SELECT ... LOCK IN SHARE MODE
3. UPDATE ...
4. DELETE ...
5. INSERT ...
  该锁只能由一个事务持有

行锁

记录锁(Record Lock)
锁一行数据
间隙锁(Gap Lock)
假如主键id有1，2，4 我们范围查询2到4之间的数据，数据库就会锁2，4
这时另一个事务插入了id = 3的数据，那么我们就会查询2，3，4 产生幻读
间隙锁就是锁住间隙之间所有数据(若新添加会被锁)，防止幻读
临键锁Next-Key Lock
记录锁+间隙锁
假如间隙锁锁了间隙3 那么Next-Key Lock还锁住了2和4 也就是说这个范围内的都被锁住了。

表锁

意向锁(Intention Lock)
是表级锁，不是用来锁数据的，而是用来表示 “某个事物准备给这张表的某些行加什么锁”，类似于打招呼的标记
举个例子
1. 事务A想给user表某一行加排他锁(I锁)
2. 在加这行锁之前，InnoDB会先给整张user表加一个意向排他锁(IX锁)
3. 这个IX不会锁这样表表不让别人查，而是告诉系统：我这张表某些行已经准备加锁了，如果你想加表锁，先问我同不同意
- 为什么要有意向锁
  如果user表有1000万行数据
  事务A锁了一行，事务B想要对整张表加表锁
  如果没有意向锁，DB会检查user表的每一行，看看有没有事务去行锁
- 意向共享锁(IS)/意向排他锁(IX)
  SELECT * FROM user Where id = 1 LOCK IN SHARE MODE
  - 表会先加IS锁，再在命中的行加S锁
    SELECT * FROM user WHERE id = 1 FOR UPDATE
  - 表会先加IX锁，再在命中的行加X锁
自增锁(AUTO-INC Lock)
INSERT INTO table VALUES(null,...)
可以保证自增ID唯一
元数据锁(MDL)
MDL是DB在访问表结构时自动加的一种锁，用来确保表结构的一致性
表结构：列结构，索引，表定义等
举个例子
- 当我们在执行DML语句的时候，突然有DDL语句把表的结构改了，比如删除一个字段，这是不允许的,所以就需要加元数据锁来保证表结构的一致

3. 页(Page)

MySQL 中的页（Page）是 InnoDB 存储引擎管理数据的最小物理单位，默认大小为 16KB。
每一个页就是B+树上的一个节点，页之间通过指针连接

为什么需要页？

数据在磁盘上并不是一条一条存储的，而是磁盘 -> 页 -> 行
因为磁盘的IO很慢，一次IO成本很高，所以需要批量读取
行数据是单向链表结构存入页中的
- 每一行都有一个指针，指向下一行
- 行结构：record1 → record2 → record3 → ...
页目录
页目录下存储的是该节点内的索引和指向下一节点的指针

如何快速查到行数据链表里的数据
- 如果我们遍历的话时间复杂度是On
- 所以我们二分查找页目录，找到指向部分记录
  然后再遍历链表找到行数据，时间复杂度是O(logn)
页分裂
当一页满，再插入新数据就会发生页分裂
- 页分裂的过程：
  1. 创建新页
  2. 将满的页一般数据迁移过去
  3. 更新b+树结构
    
    页分裂为什么影响性能

页分裂分为三个过程：读旧页写新页更新索引页 导致IO变多
数据不再连续，分裂之后：页1 -> 页100 -> 页3
本来磁盘是顺序读数据，页不连续之后变为随机读
可能会触发连锁页分裂，可能会导致树高增加

还有什么场景会导致随机读？

页分裂，导致页不连续
主键使用UUID，导致行数据随机插入，页分裂，可能会随机读

4. 说一下当前读和快照读

当前读(Current Read)读取的是DB的最新数据，而且会加锁
常见的当前读：
1. SELECT ... FOR UPDATE
2. SELECT ... LOCK IN SHARE MODE
3. UPDATE ...
4. DELETE ...
5. INSERT ...
- 直接读取最新数据，读取期间对数据进行加锁(行锁/间隙锁/Next-Key Lock)，防止别人修改或者插入
快照读(Snapshot Read)读取的是数据的历史版本，通过MVCC实现，不加锁
常见的快照读
- SELECT * FROM user Where id = 1
- 事务开始时，先创建一个Read View(读视图)，之后每次查询，只看读视图里的数据

6. 不同隔离级别都用了什么锁

读未提交
写操作加记录锁(X锁)
读操作不加锁
读已提交
写操作加记录锁(X锁)
读操作不加锁，但是读的是MVCC里的read view，每次读都会重新生成一份read view
可重复读
写操作加临键锁
读操作不加锁，读MVCC里的read view，每次都会读取第一次生成的read view
串行化
写操作加临键锁
读操作都加S锁

5. 哪些隔离级别解决了幻读

幻读：事务前后两次读到的数据条数不一致

可重复读解决了大部分的幻读
- 通过MVCC的Read Views 保证同一事物读取的快照一致，解决了快照读的幻读
- 对于当前读(select for update/update/delete)，InnoDB 使用 Next-Key Lock(行锁+间隙锁)防止其他事务插入，从而解决了当前读的幻读
  对于RR ，当前读可能出现幻读
  举个例子
  事务A 执行select * from user where id > 2，此时表内有id = 3，因为读取的是最新数据，所以只读id = 3一条
  此时事务B update了一条id = 4 的记录然后commit了
  事务A 又想select for update，又因为是当前读，读到了最新数据，也就是读到了id = 3 , id =
- 避免这种情况可以每次都是用select for update避免当前读和快照都混用
而串行化解决了所有的幻读
- 因为串行化读数据时默认加S锁lock in share mode

默认是RR 为什么阿里会选择RC级别？

幻读造成的影响不会太严重，RC的性能更高

6. MVCC

MVCC(多版本并发控制) 就是让事务不加锁，非阻塞的读取到一个历史版本
MVCC = readview + 隐藏字段 + undolog版本链

隐藏字段: 行数据里除了我们设置的字段，还有两个隐藏字段：事务ID，上一个版本的指针

undolog版本链：我们要读取以前的版本，所以每次DML之后会创建一个跟其相反的语句存入undolog日志
如果要退回之前的版本，使用这个语句即可
需要通过数据的事务ID和上一个版本的指针进行退回，假如事务ID为3，上一个版本为2，再上一个版本为1，所以需要根据undolog日志查询版本为1的语句，执行可以退回到版本1

readview：当事务读取数据时，创建一个readview来决定读取哪个版本的数据
在RC级别下，同一事务每次读取都会生成不同的readview
在RR级别下。同一事务每次读取第一次生成的readview

yin_bo_'s BLOG

MySQL查漏补缺