Mysql知识点汇总

B树、B+树、B*树都是多路搜索树【左子树的值< 根节点 < 右子树的值】

B树：非叶子结点也会存储数据。一个父节点下可以有k-1个子节点(k：B树的阶数)。

优缺点：(1)、查询不稳定，因为非叶子结点也存储数据，单次查询可能到根节点，也可能到叶子结点；(2)、不支持范围检索。

B+树：数据只存储在叶子结点中，非叶子结点只存储索引信息，叶子结点之间通过指针链接，形成链表。一个父节点下可以有k个节点，所以B+树比B树更矮胖。

优缺点：(1)、比B树更矮胖；(2)、查询复杂度稳定【所有查询都会到叶子结点】；(3)、叶子结点之间存在指针相连，便于范围查询。

B*树：在B+树的基础上新增了一个指向兄弟节点的指针。

红黑树：每个节点增加一个存储位表示节点的颜色,可以是red或black. 通过对任何一条从根到叶子的路径上各个节点着色的方式的限制,红黑树确保没有一条路径会比其它路径长出两倍.它是一种弱平衡二叉树。

优缺点：是二叉树。旋转操作耗时长。红黑是用非严格的平衡来换取增删节点时候旋转次数的降低，任何不平衡都会在三次旋转之内解决。查询效率高于AVL树。java1.8的HashMap使用此实现

AVL树【平衡二叉树】：左右子树高度差不超过1，是一种严格平衡二叉树。删除或者插入需要通过旋转来保持平衡。

优缺点：是二叉树。旋转操作耗时长，适用于插入和删除操作少，查询次数多的场景。
"Windows进程地址空间管理" 使用的此数据结构

B+树结构示意图

1.2、Innodb使用B+树做底层结构的原因：

Innodb底层采用的是B+树。原因是B+树更加矮胖，查询的次数更低，效率更高，存储的节点数更多；而且计算机的预读特性可以减少磁盘I/O。

1.3、Innodb的内存结构及预读特性

1.3.1、Innodb的内存结构：

表空间：创建的每张表对应一个表空间，表空间的磁盘文件上存储着该表里每一行的数据。

数据页：数据在磁盘中是以数据页为单位进行存储的【对应B+树的一个叶子结点】，一个数据页大小为 16KB，表空间的磁盘文件中会包含多个数据页。

数据区：便于管理数据页，将64个连续的数据页划分为一个数据区。

QA：为什么一页设计成16KB大小？

之所以设置为一页，是因为对于大部分业务，一页就足够了。(一条数据必须是放在一个节点中，不能拆开到多个节点存储。而一般一条数据大概1KByte，那么一页能容纳的大概16条，即一个节点容纳不止一条数据，而且是多达16条！！所以大小采用最小存储单元足够用了~）

QA: 3层B+树可以存放多少数据？

如果主键是bigint，差不多2000w。如果主键是int类型，差不多4000w。



B+树数据存储在叶子结点中，非叶子结点存储的是索引。

假设主键是bigint 占用8Byte，指针设置为6Byte，一个主键+指针 =14Byte，16KB/14B = 1170个组合。假设一条数据大小1K，一个叶子结点可以存储16条，2层可以存放16*1170 = 18720条。3层可以存放16*1170*1170 = 21902400 条。

假设主键是int 占用4Byte，指针设置为6Byte，一个主键+指针 =10Byte。16KB/10B = 1600个组合。假设一条数据大小1K，一个叶子结点可以存储16条，2层可以存放16*1600 = 25600条。3层可以存放16*1600*1600 = 40960000 条。

1.3.2、预读特性

局部性原理：是指程序在执行时呈现出局部性规律，即在一段时间内，整个程序的执行仅限于程序中的某一部分。相应地，执行所访问的存储空间也局限于某个内存区域（也就是说，这个程序在这一小段时间内只会用这一部分内存的这一小撮数据）

磁盘预读：提前准备好要用数据，提升I/O效率。

线性预读：如果顺序的访问了一个区里的多个数据页，数量超过了一个阈值，就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去
随机预读：如果 Buffer Pool 里缓存了一个区里的 13 个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时会触发预读机制，把这个区里的其他的数据页都加载到缓存里去。

1.4、Innodb的索引

稠密索引：为每一个数据都建立一个索引值

稀疏索引：将数据分组，每组建立一个索引值。

聚簇索引: 索引项的排序方式和表中数据记录排序方式一致【是一种数据存储方式：它会对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上】。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

非聚集索引：索引顺序与物理存储顺序不同。

Innodb：

InnoDB表数据本身就是主索引：使用B+Tree作为索引结构，索引页大小16KB，和表数据页共同存放在表空间中。InnoDB表数据文件本身就是按B+树组织的一个索引结构，这棵树的叶子节点保存了完整的数据，非叶子结点存储了索引值【默认用主键做索引，没有就用一个具有唯一属性的非空值做主键，如DB隐藏到自增主键】。主索引是聚集索引。
辅助索引：也使用B+树做索引结构，叶子结点中存储的是主索引的值。索引通过辅助索引查询时，需要先查到主索引的值，再去数据表中通过主索引查询数据。

MyIsam：

索引文件与数据文件是分离的。主索引和辅助索引都是非聚集索引。也是通过B+树做索引结构，叶子节点存的是数据记录的地址。

QA：字节面试题:Redis为啥使用跳表，但是Mysql使用B+树？

跳表：跳表是由单链表衍生出来的，但是它的效率却比普通的单链表高很多。跳表的最底层是单链表，第二层往上存储的是索引。例如查找数据9，第一层查询在5-10之间，第二层查询在8-10之间。第三层直接从节点8开始查询，比单链表查询节点更少。

跳表结构示意图

相同点：B+树和跳表查询效率都是杠杠的，而且都支持范围查询，不然MySQL就不用 B+树，Redis就不用跳表了。

不同点（新增数据方面）：B+ 树在新增数据的时候可能面临页分裂的问题，而且它还需要维护各种索引页；跳表在添加数据的时候，并没有什么页分裂的说法，就算是索引分配也是非常的简单，它只需要利用一个随机函数随机出新增数据需要出现的层级数就可以了，最低层级就是数据不建立索引，只待在最底层的单链表处。

MySQL选用 B+ 树构建索引，主要是因为 B+ 树是多叉结构，而且根据它结构组织数据页/索引页，存放2kw数据也只是需要 3 层左右就可以了，目前实践中，B+树索引几乎没有超过 4 层，换句话说，如果是 B+ 树索引的话，查找一次数据，一般最多也就 3 次磁盘 IO ；而在跳表中就不一样了，2kw的数据在跳表中存储，如果想要达到二分查找的效率的话，最起码也要2^24层级才能实现，而每个层级的数据都是分散在不同的数据页中的，所以在查找数据的过程中，跳表可能需要进行 24次磁盘IO。磁盘IO是非常消耗性能的，能够少磁盘IO就少磁盘IO的，所以单凭这个点跳表也不会被MySQL选做索引。那Redis为什么要用跳表实现zset？主要是因为Redis是一个基于内存的数据库，它的数据几乎都在内存中，就算是使用跳表，就算 2kw数据会达到 2^24次方的层级，但是这些都不是问题，因为它们都是在内存中，不存在刚刚讨论的磁盘IO影响性能的问题。而且Redis中使用跳表还不需要担心 B+树的页分裂之类的问题。

2、Innodb的锁机制：

2.1、锁的种类：表级锁、意向锁、行级锁、间隙锁、NextKey锁。

表级锁:

对整个表加锁。查询没走到索引｜小表查询时，执行计划默认扫全表更快都会走表级锁。
意向共享锁(IS)：事务打算对数据加行共享锁(S)，加S前需要先持有IS。
意向排他锁(IX)：事务打算对数据加行排他锁(X)，加X前需要先持有IX。

行锁:

行共享锁(S)：一个事务去读一行，阻止其他事务获取这行数据对应的排他锁；
行排他锁(X)：允许本事务更新数据，阻止其他事物获取这行数据对应的共享锁和排他锁。

间隙锁【RR-可重复读隔离级别下才会启用】：

当使用范围查询而不是EqualTo时会锁住这个区间范围【如select * for t where id > 9 for update】。
会阻塞符合条件范围内键值的并发插入，可以解决RR隔离级别下的幻读现象。
但是这种加锁方式会造成严重的锁等待，而且高并发插入场景间隙锁也可能造成死锁。

NextKey锁【间隙锁+行锁：只有RR下才会启用】：

每个 next-key lock 是左开右闭区间。
是先加间隙锁再加行锁，这不是一个原子性操作，因此会出现只加了间隙锁但加行锁被阻塞的情况。详情点击

任何隔离级别下，都会使用表锁、行锁、意向锁。在RR隔离级别下，还会使用间隙锁和 Next-Key 锁。参考：MySQL 不同隔离级别，都使用了什么锁？-mysql事务隔离级别

2.2、Innodb加锁的方式

锁只有在事务提交(commit)或者回滚(rollback)时才会释放，并且是所有的锁资源一起释放。

意向锁:是Innodb自动加的，不需要用户干预。其实就是打一个标记，说明这张表目前正有别的事务再加锁。
对于insert、update、delete 操作Innodb会自动加排他锁(X), select操作无需加锁。
显式加排他锁：select … for update
显式加共享锁：select …. lock in share model

2.3、Innodb锁的实现原理

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。所以Innodb的行级锁是有一定概率冲突的。如果两个事务使用的是同一个索引，那下一个session就需要等前一个session释放，应用设计的时候要注意这一点。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。

QA: RR隔离级别下，默认怎么加锁的？

行锁是锁住索引。而间隙锁用来锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为RR的，间隙锁和行锁一起组成了Next-Key Lock。当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁，再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙插入记录。这样就有效的防止了幻读的发生。

默认情况下，InnoDB工作在RR的隔离级别下，并且以Next-Key Lock的方式对索引行进行加锁。当查询的索引具有唯一性（主键、唯一索引）时，Innodb存储引擎会对Next-Key Lock进行优化，将其降为行锁，仅仅锁住索引本身，而不是范围（除非锁定不存在的值）。若是普通索引，则会使用Next-Key Lock将记录和间隙一起锁定。

3、事务

3.1、ACID特性

Atomicity(原子性)：事务的所有操作要么全部提交成功，要么全部失败回滚。

Consistency (一致性)：数据库在事务执行前后都保持一致性状态。即所有事务对一个数据的读取结果都是相同的。

Isolation(隔离性)：一个事务所做的修改在最终提交以前，对其它事务是不可见的。

Durability(持久性)：一旦事务提交，其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

QA：Mysql怎么实现事务的？

原子性是通过undo log 日志实现的(提交失败回滚)；持久性是通过redo log实现的(redo log保存了已经提交成功的事务数据)；隔离性是通过 (读写锁+MVCC)实现的。一致性是通过原子性、隔离性、持久性实现的。

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

面试官：MySQL事务是怎么实现的_mysql 数据库的事务是通过什么方式实现的?-CSDN博客

3.2、数据库日志文件

undo 日志文件：记录数据被修改前的样子（用于回滚）。

Insert undo log ：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。

Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。

Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。

redo 日志文件：记录数据被修改后的样子（用于持久化）是innodb层面的，其他的没有

binlog 日志文件：记录整个操作过程（用于主从复制和数据恢复）是mysql维度的，所有DB都存在binlog。其主要格式有：

STATEMENT 格式（语句模式，出现在 MySQL 5.1 之前）：在这种格式下，binlog 记录的是执行的 SQL 语句的文本。日志文件通常较小，复制效率较高。在某些情况下，由于数据库环境的差异（如表结构、字符集等），在从服务器上重放这些 SQL 语句可能会导致不一致。
ROW 格式（行模式，诞生于 MySQL 5.1）：在这种格式下，binlog 记录的是每一行数据更改的具体内容。能够精确地记录数据的变化，提供了更强的一致性保证。日志文件可能会比 STATEMENT 格式大。此外，ROW 格式的日志在进行大量数据更新时可能会导致更高的 I/O 开销。
MIXED 格式（混合模式）：在这种格式下，binlog 可以根据具体的 SQL 语句和操作自动选择使用 STATEMENT 或 ROW 格式。

QA: redoLog和binLog的区别？

3.3、事务隔离级别

read uncommited - 读未提交: 可以读取到未提交的事务信息。

会出现脏读：【读取到脏数据】
不可重复读：【对于同一条数据，同一事务前后读取不一致(被别的事务修改)】
幻读：【事务处理过程中, 别的事务插入或删除了一条数据】

read commited - 读已提交: 只能读取已经提交的事务信息。

可以有效的解决脏读问题，但是还是会出现不可重复读和幻读。

repeatable read - 可重复读: 在做事务操作前加锁，所有人不能修改数据。

可以解决不可重复读问题【Innodb通过快照读解决此问题，mvcc实现】
但是还是会出现幻读【Innodb在RR隔离级别下通过间隙锁和MVCC 来解决幻读问题】

serializable - 序列化：所有的事务都是串行提交的。

大多数数据库默认级别就是read commited，如oracle。Innodb默认事务隔离级别是「可重复读」。在可重复读隔离级别下，通过多版本并发控制(MVCC)+间隙锁(Next-Key Locking)防止幻读。MVCC在Innodb的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突的时候，也能不加锁，非阻塞并发读。

*QA：为什么一般大厂都会把mysql隔离级别调整为RC？***

提高并发：RC 在加锁的过程中，是不需要添加Gap Lock和 Next-Key Lock 的，只对要修改的记录添加行级锁就行了。RC的并发度要比 RR 高很多。
减少死锁：因为RR这种事务隔离级别会增加Gap Lock和 Next-Key Lock，这就使得锁的粒度变大，那么就会使得死锁的概率增大。

参考：https://www.cnblogs.com/yizhiamumu/p/16726845.html

3.4、快照读和当前读

当前读：像select for update，select lock in share model，update，insert，delete 这些操作都是一种当前读，因为他需要读取数据的最新版本，读取时还要保证其他的事务不能修改当前记录，会对读取的记录进行加锁。

快照读：单纯的select就是快照读，即不加锁的非阻塞式的读取。快照读的前提是隔离级别不能是串行级别，串行级别下的快照读会退化成当前读。快照读基于MVCC实现

参考：https://www.cnblogs.com/notayeser/p/14086211.html

3.5、MVCC实现原理

MVCC：全称是Multi-Version Concurrency Control，即多版本并发控制。可以认为是行锁的一个变种。在多数读写并发的情况下，避免了加锁操作，降低了开销。它的实现原理主要是依赖记录中的4个隐式字段，undo日志，Read View来实现的。为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。

参考：详细内容请点击

3.5.1、4个隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID、DELETED_BIT等字段

DB_ROW_ID: 6byte, 隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引。
DB_TRX_ID: 6byte, 最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务。
DB_ROLL_PTR: 7byte, 回滚指针，指向这条记录的上一个版本。
DELETED_BIT: 1byte, 记录被更新或删除并不代表真的删除，而是删除flag变了。

隐式字段示意图

3.5.2、undo日志

InnoDB把这些为了回滚而记录的这些东西称之为undo log。这里需要注意的一点是，由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo log。

undo日志示意图

3.5.3、Read View(读视图)

事务进行快照读操作的时候生产的读视图(Read View)，维护了当前活跃的线程ID列表，一个最小的线程Id，和下一个准备分配的线程ID。

ReadView：当前活跃事务ID是1和3，最小的事务ID1，下一个分配的ID是5

3.5.4、MVCC解决幻读举例：

ID为2的事务提交后查询数据。以下图为例：ReadView中活跃事务ID是1和3，undo日志中最新提交的事务ID是4，线程2查询，按照可见性算法计算，2>1 (最小的事务ID) && 2<5(即将分配的下一个事务ID) && 不在活跃事务ID内，所以2能查到当前最新的记录(事务ID4提交的)。如果不可见就通过回滚指针判断上一个事务提交记录是否可见。

QA1、RR是如何在RC级的基础上解决不可重复读的？

通过快照读解决的。事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力。

QA2、RC,RR级别下的InnoDB快照读有什么不同？

RC级别下快照读读取的是最新的ReadView【不等同于当前读，当前读会对数据加锁】，RR级别下快照读读的不一定是最新的ReadView。正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同。在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

4、数据更新至提交的流程

从准备更新一条数据到事务的提交的流程描述：

首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中
在数据被缓存到缓存池的同时，会写入 undo log 日志文件
更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redoLog buffer 中
完成以后就可以提交事务，在提交的同时会做以下三件事
将redoLog buffer中的数据刷入到 redoLog 文件中
将本次操作记录写入到 binLog文件中
将 binLog 文件名字和更新内容在 binLog 中的位置记录到redoLog中，同时在 redoLog 最后添加 commit 标记至此表示整个更新事务已经完成。

[注：Buffer Pool是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的。]

5、主从复制与读写分离

5.1、主从复制

5.1.1、主从复制的优点

高可用性：通过将主数据库的数据复制到一个或多个从数据库，可以在主数据库故障时快速切换到从数据库。
提高整体性能和吞吐量：通过将读请求分散到多个从服务器上进行处理，从而减轻了主服务器的负载压力，提高数据库系统的整体性能和吞吐量。主服务器主要负责写操作，而从服务器主要负责读操作，从而分担了主服务器的压力。
数据备份和恢复：通过主从同步，可以将主服务器上的数据异步复制到从服务器上，从而实现数据备份和灾难恢复的需求。在应对意外数据丢失、灾难恢复或误操作时，可以使用从服务器作为数据的备份源来进行数据恢复。

5.1.2、主从复制执行流程

master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

5.1.3、主从复制的模式

异步复制：MySQL 主从复制中最常见和默认的模式。在异步复制模式中，主服务器将数据修改操作记录到二进制日志（Binary Log）中，并将日志传输给从服务器。从服务器接收到二进制日志后，会异步地应用这些日志进行数据复制。

优点：主服务器不会受到从服务器的影响而等待确认，可以提高主服务器的性能。
缺点：由于是异步复制，可能存在数据传输的延迟，且从服务器上的复制过程是不可靠的。如果主服务器故障，尚未应用到从服务器的数据可能会丢失。

半同步复制：半同步复制是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用了这些日志后才能继续下一个事务。如果在一定时间内（Timeout）内没有收到 ACK ，则切换为异步复制模式。

优点：可以提供更高的数据一致性和可靠性，确保至少一个从服务器与主服务器保持同步。如果主服务器故障，已经确认接收并应用到从服务器的数据不会丢失。
缺点：由于半同步复制需要等待从服务器的确认，因此相对于异步复制，会增加一定的延迟，可能会影响主服务器的性能。

如果对数据一致性和可靠性要求较高，可以考虑使用半同步复制；如果对延迟和主服务器性能要求较高，可以继续使用异步复制，根据实际需求调整复制模式。参考：https://juejin.cn/post/6967224081410162696

QA：主从复制从库丢失事务怎么解决？

开始半同步复制，确保有一个从库返回成功主库在做后续操作。
如果发生了主从数据不一致
1. 重启优先于切换：很多情况下，主库 crash 后，会被 mysqld_safe 或者 systemd 服务自动拉起来，如果数据库重启速度很快，其实不一定要切换，RTO 也能得到保证。旧主有多的 gtid 没关系，拉起来后，gtid 会自动同步到从库，数据就不会不一致。
2. 切换后补数据
3. 切换后旧主 flashback 数据

参考：技术分享 | 无损半同步复制下，主从高可用切换后数据一致吗？

5.2、读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于:

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

6、CAP（不可能同时满足）

6.1、CAP含义

分布式系统最多只能同时满足一致性（Consistency）、可用性（Availability）和分区容错性（Partition tolerance）这三项中的两项

Consistency (一致性)：更新操作成功并返回客户端后，所有节点在同一时间的数据完全一致，这就是分布式的一致性。一致性的问题在并发系统中不可避免。

Availability (可用性)：即服务一直可用，而且是正常响应时间。

Partition Tolerance (分区容错性)：即分布式系统在遇到某节点或网络分区故障的时候，仍然能够对外提供满足一致性或可用性的服务。