一条Select 语句 的执行流程

MySQL执行流程

第一步: 连接器

如果在Linux操作系统里使用MySQL , 首先要连接MySQL服务

# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h$ip -u$user -p

MySQL是基于TCP协议进行传输的,连接过程需要先经过TCP三次握手,如果MySQL服务正常进行,完成TCP连接建立之后,连接器验证用户名和密码.如果用户名密码正确,连接器会获取用户的权限.后续该用户在此连接里的任何操作,都会基于连接开始时读到的权限进行逻辑的判断.

因此一个用户已经建立了连接,即使中途权限被修改了,也不会影响已经存在的连接权限.

mysql> show processlist;
+------+-----------------+---------------------+----------------+---------+---------+------------------------+------------------+
| Id   | User            | Host                | db             | Command | Time    | State                  | Info             |
+------+-----------------+---------------------+----------------+---------+---------+------------------------+------------------+
|    5 | event_scheduler | localhost           | NULL           | Daemon  | 1108032 | Waiting on empty queue | NULL             |
| 3384 | root            | 152.136.50.60:46618 | college        | Sleep   |   21577 |                        | NULL             |
| 3450 | root            | 152.136.50.60:51764 | lezhi_college  | Sleep   |    7487 |                        | NULL             |
| 3905 | root            | 42.233.2.106:16128  | lezhi_college2 | Sleep   |    7915 |                        | NULL             |
| 3919 | root            | 42.233.2.106:16620  | lezhi_college2 | Sleep   |    7915 |                        | NULL             |
| 3935 | root            | 42.233.2.106:17104  | lezhi_college2 | Sleep   |    7915 |                        | NULL             |
| 3998 | root            | localhost           | NULL           | Query   |       0 | init                   | show processlist |
+------+-----------------+---------------------+----------------+---------+---------+------------------------+------------------+
7 rows in set (0.00 sec)

空闲连接不会被一直占用,MySQL定义了空闲连接的最大空闲时长,由wait_timeout参数控制,默认值8小时.也可以手动断开空闲连接 使用 kill connection + id.

一个空闲连接被服务端主动断开之后,客户端不会马上知道,等待客户端发起下一个请求时,会收到报错信息.

mysql支持的最大连接数由 max_connections 参数控制,超过这个值,系统会拒绝接下来的连接请求.并报错"Too many connections".

 show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.02 sec)

mysql和http一样 , 有短链接和长连接的概念.区别如下:

# 短链接
连接mysql服务(TCP三次握手)
执行sql
断开mysql服务(TCP四次握手)

#长连接
连接 mysql服务 (TCP三次握手)
执行sql
执行sql
...
断开mysql服务(TCP四次握手)

使用长连接会占用内存增多,因为MySQL在执行查询过程中临时使用内存管理连接对象,这些连接对象资源只有在连接断开时才会释放.如果长连接过多,将导致MySQL服务占用内存太大,会发生MySQL异常重启现象. 有两种解决方式

1. 1. 定期断开长连接.
   2. 客户端主动重置连接. MySQL5.7实现了mysql_reset_connection() 函数的接口,当客户端执行了一个很大的操作后,在代码里调用 mysql_reset_connection 函数来重置连接,达到释放内存的效果.

第二步: 查询缓存(Server层)

连接器工作完成之后,客户端向MySQL服务发送SQL语句, MySQL服务收到SQL语句之后,就会解析出SQL语句的第一个字段.

如果是查询语句(select) , MySQL就会先去查询缓存里查询数据 , 看看之前有没有执行过这一条命令,这个查询缓存是以key-value形式保存在内存中的,key为SQL查询语句,value为查询结果.

如果查询的语句命中查询缓存,直接将value返回给客户端.如果查询的语句没有命中查询缓存,那么就向下执行,等执行完毕之后,查询结果就会被存入查询缓存中.

MySQL8版本之后,执行查询语句不在走查询缓存的阶段了.

第三步:解析SQL

在正式执行SQL查询语句之前,MySQL会先对SQL语句解析.由解析器完成

解析器

1. 词法分析.MySQL会根据输入的SQL语句识别出关键字 , 如 select username from tb_user;

关键字	非关键字	关键字	非关键字
select	username	from	tb_user

1. 语法分析. 根据词法分析的结果,语法分析器会根据语法规则,判断驶入的SQL语句是否满足MySQL语法,如果没有问题就会构建出SQL语法树 , 方便后面模块获取SQL类型,表名,字段名,where条件等.

表不存在或字段不存在不是在解析器里实现的.

第四步: 执行SQL

经过解析器后,接着就要进入执行SQL查询语句的流程了,每条SELECT查询语句流程主要分为以下阶段:

1. 1. prepare阶段:预处理阶段
   2. optimize阶段:优化阶段
   3. execute阶段:执行阶段

预处理器

1.检查SQL查询语句中的表或者字段是否存在.(MySQL8之后)

2.将select * 中的*符号扩展为表上的所有列.

优化器

经过预处理阶段之后,还需要为查询语句先制定一个执行计划.

优化器主要负责将SQL查询语句的执行方案确定,比如当表中存在多个索引时,优化器会基于查询成本考虑,来决定使用哪一个索引.

执行器

经历优化器之后,确定执行方案,由执行器执行语句. 在执行过程中,执行器会和存储引擎交互,交互是以记录为单位的.

主键索引查询:

select * from product where id = 1;

执行器第一次查询,会调用read_first_record函数指针指向的函数,因为优化器选择的访问类型时const,这个函数指针被指向为InnoDB引擎索引查询的接口,把条件 id = 1 交给存储引擎,让存储引擎定位符合条件的第一条记录.

存储引擎通过主键索引的B+树结构定位到id =1 的记录,如果记录不存在,查询结束.存在将记录返回给执行器.

执行器从存储引擎读到记录后,判断记录时候符合查询条件,符合发送给客户端,不符合则跳过.

执行器查询的过程是一个while循环,所以还会再查询一次,但因为不是第一次查询,所以会调用read_record函数指针指向的函数.因为优化器访问类型为const,这个函数指针会被指向为一个永远返回-1的函数.所以当执行该函数时,结束循环.

select * from t_user  where age > 20 and reward = 100000; #age和reward 联合索引

联合索引遇到范围查询(> , < )就会停止匹配,因此只有age才能用到联合索引.

不使用索引下推:(MySQL5.6之前):

Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录,即 age>20 的第一条记录

存储引擎根据二级索引的B+树快速定位到这条记录,获取主键值,然后进行回表操作,将完整的记录返回个Server层;

Server层判断该记录的reward是否等于10000 , 如果成立将结果返回给客户端.

接着向存储引擎索要下一条记录,存储引擎在二级索引定位到记录后,获取主键值,然后回表操作.如此往复,知道所有记录读完.

使用索引下推:

Server层首先调用存储引擎接口定位到满足查询条件的第一条二级索引记录.定位到二级索引后,先不执行回表操作,而是先判断该索引中包含的列(reward列)的条件是否成立.如果不成立则直接跳过该二级索引.如果成立,则执行回表操作将完整的记录返回给Server层.

Server层判断其它查询条件是否成立,如果成立则将其发送给客户端.否则跳过.