开篇钩子SELECT * FROM t_user WHERE id 1;这条你写过一万次的 SQL从敲下回车到看到结果MySQL 内部走了 6 道关卡。说不出来就说明你只会用、不懂它。很多人在面试中被问到MySQL 的架构时只能答出有存储引擎层却说不清楚一条查询在 Server 层内部究竟经历了什么。本篇把这条路完整走一遍让你从此对每一行 SQL 都有透视能力。1. 全景图一条 SQL 的六道关卡在正式讲每个组件之前先建立整体坐标系。MySQL 的架构分为两层Server 层连接器 → 查询缓存 → 分析器 → 优化器 → 执行器存储引擎层InnoDB、MyISAM、Memory 等可插拔Server 层负责怎么查存储引擎层负责从哪拿。这条分界线贯穿整个专栏后面讲事务、锁、MVCC 的时候你会反复回到这张图。TCP 连接 / Unix Socket缓存命中 → 直接返回缓存未命中读写数据页️ 客户端 连接器验证身份、管理连接、加载权限⚡ 查询缓存5.7 默认关闭8.0 已删除 分析器词法分析 语法分析 优化器选索引、定 join 顺序、生成执行计划⚙️ 执行器按执行计划调用存储引擎接口️ InnoDB 存储引擎Buffer Pool、磁盘 IO2. 连接器你和 MySQL 之间的第一道门连接器负责与客户端建立 TCP 三次握手然后做两件事身份验证和权限加载。身份验证使用的是mysql.user表中存储的加密密码。验证通过后连接器会把该用户拥有的权限读取到内存中缓存起来。这个缓存有一个重要的副作用在连接存续期间即使管理员用GRANT修改了权限也不会影响已有的连接必须断开重连才能生效。这个行为在线上授权变更时很容易踩坑务必记住。连接建立后如果你用SHOW PROCESSLIST查看会看到两种主要状态Sleep连接空闲等待客户端发命令。Query正在执行某条 SQL。SHOWPROCESSLIST;-- 输出示例:-- Id | User | db | Command | Time | State | Info-- 5 | root | shop | Sleep | 120 | NULL | NULL-- 6 | root | shop | Query | 0 | init | SHOW PROCESSLIST长连接的内存问题wait_timeout默认 8 小时控制空闲连接的超时时间。如果应用侧没有连接池或者连接池配置的maxIdleTime大于这个值客户端就会在连接池里持有一个已被 MySQL 服务端单方面关闭的连接下次使用时报MySQL server has gone away。更隐蔽的问题是内存泄漏执行过大查询的长连接会在服务端积累大量内存久了可能导致 OOM。5.7 提供了mysql_reset_connection可以在不断开连接的前提下重置连接状态释放内存适合在连接池中定期调用。3. 查询缓存5.7 还有8.0 已删连接器之后MySQL 会判断当前 SQL 是不是SELECT如果是就去查询缓存。命中则直接返回结果不走后续流程。听起来很美但实际上查询缓存在绝大多数场景下是负优化缓存 key 是完整的 SQL 字符串包括大小写、空格SELECT * FROM t_user和select * from t_user是两条不同的缓存 key。任何对该表的写操作都会使该表所有缓存失效。对于写多读少的 OLTP 业务缓存命中率接近于零但每次写入都要额外做缓存失效操作纯粹是负担。分析型大查询结果集大缓存本身就占很多内存。5.7 的query_cache_type默认是OFF官方其实已经在暗示不要用。8.0 直接删除了这个功能。-- 5.7 查看查询缓存状态SHOWVARIABLESLIKEquery_cache%;-- query_cache_type OFF (5.7 默认)-- query_cache_size 1048576 (默认 1MB但 typeOFF 时不生效)结论5.7 不要开查询缓存也不要以为它在默默帮你。4. 分析器词法分析 语法分析查询缓存未命中或已关闭后SQL 进入分析器。分析器做两件事① 词法分析把 SQL 字符串拆成一个个 token。例如SELECT * FROM t_user WHERE id 1会被拆成SELECT关键字、*通配符、FROM关键字、t_user表名标识符、WHERE关键字、id列名、运算符、1数值常量。② 语法分析根据 token 序列按照 MySQL 的 SQL 语法规则构建一棵语法树AST。如果 SQL 写错了就在这里报错。报错信息里的near xxx是定位语法错误的关键线索。-- 故意写一个语法错误观察报错SELECT*FORM t_user;-- ERROR 1064 (42000): You have an error in your SQL syntax;-- check the manual that corresponds to your MySQL server version-- for the right syntax to use near FORM t_user at line 1-- 注意near 后面的 FORM t_user 告诉你错误从 FORM 这个 token 开始分析器不关心表名、列名是否真实存在那是执行器的事它只检查语法合法性。5. 优化器选索引、定 join 顺序语法树构建完成后进入优化器。优化器是 MySQL 里最神秘的组件——它会在多个可选的执行方案里选出成本最低的那一个。优化器做的核心决策选用哪个索引如果 WHERE 子句可以用多个索引优化器会估算每个索引的扫描代价选最小的。多表 join 时的连接顺序FROM a JOIN b JOIN c有6种排列顺序优化器会选它认为最优的。子查询的改写把某些相关子查询改写成 JOIN提升执行效率。成本估算的基础行数统计信息information_schema.STATISTICS、SHOW TABLE STATUS的rows字段、索引区分度Cardinality、以及页数估算。这些统计信息是采样得来的不是精确值所以优化器有时候会选错索引。这个话题在第 7 篇optimizer_trace里会深入讲。一个常见误解很多人以为优化器会自动优化任何写法实际上优化器只能在 SQL 语义不变的前提下做有限的变换。写得足够差的 SQL优化器救不了你。6. 执行器按计划逐行取数据优化器输出执行计划后执行器负责按计划调用存储引擎的接口逐行取数据。以SELECT * FROM t_user WHERE age 25为例假设age列上没有索引执行器调用 InnoDB 的取第一行接口。InnoDB 返回第一行执行器判断age是否等于 25。如果不满足跳过满足加入结果集。执行器调用取下一行接口重复上述过程直到 InnoDB 返回没有更多行了。如果表上有索引执行器会调用从索引起点取第一条满足条件的行接口减少扫描量。EXPLAIN的rows是估算值是优化器在生成执行计划时估算的扫描行数不是实际扫描行数。如果你想知道真实扫描了多少行要看Handler_read_rnd_next全表扫描时或Handler_read_key通过索引定位时等 Handler 状态变量。-- 用 Handler 状态变量观察真实扫描行数FLUSHSTATUS;SELECT*FROMt_userWHEREage25;-- age 列无索引全表扫SHOWSTATUSLIKEHandler_read%;-- Handler_read_rnd_next: 扫描了 N 次下一行等于全表行数-- Handler_read_first: 1 从头开始扫FLUSHSTATUS;SELECT*FROMt_userWHEREcity上海;-- 命中 idx_city_age_nameSHOWSTATUSLIKEHandler_read%;-- Handler_read_key: 1 通过索引 key 精确定位-- Handler_read_next: N 顺序扫描索引叶子节点7. 动手实验从 Handler 计数器看执行器行为-- 建库建表全专栏共用DROPDATABASEIFEXISTSshop;CREATEDATABASEshopDEFAULTCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci;USEshop;CREATETABLEt_user(idint(11)NOTNULLAUTO_INCREMENT,namevarchar(32)NOTNULLDEFAULT,agetinyint(4)NOTNULLDEFAULT0,cityvarchar(32)NOTNULLDEFAULT,phonevarchar(16)NOTNULLDEFAULT,created_atdatetimeNOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_city_age_name(city,age,name),KEYidx_phone(phone))ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTOt_user(id,name,age,city,phone)VALUES(1,张三,18,北京,13800000001),(2,李四,22,北京,13800000002),(3,王五,25,上海,13800000003),(4,赵六,25,上海,13800000004),(5,钱七,30,广州,13800000005),(6,孙八,35,深圳,13800000006);-- 实验1无索引 vs 有索引的 Handler 计数对比FLUSHSTATUS;SELECT*FROMt_userWHEREage25;SHOWSTATUSLIKEHandler_read%;-- 预期: Handler_read_rnd_next 76行 1次EOFFLUSHSTATUS;SELECT*FROMt_userWHEREcity上海;SHOWSTATUSLIKEHandler_read%;-- 预期: Handler_read_key 1, Handler_read_next 2上海有2条8. 连接权限的一个陷阱执行器在第一次访问某张表时会检查当前连接缓存的权限连接建立时加载的。如果权限不足报Access denied。注意执行器检查的是表级权限列级权限检查发生在更细粒度的场景。这意味着GRANT SELECT ON shop.* TO user%之后已有的旧连接因为权限是登录时就缓存在连接里的看不到这次变更FLUSH PRIVILEGES只重载全局权限表不会刷新已建立连接里的缓存——这与很多人的直觉相反。变更权限后要求相关账号重新连接才能保证生效。9. 一句话结论Server 层负责怎么查存储引擎层负责从哪拿。六道关卡连接器→缓存→分析器→优化器→执行器→引擎就是一条 SELECT 的完整生命周期。10. 5.7 vs 8.0 差异速查特性MySQL 5.7MySQL 8.0查询缓存存在默认 OFF彻底删除默认字符集latin1服务端utf8mb4SHOW PROCESSLIST信息源information_schema.PROCESSLIST同左但新增performance_schema.processlistEXPLAIN格式TRADITIONAL、JSON新增TREE、ANALYZE权限管理基于mysql.user表新增 Roles角色
)
