Sql语句执行流程

一、 MySQL 基础架构

分层设计：
- Server 层： 跨存储引擎的通用功能。包括连接器、查询缓存（8.0 已移除）、分析器、优化器、执行器。内置函数、存储过程、触发器、视图等都在这一层。
- 存储引擎层 (插件式)： 负责数据的实际存储和读取。支持多种引擎（如 InnoDB, MyISAM, Memory 等）。不同引擎有不同的特性（事务支持、锁定粒度、存储方式等）。
  - InnoDB: 默认引擎 (自 5.5.5 起)，支持事务 (ACID)、行级锁、外键、崩溃安全 (Crash-safe)。是当前最主流、功能最全面的引擎。
  - MyISAM: 早期的默认引擎，不支持事务，使用表级锁，读取性能可能较高，支持全文索引，崩溃恢复性较差。

二、 SQL 查询语句执行流程 (select * from T where ID=10;)

连接器 (Connector):
- 负责客户端连接、TCP 握手。
- 用户认证： 验证用户名和密码。
- 权限验证： 认证通过后，查询并缓存用户权限。连接期间权限不变，即使管理员修改了权限，已建立的连接不受影响，新连接才应用新权限。
- 连接管理： 维护连接状态 (Sleep 表示空闲)。wait_timeout 参数控制空闲连接超时断开 (默认 8 小时)。
- 长连接 vs 短连接：
  - 长连接： 复用连接，减少建立开销，推荐使用。
  - 问题： 长连接可能导致 MySQL 内存占用持续增长，因为执行查询时的临时内存与连接绑定，只在连接断开时释放。可能引发 OOM (Out Of Memory) 导致 MySQL 异常重启。
  - 解决方案：
    - 定期断开： 使用一段时间或执行大查询后主动断开重连。
    - mysql_reset_connection (MySQL 5.7+)： 重置连接状态到初始状态，释放临时资源，无需重连和重新鉴权。
查询缓存 (Query Cache):
- 已在 MySQL 8.0 中移除。
- 机制： 以 SQL 语句为 Key，结果为 Value 缓存。命中则直接返回。
- 缺点： 失效频繁。任何对表的更新都会导致该表所有查询缓存失效。对于更新频繁的表，命中率极低，维护缓存开销大。
- 适用场景： 极少更新的静态表（如系统配置表）。
- 按需使用 (8.0 前)： query_cache_type=DEMAND + SQL_CACHE 关键字。
分析器 (Analyzer):
- 词法分析： 识别 SQL 中的关键字、表名、列名等。
- 语法分析： 检查 SQL 语句是否符合 MySQL 语法规则。语法错误会报错 (You have an error in your SQL syntax...)。
优化器 (Optimizer):
- 核心作用： 决定如何执行 SQL 语句以达到最高效率。
- 决策内容：
  - 选择使用哪个索引。
  - 决定多表连接 (JOIN) 的顺序。
- 目标： 生成最优的执行计划。
执行器 (Executor):
- 权限检查： 再次检查用户是否有执行该查询的权限。
- 调用引擎接口： 根据优化器确定的计划，调用存储引擎提供的接口来获取数据。
  - 无索引：调用引擎接口取第一行 -> 判断条件 -> 取下一行 -> 重复...
  - 有索引：调用引擎接口取满足条件的第一行 -> 取满足条件的下一行 -> 重复...
- 结果返回： 将满足条件的行组成结果集返回给客户端。
- rows_examined： 慢查询日志中记录执行器调用引擎接口获取数据的行数（不完全等于引擎内部扫描行数）。

三、 SQL 更新语句执行流程 (update T set c=c+1 where ID=2;)

更新语句同样经历查询流程 (连接器 -> 分析器 -> 优化器 -> 执行器)。
关键区别： 涉及两个重要的日志模块：redo log 和 binlog。
查询缓存失效： 更新操作会使相关表的查询缓存全部失效。

四、重要的日志模块

redo log (重做日志):
- 归属： InnoDB 引擎特有。
- 类型： 物理日志。记录“在某个数据页上做了什么修改”。
- 作用： 实现 Crash-safe 能力。保证即使数据库异常重启，已提交事务的修改也不会丢失。
- 机制 (WAL - Write-Ahead Logging)：
  - 先写日志 (redo log)，再写磁盘数据文件。
  - 更新时，先写 redo log buffer，再写 redo log 文件（持久化），并更新内存中的数据页。
  - InnoDB 会在后台或特定时机（如 redo log 写满）将内存中的脏页刷到磁盘。
- 特点： 固定大小，循环写入。使用 write pos (当前写入位置) 和 checkpoint (可擦除位置) 维护。当 write pos 追上 checkpoint 时，需暂停更新，先刷脏页推进 checkpoint。
- 关键参数： innodb_flush_log_at_trx_commit=1 (推荐)，每次事务提交都将 redo log 持久化到磁盘，最大限度保证不丢数据。
binlog (归档日志):
- 归属： MySQL Server 层实现，所有存储引擎都可用。
- 类型： 逻辑日志。记录 SQL 语句的原始逻辑（或行格式的变化）。
- 作用：
  - 数据恢复： 用于 Point-in-Time Recovery (基于全量备份 + 应用 binlog)。
  - 复制 (Replication)： 主从同步的基础。
- 特点： 追加写入。文件达到一定大小或满足特定条件后会切换到下一个文件，不会覆盖旧日志。
- 关键参数： sync_binlog=1 (推荐)，每次事务提交都将 binlog 持久化到磁盘，保证主从一致性和恢复时 binlog 不丢失。
为什么需要两种日志？
- 历史原因：MySQL 原生 MyISAM 不支持 crash-safe，binlog 主要用于归档。InnoDB 作为插件引入时，自带 redo log 实现 crash-safe。
- 功能互补：redo log 保证 InnoDB 自身事务的持久性和崩溃恢复；binlog 保证 Server 层的逻辑操作记录，用于恢复和复制。

五、两阶段提交 (Two-Phase Commit, 2PC)

目的： 保证 redo log 和 binlog 这两个独立日志系统的数据逻辑一致性。
流程 (针对更新语句)：
1. InnoDB 写 redo log (prepare 状态)：引擎将更新写入内存，并记录 redo log，标记为 prepare。
2. Server 写 binlog：执行器生成该操作的 binlog 并写入磁盘。
3. InnoDB 提交事务 (commit 状态)：执行器调用引擎提交接口，引擎将 redo log 状态从 prepare 改为 commit。
重要性：
- 防止数据不一致： 如果没有 2PC，在写完一个日志后、写另一个日志前发生崩溃，会导致：
  - 先 redo 后 binlog: 崩溃恢复后数据已更新 (redo log 生效)，但 binlog 缺失该操作，导致基于 binlog 的恢复或从库数据不一致 (少了更新)。
  - 先 binlog 后 redo: 崩溃恢复后数据未更新 (redo log 未 commit/写入)，但 binlog 已记录该操作，导致基于 binlog 的恢复或从库数据不一致 (多了更新)。
- 保证恢复和复制的准确性： 2PC 确保了只有在两个日志都成功记录（或标记成功）的事务，才被认为是最终成功的，从而保证了基于 binlog 的恢复和主从复制的正确性。

六、备份与恢复

常用策略： 定期全量备份 + 应用 binlog。
恢复过程： 找到最近的全量备份恢复 -> 从备份时间点开始，应用 binlog 直到指定恢复时间点。
备份频率（思考题延伸）：
- 一天一备 vs 一周一备：
  - 优势 (一天一备)：
    - 更小的 RPO (Recovery Point Objective): 意味着潜在的数据丢失量更少。极端情况下，最多丢失一天内的数据（加上未备份的 binlog）。一周一备则可能丢失近一周的数据。
    - 可能更快的 RTO (Recovery Time Objective): 需要重放的 binlog 量相对较少，恢复过程可能更快。
  - 影响指标： 主要影响 RPO (数据丢失容忍度) 和 RTO (恢复时间目标)。系统越重要，对数据丢失和恢复时间的要求越高，备份频率应越高。

七、总结

理解 MySQL 架构有助于排查问题。
查询和更新语句经过 Server 层多个组件处理，最终由存储引擎操作数据。
redo log (InnoDB) 保障崩溃安全，binlog (Server) 用于恢复和复制。
两阶段提交是保证 redo log 和 binlog 数据一致性的关键机制。
长连接需注意内存管理，推荐使用 mysql_reset_connection (5.7+)。
合理配置 innodb_flush_log_at_trx_commit 和 sync_binlog 对数据安全至关重要。