MySQL的执行流程

目录一、MySQL的执行流程详解1客户端2客户端 - 连接器3连接器 - 查询缓存流程结束方式一4查询缓存 - 分析器流程结束方式二5分析器 - 优化器6优化器 - 执行器7执行器 - 存储引擎8执行器 - 返回结果正常流程结束9举例说明二、三次插入操作的事务模型三、两阶段提交为什么需要这个流程MySQL 内部两阶段提交的完整流程执行全流程客户端 - 连接器 - 查询缓存 - 分析器 - 优化器 - 执行器 - 存储引擎 -返回结果接下来是执行流程的详解一、MySQL的执行流程详解1客户端用户或者程序通过MySQL客户端发送select语句向MySQL服务端发起连接和查询请求2客户端 - 连接器连接器是客户端通过协议与服务端建立连接并且实现身份验证权限校验维持会话状态的连接连接条件通过 TCP/IP、Socket 或 Named Pipe 协议等身份验证连接器校验客户端提供的用户名、密码是否合法若开启了 SSL还会进行加密证书校验权限校验验证当前用户是否有执行该select语句所需的权限连接维护维护连接的会话状态长连接会被连接器保存在连接池中复用避免频繁创建销毁连接的开销3连接器 - 查询缓存流程结束方式一连接器将校验过后的SQL语句作为key去查询缓存中匹配是否有相同查询的结果集查询缓存需要命中缓存结果避免重复查询命中条件SQL 语句必须与缓存中的语句完全一致目标表的数据未被修改命中后—— MySQL会将缓存结果返回给客户端结束后续流程未命中—— 则传递给分析器继续执行流程4查询缓存 - 分析器流程结束方式二分析器对传递来的SQL语句进行词法分析语法分析成功则生成语法树词法分析将 SQL 语句拆分为一个个 “token”词法单元比如关键字SELECT、FROM、WHERE、标识符表名、列名、运算符、常量等语法分析根据 MySQL 的 SQL 语法规则对 token 序列进行校验判断语句是否符合语法规范生成抽象语法树校验关键字顺序是否正确、表名列名是否存在、运算符使用是否合法、函数调用是否正确等错误处理如果语法错误会直接返回 “SQL syntax error” 错误终止执行流程5分析器 - 优化器优化器根据分析器生成的语法树生成最优的语句执行方式逻辑优化等价变换简化表达式、合并子查询 / 视图、消除冗余条件外连接转内连接如果外连接的条件可以过滤掉 NULL 值会自动转为内连接提升执行效率谓词下推将过滤条件尽可能下推到数据读取阶段减少中间数据的传输量物理优化索引选择根据目标表的索引情况、统计信息决定使用哪个索引避免全表扫描连接顺序优化对于多表连接查询选择最优的表连接顺序减少中间结果集的大小算法选择为连接、排序、分组等操作选择最优的算法结果优化器最终会生成一份执行计划6优化器 - 执行器执行器根据优化器的执行计划进行二次的权限校验调用存储引擎的接口执行过滤查询将处理后的结果按照select子句的列顺序组装返回给客户端权限二次校验执行器会再次校验当前用户是否有访问目标表 / 列的权限调用存储引擎接口根据执行计划向存储引擎发起数据读取请求执行过滤与计算对存储引擎返回的数据执行WHERE条件过滤、GROUP BY分组、ORDER BY排序、聚合函数计算等操作结果集组装将处理后的结果集按SELECT子句的列顺序组装准备返回给客户端7执行器 - 存储引擎存储引擎是为执行器提供数据读写接口的负责数据的持久化存储、索引维护、事务管理然后将读取到的数据返回到执行器数据读取根据执行器的请求从磁盘中读取数据行如果使用索引会通过索引快速定位到目标数据避免全表扫描事务处理InnoDB 支持事务读取数据时会处理事务隔离级别数据返回将读取到的数据行返回给执行器供后续过滤、计算使用8执行器 - 返回结果正常流程结束执行器将组装好的结果集通过连接器返回给客户端客户端接收并展示结果一次 SELECT 查询流程正式结束9举例说明下面是一条SELECT * FROM user WHERE id 100; 语句的完整执行过程叙述客户端发起请求应用程序通过 JDBC 向 MySQL 服务端发送上述查询语句。连接器处理客户端与 MySQL 建立 TCP 连接连接器校验用户名密码和 user 表的查询权限连接建立成功。查询缓存检查MySQL 检查查询缓存中是否存在与该语句完全一致的结果若不存在则进入分析器阶段。分析器解析词法分析将语句拆分为 SELECT、*、FROM、user、WHERE、id、、100 等 token语法分析验证语句符合 SQL 规范生成抽象语法树确认 user 表和 id 列存在无语法错误。优化器优化优化器分析执行计划发现 id 列有主键索引选择使用主键索引快速定位数据生成最优执行计划通过主键索引查找 id100 的行直接返回结果。执行器执行执行器接收执行计划再次校验权限后调用 InnoDB 存储引擎的读取接口请求 id100 的数据行。存储引擎读取数据InnoDB 先在 Buffer Pool 中查找该数据页若命中则直接读取若未命中则从磁盘读取数据页到内存再通过主键索引定位到目标行将数据返回给执行器。结果返回执行器将数据组装为结果集通过连接器返回给客户端客户端接收并展示结果查询流程结束二、三次插入操作的事务模型我们把三次INSERT操作包裹在同一个事务中BEGIN; INSERT INTO table VALUES (...); -- 第1条 INSERT INTO table VALUES (...); -- 第2条执行失败 INSERT INTO table VALUES (...); -- 第3条 COMMIT;两种执行结果异常场景失败 执行到第 2 条INSERT时发生错误如约束冲突、主键重复、异常中断。 根据事务的原子性MySQL 会触发回滚ROLLBACK 即使第 1 条已经成功写入也会被撤销相当于三次操作什么都没发生数据库恢复到事务开始前的状态正常场景成功 三次INSERT全部执行成功没有任何异常。 此时事务会执行提交COMMIT所有修改永久生效数据库状态被更新核心结论原子性的本质事务的原子性就是保证操作的 “要么全做要么全不做”不允许出现 “部分成功、部分失败” 的中间状态。 三次插入被当作一个不可分割的整体要么全部成功提交要么全部失败回滚三、两阶段提交为什么需要这个流程两阶段提交协议为了保证 Redo Log 和 Binlog 数据一致性常用于分布式事务中MySQL 事务提交时需要同时写两个日志Redo LogInnoDB 的事务日志用于崩溃恢复保证数据不丢失BinlogServer 层的二进制日志用于主从复制、数据备份这两个日志的顺序出现问题会导致数据不一致Redo Log 写成功Binlog 写失败崩溃后用 Redo Log 恢复的数据和 Binlog 里记录的数据对不上Binlog 写成功Redo Log 没写完崩溃后 Binlog 记录了修改但 Redo Log 没有恢复时数据丢失所以 MySQL 用两阶段提交保证这两个日志的写入要么都成功要么都失败MySQL 内部两阶段提交的完整流程角色定义事务管理器TM协调者整个事务的协调中心负责发起准备、收集结果、最终决定提交 / 回滚资源管理器RM参与者实际执行事务的节点比如两个独立的数据库 A 和 B它们分别执行本地事务阶段一准备阶段事务管理器向所有资源管理器A 和 B发送prepare请求每个资源管理器收到请求后执行本地事务操作如三次插入并将操作结果写入本地日志如 redo log但不真正提交每个资源管理器向事务管理器返回执行状态success准备成功或failure准备失败阶段二提交 / 回滚阶段情况 1所有返回都是success事务管理器向所有资源管理器发送commit指令。 资源管理器收到指令后正式提交本地事务释放锁资源事务完成情况 2任意一个返回failure事务管理器向所有资源管理器发送rollback指令。 所有资源管理器执行回滚操作撤销之前的修改恢复到事务开始前的状态两阶段提交的缺陷单点故障问题事务管理器是整个流程的中心如果它在阶段二宕机资源管理器会一直等待无法完成提交或回滚导致资源如锁被长期占用影响系统可用性数据不一致问题如果事务管理器发送commit指令后部分节点如 A收到并提交成功而另一部分节点如 B因为网络故障没收到指令就会出现 “部分提交、部分未提交” 的状态导致分布式数据不一致