MySQL中SQL语句的详细执行过程

MySQL中SQL语句的详细执行过程

一、连接管理阶段

  1. 连接建立
    • 客户端通过TCP/IP、Unix Socket或命名管道等方式与MySQL服务器建立连接
    • 默认端口3306，可通过netstat -anp | grep 3306查看连接状态
  2. 认证授权
    • 服务器验证用户名、密码和主机权限
    • 检查mysql.user系统表验证身份
    • 成功认证后加载用户的权限设置
  3. 线程分配
    • MySQL为每个连接创建单独的线程
    • 线程信息可在performance_schema.threads中查看
    • 连接池技术可复用线程减少开销

二、查询处理阶段

1. 解析与预处理（Parser）

词法分析：

  • 将SQL字符串拆分为token流
  • 例如：SELECT * FROM users WHERE id=1被分解为：
    • 关键字：SELECT、FROM、WHERE
    • 标识符：*、users、id
    • 运算符：=
    • 常量：1

语法分析：

  • 检查SQL是否符合语法规则
  • 生成解析树（Parse Tree）
  • 错误示例：SELCT * FROM users会报语法错误

预处理：

  • 语义检查：验证表、列是否存在
  • 权限检查：mysql.columns_priv和mysql.tables_priv
  • 视图展开：将视图引用替换为基表查询
  • 常量表达式求值：如WHERE 1=1会被优化

2. 查询优化（Optimizer）

逻辑优化：

  • 条件化简：WHERE 1 AND a=1 → WHERE a=1
  • 外连接转内连接：当WHERE条件包含被驱动表非空约束时
  • 子查询优化：
    • 将IN子查询转为半连接（semi-join）
    • 派生表合并（Derived Condition Pushdown）

物理优化：

  • 访问路径选择：
    • 全表扫描 vs 索引扫描
    • 评估成本基于统计信息（SHOW INDEX FROM table）
  • 多表连接优化：
    • 连接顺序选择（左深树或右深树）
    • 连接算法选择（嵌套循环、哈希连接、排序合并）

执行计划生成：

  • 通过EXPLAIN可查看优化器选择
  • 关键指标：
    • possible_keys：可能使用的索引
    • key：实际选择的索引
    • rows：预估检查的行数
    • Extra：额外信息（如Using filesort）

3. 执行引擎

执行准备：

  • 创建执行上下文
  • 获取必要的锁（根据隔离级别）
    • InnoDB行锁：共享锁(S)、排他锁(X)
    • 意向锁：IS、IX
    • 元数据锁（MDL）

执行过程：

  • 调用存储引擎API
  • 对于SELECT：
    • 通过索引或全表扫描定位数据
    • 使用缓冲池(Buffer Pool)减少磁盘IO
  • 对于DML：
    • 写undo log保证事务回滚
    • 写redo log保证持久性
    • 更新Buffer Pool中的数据页（脏页）

三、存储引擎层（以InnoDB为例）

  1. 缓冲池管理
    • 检查所需数据页是否在Buffer Pool中
    • 若不在则从磁盘读取（产生物理IO）
    • 使用LRU算法管理页面
  2. 事务处理
    • 开启事务（隐式或显式BEGIN）
    • 分配事务ID（trx_id）
    • 写undo log记录修改前映像
  3. 锁管理
    • 行锁实现：
      • 记录锁（Record Lock）
      • 间隙锁（Gap Lock）
      • 临键锁（Next-Key Lock）
    • 死锁检测（通过等待图）
  4. 日志记录
    • redo log：物理日志，保证持久性（WAL机制）
    • binlog：逻辑日志，用于复制和时间点恢复
    • 两阶段提交保证redo log和binlog一致性

四、结果返回阶段

  1. 结果集生成
    • 排序（如使用filesort或索引排序）
    • 聚合（GROUP BY）
    • 过滤（HAVING）
    • 限制（LIMIT）
  2. 结果返回
    • 通过网络协议发送给客户端
    • 可能分批传输大量结果
    • 返回影响行数等元信息
  3. 清理工作
    • 释放锁资源
    • 记录慢查询（如超过long_query_time）
    • 更新性能统计信息

五、特殊语句处理差异

  1. INSERT语句
    • 检查唯一约束
    • 处理自增列（auto_increment）
    • 批量插入优化（bulk insert）
  2. UPDATE/DELETE语句
    • 使用读视图（MVCC机制）
    • 处理级联操作（外键约束）
    • 标记删除而非物理删除（InnoDB）
  3. 事务型语句
    • COMMIT：刷新日志，释放锁
    • ROLLBACK：应用undo log回滚更改

六、性能影响因素

  1. 系统变量
    • sort_buffer_size：排序缓冲区
    • join_buffer_size：连接缓冲区
    • read_rnd_buffer_size：随机读缓冲区
  2. 关键指标
    • 逻辑读 vs 物理读
    • 临时表使用情况
    • 锁等待时间
  3. 优化建议
    • 添加适当索引
    • 重写复杂查询
    • 使用预处理语句