1.MySql基础架构之SQL语句的执行

1.MySQL数据库的整体架构

（i）连接器：连接器负责与客户端建立连接，获取权限、维持和管理连接。连接命令中的mysql是客户端工具，用来建立服务端连接。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 showprocesslist 命令中看到它。文本中这个图是 show processlist 的结果，其中的Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout控制的，默认值是 8 小时。

MySql连接数量的限制

MySQL服务支持的最大连接时由max_connection参数控制的，超过这个数值系统就会拒绝接下来的所有请求，并报错"Too many connections"

MySql长短连接问题

MySQL也和Http一样，有短连接和长连接

短连接概念：

连接mysql服务（TCP三次握手）

执行sql

断开mysql服务（TCP四次挥手）

长连接概念：

连接mysql服务（TCP三次握手）

执行sql

...

断开mysql服务（TCP四次挥手）

长连接占用内存过多的问题怎么解决

①定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

②如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验但是会将连接恢复到刚刚创建完时的状态。

连接器内容总结

与客户端进行TCP三次握手连接
校验客户端用户名和密码，如果用户名和密码不对，就会报错
如果用户名和密码都对了，会读取该用户的权限，然后后面的逻辑判断都会基于此权限

2.查询缓存

连接建立完成后，你就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存。MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但是大部分时候，不建议使用缓存。大多时候查询缓存弊大于利

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

3.解析器

（i）词法分析，MySQL会根据你输入的字符串识别出关键字出来

（ii）语法分析，会根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法，如果没有问题就会构建SQL语法树，这样方便后面的模块获取SQL类型、表名，字段名，where条件

4.执行SQL

经过解析器后，就要进行执行SQL查询语句的流程了，每条SELECT查询语句可以分为以下三个阶段

prepare阶段，也就是预处理阶段
optimize阶段，也就是优化阶段
execute阶段，也就是执行阶段

①预处理阶段

检查SQL查询语句中的表或者字段是否存在
将select *中的*,扩展到所有列

②优化器阶段

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2里面 d 的值是否等于 20。

也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

③执行器阶段

经历完优化器后，就确定了执行方案，接下来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的函数，所以当调用该函数的时候，执行器就退出循环，也就结束查询了。

全表扫描

eg:select * from product where name='phone'

这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为ALL进行查询，也就是全表扫描这种方式查询，那么这时执行器与存储引擎的执行流程是这样的：

执行器第一次查询，会调用read_first_record函数指针指向的函数，因为优化器选择的访问类型为ALL，这个函数指针被指向InnoDB引擎全扫描接口，让存储引擎读取表中的第一条记录;
执行器会判断读到的这条记录的name是不是iphone，如果不是则跳过；如果是则将记录发给客户（Server层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的是所有记录，是因为客户端在查询完成之后才会显示）
执行器查询的过程是一个while循环，所以还会再查一次，会调用read_record函数指针指向的函数，因为优化器选择的访问类型为all,read_record函数指针指向的还是InnoDB引擎扫描的接口，所以接着向存储引擎层要求继续读刚刚读到的那条记录的下一条记录，存储记录把下一条记录取出后就将其返回给执行器(Server层)，执行器继续判断条件，不符合查询条件的即跳过该记录，否则发送到客户端
一直重复上述过程，直到存储引擎把表中的记录全部读完，然后向执行器（Server层）返回了读取完毕的信息
执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询

索引下推

索引下推能够减少二级索引在查询时的回表操作，提高查询效率，因为它将Server层负责的事情交给了存储引擎去处理了