前言

本文对MySQL相关知识做了一个相对完整的总结，涉及到MySQL结构、索引、B+Tree、buffer pool、日志、事务、锁等。特别注意，口语化八股文系列，仅作突击复习核心知识点用，推荐有一定八股基础的人食用，更细的点需要大家自行查询相关详细图文资料

正文

MySQL由哪些部分组成, 分别用来做什么

总体分为服务层和存储引擎层，服务层又可以细分为连接器、分析器、优化器和执行器。

连接器用于校验身份和权限信息。分析器用于词法和语法分析。优化器会选择索引并生成执行计划。执行器负责调用存储引擎，执行语句。

索引，数据结构、优缺点、类型

索引是一种用于提升检索速度的数据结构，内部存有指向数据真实地址的引用。有B+Tree和Hash两种结构，默认使用B+Tree

有两个优点，一是创建索引会加快检索速度。二是建唯一性索引可以保证改数据列拥有唯一性。

同时也有两个缺点，一是当索引列进行增删改时对应索引也会发生变动，因此会影响SQL的执行效率。二是索引使用物理空间存储，会占用一定的存储空间。

索引在功能上分为三种，分别是普通索引、唯一索引、主键索引。唯一索引与普通索引的区别是内部数据具有唯一性，主键索引则是特殊的唯一索引，特殊在一张表只能有一个。主键索引又被称为聚簇索引，B+Tree叶子节点上有整行数据。除此之外的被称为非聚簇索引，叶子节点存的是主键的值，需要通过主键回表二次查询，因此效率低于聚簇索引。

索引优化(覆盖索引,联合索引,最左前缀匹配)

覆盖索引指的是SQL查询的数据列在索引列中全都，那么就不需要回表即可返回数据，少了一次查询从而提升了效率。

联合索引也就是多列索引，遵从最左前缀匹配，意思就是当生成ABC三列联合索引时，实际上同时生成了A,AB,ABC三个索引。最左前缀匹配的原理是，MySQL内部B+Tree是按照从左到右呈非递减趋势，多列索引的情况下会按照A,B,C依次排序，所以在A相等的时候B才是有序的，在AB相等时C才是有序的

索引下推优化需要手动开启，应用在等值条件后跟随Like的情况下。如果索引列含有当前数据那么直接对索引数据进行模糊匹配，而不是先回表，从而减少了查询次数。

B+Tree原理(数据结构/数据操作/为什么用，而不是别的树)

B+Tree从数据结构上看是一棵平衡查找树，它的所有叶子节点都在同一层，能通过Ologn的时间复杂度进行查找、顺序读取、插入和删除。

数据操作上从查找来看，流程是先在根节点进行二分查找key对应的节点，再从节点处递归向下直到叶子节点，然后在叶子节点上进行二分查找，找到key对应的data。插入删除操作如果影响了树的平衡性，树会进行分裂、合并、旋转等操作来再平衡。

innodb的页默认大小是16K,页可以存储两种数据。一种是存储数据，叫做数据页，另一种是存放键值和指针，指针指向对应数据页，被称作索引组织表。数据库查询的瓶颈在于磁盘IO，B+Tree相对于BTree来说只有叶子节点存储数据，单页存储的节点更多，磁盘单次IO读取数据量有限，能读到更多的节点，也就变相减少了查询次数。

• B+Tree和BTree的区别有三点，一是BTree的叶子节点和非叶子节点都存有键key和数据data，而B+Tree只有叶子节点存data。二是B+Tree的叶子节点间存在引用，方便范围查询。三是B+Tree的检索速度稳定，因为每一次查询都是从上到下在叶子节点获得结果，而BTree则可能在非叶子节点就结束查询。
• B+Tree和Hash的区别有四点，一是Hash索引适合等值查询，不适合范围查询，而B+Tree的叶子节点间有引用。二是Hash索引不能排序，B+Tree则是节点中数据存在非递减趋势，是有序的。三是Hash索引不支持联合索引的最左匹配原则。四是Hash索引在有大量重复值时存在哈希碰撞问题。
• B+Tree相对于一般二叉树来说优势在于极端情况下一般二叉树会退化成链表，也就成了全表扫描
• 红黑树是一种特殊的平衡树，它的问题在于层级可能会很多，影响查询效率。

SQL执行顺序

1. 我们先执行from,join来确定表之间的连接关系，得到初步的数据
2. where对数据进行普通的初步的筛选
3. group by 分组
4. 各组分别执行having中的普通筛选或者聚合函数筛选。
5. 然后把再根据我们要的数据进行select
6. 将查询结果去重distinct
7. 最后合并各组的查询结果，按照order by的条件进行排序

Buffer pool

innodb提供了buffer pool缓存池，会利用内存空间存储部分磁盘上的数据页。MySQL读取数据的时候会先从buffer pool中读取，如果没有则读取磁盘并放入buffer pool。当写入数据时，会先写入到buffer pool中，然后定期刷入到磁盘，这个过程被称为刷脏。

如果MySQL突然宕机，没有进行刷脏，如何保证事务持久性呢？通过redo log来解决这个问题，当数据修改时，因为redo log作为预写型日志，所有修改都会先记录在redo log，最后修改buffer pool中的数据，保证操作不会轻易丢失。当事务提交时，会调用异步接口(fsync)将redo log写入磁盘，如果此刻MySQL宕机，那么重启时会读取redo log数据进行恢复。

这里会有个疑问，为什么不直接将Buffer Pool中修改的数据写入磁盘(即刷脏)，而是选择将redo log写入磁盘呢？原因有二，一是redo log与刷脏需要寻找数据去修改的随机IO不同，属于顺序IO，找到日志文件追加即可。二是刷脏是以数据页为单位(Innodb默认页大小16K)，单页上一个小修改也需要整页写入，而redo log只需要追加真正需要的数据，减少IO次数。

Undo Log(生命周期\存储内容\存储位置\实现原理\作用)

undo log记录数据修改之前的样子，用于实现Innodb的事务回滚。它是一个反向日志，比如一个insert语句会被记录成delete语句，一个update语句会被记成相反操作的update语句。

undo log随事务开始而生成，同时也会使用redo log保证其可靠性。当事务提交后，并不会马上删除，而是放入待清理链表中，由purge线程判断是否有其他事务在使用当前事务的版本信息，从而决定是否清理undo log(MVCC中的undo log版本链)。

undo log的作用有两个，分别是保证事务原子性和实现多版本并发控制(MVCC)。因为undo log中存有事务修改前的数据，所以可以作为快照读数据供其他并发事务读取。

Redo Log

redo log又被称为重做日志，记录数据修改之后的样子，用于确保事务的持久性和一致性，当MySQL重启时会读取redo log数据进行恢复。

redo log由两部分构成，一是内存中的redo log buffer，二是磁盘上的redo log文件。

落盘时机有三个，一是主线程默认每秒刷入磁盘。二是事务提交时，会开启异步线程刷盘。三是redo log buffer空间不足一半时，也会刷盘。

Relay Log

用于数据库主从同步，将主库发送来的binlog先保存在本地，然后从库进行回放

Binlog

binlog是记录数据库表结构及数据变更的二进制日志，不会记录select和show操作。它是以事件方式记录日志，还会记录语句执行时间，记录了整个操作。

开启binlog日志主要有两个左右，分别是主从复制和数据恢复。主从复制的原理是将binlog数据同步到从库，从库根据binlog数据数据恢复从而保证数据一致性。数据恢复则是通过binlog记录的数据来恢复数据库。

binlog的文件记录模式有三种，分别是row、statement、mixed。row会记录每一行数据被修改的情况，比如update一条语句修改了一千行数据，那么就会有一千行记录。row记录的优点是记录完整，方便完整恢复数据，缺点是太详细了，日志量过大。statement则是只记录SQL，优点是原模原样，日志量少，降低了日志传输和执行的成本，缺点是部分语句不可复制，例如now()这种快照数据的函数。mixed则是融合了两者的优点，当statement无法复制SQL语句时悬着row模式记录。

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

1. 执行器根据MySQL的执行计划查询数据，先从buffer pool中查询被修改的数据，如果没有则从磁盘中读取放入buffer pool中。
2. 在事务开始之前也就是修改数据之前，生成undo log日志
3. 在buffer pool中更新数据，同时添加修改后的数据到redo log buffer
4. 最后提交事务，同时会触发三个事件。一是redo log buffer会追加到redo log磁盘文件中。二是整个事务操作写入bin log中。三是bin log记录事务信息的日志位置写入到redo log中，并添加commit标记

执行SQL发生数据库宕机恢复的过程

1. 执行SQL，然后redo log随事务开始，记录事务的xid，同时记录状态为prepare
2. 事务执行成功并提交，写入SQL信息及事务xid到bin log日志，同时写入redo log日志当前状态为commit

如果当前任一环节出现问题，会先扫描redo log，如果prepare和commit状态标记都有，那就直接提交事务。如果只有prepare，说明commit阶段崩溃了，那么需要拿着redo log的事务xid在bin log中查找相同的xid，如果有提交事务，没有则根据undo log日志回滚。

说⼀下 ACID 是什么

Atomicity(原子性)：语句要么全部执行，要么全部不执行，主要由undo log实现。

Consistency(⼀致性)：在事务开始之前和结束之后，数据库的完整性不受到破坏

Isolation(隔离性)：隔离性保证在多个事务并发执行的情况下避免出现数据不一致。写操作之间的隔离通过锁机制保障。读操作的隔离通过MVCC机制保障。

Durability(持久性)：保证事务提交后不会因为宕机等原因导致数据丢失，通过redo log保障。

事务的隔离级别

读未提交(Read UnCommitted/RU)：又被称为脏读，存在问题，比如为一个事务可以读取到另一个事务未提交的数据

读已提交(Read Committed/RC)：又被称为不可重复读。解决了脏读的问题，但是还存在问题，一个事务多次重复读取相同数据行，但是当前行在此事务期间被多个事务修改，导致每次读取数据不一致。

可重复读(Repeatable Read/RR)：又被称为幻读。通过快照读解决了不可重复读问题，但是由于快照读原因，不能获取真实情况，如果真实数据行已被修改，那就成了幻读问题。

串行化(Serializable)：所有读写均为串行执行，只支持单线程执行请求，性能极差。

InnoDB的MVCC实现逻辑(MVCC的实现\快照读和当前读\ReadView)

MVCC是一种用于处理并发读写冲突的无锁解决方案，由ReadView版本链和undo log日志提供底层实现，同时会在每一行记录后增加两个隐藏列，分别保持事务ID和回滚指针。

当多个事务修改同一条记录时，会在undo log生成多个被修改记录的版本数据及事务信息。在读已提交和可重复读两个事务隔离级别下MVCC通过ReadView控制快照读。ReadView是一个记录活跃事务ID的表，通过与当前事务ID的比较来决定读取undo log上哪个事务ID对应的版本数据。在读已提交级别下，每一次查询ReadView都要重新生成，而可重复读只有第一次查询需要生成。

MVCC存在快照读和当前读。快照读指简单的查询语句不用加锁，直接对比ReadView读取undo log版本数据即可。当前读指插入、更新、删除操作时，需要为当前数据行加锁，避免其他并发事务修改当前行。

InnoDB存储引擎的锁

MySQL有表锁和行锁两种通用锁。innodb则分为三种锁，分别是Record Lock、Gap Lock、Next-key Lock。

Record Lock为行锁，锁住单个记录行。Gap Lock为间隙锁，锁定一个范围，不包含当前记录行。Next-key Lock为行锁加间隙锁的组合，用于解决幻读问题。比如一个事务先删除了一个不存在表中的角色id相关数据，但是索引排序他的id大于表中存在的所有数据，因此间隙锁锁住了表中最大id到正无穷的数据。后面另一个事务想要往这个表里插入角色id同样大于之前id的数据时，也就是间隙锁范围内，就会发生死锁

MySQL主从(主从过程\主从延迟)

主从同步一般过程是

1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave之后，负责从master上拉取binlog内容，放进自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；

主从延迟的计算方法是，从主线程写入主库binlog的时刻到最后从库写入数据成功为止所需的时间。在备库执行 show slave status 命令 ，seconds_behind_master表示当前备库延迟了多少秒。

造成主从延迟的情况有很多，比如从库太多、网络传输慢、从库硬件配置不行、慢SQL过多。解决方式，可以开启并行复制、堆硬件、减少主库读写压力。

索引成本

什么时候用索引？取决于查询成本，以非聚簇索引为例，成本组成来源于两部分。一是读取非聚簇索引的成本，二是回表的成本。再简化一些，当非聚簇索引在全表数据中占比超过一定比例时，数据库判定成本过大，就会放弃使用索引改用全表查询。这也是部分!=或者is not null语句仍然可以使用索引的原因。

写在最后

MySQL相关的实践我也是总结了相当多了，比如从零开始的SQL修炼手册-实战篇 - 掘金 (juejin.cn)，数据库设计-MySQL设计小册 - 掘金 (juejin.cn)，口语化讲解数据库优化 - 掘金 (juejin.cn)。从SQL优化、数据库设计和优化方面近乎全方位进行了阐述。

现在是2023.3.4的深夜，明日需加班，工作不易，诸君共勉。明晚会再次更新数据库优化部分到亮点那篇文章，如何挖掘项目中的亮点(多方向带案例)