MySQL基础原理

是棍子啊

已于 2024-05-23 15:41:00 修改

阅读量867

点赞数 23

分类专栏： Java课程文章标签： mysql java 数据库

于 2024-05-22 22:44:51 首次发布

本文链接：https://blog.csdn.net/m0_64643342/article/details/139076724

版权

Java课程专栏收录该内容

11 篇文章 0 订阅

订阅专栏

MySQL有3种存储引擎:InnoDB、MyISAM、MEMORY。而InnoDB提供了事务处理、回滚、崩溃修复能力和多版本并发控制等事务安全性很高的功能,支持范围查询和顺序查询，成为目前主要使用的一种存储引擎, 也是mysql的默认存储引擎,底层采用B+树数据结构.

Page(页)

磁盘IO的消耗是很大的, 为了避免一条一条读取磁盘数据，InnoDB采取页的方式，作为磁盘和内存之间交互的基本单位。一个页的大小一般是 16KB。

InnoDB为了不同的目的而设计了多种不同类型的页。比如：存放表空间头部信息的页、存放undo日志信息的页等等。我们把存放表中数据记录的页，称为索引页or数据页。

页的结构信息:

向页里添加数据:

记录(数据)的头信息:

deleted_flag：逻辑删除标记（0:未删除 1:已删除）

min_rec_flag：B+树中每层非叶子节点中的最小的目录项记录，都会添加该标记。

n_owned：一个页面被分若干组后，“带头大哥”用于保存组中所有的记录条数。

heap_no：表示当前记录在页面堆中的相对位置。

record_type：表示当前的记录类型。

① 0：普通记录

② 1： B+树非叶子节点的目录项记录

③ 2：表示Infimum记录

④ 3：表示Supremum记录

next_record：表示下一条记录的相对位置，也就是链表。这个属性非常重要。它表示从

当前记录的真实数据到下一条记录的真实数据的距离。

页中数据的存储关系(单向链表):

Page Directory:

记录在页中是按照主键值从小到大的顺序串联成为一个单向链表，因此查询也只能以头节点开始逐一向后查询，但是如果数据量很大，那么性能就无法保证了。针对这个问题，InnoDB采取了图书目录的解决方案，即：Page Directory。

分组规则如下所示：

① 对于Infimum记录所在的分组只能有1条记录。

② 对于Supremum记录所在的分组只能在1～8条记录之间。

③ 剩下的其他记录所在的分组只能在4～8条记录之间。

分组步骤如下：

① 初始情况下，一个数据页中只有Infimum记录和Supremum记录这两条，所以分为两个组。

② 之后每当插入一条记录时，都会从页目录中找到对应记录的主键值比待插入记录的主键值大，并且差值最小的槽，然后把该槽对应的n_owned加1。

③ 当一个组中的记录数等于8时，当再插入一条记录的时候，会将组中的记录拆分成两个组（一个组中4条记录，另一个组中5条记录）。并在拆分过程中，会在Page Directory中新增一个槽，并记录这个新增分组中最大的那条记录的偏移量。

B+树和B树区别:

相同点:

一个节点可以存储多个元素。

叶子节点是有序的。

每个节点中的元素，也都按照从小到大的顺序排列，即：左小右大。

所有叶子节点都位于同一层，或者说根节点到每个叶子节点的高度都相同。

不同点:

B+树的叶子节点是有单向指针的，其中：MySQL中采用的是双向指针。

B+树的非叶子节点的元素是与叶子节点有冗余的。

Index(索引)

MySQL每个索引都会有一套B+树数据, 而主键索引所在的B+树中, 叶子节点里存储完整的数据（数据页），非叶子节点存储主键索引（索引页）

如果表中没有指定主键,将使用第一个唯一（UNIQUE）索引作为聚簇索引。如果唯一索引也没有，MySQL默认会生成一个row_id作为主键.

主键索引(聚簇索引)

二级索引(普通索引)

联合索引

非主键索引的其他索引树中, 叶子节点不包含行记录的全部数据, 它包含3部分: 索引列的值(c2)+主键值(c1)+页号(pageNo).这也就会出现我们常说的回表查询问题: 如果我们要查询所有字段值, 那么就需要第一次搜索非主键索引的B+Tree 拿到主键值后, 再去搜索主键索引的 B+Tree, 查询两次.

Buffer Pool(缓冲池)

当我们从Mysql查询一条数据, 为了提供高性能,减少IO, MySQL会把该数据所在的页全都查询出来并缓存起来.这个缓存区就是Buffer Pool.

Buffer Pool默认128M，也可以修改大小启动项配置(innodb_buffer_pool_size 字节)来设置缓冲池大小。Buffer Pool又被划分为若干个16KB大小的缓冲页。为了更好的管理Buffer Pool中的这些缓冲页，InnoDB为每个缓冲页都创建了控制块，它与缓冲页是一一对应的。

MySQL的预读

线性预读：如果顺序访问某个区（extent，一个区默认64个页）的页面超过了innodb_read_ahead_threshold（默认56）的值，就会触发一次异步读取下一个区中全部的页到Buffer Pool中的请求。

随机预读：如果开启了随机预读功能（默认：innodb_random_read_ahead=OFF），如果某个区（extent）有13个连续的页面都已经被加载到了Buffer Pool中，无论这些页面是不是顺序读取的，都会触发一次异步读取本区全部的页到Buffer Pool中的请求。

Free链表

当缓存数据的时候，为了能够知道哪些缓冲页是空闲且可分配的，MySQL把所有空闲的缓冲页对应的控制块作为一个节点放到一个链表中，这个链表便称之为Free链表。

Flush链表

当我们修改数据时, 也是先修改Buffer Pool中的数据，此时它就与磁盘上的页不一致了，这样的缓冲页也被称之为脏页（dirty page- 控制块里会存储该缓冲页是否被修改的信息, 用来判断查找脏页）。为了性能问题，我们每次修改缓冲页后，并不着急立刻把修改刷新到磁盘上，而是将被修改过的缓冲页对应的控制块作为节点加入到这个链表中，该链表也被称为flush链表。

Flush刷盘的两种方式:

1.从flush链表中刷新一部分页到磁盘

后台线程会根据当时系统的繁忙程度确定刷新速率，定时从flush链表中刷新一部分页面到磁盘。——即：BUF_FLUSH_LIST

有时后台线程刷新脏页的进度比较慢，导致用户准备加载一个磁盘页到Buffer Pool中时没有可用的缓冲页。此时，就会尝试查看LRU链表尾部，是否存在可以直接释放掉的未修改缓冲页。如果没有，则不得不将LRU链表尾部的一个脏页同步刷新到磁盘（与磁盘交互是很慢的，这会降低处理用户请求的速度）。——即：BUF_FLUSH_SINGLE_PAGE

2.从LRU链表的冷数据中刷新一部分页面到磁盘

后台线程会定时从LRU链表的尾部开始扫描一些页面，扫描的页面数量可以通过系统变量innodb_lru_scan_depth来指定，如果在LRU链表中发现脏页，则把它们刷新到磁盘。——即：BUF_FLUSH_LRU

LRU链表

用来标识区分冷热数据页, 保证热数据的读取性能, 在缓冲池空间不足时, 也只会释放冷数据页.

redo日志

当Buffer Pool中的脏页还没来得及刷新到磁盘中，而此时系统或者MySQL突然发生了故障(断电)，那么新写的数据就会丢失.

redo日志中记录了我们每次事物修改的内容,当我们提交事务时, 除了将数据写入Buffer Pool中, 还会记录对应的redo日志,并将日志刷新到磁盘上,此时MySQL才认为事物提交成功. 这样即使出现上面的突发情况, 也可以通过redo日志来恢复数据.

redo日志类型:

MLOG_REC_INSERT（type=9）

MLOG_COMP_REC_INSERT（type=38）

MLOG_COMP_PAGE_CREATE（type=58）

MLOG_COMP_REC_DELETE（type=42）

MLOG_COMP_LIST_START_DELETE（type=44）

MLOG_COMP_LIST_END_DELETE（type=43）

MLOG_ZIP_PAGE_COMPRESS（type=51）

MLOG_COMP_REC_INSERT的日志结构:

redo日志组

每个写操作产生的redo日志，被InnoDB划分成了若干个不可分割的组。比如插入一条数据MySQL内部会有很多操作，并产生多条日志（所有的的索引树添加叶子节点、可能添加新的Page、可能分组、更新偏移、更新max_row_id等等)，这些操作在MySQL中是原子性的，是不可分割的。而针对一个组中的redo日志，要么把全部的日志都恢复，要么一条也不恢复。

对底层页面进行一次原子访问的过程被称为一个Mini-Transaction（MTR）。

事务、SQL语句、MTR、redo日志之间的关系：

① 1个事务可以包含N条SQL语句

② 1条SQL语句可以包含N个MTR

③ 1条MTR可以包含N条redo日志

redo log blocker

为了更好地管理redo日志，InnoDB把通过MTR生成的redo日志都放在了大小为512字节的页中，把用来存储redo日志的页称为block。

log buffer

与Buffer Pool类似，写入redo日志时也不能直接写到磁盘中，在服务器启动时就向操作系统申请了一片连续内存空间- redo log buffer(默认innodb_log_buffer_size=16MB)，并划分成若干个连续的redo log block。

log buffer写入到磁盘中：

① log buffer空间不足50%的时候

② 事务提交的时候

③ 后台有线程，大约以每秒1次的频率将log buffer中的redo日志刷新到磁盘。

④ 正常关闭服务器时

⑤ 做checkpoint时（全局变量：checkpoint_lsn，表示当前系统中可以被覆盖的redo日志总量是多少，当一个MTR的日志刷新到磁盘上，那么它的redo log就可以被覆盖，checkpoint_lsn根据日志大小自增，这个过程叫checkpoint）

在MySQL的数据目录中，默认有名称为：ib_logfile0和ib_logfile1的两个（默认两个文件，最大100个）文件（默认innodb_log_file_size = 48MB），log buffer中的日志在默认情况下就是刷新到这两个磁盘文件中。

如果某一个脏页从Buffer Pool中刷到了磁盘上，那么该页对应的控制块就会从flush链表中移除掉，它的redo日志占用的磁盘空间就可以被覆盖掉了。

undo log

当事务遇到异常回滚rollback时，数据库为了恢复之前的旧数据而记录的日志，我们就称之为undo log. (select不会产生undo log)

undo log 类型

TRX_UNDO_INSERT_REC 插入

TRX_UNDO_DEL_MARK_REC 删除

delete操作分为两步：

delete mark阶段：

将记录的deleted_flag标识位设置为1，但是这条记录并没有加入到垃圾链表中。也就是说，这条记录即不是正常记录，也不是已删除记录。在删除语句所在的事务提交之前，被删除的记录一直都处于这种中间状态（其实主要是为了实现MVCC的功能才这样处理的）。

purge阶段：

当该删除语句所在的事务提交后，会有专门的线程来把该记录从正常记录链表中移除，并加

入到垃圾链表中作为头节点。

TRX_UNDO_UPD_EXIST_REC 更新

事务

事务ID

只有在事务对表中的记录进行改动时才会为这个事务分配一个唯一的事务id，否则默认为0。

事物的四大特性 ACID

Atomicity 原子性

事务中的所有操作，要么全都执行完毕，要么全都回滚。

Consistency 一致性

数据库中的数据全都符合现实世界中的约束，则这些数据就符合一致性。比如性别约束男or女；人民币面值不能为负数；出生地址不能为null；参与转账的账户总余额不变；等等。

Isolation 隔离性

多个事务访问相同数据时，对该数据不同状态的转换对应的数据库操作的执行顺序有一定的规律，彼此不干涉。

Durability 持久性

事务提交后，意味着对数据所做的修改都应该在磁盘中保存。

事务的状态

事务并发执行的问题

脏读：一个事务读取到了另一个事务未提交的数据。

不可重复读：一个事务修改了另一个事务已经读取到的数据。

幻读：一个事务中两次相同的范围查询得到的数据量不一致。

事务隔离级别

REPEATABLE READ是MySQL默认隔离级别

为什么REPEATABLE READ无法避免幻读？

T1和T2两个事务，T1先查询了一次数据，此时事务ID默认为0，然后T2插入了一条数据并提交事务，此时该记录T1事务是看不到的，但T1可以修改这条记录，修改操作导致T1的事务ID被赋值，同时该记录最后的事务ID也变成了T1的事务ID，此时T1就可以查到这条数据，导致幻读

MVCC (Multi-Version Concurrency Control)

多版本并发控制，利用记录的版本链和ReadView，来控制并发事务访问相同记录时的行为。

ReadView 一致性视图

用来判断版本链中的哪个版本是当前事务可见的。有4个重要的数据：

如果trx_id（当前事务ID）== creator_trx_id，则表明当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

如果trx_id < min_trx_id，则表明生成该版本的事务在当前事务生成ReadView之前已经提交了，所以该版本可以被当前事务访问。

如果trx_id >= max_trx_id，则表明生成该版本的事务在当前事务生成ReadView之后才开启，所以该版本不可以被当前事务访问。

如果trx_id in m_ids，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。

如果trx_id not in m_ids，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见，那就顺着版本链找到下一个版本的数据，并继续执行上面的步骤来判断记录的可见性，以此类推，直到版本链中的最后一个版本。

READ COMMITTED——在一个事务中， 每次读取数据前都生成一个ReadView。

REPEATABLE READ——在一个事务中， 只在第一次读取数据时生成一个ReadView。

锁

MySQL中提供了各种锁来避免并发情况下“读-写”过程中可能出现的脏读，不可重复读，幻读

对于读操作，一般通过MVCC可以控制（某些场景：比如银行业务，事务安全性重要于性能/时间，所以读写都可能会加锁）。对于写操作，则需要对操作的数据进行加锁。

行级锁

共享锁 / S锁（Shared Lock）在事务要读取一条记录时，需要先获取该记录的S锁。
SELECT ... LOCK IN SHARE MODE;
独占锁 / 排它锁 / X锁（Exclusive Lock）在事务要修改一条记录时，需要先获取该记录的X锁。
SELECT ... FOR UPDATE;
记录锁（LOCK_REC_NOT_GAP） 仅对一条记录上锁
GAP锁（LOCK_GAP） 锁住了指定记录前面的间隙，防止其间插入新记录，防止幻读。
NEXT-KEY锁（LOCK_ORDINARY） 记录锁+GAP锁的组合
插入意向锁（LOCK_INSERT_INTENTION）事务在等待时也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，目前处于等待状态。
隐式锁 严格意义上不算一种锁，是针对insert语句没有锁的场景，通过trx_id判断来避免并发问题