核心内容摘要
震撼来袭！冰冰炮150图最新版本更新内容大揭秘，点燃你的无限激情！
MySQL之SQL语句执行过程详解概述MySQL执行一条SQL语句需要经过多个处理阶段从客户端发送请求到最终返回结果整个过程涉及多个组件的协同工作。
本文将详细介绍MySQL中SQL语句的执行流程。
整体架构MySQL的架构可以分为三层连接层负责处理客户端的连接、认证和权限管理服务层包含SQL接口、解析器、优化器、缓存等核心组件存储引擎层负责数据的存储和检索SQL执行流程详解
连接器Connector当客户端连接到MySQL服务器时首先由连接器负责处理建立连接客户端通过TCP/IP协议或Unix Socket与服务器建立连接身份认证验证用户名和密码权限管理获取该用户的权限信息后续的权限判断都基于此时读取的权限-- 查看当前连接SHOWPROCESSLIST;连接建立后如果后续没有操作连接会保持空闲状态。
MySQL的wait_timeout参数控制空闲连接的超时时间默认8小时。
查询缓存Query Cache- MySQL
0已移除在MySQL
0之前的版本中如果开启了查询缓存完全相同的SQL语句会直接从缓存中返回结果如果表被修改相关缓存会被清空由于查询缓存在高并发场景下锁竞争严重MySQL
0已完全移除此功能。
分析器Parser分析器是SQL处理的第一步包含词法分析和语法分析两个阶段。
1 词法分析Lexical Analysis词法分析器将输入的SQL语句分解成一个个单词Token识别出关键字SELECT、FROM、WHERE、INSERT等表名user、order、product等列名id、name、price等操作符、、、AND、OR等常量数字、字符串等特殊符号逗号、括号、分号等示例SELECTid,nameFROMuserWHEREage18;词法分析后的Token序列SELECT - id - , - name - FROM - user - WHERE - age - - 18 - ;
2 语法分析Syntax Analysis语法分析器根据MySQL的语法规则将Token序列构建成抽象语法树AST验证SQL语句的语法是否正确。
语法树示例SELECT ├── columns: [id, name] ├── table: user └── where: └── condition: age 18如果SQL语法错误会返回错误信息mysqlSELECT*FORMuser;ERROR1064(
: You have an errorinyourSQLsyntax;checkthe manual that correspondstoyour MySQL server versionfortherightsyntaxtousenearFORM userat line
预处理器Preprocessor预处理器在语法分析之后进行主要完成以下工作
1 表和列的存在性检查验证SQL语句中引用的表和列是否存在-- 如果表不存在mysqlSELECT*FROMnonexistent_table;ERROR1146(42S
:Tabledatabase.nonexistent_tabledoesnt exist -- 如果列不存在 mysql SELECT nonexistent_column FROM user; ERROR 1054 (42S
: Unknown column nonexistent_column in field list
2 权限检查验证当前用户是否有执行该SQL语句的权限-- 检查用户权限SHOWGRANTSFORusernamehost;
3 视图展开如果查询涉及视图预处理器会将视图定义展开为实际的SQL语句。
4 消除常量表达式预处理器会计算并简化常量表达式-- 原始SQLSELECT*FROMuserWHEREage182;-- 预处理后SELECT*FROMuserWHEREage20;
优化器Optimizer优化器是MySQL的核心组件之一负责生成最优的执行计划。
1 逻辑优化逻辑优化主要对查询进行等价变换包括子查询优化将子查询转换为JOIN外连接消除将外连接转换为内连接如果可能条件简化简化WHERE条件常量传播传播常量值示例-- 原始查询子查询SELECT*FROMuserWHEREidIN(SELECTuser_idFROMordersWHEREamount
;-- 优化后转换为JOINSELECTDISTINCTuser.*FROMuserINNERJOINordersONuser.idorders.user_idWHEREorders.amount100;
2 物理优化物理优化选择最优的访问路径和连接顺序索引选择决定使用哪个索引表连接顺序决定多表连接的顺序访问方法选择全表扫描、索引扫描、索引范围扫描等索引选择示例-- 假设有索引idx_age, idx_name_ageSELECT*FROMuserWHEREage20ANDnameTom;-- 优化器会选择选择性更高的索引
3 成本估算优化器基于统计信息估算不同执行计划的成本I/O成本读取数据页的数量CPU成本CPU计算资源消耗网络成本数据传输成本分布式场景-- 查看表的统计信息SHOWTABLESTATUSLIKEuser;-- 查看索引的基数SHOWINDEXFROMuser;-- 查看执行计划EXPLAINSELECT*FROMuserWHEREage20;
4 执行计划生成优化器最终生成一个执行计划包含访问类型ALL全表扫描、index索引扫描、range范围扫描、ref索引查找、const常量查找等使用的索引实际使用的索引扫描行数预估需要扫描的行数连接顺序多表连接的顺序-- 查看详细执行计划EXPLAINFORMATJSONSELECT*FROMuserWHEREage20;-- 查看实际执行情况EXPLAINANALYZESELECT*FROMuserWHEREage20;-- MySQL
8.
0.
执行器Executor执行器根据优化器生成的执行计划调用存储引擎的接口执行查询。
1 执行流程调用存储引擎接口执行器通过Handler API与存储引擎交互获取数据根据执行计划逐行获取数据条件过滤在Server层进行WHERE条件过滤部分条件下推到存储引擎排序和聚合如果需要执行ORDER BY、GROUP BY等操作返回结果将结果返回给客户端
2 交互过程执行器与存储引擎的典型交互执行器: 请打开user表 存储引擎: 已打开准备好读取 执行器: 请读取第一行 存储引擎: 返回第一行数据 执行器: 满足WHERE条件吗 执行器: 满足加入结果集 执行器: 请读取下一行 存储引擎: 返回下一行数据 ...循环直到结束
3 执行器的工作-- 示例查询SELECT*FROMuserWHEREage20;-- 执行器的工作流程
打开user表调用handler::open_table
调用存储引擎接口读取第一行handler::rnd_next
判断age20是否成立
如果成立将数据加入结果集
继续读取下一行直到表结束
返回结果集给客户端完整执行流程图客户端 │ │ ① 发送SQL语句 ▼ 连接器 │ │ ② 建立连接、认证 ▼ 分析器 │ │ ③ 词法分析分解Token │ │ ④ 语法分析构建语法树 ▼ 预处理器 │ │ ⑤ 表/列存在性检查 │ ⑥ 权限检查 │ ⑦ 视图展开 │ ⑧ 常量表达式消除 ▼ 优化器 │ │ ⑨ 逻辑优化子查询转换等 │ ⑩ 物理优化索引选择、连接顺序 │ ⑪ 成本估算 │ ⑫ 生成执行计划 ▼ 执行器 │ │ ⑬ 调用存储引擎接口 │ ⑭ 逐行读取数据 │ ⑮ 条件过滤 │ ⑯ 排序、聚合等操作 ▼ 存储引擎 │ │ ⑰ 实际数据读取 │ ⑱ 返回数据行 ▼ 执行器 │ │ ⑲ 组装结果集 ▼ 客户端 │ │ ⑳ 返回查询结果不同SQL语句的执行特点SELECT查询经过完整的分析、预处理、优化、执行流程可能使用查询缓存MySQL
0之前优化器会重点优化访问路径和连接顺序INSERT/UPDATE/DELETE同样经过完整的分析和优化流程需要检查约束主键、外键、唯一索引等涉及事务处理ACID记录binlog用于主从复制DDL语句CREATE/ALTER/DROP经过词法分析和语法分析预处理器检查表/对象是否存在需要特殊的元数据锁MDL操作完成后更新表缓存执行计划详解EXPLAIN输出字段说明EXPLAINSELECT*FROMuserWHEREage20;字段说明idSELECT标识符select_typeSELECT类型SIMPLE、PRIMARY、SUBQUERY等table访问的表名partitions匹配的分区type访问类型const、ref、range、index、ALL等possible_keys可能使用的索引key实际使用的索引key_len使用的索引长度ref索引比较的列rows预估扫描行数filtered过滤后的行比例Extra额外信息访问类型type性能排序从好到差system表中只有一行const主键或唯一索引等值查询eq_ref连接时使用主键或唯一索引ref非唯一索引等值查询fulltext全文索引ref_or_nullref IS NULLindex_merge索引合并unique_subquery子查询使用唯一索引index_subquery子查询使用非唯一索引range索引范围扫描index索引全扫描ALL全表扫描优化建议
索引优化-- 为常用查询条件创建索引CREATEINDEXidx_ageONuser(age);CREATEINDEXidx_name_ageONuser(name,age);-- 避免在索引列上使用函数SELECT*FROMuserWHEREYEAR(create_time)2023;-- 不使用索引SELECT*FROMuserWHEREcreate_time
ANDcreate_time
;-- 使用索引
SQL语句优化-- 避免SELECT *SELECTid,nameFROMuser;-- 只查询需要的列-- 避免子查询使用JOINSELECT*FROMuserWHEREidIN(SELECTuser_idFROMorders);-- 子查询SELECTDISTINCTuser.*FROMuserINNERJOINordersONuser.idorders.user_id;-- JOIN-- 合理使用LIMITSELECT*FROMuserLIMIT1000,10;-- 深分页性能差SELECT*FROMuserWHEREid1000LIMIT10;-- 使用上一页最后ID
查看执行计划-- 使用EXPLAIN分析查询EXPLAINSELECT*FROMuserWHEREage20;-- 使用EXPLAIN ANALYZE查看实际执行情况MySQL
8.
18EXPLAINANALYZESELECT*FROMuserWHEREage20;-- 使用SHOW PROFILE分析执行时间SETprofiling1;SELECT*FROMuserWHEREage20;SHOWPROFILE;
总结MySQL SQL语句的执行过程是一个复杂而精密的流程连接器建立连接并验证权限分析器进行词法分析和语法分析构建语法树预处理器检查表/列存在性、验证权限、展开视图优化器进行逻辑优化和物理优化生成最优执行计划执行器根据执行计划调用存储引擎接口执行查询理解这个执行过程对于编写高效的SQL语句、进行性能调优具有重要意义。
通过使用EXPLAIN命令分析执行计划可以更好地理解MySQL如何执行我们的SQL语句从而进行针对性的优化。
参考资料MySQL官方文档https://dev.mysql.com/doc/《高性能MySQL》《MySQL技术内幕SQL编程》《数据库系统概念》