每日学习Java之一万个为什么？[MySQL面试篇]

分析SQL语句执行流程中遇到的问题

前言
1 MySQL是怎么在一台服务器上启动的
2 MySQL主库和从库是同时启动保持Alive的吗？
3 如果不是主从怎么在启动的时候保证数据一致性
4 ACID原则在MySQL上的体现
5 数据在MySQL是通过什么DTO实现的
6 客户端怎么与MySQL Server建立连接，有哪些建立连接的方式，默认是什么，可以选什么，有哪些应用场景
7.SQL组件接受的SQL是什么形式？为什么单一一条SQL不做任何处理就被MySQL认为是一个事务？
8.查询后的结果是由哪个组件以什么形式返回？
9.解析器是怎么解析的，解析器会调用API吗？
10.优化器是怎么优化的？会调用API吗？
11.SQL报错机制会在哪些区域出现？以什么形式发给客户端？以什么形式写到哪个日志中？
12.执行器本质上是什么？
13 MySQL内部有哪些线程？怎么分类？
14 MySQL进程包含哪些内容
15 执行器会将sql提交给引擎还是调用引擎，又是怎么调用的
16 buffer pool区域中的数据页有多少，以什么结构存储，脏页呢？
17 buffer pool 中的数据页怎么更新的，淘汰后的数据页由谁清理
18 后台线程对脏页刷盘是怎么实现的
19 sql语句中加锁在哪个部分实现的
20 事务在engine中怎么实现
21 MVCC怎么实现
22 redolog分为哪几块内容？有什么作用
23 binlog呢
24 undolog 呢
25 数据写磁盘怎么实现的，怎么传输的，怎么保证一致性的
26 主从数据一致性会保证内存数据一致吗？
27 MySQL断开连接可以从线程和客户端区分吗
28 MySQL进程结束会执行哪些持久化操作

前言

面试想要过关斩将，你就必须扪心自问，问得越多问的越细，才能披荆斩棘。盔甲若是漏洞百出，不被戳死才怪。

下列问题为作者提出，回答搜集而来。
由于作者并没有系统化从零开始学习MySQL，所以可能某些非常基础的问题并不了解。

欢迎读者留言比较重要的基础，wink👍

1 MySQL是怎么在一台服务器上启动的

MySQL通过配置文件：my.cnf my.ini 来指定各种参数，如端口号、数据目录等。
初始化数据库：首次启动的时候，MySQL需要初始化系统表（这里指定的是系统表空间吗）
启动系统，生成日志

2 MySQL主库和从库是同时启动保持Alive的吗？

为了实现数据同步，通过binlog+并行复制维持数据一致性，主库应该首先启动并正常运行，然后从库再启动并连接到主库。

3 如果不是主从怎么在启动的时候保证数据一致性

如果不是主从库，没法保证，可以自己设置一些通信维护。
如果没有同时启动，其实无所谓。
保证数据一致性，主从之间首先需要通信，分为三类异步、同步、半同步。由于是主库写日志，等待从库响应。所以主库到底等不等从库是一个影响性能的问题。异步-半同步-同步，一致性会越来越高，期望响应时间越来越长。（那么这里的sql写日志都是事务写进去的吗）
一句话概括：主库提交会写binlog，会由一个dump线程监听binlog文件的变更，如果有更新会推送更新时间到从库，从库接收到事件后会拉取数据，有一个IO线程将binlog传过来的数据写到自己的relaylog中，慢慢消化。所以，我们先更新的是事务语句，至于执行则是慢慢来。
5.6库级别并行复制：
5.7 Group Commit 级别并行复制
logical_clock 逻辑时钟复制
Write Set复制

4 ACID原则在MySQL上的体现

A:MySQL使用BEGIN /COMMIT /ROLLBACK 来管理事务
C：通过约束和触发器确保数据库的状态始终符合预期
I：支持四种隔离级别，读未提交，读已提交，可重复读，串行化，通过锁定机制和MVCC实现
D：一旦事务提交，数据就会永久保存，即使发生系统崩溃也不会丢失。MySQL使用重做日志和双写缓冲区来实现持久性

5 数据在MySQL是通过什么DTO实现的

6 客户端怎么与MySQL Server建立连接，有哪些建立连接的方式，默认是什么，可以选什么，有哪些应用场景

TCP IP 通过网络地址和端口连接到MySQL服务器，适用于远程连接
Unix Socket连接：在本地Linux/Unix系统上，可以通过Unix域套接字连接到MySQL服务器。这种方式比TCP IP更快，因为它避免了网络层的开销

java">jdbc:mysql://database_name?socket=/temp/mysql.sck

命名管道：仅适用于win 本地连接
共享内存：另一种win特有的连接方法
默认使用TCP IP

7.SQL组件接受的SQL是什么形式？为什么单一一条SQL不做任何处理就被MySQL认为是一个事务？

MySQL接收到的SQL是标准的SQL语句，通常以文本的形式发给服务层

在MySQL中，默认情况下，每条单独提交的SQL语句都被视为一个独立的事务。这是因为autocommit模式下，每条语句执行完毕后会自动提交事务。如果autocommit被关闭，需要显式的使用 BEGIN 和 COMMIT 提交事务

8.查询后的结果是由哪个组件以什么形式返回？

执行器也就是sql线程生成结果集，通过网络协议返回给客户端

执行器：执行查询并生成结果集 MYSQL_RES结构
网络层：将结果集打包成适当的格式（如二进制或文本），并通过连接通道返回给客户端。

java">typedef struct st_mysql_res
{
my_ulonglong row_count;
unsigned int field_count,current_field;
MYSQL_FIELD *fields;
MYSQL_DATA *data;
MYSQL_ROWS *data_cursor;
MEM_ROOT field_alloc;
MYSQL_ROW row;
MYSQL_ROW current_row;
unsigned long *lengths;
MYSQL *handle;
my_bool eof;
}MYSQL_REStypedef struct st_mysql_rows 
{struct st_mysql_rows *next;	//列表的行MYSQL_ROW data;
} MYSQL_ROWS;   //mysql的数据的链表节点。可见mysql的结果集是链表结构typedef struct st_mysql_data 
{my_ulonglong rows;unsigned int fields;MYSQL_ROWS *data;MEM_ROOT alloc;
} MYSQL_DATA;   // 数据集的结构typedef struct st_mysql_field 
{char *name;					// 列名称 char *table;	                //如果列是字段，列表char *def;	                //默认值(由mysql_list_fields设置)enum enum_field_types type;	//类型的字段。Se mysql_com。h的类型unsigned int length;	        //列的宽度unsigned int max_length;	    //选择集的最大宽度unsigned int flags;	        //Div标记集unsigned int decimals;	    //字段中的小数位数
} MYSQL_FIELD;  //列信息的结构typedef struct st_used_mem      //结构为once_alloc
{	struct st_used_mem *next;   	//下一个块使用unsigned int left;	        //记忆留在块unsigned int size;	        //块的大小
} USED_MEM;     //内存结构typedef struct st_mem_root 
{USED_MEM *free;USED_MEM *used;USED_MEM *pre_alloc;unsigned int	min_malloc;unsigned int	block_size;void (*error_handler)(void);
} MEM_ROOT;  //内存结构

可以看到MySQL是用C++/C 开发的

9.解析器是怎么解析的，解析器会调用API吗？

词法分析：将SQL语句分解为一个个token
语法分析：tokens转换语法树
语义分析
解析器本身并不调用外部API，但他可能会调用内部函数或者模块来完成某些任务，例如：
元数据访问：访问系统表或缓存中的元数据，以验证表和列的存在性。
权限检查：调用权限管理模块，确保用户有足够的权限执行该操作

10.优化器是怎么优化的？会调用API吗？

查询重写：对查询初步重写，以简化查询结构
统计信息收集：收集表的统计信息（例如行数、索引选择性等），用于生成最优执行计划。
生成候选计划：基于不同的访问路径（如全表扫描、索引扫描），生成多个候选执行计划（生成策略？）
选择最佳计划：评估每个候选计划的成本（评估算法？），选择成本最低的计划作为最终执行计划
优化器内部统计信息模块：获取表的统计信息（包含什么？）
代价模型：计算成本（理论支持？）
访问路径模块：确定可用的访问路径（索引、表扫描等）

11.SQL报错机制会在哪些区域出现？以什么形式发给客户端？以什么形式写到哪个日志中？

报错机制出现的区域：

解析阶段：SQL语法错误或未知表/列名
优化阶段：无法生成有效的执行计划
执行阶段：运行时错误，如违反约束、超时等
网络传输阶段：连接断开或其他通信错误

发给客户端的形式：

错误代码：一个唯一的数字标识。
错误消息：详细的描述信息，帮助理解错误原因。

java">{"code":1054,"message":"Unknown column 'nonexistent_column'in 'field list'"
}

写入日志的形式：

错误日志：记录所有严重的错误和警告，默认位于/var/log/mysql/error.log 或安装目录下的data文件夹中。
慢查询日志：记录执行时间超过阈值的查询，默认位于/var/log/mysql/slow-query.log

java">2025-02-27T11:41:23.123456Z 0 [ERROR] [MY-01054] Unknown column 'nonexistent_column' in 'field list'

12.执行器本质上是什么？

执行器是MySQL查询处理过程中实际执行查询计划的部分。它根据优化器生成的执行计划，调用存储引擎接口来访问和修改数据。

执行器的主要职责：

1.物理操作执行：根据执行计划中的物理操作（如表扫描、索引查找等）执行相应的数据库操作。
2.数据获取与处理：从存储引擎中获取数据，并进行必要的过滤、排序、聚合等操作。
3.结果集生成：将处理后的数据组织成结果集，准备返回给客户端。

执行器的工作流程：
1.初始化执行计划：加载执行计划并准备执行环境。
2.执行物理操作：按顺序执行各个物理操作步骤。
3.返回结果

13 MySQL内部有哪些线程？怎么分类？

1.主线程（Main Thread）

职责：负责启动和管理其他的线程
实例：mysql进程的主线程

2.连接处理线程（Connection Threads）

职责：处理客户端连接请求，每个客户端连接对应一个独立的线程
实例：thd线程对象，用于处理每个客户端连接

3.后台线程（Background Threads）

职责：执行各种后台任务，如日志刷新、数据页刷盘等。
示例：InnoDB缓冲池线程：负责缓冲池管理和脏页刷盘。Binlog Dump线程：监听binlog 发送给从库。Purge线程：负责清理不再需要的undo日志

4.IO线程（I/O Threads）

职责：处理与存储设备的IO操作
示例：Redo Log线程：负责写入重做日志。 Binlog线程：负责写入二进制日志。

5.事务处理线程（Transaction Handing Threads）

职责：管理事务的开始、提交和回滚操作。
示例：事务管理线程，负责协调事务的状态。

14 MySQL进程包含哪些内容

MySQL进程（mysqld）包含多个组件和模块，主要分为以下几个部分：

1.服务器核心 Server Core
1. 存储引擎 Strorage Engines
3.缓存和缓冲区（Caches and Buffers）
- Buffer Pool：用于缓存数据和索引数据。
- Query Cache：缓存查询结果以提高查询效率（读缓存已被移除）
日志系统（Logging System）
- Binlog：用于主从复制和数据备份，记录所有更改操作。
- Undo Log：用于事务回滚，记录事务的反向操作
- Redo Log：用于崩溃恢复，记录所有未提交的事务
网络层（Network Layer）
- 处理客户端连接、数据传输和协议解析。
安全管理（Security Management）
- 执行各种后台任务，如日志刷新、数据页刷盘等。

15 执行器会将sql提交给引擎还是调用引擎，又是怎么调用的

执行器会通过调用存储引擎的接口来执行具体的数据库操作

调用方式：

接口调用：执行器通过调用存储引擎提供的接口（如InnoDB的API）来执行具体的操作。
物理操作执行：根据优化器生成的执行计划，执行器调用相应的存储引擎接口来访问和修改数据。

16 buffer pool区域中的数据页有多少，以什么结构存储，脏页呢？

数据页数量：

大小可配置：Buffer Pool的大小可以通过配置文件中的innodb_buffer_pool_size参数设置，默认值通常为系统内存的70%-80%。
每页大小：默认情况下，每页大小为16KB。

存储结构：

LRU链表：Buffer Pool 使用LRU（Least Recently Used）链表来管理数据也。最近使用的页面位于链表前端，较少使用的页面位于链表末端。
Flush链表：脏页（已修改但尚未写入磁盘的页面）会被放入Flush链表中，等待刷盘

脏页：

定义：脏页是指已经被修改但还写回到磁盘的数据页。
存储位置:脏页同样存储在Buffer Pool中，并被标记为脏页。
管理：脏页通过Flush链表管理，定期或在特定条件下（如内存不足或打到一定阈值）被刷回磁盘。

17 buffer pool 中的数据页怎么更新的，淘汰后的数据页由谁清理

数据页更新：

读取当前数据页：当需要访问某个数据页时，如果该页不在Buffer Pool中，则从磁盘加载到Buffer Pool。
修改数据页：对数据页进行修改后，将其标记为脏页。
写回磁盘：脏页会在适当的时候（如内存不足或达到一定的阈值）通过后台线程写回到磁盘。

淘汰后的数据页清理：

LRU链表：当Buffer Pool 满时，使用LRU算法淘汰最近最少使用的页面
清理过程：
- 如果被淘汰的页面是干净的（未修改），可以直接丢弃。
- 如果被淘汰的页面是脏页，则必须先将其写回磁盘（通过Flush链表管理）。

18 后台线程对脏页刷盘是怎么实现的

后台线程通过以下几种机制实现脏页的刷盘：
1.定时刷盘：

InnoDB自动刷新：InnoDB会定期检查脏页的比例，如果超过设定的阈值（如innodb_max_dirty_pages_pct），则触发自动刷新操作。

2.后台线程：

Page Cleaner线程：负责将脏页写回到磁盘。它会定期检查Flush链表中的脏页，并将这些脏页回到磁盘。

3.事务提交：

同步刷盘：在某些情况下（如事务提交），为了保证数据一致性，需要立即刷盘。此时会调用fsync（）函数确保数据已经写入磁盘。

4.内存压力：

当Buffer Pool内存不足时，会优先淘汰脏页，并将其写回磁盘。

19 sql语句中加锁在哪个部分实现的

SQL语句的加锁操作主要在执行器和存储引擎中实现：

1.解析阶段：

解析器解析SQL语句，识别出需要加锁的对象（如表、行等）

2.执行器：

执行器根据解析结果，在执行过程中调用存储引擎的加锁接口，对相关对象加锁。

3.存储引擎：

存储引擎实现具体的加锁机制。例如，InnoDB支持行级锁（共享锁，排他锁）和表级锁。

java">SELECT * FROM users WHERE id = 1 FOR UPDATE;

执行器在执行这条查询时，会调用InnoDB的加锁接口，对users表中id=1的行加排它锁

20 事务在engine中怎么实现

事务在存储引擎中的实现主要包括以下几个方面：

1.事务管理器：

负责事务的生命周期，包括开始、提交、回滚。

2.日志系统：

Redo Log：记录所有未提交的事务操作，用于崩溃恢复。
Undo Log：记录事务的反向操作

3.并发控制：

MVCC（多版本并发控制）：支持高并发下的读写操作，避免锁冲突
锁机制：提供行级锁和表级锁，确保事务的隔离性。

4.持久化：

双写缓冲区（Doublewrite Buffer）：确保数据页在写入磁盘时的一致性。
刷盘操作：通过后台线程定期将脏页协会到磁盘里。