目录
一、概述
MySQL是一个关系型数据库管理系统（RDBMS），由瑞典的MySQL AB公司开发，目前属于Oracle旗下产品。MySQL以其开源、免费、体积小、速度快、总体拥有成本低等特点，成为全球最受欢迎的数据库管理系统之一，特别是在Web应用方面，MySQL是表现最优异的关系数据库管理系统应用软件之一。
二、MySQL的常见存储引擎
MySQL的存储引擎非常多,比如InnoDBdunhao、MyISAM、Memory、Archive、CSV等,当然最常用的还是InnoDB和MyISAM,InnoDB是MySQL目前的存储默认存储引擎以下是他们两个主要的区别:
| 名称 | MyISAM | InnoDB |
| 事务 | 不支持 | 支持 |
| 锁 | 表锁 | 行锁 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 索引 | 只支持主键索引 | 支持主键索引和二级索引 |
由于在日常开发中大家都默认使用InnoDB存储引擎,所以下面我们用InnoDB存储引擎展开
三、InnoDB存储引擎
1、InnoDB磁盘结构
(1)段
段是一个逻辑存储结构,也就是说不是一块固定的空间,当然段是由下面的区组成,一个段中包含多个区,段存在多种分类:
数据段（Leaf node segment）:用于存储B+树的叶子节点，即实际的数据记录。
索引段（Non-leaf node segment）:用于存储B+树的非叶子节点，即索引信息。
回滚段（Rollback segment）：用于存储回滚日志（Undo log），支持事务的回滚操作和多版本并发控制（MVCC）。
(2)区
区是一段连续的磁盘空间,由下面的页组成,一个区由64个连续的页(16KB)组成,区是存储数据的最小单元,也就是说MySQL写数据,一次从磁盘至少申请一个区的资源(1MB),为什么这样是因为,磁盘IO大家都知道很慢,但磁盘IO由两种,顺序写和随机写,顺序写就是在一块连续的磁盘空间上写,随机写则是随机在磁盘找位置写,顺序写的效率要高的多,索引MySQL采用这种方式去减少磁盘IO.
(3)页
页是数据库存储的核心单位,是内存和磁盘交互的基本单位,一个页的大小默认是16KB,不存数据的情况下也会占用空间,页有以下常见页:
用于存储B+树节点的页，包括叶子节点和非叶子节点。叶子节点中存放的是实际的数据记录，非叶子节点存放的是目录项（索引）。
Undo页（Undo Log Page）：用于存储Undo日志的页，Undo日志用于支持事务的回滚操作。
系统页（System Page）：用于存储系统信息的页，如表空间头信息等。
事务数据页（Transaction System Page）：与事务处理相关的页，存储事务的元数据信息。
页的内部结构:
File Header:这个结构是每个页都会有的数据结构,其中存放了与其他页相连的指针(双指针)和表的元数据
Page Header:这个结构不是所以页都会有,只有数据页会有,当我们存在溢出页时溢出页就不会存在此结构,此结构中存的是页内的元数据
Infimum/supermum:这个结构可以看作是两条行信息,其中Infimum中会有指针指向这一页的第一行数据,supermum则会有页中数据的最后一行指向supermum信息
User Records:存储的用户数据
Free Space:尚未被用户记录使用的空间
Page Directory:这个结构则会将数据划分为若干个模块,以优化查找效率
File Tailer:里面主要是一些校验信息,用于保证数据的完整性
页中的操作:
数据访问:在数据页中，记录会按照主键值从小到大的顺序组成单向链表。为了提高查询效率，InnoDB还会为这些记录生成一个页目录，通过二分法快速定位到对应的槽，再遍历槽中的记录找到指定数据。
数据的插入和删除:随着数据的插入和删除，页可能会变得过满或过空。为了保持数据页的均衡和提高查询效率，InnoDB会执行页分裂或页合并操作。
(4)行
行的类型非常多,我们这里以Compact为例,在我们存储数据时,数据其实是存储在行中,也就是说,行是数据存储的最小单位,这些行由页进行管理,一个行的大小最大为700byte,当一行的数据量大于这个数值后,则会创建溢出页,在行内则会保存一个指针指向溢出页,也正是因为行,mysql才能实现MVCC(多版本并发控制)和高并发的特点,由于其需要支持事务等特性,所以结构也是最复杂的:
其中变长字段长度列表记录的是每个字段的偏移量
NULL列表:表示数据为null的列
记录头中的信息非常重要,也是实现各种机制的根本,最重要的有:delete_mask,删除标记,当我们删除一个一行信息时,Innodb不会立即删除这行,而是改变改标记,因为存在事务等特性,万一需要回滚等操作只需要该变即可;next_record,指针,指向下一行的偏移量,每一行的数据也是因为这个属性连接成一个链表的形式,这页中的最后一行数据的改字段会指向supermum;
记录真实的值则是用户的真实数据,当然,该区域有几个隐藏字段相当重要:DB_ROW_ID,当一张表中没有主键时,会默认的将该字段当成ID,也会给该ID创建B+树当作主键索引;DB_TRX_ID,事务ID,MVCC的实现则依赖该字段,undo页中则存储的了改字段;DB_ROLL_PTR回滚指针,当需要回滚时,则会使用该字段.
2、InnoDB的内存结构(默认128MB)
(1)Buffer Pool
InnoDB专用缓存,用来缓存表对象的数据和索引信息的。Page页之间通过链表结构进行管理，包括free list（空闲链表）可以当作缓存用的链表、flush list（刷新链表）对一些缓冲页进行修改(脏页),需要定期书信到磁盘上ush链表和LRU list根据数据页的访问时间会将数据页排列在LRU链表中,对LRU上的页进行清除时,会从末尾开始,
LRU：LRU链表被midpoint节点分为两个链表young链表,old链表,新读取的页会先被放到midpoint节点,后续将根据访问情况分别添加至young或old,再根据时间淘汰old链的末尾页
(2)Change Pool
用于缓存对不在Buffer Pool中的非主键索引页的更新操作，以减少对磁盘的随机读取。
(3)Log Buffer
用于缓存即将写入磁盘的redo log和undo log日志数据。它减少了磁盘IO操作，提高了日志写入效率。
四、索引
1、B+树索引结构
B+树的特点：所有数据存储在叶子节点，非叶子节点仅存储索引。每个节点都是一页,
其中再每页中会有下个节点的页偏移量
2、索引分类
聚簇索引(主键索引):主键索引会包含此页中所有数据的列
二级索引:主键索引之外的都叫二级索引,包含组合索引等,组合索引是一种特殊的二级索引,在B+树的叶节点上都没有完整的数据,二级索引由于没有一行中的所有字段数据(只包含索引字段和主键字段,主键字段没有的话,前面提到的DB_ROW_ID会当成默认的主键),所以当根据索引字段进行查询时,会先通过索引字段匹配到具体的主键,再根据主键去 聚簇索引中找到详细的数据(此过成叫回表),联合索引:当二级索引B+树的匹配列大于二就时组合索引,其中如果需要同事根据两个条件进行比较,会按照最左匹配原则找到对应的数据ID,再去主键索引中找到对应的数据,避免了两个条件多次回表的情况(索引下推);当需要得到的数据可以直接在组合索引中找到,比如ID,就不会进行回表(覆盖索引)
五、日志
Bin-Log(server)逻辑日志:
binlog时mysql服务层面的日志文件,默认不开启,可以在my.ini文件中手动开启,可以通过show binlog events;命令查看binlog,当然在data目录下存在这个具体的文件,可以使用命令(mysqlbinlog)将文件转化为可读的状态,再进行查看,binlog只会记录增删改的记录,binlog会不断的累计日志的写入模式如下:
statement(默认记录方式):记录SQL,会有一定风险
row:记录具体的行数据
mixed:以上两种结合
应用场景:数据归档,主从备份,恢复到指定时间
Redo-Log(存储引擎)物理日志:
redolog也记载的是执行的数据变动,不同的是redolog记录的是数据页的变动,而不是具体的sql,
redolog有固定的大小,可以自己设置,会使用一个文件组的策略,循环的复写这个文件组去提升利用率,文件组一个重要的概念是check_point用于标记可以复写的位置.
redolog的主要应用场景是故障的恢复,因此只需要记录更新的日志,也就是产生脏页的数据,事务提交的时候redolog是一定会刷盘的,具体就是先将脏页更新到logBuffer中再进行刷盘到redolog,每一个存在数据修改的事务redolog都能保证,当提交事务时,脏页再内存中更新,并写入到redolog日志中,这里大家可能会有疑问,为什么不直接刷新到ibd文件而是刷新到redolog这样,等故障恢复还要将redolog中的数据加载到内存再刷新到ibd,不是多次一举吗,原因是,从flash链表刷新到redolog和ibd文件是不一样的,在redolog中是一个顺序写,而在ibd则有很大的概率是随机写,对性能的损耗很大.
Undo-Log(存储引擎):
undolog严格意义上讲是一种类型的页(undo页),他其中有两条链表,insert链表和updata链表,但它起到了日志作用,前面也有提到,它并不额外对应一个日志文件,所以它最终存储的位置是ibd文件中,undolog与事务回滚和MVCC(多版本并发控制)有关,所以是一种相当重要的日志,当修改一条数据时,innodb会将新增一条数据存在undolog,并用行中的回滚指针(Roll_point)连成链表形成版本链,再添加一份与当前数据相连在真正的页中,然后将值改为修改的值.
对于不同的错做,undolog存的东西也不一样,undo页也是可以复用的页,因为当insert操作完成后,
对于新增操作来说,undolog只会存储该行的ID值;
对于修改的操作,则只记录修改前的旧值
对于删除操作,会先将数据的deleted字段标记,做一个软删除,当事务提交时,会真正的删除数据
六、事务
什么是事事务?
由一组数据操作序列组成,数据操作之间是原子性的,也就是说要么同时成功,要么同时失败
事务特性(ACID):
原子性:表示一条事务中的操作要么同时成功,要么同时失败
持久性:一旦事务被提交，它对数据库的修改就应该是永久性的，即使系统发生故障也不会丢失。
隔离性:事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。这通过锁机制或并发控制机制来实现，以防止多个事务同时修改同一数据导致的冲突。
一致性:事务必须使数据库从一个一致性状态变换到另一个一致性状态。即事务执行的结果必须保持数据的一致性，不会发生破坏数据完整性的情况。
事务隔离级别:
读未提交(READ UNCOMMITTED)
在该级别下即使事务未提交,对其他事务也是可见的,即一个事务可以读取另一个事务未提交的数据。(脏读)
存在的问题:脏读,幻读,不可重复读
读已提交(READ COMMITTED)
这种隔离级别下，可以避免脏读，但可能会出现不可重复读（Non-repeatable Read），即在同一个事务中，连续两次读取同一数据可能会有不同的结果，因为其他事务可能在中间提交了修改。
存在的问题:幻读,不可重复读
可重复读(REPEATABLE READ)
在这个级别下，事务在启动时，它会看到一个一致的数据快照，这意味着同一个事务中的所有读操作会看到同样的数据。这种隔离级别下，可以避免脏读和不可重复读。但在某些情况下，可能会出现幻读（Phantom Read），即在同一事务中，连续两次执行相同的查询，第二次查询可能会看到在第一次查询后由其他事务插入的新行。InnoDB 默认使用这一隔离级别，并通过多版本并发控制（MVCC）机制来避免幻读。
在特定情况下的 有幻读风险
串行化(SERIALIZABLE)
七、MVCC(多版本并发控制)
工作原理：
确定那些比最新的事务要小并且已经提交的事务
ReadView：
为什么存在：为了确定应该使用版本链中的哪一个版本（判断一个DB_TRX_ID是已经提交的还是正在运行的）
结构：
max_trx_id:下一个要分配的trx_id;
main_trx_id:所有的活跃中的版本中最小的ID;
m_ids:所有活跃的trx_id;
creator_trx_id:当前事务的ID（可能是0，因为当前可能没有执行到自己事务的写操作）
原理：通过判断当前事务在哪一个区间里从而判断当时的版本能不能用
RC在每次读操作时都会创建一个新的ReadView,因而不可重复读，
RR在第一次读的时候会创建ReadView，除过自己写的值，其他事务读到的都是一个ReadView直到事务提交
快照读：读某一时刻的数据
当前读：每次读都是最新提交的数据（写的时候一般都是当前读）
MVCC解决了可重复读的事务隔离级别和视图读下的幻读问题；
当前读的幻读问题，需要锁来完成；
八、锁
行锁主要解决了当前读的幻读问题
行锁
记录锁(ret_not_gap)：给数据加锁，保证其他事务无法修改数据。
例如：update XXXX where id=1;
间隙锁(gap):锁住一个区间，当前条目和前一条条目的间隙不包含当前条目。
例如q：update XXXX where id<1;
临键锁：记录锁+间隙锁，包括当前条目都会被锁到。
例如：update XXXX where id<=1;
以上三种锁保证不会出现并发写
插入意向锁：在间隙锁中插入数据，会分配一个插入意向锁
隐式锁：惰性的加记录锁的方式是隐式锁（insert）
共享锁: 我占用了，其他的事务还可以读，不能写
独占锁：我占用了，其他的事务无法读也无法写
表锁
操作： lock tables user READ;
lock tables user write;
行锁和表锁有一定的互斥性
类型：意向锁：
自增锁：当insert这句SQL结束的时候释放锁（其他锁针对事务）
MDL：（serve级别）修改表的结构加的该锁
死锁：当互相争夺资源时出现死锁，死锁出现会报错，并向上报告
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
