图解MySQL | [原理解析] MySQL insert 语句的磁盘写入之旅

作者及简介:
黄 炎,爱可生首席技术官;
王 悦,爱可生研发团队成员,负责数据库管理平台相关项目的开发和故障排查,好奇 MySQL 技术原理及各类数据库实现方案。
本文来源:转载自公众号-图解 MySQL
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


一条 insert 语句在写入磁盘的过程中到底涉及了哪些文件?顺序又是如何的?下面我们用两张图和大家一起解析 insert 语句的磁盘写入之旅。 

 

图 1:事务提交前的日志文件写入


旅途过程:

1.  首先 insert 进入 server 层后,会进行一些必要的检查,检查的过程中并不会涉及到磁盘的写入。
2.  检查没有问题之后,便进入引擎层开始正式的提交。我们知道 InnoDB 会将数据页缓存至内存中的 buffer pool,所以 insert 语句到了这里并不需要立刻将数据写入磁盘文件中,只需要修改 buffer pool 当中对应的数据页就可以了。

  • buffer pool 中的数据页刷盘并不需要在事务提交前完成,其中的交互过程我们会在下一张图中分解。

4.  但仅仅写入内存的 buffer pool 并不能保证数据的持久化,如果 MySQL 宕机重启了,需要保证 insert 的数据不会丢失。redo log 因此而生,当 innodb_flush_log_at_trx_commit=1 时,每次事务提交都会触发一次 redo log 刷盘。(redo log 是顺序写入,相比直接修改数据文件,redo 的磁盘写入效率更加高效)
5.  如果开启了 binlog 日志,我们还需将事务逻辑数据写入 binlog 文件,且为了保证复制安全,建议使用 sync_binlog=1 ,也就是每次事务提交时,都要将 binlog 日志的变更刷入磁盘。

综上(在 InnoDB buffer pool 足够大且上述的两个参数设置为双一时),insert 语句成功提交时,真正发生磁盘数据写入的,并不是 MySQL 的数据文件,而是 redo log 和 binlog 文件。然而,InnoDB buffer pool 不可能无限大,redo log 也需要定期轮换,很难容下所有的数据,下面我们就来看看 buffer pool 与磁盘数据文件的交互方式。

 

名词背景说明

double write 背景

InnoDB buffer pool 一页脏页大小为 16 KB,如果只写了前 4KB 时发生宕机,那这个脏页就发生了写失败,会造成数据丢失。为了避免这一问题,InnoDB 使用了 double write 机制(InnoDB 将 double write 的数据存于共享表空间中)。在写入数据文件之前,先将脏页写入 double write 中,当然这里的写入都是需要刷盘的。有人会问 redo log 不是也能恢复数据页吗?为什么还需要 double write?这是因为 redo log 中记录的是页的偏移量,比如在页偏移量为 800 的地方写入数据 xxx,而如果页本身已经发生损坏,应用 redo log 也无济于事。

insert buffer 背景

InnoDB 的数据是根据聚集索引排列的,通常业务在插入数据时是按照主键递增的,所以插入聚集索引一般是顺序磁盘写入。但是不可能每张表都只有聚集索引,当存在非聚集索引时,对于非聚集索引的变更就可能不是顺序的,会拖慢整体的插入性能。为了解决这一问题,InnoDB 使用了 insert buffer 机制,将对于非聚集索引的变更先放入 insert buffer ,尽量合并一些数据页后再写入实际的非聚集索引中去。

 

图 2:事务提交后的数据文件写入


旅途过程:

1.  当 buffer pool 中的数据页达到一定量的脏页或 InnoDB 的 IO 压力较小 时,都会触发脏页的刷盘操作。
2.  当开启 double write 时,InnoDB 刷脏页时首先会复制一份刷入 double write,在这个过程中,由于double write的页是连续的,对磁盘的写入也是顺序操作,性能消耗不大。
3.  无论是否经过 double write,脏页最终还是需要刷入表空间的数据文件。刷入完成后才能释放 buffer pool 当中的空间。
4.  insert buffer 也是 buffer pool 中的一部分,当 buffer pool 空间不足需要交换出部分脏页时,有可能将 insert buffer 的数据页换出,刷入共享表空间中的 insert buffer 数据文件中。
5.  当 innodb_stats_persistent=ON 时,SQL 语句所涉及到的 InnoDB 统计信息也会被刷盘到 innodb_table_stats 和 innodb_index_stats 这两张系统表中,这样就不用每次再实时计算了。
6.  有一些情况下可以不经过 double write 直接刷盘

  • a. 关闭 double write
  • b. 不需要 double write 保障,如 drop table 等操作

汇总两张图,一条 insert 语句的所有涉及到的数据在磁盘上会依次写入 redo log,binlog,(double write,insert buffer) 共享表空间,最后在自己的用户表空间落定为安。

<think>嗯,我现在要理解MySQL查询数据的完整执行流程。首先,我应该回忆一下之前学过的MySQL相关知识。记得MySQL是基于客户端-服务器模型的,所以当用户发起一个查询请求时,客户端会发送SQL语句到服务器端处理。那服务器端处理的过程具体是怎样的呢? 首先,连接管理应该是一个步骤。客户端需要先连接到MySQL服务器,这可能涉及到建立TCP连接,验证用户名密码,还有权限检查。这部分可能由连接器(Connector)处理。连接建立后,连接器会管理这个连接,保持其活跃状态,或者在空闲一段时间后断开。 接下来,当SQL查询语句到达服务器后,分析器(Parser)应该会进行词法分析和语法分析。词法分析是把SQL语句拆分成各种关键字,比如SELECT、FROM、WHERE这些,还有表名、列名等。语法分析则是检查这些拆分后的元素是否符合SQL语法规则,如果语法错误,比如少了个逗号或者关键字顺序不对,这时候就会报错。 然后,优化器(Optimizer)会起作用。优化器的任务是对查询进行优化,选择最有效的执行计划。比如,当有多个索引可用时,优化器会决定使用哪个索引,或者决定表的连接顺序,以减少查询时间。这里可能涉及到成本估算,选择成本最低的执行方式。 接下来是执行器(Executor)执行查询的阶段。执行器会根据优化器生成的执行计划,调用存储引擎的接口来获取数据。执行器在操作之前会先检查用户是否有对相关表的执行权限,如果没有权限,就会返回错误。如果有权限,就会开始执行。 存储引擎(Storage Engine)负责实际的数据存储和提取。比如InnoDB会从磁盘中读取数据,处理事务,锁等。执行器通过存储引擎提供的接口,逐行获取符合条件的数据,然后返回给客户端。 在返回结果的时候,如果查询缓存(Query Cache)是开启的,MySQL可能会在分析查询之前检查缓存,如果缓存中存在完全相同的查询语句及其结果,就直接返回缓存中的数据,而不需要执行后续步骤。不过,由于查询缓存容易失效,尤其是在写操作频繁的情况下,MySQL 8.0之后已经移除了查询缓存功能。 另外,执行过程中可能还涉及到日志模块,比如redo log、undo log和binlog,但这些可能更多是在写操作(如UPDATE、INSERT)时使用,查询的话可能影响不大,除非涉及到事务的读取一致性。 不过,可能有一些细节我还不清楚。比如,预处理阶段是否在分析器之后?执行器在调用存储引擎时,是如何处理索引扫描和全表扫描的?还有,优化器具体是如何选择执行计划的,是基于统计信息吗? 再想想,连接管理阶段,线程的处理是怎样的?每个连接是否对应一个线程?是的,MySQL通常使用线程池来处理多个连接,每个连接在一个线程中处理。 还有,权限验证是在哪个阶段进行的?可能是在连接建立的时候检查全局权限,而在执行查询的时候,执行器会再次检查表级别的权限,比如是否有权限查询某个表。 关于存储引擎,不同的存储引擎处理方式不同,比如MyISAM和InnoDB在数据读取时的差异。比如,MyISAM不支持事务,而InnoDB支持,所以在处理查询时,InnoDB可能需要考虑MVCC(多版本并发控制)来保证读取的一致性,特别是在可重复读隔离级别下。 总结一下,整个流程应该是这样的: 1. 客户端发起连接,连接器管理连接,验证权限。 2. 查询先检查查询缓存(如果开启),命中则直接返回。 3. 分析器进行词法和语法分析,生成解析树。 4. 优化器生成执行计划,选择最优路径。 5. 执行器检查权限,调用存储引擎接口获取数据。 6. 存储引擎读取数据,返回给执行器,再返回给客户端。 不过,需要确认这些步骤是否正确。比如,查询缓存在MySQL 8.0已经被移除了,所以现在可能不存在第二步。另外,预处理阶段是否在分析器之后,比如处理一些变量绑定或者语句重写? 可能还有预处理阶段,比如处理变量,或者检查表是否存在,列是否存在等语义分析,这部分可能在分析器中完成,或者是在优化器之前的一个阶段。 另外,执行器在调用存储引擎的时候,是如何进行操作的?比如,执行器可能根据执行计划,调用存储引擎的索引扫描接口,逐行获取数据,然后根据WHERE条件过滤,或者存储引擎自己处理条件过滤? 比如,如果使用了索引条件下推(Index Condition Pushdown),存储引擎会在索引扫描时就进行部分条件过滤,减少回表次数。 可能执行器会生成一个执行计划,然后存储引擎按照这个计划去读取数据,比如全表扫描或者索引扫描,然后将结果返回给执行器,执行器可能再进行进一步的过滤、排序、分组等操作。 总之,整个流程大致分为连接管理、解析与优化、执行与数据获取这几个阶段。每个阶段的具体细节需要进一步确认。</think>MySQL查询数据的完整执行流程可分为六个核心阶段,按顺序如下: --- ### 一、连接管理(Connector) 1. **建立连接** - 客户端通过TCP三次握手与服务器建立连接 - 连接器验证用户名、密码、主机权限 - 成功后分配线程并维护连接状态(`show processlist`可见) 2. **连接保持** - 默认闲置8小时自动断开(`wait_timeout`参数控制) - 建议生产环境使用连接池管理 --- ### 二、查询缓存(Query Cache)※注:MySQL 8.0已移除 1. **缓存检查** - 以SQL语句为Key,结果集为Value的键值存储 - 要求语句**完全一致**(包括空格、大小写) 2. **缓存失效** - 任何数据修改操作(INSERT/UPDATE/DELETE)都会使相关表缓存失效 --- ### 三、解析与预处理(Parser) 1. **词法分析** - 拆分SQL为关键字(如SELECT)、表名、列名等Token ```sql SELECT id,name FROM users WHERE age > 18 → 拆分为 [SELECT], [id], [,], [name], [FROM], [users], [WHERE], [age], [>], [18] ``` 2. **语法分析** - 生成抽象语法树(AST) - 校验基础语法错误(如缺少WHERE关键字) 3. **语义检查** - 验证表/列是否存在 - 检查数据类型兼容性 --- ### 四、查询优化(Optimizer) 1. **生成执行计划** - 基于成本模型(Cost-Based Optimization)选择方案: ```sql -- 示例查询 SELECT * FROM t1 JOIN t2 USING(id) WHERE t1.c=10 AND t2.d=20; ``` 可能方案: - 方案1:先读取t1.c=10的记录,再关联t2 - 方案2:先读取t2.d=20的记录,再关联t1 2. **优化策略** - 索引选择(覆盖索引 vs 回表) - JOIN顺序调整 - 条件下推(Index Condition Pushdown) - 使用`EXPLAIN`可查看最终执行计划 --- ### 五、执行阶段(Executor) 1. **权限验证** - 检查用户对目标表的SELECT权限 2. **调用存储引擎** ```plaintext 执行器 → 存储引擎接口 → 数据读取 ``` - 按执行计划逐行获取数据 - 典型案例: - 使用索引:`index_read` - 全表扫描:`read_first_row` + `read_next_row` 3. **结果暂存** - 使用内存临时表处理GROUP BY/ORDER BY - 超过`tmp_table_size`则转为磁盘临时表 --- ### 六、结果返回 1. **增量返回机制** - 边执行边发送(避免内存爆增) - 网络包大小受`max_allowed_packet`限制 2. **结果格式化** - 按协议封装数据(文本协议或二进制协议) - 字符集转换(如utf8mb4 → client编码) --- ### 流程图解 ```mermaid sequenceDiagram participant Client participant Connector participant Parser participant Optimizer participant Executor participant Storage_Engine Client->>Connector: TCP连接请求 Connector->>Client: 认证结果 Client->>Connector: 发送SQL查询 Connector->>Parser: 转交查询 Parser->>Optimizer: 解析后的AST Optimizer->>Executor: 执行计划 Executor->>Storage_Engine: 数据请求 Storage_Engine->>Executor: 返回数据页 Executor->>Client: 流式返回结果 ``` --- ### 关键性能优化点 1. **连接池** - 减少频繁建立连接开销(推荐使用HikariCP等) 2. **索引设计** - 联合索引注意最左前缀原则 - 避免过度索引影响写入性能 3. **执行计划分析** - 定期使用`ANALYZE TABLE`更新统计信息 - 关注`EXPLAIN`中的type列: - const > ref > range > index > ALL 4. **存储引擎选择** - InnoDB:事务/高并发写入 - MyISAM:只读分析场景(已逐渐淘汰) 通过理解整个执行流程,可以更精准地定位查询性能瓶颈并进行针对性优化。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值