口语化讲解MySQL

最新推荐文章于 2024-07-22 17:39:53 发布

Java菜恐龙

最新推荐文章于 2024-07-22 17:39:53 发布

阅读量1k

点赞数 20

分类专栏：后端个人知识体系构建文章标签： mysql 数据库后端 java

本文链接：https://blog.csdn.net/skyqiyunhun/article/details/136899784

版权

后端个人知识体系构建专栏收录该内容

13 篇文章 0 订阅

订阅专栏

前言

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

正文

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

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

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

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

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

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

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

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

哈希索引

对于InnoDB的哈希索引，确切的应该这么说：

InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引
InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引(加速索引寻路，做索引的索引)，这一层上说，InnoDB又是支持哈希索引的

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

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

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

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

OR为什么需要左右两边都有索引才走索引

MySQL优化执行计划目标都是出于成本考虑。OK，那么我们就对比下用索引和不用索引的成本好了。

不用索引–一次全表扫描完事
使用索引–就按照上图的表来描述好了。id走索引，user_id不走索引，所以成本就是全表扫描，最后还需要merge求并集。总结：全表扫描+索引扫描+merge

结论很明显直接进行全表扫描操作更少，因此MySQL在这种情况下不走索引直接全表扫描也就可以理解了。

可见都有索引的情况是直接使用的union，重写SQL语句优化。

B+Tree原理(数据结构/数据操作)

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

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

innodb的页默认大小是16K,页可以存储两种数据。一种是存储数据，叫做数据页，另一种是存放键值和指针，指针指向对应数据页，被称作索引组织表。数据库查询的瓶颈在于磁盘IO，

B+Tree相对于BTree来说只有叶子节点存储数据，单页存储的节点更多，磁盘单次IO读取数据页有限，能读到更多的节点，也就变相减少了查询次数。

B+Tree与其他结构的对比

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执行顺序

我们先执行from,join来确定表之间的连接关系，得到初步的数据
where对数据进行普通的初步的筛选
group by分组
各组分别执行having中的普通筛选或者聚合函数筛选。
然后把再根据我们要的数据进行select
将查询结果去重distinct
最后合并各组的查询结果，按照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次数。

预读优化及改进版LRU

预读，磁盘一般是按页(4K)读取，而不是按需读取，一般至少一页，是因为数据访问通常遵循集中读写原则，或者说局部性原理，确实能减少IO。buffer pool也是遵照预读的理念，按页缓存数据，使用了改进版LRU来解决预读失效和缓冲池污染的问题。(Redis不需要复杂的预读策略，是因为基于内存操作，基于磁盘的快照和日志不需要复杂的预读策略)

改进版LRU将一个链表拆成新生代和老年代两个链表，新生代尾部连接老年代头部合成一个链表，占比默认63:37。被预读的页首先进入老年代头部，只有被真正读取，才会加入到新生代头部，这样就能确保预读但是没有被真正读取的数据页比新生代里的热数据更早淘汰。但是这样不能解决缓冲池污染的问题，即一个大数据量的全表扫描，按照前面的理论是被读取的，会立即加入到新生代头部，但是这些数据可能只使用一次，却占用了新生代的大量空间，引发了真正热数据的淘汰。为了解决这个问题，增加了一个老年代停留时间窗口策略，也就是说即使被真正读取的数据也会先加入到老年代头部，只有满足“被访问”并且“在老生代停留时间”大于T(某个自定义阈值)，才会被放入新生代头部。

写缓冲

一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。

double write buffer

redo无法修复这类“页数据损坏”的异常，修复的前提是“页数据正确”并且redo日志正常。Double Write Buffer，但它与传统的buffer又不同，它分为内存和磁盘的两层架构，对原来的页进行还原。当有页数据要刷盘时：

页数据先mem copy到DWB的内存里，速度很快；
DWB的内存里，会先刷到DWB的磁盘上，属于顺序追加写，速度也很快；
DWB的内存里，再刷到数据磁盘存储上，刷磁盘，随机写，本来就需要进行，不属于额外操作；

步骤2和步骤3要写2次磁盘，这就是“Double Write”的由来。DWB由128个页构成，容量只有2M。

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先保存在本地，然后从库进行回放

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

主从同步一般过程是

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

主从延迟的计算方法是，从主线程写入主库binlog的时刻到最后从库写入数据成功为止所需的时间。在备库执行 show slave status 命令，seconds_behind_master表示当前备库延迟了多少秒。
造成主从延迟的情况有很多，比如从库太多、网络传输慢、从库硬件配置不行、慢SQL过多。解决方式，可以开启并行复制、堆硬件、减少主库读写压力。

Binlog

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

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

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

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

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

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

执行SQL，然后redo log随事务开始，记录事务的xid，同时记录状态为prepare
事务执行成功并提交，写入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版本数据即可。当前读指插入、更新、删除操作时，需要为当前数据行加锁，避免其他并发事务修改当前行。

MVCC➕Next-key-Lock 防止幻读

InnoDB存储引擎在 RR 级别下通过 MVCC和 Next-key Lock 来解决幻读问题：
1、执行普通 select，此时会以 MVCC 快照读的方式读取数据
在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”
2、执行 select…for update/lock in share mode、insert、update、delete 等当前读
在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用间隙锁来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读

InnoDB存储引擎的锁

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

Record Lock为行锁，锁住单个记录行。Gap Lock为间隙锁，锁定一个范围，不包含当前记录行。

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

索引成本

什么时候用索引？取决于查询成本，以非聚簇索引为例，成本组成来源于两部分。一是读取非聚簇索引的成本，二是回表的成本。再简化一些，当非聚簇索引在全表数据中占比超过一定比例时，数据库判定成本过大(全表查询顺序IO快于索引的随机IO)，就会放弃使用索引改用全表查询。这也是部分!=或者is not null语句仍然可以使用索引的原因。不同版本的MySQL成本计算策略不一致，因此会出现同一条SQL在8.0索引生效，在5.7不生效的情况。