一条SQL查询语句在MySQL中是如何执行的

一、MySQL整体架构MySQL的架构主要分为两层Server层和存储引擎层。Server层负责连接管理、SQL解析、优化以及执行等核心流程。当客户端发起连接时连接器负责建立连接、验证身份与权限。同时内部的连接池/线程管理模块会为每个客户端请求分配一个线程避免了频繁创建和销毁线程的开销。存储引擎层负责数据的实际存储与提取。MySQL采用插件式的存储引擎架构其中最常用的是InnoDB。1.1 一条SQL查询语句的执行流程下面我们以一条查询语句为例详细拆解它在MySQL内部经历的各个阶段。第一步连接器客户端与MySQL建立连接始于连接器。建立TCP连接MySQL基于TCP协议传输因此第一步是完成经典的TCP三次握手。身份验证连接器验证你提交的用户名和密码。如果验证失败客户端会收到Access denied for user的错误信息并结束执行。获取权限验证成功后连接器会立即从系统权限表中读取该用户的所有权限并缓存在当前连接会话中。这里有一个关键点后续此连接中的所有操作其权限判断都基于此次读取的快照。这意味着即使连接建立后管理员修改了你的权限当前已建立的连接也不会生效必须重新连接。关于连接管理的几个问题空闲连接会一直占用资源吗不会。MySQL通过wait_timeout参数控制空闲连接的最大时长默认是8小时。如果空闲连接超时连接器会自动断开它。你也可以手动执行kill connection命令来断开。断开连接后客户端会立刻知道吗不会。连接断开是服务端单方面操作的客户端要等到下一次发送请求时才会收到类似Lost connection to MySQL server during query的报错。MySQL的连接数有限制吗有限制。由max_connections参数控制该参数可以手动调整。我的MySQL服务默认是151个。一旦连接数超过这个限制系统会拒绝后续连接请求并返回Too many connections错误。长连接 vs 短连接MySQL的连接也分为短连接和长连接。由于长连接可以减少建立和断开连接的开销所以一般推荐使用长连接。但长连接也有隐患。MySQL在执行操作时会临时使用内存来管理连接对象这些资源只有在连接断开时才会彻底释放。如果长连接累积过多且执行了大量大操作会导致MySQL内存占用飙升严重时可能被操作系统强制杀掉导致MySQL服务异常重启。如何解决长连接占用内存的问题定期断开长连接在使用完一个批次的操作后主动断开连接释放内存。重置连接MySQL 5.7MySQL 5.7版本提供了mysql_reset_connection()接口函数。在代码中调用此函数可以在不重新建立TCP连接的情况下重置连接状态并释放临时内存恢复到刚刚建立连接时的状态。第二步查询缓存已废弃连接建立后客户端就可以向MySQL发送SQL语句了。MySQL收到SQL后首先会解析出语句的第一个字段判断其类型。如果是SELECT查询语句MySQL之前会先去查询缓存中查找。查询缓存以Key-Value形式保存在内存中Key是SQL查询语句Value是该语句的查询结果。如果命中缓存结果会直接返回给客户端如果未命中则继续执行后续流程并在执行结束后将结果存入查询缓存。为什么从MySQL 8.0开始查询缓存被移除了因为它的弊端非常明显。对一个表的任何更新操作增、删、改都会导致该表所有相关的查询缓存被全部清空。如果缓存了一个结果集很大的数据还没等被使用就被清空就完全是做无用功了。对于更新频繁的表缓存命中率极低维护缓存的成本反而更高。因此从MySQL 8.0开始查询缓存功能已被彻底移除这条路径也就走不通了。第三步解析器在正式执行SQL之前MySQL必须“读懂”这条语句。这个工作由解析器完成主要包含两步词法分析将输入的SQL字符串识别出一个个“Token”关键词、标识符等。例如SELECT username FROM userinfo会被解析出SELECT、username、FROM、userinfo四个Token其中SELECT和FROM是关键词。语法分析根据词法分析的结果语法解析器会检查SQL语句是否符合MySQL的语法规则。如果没问题就会构建一棵SQL语法树方便后续模块获取SQL类型、表名、字段名、WHERE条件等信息。注意如果输入的SQL语句存在语法错误例如写错了关键词就会在这个阶段报错。但是表或字段是否存在要等到下一阶段——预处理器才会检测。第四步执行SQL执行SQL语句是整个流程的核心可以细分为三个阶段预处理、优化和执行。1. 预处理器检查语义检查SQL查询语句中涉及的表和字段判断它们是否真实存在。扩展通配符将SELECT *中的*扩展为表中所有列的名称。2. 优化器优化器的职责是确定一个最高效的执行方案。例如当表中存在多个索引时优化器会基于查询成本如CPU、I/O成本来决定使用哪个索引。我们可以通过在查询语句前加上EXPLAIN命令来查看优化器选择的执行计划。执行计划中的key字段就表示实际使用到的索引。如果key为NULL则说明没有使用索引将会进行全表扫描。当一条查询既可以使用主键索引也可以使用二级索引时优化器会综合评估选择它认为成本更低的那个方案。3. 执行器优化器确定了执行方案后MySQL就真正开始执行语句了。这个“实干”环节由执行器负责。执行器的核心工作是与存储引擎进行交互并且这种交互是以记录Row为单位的。执行器会根据执行计划不断调用存储引擎提供的接口逐行获取数据并进行条件判断和结果收集。索引下推在了解具体的交互过程之前有必要先介绍一个重要的执行优化手段——索引下推。索引下推是 MySQL 5.6 引入的一项优化核心思想是将部分 WHERE 条件的判断下推到存储引擎层进行过滤从而减少 Server 层与存储引擎之间的交互次数。我们通过一个例子来理解。假设有一张用户表user建有一个联合索引(age, city)CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50), KEY idx_age_city (age, city) );现在执行一条查询SELECT * FROM user WHERE age BETWEEN 18 AND 25 AND city 北京;由于联合索引是(age, city)而条件中的city是索引的第二列根据最左前缀原则索引只能匹配到age BETWEEN 18 AND 25这个范围city 北京无法在索引树中直接过滤。在索引下推关闭的情况下存储引擎根据age BETWEEN 18 AND 25找到所有符合条件的索引记录。每找到一条就进行一次回表获取完整的行数据返回给执行器。执行器拿到数据后再判断city 北京是否满足不满足则丢弃。这意味着即使最终只有 100 条数据满足city 北京但如果age BETWEEN 18 AND 25命中了 10000 条记录就会发生 10000 次回表其中 9900 次都是浪费的。在索引下推开启的情况下默认开启存储引擎根据age BETWEEN 18 AND 25找到索引记录后直接在存储引擎层判断city 北京是否满足。只有满足city 北京的索引记录才会进行回表获取完整的行数据返回给执行器。这样一来回表次数从 10000 次降到了 100 次大大减少了 I/O 开销。可以用EXPLAIN查看执行计划如果Extra列中出现了Using index condition就表示本次查询使用了索引下推。了解索引下推之后我们再来看三种具体场景下执行器与存储引擎的交互过程。1主键索引查询假设执行语句SELECT * FROM user WHERE id 10;执行器调用存储引擎的接口请求读取表中第一行数据。存储引擎根据主键索引B树快速定位到id 10的数据页并返回该行记录。执行器接收到该行数据后判断其是否满足WHERE条件此处为id 10。由于主键查询最多只返回一条记录执行器在拿到数据后立即将该行加入结果集。执行器再次请求下一行存储引擎返回“已无更多记录”的信号执行器结束查询并将结果集返回给客户端。2普通索引查询回表假设执行语句SELECT * FROM user WHERE age 20;age字段上建有普通索引执行器请求存储引擎根据普通索引idx_age读取满足age 20的第一条记录。存储引擎在idx_age索引树上找到该记录获取到对应的主键 ID例如id 5。存储引擎再根据这个主键 ID回表到主键索引树进行查找得到完整的行数据id 5, name 张三, age 20并返回给执行器。执行器判断该行是否满足所有条件此处为age 20满足则加入结果集。执行器继续请求下一条满足age 20的记录存储引擎重复上述回表过程直到所有符合条件的记录被读取完毕。执行器将最终结果集返回给客户端。3全表扫描无索引假设执行语句SELECT * FROM user WHERE age 20;但age字段没有索引执行器请求存储引擎读取表中的第一行数据。存储引擎从数据文件的第一页开始读取第一行记录返回给执行器。执行器判断该行的age字段是否等于20。如果满足则加入结果集如果不满足则丢弃。执行器接着请求下一行存储引擎继续顺序读取下一行记录。这个过程会一直循环直到存储引擎将整个表的数据全部扫描完毕并返回“已无更多记录”的信号。执行器将所有满足条件的行组装成结果集返回给客户端。