执行一条 select 语句，期间发生了什么？

执行一条 select 语句，期间发生了什么？

语句如下：

 select * from product where ID = 1；

上帝视角图：

共分为两层：Server 层 和 存储引擎层

• Server 层：负责建立连接、分析和执行 SQL。mysql 大多数的核心功能都在这实现，主要包括连接器、查询缓存、解析器、预处理器、优化器、执行器。还包括一些内置函数和跨存储引擎的功能。

• 存储引擎层：负责数据的存储和提取。支持多个存储引擎，不同的存储引擎都是使用同一个 Server 层。

连接器

在 Linux 系统中使用 mysql，第一步是先连接mysql 服务，然后才能执行 sql 语句，命令如下：

 mysql -h$ip -u$user -p
 -h 指定mysql服务的ip地址，本地可不用设置
 -u 用户名
 -p 密码

MySQL 是基于 TCP 协议进行传输的，连接过程要经历三次握手，如果没有启动mysql 服务，会跑以下错误：

 can't connect to local MySQL server through socket ......

如果MySQL 服务正常，完成 TCP 连接建立后，连接器就会验证用户名和密码，如果密码不对：

 Access denied for user 'xxx'@'localhost'(using password : YES)

密码正确，连接器会获取登录用户的权限，然后保存起来，后续该用户在此连接的任何操作，都会基于连接开始时读取到的权限进行逻辑的判断。

所以一个用户已经建立了连接，之后即使管理员更改了该用户的权限，也不会影响已经存在的权限。修改后，只有重新建立连接才会使用新的权限。

怎么查看MySQL服务被多少个客户端连接？

 show processlist;

command 列显示为 sleep 的连接 为空闲连接，即代表该用户连接完服务后没有再执行过任何命令。

空闲连接会一直占用吗？

不会，Mysql 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制，默认是 8 个小时。如果空闲连接时间超过了这个时间，连接器会自动断开该连接。

也可手动断开连接：

 kill connection + id；

一个处于空闲状态的连接被服务端主动断开后，该客户端并没有感知，等到该客户端再次请求时，会收到报错：

ERROR 2013（HY000): Lost connection to MySLQL server during query

MySQL 的连接数有限制吗？

由 max_connections 参数控制，可以自己设定，如果超过设定的连接数，会报错：too many connections

MySQL 的连接和 HTTP 一样，有长连接和短连接的概念。

短连接为每次执行一条sql 就建立一个短连接，长连接可执行多个sql。

 // 短连接
 连接 mysql 服务（TCP 三次握手）
 执行sql
 断开 mysql 服务（TCP 四次挥手）
 ​
 // 长连接
 连接 mysql 服务（TCP 三次握手）
 执行sql
 执行sql
 执行sql
 ....
 断开 mysql 服务（TCP 四次挥手）

一般推荐使用长连接，可以减少建立和断开连接的时间，但同样也会存在长连接可能占用内存过多的问题。因为mysql 在执行查询过程中临时使用的内存来管理连接对象，这些连接只用对象资源断开连接是才会释放。累计过多，会导致mysql占用内存过大，可能会被 系统强制杀掉，此时可能出现异常重启现象。

如何解决长连接占用内存的问题？

• 定期断开长连接 ：

• 客户端主动重置连接：

总结一下连接器的作用

• 与客户端进行 TCP 三次握手建立连接

• 校验用户名和密码

• 读取用户权限，基于权限逻辑判断

查询缓存

连接器完成工作后，客户端就可以向 服务发送 sql 语句了，服务收到 sql 后，会解析出 sql 语句的第一个字段 看是什么类型的语句。

如果是 查询语句（select），mysql 会先去查询缓存里查找缓存数据，看之前有没有执行过该命令，有的话就直接返回缓存中的 value 数据，没有的话就要继续往下执行。这个查询缓存是以 查询的sql语句为 key，查询的结果为 value 进行存储的。

但是这是一个鸡肋的功能，因为在实际使用中，缓存的命中率是极低的，因为只要有一个表有更新操作，那么该表的查询缓存就会被清空。比如你刚缓存了一个结果很大的数据，但是你还没用的时候刚好该表有一个更新操作，那么你就瞎忙活了。

所以在 MySQL 8.0 版本开始，直接将这个查询缓存删除了（server层的，并不是Innodb的buffer pool），也就是说现在新版本的mysql 执行一个查询语句不会走到查询缓存那一步了。对于之前的版本可以使用 query_cache_type设置成DEMAND。

解析 SQL

解析器主要做两件事：

• 词法分析： MySQL 会根据输入的字符串识别出关键字，构建出 SQL 语法树，方便后面模块获取 SQL 类型，表名，字段名，where 条件等。

• 语法分析： 根据词法分析的结果，语法解析器会根据语法规则，判断输入的 SQL 语句是否满足 MySQL 语法。

如果我们写的 SQL 不对，在此阶段就会报错。错误信息为：

 you have an error in your SQL syntax
 且会给出具体错误的解释和行数

执行 SQL

经过了解析器，就真正的到达了 SQL 执行的地方了，每条 select 查询语句流程主要有以下三个阶段：

• prepare：预处理阶段

• optimize：优化阶段

• execute：执行阶段

预处理器

预处理做了什么

• 检查 SQL 查询语句中的表或者字段是否存在

• 将 select * 中的 * 符号，扩展为表上所的列

比如你查询了一个不存在的表，在 MySQL 执行查询语句的 prepare 阶段就会报错。

即表或字段是否存在的判断是在 prepare 阶段判断的，而不是在解析器里。

优化器

预处理阶段后，还需要为 sql 查询语句定制一个执行计划，这个工作由优化器来实现。优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里有多个索引时，优化器会基于查询成本的考虑到底使用哪个索引。

想知道优化器到底选择了哪个索引，可以使用 explain 命令，加在查询语句前面即可。

expalin 命令 desc

该命令同样可以分析sql语句的执行过程，比如一条sql为什么执行的那么慢。

• 通过 key 和 key_len 来判断是否命中了索引

• 通过type知道查看sql是否有进一步的优化空间，是否存在全索引扫描和全表扫描

• 通过extra建议判断，是否出现了回表的情况，如果有，可以尝试添加索引或者修改返回字段来修复

执行器

经历完优化器后，就已经确定了执行方案，接下来 MySQL 就要真正的执行语句了。在执行过程中，执行器就会和存储引擎进行交互了，交互是以记录为单位的。

接下来由有三种方式执行过程

• 主键索引查询

• 全表扫描

• 索引下推

主键索引查询

以此sql语句为例：

 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 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；

• 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；

• 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

索引下推

mysql 5.6 推出的查询优化策略。

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

举个栗子：

 有一个字段如下的表，且对 age 和 reward 字段建立了联合索引。
 id    name     age      reward
 ​
 有如下的查询语句
 select * from t_user where age > 20 and reward = 100000;

联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。

不使用索引下推时（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 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。

当你发现执行计划里的 Extr 部分显示了 “Using index condition”，说明使用了索引下推。

艹，图没了。。。。。。