Mysql高频八股——SQL语句的执行过程

SQL语句在MySQL中的执行过程主要分为查询语句和更新语句两大类，核心流程涉及连接器、分析器、优化器、执行器及存储引擎的协作，同时更新操作需依赖redo log和binlog实现持久化与数据一致性。

MySQL采用分层架构，分为Server层和存储引擎层，两者通过接口交互：

• Server层：包含连接器、分析器、优化器、执行器及binlog模块，负责SQL解析、优化和通用日志记录。
• 存储引擎层：以插件形式管理数据存储（如InnoDB、MyISAM），支持自定义策略。InnoDB引擎自带redo log模块，用于崩溃恢复。

以查询文学101班英语成绩>90分的学生为例：

1. 连接器

   验证用户身份（账号密码）及数据库访问权限，建立连接后分配独立线程。

2. 查询缓存（MySQL 8.0前）

   若SQL完全匹配缓存键（含空格、大小写），直接返回缓存结果。

   注意：MySQL 8.0后移除此模块，因维护成本高于查询收益。

3. 分析器

   词法分析：拆解SQL为关键字（SELECT）、表名（students）、字段名（name）等标记。

   语法分析：检查语句结构是否合法（如JOIN语法、WHERE条件逻辑）。

   语义验证：确认表/字段存在（如验证scores.score字段是否存在）。

4. 优化器

   执行计划选择：

   无索引时：全表扫描。先遍历students表筛选class='文学101'的记录，再逐条匹配scores表中subject='English'且score>90的记录。

   有索引时：索引扫描。通过students.class索引快速定位目标班级学生，再利用scores.subject和scores.score的联合索引过滤符合条件的成绩记录。

   优化目标：减少I/O操作（如避免全表扫描）、利用索引覆盖扫描（仅通过索引获取数据，无需回表）。

5. 执行器

   根据优化器选定的计划调用存储引擎接口获取数据。

   若需排序或分组，可能在内存中完成计算（如使用临时表或文件排序）。

6. 存储引擎

   InnoDB：从磁盘或缓冲池（Buffer Pool）中读取数据页，返回结果集。

   MyISAM：直接读取.MYD数据文件，无事务支持。

以更新文学101班英语成绩+2分为例：

1. 连接器与分析器

   流程与查询语句相同，但需额外验证用户是否有UPDATE权限。

2. 优化器

   类似查询语句，优先选择索引扫描（如通过students.class和scores.subject索引定位目标记录）。

3. 执行器与日志记录（核心差异）

   步骤1：执行器调用存储引擎修改数据，先写redo log缓冲区（而非直接落盘），记录“将某数据页的某行score从93改为95”。

   步骤2：生成binlog事件，记录更新前后的完整行数据（用于主从复制）。

   步骤3：事务提交时，redo log按顺序写入磁盘（确保持久性），binlog同步刷盘（通过sync_binlog参数控制）。

   步骤4：存储引擎根据redo log更新数据页（若数据在Buffer Pool中则直接修改，否则从磁盘加载后修改）。

4. 崩溃恢复机制

   若数据库崩溃，重启后：

   redo log：重放未落盘的数据页修改，保证数据不丢失。

   binlog：通过对比redo log和binlog的位置（如检查点），确保两者一致性，避免主从数据分歧。

• 查询语句流程：连接 → （缓存） → 分析 → 优化 → 执行 → 存储引擎重点：优化器选择高效执行路径（如索引覆盖扫描）。

• 更新语句流程：连接 → 分析 → 优化 → 执行（redo log缓冲） → binlog记录 → 存储引擎更新重点：通过redo log实现崩溃恢复，binlog保障主从同步。

• 性能优化方向：

  为高频查询字段建立合适索引（如联合索引（subject, score））。

  合理配置binlog和redo log参数（如innodb_flush_log_at_trx_commit=1确保最高持久性）。

  避免大事务（减少redo log和binlog写入压力）。