MySQL技术内幕——知识梳理，应对面试杠杠滴！

最新推荐文章于 2024-03-05 22:42:38 发布

zhangkaixuan456

最新推荐文章于 2024-03-05 22:42:38 发布

阅读量391

点赞数

分类专栏： java基础文章标签：数据库 mysql 分布式

本文链接：https://blog.csdn.net/zhangkaixuan456/article/details/117733427

版权

java基础专栏收录该内容

126 篇文章 7 订阅

订阅专栏

MySQL体系架构

client(客户端) :比如JDBC、nacicat与service进行交互
连接器：管理连接验证权限
分析器：词法分析、语法分析
优化器：对sql进行优化
- CBO(Cost-Based Optimization 基于代价的优化器)
- RBO(Rule-Based Optimization 基于规则的优化器)
执行器：和存储引擎直接做交互
存储引擎：innodb、myisam、memory

InnoDB存储引擎

缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。

Buffer Pool缓冲池
undo日志文件

写入数据的旧值，便于回滚，主要用于事务的回滚(undo log记录的是每个修改操作的逆操作)和一致性非锁定读(undo log 回滚行记录到某种特定的版本---MVCC 多版本并发控制)。
redo日志文件

重做日志，对数据库中表的操作进行记录，可以用于系统崩溃时的数据恢复。保证事务的持久性
redo log buffer（重做日志缓冲）

InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。
binlog日志文件

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。

LRU List、Free List和Flush List链表

——Buffer Pool缓冲池是一个很大的内存区域，其中存放各种类型的页。InnoDB存储引擎通过这些链表对缓冲池的内存区域进行管理。

LRU List

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。

最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。

InnoDB的存储引擎中，LRU列表中还加入了midpoint位置。

在InnoDB存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。

那为什么不采用朴素的LRU算法，直接将读取的页放入到LRU列表的首部呢？

这是因为若直接将读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。

Free List

Free List是Buffer Pool中基于缓存页描述信息组织起来的双向链表。换言之，Free List中的每一个结点都是缓存页对应的描述信息。并且通过描述信息可以找到指定的缓存页（缓存页）

解决问题：从磁盘里面读取出来的数据页应该放到那个缓冲页中去呢？

如果这个缓存页中没有存储任何数据，那么它对应的描述信息就会被维护进Free List中。这时当你想把从磁盘中读取出一个数据页放入缓存页中的话，就得先从Free List中找一个节点（Free List中的所有节点都会指向一个从未被使用过的缓存页），那接着就可以把你读取出来的这个数据页放入到该节点指向的缓存页中。

相应的：当数据页中被放入数据之后。它对应的描述信息块会被从Free List中移出。

Flush List

用来管理将页刷新回磁盘。

在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页数据不一致。这时会通过checkpoint机制将脏页刷新回磁盘，而Flush list中的页即为脏页列表。

注：脏页既存在于LRU列表，也存在于Flush列表中

InnoDB行记录格式

InnoDB存储引擎和大多数数据库一样（如Oracle和Microsoft SQL Server数据库），记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。在InnoDB 1.0.x版本之前，InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据，这也是目前使用最多的一种格式。Redundant格式是为兼容之前版本而保留的。在MySQL 5.1版本中，默认设置为Compact行格式。

Compact行记录格式

行溢出数据

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。一般认为BLOB、CLOB这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，BLOB可以不将数据放在溢出页面，而且即便是VARCHAR列数据类型，依然有可能被存放为行溢出数据。

每一行数据都是放在一个数据页里的，这个数据页默认的大小是16KB，行数据的大小超过了页的大小怎么办呢？

实际上会在那一页里存储你这行数据，然后在那个字段中，仅仅包含他一部分数据，同时包含一个20个字节的指针，指向了其他的一些数据页，那些数据页用链表串联起来，存放超大字段里的数据。如下图：

InnoDB数据页结构

其中文件头占据了38个字节，数据页头占据了56个字节，最大记录和最小记录占据了26个字节，数据行区域的大小是不固定的，空闲区域的大小也是不固定的，数据页目录的大小也是不固定的，然后文件尾部占据8个字节。

InnoDB存储引擎中的锁

共享锁（S Lock），允许事务读一行数据。
排他锁（X Lock），允许事务删除或更新一行数据。
意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁

一致性非锁定读

一致性的非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制（multiversioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。该实现是通过undo段来完成。

一致性锁定读

MySQL默认事务的隔离级别为REPEATABLE READ，InnoDB存储引擎的SELECT操作使用一致性非锁定读。

但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作：

SELECT…FOR UPDATE
SELECT…LOCK IN SHARE MODE

SELECT…FOR UPDATE对读取的行记录加一个X锁(排他锁)，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁（共享锁），其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

锁问题

脏读

脏数据是指事务对缓冲池中行记录的修改，并且还没有被提交（commit）。此时被其他事务读取到的数据就是脏读。即读到未提交的数据

不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该同一数据集合，并做了一些DML操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。即读到已提交的数据

幻读

幻读是指一个事务内多次根据同一条件查询出来的记录行数不一致。

事务的实现

InnoDB存储引擎中的事务完全符合ACID的特性。

ACID是以下4个词的缩写：

原子性（atomicity）
一致性（consistency）
隔离性（isolation）
持久性（durability）

持久性

redo log重做日志用来实现事务的持久性，即事务ACID中的D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），其是持久的。

InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。

原子性

undo log回滚日志用来实现事务的原子性

在对数据库进行修改时，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

隔离性

SQL标准定义的四个隔离级别为：

READ UNCOMMITTED（读未提交）
READ COMMITTED（读已提交）
REPEATABLE READ（可重复读）------MySQL默认的隔离级别
SERIALIZABLE（串行化）

默认隔离级别（RR）下的事务并发执行的隔离性是由经典的MVCC多版本并发控制机制实现的，解决了脏写、脏读、不可重复读、幻读的问题。

undo log 版本链

undo log分为：

insert undo log
update undo log

insert undo log是指在insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。

update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

update undo log每条数据其实都有两个隐藏字段，一个是DATA_TRX_ID，一个是DATA_ROLL_PTR，这个DATA_TRX_ID就是最近一次更新这条数据的事务id，DATA_ROLL_PTR就是指向你了你更新这个事务之前生成的undo log。

ReadView机制

ReadView是事务开启时，当前所有事务的一个集合。

m_ids------这个就是说此时有哪些事务在MySQL里执行还没提交的
min_trx_id------就是m_ids里最小的值
max_trx_id------这是说mysql下一个要生成的事务id，就是最大事务id
creator_trx_id------就是你这个事务的id

详解

并发执行两个事务

事务A(trx_id=45) 事务B(trx_id=59)

此时生成一个ReadView

在事务A的场景下

如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。

MySQL实现MVCC机制的时候，是基于undo log多版本链条+ReadView机制来做的，默认的RR隔离级别，就是基于这套机制来实现的，依托这套机制实现了RR级别，除了避免脏写、脏读、不可重复读，还能避免幻读问题。

一致性

原子性，持久性，隔离性目的也是为了保障数据的一致性！

InnoDB存储引擎索引

InnoDB存储引擎支持以下几种常见的索引：

B+树索引
全文索引
哈希索引

分类

聚集索引（clustered index）

就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

辅助索引（Secondary Index，也称非聚集索引）

叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。

Cardinality值（基数）

建立索引字段是需要考虑下基数的大小，例如sex性别这个字段，基数很低，查询的根据这个条件查数据得到的结果可能是该表50%的数据（假设男女比例1∶1），就没有太大必要建立索引

联合索引

联合索引是指对表上的多个列进行索引

覆盖索引（covering index，或称索引覆盖）

即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

explain 分析

explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

字段	含义
select_type	常用的有 SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询等。
table	要查询的表
possible_keys	可选择的索引
key	实际使用的索引
rows	扫描的行数
type	索引查询类型

索引查询类型（type）
const	使用主键或者唯一索引进行查询的时候只有一行匹配
ref	使用非唯一索引
range	使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询
index	和all的区别是扫描的是索引树
all	扫描全表
system	表只有一行，这是一个 const type 的特殊情况
eq_ref	在进行联接查询的，使用主键或者唯一索引并且只匹配到一行记录

MySQL主从复制架构

MySQL的三种复制方式：

异步复制

MySQL默认的复制就是异步复制，主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并处理。主库将事务 Binlog 事件写入到 Binlog 文件中，此时主库只是通知 Dump 线程发送这些新的 Binlog，然后主库就会继续处理提交操作，并不保证这些 Binlog 传到任何一个从库节点上。这样就会存在一个问题，如果主库出现故障，此时主库已经提交的事务可能并没有传到从库上，可能导致数据丢失。

全同步复制

当主库提交事务之后，所有的从库节点必须收到、APPLY并且提交这些事务，然后主库线程才能继续做后续操作。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟。

MySQL高可用架构

一般生产环境里用于进行数据库高可用架构管理的工具是MHA，也就是Master High Availability Manager and Tools for MySQL，用perl脚本写的一个工具，这个工具就是专门用于监控主库的状态，如果感觉不对劲，可以把从库切换为主库。

MySQL分库分表

分库分表一定是为了支撑高并发、数据量大两个问题的。

常用的分库分表中间件

ShardingSphere

当当开源的，属于 client 层方案

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

Mycat

基于 Cobar 改造的，属于 proxy 层方案

支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 Sharding jdbc 来说，年轻一些，经历的锤炼少一些。

Sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 Sharding-jdbc 的依赖；

Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

zhangkaixuan456

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL技术内幕——知识梳理，应对面试杠杠滴！

MySQL体系架构 client(客户端):比如JDBC、nacicat与service进行交互连接器：管理连接验证权限分析器：词法分析、语法分析优化器：对sql进行优化 CBO(Cost-Based Optimization 基于代价的优化器) RBO(Rule-Based Optimization 基于规则的优化器) 执行器：和存储引擎直接做交互存储引擎：innodb、myisam、memory..
复制链接

扫一扫