学习 SQL 的时候大家肯定第一个先学到的就是 select 查询语句了比如下面这句查询语句// 在 product 表中查询 id 1 的记录 select * from product where id 1;但是有没有想过MySQL 执行一条 select 查询语句在 MySQL 中期间发生了什么带着这个问题我们可以很好的了解 MySQL 内部的架构所以这次小林就带大家拆解一下 MySQL 内部的结构看看内部里的每一个“零件”具体是负责做什么的。#MySQL 执行流程是怎样的先来一个上帝视角图下面就是 MySQL 执行一条 SQL 查询语句的流程也从图中可以看到 MySQL 内部架构里的各个功能模块。可以看到 MySQL 的架构共分为两层Server 层和存储引擎层Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现主要包括连接器查询缓存、解析器、预处理器、优化器、执行器等。另外所有的内置函数如日期、时间、数学和加密函数等和所有跨存储引擎的功能如存储过程、触发器、视图等。都在 Server 层实现。存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB从 MySQL 5.5 版本开始 InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构就是由存储引擎层实现的不同的存储引擎支持的索引类型也不相同比如 InnoDB 支持索引类型是 B树 且是默认使用也就是说在数据表中创建的主键索引和二级索引默认使用的是 B 树索引。好了现在我们对 Server 层和存储引擎层有了一个简单认识接下来就详细说一条 SQL 查询语句的执行流程依次看看每一个功能模块的作用。#第一步连接器如果你在 Linux 操作系统里要使用 MySQL那你第一步肯定是要先连接 MySQL 服务然后才能执行 SQL 语句普遍我们都是使用下面这条命令进行连接# -h 指定 MySQL 服务得 IP 地址如果是连接本地的 MySQL服务可以不用这个参数 # -u 指定用户名管理员角色名为 root # -p 指定密码如果命令行中不填写密码为了密码安全建议不要在命令行写密码就需要在交互对话里面输入密码 mysql -h$ip -u$user -p连接的过程需要先经过 TCP 三次握手因为 MySQL 是基于 TCP 协议进行传输的如果 MySQL 服务并没有启动则会收到如下的报错如果 MySQL 服务正常运行完成 TCP 连接的建立后连接器就要开始验证你的用户名和密码如果用户名或密码不对就收到一个Access denied for user的错误然后客户端程序结束执行。如果用户密码都没有问题连接器就会获取该用户的权限然后保存起来后续该用户在此连接里的任何操作都会基于连接开始时读到的权限进行权限逻辑的判断。所以如果一个用户已经建立了连接即使管理员中途修改了该用户的权限也不会影响已经存在连接的权限。修改完成后只有再新建的连接才会使用新的权限设置。如何查看 MySQL 服务被多少个客户端连接了如果你想知道当前 MySQL 服务被多少个客户端连接了你可以执行show processlist命令进行查看。比如上图的显示结果共有两个用户名为 root 的用户连接了 MySQL 服务其中 id 为 6 的用户的 Command 列的状态为Sleep这意味着该用户连接完 MySQL 服务就没有再执行过任何命令也就是说这是一个空闲的连接并且空闲的时长是 736 秒 Time 列。空闲连接会一直占用着吗当然不是了MySQL 定义了空闲连接的最大空闲时长由wait_timeout参数控制的默认值是 8 小时28880秒如果空闲连接超过了这个时间连接器就会自动将它断开。mysql show variables like wait_timeout; ---------------------- | Variable_name | Value | ---------------------- | wait_timeout | 28800 | ---------------------- 1 row in set (0.00 sec)当然我们自己也可以手动断开空闲的连接使用的是 kill connection id 的命令。mysql kill connection 6; Query OK, 0 rows affected (0.00 sec)一个处于空闲状态的连接被服务端主动断开后这个客户端并不会马上知道等到客户端在发起下一个请求的时候才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。MySQL 的连接数有限制吗MySQL 服务支持的最大连接数由 max_connections 参数控制比如我的 MySQL 服务默认是 151 个,超过这个值系统就会拒绝接下来的连接请求并报错提示“Too many connections”。mysql show variables like max_connections; ------------------------ | Variable_name | Value | ------------------------ | max_connections | 151 | ------------------------ 1 row in set (0.00 sec)MySQL 的连接也跟 HTTP 一样有短连接和长连接的概念它们的区别如下// 短连接 连接 mysql 服务TCP 三次握手 执行sql 断开 mysql 服务TCP 四次挥手 // 长连接 连接 mysql 服务TCP 三次握手 执行sql 执行sql 执行sql .... 断开 mysql 服务TCP 四次挥手可以看到使用长连接的好处就是可以减少建立连接和断开连接的过程所以一般是推荐使用长连接。但是使用长连接后可能会占用内存增多因为 MySQL 在执行查询过程中临时使用内存管理连接对象这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多将导致 MySQL 服务占用内存太大有可能会被系统强制杀掉这样会发生 MySQL 服务异常重启的现象。怎么解决长连接占用内存的问题有两种解决方式。第一种定期断开长连接。既然断开连接后就会释放连接占用的内存资源那么我们可以定期断开长连接。第二种客户端主动重置连接。MySQL 5.7 版本实现了mysql_reset_connection()函数的接口注意这是接口函数不是命令那么当客户端执行了一个很大的操作后在代码里调用 mysql_reset_connection 函数来重置连接达到释放内存的效果。这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态。至此连接器的工作做完了简单总结一下与客户端进行 TCP 三次握手建立连接校验客户端的用户名和密码如果用户名或密码不对则会报错如果用户名和密码都对了会读取该用户的权限然后后面的权限逻辑判断都基于此时读取到的权限#第二步查询缓存连接器的工作完成后客户端就可以向 MySQL 服务发送 SQL 语句了MySQL 服务收到 SQL 语句后就会解析出 SQL 语句的第一个字段看看是什么类型的语句。如果 SQL 是查询语句select 语句MySQL 就会先去查询缓存 Query Cache 里查找缓存数据看看之前有没有执行过这一条命令这个查询缓存是以 key-value 形式保存在内存中的key 为 SQL 查询语句value 为 SQL 语句查询的结果。如果查询的语句命中查询缓存那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中那么就要往下继续执行等执行完后查询的结果就会被存入查询缓存中。这么看查询缓存还挺有用但是其实查询缓存挺鸡肋的。对于更新比较频繁的表查询缓存的命中率很低的因为只要一个表有更新操作那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据还没被使用的时候刚好这个表有更新操作查询缓冲就被清空了相当于缓存了个寂寞。所以MySQL 8.0 版本直接将查询缓存删掉了也就是说 MySQL 8.0 开始执行一条 SQL 查询语句不会再走到查询缓存这个阶段了。对于 MySQL 8.0 之前的版本如果想关闭查询缓存我们可以通过将参数 query_cache_type 设置成 DEMAND。TIP这里说的查询缓存是 server 层的也就是 MySQL 8.0 版本移除的是 server 层的查询缓存并不是 Innodb 存储引擎中的 buffer pool。#第三步解析 SQL在正式执行 SQL 查询语句之前 MySQL 会先对 SQL 语句做解析这个工作交由「解析器」来完成。#解析器解析器会做如下两件事情。第一件事情词法分析。MySQL 会根据你输入的字符串识别出关键字出来例如SQL语句 select username from userinfo在分析之后会得到4个Token其中有2个Keyword分别为select和from关键字非关键字关键字非关键字selectusernamefromuserinfo第二件事情语法分析。根据词法分析的结果语法解析器会根据语法规则判断你输入的这个 SQL 语句是否满足 MySQL 语法如果没问题就会构建出 SQL 语法树这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。如果我们输入的 SQL 语句语法不对就会在解析器这个阶段报错。比如我下面这条查询语句把 from 写成了 form这时 MySQL 解析器就会给报错。但是注意表不存在或者字段不存在并不是在解析器里做的《MySQL 45 讲》说是在解析器做的但是经过我和朋友看 MySQL 源码5.7和8.0得出结论是解析器只负责检查语法和构建语法树但是不会去查表或者字段存不存在。那到底谁来做检测表和字段是否存在的工作呢别急接下来就是了。#第四步执行 SQL经过解析器后接着就要进入执行 SQL 查询语句的流程了每条SELECT查询语句流程主要可以分为下面这三个阶段prepare 阶段也就是预处理阶段optimize 阶段也就是优化阶段execute 阶段也就是执行阶段#预处理器我们先来说说预处理阶段做了什么事情。检查 SQL 查询语句中的表或者字段是否存在将select *中的*符号扩展为表上的所有列我下面这条查询语句test 这张表是不存在的这时 MySQL 就会在执行 SQL 查询语句的 prepare 阶段中报错。mysql select * from test; ERROR 1146 (42S02): Table mysql.test doesnt exist这里贴个 MySQL 8.0 源码来证明表或字段是否存在的判断不是在解析器里做的而是在 prepare 阶段。PS下图是公众号「一树一溪」老哥帮我分析的这位老哥专门写 MySQL 源码文章感兴趣的朋友可以微信搜索关注上面的中间部分是 MySQL 报错表不存在时的函数调用栈可以看到表不存在的错误是在get_table_share() 函数里报错的而这个函数是在 prepare 阶段调用的。不过对于 MySQL 5.7 判断表或字段是否存在的工作是在词法分析语法分析之后prepare 阶段之前做的。结论都一样不是在解析器里做的。代码我就不放了正因为 MySQL 5.7 代码结构不好所以 MySQL 8.0 代码结构变化很大后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了。#优化器经过预处理阶段后还需要为 SQL 查询语句先制定一个执行计划这个工作交由「优化器」来完成的。优化器主要负责将 SQL 查询语句的执行方案确定下来比如在表里面有多个索引的时候优化器会基于查询成本的考虑来决定选择使用哪个索引。当然我们本次的查询语句select * from product where id 1很简单就是选择使用主键索引。要想知道优化器选择了哪个索引我们可以在查询语句最前面加个explain命令这样就会输出这条 SQL 语句的执行计划然后执行计划中的 key 就表示执行过程中使用了哪个索引比如下图的 key 为PRIMARY就是使用了主键索引。如果查询语句的执行计划里的 key 为 null 说明没有使用索引那就会全表扫描type ALL这种查询扫描的方式是效率最低档次的如下图这张 product 表只有一个索引就是主键现在我在表中将 name 设置为普通索引二级索引。这时 product 表就有主键索引id和普通索引name。假设执行了这条查询语句select id from product where id 1 and name like i%;这条查询语句的结果既可以使用主键索引也可以使用普通索引但是执行的效率会不同。这时就需要优化器来决定使用哪个索引了。很显然这条查询语句是覆盖索引直接在二级索引就能查找到结果因为二级索引的 B 树的叶子节点的数据存储的是主键值就没必要在主键索引查找了因为查询主键索引的 B 树的成本会比查询二级索引的 B 的成本大优化器基于查询成本的考虑会选择查询代价小的普通索引。在下图中执行计划我们可以看到执行过程中使用了普通索引nameExta 为 Using index这就是表明使用了覆盖索引优化。#执行器经历完优化器后就确定了执行方案接下来 MySQL 就真正开始执行语句了这个工作是由「执行器」完成的。在执行的过程中执行器就会和存储引擎交互了交互是以记录为单位的。接下来用三种方式执行过程跟大家说一下执行器和存储引擎的交互过程PS 为了写好这一部分特地去看 MySQL 源码也是第一次看哈哈。主键索引查询全表扫描索引下推#主键索引查询以本文开头查询语句为例看看执行器是怎么工作的。select * from product where id 1;这条查询语句的查询条件用到了主键索引而且是等值查询同时主键 id 是唯一不会有 id 相同的记录所以优化器决定选用访问类型为 const 进行查询也就是使用主键索引查询一条记录那么执行器与存储引擎的执行流程是这样的执行器第一次查询会调用 read_first_record 函数指针指向的函数因为优化器选择的访问类型为 const这个函数指针被指向为 InnoDB 引擎索引查询的接口把条件id 1交给存储引擎让存储引擎定位符合条件的第一条记录。存储引擎通过主键索引的 B 树结构定位到 id 1的第一条记录如果记录是不存在的就会向执行器上报记录找不到的错误然后查询结束。如果记录是存在的就会将记录返回给执行器执行器从存储引擎读到记录后接着判断记录是否符合查询条件如果符合则发送给客户端如果不符合则跳过该记录。执行器查询的过程是一个 while 循环所以还会再查一次但是这次因为不是第一次查询了所以会调用 read_record 函数指针指向的函数因为优化器选择的访问类型为 const这个函数指针被指向为一个永远返回 - 1 的函数所以当调用该函数的时候执行器就退出循环也就是结束查询了。至此这个语句就执行完成了。#全表扫描举个全表扫描的例子select * from product where name iphone;这条查询语句的查询条件没有用到索引所以优化器决定选用访问类型为 ALL 进行查询也就是全表扫描的方式查询那么这时执行器与存储引擎的执行流程是这样的执行器第一次查询会调用 read_first_record 函数指针指向的函数因为优化器选择的访问类型为 all这个函数指针被指向为 InnoDB 引擎全扫描的接口让存储引擎读取表中的第一条记录执行器会判断读到的这条记录的 name 是不是 iphone如果不是则跳过如果是则将记录发给客户的是的没错Server 层每从存储引擎读到一条记录就会发送给客户端之所以客户端显示的时候是直接显示所有记录的是因为客户端是等查询语句查询完成后才会显示出所有的记录。执行器查询的过程是一个 while 循环所以还会再查一次会调用 read_record 函数指针指向的函数因为优化器选择的访问类型为 allread_record 函数指针指向的还是 InnoDB 引擎全扫描的接口所以接着向存储引擎层要求继续读刚才那条记录的下一条记录存储引擎把下一条记录取出后就将其返回给执行器Server层执行器继续判断条件不符合查询条件即跳过该记录否则发送到客户端一直重复上述过程直到存储引擎把表中的所有记录读完然后向执行器Server层 返回了读取完毕的信息执行器收到存储引擎报告的查询完毕的信息退出循环停止查询。至此这个语句就执行完成了。#索引下推在这部分非常适合讲索引下推MySQL 5.6 推出的查询优化策略这样大家能清楚的知道「下推」这个动作下推到了哪里。索引下推能够减少二级索引在查询时的回表操作提高查询的效率因为它将 Server 层部分负责的事情交给存储引擎层去处理了。举一个具体的例子方便大家理解这里一张用户表如下我对 age 和 reward 字段建立了联合索引agereward现在有下面这条查询语句select * from t_user where age 20 and reward 100000;联合索引当遇到范围查询 (、) 就会停止匹配也就是age 字段能用到联合索引但是 reward 字段则无法利用到索引。具体原因这里可以看这篇索引常见面试题(opens new window)那么不使用索引下推MySQL 5.6 之前的版本时执行器与存储引擎的执行流程是这样的Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录也就是定位到 age 20 的第一条记录存储引擎根据二级索引的 B 树快速定位到这条记录后获取主键值然后进行回表操作将完整的记录返回给 Server 层Server 层在判断该记录的 reward 是否等于 100000如果成立则将其发送给客户端否则跳过该记录接着继续向存储引擎索要下一条记录存储引擎在二级索引定位到记录后获取主键值然后回表操作将完整的记录返回给 Server 层如此往复直到存储引擎把表中的所有记录读完。可以看到没有索引下推的时候每查询到一条二级索引记录都要进行回表操作然后将记录返回给 Server接着 Server 再判断该记录的 reward 是否等于 100000。而使用索引下推后判断记录的 reward 是否等于 100000 的工作交给了存储引擎层过程如下 Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录也就是定位到 age 20 的第一条记录存储引擎定位到二级索引后先不执行回表操作而是先判断一下该索引中包含的列reward列的条件reward 是否等于 100000是否成立。如果条件不成立则直接跳过该二级索引。如果成立则执行回表操作将完成记录返回给 Server 层。Server 层在判断其他的查询条件本次查询没有其他条件是否成立如果成立则将其发送给客户端否则跳过该记录然后向存储引擎索要下一条记录。如此往复直到存储引擎把表中的所有记录读完。可以看到使用了索引下推后虽然 reward 列无法使用到联合索引但是因为它包含在联合索引agereward里所以直接在存储引擎过滤出满足 reward 100000 的记录后才去执行回表操作获取整个记录。相比于没有使用索引下推节省了很多回表操作。当你发现执行计划里的 Extra 部分显示了 “Using index condition”说明使用了索引下推。总结执行一条 SQL 查询语句期间发生了什么连接器建立连接管理连接、校验用户身份查询缓存查询语句如果命中查询缓存则直接返回否则继续往下执行。MySQL 8.0 已删除该模块解析 SQL通过解析器对 SQL 查询语句进行词法分析、语法分析然后构建语法树方便后续模块读取表名、字段、语句类型执行 SQL执行 SQL 共有三个阶段预处理阶段检查表或字段是否存在将select *中的*符号扩展为表上的所有列。优化阶段基于查询成本的考虑 选择查询成本最小的执行计划执行阶段根据执行计划执行 SQL 查询语句从存储引擎读取记录返回给客户端怎么样现在再看这张图是不是很清晰了。完参考资料《MySQL 45 讲》《MySQL是怎样运行的从根儿上理解MySQL》https://gohalo.me/post/mysql-executor.htmlhttp://www.iskm.org/mysql56/sql__executor_8cc_source.htmlhttps://tangocc.github.io/2018/10/11/mysql-sourcecode/
执行一条 select 语句，期间发生了什么？
学习 SQL 的时候大家肯定第一个先学到的就是 select 查询语句了比如下面这句查询语句// 在 product 表中查询 id 1 的记录 select * from product where id 1;但是有没有想过MySQL 执行一条 select 查询语句在 MySQL 中期间发生了什么带着这个问题我们可以很好的了解 MySQL 内部的架构所以这次小林就带大家拆解一下 MySQL 内部的结构看看内部里的每一个“零件”具体是负责做什么的。#MySQL 执行流程是怎样的先来一个上帝视角图下面就是 MySQL 执行一条 SQL 查询语句的流程也从图中可以看到 MySQL 内部架构里的各个功能模块。可以看到 MySQL 的架构共分为两层Server 层和存储引擎层Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现主要包括连接器查询缓存、解析器、预处理器、优化器、执行器等。另外所有的内置函数如日期、时间、数学和加密函数等和所有跨存储引擎的功能如存储过程、触发器、视图等。都在 Server 层实现。存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB从 MySQL 5.5 版本开始 InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构就是由存储引擎层实现的不同的存储引擎支持的索引类型也不相同比如 InnoDB 支持索引类型是 B树 且是默认使用也就是说在数据表中创建的主键索引和二级索引默认使用的是 B 树索引。好了现在我们对 Server 层和存储引擎层有了一个简单认识接下来就详细说一条 SQL 查询语句的执行流程依次看看每一个功能模块的作用。#第一步连接器如果你在 Linux 操作系统里要使用 MySQL那你第一步肯定是要先连接 MySQL 服务然后才能执行 SQL 语句普遍我们都是使用下面这条命令进行连接# -h 指定 MySQL 服务得 IP 地址如果是连接本地的 MySQL服务可以不用这个参数 # -u 指定用户名管理员角色名为 root # -p 指定密码如果命令行中不填写密码为了密码安全建议不要在命令行写密码就需要在交互对话里面输入密码 mysql -h$ip -u$user -p连接的过程需要先经过 TCP 三次握手因为 MySQL 是基于 TCP 协议进行传输的如果 MySQL 服务并没有启动则会收到如下的报错如果 MySQL 服务正常运行完成 TCP 连接的建立后连接器就要开始验证你的用户名和密码如果用户名或密码不对就收到一个Access denied for user的错误然后客户端程序结束执行。如果用户密码都没有问题连接器就会获取该用户的权限然后保存起来后续该用户在此连接里的任何操作都会基于连接开始时读到的权限进行权限逻辑的判断。所以如果一个用户已经建立了连接即使管理员中途修改了该用户的权限也不会影响已经存在连接的权限。修改完成后只有再新建的连接才会使用新的权限设置。如何查看 MySQL 服务被多少个客户端连接了如果你想知道当前 MySQL 服务被多少个客户端连接了你可以执行show processlist命令进行查看。比如上图的显示结果共有两个用户名为 root 的用户连接了 MySQL 服务其中 id 为 6 的用户的 Command 列的状态为Sleep这意味着该用户连接完 MySQL 服务就没有再执行过任何命令也就是说这是一个空闲的连接并且空闲的时长是 736 秒 Time 列。空闲连接会一直占用着吗当然不是了MySQL 定义了空闲连接的最大空闲时长由wait_timeout参数控制的默认值是 8 小时28880秒如果空闲连接超过了这个时间连接器就会自动将它断开。mysql show variables like wait_timeout; ---------------------- | Variable_name | Value | ---------------------- | wait_timeout | 28800 | ---------------------- 1 row in set (0.00 sec)当然我们自己也可以手动断开空闲的连接使用的是 kill connection id 的命令。mysql kill connection 6; Query OK, 0 rows affected (0.00 sec)一个处于空闲状态的连接被服务端主动断开后这个客户端并不会马上知道等到客户端在发起下一个请求的时候才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。MySQL 的连接数有限制吗MySQL 服务支持的最大连接数由 max_connections 参数控制比如我的 MySQL 服务默认是 151 个,超过这个值系统就会拒绝接下来的连接请求并报错提示“Too many connections”。mysql show variables like max_connections; ------------------------ | Variable_name | Value | ------------------------ | max_connections | 151 | ------------------------ 1 row in set (0.00 sec)MySQL 的连接也跟 HTTP 一样有短连接和长连接的概念它们的区别如下// 短连接 连接 mysql 服务TCP 三次握手 执行sql 断开 mysql 服务TCP 四次挥手 // 长连接 连接 mysql 服务TCP 三次握手 执行sql 执行sql 执行sql .... 断开 mysql 服务TCP 四次挥手可以看到使用长连接的好处就是可以减少建立连接和断开连接的过程所以一般是推荐使用长连接。但是使用长连接后可能会占用内存增多因为 MySQL 在执行查询过程中临时使用内存管理连接对象这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多将导致 MySQL 服务占用内存太大有可能会被系统强制杀掉这样会发生 MySQL 服务异常重启的现象。怎么解决长连接占用内存的问题有两种解决方式。第一种定期断开长连接。既然断开连接后就会释放连接占用的内存资源那么我们可以定期断开长连接。第二种客户端主动重置连接。MySQL 5.7 版本实现了mysql_reset_connection()函数的接口注意这是接口函数不是命令那么当客户端执行了一个很大的操作后在代码里调用 mysql_reset_connection 函数来重置连接达到释放内存的效果。这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态。至此连接器的工作做完了简单总结一下与客户端进行 TCP 三次握手建立连接校验客户端的用户名和密码如果用户名或密码不对则会报错如果用户名和密码都对了会读取该用户的权限然后后面的权限逻辑判断都基于此时读取到的权限#第二步查询缓存连接器的工作完成后客户端就可以向 MySQL 服务发送 SQL 语句了MySQL 服务收到 SQL 语句后就会解析出 SQL 语句的第一个字段看看是什么类型的语句。如果 SQL 是查询语句select 语句MySQL 就会先去查询缓存 Query Cache 里查找缓存数据看看之前有没有执行过这一条命令这个查询缓存是以 key-value 形式保存在内存中的key 为 SQL 查询语句value 为 SQL 语句查询的结果。如果查询的语句命中查询缓存那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中那么就要往下继续执行等执行完后查询的结果就会被存入查询缓存中。这么看查询缓存还挺有用但是其实查询缓存挺鸡肋的。对于更新比较频繁的表查询缓存的命中率很低的因为只要一个表有更新操作那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据还没被使用的时候刚好这个表有更新操作查询缓冲就被清空了相当于缓存了个寂寞。所以MySQL 8.0 版本直接将查询缓存删掉了也就是说 MySQL 8.0 开始执行一条 SQL 查询语句不会再走到查询缓存这个阶段了。对于 MySQL 8.0 之前的版本如果想关闭查询缓存我们可以通过将参数 query_cache_type 设置成 DEMAND。TIP这里说的查询缓存是 server 层的也就是 MySQL 8.0 版本移除的是 server 层的查询缓存并不是 Innodb 存储引擎中的 buffer pool。#第三步解析 SQL在正式执行 SQL 查询语句之前 MySQL 会先对 SQL 语句做解析这个工作交由「解析器」来完成。#解析器解析器会做如下两件事情。第一件事情词法分析。MySQL 会根据你输入的字符串识别出关键字出来例如SQL语句 select username from userinfo在分析之后会得到4个Token其中有2个Keyword分别为select和from关键字非关键字关键字非关键字selectusernamefromuserinfo第二件事情语法分析。根据词法分析的结果语法解析器会根据语法规则判断你输入的这个 SQL 语句是否满足 MySQL 语法如果没问题就会构建出 SQL 语法树这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。如果我们输入的 SQL 语句语法不对就会在解析器这个阶段报错。比如我下面这条查询语句把 from 写成了 form这时 MySQL 解析器就会给报错。但是注意表不存在或者字段不存在并不是在解析器里做的《MySQL 45 讲》说是在解析器做的但是经过我和朋友看 MySQL 源码5.7和8.0得出结论是解析器只负责检查语法和构建语法树但是不会去查表或者字段存不存在。那到底谁来做检测表和字段是否存在的工作呢别急接下来就是了。#第四步执行 SQL经过解析器后接着就要进入执行 SQL 查询语句的流程了每条SELECT查询语句流程主要可以分为下面这三个阶段prepare 阶段也就是预处理阶段optimize 阶段也就是优化阶段execute 阶段也就是执行阶段#预处理器我们先来说说预处理阶段做了什么事情。检查 SQL 查询语句中的表或者字段是否存在将select *中的*符号扩展为表上的所有列我下面这条查询语句test 这张表是不存在的这时 MySQL 就会在执行 SQL 查询语句的 prepare 阶段中报错。mysql select * from test; ERROR 1146 (42S02): Table mysql.test doesnt exist这里贴个 MySQL 8.0 源码来证明表或字段是否存在的判断不是在解析器里做的而是在 prepare 阶段。PS下图是公众号「一树一溪」老哥帮我分析的这位老哥专门写 MySQL 源码文章感兴趣的朋友可以微信搜索关注上面的中间部分是 MySQL 报错表不存在时的函数调用栈可以看到表不存在的错误是在get_table_share() 函数里报错的而这个函数是在 prepare 阶段调用的。不过对于 MySQL 5.7 判断表或字段是否存在的工作是在词法分析语法分析之后prepare 阶段之前做的。结论都一样不是在解析器里做的。代码我就不放了正因为 MySQL 5.7 代码结构不好所以 MySQL 8.0 代码结构变化很大后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了。#优化器经过预处理阶段后还需要为 SQL 查询语句先制定一个执行计划这个工作交由「优化器」来完成的。优化器主要负责将 SQL 查询语句的执行方案确定下来比如在表里面有多个索引的时候优化器会基于查询成本的考虑来决定选择使用哪个索引。当然我们本次的查询语句select * from product where id 1很简单就是选择使用主键索引。要想知道优化器选择了哪个索引我们可以在查询语句最前面加个explain命令这样就会输出这条 SQL 语句的执行计划然后执行计划中的 key 就表示执行过程中使用了哪个索引比如下图的 key 为PRIMARY就是使用了主键索引。如果查询语句的执行计划里的 key 为 null 说明没有使用索引那就会全表扫描type ALL这种查询扫描的方式是效率最低档次的如下图这张 product 表只有一个索引就是主键现在我在表中将 name 设置为普通索引二级索引。这时 product 表就有主键索引id和普通索引name。假设执行了这条查询语句select id from product where id 1 and name like i%;这条查询语句的结果既可以使用主键索引也可以使用普通索引但是执行的效率会不同。这时就需要优化器来决定使用哪个索引了。很显然这条查询语句是覆盖索引直接在二级索引就能查找到结果因为二级索引的 B 树的叶子节点的数据存储的是主键值就没必要在主键索引查找了因为查询主键索引的 B 树的成本会比查询二级索引的 B 的成本大优化器基于查询成本的考虑会选择查询代价小的普通索引。在下图中执行计划我们可以看到执行过程中使用了普通索引nameExta 为 Using index这就是表明使用了覆盖索引优化。#执行器经历完优化器后就确定了执行方案接下来 MySQL 就真正开始执行语句了这个工作是由「执行器」完成的。在执行的过程中执行器就会和存储引擎交互了交互是以记录为单位的。接下来用三种方式执行过程跟大家说一下执行器和存储引擎的交互过程PS 为了写好这一部分特地去看 MySQL 源码也是第一次看哈哈。主键索引查询全表扫描索引下推#主键索引查询以本文开头查询语句为例看看执行器是怎么工作的。select * from product where id 1;这条查询语句的查询条件用到了主键索引而且是等值查询同时主键 id 是唯一不会有 id 相同的记录所以优化器决定选用访问类型为 const 进行查询也就是使用主键索引查询一条记录那么执行器与存储引擎的执行流程是这样的执行器第一次查询会调用 read_first_record 函数指针指向的函数因为优化器选择的访问类型为 const这个函数指针被指向为 InnoDB 引擎索引查询的接口把条件id 1交给存储引擎让存储引擎定位符合条件的第一条记录。存储引擎通过主键索引的 B 树结构定位到 id 1的第一条记录如果记录是不存在的就会向执行器上报记录找不到的错误然后查询结束。如果记录是存在的就会将记录返回给执行器执行器从存储引擎读到记录后接着判断记录是否符合查询条件如果符合则发送给客户端如果不符合则跳过该记录。执行器查询的过程是一个 while 循环所以还会再查一次但是这次因为不是第一次查询了所以会调用 read_record 函数指针指向的函数因为优化器选择的访问类型为 const这个函数指针被指向为一个永远返回 - 1 的函数所以当调用该函数的时候执行器就退出循环也就是结束查询了。至此这个语句就执行完成了。#全表扫描举个全表扫描的例子select * from product where name iphone;这条查询语句的查询条件没有用到索引所以优化器决定选用访问类型为 ALL 进行查询也就是全表扫描的方式查询那么这时执行器与存储引擎的执行流程是这样的执行器第一次查询会调用 read_first_record 函数指针指向的函数因为优化器选择的访问类型为 all这个函数指针被指向为 InnoDB 引擎全扫描的接口让存储引擎读取表中的第一条记录执行器会判断读到的这条记录的 name 是不是 iphone如果不是则跳过如果是则将记录发给客户的是的没错Server 层每从存储引擎读到一条记录就会发送给客户端之所以客户端显示的时候是直接显示所有记录的是因为客户端是等查询语句查询完成后才会显示出所有的记录。执行器查询的过程是一个 while 循环所以还会再查一次会调用 read_record 函数指针指向的函数因为优化器选择的访问类型为 allread_record 函数指针指向的还是 InnoDB 引擎全扫描的接口所以接着向存储引擎层要求继续读刚才那条记录的下一条记录存储引擎把下一条记录取出后就将其返回给执行器Server层执行器继续判断条件不符合查询条件即跳过该记录否则发送到客户端一直重复上述过程直到存储引擎把表中的所有记录读完然后向执行器Server层 返回了读取完毕的信息执行器收到存储引擎报告的查询完毕的信息退出循环停止查询。至此这个语句就执行完成了。#索引下推在这部分非常适合讲索引下推MySQL 5.6 推出的查询优化策略这样大家能清楚的知道「下推」这个动作下推到了哪里。索引下推能够减少二级索引在查询时的回表操作提高查询的效率因为它将 Server 层部分负责的事情交给存储引擎层去处理了。举一个具体的例子方便大家理解这里一张用户表如下我对 age 和 reward 字段建立了联合索引agereward现在有下面这条查询语句select * from t_user where age 20 and reward 100000;联合索引当遇到范围查询 (、) 就会停止匹配也就是age 字段能用到联合索引但是 reward 字段则无法利用到索引。具体原因这里可以看这篇索引常见面试题(opens new window)那么不使用索引下推MySQL 5.6 之前的版本时执行器与存储引擎的执行流程是这样的Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录也就是定位到 age 20 的第一条记录存储引擎根据二级索引的 B 树快速定位到这条记录后获取主键值然后进行回表操作将完整的记录返回给 Server 层Server 层在判断该记录的 reward 是否等于 100000如果成立则将其发送给客户端否则跳过该记录接着继续向存储引擎索要下一条记录存储引擎在二级索引定位到记录后获取主键值然后回表操作将完整的记录返回给 Server 层如此往复直到存储引擎把表中的所有记录读完。可以看到没有索引下推的时候每查询到一条二级索引记录都要进行回表操作然后将记录返回给 Server接着 Server 再判断该记录的 reward 是否等于 100000。而使用索引下推后判断记录的 reward 是否等于 100000 的工作交给了存储引擎层过程如下 Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录也就是定位到 age 20 的第一条记录存储引擎定位到二级索引后先不执行回表操作而是先判断一下该索引中包含的列reward列的条件reward 是否等于 100000是否成立。如果条件不成立则直接跳过该二级索引。如果成立则执行回表操作将完成记录返回给 Server 层。Server 层在判断其他的查询条件本次查询没有其他条件是否成立如果成立则将其发送给客户端否则跳过该记录然后向存储引擎索要下一条记录。如此往复直到存储引擎把表中的所有记录读完。可以看到使用了索引下推后虽然 reward 列无法使用到联合索引但是因为它包含在联合索引agereward里所以直接在存储引擎过滤出满足 reward 100000 的记录后才去执行回表操作获取整个记录。相比于没有使用索引下推节省了很多回表操作。当你发现执行计划里的 Extra 部分显示了 “Using index condition”说明使用了索引下推。总结执行一条 SQL 查询语句期间发生了什么连接器建立连接管理连接、校验用户身份查询缓存查询语句如果命中查询缓存则直接返回否则继续往下执行。MySQL 8.0 已删除该模块解析 SQL通过解析器对 SQL 查询语句进行词法分析、语法分析然后构建语法树方便后续模块读取表名、字段、语句类型执行 SQL执行 SQL 共有三个阶段预处理阶段检查表或字段是否存在将select *中的*符号扩展为表上的所有列。优化阶段基于查询成本的考虑 选择查询成本最小的执行计划执行阶段根据执行计划执行 SQL 查询语句从存储引擎读取记录返回给客户端怎么样现在再看这张图是不是很清晰了。完参考资料《MySQL 45 讲》《MySQL是怎样运行的从根儿上理解MySQL》https://gohalo.me/post/mysql-executor.htmlhttp://www.iskm.org/mysql56/sql__executor_8cc_source.htmlhttps://tangocc.github.io/2018/10/11/mysql-sourcecode/
