MySQL进阶—系统架构篇🔥

MySQL 大厂面试题—系统架构篇

MySQL (6)

1、MySQL整体架构？

2、MySQL 总览分为网络连接层、服务层、系统文件层

其中，MySQL服务内，可以以插件的形式，实现各种存储引擎。因此，再细分我们可以分为网络连接层、服务层、存储引擎层、系统文件层。

2、MySQL 支持哪些存储引擎？默认用的哪个？

常见的几个存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务
InnoDB	5.5版本后MySQL的默认数据库存储引擎，支持事务和行级锁，比MyISAM处理速度稍慢
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。只在内存上保存数据，意味着数据可能会丢失
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装

重点理解 InnoDB存储引擎，当前主流与默认的引擎。此外，也需对照 MyISAM 理解

3、能否单独给一表设置存储引擎？

MySQL允许为每一张表单独设置存储引擎。在创建表的时候，可以通过 ENGINE 选项来指定表的存储引擎。例如：

CREATETABLE my_table ( id INT, name VARCHAR(20) ) ENGINE=InnoDB;

在这个例子中，我们创建了一个名为my_table的表，并指定其存储引擎为InnoDB。

如果你想改变已经存在的表的存储引擎，可以使用ALTER TABLE命令。例如：

ALTERTABLE my_table ENGINE=MyISAM;

在这个例子中，我们将my_table表的存储引擎改为了MyISAM。

需要注意的是，不同的存储引擎有不同的特性和用途，选择合适的存储引擎可以帮助提高数据库的性能。在实际使用中，应根据具体的需求和场景来选择存储引擎。

5、InnoDB 存储引擎底层架构是怎样的？

上图详细展示了 InnoDB 存储引擎的体系架构，从图中可见，InnoDB 存储引擎由 内存结构、磁盘结构 两部分组成。

5.1 内存结构

InnoDB 内存结构主要分为如下四个区域：

Buffer Pool 缓冲池
Change Buffer 修改缓冲
Adaptive Hash Index 自适应索引
Log Buffer 日志缓冲

5.2 磁盘结构

在磁盘中，InnoDB 将所有数据都逻辑地存放在一个空间中，称为表空间（Tablespace）。表空间由段（Segment）、区（extent）、页（Page）组成。

开启独立表空间innodb_file_per_table=1，每张表的数据都会存储到一个独立表空间，即表名.ibd 文件
关闭独占表空间innodb_file_per_table=0，则所有基于InnoDB存储引擎的表数据都会记录到系统表空间，即 ibdata1 文件

表空间是 InnoDB 物理存储中的最高层，目前的表空间类别包括：

系统表空间（System Tablespace）
独立表空间（File-per-table Tablespace）
通用表空间（General Tablespace）
回滚表空间（Undo Tablespace）
临时表空间（The Temporary Tablespace

面试需要着重理解的当以下两种，其余有兴趣自行了解

6、MyISAM 和 InnoDB 区别？

InnoDB 和 MyISAM 存储引擎区别：

区别	InnoDB	MyISAM
锁	支持行锁、表锁	只支持表锁
事务	支持ACID事务，支持四种隔离级别	不支持事务，但是每次查询都是原子的
存储文件	.frm 表定义文件 .ibd 数据文件和索引文件	.frm 表定义文件 .myd 数据文件 .myi 索引文件
索引	使用聚簇索引（底层 B+tree）	使用非聚簇索引（底层 B+tree）

1.MyISAM 与 InnoDB 区别

InnoDB聚簇索引，MyISAM非聚簇索引
InnoDB数据与索引一起保存.ibd，MyISAM 表结构 .frm 索引 .myi 数据 .myd
InnoDB支持事务、外键、行锁表锁，MyISAM不支持事务、外键、只支持表锁
select count(*)：InnoDB不存储总行数，MyISAM存储表的总行数
MyISAM查询更优，InnoDB 更新更优
都是B+tree索引
MyISAM支持全文索引，5.6后支

2.MyISAM

不支持事务，但是每次查询都是原子的
支持表级锁，每次操作对整个表加锁
存储表的总行数
一个 MyIAM 表有三个文件：表结构.frm、索引.myi、数据 .myd
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

3.InnoDB

支持ACID事务，支持四种隔离级别
支持行级锁及外键约束，因此支持写并发
不存储总行数
主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值;因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引;最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

4.使用场景

多数时候我们使用的都是 InnoDB 存储引擎，在某些读密集的情况下，使用 MyISAM 也是合适的。不过，前提是你的项目不介意 MyISAM 不支持事务、崩溃恢复等缺点（可是~我们一般都会介意啊！）。

MyISAM 适合读多更新少的：MyISAM 索引跟数据分开放，因此有读取更快的说法。
InnoDB 适合插入更新频繁的：索引与数据一起放，建立索引更复杂，使用行锁，更新频繁效率更高
需要事务，高并发场景用 InnoDB·：InnoDB 支持事务，采用行锁
MyISAM 查询比 InnoDB 快，更新 InnoDB 快

5.具体说一下如何做技术选型？

场景：MyISAM 查询更优，InnoDB 更新更优

MyISAM 适合读多，更新少的场景。MyISAM 使用非聚簇索引，数据和索引分开存的，而 InnoDB 数据和索引存一起的，数据量大时，一个内存页大小固定，读进内存的数据 MyISAM 就多一点(数据量小看不出差距，数据量大时差距就明显），因为 MyISAM 只把索引指针读进内存，可以存更多，查询速度也就更快，而且 InnoDB 还需要维护其他数据，比如其他隐藏字段 row_id tx_id等

选型：因此，默认使用 InnoDB，MyISAM 适用以插入为主的程序，比如博客系统、新闻门户

7、MySQL 执行一条查询语句内部执行过程？(越详细越好，可以往内存、磁盘方向说说)

mysql分为server层与存储引擎层，server层包含连接器、分析器、优化器、执行器**。**

接下来以一条sql查询语句执行过程介绍各个部分功能。客户端执行一条sql：

1、首先由连接器进行身份验证，权限管理

2、若开启了缓存，会检查缓存是否有该sql对应结果（缓存存储形式key-vlaue，key是执行的sql，value是对应的值）若开启缓存又有该sql的映射，将结果直接返回；

3、分析器进行词法语法分析

4、优化器会生成执行计划、选择索引等操作，选取最优执行方案

5、然后来到执行器，打开表调用存储引擎接口，逐行判断是否满足查询条件，满足放到结果集，最终返回给客户端；若用到索引，筛选行也会根据索引筛选。

图1、一条sql语句执行流程

8、一条更新语句是怎样的？

8.1、流程简述

系统通过一个数据库连接发送到了MySQL上，再经过SQL接口、解析器、优化器、执行器

几个环节，解析SQL语句，生成执行计划，接着去由执行器负责这个计划的执行，调用InnoDB存储引擎的接口去执行。

8.2、细节剖析

假如我现在执行如下 SQL 语句，结合手绘图分析一条更新 SQL 语句底层经历了哪些？

update tb_xl set num = 910 where id = N

客户端发起请求，执行如上 SQL语句，并向服务端建立连接
然后连接器进行身份验证，权限管理
若开启了缓存，会检查缓存是否有该 SQL 对应结果（缓存存储形式 key-vlaue，key 是执行的 SQL，value 是对应的值）若开启缓存又有该 SQL 的映射，将结果直接返回，否则开始真正分析并执行 SQL语句；（MySQL8.x 已经废弃了查询缓存）
分析器进行词法语法分析（识别到update等关键词，检查语法没问题便继续执行）
优化器会生成执行计划、选择索引等操作，选取最优执行方案
然后来到执行器，打开表调用存储引擎接口，执行 SQL 语句

结合本条 SQL语句它是怎样执行的呢？

执行器先调用引擎取 ID=N 这一行。ID 是主键，引擎直接用索引树搜索找到这一行；
如果本行所在数据页在 buffer pool 内存中，直接返回行数据给执行器更新
如果不在内存，就需要去磁盘文件读（如果用到唯一索引，会有一些不同）
1. 若为普通索引，会将更新操作记录在change buffer，然后进行merge操作（见笔记唯一索引和普通索引选取区别）
2. 若为唯一索引，由于要判断唯一性，只能从硬盘读取内存，再返给执行器
数据更新前为保证事务可回滚，会记录老版本数据至 undo log，并更新记录的回滚指针和事务ID，便于形成MVCC的版本链和实现回滚等操作
此后，执行器拿到引擎给的数据行，再将这个值变为 N+1 ，得到新数据，再调用引擎接口写入新数据
引擎将新数据更新到内存（注意先在缓冲池完成更新，此时数据还未同步磁盘，内存中数据可看成脏数据），同时这个更新操作会记录到 redo log，如果不用主从模式，未开启binlog ，事务提交，数据日志写盘完事。若开启binlog，需要两阶段提交，继续。
redo log 此时处于prepare状态，告知执行器执行完，可提交事务
执行器生产这个操作的 binlog，并将binlog写入磁盘
执行器调用引擎事务提交接口，引擎把刚写入的 redo log 改为 commit 状态，更新完成。
此后，MySQL有一个后台的IO线程，会在之后某个时间里，随机的把内存buffer pool中的修改后的脏数据给刷回到磁盘上的数据文件里去

8.3、面试亮点（再重述流程）

InnoDB 存储引擎架构

InnoDB的重要内存结构：缓冲池

InnoDB存储引擎中有块内存结构—缓冲池（Buffer Pool），执行操作时，会先检查 buffer Pool 里是否有该数据，如果不在，会先从磁盘里加载到缓冲池里，并且还会对这行记录加独占锁（别人更新时，不允许被修改）。

undo日志文件：如何让你更新的数据可以回滚？

执行更新语句时，在事务内是可回滚的，为了保证数据库能回滚到原始状态，在真正执行更新操作时，需要将更新前的值写入undo日志文件。日志机制在日志篇详解

**undo log，**回滚日志

**作用：**undolog记录事务开始前老版本数据，用于实现回滚，保证原子性，实现MVCC，会将数据修改前的旧版本保存在undolog，然后行记录有个隐藏字段回滚指针指向老版本。

更新buffer pool中的缓存数据

读取磁盘数据到 buffer pool 缓冲池中
记录更新前老版本数据至 unlog 回滚日志中
开始执行更新操作，更新缓冲池中数据

考点剖析：注意，先更新缓冲池内存中的数据后，此时真正更新后的数据还未同步磁盘，此时的 buffer pool 数据可以看为脏数据，那么假如 MySQL 还未来得及同步数据至磁盘，宕机导致内存里修改过的数据丢失怎么办**？**

Redo Log Buffer：如何避免数据丢失，crash-safe？

为了避免在数据库宕机时内存里已经被修改过但还未来得及同步磁盘的数据丢失，MySQL 还需要将它的修改动作进行备忘录。

也就是说更新数据前，需要记录老版本数据至 undolog，保证可回滚；更新数据后，需要记录数据的修改操作记录在 redolog 中，保证 crash-safe

Redo Log 写盘策略

操作写入 redolog 后，进行事务提交，也需要将 redo log 从redo log buffer 刷到磁盘文件中。需要配合以下参数策略完成，根据情况选择。

innodb_flush_log_at_trx_commit = 0（提交事务，日志不写磁盘，MySQL 宕机内存里的数据还是会丢失—内存中的数据和 Redo Log 都丢失）
innodb_flush_log_at_trx_commit = 1 （写入磁盘）
innodb_flush_log_at_trx_commit = 2（把redo日志写入磁盘文件对应的os cache缓存里，见上面架构图，没同步磁盘，亦可能丢失)

到这里，如果你不需要开启主从模式，事务提交，数据写入磁盘，日志写入磁盘便完事啦，但是，若你开启了主从，为了保证其数据一致性，便需开启 binlog 使用两阶段提交保证数据一致性，那接着往下看

两阶段提交

想要全面了解两阶段提交，我接下从这3个方面分析：

1、何为两阶段提交？

2、为什么要两阶段提交？

3、两阶段提交的过程是怎样的？

何为两阶段提交？（2PC）

MySQL 中在 server 层级别有个 binlog 日志，归档日志，用于备份，主从同步复制，如果采用一主多从架构，主备切换，那就必须用到 binlog 进行主从同步，此时事务提交就必须保证 redolog 与 binlog 的一致性，一般情况没有开启binlog日志，事务提交不会两阶段提交，若需要主从同步就必须开启 binlog 使用两阶段提交保证数据一致性。

为什么要两阶段提交？

保证 redo log 与 binlog 一致性，保证事务在多个引擎的原子性。

两阶段提交过程？

Prepare 阶段：InnoDB 将回滚段 undolog 设置为 prepare 状态；将 redolog 写文件并刷盘；（1、先写redolog，事务进入prepare状态）

Commit 阶段：Binlog 写入文件；binlog 刷盘；InnoDB commit；（2、prepare成功，binlog写盘，然后事务进入 commit 状态，同时会在 redo log 记录commit 标识，代表事务提交成功）

redolog与binlog怎样联系起来的？（XID）

崩溃恢复的时候，会按顺序扫描redo log，若redolog既有prepare又有commit，直接提交
如果碰到只有prepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

怎样判断binlog是否完整？

statement格式的binlog，最后会有COMMIT
row格式末尾有XID event

2pc不同时刻的崩溃恢复？

1、redolog有commite标识，事务完整，直接提交事务
2、若redolog里面的事务只有完整的prepare，则判断对应事务的binlog是否存在并完整（是-提交事务 | 否-回滚事务）

9、如果一张表创建了多个索引，在哪个阶段或模块进行索引选择？

在MySQL中，当一张表创建了多个索引时，索引的选择主要在生成执行计划的阶段进行。

生成执行计划是查询优化器的工作。查询优化器会根据查询语句和表的统计信息，评估每个可用索引的代价，然后选择代价最小的索引来执行查询。

代价的计算主要考虑以下因素：

数据的分布：如果索引的列上的数据分布不均，那么这个索引的代价可能会比较高。例如，如果索引的列上的大部分值都是相同的，那么这个索引可能不会被选择。
查询的选择性：如果查询的条件能够大幅度地减少结果集的大小，那么这个查询的选择性就比较高，相应的索引的代价就比较低。
索引的类型：不同类型的索引有不同的性能特点。例如，B-Tree索引适合范围查询，而Hash索引适合精确查询。
查询的其他部分：查询的其他部分，如排序条件（ORDER BY）、分组条件（GROUP BY）等，也会影响索引的选择。

在实际使用中，可以通过EXPLAIN命令来查看MySQL的查询计划，从而确定哪个索引被选择用来执行查询。

10、说说Buffer Pool 缓冲池？

还是拿这张图来看。Innodb引擎分内存结构，os cache，磁盘结构，而内存中便保护buffer pool 缓冲区。

我们在执行数据增删改操作时，不可能直接更新磁盘上的数据的，因为如果你对磁盘进行随机读写操作，那速度是相当的慢，随便一个大磁盘文件的随机读写操作，可能都要几百毫秒。

因此，操作数据时需要先将数据加载到内存（buffer pool 缓冲池）中，进行操作。同时，为了保证事务可回滚，数据库crash-safe，宕机数据不丢失，又需要借助 undolog回滚日志+redolog 日志。

如何配置buffer pool 大小？

Buffer Pool 作为内存数据结构默认情况下是128MB，实际生产环境下可调整可以修改 innodb_buffer_pool_size 对Buffer Pool进行按需调整。

而数据库只要一启动，就会按照你设置的Buffer Pool大小，再稍微加大一点（存描述信息等），去找操作系统申请一块内存区域，作为Buffer Pool的内存区域。

buffer pool里数据怎样存的？

道数据库的核心数据模型就是表+字段+行，而buffer pool 里数据则是按页加载，如下图：

假设我们要更新一行数据，1、数据库会先找到这行数据所在的数据页，2**、然后从磁盘文件里把这行数据所在的数据页加载到Buffer Pool里去**，数据页默认 16k.（详细页、段、区见下）

从磁盘加载过来的数据页便对应 buffer pool 的一张缓存页，每个缓存页都会对应一个元数据描述信息（数据页所属的表空间、数据页的编号、这个缓存页在Buffer Pool中的地址等），最后，在buffer pool 中呈现的形式大概如图所示：

11、谈谈 change buffer 的理解？

Change Buffer是MySQL中InnoDB存储引擎的一个特性，主要用于优化对二级索引的写操作。

在InnoDB中，每个数据页都有一个对应的Change Buffer。当需要修改二级索引时，InnoDB不会立即将修改写入磁盘，而是先将修改操作记录在Change Buffer中。然后，在后台的空闲时间，或者当需要读取被修改的数据页时，InnoDB会将Change Buffer中的修改合并到数据页，并将数据页写入磁盘。

Change Buffer的主要优点是可以减少磁盘I/O操作。因为多个修改可以合并为一次磁盘写入，所以使用Change Buffer可以显著提高写入性能。此外，Change Buffer还可以提高事务的并发性，因为事务在提交时不需要等待磁盘写入完成。

但是，Change Buffer也有一些限制。首先，Change Buffer只适用于二级索引，不适用于主索引。其次，Change Buffer只适用于INSERT、UPDATE和DELETE操作，不适用于其他类型的写操作。最后，Change Buffer会占用一部分内存，如果内存资源有限，可能需要调整Change Buffer的大小。

在实际使用中，可以通过innodb_change_buffering参数来控制Change Buffer的行为，包括启用或禁用Change Buffer，以及设置Change Buffer的大小。

12、说说 InnoDB 的页、区、段？

1）段【Segment】

表空间由各个段（Segment）组成，创建的段类型分为数据段、索引段、回滚段等。

由于 InnoDB 采用聚簇索引与 B+ 树的结构存储数据，所以事实上数据页和二级索引页仅仅只是 B+ 树的叶子节点。

因此数据段称为 Leaf node segment，索引段其实指的是 B+ 树的非叶子节点，称为 Non-Leaf node segment。

一个段会包含多个区，至少会有一个区，段扩展的最小单位是区。

数据段称为 Leaf node segment
索引段称为 Non-Leaf node segment

2）区【Extent】

区（Extend）是由连续的页组成的空间，大小固定为 1MB，由于默认页大小为 16K，因此一个区默认存储 64 个连续的页。如果页大小调整为 4K，则 256 个连续页组成一个区。为了保证页的连续性， InnoDB 存储引擎会一次从磁盘申请 4 ~ 5 个区。

3）页【Page】

页（Page）是 InnoDB 的基本存储单位，每个页大小默认为 16K，从 InnoDB1.2.x 版本开始，可通过设置 innodb_page_size 修改为 4K、8K、16K。

4）行【Row】

InnoDB 的数据是以行为单位存储的，1个页中包含多个行。在 MySQL5.7 中，InnoDB 提供了4种行格式：Compact、Redundant、Dynamic 和 Compressed 行格式，Dynamic 为 MySQL5.7 默认的行格式。创建表时可以指定行格式：

MySQL进阶—系统架构篇🔥

# MySQL 大厂面试题—系统架构篇

# 1、MySQL整体架构？

# 2、MySQL 支持哪些存储引擎？默认用的哪个？

# 3、能否单独给一表设置存储引擎？

# 5、InnoDB 存储引擎底层架构是怎样的？

# 5.1 内存结构

# 5.2 磁盘结构

# 6、MyISAM 和 InnoDB 区别？

# 1.MyISAM 与 InnoDB 区别

# 2.MyISAM

# 3.InnoDB

# 4.使用场景

# 5.具体说一下如何做技术选型？

# 7、MySQL 执行一条查询语句内部执行过程？(越详细越好，可以往内存、磁盘方向说说)

# 8、一条更新语句是怎样的？

# 8.1、流程简述

# 8**.2、细节剖析**

# 8.3、面试亮点（再重述流程）

# InnoDB 存储引擎架构

# InnoDB的重要内存结构：缓冲池

# undo日志文件：如何让你更新的数据可以回滚？

# 更新buffer pool中的缓存数据

# Redo Log Buffer：如何避免数据丢失，crash-safe？

# Redo Log 写盘策略

# 两阶段提交

# 9、如果一张表创建了多个索引，在哪个阶段或模块进行索引选择？

# 10、说说Buffer Pool 缓冲池？

# 如何配置buffer pool 大小？

# buffer pool里数据怎样存的？

# 11、谈谈 change buffer 的理解？

# 12、说说 InnoDB 的页、区、段？

# 1）段【Segment】

# 2）区【Extent】

# 3）页【Page】

# 4）行【Row】