深入浅出 MySQL 连接、线程、查询缓存与优化器详解

运行机制

基本流程

MySQL的连接管理是通过Connector（连接器）和Connector Pool（连接池）实现的。

连接通信机制详解

MySQL客户端与服务端的通信采用的是”半双工”模式：

  1. 半双工特性：

    • 在任何给定的时刻，连接只能执行发送或接收操作中的一种
    • 不能像全双工通信那样同时进行双向数据传输

  2. 线程状态管理：

    • 每个MySQL连接都对应一个独立的线程

连接监控与管理

-- 查看活动线程
show processlist;

-- 查看完整线程信息
SHOW FULL PROCESSLIST;

-- 终止特定连接
KILL [CONNECTION|QUERY] process_id;

查询缓存详解

什么是查询缓存

MySQL查询缓存是数据库内置的一个查询优化机制。当开启查询缓存功能后，系统会将SELECT查询的结果和对应的SQL语句存储在缓存中。

查询缓存工作原理

  1. 检查查询缓存是否开启
  2. 在缓存中查找完全匹配的SQL
  3. 命中则直接返回结果，未命中则继续解析执行流程

缓存排除情况

  • 使用了SQL_NO_CACHE提示的查询
  • 查询结果超过query_cache_limit设置的大小
  • 包含非确定性函数如now(), rand()的查询

配置参数

-- 查看缓存基本配置
SHOW VARIABLES LIKE '%query_cache%';

-- 查看缓存使用详情
SHOW STATUS LIKE '%Qcache%';

查询优化器

1. 静态优化（编译时优化）

  • 等价变换：通过逻辑等价变换简化查询条件
  • 常量表达式计算：在编译时计算常量表达式

2. 动态优化（运行时优化）

  • 聚合函数优化：
    • count(*)：InnoDB引擎会优先使用最小索引统计行数
    • min()：只需要查找索引最左边的记录
    • max()：只需要查找索引最右边的记录
  • LIMIT优化：当查询包含LIMIT子句时，优化器会提前终止扫描
  • 索引条件下推(ICP)：将WHERE条件直接下推到存储引擎层进行过滤

查询执行过程

  1. 存储引擎交互：根据表的存储引擎类型调用对应的API接口
  2. 结果返回：对于大型结果集采用增量返回模式（流式处理）
  3. 查询缓存处理：首先检查缓存中是否存在完全匹配的SQL语句

注意：MySQL 8.0已移除查询缓存功能，建议使用其他缓存方案。